On this page

Show all

Help Center/ Data Lake Insight/ Delta SQL Syntax Reference/ Delta SQL Syntax Reference/ Delta DDL Syntax/ CREATE TABLE

CREATE TABLE

Updated on 2025-01-09 GMT+08:00

View PDF

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) ]

NOTE:

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

Previous topic: Delta DDL Syntax

Next topic: DROP TABLE

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot