Support for BigQuery external tables on Google Sheets using python

[daniel-bartley]

Describe the feature

It's not yet possible to create an external table using sql.
However Google provides Python script that does the job.

Describe alternatives you've considered

https://cloud.google.com/bigquery/docs/external-data-drive#create_external_tables

from google.cloud import bigquery
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

# Construct a BigQuery client object.
client = bigquery.Client(credentials=credentials, project=project)

# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
# dataset_id = "your-project.your_dataset"

# Configure the external data source.
dataset = client.get_dataset(dataset_id)
table_id = "us_states"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
table = bigquery.Table(dataset.table(table_id), schema=schema)
external_config = bigquery.ExternalConfig("GOOGLE_SHEETS")
# Use a shareable link or grant viewing access to the email address you
# used to authenticate with BigQuery (this example Sheet is public).
sheet_url = (
    "https://docs.google.com/spreadsheets"
    "/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing"
)
external_config.source_uris = [sheet_url]
external_config.options.skip_leading_rows = 1  # Optionally skip header row.
external_config.options.range = (
    "us-states!A20:B49"  # Optionally set range of the sheet to query from.
)
table.external_data_configuration = external_config

# Create a permanent table linked to the Sheets file.
table = client.create_table(table)  # Make an API request.

# Example query to find states starting with "W".
sql = 'SELECT * FROM `{dataset_id}.{table_id}` WHERE name LIKE "W%"'

query_job = client.query(sql)  # Make an API request.

# Wait for the query to complete.
w_states = list(query_job)
print(
    f"There are {len(w_states)} states with names starting with W in the selected range."
)

Additional context

Yes. BigQuery specific.

Who will this benefit?

BigQuery external tables over Google Sheets provides a user friendly endpoint to and mechanism to get spreadsheet data into BigQuery. Creating the external tables in dbt makes them more reproducible in different environments.

[amirbtb]

Hi,
I found this gist from @thomashandorf :
https://gist.github.com/thomashandorf/0d54848ccd84448001abb2a51d48794a

It seems that it's now possible to use SQL to create BigQuery external tables over Google Sheets but I couldn't find anything about this in BigQuery documentation.

Thank you @thomashandorf for that gist 🙏🏽 !

[github-actions]

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.

[geo909]

🆙

I would like to see this feature implemented, too.