如何通过Hive表读取列数不同的Parquet文件及设置mergeSchema

Alright, let's tackle your problem with Hive and Parquet files step by step. I've broken this into two clear parts for you:

The key here is to create a table matching your target schema (all four columns) and enable Parquet schema merging so Hive can handle the missing column in the first file. Here's how:

• Step 1: Create the Target Hive Table
  Define your table with all four columns (name, class, marks, grades) and set it to store data as Parquet. You'll also specify a storage location where you'll place both files (or use LOAD DATA to import them later):

  CREATE TABLE student_scores (
      name STRING,
      class STRING,
      marks INT,
      grades STRING
  )
  STORED AS PARQUET
  LOCATION '/user/hive/warehouse/student_scores'; -- Replace with your preferred HDFS path
  

• Step 2: Add Your Parquet Files to the Table's Directory
  Either upload both files directly to the LOCATION path you specified above, or use the LOAD DATA command to import them from your local filesystem:

  -- Import first file (without grades column)
  LOAD DATA LOCAL INPATH '/local/path/to/your/first_file.parquet' INTO TABLE student_scores;
  -- Import second file (with grades column)
  LOAD DATA LOCAL INPATH '/local/path/to/your/second_file.parquet' INTO TABLE student_scores;
  

• Step 3: Enable Schema Merging (Critical!)
  Without this setting, Hive will either throw an error or return inconsistent results when reading mismatched schemas. We'll cover how to set this property in the next section.

This property tells Parquet to automatically merge schemas across different files. You can set it at two levels:

• Session-Level (Temporary, Only for Current Hive Session)
  Run this command before querying your table to enable schema merging for the current session:

  SET parquet.mergeSchema=true;
  

  After running this, when you query SELECT * FROM student_scores;, the first file's grades column will show up as NULL, while the second file's grades values will appear normally.

• Table-Level (Permanent, Applies to the Table Always)
  If you want the table to always use schema merging, set it in the table properties when creating the table, or modify an existing table:
  -- Set during table creation

  CREATE TABLE student_scores (
      name STRING,
      class STRING,
      marks INT,
      grades STRING
  )
  STORED AS PARQUET
  LOCATION '/user/hive/warehouse/student_scores'
  TBLPROPERTIES ('parquet.mergeSchema'='true');
  

  -- Modify an existing table

  ALTER TABLE student_scores SET TBLPROPERTIES ('parquet.mergeSchema'='true');
  

Once you've set up everything correctly, you can verify by running a query—all records from both files will be returned, with NULL filling in for the missing grades values from the first file.

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