嵌套JSON数据导入Hive并拆分为多行的技术问询

Hey there! Let's walk through how to import that nested JSON taxes array into Hive and split it into multiple rows properly. I’ve worked through similar nested JSON flattening tasks in Hive before, so here’s a step-by-step breakdown tailored to your scenario:

First, we need to define a table that maps to the nested structure of your JSON. Hive uses SerDe (Serializer/Deserializer) to parse JSON, so we’ll use the built-in JSON SerDe for this. Note: Hive doesn’t allow special characters like - in column names, so I’ve replaced tax-details with tax_details in the schema (adjust your JSON keys or schema if needed to match).

CREATE EXTERNAL TABLE IF NOT EXISTS tax_data (
    taxes ARRAY<STRUCT<
        line_id: INT,
        commodity_code: STRING,
        fee: STRUCT<
            amt: STRUCT<curr_code: STRING, value: DOUBLE>,
            type: STRING
        >,
        ship_addr: STRUCT<admin_area_1: STRING, country_code: STRING>,
        total_tax: STRUCT<curr_code: STRING, value: DOUBLE>,
        tax_details: ARRAY<STRUCT<
            exempt_option: BOOLEAN,
            auth_name: STRING,
            doc_amt: STRUCT<currency_code: STRING, value: DOUBLE>
        >>
    >>
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION '/path/to/your/json/files'; -- Replace with your actual HDFS path

Use LATERAL VIEW EXPLODE to break apart the taxes array—this will turn each element in the array into a separate row, while keeping the rest of the data aligned.

SELECT
    tax.line_id,
    tax.commodity_code,
    tax.fee.amt.curr_code AS fee_curr_code,
    tax.fee.amt.value AS fee_value,
    tax.fee.type AS fee_type,
    tax.ship_addr.admin_area_1 AS ship_admin_area,
    tax.ship_addr.country_code AS ship_country_code,
    tax.total_tax.curr_code AS total_tax_curr_code,
    tax.total_tax.value AS total_tax_value,
    tax.tax_details -- This is still an array; we'll split it next if needed
FROM
    tax_data
LATERAL VIEW EXPLODE(taxes) exploded_taxes AS tax;

If you need to flatten the inner tax_details array into individual rows too, just add a second LATERAL VIEW EXPLODE to the query. This will give you one row per tax detail entry, linked to its parent tax record.

SELECT
    tax.line_id,
    tax.commodity_code,
    tax.fee.amt.curr_code AS fee_curr_code,
    tax.fee.amt.value AS fee_value,
    tax.fee.type AS fee_type,
    tax.ship_addr.admin_area_1 AS ship_admin_area,
    tax.ship_addr.country_code AS ship_country_code,
    tax.total_tax.curr_code AS total_tax_curr_code,
    tax.total_tax.value AS total_tax_value,
    tax_detail.exempt_option,
    tax_detail.auth_name,
    tax_detail.doc_amt.currency_code AS doc_amt_curr_code,
    tax_detail.doc_amt.value AS doc_amt_value
FROM
    tax_data
LATERAL VIEW EXPLODE(taxes) exploded_taxes AS tax
LATERAL VIEW EXPLODE(tax.tax_details) exploded_tax_details AS tax_detail;

If you want to avoid running the explode logic every time, save the flattened result to a new table (Parquet is recommended for better performance and compression):

CREATE TABLE IF NOT EXISTS flattened_tax_data
STORED AS PARQUET
AS
SELECT
    tax.line_id,
    tax.commodity_code,
    tax.fee.amt.curr_code AS fee_curr_code,
    tax.fee.amt.value AS fee_value,
    tax.fee.type AS fee_type,
    tax.ship_addr.admin_area_1 AS ship_admin_area,
    tax.ship_addr.country_code AS ship_country_code,
    tax.total_tax.curr_code AS total_tax_curr_code,
    tax.total_tax.value AS total_tax_value,
    tax_detail.exempt_option,
    tax_detail.auth_name,
    tax_detail.doc_amt.currency_code AS doc_amt_curr_code,
    tax_detail.doc_amt.value AS doc_amt_value
FROM
    tax_data
LATERAL VIEW EXPLODE(taxes) exploded_taxes AS tax
LATERAL VIEW EXPLODE(tax.tax_details) exploded_tax_details AS tax_detail;

Quick Notes to Avoid Issues: #

• Data Types: I used DOUBLE for numeric values, but if you need precise decimal handling, swap it for DECIMAL(18,10) (adjust precision/scale to match your data).
• SerDe Alternatives: If the built-in JSON SerDe gives you trouble, try the OpenX JSON SerDe—you’ll just need to add the JAR first with ADD JAR /path/to/json-serde.jar;.
• Path Permissions: Make sure the Hive user has read access to the HDFS path where your JSON files are stored.

内容的提问来源于stack exchange，提问作者PK25