概要

CREATE TABLE [ IF NOT EXISTS ] table_name [ ( column_alias, ... ) ]
[ COMMENT table_comment ]
[ WITH ( property_name = expression [, ...] ) ]
AS query
[ WITH [ NO ] DATA ]

描述

创建新表,表中的数据来自SELECT子句。

  • 使用IF NOT EXISTS子句可以避免在表存在时抛出异常;

  • 使用WITH子句可以在创建表格时,为表格设置属性。支持的属性列表可以通过下列语句获取:

    SELECT * FROM system.metadata.table_properties;

示例

--- 从表orders中选择两个列来创建新的表
CREATE TABLE orders_column_aliased (order_date, total_price)
AS
SELECT orderdate, totalprice
FROM orders

--- 创建新表,使用聚合函数
CREATE TABLE orders_by_date
COMMENT 'Summary of orders by date'
WITH (format = 'ORC')
AS
SELECT orderdate, sum(totalprice) AS price
FROM orders
GROUP BY orderdate

--- 创建新表,使用`IF NOT EXISTS`子句
CREATE TABLE IF NOT EXISTS orders_by_date AS
SELECT orderdate, sum(totalprice) AS price
FROM orders
GROUP BY orderdate

--- 创建新表,schema和表nation一样,但是没有数据
CREATE TABLE empty_nation AS
SELECT *
FROM nation
WITH NO DATA