How to Merge DataFrames in Apache Spark/Hive and then increment version

Question

We receive daily files from external system and store it into Hive. Want to enable versioning on data.

col1, col2 is composite key so if we receive same combination of data from file then it should be stored into Hive with new version. Latest data that comes from file should get the biggest version number. How could we do this in spark

file df

+----+----+-----+-------------------+-------+
||col1 |col2|value| ts            |version|
+----+----+-----+-------------------+-------+
| A| B| 777|2019-01-01 00:00:00| 1|
| K| D| 228|2019-01-01 00:00:00| 1|
| G| G| 241|2019-01-01 00:00:00| 1|
+----+----+-----+-------------------+-------+
Don't receive version from external system but if we need it for comparison then it will be always 1

hive df

+----+----+-----+-------------------+-------+
||col1 |col2|value| ts            |version|
+----+----+-----+-------------------+-------+
| A| B| 999|2018-01-01 00:00:00| 1|
| A| B| 888|2018-01-02 00:00:00| 2|
| B| C| 133|2018-01-03 00:00:00| 1|
| G| G| 231|2018-01-01 00:00:00| 1|
+----+----+-----+-------------------+-------+

After merge

+----+----+-----+-------------------+-----------+
|col1|col2|value| ts                    |new_version|
+----+----+-----+-------------------+-----------+
| B| C| 133|2018-01-03 00:00:00| 1|
| K| D| 228|2019-01-01 00:00:00| 1|
| A| B| 999|2018-01-01 00:00:00| 1|
| A| B| 888|2018-01-02 00:00:00| 2|
| A| B| 777|2019-01-01 00:00:00| 3|
| G| G| 231|2018-01-01 00:00:00| 1|
| G| G| 241|2019-01-01 00:00:00| 2|
+----+----+-----+-------------------+-----------+

Answer 1

existing main hive table:

  INSERT INTO TABLE test_dev_db.test_1 VALUES
    ('A','B',124,1),
    ('A','B',123,2),
    ('B','C',133,1),
    ('G','G',231,1);

suppose you have loaded below data from file

INSERT INTO TABLE test_dev_db.test_2 VALUES
('A','B',222,1),
('K','D',228,1),
('G','G',241,1);

here is your query:

WITH CTE AS (
    SELECT col1,col2,value,version FROM test_dev_db.test_1
    UNION
    SELECT col1,col2,value,version FROM test_dev_db.test_2
)
insert overwrite table test_dev_db.test_1
SELECT a.col1,a.col2,a.value, row_number() over(partition by a.col1,a.col2 order by a.col1,a.col1) as new_version
FROM CTE a;


hive> select * from test_dev_db.test_1;
OK
A       B       123     1
A       B       124     2
A       B       222     3
B       C       133     1
G       G       231     1
G       G       241     2
K       D       228     1

for Spark:

create your dataframes reading from file and hive table and union them

uniondf=df1.unionAll(df2)

from pyspark.sql.functions import row_number,lit
from pyspark.sql.window import Window
w = Window().partitionBy('col1','col2').orderBy(lit('A'))
newdf= uniondf.withColumn("new_version", row_number().over(w)).drop('version')

>>> newdf.show();
+----+----+-----+-----------+
|col1|col2|value|new_version|
+----+----+-----+-----------+
|   B|   C|  133|          1|
|   K|   D|  228|          1|
|   A|   B|  124|          1|
|   A|   B|  123|          2|
|   A|   B|  222|          3|
|   G|   G|  231|          1|
|   G|   G|  241|          2|
+----+----+-----+-----------+

saving it to hive

newdf.write.format("orc").option("header", "true").mode("overwrite").saveAsTable('test_dev_db.new_test_1')