Help Center> Data Lake Insight> SQL Syntax Reference> SQL Syntax of Batch Jobs> Creating a DLI Table> Creating a DLI Table Using the Hive Syntax
View PDF

Creating a DLI Table Using the Hive Syntax

Function

This Hive syntax is used to create a DLI table.

Syntax

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name 
  [(col_name1 col_type1 [COMMENT col_comment1], ...)]
  [COMMENT table_comment] 
  [PARTITIONED BY (col_name2 col_type2, [COMMENT col_comment2], ...)] 
  [ROW FORMAT row_format]
  STORED AS file_format 
  [TBLPROPERTIES (key1=val1, key2=val2, ...)]
  [AS select_statement];

row_format:
  : SERDE serde_cls [WITH SERDEPROPERTIES (key1=val1, key2=val2, ...)]
  | DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]]
      [COLLECTION ITEMS TERMINATED BY char]
      [MAP KEYS TERMINATED BY char]
      [LINES TERMINATED BY char]
      [NULL DEFINED AS char]

Keyword

  • IF NOT EXISTS: Prevents system errors when the created table exists.
  • COMMENT: Field or table description.
  • PARTITIONED BY: Partition field.
  • ROW FORMAT: Row data format.
  • STORED AS: Specifies the format of the file to be stored. Currently, only the TEXTFILE, AVRO, ORC, SEQUENCEFILE, RCFILE, PARQUET, and CARBON format are supported.
  • TBLPROPERTIES: The TBLPROPERTIES clause allows you to add the key/value attribute to a table.
  • AS: Run the CREATE TABLE AS statement to create a table.

Parameter Description

Table 1 CREATE TABLE parameter description

Parameter

Description

db_name

Database name that contains letters, digits, and underscores (_). It cannot contain only digits and cannot start with an underscore (_).

table_name

Table name of a database that contains letters, digits, and underscores (_). It cannot contain only digits and cannot start with an underscore (_). The matching rule is ^(?!_)(?![0-9]+$)[A-Za-z0-9_$]*$. If special characters are required, use single quotation marks ('') to enclose them.

col_name

Column names with data types separated by commas (,). The column name contains letters, digits, and underscores (_). It cannot contain only digits and must contain at least one letter.

col_type

Field type

col_comment

Field description

row_format

Line data format

file_format

Data storage format: TEXTFILE, AVRO, ORC, SEQUENCEFILE, RCFILE, PARQUET, CARBON.

table_comment

Table description

select_statement

The CREATE TABLE AS statement is used to insert the SELECT query result of the source table or a data record to a newly created DLI table.

Precautions

  • When a partition table is created, the partition column specified in PARTITIONED BY must be a column in the table, and the partition type must be specified. The partition column supports only the string, boolean, tinyint, smallint, short, int, bigint, long, decimal, float, double, date, and timestamp type.
  • Multiple partition fields can be specified. The partition fields need to be specified after the PARTITIONED BY keyword, instead of the table name. Otherwise, an error occurs.
  • A maximum of 100,000 partitions can be created in a single table.
  • The CREATE TABLE AS statement cannot specify table attributes or create partition tables.

Example

  • To create a table named src, where the key and value fields are contained, the corresponding data types are INT and STRING, and the attribute is specified as required, run the following statement: 
    1
    		 
    CREATE TABLE src(key INT, value STRING) TBLPROPERTIES('key1' = 'value1');
  • To create a table named student, for which classNo is the partition field and two fields name and score are specified, run the following statement: 
    1
    		 
    CREATE TABLE student(name STRING, score INT) TBLPROPERTIES('key1' = 'value1') PARTITIONED BY(classNo INT);
  • To create table t1 and insert data of table t2 into table t1, run the following statement: 
    1
    		 
    CREATE TABLE t1 as select * from t2;
Parent topic: Creating a DLI Table