Support Questions

Find answers, ask questions, and share your expertise
Celebrating as our community reaches 100,000 members! Thank you!

Can Hive Check data type correctness when reading a csv file?

New Contributor

So I have installed hive on my server. Now I need to load tons of data, into hive tables. Is there any configuration to check when the data type inserted from the CSV is not the same as its supposed column? As far as I know, if the CSV contains a string in the column that is supposed to be an integer (or another data type) the hive table will consider it as an empty cell.


Super Collaborator



I do not think we have such configuration to validate the data, We need to ensure that data matches with the table that we have created.



Chethan YM

Super Collaborator

Hive typically relies on the schema definition provided during table creation, and it doesn't perform automatic type conversion while loading data. If there's a mismatch between the data type in the CSV file and the expected data type in the Hive table, it may result in null or incorrect values.

Use the CAST function to explicitly convert the data types during the INSERT statement.


INSERT INTO TABLE target_table
  CAST(column1 AS INT),
  CAST(column2 AS STRING),
FROM source_table;


Preprocess your CSV data before loading it into Hive. You can use tools like Apache NiFi or custom scripts to clean and validate the data before ingestion.

Remember to thoroughly validate and clean your data before loading it into Hive to avoid unexpected issues. Also, the choice of method depends on your specific use case and the level of control you want over the data loading process.