Any clue where / why this error might happen - "Parser Error: syntax error at or near "-" "
Opened this issue · 5 comments
Hi there,
Given the latest version of duckdb and dbt-core and using the below configuration, i'm bumping into an issue where the debug error message doesn't help at all where run dbt --debug docs generate
Note, if i have different targets of type bigquery and run dbt run
it compiles and works as expected, no issues. Is just when using with duckdb ....
profile.yml
# This dbt profiles.yml config file.
dwh:
docs: # dummy duckdb adapter. Currently this is a workaround to create documentation without connecting to a real DB (Ref:https://github.com/dbt-labs/dbt-core/issues/3947)
path: /tmp/dbt.duckdb
type: duckdb
in dbt_profile.yml
i have defined a variable as below
vars:
gcp_billing_datasets: ['project-bq-export.gcp_billing']
in the model sql file i have
{{ config(
materialized='incremental',
incremental_strategy='insert_overwrite',
partition_by={
"field": "usage_date",
"data_type": "DATE",
"granularity": "DAY"
}
)
}}
WITH billing_data AS (
{%- for dataset in var("gcp_billing_datasets") -%}
{% set dataset_query %}
SELECT CONCAT("{{ dataset }}.", table_name) AS table_name
FROM {{ dataset }}.INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE '%resource_v1%'
{% endset %}
and the error is
09:45:46 DuckDB adapter: Error running SQL:
SELECT CONCAT("project-bq-export.gcp_billing.", table_name) AS table_name
FROM project-bq-export.gcp_billing.INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE '%resource_v1%'
09:45:46 DuckDB adapter: Rolling back transaction.
09:45:46 On model.dwh.dwh_gcp_billing: Close
09:45:46 Runtime Error in model dwh_gcp_billing (models/gcp/warehouse/dwh_gcp_billing.sql)
Parser Error: syntax error at or near "-"
Now based on my understanding i don't need to provide the backticks to FROM
{{ dataset }}.INFORMATION_SCHEMA.TABLES
as i don't have any special chars. However i've tried to dig into your code as well as dbt-core ( i.e https://github.com/dbt-labs/dbt-core/tree/main/core/dbt/parser ) but couldn't find much.
Due to lack of more debug info i'm a bit in the dark, could the issue arises due to the way dbt handles string interpolation and how DuckDB interprets identifiers. ?
Mmm I think there are a few problems here.
- You definitely need to add
"
around the{{ dataset }}
variable in the model SQL; dbt doesn't do that for you for variables, only for relations it controls. - I don't understand why the resulting
"project-bq-export.gcp_billing".INFORMATION_SCHEMA.TABLES
relation exists-- what isproject-bq-export.gcp_billing
? Is it a DuckDB database? Something else?
hi @jwills , thank you for taking the time to respond
You definitely need to add " around the {{ dataset }} variable in the model SQL; dbt doesn't do that for you for variables, only for relations it controls.
ack, will do so
I don't understand why the resulting "project-bq-export.gcp_billing".INFORMATION_SCHEMA.TABLES relation exists-- what is project-bq-export.gcp_billing? Is it a DuckDB database? Something else?
this is not a duckDB db, is the
and the result is to build a dynamic list of tables which belongs to the dataset (which exist in a non US region) so we can loop over it
does that make sense?
@DanyC97 that does make sense, but I don't see how DuckDB would help with that problem since I assume you need to use BigQuery to read that data; dbt-duckdb doesn't support queries that are intended to run against BigQuery.
i understand, make sense.
Maybe a small help with be the error message return to provide a bit more meaningful info although i'm not sure if that is on dbt-core side or dbt-duckdb