Working with BigQuery STRUCT dataΒΆ
The BigQuery STRUCT data type provided data that are collections of named fields.
sqlalchemy-bigquery provided a STRUCT type that can be used to define tables with STRUCT columns:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Float
from sqlalchemy_bigquery import STRUCT
Base = declarative_base()
class Car(Base):
__tablename__ = "Cars"
model = Column(String, primary_key=True)
engine = Column(
STRUCT(
cylinder=STRUCT(("count", Integer),
("compression", Float)),
horsepower=Integer)
)
STRUCT types can be nested, as in this example. Struct fields can be defined in two ways:
Fields can be provided as keyword arguments, as in the cylinder and horsepower fields in this example.
Fields can be provided as name-type tuples provided as positional arguments, as with the count and compression fields in this example.
STRUCT columns are automatically created when existing database tables containing STRUCT columns are introspected.
Struct data are represented in Python as Python dictionaries:
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
sebring = Car(model="Sebring",
engine=dict(
cylinder=dict(
count=6,
compression=18.0),
horsepower=235))
townc = Car(model="Town and Counttry",
engine=dict(
cylinder=dict(
count=6,
compression=16.0),
horsepower=251))
xj8 = Car(model="XJ8",
engine=dict(
cylinder=dict(
count=8,
compression=10.75),
horsepower=575))
session.add_all((sebring, townc, xj8))
session.commit()
When querying struct fields, you can use attribute access syntax:
sixes = session.query(Car).filter(Car.engine.cylinder.count == 6)
or mapping access:
sixes = session.query(Car).filter(Car.engine["cylinder"]["count"] == 6)
and field names are case insensitive:
sixes = session.query(Car).filter(Car.engine.CYLINDER.COUNT == 6)
When using attribute-access syntax, field names may conflict with column attribute names. For example SQLAlchemy columns have name and type attributes, among others. When accessing a field whose name conflicts with a column attribute name, either use mapping access, or spell the field name with upper-case letters.
