-
Notifications
You must be signed in to change notification settings - Fork 113
/
hash.sql
121 lines (79 loc) · 4.03 KB
/
hash.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
/*
* Copyright (c) Business Thinking Ltd. 2019-2022
* This software includes code developed by the dbtvault Team at Business Thinking Ltd. Trading as Datavault
*/
{%- macro hash(columns=none, alias=none, is_hashdiff=false, columns_to_escape=none) -%}
{%- if is_hashdiff is none -%}
{%- set is_hashdiff = false -%}
{%- endif -%}
{{- adapter.dispatch('hash', 'dbtvault')(columns=columns, alias=alias,
is_hashdiff=is_hashdiff, columns_to_escape=columns_to_escape) -}}
{%- endmacro %}
{%- macro default__hash(columns, alias, is_hashdiff, columns_to_escape) -%}
{%- set hash = var('hash', 'md5') -%}
{%- set concat_string = var('concat_string', '||') -%}
{%- set null_placeholder_string = var('null_placeholder_string', '^^') -%}
{%- set hash_alg = dbtvault.select_hash_alg(hash) -%}
{%- set standardise = dbtvault.standard_column_wrapper() %}
{#- Alpha sort columns before hashing if a hashdiff -#}
{%- if is_hashdiff and dbtvault.is_list(columns) -%}
{%- set columns = columns|sort -%}
{%- endif -%}
{#- If single column to hash -#}
{%- if columns is string -%}
{%- set column_str = dbtvault.as_constant(columns) -%}
{%- if dbtvault.is_something(columns_to_escape) -%}
{%- if column_str in columns_to_escape -%}
{%- set column_str = dbtvault.escape_column_name(column_str) -%}
{%- endif -%}
{%- endif -%}
{{ hash_alg | replace('[HASH_STRING_PLACEHOLDER]', standardise | replace('[EXPRESSION]', column_str)) }} AS {{ alias | indent(4) }}
{#- Else a list of columns to hash -#}
{%- else -%}
{%- set all_null = [] -%}
{%- set processed_columns = [] -%}
{%- for column in columns -%}
{%- if dbtvault.is_something(columns_to_escape) -%}
{%- if column in columns_to_escape -%}
{%- set column = dbtvault.escape_column_name(column) -%}
{%- endif -%}
{%- endif -%}
{%- set column_str = dbtvault.as_constant(column) -%}
{%- set column_expression = dbtvault.null_expression(column_str) -%}
{%- do all_null.append(null_placeholder_string) -%}
{%- do processed_columns.append(column_expression) -%}
{% endfor -%}
{% if not is_hashdiff -%}
{%- set concat_sql -%}
NULLIF({{ dbtvault.concat_ws(processed_columns, separator=concat_string) -}} {{ ', ' -}}
'{{ all_null | join(concat_string) }}')
{%- endset -%}
{%- set hashed_column -%}
{{ hash_alg | replace('[HASH_STRING_PLACEHOLDER]', concat_sql) }} AS {{ alias }}
{%- endset -%}
{%- else -%}
{% if dbtvault.is_list(processed_columns) and processed_columns | length > 1 %}
{%- set hashed_column -%}
{{ hash_alg | replace('[HASH_STRING_PLACEHOLDER]', dbtvault.concat_ws(processed_columns, separator=concat_string)) }} AS {{ alias }}
{%- endset -%}
{%- else -%}
{%- set hashed_column -%}
{{ hash_alg | replace('[HASH_STRING_PLACEHOLDER]', processed_columns[0]) }} AS {{ alias }}
{%- endset -%}
{%- endif -%}
{%- endif -%}
{{ hashed_column }}
{%- endif -%}
{%- endmacro -%}
{%- macro bigquery__hash(columns, alias, is_hashdiff, columns_to_escape) -%}
{{ dbtvault.default__hash(columns=columns, alias=alias, is_hashdiff=is_hashdiff, columns_to_escape=columns_to_escape) }}
{%- endmacro -%}
{%- macro sqlserver__hash(columns, alias, is_hashdiff, columns_to_escape) -%}
{{ dbtvault.default__hash(columns=columns, alias=alias, is_hashdiff=is_hashdiff, columns_to_escape=columns_to_escape) }}
{%- endmacro -%}
{%- macro postgres__hash(columns, alias, is_hashdiff, columns_to_escape) -%}
{{ dbtvault.default__hash(columns=columns, alias=alias, is_hashdiff=is_hashdiff, columns_to_escape=columns_to_escape) }}
{%- endmacro -%}
{%- macro databricks__hash(columns, alias, is_hashdiff, columns_to_escape) -%}
{{ dbtvault.default__hash(columns=columns, alias=alias, is_hashdiff=is_hashdiff, columns_to_escape=columns_to_escape) }}
{%- endmacro -%}