Before You Start

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.

   1	CREATE USER joe WITH PASSWORD 'password';

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

   1	CREATE USER

   A user named joe is created. You can define its password.

   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.

   1	CREATE DATABASE mytpcds;

   For details about database management, 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 its objects. When creating an object, specify the required schema using either of the following methods:

   Set search_path of the database to the schema.

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

   Specify an object name that contains the schema name. Separate multiple object names by periods (.). The following shows an 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 statement 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, you can run the following command to quit the gaussdb database:

   1

   \q

   For 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.

     1	CREATE TABLE mytable (firstcol int);

     If the DISTRIBUTE BY statement is not used to specify the distribution column, 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	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.

     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.

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

   • Insert data to the table.

     1	INSERT INTO mytable values (100);

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

   • View data in the table.

     1 2 3 4 5

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

   

   • By default, new database objects, such as the mytable table, are created in the public schema. For details about schemas, see Creating and Managing Schemas.
   • For 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 of GaussDB(DWS). You can collect information about a database by querying system catalogs. For details, see Querying System Catalogs.
   • GaussDB(DWS) supports hybrid row and column storage, which provides 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 gaussdb 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 a cluster is deployed for the practice, delete the cluster after the practice is complete.

For details about how to delete clusters, see Deleting Clusters.

To keep the cluster but delete the db_tpcds database, run the following statement:

1

DROP DATABASE mytpcds;

To keep the cluster and the database, run the following statement to delete the tables in the database:

1

DROP TABLE mytable;