CREATE TABLE

Creates a new table using GemFire XD features.

Syntax

CREATE TABLE table-name {
    ( { column-definition | table-constraint }
    [ , { column-definition | table-constraint } ] * )
| 
    [ ( column-name [, column-name ] * ) ]
    AS query-expression
    WITH NO DATA
}
    [ partitioning-clause | REPLICATE ]
    [ server-groups-clause ]
    [ gateway-sender-clause ]
    [ async-event-listener-clause ]
    [ eviction-clause | eviction-by-criteria-clause]
    [ expiration-clause ] *
    [ persistence-clause ]
    [ hdfs-store-clause ]
    [ offheap-clause ]

Description

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 syntax of CREATE TABLE is extended to give properties to the tables that are specific to GemFire XD.

The CREATE TABLE statement has two variants depending on whether you are specifying the column definitions and constraints (CREATE TABLE), or whether you are modeling the columns after the results of a query expression (CREATE TABLE...AS...).

Example of CREATE TABLE

gfxd> CREATE TABLE HOTELAVAILABILITY
    (HOTEL_ID INT NOT NULL, 
     BOOKING_DATE DATE NOT NULL,
     ROOMS_TAKEN INT DEFAULT 0,
     PRIMARY KEY (HOTEL_ID, BOOKING_DATE));

CREATE TABLE ... AS ...

With the alternate form of the CREATE TABLE statement, you specify the column names and/or the column data types with a query. The columns in the query result are used as a model for creating the columns in the new table.

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

Note that only the column names and datatypes from the queried table are used when creating the new table. Additional settings in the queried table, such as partitioning, replication, and persistence, are not duplicated. You can optionally specify partitioning, replication, and persistence configuration settings for the new table, and those settings need not match the settings of the queried table.

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

Example of CREATE TABLE...AS...

Create a new table using all of the columns and data types from an existing table, but partition the new table over all GemFire XD members in the default server group:
gfxd> CREATE TABLE HOTELSPARTITIONED AS SELECT * FROM HOTELAVAILABILITY WITH NO DATA PARTITION BY COLUMN (HOTEL_ID, BOOKING_DATE);
Create a new persistent table, providing new names for the columns but using the same data types as the columns of an existing table:
gfxd> CREATE TABLE HOTELSRENAMED (HID,BOOKDATE,OCCUPIEDRMS) AS SELECT * FROM HOTELAVAILABILITY WITH NO DATA PERSISTENT;
Create a new table, providing new names for the columns but using the data types from specific columns of an existing table:
gfxd> CREATE TABLE ROOMSTAKEN (HID,ROOMS) AS SELECT HOTEL_ID,ROOMS_TAKEN FROM HOTELAVAILABILITY WITH NO DATA;
This example shows that the columns in the result of the query expression may be unnamed expressions, but their data types can still be used to provide the data types for the corresponding named columns in the newly-created table:
gfxd> CREATE TABLE HOTELEXPERSSION (HID,ROOMS) AS SELECT 2*HOTEL_ID, 2*ROOMS_TAKEN FROM HOTELAVAILABILITY WITH NO DATA;