• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. DDL Syntax Overview

DDL Syntax Overview

Data definition language (DDL) is used to define or modify an object in a database, such as a table, an index, or a view.

NOTE:

DWS does not support DDL if its CN is unavailable. For example, if a CN in the cluster is faulty, creating a database or a table will fail.

Defining a database

A database is the warehouse for organizing, storing, and managing data. Defining a database includes: creating a database, altering the database attributes, and dropping the database. The following table lists the related SQL statements.

Table 1 SQL statements for defining a database

Function

SQL Statement

Create a database.

CREATE DATABASE

Altering database attributes

ALTER DATABASE

Delete a database:

DROP DATABASE

Defining a schema

A schema is the set of a group of database objects and is used to control the access to the database objects. The following table lists the related SQL statements.

Table 2 SQL statements for defining a schema

Function

SQL Statement

Creation mode

CREATE SCHEMA

Altering schema attributes

ALTER SCHEMA

Deleting a schema

DROP SCHEMA

Defining a table

A table is a special data structure in a database and is used to store data objects and the relationship between data objects. The following table lists the related SQL statements.

Table 3 SQL statements for defining a table

Function

SQL Statement

Creating a table

CREATE TABLE

Altering table attributes

ALTER TABLE

Deleting a table

DROP TABLE

Defining a Partitioned Table

A partitioned table is a special data structure in a database and is used to store data objects and the relationship between data objects. The following table lists the related SQL statements.

Table 4 SQL statements for defining a partitioned table

Function

SQL Statement

Create a partitioned table.

CREATE TABLE PARTITION

Creating a partition

ALTER TABLE PARTITION

Altering partitioned table attributes

ALTER TABLE PARTITION

Delete a partition.

ALTER TABLE PARTITION

Deleting a partitioned table

DROP TABLE

Defining an index

An index indicates the sequence of values in one or multiple columns in the database table. The database index is a data structure that improves the speed of data access to specific information in a database table. The following table lists the related SQL statements.

Table 5 SQL statements for defining an index

Function

SQL Statement

Creating an Index

CREATE INDEX

Altering index attributes

ALTER INDEX

Delete an index.

DROP INDEX

Rebuild an index.

REINDEX

Defining a role

A role is used to manage rights. For database security, all management and operation rights can be assigned to different roles. The following table lists the related SQL statements.

Table 6 SQL statements for defining a role

Function

SQL Statement

Creating a role

CREATE ROLE

Altering role attributes

ALTER ROLE

Deleting a role

DROP ROLE

Defining a user

A user is used to log in to a database. Different rights can be assigned to users for managing data accesses and operations of users. The following table lists the related SQL statements.

Table 7 SQL statements for defining a user

Function

SQL Statement

Creating a Service User

CREATE USER

Altering user attributes

ALTER USER

Deleting a User

DROP USER

Defining a stored procedure

A stored procedure is a set of SQL statements for achieving specific functions and is stored in the database after compiling. Users can specify a name and provide parameters (if necessary) to execute the stored procedure. The following table lists the related SQL statements.

Table 8 SQL statements for defining a stored procedure

Function

SQL Statement

Creating a stored procedure

CREATE PROCEDURE

Deleting a stored procedure

DROP PROCEDURE

Defining a function

In DWS, a function is similar to a stored procedure, which is a set of SQL statements. The function and stored procedure are used the same. The following table lists the related SQL statements.

Table 9 SQL statements for defining a function

Function

SQL Statement

Creating a function

CREATE FUNCTION

Altering function attributes

ALTER FUNCTION

Deleting a function

DROP FUNCTION

Defining a View

A view is a virtual table exported from one or several basic tables. The view is used to control data accesses for users. The following table lists the related SQL statements.

Table 10 SQL statements for defining a view

Function

SQL Statement

Creating a view

CREATE VIEW

Deleting a view

DROP VIEW

Define a cursor:

To process SQL statements, the stored procedure process assigns a memory segment to store context association. Cursors are handles or pointers to context regions. With a cursor, the stored procedure can control alterations in context areas.

Table 11 SQL statements for defining a cursor

Function

SQL Statement

Creating a cursor

CURSOR

Moving a cursor

MOVE

Extracting data from a cursor

FETCH

Closing a cursor

CLOSE

Operation Session

A session is a connection established between the user and the database. The following table lists the related SQL statements.

Table 12 SQL statements related to sessions

Function

SQL Statement

Altering a session

ALTER SESSION

Ending a session

ALTER SYSTEM KILL SESSION

Defining a Workload Group

A workload group is a system catalog used by the resource load management module to specify the number of concurrent SQL statements in the associated resource pool. The following table lists the related SQL statements.

Table 13 SQL statements for defining a workload group

Function

SQL Statement

Creating a workload group

CREATE WORKLOAD GROUP

Modifying the attributes of a workload group

ALTER WORKLOAD GROUP

Deleting a workload group

DROP WORKLOAD GROUP

Defining application mapping

Application mapping is a system catalog used by the resource load management module to associate with a workload group. After a user connects to a database, the user can specify a workload group to associate SQL statements to certain resources. The following table lists the related SQL statements.

Table 14 SQL statements for defining application mapping

Function

SQL Statement

Creating application mapping

CREATE APP WORKLOAD GROUP MAPPING

Changing application mapping

ALTER APP WORKLOAD GROUP MAPPING

Deleting application mapping

DROP APP WORKLOAD GROUP MAPPING