How to handle incremental load for hive table

-

table_1 contains:

customer_id | items | price | updated_date------------+-------+-------+-------------10          | watch | 1000  | 2017062611          | bat   | 400   | 20170625

table_2 contains:

customer_id | items    | price | updated_date------------+----------+-------+-------------10          | computer | 20000 | 20170624

I want to update records of table_2 if customer_id already exists in it, if not, it should append to table_2.

As Hive 0.13 does not support update, I tried using join, but it fails.

There are 2 ways to handle this:

1st way: using row_number

2nd way: using Full outer join but hive 0.13 does not support update to we cant use this Full join

By Using row Number:

insert overwrite table_1 select customer_id, items, price, updated_datefrom(select customer_id, items, price, updated_date,       row_number() over(partition by customer_id order by new_flag desc) rnfrom     (     select customer_id, items, price, updated_date, 0 as new_flag       from table_1     union all     select customer_id, items, price, updated_date, 1 as new_flag       from table_2    ) all_data)s where rn=1;Also if you want to update all columns with new data, you can apply this solution with UNION ALL+row_number(), it works faster than full join:

2nd Way: using Full outer join

If you cannot update or merge in ACID mode then it’s possible to update using FULL OUTER JOIN  To find all entries that will be updated you can join increment data with old data:

insert overwrite target_data [partition() if applicable]SELECT  --select new if exists, old if not exists  case when i.PK is not null then i.PK   else t.PK   end as PK,  case when i.PK is not null then i.COL1 else t.COL1 end as COL1,  ...   case when i.PK is not null then i.COL_n else t.COL_n end as COL_n  FROM       target_data t --restrict partitions if applicable      FULL JOIN increment_data i on (t.PK=i.PK);

It’s possible to optimize this by restricting partitions in target_data that will be overwritten and joined using WHERE partition_col in (select distinct partition_col from increment_data) or pass partition list if possible as a parameter and use in the where clause, it will work even faster.

Popular posts from this blog

Window function in PySpark with Joins example using 2 Dataframes (inner join)

-
Input details: #● File has json records #● Each record has fields: #○ user_id #○ card_num #○ merchant #○ category #○ amount #○ ts ### Below analysis to be done Sample data: +------+--------+---------+--------+----------+-------+|amount|card_num| category|merchant| ts|user_id|+------+--------+---------+--------+----------+-------+| 243| C_108| food| M_102|1579532902| U_104|| 699| C_106|cosmetics| M_103|1581759040| U_103|| 228| C_104| children| M_110|1584161986| U_103| Solution: from pyspark.sql.functions import col # this can be done without using window function creditCardData = spark.read.json(“card_transactions.json”) useridMaxSpendDF = df.groupby(‘user_id’).max(‘amount’) useridMaxSpendDF=useridMaxSpendDF.withColumnRenamed(“max(amount)”,”max_amount”) useridMaxSpendDF=useridMaxSpendDF.withColumnRenamed(“user_id”,”m_user_id”) cond = [creditCardData.user_id == useridMaxSpen...
Read more

Complex SQL: fetch the users who logged in consecutively 3 or more times (lead perfect example)

-
From the login_details table, fetch the users who logged in consecutively 3 or more times. Table Name : LOGIN_DETAILS Approach : We need to fetch users who have appeared 3 or more times consecutively in login details table. There is a window function which can be used to fetch data from the following record. Use that window function to compare the user name in current row with user name in the next row and in the row following the next row. If it matches then fetch those records. –Table Structure: drop table login_details; create table login_details( login_id int primary key, user_name varchar(50) not null, login_date date); delete from login_details; insert into login_details values (101, ‘Michael’, current_date), (102, ‘James’, current_date), (103, ‘Stewart’, current_date+1), (104, ‘Stewart’, current_date+1), (105, ‘Stewart’, current_date+1), (106, ‘Michael’, current_date+2), (107, ‘Michael’, cur...
Read more

Credit Card Data Analysis using PySpark (how to use auto broadcast join after disabling it)

-
Input details: #● File has json records #● Each record has fields: #○ user_id #○ card_num #○ merchant #○ category #○ amount #○ ts ### Below analysis to be done Sample data: +------+--------+---------+--------+----------+-------+|amount|card_num| category|merchant| ts|user_id|+------+--------+---------+--------+----------+-------+| 243| C_108| food| M_102|1579532902| U_104|| 699| C_106|cosmetics| M_103|1581759040| U_103|| 228| C_104| children| M_110|1584161986| U_103| Application: Here we will disable auto broadcast and then join 2 data frames and will see execution plan which should use broadcast join Solution: from pyspark.sql import SparkSession from pyspark.sql.functions import broadcast spark = SparkSession.builder.master(‘local[2]’)\ .appName(‘RDD_Methods_Examples’)\ .getOrCreate() print(spark.conf.get(“spark.sql.autoBroadcastJoinThreshold”)) spark.conf.set(“spark.sql.autoBroadcastJoinThreshold”, -1) pr...
Read more