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

Creating a DLI Table Using the DataSource Syntax

Function

This DataSource syntax can be used to create a DLI table.

Syntax

1
2
3
4
5
6
7
 
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name 
  [(col_name1 col_type1 [COMMENT col_comment1], ...)]
  USING file_format 
  [OPTIONS (key1=val1, key2=val2, ...)] 
  [PARTITIONED BY (col_name1, col_name2, ...)]
  [COMMENT table_comment]
  [AS select_statement];

Keyword

  • IF NOT EXISTS: Prevents system errors when the created table exists.
  • USING: Specifies the storage format.
  • OPTIONS: Specifies the attribute name and attribute value when a table is created.
  • COMMENT: Field or table description.
  • PARTITIONED BY: Partition field.
  • 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

file_format

Data storage format of DLI tables. The value of file_format can be parquet.

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.
Table 2 OPTIONS parameter description

Parameter

Description

Default Value

multiLevelDirEnable

Whether to iteratively query data in subdirectories. When this parameter is set to true, all files in the table path, including files in subdirectories, are iteratively read when a table is queried.

false

Precautions

  • If no delimiter is specified, the comma (,) is used by default.
  • 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.
  • When a partitioned table is created, the partition field must be the last one or several fields of the table field, and the sequence of the partition fields must be the same. Otherwise, an error occurs.
  • A maximum of 7,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) USING PARQUET OPTIONS('key1' = 'value1');
  • To create a table in Carbon format named tb_carbon, 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 tb_carbon(key INT, value STRING) USING CARBON OPTIONS('key1' = 'value1');
  • To create a table in the Parquet format 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, classNo INT) USING PARQUET OPTIONS('key1' = 'value1') PARTITIONED BY(classNo) ;

    classNo is the partition field, which must be placed at the end of the table field, that is, student(name STRING, score INT, classNo INT).

  • To create a table in the Carbon format 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, classNo INT) USING CARBON OPTIONS('key1' = 'value1') PARTITIONED BY(classNo) ;

    classNo is the partition field, which must be placed at the end of the table field, that is, student(name STRING, score INT, classNo INT).

  • To create table t1 and insert data of table t2 into table t1, run the following statement: 
    1
    		 
    CREATE TABLE t1 USING parquet AS select * from t2;
Parent topic: Creating a DLI Table