Basic Database Operations

1. Create a database user.

   By default, only administrators that are generated during cluster creation can access the initial database. If other users want to access the database, run the following statement to create a new user account with permission granted:

   1	CREATE USER joe WITH PASSWORD "Bigdata@123";

   If the following information is displayed, the user has been created.

   1	CREATE USER

   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 more information, see Users.

2. Create a database.

   1 2	CREATE DATABASE mytpcds; CREATE DATABASE

   For details, see Creating and Managing Databases.

3. (Optional) Create a schema.

   Schemas allow multiple users to use the same database without interfering with each other.

   Run the following command to create a schema:

   1	CREATE SCHEMA myschema;

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

   1	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 specified schema.

   1 2	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 (.). Example:

   1	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:

   1 2 3 4 5

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

   After the mytpcds database is created, run the following command to quit the postgres database:

   1

   \q

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

4. Create a table.
   • Create a table named mytable with only one column. The column name is firstcol and the column type is integer.

     1	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 that meets the criteria as a distribution column. If CREATE TABLE is displayed at the end of the returned information, the table has been created.

     1 2 3

     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

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

     1	SELECT * FROM PG_TABLES WHERE TABLENAME = 'mytable';

   • Run the following command to insert data to the table:

     1 2	mytpcds=> INSERT INTO mytable values (100); INSERT 0 1

     The INSERT statement is used to insert 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:

     1 2 3 4 5

     mytpcds=> SELECT * from mytable; firstcol ---------- 100 (1 row)

   

   • 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 details about table creation, see Creating a Table.
   • In addition to the created tables, a database contains many system catalogs which contain cluster installation information and information about various queries and processes in GaussDB(DWS). You can collect information about the database by querying the system catalog. For details, see Querying System Catalogs.
   • GaussDB(DWS) supports hybrid row-column storage, providing high query performance for interactive analysis in complex scenarios. For details about storage model selection, see Planning a Storage Model.

Loading Sample Data

Most examples in this document are based on the TPC-DS sample table created in the postgres database. Before you use your SQL query tool to perform operations, create the TPC-DS sample table first 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 a cluster is deployed for the practice, delete the cluster after the practice is complete.

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:

1

DROP DATABASE mytpcds;

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

1

DROP TABLE mytable;