Debug a Hive insert query
Company: TikTok
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Given a Hive table schema and an incoming table plus an INSERT/SELECT statement meant to inject data, identify why the query fails and provide step-by-step fixes. Check column alignment and names, data types and casts, partition columns and values, dynamic partition settings, file formats/SerDe, ACID/transactional requirements, and any reserved keywords or syntax errors.
Overview: This question evaluates a candidate's ability to debug Hive INSERT/SELECT operations, focusing on schema and column alignment, data types and casts, partition handling, file formats/SerDe, ACID/transactional constraints, and syntax or reserved keyword issues.
You are given a transactional Hive fact table and a staging table that receives raw CSV data.
Target table (already created):
CREATE TABLE fact_orders (
order_id BIGINT,
customer_id BIGINT,
order_timestamp TIMESTAMP,
total_amount DECIMAL(10,2),
status STRING,
country STRING
)
PARTITIONED BY (order_date STRING)
STORED AS ORC
TBLPROPERTIES (
'transactional' = 'true'
);
Incoming staging table (text-based, non-transactional):
CREATE TABLE staging_orders (
staging_order_id BIGINT,
staging_customer_id BIGINT,
order_ts STRING, -- e.g. '2025-05-30 10:15:00'
order_total STRING, -- numeric value as text
status STRING,
country STRING,
order_date STRING -- e.g. '2025-05-30'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
An engineer wrote the following INSERT query to load data from staging_orders into fact_orders and enable dynamic partitioning, but it fails with errors about column mismatches and types:
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;
INSERT INTO TABLE fact_orders PARTITION (order_date)
SELECT
staging_order_id,
staging_customer_id,
order_ts,
order_total,
country,
status,
order_date
FROM staging_orders;
Your tasks:
1. Identify the issues in the failing INSERT, including column order and alignment with the partitioned table, data types and necessary casts, and any potential reserved keyword or syntax problems.
2. Consider that fact_orders is an ORC, transactional table and staging_orders is a text/SerDe table. Ensure your solution remains compatible with these settings and with dynamic partitions.
3. Write a corrected, working Hive INSERT ... SELECT statement (including any necessary SET statements) that successfully loads all rows from staging_orders into fact_orders, with correct column mapping, data types, and dynamic partitioning on order_date.
Assume there are no existing rows in fact_orders before your INSERT is executed. For the PostgreSQL validation harness, express the corrected load as an INSERT ... SELECT ... RETURNING equivalent over the provided tables.
Tables
fact_orders(order_id BIGINT, customer_id BIGINT, order_timestamp TIMESTAMP, total_amount DECIMAL(10,2), status TEXT, country TEXT, order_date TEXT)
staging_orders(staging_order_id BIGINT, staging_customer_id BIGINT, order_ts TEXT, order_total TEXT, status TEXT, country TEXT, order_date TEXT)
Hints
- In Hive, the SELECT list in an INSERT must match the target table: first all non-partition columns in order, then the partition columns, in the same order as in PARTITION(...).
- You must CAST string-based timestamp and numeric fields in staging_orders to TIMESTAMP and DECIMAL(10,2) to match fact_orders, and enable dynamic partitions with the appropriate SET parameters.