数据湖deltalake的的insert into、 update 和timeline（时间旅行）验证

最新推荐文章于 2024-04-16 10:10:19 发布

chriszzww

最新推荐文章于 2024-04-16 10:10:19 发布

阅读量523

点赞数

文章标签： spark hive sql

本文链接：https://blog.csdn.net/zhu2525wei/article/details/125100336

版权

验证过程：

1、验证insert OVERWRITE，insert into 基于timeline多版本的查询（时间旅行）

2、构造一张store_sales_delta_update1的表，表中有更新字段的数据，有新增的数据，验证采用 delta lake 的merge into 是否能够完成最终的更新（更新字段根据条件进行update操作，新增的记录采用insert into操作）

验证结论：

当前验证结果deltalake 是能够根据timeline进行按照历史版本进行查询的，增量场景能够根据条件完成update、insertinto，当前没有验证delelte的操作，结果符合预期的

当前应用上不足，就是spark sql 不支持timeline参数的指定，易用性不好

验证步骤和结果：

1、环境：spark 3.2.1，hive 3.1.2

2、启动pyspark和 spark-sql（说明：由于当前基于timeline的查询不支持spark sql直接操作，基于时间线的查询需要切换到pyspark进行查询）：

pyspark --packages io.delta:delta-core_2.12:1.2.1 --conf "spark.sql.extensions=io.delta.sql.DeltaSparkSessionExtension" --conf "spark.sql.catalog.spark_catalog=org.apache.spark.sql.delta.catalog.DeltaCatalog"

spark-sql --packages io.delta:delta-core_2.12:1.2.1 --conf "spark.sql.extensions=io.delta.sql.DeltaSparkSessionExtension" --conf "spark.sql.catalog.spark_catalog=org.apache.spark.sql.delta.catalog.DeltaCatalog"
2、根据deltalake帮忙，采用tpc-ds的store_sales进行验证

3、创建deltalake表：

CREATE TABLE IF NOT EXISTS store_sales_delta(
ss_sold_date_sk bigint
, ss_sold_time_sk bigint
, ss_item_sk bigint
, ss_customer_sk bigint
, ss_cdemo_sk bigint
, ss_hdemo_sk bigint
, ss_addr_sk bigint
, ss_store_sk bigint
, ss_promo_sk bigint
, ss_ticket_number bigint
, ss_quantity int
, ss_wholesale_cost decimal(7,2)
, ss_list_price decimal(7,2)
, ss_sales_price decimal(7,2)
, ss_ext_discount_amt decimal(7,2)
, ss_ext_sales_price decimal(7,2)
, ss_ext_wholesale_cost decimal(7,2)
, ss_ext_list_price decimal(7,2)
, ss_ext_tax decimal(7,2)
, ss_coupon_amt decimal(7,2)
, ss_net_paid decimal(7,2)
, ss_net_paid_inc_tax decimal(7,2)
, ss_net_profit decimal(7,2)
)USING DELTA;

存储路径：/user/hive/warehouse/delta_test.db/store_sales_delta/

2、从基本表导入数据

INSERT OVERWRITE TABLE store_sales_delta SELECT * FROM tpcds_text_3.store_sales limit 10;

3、登录pyspark根据timeline查询：

df2 = spark.read.format("delta").option("versionAsOf", 1).load("/user/hive/warehouse/delta_test.db/store_sales_delta")
df2.show()
|ss_sold_date_sk|ss_sold_time_sk|ss_item_sk|ss_customer_sk|ss_cdemo_sk|ss_hdemo_sk|ss_addr_sk|ss_store_sk|ss_promo_sk|ss_ticket_number|ss_quantity|ss_wholesale_cost|ss_list_price|ss_sales_price|ss_ext_discount_amt|ss_ext_sales_price|ss_ext_wholesale_cost|ss_ext_list_price|ss_ext_tax|ss_coupon_amt|ss_net_paid|ss_net_paid_inc_tax|ss_net_profit|
+---------------+---------------+----------+--------------+-----------+-----------+----------+-----------+-----------+----------------+-----------+-----------------+-------------+--------------+-------------------+------------------+---------------------+-----------------+----------+-------------+-----------+-------------------+-------------+
| 2451813| 65495| 23993| 43006| 591617| 3428| 12839| 7| 37| 1| 79| 11.41| 18.71| 2.80| 99.54| 221.20| 901.39| 1478.09| 6.08| 99.54| 121.66| 127.74| -779.73|
| 2451813| 65495| 18007| 43006| 591617| 3428| 12839| 7| 246| 1| 37| 63.63| 101.17| 41.47| 46.03| 1534.39| 2354.31| 3743.29| 59.53| 46.03| 1488.36| 1547.89| -865.95|
| 2451813| 65495| 17690| 43006| 591617| 3428| 12839| 7| 184| 1| 99| 80.52| 137.68| 83.98| 0.00| 8314.02| 7971.48| 13630.32| 0.00| 0.00| 8314.02| 8314.02| 342.54|
| 2451813| 65495| 3404| 43006| 591617| 3428| 12839| 7| 284| 1| 14| 57.37| 76.30| 6.10| 0.00| 85.40| 803.18| 1068.20| 0.00| 0.00| 85.40| 85.40| -717.78|
| 2451813| 65495| 29726| 43006| 591617| 3428| 12839| 7| 8| 1| 100| 25.08| 36.86| 0.73| 0.00| 73.00| 2508.00| 3686.00| 6.57| 0.00| 73.00| 79.57| -2435.00|
| 2451813| 65495| 24842| 43006| 591617| 3428| 12839| 7| 335| 1| 91| 93.48| 108.43| 93.24| 0.00| 8484.84| 8506.68| 9867.13| 254.54| 0.00| 8484.84| 8739.38| -21.84|
| 2451813| 65495| 19141| 43006| 591617| 3428| 12839| 7| 150| 1| 5| 10.68| 15.91| 6.68| 0.00| 33.40| 53.40| 79.55| 2.33| 0.00| 33.40| 35.73| -20.00|
| 2451813| 65495| 2672| 43006| 591617| 3428| 12839| 7| 247| 1| 72| 84.72| 111.83| 61.50| 0.00| 4428.00| 6099.84| 8051.76| 177.12| 0.00| 4428.00| 4605.12| -1671.84|
| 2451813| 65495| 31412| 43006| 591617| 3428| 12839| 7| 2| 1| 14| 11.54| 11.77| 0.00| 0.00| 0.00| 161.56| 164.78| 0.00| 0.00| 0.00| 0.00| -161.56|
| 2451813| 65495| 11711| 43006| 591617| 3428| 12839| 7| 221| 1| 58| 4.57| 5.34| 3.52| 0.00| 204.16| 265.06| 309.72| 0.00| 0.00| 204.16| 204.16| -60.90|

4、执行append方式的insert into并按照timeline查询：

INSERT into TABLE store_sales_delta SELECT * FROM tpcds_text_3.store_sales limit 10;

df2 = spark.read.format("delta").option("versionAsOf", 2).load("/user/hive/warehouse/delta_test.db/store_sales_delta")
df2.show()

|ss_sold_date_sk|ss_sold_time_sk|ss_item_sk|ss_customer_sk|ss_cdemo_sk|ss_hdemo_sk|ss_addr_sk|ss_store_sk|ss_promo_sk|ss_ticket_number|ss_quantity|ss_wholesale_cost|ss_list_price|ss_sales_price|ss_ext_discount_amt|ss_ext_sales_price|ss_ext_wholesale_cost|ss_ext_list_price|ss_ext_tax|ss_coupon_amt|ss_net_paid|ss_net_paid_inc_tax|ss_net_profit|
+---------------+---------------+----------+--------------+-----------+-----------+----------+-----------+-----------+----------------+-----------+-----------------+-------------+--------------+-------------------+------------------+---------------------+-----------------+----------+-------------+-----------+-------------------+-------------+
| 2452252| 31384| 32411| 19575| 1540941| 1860| 29226| 31| 232| 82355| 56| 4.33| 5.49| 3.12| 0.00| 174.72| 242.48| 307.44| 12.23| 0.00| 174.72| 186.95| -67.76|
| 2452252| 31384| 9427| 19575| 1540941| 1860| 29226| 31| 320| 82355| 17| 74.73| 75.47| 24.90| 0.00| 423.30| 1270.41| 1282.99| 4.23| 0.00| 423.30| 427.53| -847.11|
| 2452252| 31384| 3839| 19575| 1540941| 1860| 29226| 31| 53| 82355| 13| 2.40| 3.12| 3.08| 0.00| 40.04| 31.20| 40.56| 2.00| 0.00| 40.04| 42.04| 8.84|
| 2452252| 31384| 6785| 19575| 1540941| 1860| 29226| 31| 80| 82355| 1| 10.82| 21.53| 8.82| 0.00| 8.82| 10.82| 21.53| 0.17| 0.00| 8.82| 8.99| -2.00|
| null| null| 12183| null| null| null| null| 31| null| 82355| null| 34.52| null| 22.88| null| null| 517.80| 880.20| null| null| null| null| null|
| 2452252| 31384| 4937| 19575| 1540941| 1860| 29226| 31| 54| 82355| 86| 69.50| 93.13| 50.29| 3978.94| 4324.94| 5977.00| 8009.18| 17.30| 3978.94| 346.00| 363.30| -5631.00|
| 2452252| 31384| 11491| 19575| 1540941| 1860| 29226| 31| 294| 82355| 53| 34.65| 44.35| 39.91| 1797.94| 2115.23| 1836.45| 2350.55| 15.86| 1797.94| 317.29| 333.15| -1519.16|
| 2452252| 31384| 1963| 19575| 1540941| 1860| 29226| 31| 288| 82355| 49| 30.68| 35.89| 3.94| 61.77| 193.06| 1503.32| 1758.61| 3.93| 61.77| 131.29| 135.22| -1372.03|
| 2452252| 31384| 22539| 19575| 1540941| 1860| 29226| 31| 50| 82355| 100| 1.87| 2.09| 2.04| 0.00| 204.00| 187.00| 209.00| 16.32| 0.00| 204.00| 220.32| 17.00|
| 2452128| 50003| 10247| 94588| 588704| 4067| 29383| 16| 119| 82356| 41| 2.99| 3.73| 3.31| 0.00| 135.71| 122.59| 152.93| 4.07| 0.00| 135.71| 139.78| 13.12|
| 2451813| 65495| 23993| 43006| 591617| 3428| 12839| 7| 37| 1| 79| 11.41| 18.71| 2.80| 99.54| 221.20| 901.39| 1478.09| 6.08| 99.54| 121.66| 127.74| -779.73|
| 2451813| 65495| 18007| 43006| 591617| 3428| 12839| 7| 246| 1| 37| 63.63| 101.17| 41.47| 46.03| 1534.39| 2354.31| 3743.29| 59.53| 46.03| 1488.36| 1547.89| -865.95|
| 2451813| 65495| 17690| 43006| 591617| 3428| 12839| 7| 184| 1| 99| 80.52| 137.68| 83.98| 0.00| 8314.02| 7971.48| 13630.32| 0.00| 0.00| 8314.02| 8314.02| 342.54|
| 2451813| 65495| 3404| 43006| 591617| 3428| 12839| 7| 284| 1| 14| 57.37| 76.30| 6.10| 0.00| 85.40| 803.18| 1068.20| 0.00| 0.00| 85.40| 85.40| -717.78|
| 2451813| 65495| 29726| 43006| 591617| 3428| 12839| 7| 8| 1| 100| 25.08| 36.86| 0.73| 0.00| 73.00| 2508.00| 3686.00| 6.57| 0.00| 73.00| 79.57| -2435.00|
| 2451813| 65495| 24842| 43006| 591617| 3428| 12839| 7| 335| 1| 91| 93.48| 108.43| 93.24| 0.00| 8484.84| 8506.68| 9867.13| 254.54| 0.00| 8484.84| 8739.38| -21.84|
| 2451813| 65495| 19141| 43006| 591617| 3428| 12839| 7| 150| 1| 5| 10.68| 15.91| 6.68| 0.00| 33.40| 53.40| 79.55| 2.33| 0.00| 33.40| 35.73| -20.00|
| 2451813| 65495| 2672| 43006| 591617| 3428| 12839| 7| 247| 1| 72| 84.72| 111.83| 61.50| 0.00| 4428.00| 6099.84| 8051.76| 177.12| 0.00| 4428.00| 4605.12| -1671.84|
| 2451813| 65495| 31412| 43006| 591617| 3428| 12839| 7| 2| 1| 14| 11.54| 11.77| 0.00| 0.00| 0.00| 161.56| 164.78| 0.00| 0.00| 0.00| 0.00| -161.56|
| 2451813| 65495| 11711| 43006| 591617| 3428| 12839| 7| 221| 1| 58| 4.57| 5.34| 3.52| 0.00| 204.16| 265.06| 309.72| 0.00| 0.00| 204.16| 204.16| -60.90|

5、执行update操作，更新ss_net_profit为100.00，查询最新版本可以查询到

UPDATE store_sales_delta SET ss_net_profit = 100.00 WHERE ss_sold_date_sk = 2452252;

df3 = spark.read.format("delta").option("versionAsOf", 3).load("/user/hive/warehouse/delta_test.db/store_sales_delta")
df3.show()
|ss_sold_date_sk|ss_sold_time_sk|ss_item_sk|ss_customer_sk|ss_cdemo_sk|ss_hdemo_sk|ss_addr_sk|ss_store_sk|ss_promo_sk|ss_ticket_number|ss_quantity|ss_wholesale_cost|ss_list_price|ss_sales_price|ss_ext_discount_amt|ss_ext_sales_price|ss_ext_wholesale_cost|ss_ext_list_price|ss_ext_tax|ss_coupon_amt|ss_net_paid|ss_net_paid_inc_tax|ss_net_profit|
+---------------+---------------+----------+--------------+-----------+-----------+----------+-----------+-----------+----------------+-----------+-----------------+-------------+--------------+-------------------+------------------+---------------------+-----------------+----------+-------------+-----------+-------------------+-------------+
| 2452252| 31384| 32411| 19575| 1540941| 1860| 29226| 31| 232| 82355| 56| 4.33| 5.49| 3.12| 0.00| 174.72| 242.48| 307.44| 12.23| 0.00| 174.72| 186.95| 100.00|
| 2452252| 31384| 9427| 19575| 1540941| 1860| 29226| 31| 320| 82355| 17| 74.73| 75.47| 24.90| 0.00| 423.30| 1270.41| 1282.99| 4.23| 0.00| 423.30| 427.53| 100.00|
| 2452252| 31384| 3839| 19575| 1540941| 1860| 29226| 31| 53| 82355| 13| 2.40| 3.12| 3.08| 0.00| 40.04| 31.20| 40.56| 2.00| 0.00| 40.04| 42.04| 100.00|
| 2452252| 31384| 6785| 19575| 1540941| 1860| 29226| 31| 80| 82355| 1| 10.82| 21.53| 8.82| 0.00| 8.82| 10.82| 21.53| 0.17| 0.00| 8.82| 8.99| 100.00|
| null| null| 12183| null| null| null| null| 31| null| 82355| null| 34.52| null| 22.88| null| null| 517.80| 880.20| null| null| null| null| null|
| 2452252| 31384| 4937| 19575| 1540941| 1860| 29226| 31| 54| 82355| 86| 69.50| 93.13| 50.29| 3978.94| 4324.94| 5977.00| 8009.18| 17.30| 3978.94| 346.00| 363.30| 100.00|
| 2452252| 31384| 11491| 19575| 1540941| 1860| 29226| 31| 294| 82355| 53| 34.65| 44.35| 39.91| 1797.94| 2115.23| 1836.45| 2350.55| 15.86| 1797.94| 317.29| 333.15| 100.00|
| 2452252| 31384| 1963| 19575| 1540941| 1860| 29226| 31| 288| 82355| 49| 30.68| 35.89| 3.94| 61.77| 193.06| 1503.32| 1758.61| 3.93| 61.77| 131.29| 135.22| 100.00|
| 2452252| 31384| 22539| 19575| 1540941| 1860| 29226| 31| 50| 82355| 100| 1.87| 2.09| 2.04| 0.00| 204.00| 187.00| 209.00| 16.32| 0.00| 204.00| 220.32| 100.00|
| 2452128| 50003| 10247| 94588| 588704| 4067| 29383| 16| 119| 82356| 41| 2.99| 3.73| 3.31| 0.00| 135.71| 122.59| 152.93| 4.07| 0.00| 135.71| 139.78| 13.12|
| 2451813| 65495| 23993| 43006| 591617| 3428| 12839| 7| 37| 1| 79| 11.41| 18.71| 2.80| 99.54| 221.20| 901.39| 1478.09| 6.08| 99.54| 121.66| 127.74| -779.73|
| 2451813| 65495| 18007| 43006| 591617| 3428| 12839| 7| 246| 1| 37| 63.63| 101.17| 41.47| 46.03| 1534.39| 2354.31| 3743.29| 59.53| 46.03| 1488.36| 1547.89| -865.95|
| 2451813| 65495| 17690| 43006| 591617| 3428| 12839| 7| 184| 1| 99| 80.52| 137.68| 83.98| 0.00| 8314.02| 7971.48| 13630.32| 0.00| 0.00| 8314.02| 8314.02| 342.54|
| 2451813| 65495| 3404| 43006| 591617| 3428| 12839| 7| 284| 1| 14| 57.37| 76.30| 6.10| 0.00| 85.40| 803.18| 1068.20| 0.00| 0.00| 85.40| 85.40| -717.78|
| 2451813| 65495| 29726| 43006| 591617| 3428| 12839| 7| 8| 1| 100| 25.08| 36.86| 0.73| 0.00| 73.00| 2508.00| 3686.00| 6.57| 0.00| 73.00| 79.57| -2435.00|
| 2451813| 65495| 24842| 43006| 591617| 3428| 12839| 7| 335| 1| 91| 93.48| 108.43| 93.24| 0.00| 8484.84| 8506.68| 9867.13| 254.54| 0.00| 8484.84| 8739.38| -21.84|
| 2451813| 65495| 19141| 43006| 591617| 3428| 12839| 7| 150| 1| 5| 10.68| 15.91| 6.68| 0.00| 33.40| 53.40| 79.55| 2.33| 0.00| 33.40| 35.73| -20.00|
| 2451813| 65495| 2672| 43006| 591617| 3428| 12839| 7| 247| 1| 72| 84.72| 111.83| 61.50| 0.00| 4428.00| 6099.84| 8051.76| 177.12| 0.00| 4428.00| 4605.12| -1671.84|
| 2451813| 65495| 31412| 43006| 591617| 3428| 12839| 7| 2| 1| 14| 11.54| 11.77| 0.00| 0.00| 0.00| 161.56| 164.78| 0.00| 0.00| 0.00| 0.00| -161.56|
| 2451813| 65495| 11711| 43006| 591617| 3428| 12839| 7| 221| 1| 58| 4.57| 5.34| 3.52| 0.00| 204.16| 265.06| 309.72| 0.00| 0.00| 204.16| 204.16| -60.90|

7、根据时间线查询老版本2的数据：

>>> df2 = spark.read.format("delta").option("versionAsOf", 2).load("/user/hive/warehouse/delta_test.db/store_sales_delta")
>>> df2.show()

8、创建clone表，并验证更新场景：

CREATE TABLE IF NOT EXISTS store_sales_delta_update1(
ss_sold_date_sk bigint
, ss_sold_time_sk bigint
, ss_item_sk bigint
, ss_customer_sk bigint
, ss_cdemo_sk bigint
, ss_hdemo_sk bigint
, ss_addr_sk bigint
, ss_store_sk bigint
, ss_promo_sk bigint
, ss_ticket_number bigint
, ss_quantity int
, ss_wholesale_cost decimal(7,2)
, ss_list_price decimal(7,2)
, ss_sales_price decimal(7,2)
, ss_ext_discount_amt decimal(7,2)
, ss_ext_sales_price decimal(7,2)
, ss_ext_wholesale_cost decimal(7,2)
, ss_ext_list_price decimal(7,2)
, ss_ext_tax decimal(7,2)
, ss_coupon_amt decimal(7,2)
, ss_net_paid decimal(7,2)
, ss_net_paid_inc_tax decimal(7,2)
, ss_net_profit decimal(7,2)
)USING DELTA;

INSERT into TABLE store_sales_delta_update1 SELECT * FROM store_sales_delta WHERE ss_sold_date_sk = 2452252;

查询导入的结果数据：

9、构造更新数据：

UPDATE store_sales_delta_update1 SET ss_net_profit = 99.00 WHERE ss_sold_date_sk = 2452252;

查询数据：

导入ss_sold_date_sk 为2451813的部分数据：

INSERT into TABLE store_sales_delta_update1 select * from tpcds_text_3.store_sales WHERE ss_sold_date_sk = 2451813 limit 5;

spark-sql (default)> select * from store_sales_delta_update1;
ss_sold_date_sk   ss_sold_time_sk   ss_item_sk   ss_customer_sk   ss_cdemo_sk   ss_hdemo_sk   ss_addr_sk   ss_store_sk   ss_promo_sk   ss_ticket_number ss_quantity   ss_wholesale_cost   ss_list_price   ss_sales_price   ss_ext_discount_amt   ss_ext_sales_price   ss_ext_wholesale_cost   ss_ext_list_price ss_ext_tax   ss_coupon_amt   ss_net_paid   ss_net_paid_inc_tax   ss_net_profit
2452252   31384   32411   19575   1540941   1860   29226   31   232   82355   56   4.33   5.49   3.12   0.00   174.72   242.48   307.44   12.23   0.00   174.72 186.95   99.00
2452252   31384   9427   19575   1540941   1860   29226   31   320   82355   17   74.73   75.47   24.90   0.00   423.30   1270.41   1282.99   4.23   0.00   423.30 427.53   99.00
2452252   31384   3839   19575   1540941   1860   29226   31   53   82355   13   2.40   3.12   3.08   0.00   40.04   31.20   40.56   2.00   0.00   40.04 42.04   99.00
2452252   31384   6785   19575   1540941   1860   29226   31   80   82355   1   10.82   21.53   8.82   0.00   8.82   10.82   21.53   0.17   0.00   8.82 8.99   99.00
2452252   31384   4937   19575   1540941   1860   29226   31   54   82355   86   69.50   93.13   50.29   3978.94   4324.94   5977.00   8009.18   17.30   3978.94   346.00 363.30   99.00
2452252   31384   11491   19575   1540941   1860   29226   31   294   82355   53   34.65   44.35   39.91   1797.94   2115.23   1836.45   2350.55   15.86   1797.94   317.29 333.15   99.00
2452252   31384   1963   19575   1540941   1860   29226   31   288   82355   49   30.68   35.89   3.94   61.77   193.06   1503.32   1758.61   3.93   61.77   131.29 135.22   99.00
2452252   31384   22539   19575   1540941   1860   29226   31   50   82355   100   1.87   2.09   2.04   0.00   204.00   187.00   209.00   16.32   0.00   204.00 220.32   99.00
2451813   65495   23993   43006   591617   3428   12839   7   37   1   79   11.41   18.71   2.80   99.54   221.20   901.39   1478.09   6.08   99.54   121.66 127.74   -779.73
2451813   65495   18007   43006   591617   3428   12839   7   246   1   37   63.63   101.17   41.47   46.03   1534.39   2354.31   3743.29   59.53   46.03   1488.361547.89   -865.95
2451813   65495   17690   43006   591617   3428   12839   7   184   1   99   80.52   137.68   83.98   0.00   8314.02   7971.48   13630.32   0.00   0.00 8314.02   8314.02   342.54
2451813   65495   3404   43006   591617   3428   12839   7   284   1   14   57.37   76.30   6.10   0.00   85.40   803.18   1068.20   0.00   0.00   85.40 85.40   -717.78
2451813   65495   29726   43006   591617   3428   12839   7   8   1   100   25.08   36.86   0.73   0.00   73.00   2508.00   3686.00   6.57   0.00   73.00 79.57   -2435.00

10、导入新增场景的数据

INSERT into TABLE store_sales_delta_update1 select * from tpcds_text_3.store_sales WHERE ss_sold_date_sk = 2452179 limit 5;

构造更新数据

UPDATE store_sales_delta_update1 SET ss_net_profit = 88.00 WHERE ss_sold_date_sk = 2451813;

11、查询待更新表（store_sales_delta）和更新表的数据（store_sales_delta_update1）

select * from store_sales_delta;
ss_sold_date_sk   ss_sold_time_sk   ss_item_sk   ss_customer_sk   ss_cdemo_sk   ss_hdemo_sk   ss_addr_sk   ss_store_sk   ss_promo_sk   ss_ticket_number ss_quantity   ss_wholesale_cost   ss_list_price   ss_sales_price   ss_ext_discount_amt   ss_ext_sales_price   ss_ext_wholesale_cost   ss_ext_list_price ss_ext_tax   ss_coupon_amt   ss_net_paid   ss_net_paid_inc_tax   ss_net_profit
2452252   31384   32411   19575   1540941   1860   29226   31   232   82355   56   4.33   5.49   3.12   0.00   174.72   242.48   307.44   12.23   0.00   174.72 186.95   100.00
2452252   31384   9427   19575   1540941   1860   29226   31   320   82355   17   74.73   75.47   24.90   0.00   423.30   1270.41   1282.99   4.23   0.00   423.30 427.53   100.00
2452252   31384   3839   19575   1540941   1860   29226   31   53   82355   13   2.40   3.12   3.08   0.00   40.04   31.20   40.56   2.00   0.00   40.04 42.04   100.00
2452252   31384   6785   19575   1540941   1860   29226   31   80   82355   1   10.82   21.53   8.82   0.00   8.82   10.82   21.53   0.17   0.00   8.82 8.99   100.00
NULL   NULL   12183   NULL   NULL   NULL   NULL   31   NULL   82355   NULL   34.52   NULL   22.88   NULL   NULL   517.80   880.20   NULL   NULL   NULL NULL   NULL
2452252   31384   4937   19575   1540941   1860   29226   31   54   82355   86   69.50   93.13   50.29   3978.94   4324.94   5977.00   8009.18   17.30   3978.94   346.00 363.30   100.00
2452252   31384   11491   19575   1540941   1860   29226   31   294   82355   53   34.65   44.35   39.91   1797.94   2115.23   1836.45   2350.55   15.86   1797.94   317.29 333.15   .00
2452252   31384   1963   19575   1540941   1860   29226   31   288   82355   49   30.68   35.89   3.94   61.77   193.06   1503.32   1758.61   3.93   61.77   131.29 135.22   100.00
2452252   31384   22539   19575   1540941   1860   29226   31   50   82355   100   1.87   2.09   2.04   0.00   204.00   187.00   209.00   16.32   0.00   204.00 220.32   100.00
2452128   50003   10247   94588   588704   4067   29383   16   119   82356   41   2.99   3.73   3.31   0.00   135.71   122.59   152.93   4.07   0.00   135.71 139.78   13.12
2451813   65495   23993   43006   591617   3428   12839   7   37   1   79   11.41   18.71   2.80   99.54   221.20   901.39   1478.09   6.08   99.54   121.66 127.74   -779.73
2451813   65495   18007   43006   591617   3428   12839   7   246   1   37   63.63   101.17   41.47   46.03   1534.39   2354.31   3743.29   59.53   46.03   1488.361547.89   -865.95
2451813   65495   17690   43006   591617   3428   12839   7   184   1   99   80.52   137.68   83.98   0.00   8314.02   7971.48   13630.32   0.00   0.00 8314.02   8314.02   342.54
2451813   65495   3404   43006   591617   3428   12839   7   284   1   14   57.37   76.30   6.10   0.00   85.40   803.18   1068.20   0.00   0.00   85.40 85.40   -717.78
2451813   65495   29726   43006   591617   3428   12839   7   8   1   100   25.08   36.86   0.73   0.00   73.00   2508.00   3686.00   6.57   0.00   73.00 79.57   -2435.00
2451813   65495   24842   43006   591617   3428   12839   7   335   1   91   93.48   108.43   93.24   0.00   8484.84   8506.68   9867.13   254.54   0.00   8484.848739.38   -21.84
2451813   65495   19141   43006   591617   3428   12839   7   150   1   5   10.68   15.91   6.68   0.00   33.40   53.40   79.55   2.33   0.00   33.40 35.73   -20.00
2451813   65495   2672   43006   591617   3428   12839   7   247   1   72   84.72   111.83   61.50   0.00   4428.00   6099.84   8051.76   177.12   0.00   4428.004605.12   -1671.84
2451813   65495   31412   43006   591617   3428   12839   7   2   1   14   11.54   11.77   0.00   0.00   0.00   161.56   164.78   0.00   0.00   0.00 0.00   -161.56
2451813   65495   11711   43006   591617   3428   12839   7   221   1   58   4.57   5.34   3.52   0.00   204.16   265.06   309.72   0.00   0.00   204.16 204.16   -60.90

spark-sql (default)> select * from store_sales_delta_update1;
ss_sold_date_sk   ss_sold_time_sk   ss_item_sk   ss_customer_sk   ss_cdemo_sk   ss_hdemo_sk   ss_addr_sk   ss_store_sk   ss_promo_sk   ss_ticket_number ss_quantity   ss_wholesale_cost   ss_list_price   ss_sales_price   ss_ext_discount_amt   ss_ext_sales_price   ss_ext_wholesale_cost   ss_ext_list_price ss_ext_tax   ss_coupon_amt   ss_net_paid   ss_net_paid_inc_tax   ss_net_profit
2452252   31384   32411   19575   1540941   1860   29226   31   232   82355   56   4.33   5.49   3.12   0.00   174.72   242.48   307.44   12.23   0.00   174.72 186.95   99.00
2452252   31384   9427   19575   1540941   1860   29226   31   320   82355   17   74.73   75.47   24.90   0.00   423.30   1270.41   1282.99   4.23   0.00   423.30 427.53   99.00
2452252   31384   3839   19575   1540941   1860   29226   31   53   82355   13   2.40   3.12   3.08   0.00   40.04   31.20   40.56   2.00   0.00   40.04 42.04   99.00
2452252   31384   6785   19575   1540941   1860   29226   31   80   82355   1   10.82   21.53   8.82   0.00   8.82   10.82   21.53   0.17   0.00   8.82 8.99   99.00
2452252   31384   4937   19575   1540941   1860   29226   31   54   82355   86   69.50   93.13   50.29   3978.94   4324.94   5977.00   8009.18   17.30   3978.94   346.00 363.30   99.00
2452252   31384   11491   19575   1540941   1860   29226   31   294   82355   53   34.65   44.35   39.91   1797.94   2115.23   1836.45   2350.55   15.86   1797.94   317.29 333.15   99.00
2452252   31384   1963   19575   1540941   1860   29226   31   288   82355   49   30.68   35.89   3.94   61.77   193.06   1503.32   1758.61   3.93   61.77   131.29 135.22   99.00
2452252   31384   22539   19575   1540941   1860   29226   31   50   82355   100   1.87   2.09   2.04   0.00   204.00   187.00   209.00   16.32   0.00   204.00 220.32   99.00
2451813   65495   23993   43006   591617   3428   12839   7   37   1   79   11.41   18.71   2.80   99.54   221.20   901.39   1478.09   6.08   99.54   121.66 127.74   88.00
2451813   65495   18007   43006   591617   3428   12839   7   246   1   37   63.63   101.17   41.47   46.03   1534.39   2354.31   3743.29   59.53   46.03   1488.361547.89   88.00
2451813   65495   17690   43006   591617   3428   12839   7   184   1   99   80.52   137.68   83.98   0.00   8314.02   7971.48   13630.32   0.00   0.00 8314.02   8314.02   88.00
2451813   65495   3404   43006   591617   3428   12839   7   284   1   14   57.37   76.30   6.10   0.00   85.40   803.18   1068.20   0.00   0.00   85.40 85.40   88.00
2451813   65495   29726   43006   591617   3428   12839   7   8   1   100   25.08   36.86   0.73   0.00   73.00   2508.00   3686.00   6.57   0.00   73.00 79.57   88.00
2452179   NULL   21851   160943   NULL   2835   68181   NULL   198   85336   NULL   86.84   171.94   118.63   0.00   NULL   7728.76   NULL   211.16   0.00   NULL 10769.23   NULL
2452179   55757   15525   160943   1436689   2835   68181   14   11   85336   37   93.46   144.86   44.90   0.00   1661.30   3458.02   5359.82   99.67   0.00   1661.301760.97   -1796.72
2452179   55757   27563   160943   1436689   2835   68181   14   129   85336   22   49.50   80.19   49.71   0.00   1093.62   1089.00   1764.18   32.80   0.00   1093.621126.42   4.62
2452179   55757   12731   160943   1436689   2835   68181   14   136   85336   86   53.95   72.29   7.22   0.00   620.92   4639.70   6216.94   37.25   0.00   620.92 658.17   -4018.78
2452179   55757   4529   160943   1436689   2835   68181   14   120   85336   58   42.79   74.45   67.74   0.00   3928.92   2481.82   4318.10   39.28   0.00   3928.923968.20   1447.10

12、执行MERGE INTO 操作：

MERGE INTO store_sales_delta
USING store_sales_delta_update1
ON (store_sales_delta.ss_sold_date_sk = store_sales_delta_update1.ss_sold_date_sk) and (store_sales_delta.ss_item_sk= store_sales_delta_update1.ss_item_sk) and (store_sales_delta.ss_customer_sk=store_sales_delta_update1.ss_customer_sk)
WHEN MATCHED THEN
UPDATE SET
ss_net_profit = store_sales_delta_update1.ss_net_profit
WHEN NOT MATCHED
THEN INSERT (
ss_sold_date_sk,
ss_sold_time_sk,
ss_item_sk,
ss_customer_sk,
ss_cdemo_sk,
ss_hdemo_sk,
ss_addr_sk,
ss_store_sk,
ss_promo_sk,
ss_ticket_number,
ss_quantity,
ss_wholesale_cost,
ss_list_price,
ss_sales_price,
ss_ext_discount_amt,
ss_ext_sales_price,
ss_ext_wholesale_cost,
ss_ext_list_price,
ss_ext_tax,
ss_coupon_amt,
ss_net_paid,
ss_net_paid_inc_tax,
ss_net_profit
)
VALUES (
store_sales_delta_update1.ss_sold_date_sk,
store_sales_delta_update1.ss_sold_time_sk,
store_sales_delta_update1.ss_item_sk,
store_sales_delta_update1.ss_customer_sk,
store_sales_delta_update1.ss_cdemo_sk,
store_sales_delta_update1.ss_hdemo_sk,
store_sales_delta_update1.ss_addr_sk,
store_sales_delta_update1.ss_store_sk,
store_sales_delta_update1.ss_promo_sk,
store_sales_delta_update1.ss_ticket_number,
store_sales_delta_update1.ss_quantity,
store_sales_delta_update1.ss_wholesale_cost,
store_sales_delta_update1.ss_list_price,
store_sales_delta_update1.ss_sales_price,
store_sales_delta_update1.ss_ext_discount_amt,
store_sales_delta_update1.ss_ext_sales_price,
store_sales_delta_update1.ss_ext_wholesale_cost,
store_sales_delta_update1.ss_ext_list_price,
store_sales_delta_update1.ss_ext_tax,
store_sales_delta_update1.ss_coupon_amt,
store_sales_delta_update1.ss_net_paid,
store_sales_delta_update1.ss_net_paid_inc_tax,
store_sales_delta_update1.ss_net_profit
);

13、查询更新后表结果：

spark-sql (default)> select * from store_sales_delta;
ss_sold_date_sk   ss_sold_time_sk   ss_item_sk   ss_customer_sk   ss_cdemo_sk   ss_hdemo_sk   ss_addr_sk   ss_store_sk   ss_promo_sk   ss_ticket_number ss_quantity   ss_wholesale_cost   ss_list_price   ss_sales_price   ss_ext_discount_amt   ss_ext_sales_price   ss_ext_wholesale_cost   ss_ext_list_price ss_ext_tax   ss_coupon_amt   ss_net_paid   ss_net_paid_inc_tax   ss_net_profit
NULL   NULL   12183   NULL   NULL   NULL   NULL   31   NULL   82355   NULL   34.52   NULL   22.88   NULL   NULL   517.80   880.20   NULL   NULL   NULL NULL   NULL
2451813   65495   2672   43006   591617   3428   12839   7   247   1   72   84.72   111.83   61.50   0.00   4428.00   6099.84   8051.76   177.12   0.00   4428.004605.12   -1671.84
2451813   65495   3404   43006   591617   3428   12839   7   284   1   14   57.37   76.30   6.10   0.00   85.40   803.18   1068.20   0.00   0.00   85.40 85.40   88.00
2451813   65495   11711   43006   591617   3428   12839   7   221   1   58   4.57   5.34   3.52   0.00   204.16   265.06   309.72   0.00   0.00   204.16 204.16   -60.90
2451813   65495   17690   43006   591617   3428   12839   7   184   1   99   80.52   137.68   83.98   0.00   8314.02   7971.48   13630.32   0.00   0.00 8314.02   8314.02   88.00
2451813   65495   18007   43006   591617   3428   12839   7   246   1   37   63.63   101.17   41.47   46.03   1534.39   2354.31   3743.29   59.53   46.03   1488.361547.89   88.00
2451813   65495   19141   43006   591617   3428   12839   7   150   1   5   10.68   15.91   6.68   0.00   33.40   53.40   79.55   2.33   0.00   33.40 35.73   -20.00
2451813   65495   23993   43006   591617   3428   12839   7   37   1   79   11.41   18.71   2.80   99.54   221.20   901.39   1478.09   6.08   99.54   121.66 127.74   88.00
2451813   65495   24842   43006   591617   3428   12839   7   335   1   91   93.48   108.43   93.24   0.00   8484.84   8506.68   9867.13   254.54   0.00   8484.848739.38   -21.84
2451813   65495   29726   43006   591617   3428   12839   7   8   1   100   25.08   36.86   0.73   0.00   73.00   2508.00   3686.00   6.57   0.00   73.00 79.57   88.00
2451813   65495   31412   43006   591617   3428   12839   7   2   1   14   11.54   11.77   0.00   0.00   0.00   161.56   164.78   0.00   0.00   0.00 0.00   -161.56
2452128   50003   10247   94588   588704   4067   29383   16   119   82356   41   2.99   3.73   3.31   0.00   135.71   122.59   152.93   4.07   0.00   135.71 139.78   13.12
2452179   55757   4529   160943   1436689   2835   68181   14   120   85336   58   42.79   74.45   67.74   0.00   3928.92   2481.82   4318.10   39.28   0.00   3928.923968.20   1447.10
2452179   55757   12731   160943   1436689   2835   68181   14   136   85336   86   53.95   72.29   7.22   0.00   620.92   4639.70   6216.94   37.25   0.00   620.92 658.17   -4018.78
2452179   55757   15525   160943   1436689   2835   68181   14   11   85336   37   93.46   144.86   44.90   0.00   1661.30   3458.02   5359.82   99.67   0.00   1661.301760.97   -1796.72
2452179   NULL   21851   160943   NULL   2835   68181   NULL   198   85336   NULL   86.84   171.94   118.63   0.00   NULL   7728.76   NULL   211.16   0.00   NULL 10769.23   NULL
2452179   55757   27563   160943   1436689   2835   68181   14   129   85336   22   49.50   80.19   49.71   0.00   1093.62   1089.00   1764.18   32.80   0.00   1093.621126.42   4.62
2452252   31384   1963   19575   1540941   1860   29226   31   288   82355   49   30.68   35.89   3.94   61.77   193.06   1503.32   1758.61   3.93   61.77   131.29 135.22   99.00
2452252   31384   3839   19575   1540941   1860   29226   31   53   82355   13   2.40   3.12   3.08   0.00   40.04   31.20   40.56   2.00   0.00   40.04 42.04   99.00
2452252   31384   4937   19575   1540941   1860   29226   31   54   82355   86   69.50   93.13   50.29   3978.94   4324.94   5977.00   8009.18   17.30   3978.94   346.00 363.30   99.00
2452252   31384   6785   19575   1540941   1860   29226   31   80   82355   1   10.82   21.53   8.82   0.00   8.82   10.82   21.53   0.17   0.00   8.82 8.99   99.00
2452252   31384   9427   19575   1540941   1860   29226   31   320   82355   17   74.73   75.47   24.90   0.00   423.30   1270.41   1282.99   4.23   0.00   423.30 427.53   99.00
2452252   31384   11491   19575   1540941   1860   29226   31   294   82355   53   34.65   44.35   39.91   1797.94   2115.23   1836.45   2350.55   15.86   1797.94   317.29 333.15   99.00
2452252   31384   22539   19575   1540941   1860   29226   31   50   82355   100   1.87   2.09   2.04   0.00   204.00   187.00   209.00   16.32   0.00   204.00 220.32   99.00
2452252   31384   32411   19575   1540941   1860   29226   31   232   82355   56   4.33   5.49   3.12   0.00   174.72   242.48   307.44   12.23   0.00   174.72 186.95   99.00