Invalid SQL Syntax for create or replace table in BigQuery

ward-resa commented 1 year ago

Describe the bug

As of today we started getting an error on the dbt run-operation stage_external_sources in our Production environmnent. According to the detail log the name for the field is invalid SQL syntax. It appears that the field name is not being generated correctly.

Steps to reproduce

This was only reproducible in the Production environment. I had to reclone the master branch into the Cloud IDE and then manually clean the target and packages folder. This fails again. I then checked the dev branch out again. After working with my dev branch it now fails consistently as well.

dbt run-operation stage_external_sources

Expected results

I expect the field name to be generated based on the yml file specification.

Actual results

This is the generated script.

create or replace external table `removed for the posting`(

                <property object at 0x7fdbae2c5220> STRING,
                <property object at 0x7fdbae2c5220> STRING,
                <property object at 0x7fdbae2c5220> STRING,
                <property object at 0x7fdbae2c5220> INT64)

        options (
            uris = ['https://docs.google.com/spreadsheets/d/removedsheetid/'], format = 'GOOGLE_SHEETS', skip_leading_rows = 1, sheet_range = 'users')

Screenshots and log output

On macro_stage_external_sources: / {"app": "dbt", "dbt_version": "1.2.1", "profile_name": "user", "target_name": "dev", "connection_name": "macro_stage_external_sources"} /

System information

packages:

package: dbt-labs/dbt_utils version: [">=0.8.6"]
package: dbt-labs/dbt_external_tables version: [">=0.8.0"]
package: dbt-labs/codegen version: [">=0.6.0"]

Which database are you using dbt with? BigQuery

The output of dbt --version: dbt Cloud IDE I have tried versions 1.2 and up to the prerelease version of 1.3

The operating system you're using: dbt Cloud IDE

jeremyyeo commented 1 year ago

Hey @ward-74, we added a line recently that would allow quoted columns but that was placed in the wrong spot. Are you up for testing the fix? You can put this in your packages.yml file to test out the fix branch:

packages:
  - git: https://github.com/dbt-labs/dbt-external-tables.git
    revision: fix/165/bq-column-quoting

It also seems that our BQ integration tests are passing when there are syntax errors - so need to double check why this is the case.

ward-resa commented 1 year ago

Certainly, testing now.

ward-resa commented 1 year ago

Running the patched fix corrects the SQL generation for my external tables. This seems to resolve the issue completely. Thanks for the quick turnaround.

jeremyyeo commented 1 year ago

Thanks for raising and testing this - wouldn't have caught that the integration tests were wonky if you hadn't. 👍

The hub should get the new patch version in a few hours I think then your job next should work as expected.

ward-resa commented 1 year ago

I have reverted to 0.8.0, until we get the fix in. Thanks for the hard work on this VERY valuable addition to dbt!

dbt-labs / dbt-external-tables