Apache Hive和Snowflake的`CREATE TABLE`语法和功能特性整理的对比表

写一个Apache Hive中CREATE TABLE语句转换为对应Snowflake中CREATE TABLE语句的程序，现在需要一个根据功能的相似性对应的Apache HiveQL和Snowflake SQL的CREATE TABLE语句的表。

以下是Apache Hive中CREATE TABLE语句的合法实例及其功能说明，涵盖所有主要语法规则和参数组合：

1. 基本内部表

CREATE TABLE employees (
  id INT COMMENT 'Employee ID',
  name STRING,
  salary FLOAT
)
COMMENT 'Employee details'
STORED AS ORC;

功能：创建一个以ORC格式存储的内部表，包含ID、姓名和薪水字段，附带表和列注释。

2. 外部表指定存储位置

CREATE EXTERNAL TABLE logs (
  log_date STRING,
  content STRING
)
PARTITIONED BY (region STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION '/user/hive/external/logs';

功能：创建外部表，按region分区，数据存储在HDFS路径，字段以制表符分隔。

3. 分桶排序表

CREATE TABLE users (
  user_id INT,
  username STRING,
  signup_date DATE
)
CLUSTERED BY (user_id) INTO 4 BUCKETS
SORTED BY (signup_date DESC)
STORED AS PARQUET;

功能：创建按user_id分4个桶并按注册日期降序排序的Parquet表，优化查询性能。

4. 自定义SerDe和存储格式

CREATE TABLE apache_logs (
  host STRING,
  identity STRING,
  user STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "^([^ ]*) ([^ ]*) ([^ ]*)"
)
STORED AS TEXTFILE;

功能：使用正则表达式解析日志数据，自定义SerDe处理非结构化文本。

5. 事务表（ACID支持）

CREATE TABLE transactions (
  txn_id INT,
  amount DOUBLE,
  txn_time TIMESTAMP
)
CLUSTERED BY (txn_id) INTO 2 BUCKETS
STORED AS ORC
TBLPROPERTIES (
  'transactional'='true', 
  'orc.compress'='SNAPPY'
);

功能：支持ACID事务的ORC表，需分桶并设置事务属性，适用于高并发更新。

6. CTAS（Create Table As Select）

CREATE TABLE high_salary_employees
STORED AS ORC
AS
SELECT * FROM employees WHERE salary > 100000;

功能：通过查询结果创建新表，存储薪资超过100000的员工数据。

7. 复制表结构（LIKE）

CREATE TABLE employees_copy LIKE employees;

功能：复制employees表的结构（不复制数据），包括存储格式和属性。

8. 复杂数据格式（嵌套/集合类型）

CREATE TABLE web_data (
  url STRING,
  metrics MAP<STRING, FLOAT>,
  tags ARRAY<STRING>,
  time TIMESTAMP
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
STORED AS TEXTFILE;

功能：定义包含MAP和ARRAY复杂类型的表，指定字段、集合项和键的分隔符。

9. 指定表属性和注释

CREATE TABLE sensor_data (
  device_id STRING,
  reading INT,
  ts TIMESTAMP
)
COMMENT 'IoT Sensor Data'
TBLPROPERTIES (
  'creator'='analytics_team', 
  'created_at'='2023-01-01'
);

功能：添加表和列的元数据注释，自定义表属性便于元数据管理。

10. Hive约束（实验性功能）

CREATE TABLE orders (
  order_id INT PRIMARY KEY DISABLE NOVALIDATE,
  customer_id INT NOT NULL DISABLE,
  amount DECIMAL(10,2)
)
STORED AS ORC;

功能：定义主键和非空约束（需Hive 3+），需注意约束可能仅在Hive LLAP中生效。

11. 使用Hive默认分隔符

CREATE TABLE csv_data (
  col1 STRING,
  col2 INT,
  col3 BOOLEAN
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

功能：创建CSV格式文本表，字段逗号分隔，行以换行符结束。

12. 分区表动态插入

CREATE TABLE sales (
  product_id INT,
  quantity INT,
  sale_date DATE
)
PARTITIONED BY (country STRING, year INT)
STORED AS ORC;

功能：按country和year分区，便于按地区和时间快速查询销售数据。

13. 自定义输入输出格式

CREATE TABLE custom_format_table (
  key BINARY,
  value BINARY
)
STORED AS INPUTFORMAT 'com.example.CustomInputFormat'
OUTPUTFORMAT 'com.example.CustomOutputFormat';

功能：使用自定义InputFormat/OutputFormat处理特殊数据存储需求。

参数组合注意事项：

• STORED AS 与 ROW FORMAT 可能互斥（如指定STORED AS ORC时无需定义ROW FORMAT）。
• EXTERNAL 表需配合 LOCATION 使用，否则数据由Hive管理。
• 事务表必须满足：ORC格式 + 分桶 + TBLPROPERTIES(‘transactional’=‘true’)。
• CTAS 不支持直接指定分区或分桶，需后续通过ALTER TABLE添加。

以上示例覆盖了Hive CREATE TABLE的主要用法，实际使用时需根据数据特性和查询需求选择合适结构。

以下是基于Snowflake的CREATE TABLE语法规则构造的合法SQL语句示例及其功能说明：

1. 基础表创建

示例：

CREATE TABLE employees (
    emp_id INT,
    name VARCHAR(50),
    hire_date DATE
);

功能：创建一个包含emp_id（整数）、name（字符串）和hire_date（日期）的基本表。

2. 列默认值

示例：

CREATE TABLE orders (
    order_id INT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
    status VARCHAR(20) DEFAULT 'PENDING'
);

功能：插入数据时，若未指定order_date和status，则分别使用当前时间和默认值'PENDING'填充。

3. 约束声明（主键、外键、唯一键）

示例：

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) UNIQUE
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    dept_id INT REFERENCES departments(dept_id),
    email VARCHAR(100) UNIQUE
);

功能：

• departments表定义主键dept_id和唯一键dept_name。
• employees表定义主键emp_id、外键dept_id（引用departments表）和唯一邮箱。

4. 临时表与瞬态表

示例：

-- 临时表（会话结束时自动删除）
CREATE TEMPORARY TABLE temp_logs (
    log_id INT,
    message VARCHAR
);

-- 瞬态表（无时间旅行，需手动删除）
CREATE TRANSIENT TABLE sensitive_data (
    data_id INT,
    content VARCHAR
);

功能：

• temp_logs仅在当前会话中存在。
• sensitive_data不保留历史数据，减少存储开销。

5. 克隆表

示例：

-- 克隆表结构和数据
CREATE TABLE orders_backup CLONE orders;

-- 克隆表结构（不复制数据）
CREATE TABLE empty_orders CLONE orders;
TRUNCATE empty_orders; -- 清空数据

功能：快速复制表结构和数据（克隆后可选清空数据）。

6. 集群键优化查询

示例：

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount FLOAT
) CLUSTER BY (sale_date);

功能：按sale_date自动优化数据存储布局，提升时间范围查询性能。

7. 外部表（关联云存储）

示例：

CREATE EXTERNAL TABLE logs (
    log_id INT,
    log_time TIMESTAMP,
    details VARCHAR
) LOCATION = @s3_stage/logs/
  FILE_FORMAT = (TYPE = CSV);

功能：映射云存储（如S3）中的CSV文件为可查询的表，数据不存储在Snowflake中。

8. 注释与元数据

示例：

CREATE TABLE products (
    product_id INT COMMENT '唯一产品ID',
    name VARCHAR(100) COMMENT '产品名称'
) COMMENT = '存储所有产品信息';

功能：为表和列添加注释，提升元数据可读性。

9. 复制表结构（LIKE子句）

示例：

CREATE TABLE new_employees LIKE employees;

功能：复制employees表的列定义（不包含数据、约束或索引）。

10. 复杂数据类型（JSON、数组）

示例：

CREATE TABLE events (
    event_id INT,
    payload VARIANT,
    tags ARRAY
);

功能：存储半结构化数据（如JSON）和数组类型。

11. 自增列（IDENTITY）

示例：

CREATE TABLE sequences (
    id INT IDENTITY(1, 1),  -- 从1开始，步长为1
    value VARCHAR
);

功能：自动生成唯一递增的id值，适用于代理主键。

12. 非空约束

示例：

CREATE TABLE users (
    user_id INT NOT NULL,
    username VARCHAR(50) NOT NULL
);

功能：强制user_id和username列不允许插入空值。

13. 动态数据屏蔽（高级安全）

示例：

CREATE TABLE customer_data (
    email VARCHAR MASKING POLICY email_mask,
    ssn VARCHAR MASKING POLICY ssn_mask
);

功能：通过策略动态屏蔽敏感数据（需预定义掩码策略）。

14. 混合使用多种选项

示例：

CREATE TRANSIENT TABLE temp_analytics (
    id INT IDENTITY,
    event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
    data VARIANT
) CLUSTER BY (event_time)
  COMMENT = '临时分析数据';

功能：结合瞬态表、自增列、默认值、集群键和注释的综合用例。

注意事项：

• Snowflake不强制外键约束，需在应用层保证数据完整性。
• 临时表/瞬态表与标准表的存储成本和生命周期不同。
• 集群键适用于大表查询优化，但物理分区由Snowflake自动管理。

以下是根据Apache Hive和Snowflake的CREATE TABLE语法和功能特性整理的对比表。该表列出了Hive和Snowflake中CREATE TABLE语句的主要功能及其对应的实现方式，帮助你编写转换程序。

转换程序设计建议

1. 语法映射：

   • 将Hive的ROW FORMAT和STORED AS映射到Snowflake的FILE_FORMAT。
   • 将Hive的PARTITIONED BY映射到Snowflake的CLUSTER BY。
   • 将Hive的LOCATION映射到Snowflake的LOCATION。

2. 功能差异处理：

   • 对于Hive特有的功能（如分桶、SerDe、非空约束），需在转换时提示用户或忽略。
   • 对于Snowflake特有的功能（如IDENTITY列、动态数据屏蔽），可选择性添加到转换结果中。

3. 复杂类型处理：

   • 将Hive的MAP和ARRAY映射到Snowflake的VARIANT和ARRAY。
   • 对于嵌套结构，需解析Hive的STRUCT并映射到Snowflake的VARIANT。

4. 注释和元数据：

   • 将Hive的表和列注释直接映射到Snowflake的注释语法。

5. 外部表处理：

   • 将Hive的EXTERNAL TABLE映射到Snowflake的EXTERNAL TABLE，并调整路径和文件格式。

通过以上映射规则，可以编写一个程序自动将Hive的CREATE TABLE语句转换为Snowflake的CREATE TABLE语句，同时处理功能差异和语法变化。