Feature Request: Support for building CREATE OR REPLACE EXTERNAL TABLE statements

[colemandsmith]

Hello all, I'd like to request support for building CREATE OR REPLACE EXTERNAL TABLE statements in bigquery_sqlalchemy in a similar manner to SQLAlchemy core.

I often use SQLAlchemy core to build SQL queries to execute against BigQuery. For simpler DDL/DML use cases, compiling with BigQueryDialect works great! But for some more specific features of BigQuery, like making external tables, I end up building queries as text/strings, which is not ideal, as I tend to want to avoid templating SQL strings.

Many sqlalchemy extension libraries provide helpers to build queries for their more bespoke functions, such as copying tables to CSVs in Snowflake. It would be nice to have this sort of helper for building external table creation queries in bigquery_sqlalchemy.

[tswast]

@colemandsmith This is a good suggestion. Would you mind mocking out a prototype of a code sample using this feature? It'd be helpful to understand what kind of interface you'd expect.

[colemandsmith]

@tswast Sure, I can take a stab at that that relatively soon

[colemandsmith]

For the specific purposes of creating an external table, I would imagine handling a SQLAlchemy table object, some sort of formatter object (specific to the imported file type), and having a special CreateExternalTable class (much like sqlalchemy.schema.CreateTable) that would make a compilable query.

from sqlalchemy import Table, Column, String
from sqlalchemy_bigquery import BigQueryDialect, CreateExternalTable, CSVFormatter

…

csv_formatter = (
    CSVFormatter(uris=[“gs://bucket/path/to/file.csv“])
    .skip_leading_rows(1)
    .allow_quoted_newlines(True)
)
new_table = Table(“new_external_table”, meta, Column(“name”, String, nullable=False))
create_table = CreateExternalTable(new_table, formatter=csv_formatter, replace=True)

# compiled output from create_table.compile(dialect=BigQueryDialect()) looks like:
"""
CREATE OR REPLACE EXTERNAL TABLE new_table (
    name STRING NOT NULL
)
OPTIONS (
    format = ‘CSV’,
    uris = ['gs://bucket/path/to/file.csv']
    skip_leading_rows = 1
    allow_quoted_newlines = true
)
"""

This is a rough sketch, but comes into line with what I expect from SQLAlchemy and various extension packages I've used. Other utility functions would similarly likely follow a pattern of having a class per function that exposes options and arguments, e.g.

CreateMaterializedView(
    view_table_definition,
    query,
    partition_by=view_table_definition.columns["subscription_date"]
)