无法向Hive插入JSON数据，按指定Schema建表失败求助

Hey there! Let’s walk through the problems with your current setup and get your table working properly.

Key Issues in Your Original Query #

1. Conflicting SerDe and Storage Format

You’re using JsonSerDe but specifying STORED AS AVRO—these two don’t play together. AVRO requires its own dedicated SerDe, while JSON uses a text-based SerDe. Mixing them will cause Hive to throw errors immediately.

2. Case Mismatch Between Schema and Table Fields

Your source JSON schema uses uppercase field names (like MESSAGE_ID), but your table defines lowercase names (like message_id). JSON is case-sensitive, so Hive won’t map the fields correctly unless you account for this.

3. Missing Nullable Field Handling (For AVRO)

Your original schema has many fields marked as ["string", "null"] (nullable), but your table doesn’t explicitly align with this for AVRO. While Hive strings default to nullable, AVRO needs the schema definition to recognize null values properly.

Corrected Solutions (Choose Based on Your Data Format) #

Option 1: If Your Data is in AVRO Format

Use the AVRO SerDe and explicitly define the schema to match your source:

CREATE EXTERNAL TABLE governed_data.customer_order
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
LOCATION 'adl://rbsitbinsighstdlt001.azuredatalakestore.net/insights/governed_data/'
TBLPROPERTIES (
  'avro.schema.literal' = '{
    "type":"record",
    "name":"topLevelRecord",
    "fields":[
      {"name":"MESSAGE_ID","type":["string","null"]},
      {"name":"MSGNAME","type":["string","null"]},
      {"name":"SOURCE","type":["string","null"]},
      {"name":"EVENT_DATETIME","type":["string","null"]},
      {"name":"CUSTOMER_ORDER_ID","type":["string","null"]},
      {"name":"SP_ORGANISATION_NAME","type":["string","null"]},
      {"name":"CUSTOMER_ACCOUNT_ID","type":["string","null"]},
      {"name":"ORDER_TYPE_NAME","type":["string","null"]},
      {"name":"ORDER_SUBTYPE_NAME","type":["string","null"]},
      {"name":"ORDER_REASON_NAME","type":["string","null"]},
      {"name":"ORDER_CREATED_DATE","type":["string","null"]},
      {"name":"ORDER_CREATED_CHANNEL_NAME","type":["string","null"]},
      {"name":"ORDER_CREATED_RETAILER_ID","type":["string","null"]},
      {"name":"ORDER_CREATED_DEALER_ID","type":["string","null"]},
      {"name":"ORDER_CREATED_AFFILIATE_ID","type":["string","null"]},
      {"name":"ORDER_CREATED_EMPLOYEE_ID","type":["string","null"]},
      {"name":"ORDER_CREATED_CONTACT_CENTRE_AGENT_ID","type":["string","null"]},
      {"name":"ORDER_SUBMITTED_DATE","type":["string","null"]},
      {"name":"ORDER_SUBMITTED_CHANNEL_NAME","type":["string","null"]},
      {"name":"ORDER_DUE_DATE","type":["string","null"]},
      {"name":"ONE_TIME_CHARGE_AMT","type":["string","null"]},
      {"name":"RECURRING_CHARGE_AMT","type":["string","null"]},
      {"name":"ORDER_STATUS_NAME","type":["string","null"]},
      {"name":"ORDER_STATUS_CHANGE_REASON_NAME","type":["string","null"]},
      {"name":"CREATE_JOB_RUN_ID","type":"int"},
      {"name":"CREATE_DATE_TIME","type":"string"},
      {"name":"SYSTEM_ID","type":"int"},
      {"name":"SRC_FILE_NAME","type":"string"}
    ]
  }'
);

Option 2: If Your Data is in Line-by-Line JSON Format

Use the JsonSerDe, set storage to text file, and fix the case mismatch:

CREATE EXTERNAL TABLE governed_data.customer_order(
 message_id string,
 msgname string,
 source string,
 event_datetime string,
 customer_order_id string,
 sp_organisation_name string,
 customer_account_id string,
 order_type_name string,
 order_subtype_name string,
 order_reason_name string,
 order_created_date string,
 order_created_channel_name string,
 order_created_retailer_id string,
 order_created_dealer_id string,
 order_created_affiliate_id string,
 order_created_employee_id string,
 order_created_contact_centre_agent_id string,
 order_submitted_date string,
 order_submitted_channel_name string,
 order_due_date string,
 one_time_charge_amt string,
 recurring_charge_amt string,
 order_status_name string,
 order_status_change_reason_name string,
 create_job_run_id int,
 create_date_time string,
 system_id int,
 src_file_name string)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE
LOCATION 'adl://rbsitbinsighstdlt001.azuredatalakestore.net/insights/governed_data/'
TBLPROPERTIES (
  'hive.serde.json.lowercase.names' = 'true' -- Converts JSON uppercase keys to lowercase to match your table fields
);

Additional Troubleshooting Tips #

• Verify Path Permissions: Ensure Hive has read/write access to your ADLS location. Check if your cluster’s credentials are configured correctly for ADLS.
• Validate Data Format: For JSON, make sure each line is a valid JSON object (no multi-line JSON). For AVRO, confirm files are proper AVRO containers (not JSON-encoded AVRO).
• Test with a Small Dataset: Try inserting a single record first to rule out schema mismatches before loading full data.

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