Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

get_relations_by_pattern macro fails forclone tables in BigQuery #955

Open
1 of 5 tasks
lachlanclulow opened this issue Sep 30, 2024 · 0 comments · May be fixed by #956
Open
1 of 5 tasks

get_relations_by_pattern macro fails forclone tables in BigQuery #955

lachlanclulow opened this issue Sep 30, 2024 · 0 comments · May be fixed by #956
Labels
bug Something isn't working triage

Comments

@lachlanclulow
Copy link

lachlanclulow commented Sep 30, 2024 •
edited
Loading

Compilation of models using get_relations_by_pattern macro fail if the macro returns BigQuery clone type tables.

Runtime Error
  Field "type" of type Optional[RelationType] in BigQueryRelation has invalid value 'clone'

Steps to reproduce

my_model.sql

SELECT
  1 AS a

my_model.yml

version: 2
models:
  - name: my_model

my_clone_model.sql

{%- set table_relations = dbt_utils.get_relations_by_pattern("myDataset", "%_clone") -%}
{%- for table in table_relations -%}
    {% if not loop.first %}
UNION ALL
    {% endif %}
SELECT * FROM {{ table }}
{%- endfor -%}

my_clone_model.yml

version: 2
models:
  - name: my_clone_model

Execute the following:

dbt build -s my_model
bq cp --clone -n project1:myDataset.my_model project1:myDataset.my_model_clone
dbt build -s my_clone_model

Expected results

A model (view or table) should be created based on the clone tables retrieved via dbt_utils.get_relations_by_pattern

Actual results

Compilation Error:

Field "type" of type Optional[RelationType] in BigQueryRelation has invalid value 'clone'

Screenshots and log output

❯ dbt build -s my_clone_model
01:50:33  Running with dbt=1.8.6
01:50:34  Registered adapter: bigquery=1.8.2
01:50:34  Found 164 models, 7 seeds, 692 data tests, 29 snapshots, 20 sources, 1130 macros, 37 unit tests
01:50:34  
01:50:37  Concurrency: 16 threads (target='dev')
01:50:37  
01:50:37  1 of 1 START sql view model my_dataset.my_clone_model ...... [RUN]
01:50:39  Unhandled error while executing 
Field "type" of type Optional[RelationType] in BigQueryRelation has invalid value 'clone'
01:50:39  1 of 1 ERROR creating sql view model my_dataset.my_clone_model  [ERROR in 2.28s]
01:50:39  
01:50:39  Finished running 1 view model in 0 hours 0 minutes and 4.61 seconds (4.61s).
01:50:39  
01:50:39  Completed with 1 error and 0 warnings:
01:50:39  
01:50:39    Field "type" of type Optional[RelationType] in BigQueryRelation has invalid value 'clone'
01:50:39  
01:50:39  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

System information

The contents of your packages.yml file:

packages:
  - package: Datavault-UK/automate_dv
    version: 0.10.1
    # https://hub.getdbt.com/Datavault-UK/automate_dv/latest/
  - package: calogica/dbt_expectations
    version: 0.10.3
    # https://hub.getdbt.com/calogica/dbt_expectations/latest/
  - package: dbt-labs/dbt_utils
    version: 1.1.1

Which database are you using dbt with?

  • postgres
  • redshift
  • bigquery
  • snowflake
  • other (specify: ____________)

The output of dbt --version:

❯ dbt --version
Core:
  - installed: 1.8.6
  - latest:    1.8.7 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - bigquery: 1.8.2 - Up to date!

Additional context

We should treat clone type tables as regular tables in BigQuery. From what I can tell, we can add an additional jinja block here to allow clone tables.

...
{% macro bigquery__get_table_types_sql() %}
            case table_type
                when 'BASE TABLE' then 'table'
                when 'EXTERNAL TABLE' then 'external'
                when 'MATERIALIZED VIEW' then 'materializedview'
                when 'CLONE' then 'table'
                else lower(table_type)
            end as {{ adapter.quote('table_type') }}
{% endmacro %}
...

Are you interested in contributing the fix?

Yes, see my PR
@lachlanclulow lachlanclulow added bug Something isn't working triage labels Sep 30, 2024
@lachlanclulow lachlanclulow linked a pull request Sep 30, 2024 that will close this issue
Open
5 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working triage
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Update get_table_types_sql.sql lachlanclulow/dbt-utils
1 participant
@lachlanclulow