CREATE TABLE

A CREATE TABLE statement creates a table. Tables contain columns and constraints, rules to which data must conform. Table-level constraints specify a column or columns. Columns have a data type and can specify column constraints (column-level constraints).

The table owner and the database owner automatically gain the following privileges on the table and are able to grant these privileges to other users:

• INSERT
• SELECT
• TRIGGER
• UPDATE

These privileges cannot be revoked from the table and database owners.

Only database and schema owners can use the CREATE TABLE statement, which means that table creation privileges cannot be granted to others.

For information about constraints, see  CONSTRAINT clause.

You can specify a default value for a column. A default value is the value to be inserted into a column if no other value is specified. If not explicitly specified, the default value of a column is NULL.

If a qualified table name is specified, the schema name cannot begin with SYS.

Syntax

There are two different variants of the CREATE TABLE statement, depending on whether you are specifying the column definitions and constraints, or whether you are modeling the columns after the results of a query expression with the CREATE TABLE AS form:

CREATE TABLE table-Name
  {
      ( {column-definition |
         Table-level constraint}
         [ , {column-definition} ] *
      )
      [ [LOGICAL | PHYSICAL] SPLITKEYS LOCATION filePath]
  |
      [ ( column-name ]* ) ]
      AS query-expression [AS <name>]
      WITH NO DATA
  }

Using Split Keys

You can optionally include a file of split keys for the new table; you can include split keys when you know how the data that is going to be added to the table should be split into regions. This capability is typically used when you’re creating a table for restoring a table that was previously backed up using the SYSCS_UTIL.SYSCS_BACKUP_TABLE system procedure.

Creating a table with pre-defined splits is much faster than creating a table with one region and then splitting it into many regions. The split keys file can contain either LOGICAL or PHYSICAL keys:

• Logical keys are the primary key column values that you want to define the splits.
• Physical keys are actual split keys for the HBase table, in encoded HBase format.

CREATE TABLE … AS …

With this alternate form of the CREATE TABLE statement, the column names and/or the column data types can be specified by providing a query. The columns in the query result are used as a model for creating the columns in the new table.

You cannot include an ORDER BY clause in the query expression you use in the CREATE TABLE AS statement.

If the select list contains an expression, you must name the result of the expression. Refer to the final example at the bottom of this topic page.

If no column names are specified for the new table, then all the columns in the result of the query expression are used to create same-named columns in the new table, of the corresponding data type(s). If one or more column names are specified for the new table, then the same number of columns must be present in the result of the query expression; the data types of those columns are used for the corresponding columns of the new table.

The WITH NO DATA clause specifies that the data rows which result from evaluating the query expression are not used; only the names and data types of the columns in the query result are used.

splice> CREATE TABLE t3 AS
   SELECT t1.a,t1.b,t2.c,t2.d
   FROM t1 RIGHT OUTER JOIN t2 ON t1.b = t2.c
   WITH DATA;
0 rows inserted/updated/deleted

splice> CREATE TABLE t3 AS
   SELECT t1.a,t1.b,t2.c,t2.d
   FROM t1 RIGHT OUTER JOIN t2 ON t1.b = t2.c
   WITH NO DATA;
0 rows inserted/updated/deleted

splice> INSERT INTO t3
   SELECT t1.a,t1.b,t2.c,t2.d
   FROM t1 RIGHT OUTER JOIN t2 ON t1.b = t2.c;
0 rows inserted/updated/deleted

Examples

This section presents examples of both forms of the CREATE TABLE statement.

CREATE TABLE

This example creates our Players table:

splice> CREATE TABLE Players(
    ID           SMALLINT NOT NULL PRIMARY KEY,
    Team         VARCHAR(64) NOT NULL,
    Name         VARCHAR(64) NOT NULL,
    Position     CHAR(2),
    DisplayName  VARCHAR(24),
    BirthDate    DATE
    );
0 rows inserted/updated/deleted

This example includes a table-level primary key definition that includes two columns:

splice> CREATE TABLE HOTELAVAILABILITY (
   Hotel_ID INT NOT NULL,
   Booking_Date DATE NOT NULL,
   Rooms_Taken INT DEFAULT 0,
   PRIMARY KEY (Hotel_ID, Booking_Date ));
0 rows inserted/updated/deleted

This example assigns an identity column attribute with an initial value of 5 that increments by 5, and also includes a primary key constraint:

splice> CREATE TABLE PEOPLE (
   Person_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 5, INCREMENT BY 5)
      CONSTRAINT People_PK PRIMARY KEY,
   Person VARCHAR(26) );
0 rows inserted/updated/deleted

For more examples of CREATE TABLE statements using the various constraints, see  CONSTRAINT clause

CREATE TABLE with SPLIT KEYS

Using Logical Split Keys

This is an example of creating a new table that will be split into regions based on the primary key values in the lineitemKeys.csv file:

CREATE TABLE LINEITEM (
  L_ORDERKEY      INTEGER NOT NULL,
  L_PARTKEY       INTEGER NOT NULL,
  L_SUPPKEY       INTEGER NOT NULL,
  L_LINENUMBER    INTEGER NOT NULL,
  L_QUANTITY      DECIMAL(15, 2),
  L_EXTENDEDPRICE DECIMAL(15, 2),
  L_DISCOUNT      DECIMAL(15, 2),
  L_TAX           DECIMAL(15, 2),
  L_RETURNFLAG    CHAR(1),
  L_LINESTATUS    CHAR(1),
  L_SHIPDATE      DATE,
  L_COMMITDATE    DATE,
  L_RECEIPTDATE   DATE,
  L_SHIPINSTRUCT  CHAR(25),
  L_SHIPMODE      CHAR(10),
  L_COMMENT       VARCHAR(44),
  PRIMARY KEY (L_ORDERKEY, L_LINENUMBER)
) splitkeys location '/temp/lineitemKeys.csv';

Here’s what the lineitemKeys.csv file looks like:

1424004,7
2384419,4
3244416,6
5295747,4

Using Physical Split Keys

This is an example of creating a new table that will be split into regions based on the encoded HBase split keys:

CREATE TABLE LINEITEM (
  L_ORDERKEY      INTEGER NOT NULL,
  L_PARTKEY       INTEGER NOT NULL,
  L_SUPPKEY       INTEGER NOT NULL,
  L_LINENUMBER    INTEGER NOT NULL,
  L_QUANTITY      DECIMAL(15, 2),
  L_EXTENDEDPRICE DECIMAL(15, 2),
  L_DISCOUNT      DECIMAL(15, 2),
  L_TAX           DECIMAL(15, 2),
  L_RETURNFLAG    CHAR(1),
  L_LINESTATUS    CHAR(1),
  L_SHIPDATE      DATE,
  L_COMMITDATE    DATE,
  L_RECEIPTDATE   DATE,
  L_SHIPINSTRUCT  CHAR(25),
  L_SHIPMODE      CHAR(10),
  L_COMMENT       VARCHAR(44),
  PRIMARY KEY (L_ORDERKEY, L_LINENUMBER)
) physical splitkeys location '/temp/lineitemKeys.txt';

Here is what the lineitemKeys.txt file looks like:

\xE4\x15\xBA\x84\x00\x87
\xE4$b#\x00\x84
\xE41\x81\x80\x00\x86
\xE4P\xCE\x83\x00\x84

CREATE TABLE AS

This example creates a new table that uses all of the columns (and their data types) from an existing table, but does not duplicate the data:

splice> CREATE TABLE NewPlayers
   AS SELECT * 
         FROM Players WITH NO DATA;
0 rows inserted/updated/deleted

This example creates a new table that includes the data and uses only some of the columns from an existing table, and assigns new names for the columns:

splice> CREATE TABLE MorePlayers (ID, PlayerName, Born)
   AS SELECT ID, DisplayName, Birthdate
         FROM Players WITH DATA;
94 rows inserted/updated/deleted

This example creates a new table using unnamed expressions in the query and shows that the data types are the same for the corresponding columns in the newly created table:

splice> CREATE TABLE T3 (X,Y)
   AS SELECT 2*I AS COL1, 2.0*F AS COL2
         FROM T1 WITH NO DATA;
0 rows inserted/updated/deleted

See Also

• ALTER TABLE statement
• CREATE EXTERNAL TABLE statement
• CONSTRAINT clause
• DROP TABLE statement
• Foreign Keys in the Developer’s Guide.
• Triggers in the Developer’s Guide.