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

Go to download

{#

{{ sinkConnectionRef }}
{{ sinkClustering }}
{{ sinkDays }}
{{ sinkRequirePartitionFilter }}
{{ sinkEnableRefresh }}
{{ sinkRefreshIntervalMs }}
{{ sinkId }}
{{ sinkFormat }}
{{ sinkExtension }}
{{ sinkPartition }}
{{ sinkCoalesce }}
{{ sinkOptions }}
{{ sinkTableOptionsClause }}
{{ sinkTablePartitionClause }}
{{ sinkTableClusteringClause }}

{{ tableDatabase }}
{{ tableDomain }}
{{ tableName }}
{{ tableColumnNames }}
{{ quotedTableColumnNames }}
{{ tableFullName }}
{{ selectStatement }}
{{ tableExists }}
{{ tableTruncate }}
{{ tableParamsForInsertSql }}
{{ tableParamsForUpdateSql }}
{{ tableInsert }}
UPDATE {{ tableUpdateSetExpression }}
{{ tableColumnsCsv }}
{{ tableIncomingColumnsCsv }}
{{ materializedView }}

{{ engineQuote }}
{{ engineCanMerge }}
{{ engineViewPrefix }}
{{ enginePreActions }}
{{ engineStrategyBuilder }}

{{ strategyType }}
{{ strategyTypes }}
{{ strategyKey }}
{{ strategyTimestamp }}
{{ strategyQueryFilter }}
{{ strategyOn }}
{{ strategyStartTs }}
{{ strategyEndTs }}
{{ strategyKeyCsv }}
{{ strategyKeyJoinCondition }}

#}

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

{% if strategyOn == 'TARGET' %}

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

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

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

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

{{ createPrefix }} {{ tableFullName }} AS
WITH SL_INCOMING AS ({{ selectStatement }})
SELECT  {{ tableColumnsCsv }},
FROM SL_INCOMING
QUALIFY ROW_NUMBER() OVER (PARTITION BY {{ strategyKeyCsv }} ORDER BY {{ strategyTimestamp }} DESC) = 1;

{%  endif %}


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