Help & Documentation>Data Lake Compute

CREATE TABLE

Last updated: 2024-01-12 10:19:55

On this page:

Note

Supported kernels: Presto, SparkSQL.
Applicable table scope: Native Iceberg tables, external tables.
Purpose: To create a table with certain attributes, the CREATE TABLE AS syntax is supported.

External Table Syntax

Syntax

CREATE TABLE [ IF NOT EXISTS ] table_identifier
    ( col_name[:] col_type [ COMMENT col_comment ], ... )
USING data_source
    [ COMMENT table_comment ]
    [ PARTITIONED BY ( col_name1, transform(col_name2), ... ) ]
    [ LOCATION path ]
    [ TBLPROPERTIES ( property_name=property_value, ... ) ]

Category

USING data_source: Specifies the input type of data during table creation, currently includes: CSV, ORC, PARQUET, ICEBERG, etc. table_identifier: Specifies the table name, supports three-part names, for example: catalog.database.table. COMMENT: Description of the table. PARTITIONED BY: Creates partitions based on specified columns. LOCATION path: Storage path of the data table. TBLPROPERTIES: A set of key-value pairs used to specify table parameters.

Sample

CREATE TABLE dempts(
    id bigint COMMENT 'id number',
    num int,
    eno float,
    dno double,
    cno decimal(9,3),
    flag  boolean,
    data string,
    ts_year timestamp,
    date_month date,
    bno binary,
    point struct<x: double, y: double>,
    points array<struct<x: double, y: double>>,
    pointmaps map<struct<x: int>, struct<a: int>> 
    )
USING iceberg
COMMENT 'table documentation' 
PARTITIONED BY (bucket(16,id), years(ts_year), months(date_month), identity(bno),  bucket(3,num),  truncate(10,data))
LOCATION '/warehouse/db_001/dempts'
TBLPROPERTIES ('write.format.default'='orc');

Native Iceberg Table Syntax

Note
This syntax is exclusively for the creation of native tables.

Syntax

CREATE TABLE [ IF NOT EXISTS ] table_identifier
    ( col_name[:] col_type [ COMMENT col_comment ], ... )
[ COMMENT table_comment ]
[ PARTITIONED BY ( col_name1, transform(col_name2), ... ) ]

Category

table_identifier: Three-part notation supported, catalog.db.name Schemas and Data Types
col_type
  : primitive_type
  | nested_type

primitive_type
  : boolean
  | int/integer
  | long/bigint
  | float
  | double
  | decimal(p,s), where p is the maximum number of digits, s is the maximum number of decimal places, and s<=p<=38.
  | date
  | timestamp, timestamp with timezone, time and without timezone are not supported.
  | String, can also correspond to the Iceberg uuid type.
  | binary, can also correspond to Iceberg's fixed type

nested_type
  : struct
  | list
  | map
Partition Transforms
transform
  : identity, supports any type, DLC does not support this conversion.
  | bucket[N], hash mod N bucketing, supported col_type: int, long, decimal, date, timestamp, string, binary.
  | truncate[L], L truncates the bucket, supported col_type: int, long, decimal, string.
  | years, the year, col_type support: date, timestamp
  | Months, Month, col_type support: date, timestamp
  | days/date, date, col_type: date, timestamp are supported.
  | hours/date_hour, hour, col_type: timestamp is supported.

Sample

CREATE TABLE dempts(
    id bigint COMMENT 'id number',
    num int,
    eno float,
    dno double,
    cno decimal(9,3),
    flag  boolean,
    data string,
    ts_year timestamp,
    date_month date,
    bno binary,
    point struct<x: double, y: double>,
    points array<struct<x: double, y: double>>,
    pointmaps map<struct<x: int>, struct<a: int>> 
    )
COMMENT 'table documentation' 
PARTITIONED BY (bucket(16,id), years(ts_year), months(date_month), identity(bno),  bucket(3,num),  truncate(10,data));

中国站 - English