templates.write-strategies.duckdb.create.j2 Maven / Gradle / Ivy

Go to download

{% if materializedView == 'MATERIALIZED_VIEW' %}
{% set createPrefix = 'CREATE MATERIALIZED VIEW' %}
{% else %}
{% set createPrefix = 'CREATE TABLE' %}
{% endif %}

{% if strategyOn == 'TARGET' %}

{{ createPrefix }} {{ tableFullName }} AS {{selectStatement }};

{% elif strategyOn == 'SOURCE_AND_TARGET' and strategyTimestamp == '' %}

{{ createPrefix }} {{ tableFullName }} AS
WITH SL_INCOMING AS ({{selectStatement }}),
SL_VIEW_WITH_ROWNUM AS (SELECT {{ tableColumnsCsv }}, ROW_NUMBER() OVER (PARTITION BY {{ strategyKeyCsv }} ORDER BY (select 0)) AS SL_SEQ FROM SL_INCOMING)
SELECT  {{ tableColumnsCsv }} FROM SL_VIEW_WITH_ROWNUM WHERE SL_SEQ = 1;

{% else %} {# strategyOn == 'SOURCE_AND_TARGET' and timestamp is present #}

{{ createPrefix }} {{ tableFullName }} AS
WITH SL_INCOMING AS ({{ selectStatement }}),
SL_VIEW_WITH_ROWNUM AS (
  SELECT  {{ tableColumnsCsv }},
          ROW_NUMBER() OVER (PARTITION BY  {{ strategyKeyCsv }} ORDER BY {{ strategyTimestamp }} DESC) AS SL_SEQ
  FROM SL_INCOMING
)
SELECT {{ tableColumnsCsv }} FROM SL_VIEW_WITH_ROWNUM WHERE SL_SEQ = 1;
""".stripMargin

{%  endif %}


{% if strategyType == 'SCD2' %}
ALTER TABLE {{ tableFullName }} ADD COLUMN {{ strategyStartTs }} TIMESTAMP;
ALTER TABLE {{ tableFullName }} ADD COLUMN {{ strategyEndTs }} TIMESTAMP;
{% endif %}