Help Center > > Developer Guide> Database Quick Start> Before You Start

Before You Start

Updated at: Jun 24, 2019 GMT+08:00

This chapter describes common operations in a database.

This chapter assumes that you have created a DWS cluster as required in Prerequisites and established the connection between a SQL query tool and the cluster.

This chapter describes how to quickly create databases and tables, insert data to tables, and query data in tables. Later sections in this chapter will elaborate on common operations.

Basic Database Operations

  1. Create a database user.

    By default, only administrators that are generated during cluster creation can access the initial database. They need to create user accounts and grant permissions to let other users access the database.

    CREATE USER joe WITH PASSWORD "Bigdata@123";

    If the following information is displayed, the resource pool has been created.

    CREATE ROLE

    In this case, you have created a user named joe and its password is Bigdata@123.

    By default, a new user account has the permissions to log in to all databases, create tables, views, and indexes, and perform operations on these objects. For details, see Users.

  2. Create a database:

    CREATE DATABASE mytpcds;
    CREATE DATABASE

    After creating the db_tpcds database, you can run the following command to exit the postgres database and connect to the db_tpcds database as the user you have created. You can also continue using the default database postgres.

    \q
    gsql -d db_tpcds -p 8000 -U joe -W Bigdata@123
    gsql ((GaussDB Kernel V300R002C00 build 50dc16a6) compiled at 2019-01-29 05:49:21 commit 1071 last mr 1373)
    Non-SSL connection (SSL connection is recommended when requiring high-security)
    Type "help" for help.
    mytpcds=> 

    For more information about database management, see Creating and Managing Databases.

  3. (Optional) Create a schema.

    Schemas allow multiple users to use the same database without mutual impacts.

    Run the following command to create a schema:

     CREATE SCHEMA myschema;

    If the following information is displayed, the schema named myschema has been created:

    CREATE SCHEMA

    After a schema is created, you can create objects under it. When creating an object, specify the required schema using either of the following methods:

    Set search_path of the database to the schema.

    SET SEARCH_PATH TO myschema;
    CREATE TABLE mytable (firstcol int);

    Specify a complete object name consisting of the schema and object names separated by periods (.). For example:

    CREATE TABLE myschema.mytable (firstcol int);

    If no schema is specified during object creation, the object will be created in the current schema. Run the following command to query the current schema:

     show search_path;
      search_path
    ----------------
     "$user",public
    (1 row)

    For more details about schemas, see Creating and Managing Schemas.

  4. Create a table.

    • Create a table named mytable that has only one column. The column name is firstcol and the column type is integer.
      mytpcds=>  CREATE TABLE mytable (firstcol int);

      If the DISTRIBUTE BY statement is not used to specify distribution columns, the system automatically specifies the first column as a distribution column and informs you of it. If CREATE TABLE is displayed at the end of the returned information, the table has been created.

      NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'firstcol' as the distribution column by default.
      HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
      CREATE TABLE

      The system catalog PG_TABLES contains information about all tables in a cluster. You can run the SELECT statement to view the attributes of a table in the system catalog.

      SELECT * FROM PG_TABLES WHERE TABLENAME = 'mytable';
    • Run the following command to insert data to the table:
      mytpcds=> INSERT INTO mytable values (100);
      INSERT 0 1

      The INSERT statement inserts rows to the database table. For details about standard batch loading, see About Parallel Data Import from OBS.

    • Run the following command to view data in the table:
      mytpcds=> SELECT * from mytable;
       firstcol 
      ----------
            100
      (1 row)
    NOTE:
    • By default, new database objects, such as the mytable table, are created in the public schema. For more details about schemas, see Creating and Managing Schemas.
    • For more details about how to create a table, see Creating a Table.
    • In addition to the created tables, a database contains many system catalogs. These system catalogs contain cluster installation information and information about various queries and processes in DWS. You can collect information about the database by querying the system catalog. For details, see Viewing a System Catalog.
    • DWS supports row and column storage, providing high query performance for interaction analysis in complex scenarios. For details about how to select a storage model, see Planning a Storage Model.

Loading Sample Data

Most examples in this document use the TPC-DS sample table created in the postgres database. Before you use your SQL query tool to perform operations in the examples, create the TPC-DS sample table and load data to it.

An OBS bucket provides sample data and is accessible to all authenticated cloud users.

For the steps to create a table and load sample data, see Loading Sample Data.

Releasing Resources

If you have deployed a cluster to test-perform operations in this section, delete it immediately after completing the operations, because the cluster continuously incurs cost.

To delete a cluster, follow the steps in section Deleting a Cluster in Data Warehouse Service Management Guide.

To retain the cluster but clear the db_tpcds database, run the following command:

DROP DATABASE mytpcds;

To retain the cluster and the database, run the following command to clear only the tables in the database:

DROP TABLE mytable;

  

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel