Help Center/ Data Lake Insight/ Delta SQL Syntax Reference/ Delta SQL Syntax Reference/ Delta DML Syntax/ CREATE TABLE AS SELECT
Updated on 2025-01-09 GMT+08:00
View PDF
Share

CREATE TABLE AS SELECT

Function

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

Syntax

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

USING DELTA

[ COMMENT table_comment ]

[ PARTITIONED BY (partColumnList) ]

[ LOCATION location_path]

[ AS query_statement ]

Parameter Description

Table 1 Parameter descriptions of CREATE TABLE AS SELECT

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

OBS bucket name

tbl_path

Storage location of the Delta table in the OBS bucket

using

Parameter delta, defines and creates the Delta table

table_comment

Description of the table

location_path

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

query_statement

SELECT statement

Required Permissions

  • SQL permissions
    Table 2 Permissions required for executing CREATE TABLE AS SELECT

    Permission Description

    CREATE_TABLE permission on the database

    SELECT permission to query a table
  • Fine-grained permissions: dli:database:createTable and dli:table:select
  • Metadata services provided by LakeFormation. Refer to the LakeFormation documentation for details on permission configuration.

Examples

  • Create a partitioned table. 
    create table if not exists delta_table0
using delta
partitioned by (dt)
location 'obs://bucket_name0/db0/delta_table0'
as
select 1 as id, 'a1' as name, 10 as price, 1000 as dt;
  • Create a non-partitioned table. 
    create table if not exists delta_table0
using delta
location 'obs://bucket_name0/db0/delta_table0'
as
select 1 as id, 'a1' as name, 10 as price;

create table delta.`obs://bucket_name0/db0/delta_table0`
using delta
partitioned by (part_col1, part_col2)
as select id,name,year,class_name from table1 where part_col1=2024;
Parent topic: Delta DML Syntax