Hive外表（XIHE SQL）

语法

CREATE EXTERNAL TABLE [IF NOT EXISTS] <db>.<table> (
    <col1>  <type1>,
    <col2>  <type2>,
    ...
)
[PARTITIONED BY (<part_col1> <type1>[, <part_col2> <type2>, ...])]
[ROW FORMAT DELIMITED FIELDS TERMINATED BY '<delimiter>']
[ROW FORMAT SERDE '<serde_class>' [WITH SERDEPROPERTIES (...)]]
STORED AS <FORMAT>
LOCATION '<oss_path>'
[TBLPROPERTIES (
    '<key1>' = '<value1>',
    ...
)];

存储格式

压缩类型

通过TBLPROPERTIES指定写入时的压缩算法：

TBLPROPERTIES ('compress_type' = 'SNAPPY')

ROW FORMAT

TEXTFILE格式支持以下三种行格式定义方式：

OpenCSVSerde示例（要求所有列类型为STRING）：

CREATE EXTERNAL TABLE ext_db.csv_serde (
    col1 STRING,
    col2 STRING,
    col3 STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ('separatorChar' = ',', 'quoteChar' = '"')
STORED AS TEXTFILE
LOCATION 'oss://<YOUR-BUCKET>/data/csv_serde/';

示例

CREATE EXTERNAL DATABASE IF NOT EXISTS ext_hive_db;

CREATE EXTERNAL TABLE ext_hive_db.orders (
    order_id   BIGINT,
    user_id    BIGINT,
    status     VARCHAR(50),
    amount     DOUBLE,
    created_at TIMESTAMP
)
STORED AS PARQUET
LOCATION 'oss://<YOUR-BUCKET>/warehouse/ext_hive_db/orders/';

CREATE EXTERNAL TABLE ext_hive_db.access_log (
    id            BIGINT,
    ip            VARCHAR(50),
    path          VARCHAR(200),
    status_code   INT,
    response_time DOUBLE
)
STORED AS ORC
LOCATION 'oss://<YOUR-BUCKET>/warehouse/ext_hive_db/access_log/';

CREATE EXTERNAL TABLE ext_hive_db.csv_data (
    id     INT,
    name   VARCHAR(100),
    amount DOUBLE
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 'oss://<YOUR-BUCKET>/warehouse/ext_hive_db/csv_data/'
TBLPROPERTIES ('skip_header_line_count' = '1');

CREATE EXTERNAL TABLE ext_hive_db.json_logs (
    timestamp_col TIMESTAMP,
    level         VARCHAR(10),
    message       VARCHAR(1024)
)
STORED AS JSON
LOCATION 'oss://<YOUR-BUCKET>/warehouse/ext_hive_db/json_logs/';

CREATE EXTERNAL TABLE ext_hive_db.legacy_data (
    id     BIGINT,
    name   VARCHAR(100),
    value  DOUBLE
)
STORED AS RCFILE
LOCATION 'oss://<YOUR-BUCKET>/warehouse/ext_hive_db/legacy_data/';

-- ARRAY和MAP类型
CREATE EXTERNAL TABLE ext_hive_db.complex_types (
    id        BIGINT,
    tags      ARRAY<VARCHAR(100)>,
    attrs     MAP<STRING, STRING>
)
STORED AS PARQUET
LOCATION 'oss://<YOUR-BUCKET>/warehouse/ext_hive_db/complex_types/';

-- STRUCT类型
CREATE EXTERNAL TABLE ext_hive_db.struct_table (
    id    INT,
    info  STRUCT<name: STRING, age: INT, city: STRING>
)
STORED AS PARQUET
LOCATION 'oss://<YOUR-BUCKET>/warehouse/ext_hive_db/struct_table/';

-- 单级分区
CREATE EXTERNAL TABLE ext_hive_db.events (
    event_id   BIGINT,
    event_type VARCHAR(50),
    payload    VARCHAR(2048)
)
PARTITIONED BY (dt STRING)
STORED AS PARQUET
LOCATION 'oss://<YOUR-BUCKET>/warehouse/ext_hive_db/events/';

-- 多级分区
CREATE EXTERNAL TABLE ext_hive_db.events_multi (
    event_id   BIGINT,
    event_type VARCHAR(50)
)
PARTITIONED BY (dt STRING, region INT)
STORED AS PARQUET
LOCATION 'oss://<YOUR-BUCKET>/warehouse/ext_hive_db/events_multi/';

INSERT INTO（追加写入）

INSERT INTO支持从VALUES插入数据和从其他表插入数据两种方式。

-- 从VALUES插入
INSERT INTO ext_hive_db.orders
SELECT * FROM VALUES
    ROW(1001, 501, 'paid', 299.90, TIMESTAMP '2026-06-11 10:00:00'),
    ROW(1002, 502, 'pending', 158.00, TIMESTAMP '2026-06-11 10:05:00'),
    ROW(1003, 503, 'shipped', 450.00, TIMESTAMP '2026-06-12 10:10:00');

-- 从其他表插入
INSERT INTO ext_hive_db.orders
SELECT * FROM internal_db.source_table
WHERE created_at >= TIMESTAMP '2026-06-01 00:00:00';

-- 分区表写入（分区列作为SELECT最后的列）
INSERT INTO ext_hive_db.events
SELECT 1, 'click', '{"page":"home"}', '2026-06-11'
UNION ALL
SELECT 2, 'view', '{"page":"product"}', '2026-06-12';

INSERT OVERWRITE（覆写）

INSERT OVERWRITE支持非分区表全表覆写和分区表动态分区覆写两种场景。

-- 非分区表：全表覆写
INSERT OVERWRITE ext_hive_db.orders
SELECT * FROM staging_db.new_orders;

-- 分区表：动态分区覆写（仅替换涉及的分区，其他分区不受影响）
INSERT OVERWRITE ext_hive_db.events
SELECT 10, 'purchase', '{"item":"laptop"}', '2026-06-11'
UNION ALL
SELECT 11, 'refund', '{"item":"phone"}', '2026-06-11';

分区表覆写后，仅dt='2026-06-11'分区的数据被替换，其他日期分区不受影响。

内表与外表互导

-- 将内表数据导出到OSS（Parquet格式）
INSERT INTO ext_hive_db.orders
SELECT * FROM internal_db.source_table
WHERE created_at >= TIMESTAMP '2026-06-01 00:00:00';

-- 将OSS外表数据导入内表
INSERT INTO internal_db.target_table
SELECT * FROM ext_hive_db.orders
WHERE status = 'paid';

查询能力

基本查询与分区裁剪

-- 条件过滤
SELECT order_id, status, amount
FROM ext_hive_db.orders
WHERE status = 'paid' AND amount > 100
ORDER BY amount DESC
LIMIT 10;

-- 聚合查询
SELECT status, COUNT(*) AS cnt, SUM(amount) AS total
FROM ext_hive_db.orders
GROUP BY status;

-- 分区裁剪：WHERE条件中包含分区列时，引擎自动跳过不相关分区目录
SELECT * FROM ext_hive_db.events WHERE dt = '2026-06-11';

SELECT dt, COUNT(*) AS cnt
FROM ext_hive_db.events
WHERE dt >= '2026-06-01' AND dt < '2026-07-01'
GROUP BY dt;

JOIN查询

-- 外表与外表JOIN
SELECT o.order_id, o.amount, e.event_type
FROM ext_hive_db.orders o
JOIN ext_hive_db.events e ON o.order_id = e.event_id
WHERE e.dt = '2026-06-11';

-- 外表与内表JOIN
SELECT e.order_id, e.amount, u.user_name
FROM ext_hive_db.orders e
JOIN internal_db.users u ON e.user_id = u.user_id;

-- 自JOIN
SELECT a.order_id, a.status, b.order_id AS related_id
FROM ext_hive_db.orders a
INNER JOIN ext_hive_db.orders b ON a.user_id = b.user_id
WHERE a.order_id != b.order_id;

UNION查询

-- 合并多个外表的数据
SELECT order_id, amount, status FROM ext_hive_db.orders_2025
UNION ALL
SELECT order_id, amount, status FROM ext_hive_db.orders_2026
ORDER BY order_id;

谓词下推

Parquet和ORC格式支持谓词下推，引擎利用文件内部的统计信息（min/max/null count）跳过不满足条件的数据块，减少I/O扫描量。TEXTFILE和JSON格式不支持谓词下推，查询时为全表扫描。

基本视图

-- 创建视图
CREATE VIEW ext_hive_db.paid_orders AS
SELECT order_id, user_id, amount
FROM ext_hive_db.orders
WHERE status = 'paid';

-- 查询视图
SELECT * FROM ext_hive_db.paid_orders WHERE amount > 100;

-- 删除视图
DROP VIEW IF EXISTS ext_hive_db.paid_orders;

嵌套视图

-- 视图引用视图
CREATE VIEW ext_hive_db.base_view AS
SELECT * FROM ext_hive_db.orders;

CREATE VIEW ext_hive_db.union_view AS
SELECT * FROM ext_hive_db.base_view
UNION ALL
SELECT * FROM ext_hive_db.orders;

SELECT COUNT(*) FROM ext_hive_db.union_view;

CTE（公共表表达式）

WITH order_summary AS (
    SELECT dt, COUNT(*) AS cnt, SUM(amount) AS total
    FROM ext_hive_db.events
    GROUP BY dt
)
SELECT * FROM order_summary WHERE total > 10000;

功能概览

分区目录结构要求

OSS上的目录结构必须遵循Hive风格key=value/命名。分区列的值从目录名推导，数据文件中不包含分区列。

oss://<bucket>/data/orders/
├── dt=20260527/
│   ├── region=1/
│   │   └── data.parquet
│   └── region=2/
│       └── data.parquet
└── dt=20260528/
    └── region=1/
        └── data.parquet

对应建表语句：

CREATE EXTERNAL TABLE ext_db.orders (id INT, name VARCHAR(100))
PARTITIONED BY (dt STRING, region INT)
STORED AS PARQUET
LOCATION 'oss://<bucket>/data/orders/';

分区发现（MSCK REPAIR TABLE）

分区表创建后，需执行MSCK REPAIR TABLE扫描OSS目录结构，自动发现并注册符合key=value/命名规范的分区：

-- 全表分区修复
MSCK REPAIR TABLE ext_hive_db.events;

-- 指定子目录增量修复（分区数量多时推荐）
MSCK REPAIR TABLE ext_hive_db.events sync_dir 'oss://<YOUR-BUCKET>/warehouse/ext_hive_db/events/dt=2026-06-11/';

删除和查看分区

-- 查看已注册的分区（单级分区表）
SHOW PARTITIONS ext_hive_db.events;

-- 查看已注册的分区（多级分区表）
SHOW PARTITIONS ext_hive_db.events_multi;

-- 删除单级分区（仅删除元数据，不删除OSS文件）
ALTER TABLE ext_hive_db.events DROP PARTITION (dt = '2026-06-11');

-- 删除多级分区
ALTER TABLE ext_hive_db.events_multi DROP PARTITION (dt = '20260527', region = 1);

-- 删除后可通过MSCK REPAIR恢复（如果OSS上文件仍在）
MSCK REPAIR TABLE ext_hive_db.events;

启用分区投影

要启用分区投影，需在建表语句的TBLPROPERTIES中完成以下设置：

• 设置'projection.enabled' = 'true'启用分区投影（必需）。

• 通过'projection.<分区列名>.type' = '<类型>'指定各分区列的投影类型。

• 根据投影类型，设置对应的参数（如values、range、miss等）。

投影类型

示例

CREATE EXTERNAL TABLE ext_hive_db.fs_projected (
    a INT,
    b INT
)
PARTITIONED BY (c INT, d INT)
STORED AS PARQUET
LOCATION 'oss://<YOUR-BUCKET>/data/fs_projected/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.c.type' = 'injected',
    'projection.c.miss' = 'LIST',
    'projection.d.type' = 'injected',
    'projection.d.miss' = 'LIST'
);

CREATE EXTERNAL TABLE ext_hive_db.enum_only (
    a INT,
    b INT
)
PARTITIONED BY (region INT)
STORED AS PARQUET
LOCATION 'oss://<YOUR-BUCKET>/data/enum_only/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.region.type' = 'enum',
    'projection.region.values' = '1,2,3,4'
);

CREATE EXTERNAL TABLE ext_hive_db.mixed_projected (
    a INT,
    b INT
)
PARTITIONED BY (c INT, d INT)
STORED AS PARQUET
LOCATION 'oss://<YOUR-BUCKET>/data/mixed_projected/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.c.type' = 'enum',
    'projection.c.values' = '1,2',
    'projection.d.type' = 'injected',
    'projection.d.miss' = 'LIST'
);

使用建议

功能概览

语法

-- 收集表级统计信息
ANALYZE TABLE <table_name>;

-- 收集指定列的基本统计信息（NDV、NULL比率、min/max等）
ANALYZE TABLE <table_name> UPDATE BASIC ON `<col1>`, `<col2>`;

-- 收集指定列的采样统计信息
ANALYZE TABLE <table_name> UPDATE SAMPLED_BASIC ON `<col1>`;

-- 收集指定列的直方图
ANALYZE TABLE <table_name> UPDATE HISTOGRAM ON `<col1>`, `<col2>`;

-- 仅分析指定分区的统计信息
ANALYZE TABLE <table_name> WITH PARTITIONS = ARRAY[ARRAY['<part_val1>', <part_val2>]];

示例

-- 收集全局统计信息
ANALYZE TABLE ext_hive_db.orders;

-- 收集id和amount列的基本统计
ANALYZE TABLE ext_hive_db.orders UPDATE BASIC ON `order_id`, `amount`;

-- 收集amount列的直方图
ANALYZE TABLE ext_hive_db.orders UPDATE HISTOGRAM ON `amount`;

文件格式选择

分区设计

写入优化

查询优化

DDL限制

DML限制

数据类型限制

• AVRO格式不支持TINYINT等类型。

• CHAR(N)部分场景存在兼容性问题，建议使用VARCHAR替代。

• STRUCT类型按名称匹配，表定义中的属性可以是底层文件struct属性的子集，不要求严格对齐。

分区限制

• 分区列不在数据文件中，分区值从目录名推导（如dt=20260527/ → dt = '20260527'）。

• 不支持ALTER TABLE ADD PARTITION手动添加分区，请使用MSCK REPAIR TABLE。

• 分区目录结构必须遵循Hive风格key=value/命名规范。

• DROP PARTITION指定的分区列名必须与建表时定义的分区列名匹配，否则：

  • 若开启INTERCEPT_DROP_NOT_EXIST_PARTITION_COLUMN配置，会拦截并报错；

  • 若未开启，可能误删或无效操作。

• 分区列支持的类型：STRING、INT、BIGINT、BOOLEAN、DATE等。

统计信息限制

• 不支持ANALYZE TABLE ... FOR ALL COLUMNS语法。

• 建议在大批量写入后执行ANALYZE TABLE更新统计信息。

• 增量写入场景下，写入时自动收集统计信息可通过配置开关o_cbo_collect_stats_when_import控制。

查询限制

• 分区裁剪：WHERE条件中包含分区列时自动生效，无需Hint。

• 谓词下推：Parquet/ORC格式支持利用文件级min/max统计跳过不匹配的row group。

• TextFile/JSON不支持谓词下推，全表扫描性能不如列式格式。

• 建议查询时带上分区条件，避免全表扫描大量OSS文件。

其他限制

• DROP TABLE仅删除外部表定义，不删除OSS上的数据文件。

• SQL中不支持传入AK/SK，所有鉴权由实例级配置完成。

• LOCATION指向的OSS路径必须已存在（Hive外表不会自动创建目录）。

• 数据文件格式必须与建表时STORED AS声明一致，否则查询报错。

• TEXTFILE和JSON格式不支持谓词下推，全表扫描性能不如Parquet和ORC。

• 跨Region访问OSS取决于实例级配置。