CREATE TABLE 语句

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
  (col_name data_type [constraint_specification] [COMMENT 'col_comment']    [, ...]  
)
  [PARTITIONED BY SPEC partition_clause]
  [SORT BY ([column [, column ...]])]
  [COMMENT 'table_comment']

constraint_specification:
    PRIMARY KEY (col_name, ...)

partition_clause:
      column_name                                     -- 仅分区
    | BUCKET(bucket_number, column_name)              -- 分桶

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
  [PRIMARY KEY (col_name [, ...])]
  [PARTITIONED BY SPEC partition_clause]
  [SORT BY ([column [, column ...]])]
  [COMMENT 'table_comment']
AS
  select_statement

-- 准备源表数据
CREATE TABLE t1 (x INT, y STRING);
INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');

-- 1. 克隆所有列和数据 (全量复制)
CREATE TABLE clone_of_t1 AS 
    SELECT * FROM t1;

-- 2. 按条件复制行 (创建子集)
CREATE TABLE subset_of_t1 AS 
    SELECT * FROM t1 WHERE x >= 2;

-- 3. 只克隆表结构，不复制数据 (空表克隆)
CREATE TABLE empty_clone_of_t1 AS 
    SELECT * FROM t1 WHERE 1=0;

-- 4. 重新组织、重命名列并转换数据
CREATE TABLE t5 AS 
    SELECT 
        upper(y) AS s,                   -- 数据转换和重命名
        x+1 AS a,                        -- 表达式计算
        'Entirely new column' AS n       -- 插入新的常量列
    FROM t1;
SELECT * FROM t5;
/*
+-------+---+---------------------+
| s     | a | n                   |
+-------+---+---------------------+
| ONE   | 2 | Entirely new column |
| TWO   | 3 | Entirely new column |
| THREE | 4 | Entirely new column |
+-------+---+---------------------+
*/

-- 5. 基于源数据创建分区表
CREATE TABLE partitions_no (year smallint, month tinyint, s string);
INSERT INTO partitions_no VALUES 
  (2016, 1, 'January 2016'),
  (2016, 2, 'February 2016'), 
  (2016, 3, 'March 2016');

-- 创建一个新表，并使用源表中的 year 和 month 列作为分区键
CREATE TABLE partitions_yes PARTITIONED BY SPEC (year, month)
  AS SELECT s, year, month FROM partitions_no;

CREATE TABLE [IF NOT EXISTS] [target_db_name.]target_table_name
LIKE [source_db_name.]source_table_name
[COMMENT 'table_comment'];

-- 1. 创建源表，包含主键、分区和排序属性
CREATE TABLE test_source(c1 int, c2 string, PRIMARY KEY(c1)) 
    PARTITIONED BY SPEC(c2) 
    SORT BY(c1);

-- 2. 使用 LIKE 创建一个空表 test2，继承 test_source 的所有结构属性，并指定一个新的注释
CREATE TABLE test2 LIKE test_source 
    COMMENT 'This is a clone of the structure of test_source.';

CREATE EXTERNAL TABLE [IF NOT EXISTS] [db_name.]table_name
  (col_name data_type [COMMENT 'col_comment']    [, ...]  
)
  [PARTITIONED BY partition_clause]
  [SORT BY ([column [, column ...]])]
  [COMMENT 'table_comment']
  [ROW FORMAT row_format]
  STORED AS file_format
  LOCATION 'table_path'
  
partition_clause
  (col_name data_type, [, ...])

row_format:
    DELIMITED [FIELDS TERMINATED BY 'char' [ESCAPED BY 'char']] [LINES TERMINATED BY 'char']

file_format:
    PARQUET
  | TEXTFILE
  

TINYINT,SMALLINT,INT,BIGINT,BOOLEAN,FLOAT,DOUBLE,DECIMAL,STRING,CHAR,VARCHAR,TIMESTAMP
1,100,1000,1000000,true,3.14,3.1415926535,123.456,Hello World,A,Short Text,2023-10-10 12:34:56
2,200,2000,2000000,false,2.71,2.7182818284,654.321,Data Lake,B,Longer Text,2023-10-11 13:45:00
3,300,3000,3000000,true,1.23,1.2345678901,789.123,Big Data,C,Very Long Text,2023-10-12 14:56:30

CREATE EXTERNAL TABLE text_t (a TINYINT,b SMALLINT, c INT, d BIGINT,e BOOLEAN,f FLOAT,g DOUBLE,h DECIMAL(10,4),i STRING,j CHAR(30),k VARCHAR(30),l TIMESTAMP)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS textfile LOCATION 'cosn://douglaspeng-cq-1375622477/shawbozhang_external_table/text_t'
TBLPROPERTIES ('skip.header.line.count'='1');

CREATE EXTERNAL TABLE [IF NOT EXISTS] [db_name.]table_name
  [COMMENT 'table_comment']
  STORED AS ICEBERG
  [LOCATION 'table_path']
  [TBLPROPERTIES ('key1'='value1', 'key2'='value2', ...)]

CREATE EXTERNAL TABLE ice_hadoop_tbl
STORED AS ICEBERG
LOCATION '/path/to/table'
TBLPROPERTIES('iceberg.catalog'='hadoop.tables');

CREATE EXTERNAL TABLE ice_hadoop_cat
STORED AS ICEBERG
TBLPROPERTIES('iceberg.catalog'='hadoop.catalog',
'iceberg.catalog_location'='/path/to/catalog',
'iceberg.table_identifier'='namespace.table');

CREATE EXTERNAL TABLE ice_hive_cat STORED AS ICEBERG
TBLPROPERTIES ('iceberg.catalog'='hive.catalog',
  'hive.metastore.uris'='thrift://127.0.0.1:9083',
  'metastore.catalog.default'='hive',
  'fs.defaultFS'='cosn://cos_bucket',
  'iceberg.table_identifier'='namespace.table');