• Home
• ai.starlake
• starlake-spark3_2.12
• 1.3.0
• source code
• upsert_by_key_and_timestamp.j2

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

templates.write-strategies.spark.upsert_by_key_and_timestamp.j2 Maven / Gradle / Ivy

CREATE OR REPLACE TEMPORARY VIEW SL_INCOMING AS {{ selectStatement }};

{% if strategyOn == 'TARGET' %}

MERGE INTO  {{ tableFullName }} SL_EXISTING USING SL_INCOMING ON ( {{ strategyKeyJoinCondition }})
WHEN MATCHED AND SL_INCOMING.{{ strategyTimestamp }} > SL_EXISTING.{{ strategyTimestamp }} THEN  UPDATE {{ tableUpdateSetExpression }}
WHEN NOT MATCHED THEN {{ tableInsert }}

{% else %} {# strategyOn == 'SOURCE_AND_TARGET' #}

CREATE OR REPLACE TEMPORARY VIEW SL_VIEW_WITH_ROWNUM AS
SELECT  *, ROW_NUMBER() OVER (PARTITION BY {{ strategyKeyCsv }} ORDER BY {{ strategyTimestamp }} DESC) AS SL_SEQ
FROM SL_INCOMING;

CREATE OR REPLACE TEMPORARY VIEW SL_DEDUP AS SELECT {{ tableColumnsCsv }} 
FROM SL_VIEW_WITH_ROWNUM 
WHERE SL_SEQ = 1;

MERGE INTO {{ tableFullName }} SL_EXISTING 
USING SL_DEDUP AS SL_INCOMING ON ( {{ strategyKeyJoinCondition }})
WHEN MATCHED AND SL_INCOMING.{{ strategyTimestamp }} > SL_EXISTING.{{ strategyTimestamp }} THEN  UPDATE {{ tableUpdateSetExpression }}
WHEN NOT MATCHED THEN {{ tableInsert }}


{%  endif %}