forked from dbt-labs/dbt-utils
-
Notifications
You must be signed in to change notification settings - Fork 0
/
get_column_values.sql
55 lines (36 loc) · 1.8 KB
/
get_column_values.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
{% macro get_column_values(table, column, order_by='count(*) desc', max_records=none, default=none) -%}
{{ return(adapter.dispatch('get_column_values', 'dbt_utils')(table, column, order_by, max_records, default)) }}
{% endmacro %}
{% macro default__get_column_values(table, column, order_by='count(*) desc', max_records=none, default=none) -%}
{#-- Prevent querying of db in parsing mode. This works because this macro does not create any new refs. #}
{%- if not execute -%}
{{ return('') }}
{% endif %}
{%- set target_relation = adapter.get_relation(database=table.database,
schema=table.schema,
identifier=table.identifier) -%}
{%- call statement('get_column_values', fetch_result=true) %}
{%- if not target_relation and default is none -%}
{{ exceptions.raise_compiler_error("In get_column_values(): relation " ~ table ~ " does not exist and no default value was provided.") }}
{%- elif not target_relation and default is not none -%}
{{ log("Relation " ~ table ~ " does not exist. Returning the default value: " ~ default) }}
{{ return(default) }}
{%- else -%}
select
{{ column }} as value
from {{ target_relation }}
group by 1
order by {{ order_by }}
{% if max_records is not none %}
limit {{ max_records }}
{% endif %}
{% endif %}
{%- endcall -%}
{%- set value_list = load_result('get_column_values') -%}
{%- if value_list and value_list['data'] -%}
{%- set values = value_list['data'] | map(attribute=0) | list %}
{{ return(values) }}
{%- else -%}
{{ return(default) }}
{%- endif -%}
{%- endmacro %}