CREATE TABLE

Syntax

CREATE [EXTERNAL]^1,2 TABLE [IF NOT EXISTS] [catalog_name.][db_name.]table_name (

{ [col_name data_type [COMMENT col_comment] [ WITH³ ( property_name = expression [, ...] )³ ]

| LIKE³ existing_table_name [ {INCLUDING | EXCLUDING}³ PROPERTIES]³ } [, ...])

[[PARTITIONED BY¹(col_name data_type, ....)] [SORT BY¹ ([column [, column ...]])] ]¹

[COMMENT 'table_comment']

[ WITH ( property_name = expression [, ...] ) ]³

[[PARTITIONED BY² (col_name data_type, .....)] [CLUSTERED BY² (col_name, col_name, ...) [SORTED BY²³ (col_name, col_name, ...)] INTO num_buckets BUCKETS]² ]²

[[STORED AS file_format]

[LOCATION 'hdfs_path']

[TBLPROPERTIES (orc_table_property = value [, ...] ) ] ]^1,2

Remarks

Expressions or keywords with the same sequence number 1, 2, or 3) can be used at the same time. Expressions or keywords with different sequence numbers cannot be used at the same time.

• bucket_count can be set for the session attribute. The default value is -1, indicating that bucket_count is not set. During table creation, if bucket_count is set to -1 and buckets is not set in the table creation statement, the default value 16 is used.
• The default storage location of external tables is /user/hive/warehouse.
• Setting "transactional=true" enables tables to support atomicity, consistency, isolation, and durability (ACID). However, after a table is defined as a transaction table, it cannot be degraded to a non-transaction table by setting "transactional=false".

  When transactional='true' or '0' is executed, type conversion will not be performed. Therefore, the following exception is thrown:

  Cannot convert ['true'] to boolean

  Cannot convert ['0'] to boolean

• By default, data cannot be inserted into a managed table whose property external is set to true. To use this function, add custom Hive property hive.non-managed-table-writes-enabled=true. For details, see Precautions.
• The MPPDB has a restriction that the maximum length of a database identifier is 63. If the identifier name exceeds the maximum length, the excess part is automatically truncated and only the identifier with the maximum length is left.
• Table creation is not supported in cross-domain scenarios.

Description

Creates a new empty table with specified columns by using CREATE TABLE. Use the CREATE TABLE AS statement to create a table with data. When the optional parameter IF NOT EXISTS is used, no error is reported if the table already exists.

The WITH clause can be used to set properties for a newly created table or a single column, such as the storage location of the table and whether the table is an external table.

The LIKE clause is used to include all column definitions from an existing table in a new table. You can specify multiple LIKE clauses to allow columns to be copied from multiple tables. If INCLUDING PROPERTIES is specified, all table properties are copied to the new table. If the attribute name specified in the WITH clause is the same as the copied attribute name, the value in the WITH clause is used. By default, the EXCLUDING PROPERTIES attribute is used. You can specify the INCLUDING PROPERTIES attribute for only one table.

PARTITIONED BY can be used to specify the column of a partition. CLUSTERED BY can be used to specify columns for buckets. SORT BY and SORTED BY can be used to sort specified bucket columns. BUCKETS can be used to specify the number of buckets. EXTERNAL can be used to create foreign tables. STORED AS can be used to specify the file storage format. LOCATION can be used to specify the storage path in HDFS.

To view the table properties supported by the current data source, run the following command:

SELECT * FROM system.metadata.table_properties;

The following table lists the query result when the catalog is hive.

Example

• To create a new table orders and use the WITH clause to specify the storage format, storage location, and whether the table is an external table.

  The auto.purge parameter can be used to specify whether to clear related data when data removal operations (such as DROP, DELETE, INSERT OVERWRITE, and TRUNCATE TABLE) are performed.

  • If it is set to true, metadata and data files are cleared.
  • If it is set to false, only metadata is cleared and data files are moved to the HDFS trash bin. The default value is false. You are advised not to change the value. Otherwise, deleted data cannot be restored.

  CREATE TABLE orders (
  orderkey bigint,
  orderstatus varchar,
  totalprice double,
  orderdate date
  )
  WITH (format = 'ORC', location='/user',orc_compress='ZLIB',external=true, "auto.purge"='false');
  
  -- You can run the DESC FORMATTED statement to view details about table creation.
  desc formatted  orders ;
                             Describe Formatted Table                           
  ------------------------------------------------------------------------------
   # col_name      data_type      comment                                       
   orderkey      bigint                                                         
   orderstatus      varchar                                                     
   totalprice      double                                                       
   orderdate      date                                                          
  
   # Detailed Table Information                                                 
   Database:                   default                                          
   Owner:                      admintest                                        
   LastAccessTime:             0                                                
   Location:                   hdfs://hacluster/user                            
   Table Type:                 EXTERNAL_TABLE                                   
  
   # Table Parameters:                                                          
          EXTERNAL                TRUE                                                     
          auto.purge              false                                                     
          orc.compress.size       262144                                                   
          orc.compression.codec   ZLIB                                                    
          orc.row.index.stride    10000                                                    
          orc.stripe.size         67108864                                                 
          presto_query_id         20220812_084110_00050_srknk@default@HetuEngine           
          presto_version          1.2.0-h0.cbu.mrs.320.r1-SNAPSHOT                         
          transient_lastDdlTime   1660293670                                              
  
   # Storage Information                                                        
   SerDe Library:              org.apache.hadoop.hive.ql.io.orc.OrcSerde        
   InputFormat:                org.apache.hadoop.hive.ql.io.orc.OrcInputFormat  
   OutputFormat:               org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat 
   Compressed:                 No                                               
   Num Buckets:                -1                                               
   Bucket Columns:             []                                               
   Sort Columns:               []                                               
   Storage Desc Params:                                                         
          serialization.format    1                                                        
  (1 row)

• If the orders table does not exist, create the orders table and add table comments and column comments:

  CREATE TABLE IF NOT EXISTS new_orders (
  orderkey bigint,
  orderstatus varchar,
  totalprice double COMMENT 'Price in cents.',
  orderdate date
  )
  COMMENT 'A table to keep track of orders.';
  insert into new_orders values
  (202011181113,'online',9527,date '2020-11-11'),
  (202011181114,'online',666,date '2020-11-11'),
  (202011181115,'online',443,date '2020-11-11'),
  (202011181115,'offline',2896,date '2020-11-11');

• Create the bigger_orders table using the column definition of the orders table:

  CREATE TABLE bigger_orders (
  another_orderkey bigint,
  LIKE orders,
  another_orderdate date
  );
  
  SHOW CREATE TABLE bigger_orders ;
                              Create Table                             
  ---------------------------------------------------------------------
   CREATE TABLE hive.default.bigger_orders (                           
      another_orderkey bigint,
      orderkey bigint,
      orderstatus varchar,
      totalprice double,
      ordersdate date,                                             
      another_orderdate date                                           
   )                                                                   
   WITH (                                                              
      external = false,                                                
      format = 'ORC',                                                  
      location = 'hdfs://hacluster/user/hive/warehouse/bigger_orders', 
      orc_compress = 'GZIP',                                           
      orc_compress_size = 262144,                                      
      orc_row_index_stride = 10000,                                    
      orc_stripe_size = 67108864                                       
   )                                                                   
  (1 row)

¹ Example of creating a table:

CREATE EXTERNAL TABLE hetu_test (orderkey bigint, orderstatus varchar, totalprice double, orderdate date) PARTITIONED BY(ds int) SORT BY (orderkey, orderstatus) COMMENT 'test' STORED AS ORC LOCATION '/user' TBLPROPERTIES (orc_compress = 'SNAPPY', orc_compress_size = 6710422, orc_bloom_filter_columns = 'orderstatus,totalprice');

² Example of creating a table:

CREATE EXTERNAL TABLE hetu_test1 (orderkey bigint, orderstatus varchar, totalprice double, orderdate date) COMMENT 'test' PARTITIONED BY(ds int) CLUSTERED BY (orderkey, orderstatus) SORTED BY (orderkey, orderstatus) INTO 16 BUCKETS STORED AS ORC LOCATION '/user'  TBLPROPERTIES (orc_compress = 'SNAPPY', orc_compress_size = 6710422, orc_bloom_filter_columns = 'orderstatus,totalprice');

³Example of creating a table:

CREATE TABLE hetu_test2 (orderkey bigint, orderstatus varchar, totalprice double, orderdate date, ds int) COMMENT 'This table is in Hetu syntax' WITH (partitioned_by = ARRAY['ds'], bucketed_by = ARRAY['orderkey', 'orderstatus'], sorted_by = ARRAY['orderkey', 'orderstatus'], bucket_count = 16, orc_compress = 'SNAPPY', orc_compress_size = 6710422, orc_bloom_filter_columns = ARRAY['orderstatus', 'totalprice'], external = true, format = 'orc', location = '/user');

Table Configuration

Example:

CREATE TABLE format1(orderkey bigint, orderstatus varchar, totalprice double, orderdate date)  TBLPROPERTIES (orc_compress = 'SNAPPY', orc_compress_size = 6710422, orc_bloom_filter_columns = 'orderstatus,totalprice');

CREATE TABLE format2(orderkey bigint, orderstatus varchar, totalprice double, orderdate date) TBLPROPERTIES (orc_compress = 'LZ4', orc_stripe_size = 33554432, orc_bloom_filter_fpp = 0.1, orc_row_index_stride=5000,  orc_compress_size = 6710422, orc_bloom_filter_columns = 'orderstatus,totalprice');

Show create table format2;
                             Create Table
-----------------------------------------------------------------------
 CREATE TABLE hive.default.format2 (
    orderkey bigint,
    orderstatus varchar,
    totalprice double,
    orderdate date
 )
 WITH (
    external = false,
    format = 'ORC',
    location = '/user/hive/warehouse/format2',
    orc_bloom_filter_columns = ARRAY['orderstatus','totalprice'],
    orc_bloom_filter_fpp = 1E-1,
    orc_compress = 'LZ4',
    orc_compress_size = 6710422,
    orc_row_index_stride = 5000,
    orc_stripe_size = 33554432
 )
(1 row)

Create a partitioned table.

--Create a schema.
CREATE SCHEMA hive.web WITH (location = 'hdfs://hacluster/user');
--Create a partitioned table.
CREATE TABLE hive.web.page_views (
  view_time timestamp,
  user_id bigint,
  page_url varchar,
  ds date,
  country varchar
)
WITH (
  format = 'ORC',
  partitioned_by = ARRAY['ds', 'country'],
  bucketed_by = ARRAY['user_id'],
  bucket_count = 50
);
--Insert an empty partition.
CALL system.create_empty_partition(
    schema_name => 'web',
    table_name => 'page_views',
    partition_columns => ARRAY['ds', 'country'],
    partition_values => ARRAY['2020-07-17', 'US']);
    
CALL system.create_empty_partition(
    schema_name => 'web',
    table_name => 'page_views',
    partition_columns => ARRAY['ds', 'country'],
    partition_values => ARRAY['2020-07-18', 'US']);
    
--View the partition.
SELECT * FROM hive.web."page_views$partitions";
     ds     | country 
------------|---------
 2020-07-18 | US      
 2020-07-17 | US    
--Insert data.
insert into hive.web.page_views values(timestamp '2020-07-17 23:00:15',bigint '15141','www.local.com',date '2020-07-17','US' );
insert into hive.web.page_views values(timestamp '2020-07-18 23:00:15',bigint '18148','www.local.com',date '2020-07-18','US' );

--Query data.
 select * from hive.web.page_views;
        view_time        | user_id |   page_url    |     ds     | country 
-------------------------|---------|---------------|------------|---------
 2020-07-17 23:00:15.000 |   15141 | www.local.com | 2020-07-17 | US      
2020-07-18 23:00:15.000 |   18148 | www.local.com | 2020-07-18 | US