Automatically Updating a Hive View Daily

Question

I have a requirement I want to meet. I need to sqoop over data from a DB to Hive. I am sqooping on a daily basis since this data is updated daily.

This data will be used as lookup data from a spark consumer for enrichment. We want to keep a history of all the data we have received but we don't need all the data for lookup only the latest data (same day). I was thinking of creating a hive view from the historical table and only showing records that were inserted that day. Is there a way to automate the view on a daily basis so that the view query will always have the latest data?

Ram Ghadiyaram · Accepted Answer · 2019-08-06T04:48:29.027

Q: Is there a way to automate the view on a daily basis so that the view query will always have the latest data?

No need to update/automate the process if you get a partitioned table based on date.

Q: We want to keep a history of all the data we have received but we don't need all the data for lookup only the latest data (same day).

NOTE : Either hive view or hive table you should always avoid scanning the full table data aka full table scan for getting latest partitioned data.

Option 1: hive approach to query data

If you want to adapt hive approach

you have to go with partition column for example : partition_date and partitioned table in hive

 select *  from table where partition_column in 
    (select max(distinct partition_date  ) from yourpartitionedTable)

or

  select  * from (select *,dense_rank() over (order by partition_date  desc) dt_rnk from db.yourpartitionedTable ) myview
    where myview.dt_rnk=1

will give the latest partition always. (if same day or todays date is there in partition data then it will give the same days partition data otherwise it will give max partition_date) and its data from the partition table.

Option 2: Plain spark approach to query data with spark show partitions command i.e. spark.sql(s"show Partitions $yourpartitionedtablename") get the result in array and sort that to get latest partition date. using that you can query only latest partitioned date as lookup data using your spark component.

see my answer as an idea for getting latest partition date.

I prefer option2 since no hive query is needed and no full table query since we are using show partitions command. and no performance bottle necks and speed will be there.

One more different idea is querying with HiveMetastoreClient or with option2... see this and my answer and the other

Thanks. So using partitions like this there is no need to create a new table? Just query hive tables by the partition? — binaryhex, Aug 07 '19 at 02:54
yes exactly. [spark solution is more suitable for you I think which has full code](https://stackoverflow.com/a/56995785/647053) — Ram Ghadiyaram, Aug 07 '19 at 02:57

vikrant rana · Answer 2 · 2019-08-05T22:48:00.290

I am assuming that you are loading daily transaction records to your history table with some last modified date. Every time you insert or update record to your history table you get your last_modified_date column updated. It could be date or timestamp also. you can create a view in hive to fetch the latest data using analytical function.

Here's some sample data:

CREATE TABLE IF NOT EXISTS db.test_data
(
 user_id int
,country string
,last_modified_date date
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS orc
;

I am inserting few sample records. you see same id is having multiple records for different dates.

INSERT INTO TABLE db.test_data VALUES
(1,'India','2019-08-06'),
(2,'Ukraine','2019-08-06'),
(1,'India','2019-08-05'),
(2,'Ukraine','2019-08-05'),
(1,'India','2019-08-04'),
(2,'Ukraine','2019-08-04');

creating a view in Hive:

CREATE VIEW db.test_view AS
select user_id, country, last_modified_date
from ( select user_id, country, last_modified_date, 
              max(last_modified_date) over (partition by user_id) as max_modified 
       from db.test_data ) as sub
where   last_modified_date = max_modified 
;

hive> select * from db.test_view;

1       India   2019-08-06
2       Ukraine 2019-08-06
Time taken: 5.297 seconds, Fetched: 2 row(s)

It's showing us result with max date only.

If you further inserted another set of record with max last modified date as:

hive> INSERT INTO TABLE db.test_data VALUES
    > (1,'India','2019-08-07');

hive> select * from db.test_view;

1       India   2019-08-07
2       Ukraine 2019-08-06

for reference:Hive View manuual

Automatically Updating a Hive View Daily

2 Answers2