How to create a SQLAlchemy model on BigQuery?

Question

After reading the following link https://github.com/googleapis/python-bigquery-sqlalchemy I managed to query a table stored on Google BigQuery by using SLQAlchemy. Now I would like to create a SQLAlchemy Users model on Google BigQuery so that I can use Flask-login features (e.g. UserMixin) in order to validate whether a user is authenticated, active, etc. but in this case my database is stored on BigQuery instead of a traditional SQL database (mainly due to billing costs as I find Google Cloud SQL way more expensive than Google BigQuery).

This is my code:

from sqlalchemy.engine import create_engine
from flask_sqlalchemy import SQLAlchemy

engine = create_engine('bigquery://my_project',credentials_path='my_credentials.json')

db = SQLAlchemy()

class Users(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(15), unique=True, nullable = False)
    email = db.Column(db.String(50), unique=True)

Users.metadata.create_all(engine)

Unfortunately I'm getting the following error:

DatabaseError: (google.cloud.bigquery.dbapi.exceptions.DatabaseError) 400 Table "users" must be qualified with a dataset (e.g. dataset.table).

I tried to modify the engine variable as follows:

engine = create_engine('bigquery://my_project.my_dataset',credentials_path='my_credentials.json')

but then I get the following error (I'm intrigued by the None part):

ValueError: table_id must be a fully-qualified ID in standard SQL format, e.g., "project.dataset.table_id", got my_project.my_dataset.None.users

Does anyone know how can I create a SQLAlchemy model on Google BigQuery?

Answer 1

Can you try this:

from sqlalchemy.engine import create_engine
from flask_sqlalchemy import SQLAlchemy
from pybigquery.api import ApiClient
from flask import Flask


db = SQLAlchemy()

#ToDo:Change project name and dataset name.
engine = create_engine('bigquery://my-project/my-dataset')

db = SQLAlchemy()

class Users(db.Model):
   id = db.Column(db.Integer, primary_key=True)
   username = db.Column(db.String(15), unique=True, nullable = False)
   email = db.Column(db.String(50), unique=True)
Users.metadata.create_all(engine)

I solved the error by adding the dataset name. engine = create_engine('bigquery://my-project/my-dataset')