Help Center/ Data Lake Insight/ Delta SQL Syntax Reference/ Delta SQL Syntax Reference/ Delta DDL Syntax/ CREATE TABLE
Updated on 2025-04-21 GMT+08:00
View PDF

CREATE TABLE

Function

This command creates a Delta table by specifying a list of fields with table attributes.

Precautions

  • In this command, IF EXISTS and db_name are optional.
  • In DLI, Delta only supports OBS foreign tables. Creating a table using a table name without specifying the location will fail.

Syntax

  • Create a Delta table by table name.

    CREATE[ OR REPLACE] TABLE [ IF NOT EXISTS] [database_name.]table_name

    [ (columnTypeList)]

    USING DELTA

    [ COMMENT table_comment ]

    [ PARTITIONED BY (partColumnList) ]

    LOCATION location_path

  • Create a Delta table by delta.'Obs path'.

    CREATE[ OR REPLACE] TABLE [ IF NOT EXISTS] DELTA.`obs://bucket_name/tbl_path`

    [ (columnTypeList)]

    USING DELTA

    [ COMMENT table_comment ]

    [ PARTITIONED BY (partColumnList) ]

Creating a table by table name allows the table to be found with show tables. In the current version, the location must be specified and can only be set to an OBS path. Creating a table by delta.'Obs path' means the table cannot be found with show tables.

Parameter Description

Table 1 Parameter descriptions of CREATE TABLE

Parameter

Description

database_name

Name of the database, consisting of letters, numbers, and underscores (_)

table_name

Name of the table in the database, consisting of letters, numbers, and underscores (_)

bucket_name

Name of the OBS bucket

tbl_path

Storage location of the Delta table in the OBS bucket

columnTypeList

Comma-separated list with data types. Column names consist of letters, numbers, and underscores (_).

using

Parameter delta, defines and creates the Delta table

table_comment

Description of the table

partColumnList

List of partition fields, with column names from columnTypeList

location_path

Storage location of the Delta table. In the current version, it must be specified and can only support OBS paths. Specifying this path creates the Delta table as a foreign table.

Required Permissions

  • SQL permissions
    Table 2 Permissions required for executing CREATE TABLE

    Permission Description

    CREATE_TABLE permission on the database
  • Fine-grained permission: dli:database:createTable
  • Metadata services provided by LakeFormation. Refer to the LakeFormation documentation for details on permission configuration.

Examples

  • Create a non-partitioned table by table name. 
    create table if not exists delta_table0 (
id int,
name string,
addr struct<priv:string,city:string>,
price double
) using delta
location 'obs://bucket_name0/db0/delta_table0';
  • Create a partitioned table by table name. 
    create table if not exists delta_table0 (
id bigint,
name string,
ts bigint,
dt string,
hh string
) using delta
partitioned by (dt, hh)
location 'obs://bucket_name0/db0/delta_table0';
  • Create a non-partitioned table by delta path. 
    create table if not exists delta.`obs://bucket_name0/db0/delta_table0`(
id int,
name string,
price double
) using delta;
  • Create a partitioned table by delta path. 
    create table if not exists delta.`obs://bucket_name0/db0/delta_table0` (
id bigint,
name string,
ts bigint,
dt string,
hh string
) using delta
partitioned by (dt, hh);
  • Create a table using create table like. 
    create table delta_table2 like delta_table1 
using delta
location 'obs://bucket_name0/db0/delta_table2';

System Response

Displays the successful creation of the table.

Parent topic: Delta DDL Syntax