Debug a Hive insert query

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.

Debug a failing Hive INSERT into a partitioned transactional table

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.

Table fact_orders

column	type	constraints
order_id	BIGINT
customer_id	BIGINT
order_timestamp	TIMESTAMP
total_amount	DECIMAL(10,2)
status	TEXT
country	TEXT
order_date	TEXT

Table staging_orders

column	type	constraints
staging_order_id	BIGINT
staging_customer_id	BIGINT
order_ts	TEXT
order_total	TEXT
status	TEXT
country	TEXT
order_date	TEXT

Sample data:

country	order_date	order_total	order_ts	staging_customer_id	staging_order_id	status
US	2025-05-30	59.99	2025-05-30 10:15:00	1	101	PAID
CA	2025-05-31	120.50	2025-05-31 09:00:00	2	102	PENDING
US	2025-06-01	15.00	2025-06-01 12:30:00	1	103	CANCELLED

Expected output

Contents of fact_orders after executing the corrected INSERT ... SELECT once, starting from an empty fact_orders table. The rows are partitioned by order_date, but shown here as a single logical result set.

country	customer_id	order_date	order_id	order_timestamp	status	total_amount
US	1	2025-05-30	101	2025-05-30 10:15:00	PAID	59.99
CA	2	2025-05-31	102	2025-05-31 09:00:00	PENDING	120.5
US	1	2025-06-01	103	2025-06-01 12:30:00	CANCELLED	15

Hints

1. 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(...).
2. 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.