Support Questions
Find answers, ask questions, and share your expertise
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

HQL - Getting the max(partitioned column data) out from the query


HQL - Getting the max(partitioned column data) out from the query

New Contributor

Hi ,

Below is my scenario with a HQL.

I have a Hive table which is partitioned on the ROW_BATCH_ID column which is generated as part of each day load. But the sqoop process the data from the Hive tables are loaded to Target Oracle Tables.

As part of Testing i need to verify the data load happening on each day is not having any issue. I am trying to do that with a HQL and SQL on the Oracle side.

My hive query will be something like:

Select * from Table A where A.ROW_BATCH_ID in ( Select Max(ROW_BATCH_ID) from Table A);

The issue i am facing is , say for a day the load to the hive table was 0 records and the partition on the row_batch_id fields for that load is 100. But when i run the query its not returning the 0 records rather its going and fetching the previous/earlier day batches which have some data in the Hive table.

Can someone please help with the query which will only pull the 0 records from the max(ROW_BATCH_ID) partitioned Hive table.