CREATE TABLE
Function
CREATE TABLE is specific to DataArts Fabric SQL. It is used to create a table with a specified name on LakeFormation.
Precautions
- Columns appearing in PARTITIONED BY must not be described among the regular columns of the table. Partition columns are always positioned after the regular columns.
- The CLUSTERED BY syntax is applicable only to ORC and Parquet tables, and not to Hudi and Iceberg tables.
Syntax
1 2 3 4 5 6 7 8 9 |
CREATE [ EXTERNAL ] TABLE [ IF NOT EXISTS ] [schema_name.]table_name [ ( col_name col_type [ COMMENT col_comment ] [, ... ] ) ] [ COMMENT table_comment ] [ PARTITION BY ( col_name col_type COMMENT col_comment [, ... ] ) ] [ CLUSTERED BY (col_name [,...]) INTO ( bucket_num ) BUCKETS ] [ TABLEPROPERTIES ( option_key = option_value [, ... ] ) ] [ STORE AS table_format ] [ LOCATION table_path ] AS select_stmt; |
Parameter Description
- EXTERNAL
Specifying this keyword creates an EXTERNAL table. Omitting it defaults to creating a MANAGED table, where files, metadata, and statistical information are all managed by DataArts Fabric SQL.
- IF NOT EXISTS
Issues a notice rather than throwing an error if a table with the same name already exists.
- schema_name
The name of the database to which the table belongs. If no database name is specified, the table is created within the current_schema.
- table_name
The name of the table to be created, which should not exceed 63 characters in length.
- col_name
The name of the column to be created in the table, also limited to 63 characters. The combined total of regular and partition columns must not exceed 5,000.
- col_type
The data type of the column to be created, with supported ranges as follows:
Column Type
Can Be Declared As Partition Column
Supports ORC Format
Supports Parquet Format
Supports Iceberg Format
SmallInt
√
√
√
√
Int
√
√
√
√
BigInt
√
√
√
√
Float
×
√
√
√
Double
×
√
√
√
Decimal
√
√
√
√
Numeric
√
√
√
√
Timestamp
√
√
√
√
Date
√
√
√
√
Varchar
√
√
√
√
Char
√
√
√
√
Bool
×
√
√
√
Bytea
×
√
√
√
Text
√
√
√
√
- col_comment
Comment for the column, which can be any string.
- table_comment
Comment for the table, which can also be any string.
- bucket_num
Number of buckets.
- option_key = option_value
Setting parameters at the table level, with supported parameter ranges as follows:
Table 1 Value range of the option_key parameter option_key
option_value
Description
Applicable Scope
orc.compress
zlib, snappy, lz4
Compression method for ORC files.
ORC
parquet.compression
zlib, snappy, lz4
Compression method for Parquet files.
PARQUET
julian_adjust
true, false
Whether to convert to Julian date.
PARQUET
checkencoding
high, low, no
Whether to check character encoding.
ORC, PARQUET
column_index_access
true, false
Matching method between table-defined columns and file columns during reading. Defaults to true for column index matching, false for column name matching.
ORC, PARQUET
filesize
Certificates ranging from 1 to 1,024 bits
Size of external table files generated.
ORC, PARQUET
write.delete.mode
copy-on-write, merge-on-read
Mode setting for deletion: cow or mor.
Iceberg
write.update.mode
copy-on-write, merge-on-read
Mode setting for updates: cow or mor.
write.merge.mode
copy-on-write, merge-on-read
Mode setting for merges: cow or mor.
write.parquet.compression-codec
zstd, lz4, snappy, gzip,uncompressed
Compression method for Parquet files.
write.merge.isolation-level
snapshot, serializable
Isolation level for merge commands.
write.metadata.delete-after-commit.enabled
true, false
Controls whether the oldest tracked version metadata file is deleted after committing.
write.update.isolation-level
snapshot, serializable
Isolation level for update commands.
write.delete.isolation-level
snapshot, serializable
Isolation level for delete commands.
write.metadata.previous-versions-max
An integer greater than 0
Number of older metadata files to retain.
- table_format
Table storage formats, supporting ORC, PARQUET, and ICEBERG.
- table_path
Table storage path. The value must be a valid OBS path. OBS buckets and parallel file systems are supported. If the path is an OBS bucket path, the table is read-only. Otherwise, the table can be read and written. If the table to be created is a managed table, the table storage path cannot be specified. The table storage path is specified by the system as the path with the same name as the table name in the schema path, and the path must be empty during table creation.
- select_stmt
Query statement. External tables do not support CTAS statements.
- table_format
Examples
Create an ORC table.
1
|
CREATE TABLE test_table1 (a int, b bool, c text) store as orc; |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot