CREATE TABLE

Synopsis

CREATE TABLE [ IF NOT EXISTS ]
table_name (
  { column_name data_type [ COMMENT comment ] [ WITH ( property_name = expression [, ...] ) ]
  | LIKE existing_table_name [ { INCLUDING | EXCLUDING } PROPERTIES ] }
  [, ...]
)
[ COMMENT table_comment ]
[ WITH ( property_name = expression [, ...] ) ]

Description

Create a new, empty table with the specified columns. Use create-table-as to create a table with data.

The optional IF NOT EXISTS clause causes the error to be suppressed if the table already exists.

The optional WITH clause can be used to set properties on the newly created table or on single columns. To list all available table properties, run the following query:

SELECT * FROM system.metadata.table_properties

For example, to hive connector, below are some of available and frequently used table properties:

Property Namedata typeDescriptionDefault
formatvarcharHive storage format for the table. Possible values: [ORC, PARQUET, AVRO, RCBINARY, RCTEXT, SEQUENCEFILE, JSON, TEXTFILE, CSV]ORC
bucket_countintegerNumber of buckets
bucketed_byarray(varchar)Bucketing columns
sorted_byarray(varchar)Bucket sorting columns
externalbooleanIs the table an external tablefalse
locationvarcharFile system location URI for the table location value must be provided if external=true
partitioned_byarray(varchar)Partition columns
transactionalbooleanIs transactional property enabled There is a limitation that only ORC Storage format support creating an transactional tablefalse

To list all available column properties, run the following query:

SELECT * FROM system.metadata.column_properties

The LIKE clause can be used to include all the column definitions from an existing table in the new table. Multiple LIKE clauses may be specified, which allows copying the columns from multiple tables.

If INCLUDING PROPERTIES is specified, all of the table properties are copied to the new table. If the WITH clause specifies the same property name as one of the copied properties, the value from the WITH clause will be used. The default behavior is EXCLUDING PROPERTIES. The INCLUDING PROPERTIES option maybe specified for at most one table.

Examples

Create a new table orders:

CREATE TABLE orders (
  orderkey bigint,
  orderstatus varchar,
  totalprice double,
  orderdate date
)
WITH (format = 'ORC')

Create a new transactional table orders:

CREATE TABLE orders (
  orderkey bigint,
  orderstatus varchar,
  totalprice double,
  orderdate date
)
WITH (format = 'ORC',
transactional=true)

Create an external table orders:

CREATE TABLE orders (
  orderkey bigint,
  orderstatus varchar,
  totalprice double,
  orderdate date
)
WITH (format = 'ORC',
external=true,
location='hdfs://hdcluster/tmp/externaltbl')

Create the table orders if it does not already exist, adding a table comment and a column comment:

CREATE TABLE IF NOT EXISTS orders (
  orderkey bigint,
  orderstatus varchar,
  totalprice double COMMENT 'Price in cents.',
  orderdate date
)
COMMENT 'A table to keep track of orders.'

Create the table bigger_orders using the columns from orders plus additional columns at the start and end:

CREATE TABLE bigger_orders (
  another_orderkey bigint,
  LIKE orders,
  another_orderdate date
)

Limitations

Different connector might support different data type, and different table/column properties. See connector documentation for more details.

See Also

ALTER TABLE, DROP TABLE, CREATE TABLE AS, SHOW CREATE TABLE

有奖捉虫

“有虫”文档片段

0/500

存在的问题

文档存在风险与错误

● 拼写,格式,无效链接等错误;

● 技术原理、功能、规格等描述和软件不一致,存在错误;

● 原理图、架构图等存在错误;

● 版本号不匹配:文档版本或内容描述和实际软件不一致;

● 对重要数据或系统存在风险的操作,缺少安全提示;

● 排版不美观,影响阅读;

内容描述不清晰

● 描述存在歧义;

● 图形、表格、文字等晦涩难懂;

● 逻辑不清晰,该分类、分项、分步骤的没有给出;

内容获取有困难

● 很难通过搜索引擎,openLooKeng官网,相关博客找到所需内容;

示例代码有错误

● 命令、命令参数等错误;

● 命令无法执行或无法完成对应功能;

内容有缺失

● 关键步骤错误或缺失,无法指导用户完成任务,比如安装、配置、部署等;

● 逻辑不清晰,该分类、分项、分步骤的没有给出

● 图形、表格、文字等晦涩难懂

● 缺少必要的前提条件、注意事项等;

● 描述存在歧义

0/500

您对文档的总体满意度

非常不满意
非常满意

请问是什么原因让您参与到这个问题中

您的邮箱

创Issue赢奖品
根据您的反馈,会自动生成issue模板。您只需点击按钮,创建issue即可。
有奖捉虫