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.
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.
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 User and Permission Overview.
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 ((Gauss200 V100R008C00 build 50dc16a6) compiled at 2018-03-02 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.
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:
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.
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';
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.
mytpcds=> SELECT * from mytable; firstcol ---------- 100 (1 row)
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.
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 User 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;