Bigquery max_staleness configuration

[sebastiancharrier]

Describe the bug

Bigquery specific. One of the options for external tables is the metadata cache mode. In order to use this option is necessary to add the max_staleness interval. Intervals in Bigquery are passed in the format of INTERVAL 4 HOUR. All the options detected as string are passed with single quotes to bigquery 'INTERVAL 4 HOUR' Unfortunately this specific field return an error if is passed with quotes

Steps to reproduce

- name: my_table external: connection: 'aws-us-east-1.s3-data-lake' location: 's3://data/*' options: format: parquet metadata_cache_mode: automatic max_staleness: INTERVAL 4 HOUR

this is executed in bigquery

create or replace external tablemy_project.my_dataset.my_table with connectionaws-us-east-1.s3-data-lake options ( uris = ['s3://data/*'], format = 'parquet', metadata_cache_mode = 'automatic', max_staleness = ''INTERVAL 6 HOUR'')

Expected results

max_staleness needs to be passed without quotes

create or replace external tablemy_project.my_dataset.my_table with connectionaws-us-east-1.s3-data-lake options ( uris = ['s3://data/*'], format = 'parquet', metadata_cache_mode = 'automatic', max_staleness = INTERVAL 6 HOUR)

Actual results

create or replace external tablemy_project.my_dataset.my_table with connectionaws-us-east-1.s3-data-lake options ( uris = ['s3://data/*'], format = 'parquet', metadata_cache_mode = 'automatic', max_staleness = ''INTERVAL 6 HOUR'')

Error return by Bigquery:

• Option max_staleness value has type STRING which cannot be coerced to expected type INTERVAL at [9:158]

System information

`packages:

• package: dbt-labs/dbt_external_tables version: 0.8.6`

Which database are you using dbt with? Bigquery

The output of dbt --version:

1.5.0

[github-actions[bot]]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions[bot]]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

[dataders]

resolved by: #237