Hive Queries Fail with Field Not Found Error

Resolve Hive query failures on Parquet tables caused by case sensitivity mismatches between external Parquet schema metadata and lowercased Hive Metastore schema entries.

About the task

When running critical jobs or queries on Parquet-backed tables, the process fails with the following message: does not contain requested field: optional double

This issue occurs because third-party vendor tools generate Parquet files with camelCase column names, whereas the Hive Metastore (HMS) stores all column names in lowercase. The case mismatch prevents Hive from matching table fields during execution.

Cause 1

For SELECT queries, simple queries get converted into fetch tasks that retrieve data directly from storage. For INSERT queries, automated statistics collection triggers schema validation, which causes the operation to fail when inspecting column-level statistics.

Workaround for query failures

For SELECT queries, disable fetch task conversions by running the following command in your session i.e. Hive is forced down the regular execution path:
```
SET hive.fetch.task.conversion=none;
```
Confirms that simple SELECT queries bypass direct fetch tasks and run successfully.
For INSERT queries, set the hive.stats.column.autogather property to false in the HiveServer2 hive-site.xml configuration file at the warehouse level:
```
hive.stats.column.autogather false
```
Confirms that INSERT queries complete without auto-gathering column statistics.
Restart the Hive Virtual Warehouse.

note
The above settings need to be present in all Virtual Warehouses.

Cause 2

The long-term root cause is the schema discrepancy between camelCase metadata in vendor Parquet files and lowercase column definitions in the Hive Metastore.

Resolution

Request the data vendor to generate Parquet metadata with lowercase column names instead of camelCase.
After updating the source Parquet files, revert the hive.stats.column.autogather property to true at the warehouse level to resume automatic collection of column statistics for Cost-Based Optimizer (CBO) plan optimization.

Confirms that queries complete successfully with automated column statistics gathering enabled.