Neat!

I know we have a dictionary from BigQuery types to SQLAlchemy types. Would it make sense to invert that dictionary and use it for these methods? I hate to have the same mapping defined in two places, but I guess this is the only place so far where we need to go from SQLAlchemy types to BigQuery types.

Definitely don't want to duplicate logic, but it looks like the mapping in _type_map isn't the same as the one I'm adding here. The _type_map maps INTEGER to sqlalchemy.types.Integer, but we need to return INT64 in the integer visitor in the sql compiler visitor. If I understand correctly, the existing _type_map maps from bigquery types to sqlalchemy types, but the compiler visitors need to map from sqlalchemy types to bigquery sql types, which are different. So maybe we do need two separate (but pretty similar) mappings. What do you think?

Yeah, you're right. That's an unfortunate fact of the fact that BigQuery still uses the legacy SQL data type names in the table API methods but we use the standard SQL data types for SQL in the DB-API connector.

Amended to add a few more common types.

Thanks @jmcarp! I've added a simple test, but it looks like sqlalchemy doesn't allow creating tables without primary keys?

E sqlalchemy.exc.ArgumentError: Mapper Mapper|Table|test_pybigquery.test_table_create could not assemble any primary key columns for mapped table 'test_pybigquery.test_table_create'

I was testing with sqlalchemy core, which apparently does support tables without primary keys. For sqlalchemy declarative, tables without primary keys aren't supported, but I added a ddl compiler that skips foreign and primary keys on table creation.

Thanks!