Hive external tables (XIHE SQL)

Syntax

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>']
STORED AS <FORMAT>
LOCATION '<oss_path>'
[TBLPROPERTIES (
    '<key1>' = '<value1>',
    ...
)];

Parquet external table example

-- Create an external database
CREATE EXTERNAL DATABASE IF NOT EXISTS ext_hive_db;

-- Create a Hive external table in the Parquet format
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/';

ORC external table example

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/';

CSV (TEXTFILE) external table example

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');

JSON external table example

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/';

Partitioned table example

-- Single-level partitioning
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/';

-- Multi-level partitioning
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/';

Other DDL operations

-- View the complete CREATE TABLE statement of a table
SHOW CREATE TABLE ext_hive_db.orders;

-- View the column structure
DESCRIBE ext_hive_db.orders;

-- Drop an external table
DROP TABLE IF EXISTS ext_hive_db.orders;

INSERT INTO (append)

Insert from VALUES:

-- Use the ROW() syntax to insert from 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 from other tables:

-- Insert from another table
INSERT INTO ext_hive_db.orders
SELECT * FROM staging_db.new_orders
WHERE created_at >= TIMESTAMP '2026-06-01 00:00:00';

Write data to a partitioned table (partition columns as the last columns in SELECT):

INSERT INTO ext_hive_db.events
SELECT * FROM VALUES
    ROW(1, 'click', '{"page":"home"}', '2026-06-11'),
    ROW(2, 'view', '{"page":"product"}', '2026-06-12');

INSERT OVERWRITE (overwrite)

Non-partitioned table: full table overwrite. All existing data is replaced.

INSERT OVERWRITE ext_hive_db.orders
SELECT * FROM staging_db.corrected_orders;

Partitioned table: dynamic partition overwrite. Only the partitions involved in the SELECT result are replaced. Data in other partitions is not affected.

-- Overwrite data of a specific date
INSERT OVERWRITE ext_hive_db.events
SELECT * FROM VALUES
    ROW(10, 'purchase', '{"item":"laptop"}', '2026-06-11'),
    ROW(11, 'refund', '{"item":"phone"}', '2026-06-11');

After execution, only the data in the dt='2026-06-11' partition is replaced. Data in other date partitions is not affected.

Import and export between internal and external tables

-- Export data from an internal table to OSS (Parquet format)
INSERT INTO ext_hive_db.orders
SELECT * FROM internal_db.source_table
WHERE created_at >= TIMESTAMP '2026-06-01 00:00:00';

-- Import data from an OSS external table to an internal table
INSERT INTO internal_db.target_table
SELECT * FROM ext_hive_db.orders
WHERE status = 'paid';

Basic queries

-- Count the number of rows
SELECT COUNT(*) FROM ext_hive_db.orders;

-- Filter by conditions
SELECT order_id, status, amount
FROM ext_hive_db.orders
WHERE status = 'paid' AND amount > 100
ORDER BY amount DESC
LIMIT 10;

-- Aggregation query
SELECT status, COUNT(*) AS cnt, SUM(amount) AS total
FROM ext_hive_db.orders
GROUP BY status;

Partition pruning

When you specify partition column values in the WHERE clause, the engine automatically prunes irrelevant partition directories to reduce the amount of data scanned:

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;

Predicate pushdown

The Parquet and ORC formats support predicate pushdown. The engine uses the internal statistics of files (min/max/null count) to skip data blocks that do not meet the conditions. The TEXTFILE and JSON formats do not support predicate pushdown.

JOIN queries

-- JOIN between external tables
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 between an external table and an internal table
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;

Partition discovery (MSCK REPAIR TABLE)

After a partitioned table is created, you must run the MSCK REPAIR TABLE statement to scan the OSS directory structure and automatically discover and register partitions that follow the key=value/ naming convention:

-- Full table partition repair
MSCK REPAIR TABLE ext_hive_db.events;

-- Incremental repair for a specific subdirectory (recommended when there are many partitions)
MSCK REPAIR TABLE ext_hive_db.events sync_dir 'oss://<YOUR-BUCKET>/warehouse/ext_hive_db/events/dt=2026-06-11/';

Drop and view partitions

-- View registered partitions
SHOW PARTITIONS ext_hive_db.events;

-- Drop a specific partition (removes metadata only, does not delete OSS files)
ALTER TABLE ext_hive_db.events DROP PARTITION (dt = '2026-06-11');

-- View partitions of a multi-level partitioned table
SHOW PARTITIONS ext_hive_db.events_multi;

-- Drop a partition of a multi-level partitioned table
ALTER TABLE ext_hive_db.events_multi DROP PARTITION (dt = '20260527', region = 1);

-- Restore the partition by running MSCK REPAIR after dropping it
MSCK REPAIR TABLE ext_hive_db.events;

Feature overview

Syntax

-- Collect table-level statistics
ANALYZE TABLE <table_name>;

-- Collect basic statistics for specific columns (NDV, NULL ratio, min/max)
ANALYZE TABLE <table_name> UPDATE BASIC ON `<col1>`, `<col2>`;

-- Collect sampled statistics for specific columns
ANALYZE TABLE <table_name> UPDATE SAMPLED_BASIC ON `<col1>`;

-- Collect histogram statistics for specific columns
ANALYZE TABLE <table_name> UPDATE HISTOGRAM ON `<col1>`, `<col2>`;

-- Collect statistics for specific partitions only
ANALYZE TABLE <table_name> WITH PARTITIONS = ARRAY[ARRAY['<part_val1>', <part_val2>]];

Examples

-- Collect global statistics
ANALYZE TABLE ext_hive_db.orders;

-- Collect basic statistics for the id and amount columns
ANALYZE TABLE ext_hive_db.orders UPDATE BASIC ON `order_id`, `amount`;

-- Collect histogram statistics for the amount column
ANALYZE TABLE ext_hive_db.orders UPDATE HISTOGRAM ON `amount`;

File format selection

Partition design

Write optimization

Query optimization

DDL limits

DML limits

Data type limits

• The AVRO format does not support TINYINT and some other data types.

• CHAR(N) has compatibility issues in some scenarios. We recommend that you use VARCHAR instead.

• STRUCT fields are matched by name. The attributes in the table definition can be a subset of the attributes in the underlying file, without requiring strict alignment.

Partition limits

• Partition columns are not stored in data files. Partition values are derived from directory names (for example, dt=20260527/ results in dt = '20260527').

• ALTER TABLE ADD PARTITION for manually adding partitions is not supported. Use MSCK REPAIR TABLE instead.

• The partition directory structure must follow the Hive-style key=value/ naming convention.

• The partition column names specified in DROP PARTITION must match the partition column names defined at table creation. Otherwise:

  • If the INTERCEPT_DROP_NOT_EXIST_PARTITION_COLUMN configuration is enabled, the operation is intercepted and an error is returned.

  • If not enabled, the operation may result in unintended deletion or a no-op.

• Supported partition column types: STRING, INT, BIGINT, BOOLEAN, DATE, and others.

Statistics limits

• The ANALYZE TABLE ... FOR ALL COLUMNS syntax is not supported.

• We recommend that you run ANALYZE TABLE to update statistics after large batch writes.

• For incremental write scenarios, auto-collection of statistics on write can be controlled by the o_cbo_collect_stats_when_import configuration.

Query limits

• Partition pruning: automatically takes effect when partition columns are included in the WHERE clause, without requiring a Hint.

• Predicate pushdown: Parquet/ORC formats support using file-level min/max statistics to skip row groups that do not match the conditions.

• TextFile/JSON do not support predicate pushdown. Full table scans for these formats are less performant than columnar formats.

• We recommend that you include partition conditions in queries to avoid full table scans of a large number of OSS files.

Other limits

• DROP TABLE only removes the external table definition. It does not delete the data files in OSS.

• Access keys (AK/SK) cannot be passed in SQL statements. All authentication is configured at the instance level.

• The OSS path specified by LOCATION must already exist. Hive external tables do not automatically create directories.

• The data file format must be consistent with the STORED AS declaration at table creation. Otherwise, queries return errors.

• TextFile and JSON formats do not support predicate pushdown. Full table scan performance is inferior to Parquet and ORC.

• Cross-region access to OSS depends on instance-level configuration.