Skip to content

Instantly share code, notes, and snippets.

@sungchun12
Created May 10, 2022 21:03
Show Gist options
  • Save sungchun12/f7ea081773ae824a83294649530d6e41 to your computer and use it in GitHub Desktop.
Save sungchun12/f7ea081773ae824a83294649530d6e41 to your computer and use it in GitHub Desktop.
BigQuery: Table with Constraints Custom Materialization
{{
config(
materialized = "table_with_constraints"
)
}}
select
1 as id,
'blue' as color,
cast('2019-01-01' as date) as date_day
version: 2
models:
- name: table_constraints_demo
config:
has_constraints: true
columns:
- name: id
data_type: int64
description: I want to describe this one, but I don't want to list all the columns
meta:
constraint: not null
- name: color
data_type: string
- name: date_day
data_type: date
{%- materialization table_with_constraints, adapter='bigquery' -%}
--prepare database for new model
{%- set identifier = this.identifier -%}
{%- set old_relation = adapter.get_relation(database=database, schema=schema, identifier=identifier) -%}
-- drop original table if it exists
{% if old_relation is not none %}
{% do adapter.drop_relation(old_relation) %}
{% endif %}
--set the new target table
{%- set target_relation = api.Relation.create(
identifier=identifier, schema=schema, database=database,
type='table') -%}
--optional configs
{%- set raw_partition_by = config.get('partition_by', none) -%}
{%- set raw_cluster_by = config.get('cluster_by', none) -%}
{%- set sql_header = config.get('sql_header', none) -%}
{%- set partition_config = adapter.parse_partition_by(raw_partition_by) -%}
{{ sql_header if sql_header is not none }}
{%- set user_provided_columns = model['columns'] -%}
{% call statement('main') -%}
create or replace table {{ target_relation }}
{% if config.get('has_constraints', False) %}
(
{% for i in user_provided_columns %}
{% set col = user_provided_columns[i] %}
{{ col['name'] }} {{ col['data_type'] }} {{ col['meta']['constraint'] or "" }} {{ "," if not loop.last }}
{% endfor %}
)
{% endif %}
{{ partition_by(partition_config) }}
{{ cluster_by(raw_cluster_by) }}
{{ bigquery_table_options(config, model, temporary) }}
as (
{{ sql }}
);
{%- endcall %}
{{ return({'relations': [target_relation]}) }}
{%- endmaterialization -%}
@sungchun12
Copy link
Author

sungchun12 commented May 10, 2022

Demo video: here

@sungchun12
Copy link
Author

sungchun12 commented Jul 11, 2022

Where should I copy and paste these files?

models/: table_constraints_schema.yml, table_constraints_demo.sql
macros/: table_with_constraints.sql

@sungchun12
Copy link
Author

BigQuery documentation on schema constraints: https://cloud.google.com/bigquery/docs/schemas#modes

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment