generate_dbt_yml_schema(project_id, dataset_id)
Generates a DBT schema.yml using the information_schema of the generated tables for the specified <project_id> and <dataset_id>. For this function to work you need to create your own function in your dataset.
CREATE OR REPLACE FUNCTION `justfunctions.eu.generate_dbt_yml_schema`(`project_id` string, `dataset_id` string)
RETURNS string AS ((with columns as (
select '- name: ' || column_name || '' as column_statement,
table_name
from `justfunctions.eu`.INFORMATION_SCHEMA.COLUMNS
),
tables as (
select table_name,
" - name: " || table_name || "\n" ||
" columns:\n" ||
string_agg(' ' || column_statement, "\n") as yml_file
from columns
group by table_name
order by table_name
)
select
"version: 2\n" ||
"\n" ||
"models:\n" ||
string_agg(yml_file, "\n") as yml_file
from tables)
)
OPTIONS ( description = '''Generates a DBT schema.yml using the information_schema of the generated tables for the specified <project_id> and <dataset_id>. For this function to work you need to create your own function in your dataset.''')
Example
SELECT `justfunctions.eu.generate_dbt_yml_schema`("justfunctions", "eu")
version: 2
models: - name: country_distinct columns: - name: service_country - name: official_name - name: alpha_2