• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. CREATE TABLE AS

CREATE TABLE AS

Function

CREATE TABLE AS creates a table based on the results of a query.

CREATE TABLE AS creates a table and fills it with data computed by a SELECT command. The table columns have the names and data types associated with the output columns of the SELECT. Except that you can override the SELECT output column names by giving an explicit list of new column names.

CREATE TABLE AS queries once the source table and writes data in the new table. The query result view changes when the source table changes. In contrast, a view re-evaluates its defining SELECT statement whenever it is queried.

Precautions

  • This command cannot be used to create a partitioned table.
  • If an error occurs when you create a table, after the system is recovered, the system probably cannot automatically clear the created disk file whose size is not 0. This problem seldom occurs.

Syntax

CREATE [ UNLOGGED ] TABLE table_name
    [ (column_name [, ...] ) ]
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ COMPRESS | NOCOMPRESS ]
    
    [ DISTRIBUTE BY { REPLICATION | { [HASH ] ( column_name ) } } ]
    AS query
    [ WITH [ NO ] DATA ];

Parameter Description

  • UNLOGGED

    Specifies that the table is created as an unlogged table. Data written to unlogged tables is not written to the write-ahead log, which makes them considerably faster than ordinary tables. However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown. Contents of an unlogged table are also not replicated to standby servers. Any indexes created on an unlogged table are not automatically logged as well.

    • Usage scenario: Unlogged tables do not ensure safe data. Users can back up data before using unlogged tables; for example, users should back up the data before a system upgrade.
    • Troubleshooting: If data is missing in the indexes of unlogged tables due to some unexpected operations such as an unclean shutdown, users should re-create the indexes with errors.
  • table_name

    Specifies the name of the table to be created.

    Value range: a string compliant with the naming convention.

  • column_name

    Specifies the name of a column to be created in the new table.

    Value range: a string compliant with the naming convention.

  • WITH ( storage_parameter [= value] [, ... ] )

    Specifies an optional storage parameter for a table or an index. See details of parameters below.

    • FILLFACTOR

      The fillfactor of a table is a percentage between 10 and 100. 100 (complete packing) is the default value. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage. The remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, setting the fillfactor to 100 (complete packing) is the best choice, but in heavily updated tables smaller fillfactors are appropriate. The parameter is only valid for row–store table.

      Value range: 10–100

    • ORIENTATION

      Value range:

      COLUMN: The data will be stored in columns.

      ROW (default value): The data will be stored in rows.

    • COMPRESSION

      Specifies the compression level of the table data. It determines the compression ratio and time. Generally, the higher the level of compression, the higher the ratio, the longer the time, and the lower the level of compression, the lower the ratio, the shorter the time. The actual compression ratio depends on the distribution characteristics of loading table data.

      Value range:

      The valid values of column-store tables are YES, NO, MIDDLE, and HIGH, and the default is LOW.

      The valid values of row-store tables are YES and NO, and the default is NO.

    • MAX_BATCHROW

      Specifies the maximum of a storage unit during data loading process. The parameter is only valid for column-store table.

      Value range: 10000 to 60000

    • PARTIAL_CLUSTER_ROWS

      Specifies the number of records to be partial cluster stored during data loading process. The parameter is only valid for column-store table.

      Value range: 600000 to 2147483647

  • COMPRESS / NOCOMPRESS

    Specifies key word COMPRESS during the creation of a table, so that the compression feature is triggered in the case of a bulk INSERT operation. If this feature is enabled, a scan is performed for all tuple data within the page to generate a dictionary and then the tuple data is compressed and stored. If NOCOMPRESS is specified, the table is not compressed.

    Default value: NOCOMPRESS, tuple data is not compressed before storage.

  • DISTRIBUTE BY

    Specifies how the table is distributed or replicated between DNs.

    • REPLICATION: Each row in the table exists on all DNs, that is, each DN has complete table data.
    • HASH (column_name): Performs Hash to a specified column and maps data to a specified DN.
    • When DISTRIBUTE BY HASH (column_name) is specified, the primary key and its unique index must contain the column_name column.
    • When DISTRIBUTE BY HASH (column_name) in a referenced table is specified, the foreign key of the reference table must contain the column_name column.

    Default value: HASH(column_name), the key column of column_name (if any) or the column of distribution column supported by first data type.

    column_name supports the following date types:

    • Integer types: TINYINT, SMALLINT, INT, BIGINT, and NUMERIC/DECIMAL
    • Character types: CHAR, BPCHAR, VARCHAR, VARCHAR2, and NVARCHAR2
    • Date/time types: DATE, TIME, TIMETZ, TIMESTAMP, TIMESTAMPTZ, INTERVAL, and SMALLDATETIME
  • AS query

    Indicates a SELECT or VALUES command, or an EXECUTE command that runs a prepared SELECT, or VALUES query.

  • [ WITH [ NO ] DATA ]

    Specifies whether the data produced by the query should be copied to the new table. By default, the data is copied. If this parameter is set to NO, only the table structure is copied.

Example

-- Create the tpcds.store_returns_t1 table and insert numbers that are greater than 16 in the sr_item_sk column of the tpcds.store_returns table:
CREATE TABLE tpcds.store_returns_t1 AS SELECT * FROM tpcds.store_returns WHERE sr_item_sk > '4795';

-- Copy tpcds.store_returns to create the tpcds.store_returns_t2 table:
CREATE TABLE tpcds.store_returns_t2 AS table tpcds.store_returns;

-- Delete the tables:
DROP TABLE tpcds.store_returns_t1 ;
DROP TABLE tpcds.store_returns_t2 ;

Helpful Links

CREATE TABLE, SELECT