BsoBird commented on issue #8624:
URL: https://github.com/apache/iceberg/issues/8624#issuecomment-1732480815
SQL:
```
MERGE INTO datacenter.dwd.b_std_category t using(
select
data_from,
partner,
plat_code,
uni_shop_id,
category_id,
parent_category_id,
category_name,
root_cid,
is_leaf,
tenant,
last_sync
from
(
select
data_from,
partner,
plat_code,
shop_id as uni_shop_id,
cid as category_id,
parent_cid as parent_category_id,
name as category_name,
root_cid,
is_leaf,
tenant,
modified as last_sync,
row_number() over(partition by shop_id,cid order by modified
desc) rank
from spark_catalog.dw_base_temp.category_analyse_result
) small
where rank=1
)s
on s.uni_shop_id = t.uni_shop_id and s.category_id = t.category_id
WHEN MATCHED AND s.last_sync>=t.last_sync
THEN UPDATE SET
t.data_from = s.data_from ,
t.partner = s.partner ,
t.plat_code = s.plat_code ,
t.uni_shop_id = s.uni_shop_id ,
t.category_id = s.category_id ,
t.parent_category_id = s.parent_category_id ,
t.category_name = s.category_name ,
t.root_cid = s.root_cid ,
t.is_leaf = s.is_leaf ,
t.tenant = s.tenant ,
t.last_sync = s.last_sync
WHEN NOT MATCHED THEN INSERT *
```
create sql:
```
CREATE TABLE IF NOT EXISTS datacenter.dwd.b_std_category (
data_from int,
partner string,
plat_code string,
uni_shop_id string,
category_id string,
parent_category_id string,
category_name string,
root_cid string,
is_leaf int,
tenant string,
last_sync string
)
PARTITIONED BY (bucket(64,uni_shop_id))
TBLPROPERTIES
('read.orc.vectorization.enabled'='true','write.format.default'='orc','write.orc.bloom.filter.columns'='category_id,root_cid','write.orc.compression-codec'='zstd','write.metadata.previous-versions-max'='3','write.metadata.delete-after-commit.enabled'='true')
STORED AS iceberg;
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
