CREATE TABLE AS SELECT

Function

This command creates a Hudi table by specifying a list of fields with table attributes. When using the metadata service provided by DLI, only foreign tables can be created, meaning you need to specify the table path through LOCATION.

Syntax

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

USING hudi

[ COMMENT table_comment ]

[ LOCATION location_path ]

[ OPTIONS (options_list) ]

[ AS query_statement ]

Parameter Description

Example

• Create a partitioned table.

  create table h2 using hudi
  options (type = 'cow', primaryKey = 'id', preCombineField = 'dt')
  partitioned by (dt)
  as
  select 1 as id, 'a1' as name, 10 as price, 1000 as dt;

• Create a non-partitioned table.

  create table h3 using hudi
  options (type = 'cow', primaryKey = 'id', preCombineField = 'dt')
  as
  select 1 as id, 'a1' as name, 10 as price, 1000 as dt;
  
  Load data from a Parquet table to the Hudi table.
  # Create a Parquet table.
  create table parquet_mngd using parquet options(path='obs://bucket/path/parquet_dataset/*.parquet');
  
  # Create a Hudi table by Creating a Table from Query Results (CTAS).
  create table hudi_tbl using hudi location 'obs://bucket/path/hudi_tbl/' options (
  type = 'cow',
  primaryKey = 'id',
  preCombineField = 'ts'
  )
  partitioned by (datestr) as select * from parquet_mngd;

Caveats

CTAS uses bulk insert to write data for improved loading performance.

Permission Requirements

Metadata service provided by DLI

• SQL permissions:

  database	table
  CREATE_TABLE	Source table: SELECT

• Fine-grained permissions: dli:table:createTable and dli:table:select

Metadata services provided by LakeFormation. Refer to the LakeFormation documentation for details on permission configuration.

System Response

The table is successfully created. The created Hudi table can be accessed by entering the DLI console, choosing Data Management > Databases and Tables from the left navigation pane, and then clicking the name of the database where the table is created.