-- =====================================================================
-- @Name:             HIVE-D-SQL-{表名}-CREATE
-- @Version:          1.0
-- @Desc:             Hive 建表模板（内部表/外部表/分区/分桶）
-- @TargetDatabase:   Hive
-- =====================================================================

-- ============================================================================
-- 场景1：内部表（Managed Table）
-- ============================================================================
-- 适用：Hive 管理数据和元数据，DROP TABLE 时数据一并删除

CREATE TABLE IF NOT EXISTS db_name.managed_table (
    id              BIGINT          COMMENT '主键ID',
    name            STRING          COMMENT '名称',
    category        STRING          COMMENT '类别',
    amount          DECIMAL(18,2)   COMMENT '金额',
    status          STRING          COMMENT '状态',
    created_at      TIMESTAMP       COMMENT '创建时间',
    updated_at      TIMESTAMP       COMMENT '更新时间',
    etl_time        TIMESTAMP       COMMENT '数据加工时间',
    etl_remark      STRING          COMMENT '备注信息'
)
COMMENT '内部表示例'
STORED AS ORC;                        -- 推荐存储格式

-- ============================================================================
-- 场景2：外部表（External Table）
-- ============================================================================
-- 适用：数据由外部系统管理，DROP TABLE 只删元数据不删数据

CREATE EXTERNAL TABLE IF NOT EXISTS db_name.external_table (
    id              BIGINT          COMMENT '主键ID',
    user_id         STRING          COMMENT '用户ID',
    action          STRING          COMMENT '操作类型',
    page_url        STRING          COMMENT '页面URL',
    ip_address      STRING          COMMENT 'IP地址',
    event_time      TIMESTAMP       COMMENT '事件时间'
)
COMMENT '日志外部表'
ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '\t'
    LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/data/external/logs/';

-- ============================================================================
-- 场景3：分区表（单分区）
-- ============================================================================

CREATE TABLE IF NOT EXISTS db_name.partitioned_table (
    id              BIGINT          COMMENT '主键ID',
    user_id         STRING          COMMENT '用户ID',
    user_name       STRING          COMMENT '用户姓名',
    order_count     BIGINT          COMMENT '订单数',
    total_amount    DECIMAL(18,2)   COMMENT '总金额',
    department      STRING          COMMENT '部门',
    region          STRING          COMMENT '地区',
    etl_time        TIMESTAMP       COMMENT '数据加工时间'
)
COMMENT '按日分区的统计表'
PARTITIONED BY (day_id STRING COMMENT '统计日期，格式yyyy-MM-dd')
STORED AS ORC;

-- ============================================================================
-- 场景4：多分区字段
-- ============================================================================

CREATE TABLE IF NOT EXISTS db_name.multi_partition_table (
    id              BIGINT          COMMENT '主键ID',
    name            STRING          COMMENT '名称',
    amount          DECIMAL(18,2)   COMMENT '金额',
    etl_time        TIMESTAMP       COMMENT '数据加工时间'
)
COMMENT '多分区字段示例表'
PARTITIONED BY (
    year_id         STRING          COMMENT '年份',
    month_id        STRING          COMMENT '月份'
)
STORED AS ORC;

-- ============================================================================
-- 场景5：分桶表
-- ============================================================================

CREATE TABLE IF NOT EXISTS db_name.bucketed_table (
    id              BIGINT          COMMENT '主键ID',
    user_id         BIGINT          COMMENT '用户ID',
    user_name       STRING          COMMENT '用户姓名',
    amount          DECIMAL(18,2)   COMMENT '金额'
)
COMMENT '分桶表示例'
PARTITIONED BY (day_id STRING)
CLUSTERED BY (user_id)              -- 分桶列
SORTED BY (amount DESC)             -- 桶内排序
INTO 16 BUCKETS                     -- 桶数量
STORED AS ORC;

-- ============================================================================
-- 场景6：ORC 格式 + 表属性
-- ============================================================================

CREATE TABLE IF NOT EXISTS db_name.orc_table (
    id              BIGINT          COMMENT '主键ID',
    name            STRING          COMMENT '名称',
    amount          DECIMAL(18,2)   COMMENT '金额',
    etl_time        TIMESTAMP       COMMENT '数据加工时间'
)
COMMENT 'ORC格式带属性配置'
PARTITIONED BY (day_id STRING)
STORED AS ORC
TBLPROPERTIES (
    'orc.compress' = 'SNAPPY',                      -- 压缩格式
    'orc.create.index' = 'true',                    -- 创建索引
    'transactional' = 'false'                       -- 非事务表
);

-- ============================================================================
-- 场景7：Parquet 格式
-- ============================================================================

CREATE TABLE IF NOT EXISTS db_name.parquet_table (
    id              BIGINT          COMMENT '主键ID',
    name            STRING          COMMENT '名称',
    amount          DECIMAL(18,2)   COMMENT '金额',
    tags            ARRAY<STRING>   COMMENT '标签数组',
    props           MAP<STRING,STRING> COMMENT '属性映射'
)
COMMENT 'Parquet格式表示例'
PARTITIONED BY (day_id STRING)
STORED AS PARQUET
TBLPROPERTIES (
    'parquet.compression' = 'SNAPPY'
);

-- ============================================================================
-- 场景8：临时表
-- ============================================================================

-- 会话级临时表（会话结束自动删除）
CREATE TEMPORARY TABLE tmp_processing (
    id              BIGINT,
    name            STRING,
    amount          DECIMAL(18,2)
);

-- CTAS 快速创建临时表
CREATE TEMPORARY TABLE tmp_source AS
SELECT id, name, amount
FROM source_table
WHERE day_id = '${day_id}';

-- ============================================================================
-- 字段类型速查
-- ============================================================================
/*
| 类型              | 说明           | 适用场景               |
|-------------------|----------------|------------------------|
| TINYINT           | 1字节整数      | 小范围数值             |
| SMALLINT          | 2字节整数      | 小范围数值             |
| INT               | 4字节整数      | 数量、等级             |
| BIGINT            | 8字节整数      | ID、计数               |
| FLOAT             | 4字节浮点      | 近似计算               |
| DOUBLE            | 8字节浮点      | 科学计算               |
| DECIMAL(p,s)      | 定点数         | 金额、精确数值         |
| BOOLEAN           | 布尔           | 状态标志               |
| STRING            | 变长字符串     | 名称、描述（最常用）   |
| VARCHAR(n)        | 变长字符串     | 限定长度字符串         |
| CHAR(n)           | 定长字符串     | 固定长度编码           |
| DATE              | 日期           | 日期字段               |
| TIMESTAMP         | 时间戳         | 时间字段               |
| BINARY            | 二进制         | 二进制数据             |
| ARRAY<type>       | 数组           | 多值字段               |
| MAP<k,v>          | 映射           | 属性字典               |
| STRUCT<f1:t1,...> | 结构体         | 嵌套结构               |
*/

-- ============================================================================
-- 建表规范说明
-- ============================================================================
/*
1. 内部表 vs 外部表
   - 内部表：Hive 管理数据，DROP 删数据和元数据
   - 外部表：外部管理数据，DROP 只删元数据
   - 生产推荐：原始数据用外部表，加工结果用内部表

2. 存储格式选择
   - ORC（推荐）：压缩好，列存储，支持谓词下推
   - PARQUET：跨平台兼容好，列存储
   - TEXTFILE：原始数据导入，性能最差

3. 分区设计
   - 按时间分区最常用（day_id, month_id）
   - 分区列不能出现在表定义的列中（Hive 特有）
   - 查询时分区列作为普通字段使用

4. 分桶设计
   - 选择高基数列做分桶列
   - 用于优化 JOIN（分桶列相同可做 map-side join）
   - 用于数据抽样（TABLESAMPLE）

5. 字段命名规范
   - snake_case 格式：user_id, total_amount
   - 主键：id 或 {业务}_id
   - 技术字段：etl_time, etl_remark
   - 分区字段：day_id, month_id, year_id

6. COMMENT 必须添加
   - 每个字段必须有 COMMENT
   - 表必须有 COMMENT
*/
