{"id":581,"library":"sqlalchemy-bigquery","title":"SQLAlchemy BigQuery Dialect","description":"SQLAlchemy BigQuery Dialect (sqlalchemy-bigquery) is a Python library that provides a SQLAlchemy dialect for connecting to and interacting with Google BigQuery. It enables users to leverage SQLAlchemy's Core and ORM functionalities to query, manipulate, and manage BigQuery data, treating it like a traditional relational database. The library is actively maintained by Google Cloud, with regular releases, and its current version is 1.16.0.","status":"active","version":"1.16.0","language":"python","source_language":"en","source_url":"https://github.com/googleapis/python-bigquery-sqlalchemy","tags":["sqlalchemy","bigquery","google-cloud","database","orm","sql-dialect"],"install":[{"cmd":"pip install sqlalchemy-bigquery","lang":"bash","label":"Default Install"},{"cmd":"pip install sqlalchemy-bigquery[bqstorage]","lang":"bash","label":"Install with BigQuery Storage API support"}],"dependencies":[{"reason":"Core dependency for the SQL toolkit and ORM functionality.","package":"sqlalchemy"},{"reason":"Underlying Python client for interacting with the BigQuery API.","package":"google-cloud-bigquery"},{"reason":"Google API client core library.","package":"google-api-core"},{"reason":"Google authentication library.","package":"google-auth"},{"reason":"gRPC Python for Google APIs.","package":"grpcio"},{"reason":"Core dependency for version parsing.","package":"packaging"},{"reason":"Optional: For faster data transfer, especially with large datasets, via the BigQuery Storage API.","package":"google-cloud-bigquery-storage","optional":true},{"reason":"Optional: Required by BigQuery Storage API for efficient data handling.","package":"pyarrow","optional":true}],"imports":[{"symbol":"create_engine","correct":"from sqlalchemy.engine import create_engine"},{"symbol":"Table, Column, Integer, String, MetaData","correct":"from sqlalchemy import Table, Column, Integer, String, MetaData"},{"symbol":"text","correct":"from sqlalchemy import text"}],"quickstart":{"code":"import os\nfrom sqlalchemy import create_engine, text, Table, MetaData, Column, String, Integer\n\n# Configure your Google Cloud Project ID. This can also be inferred from the environment.\n# For local development, ensure GOOGLE_APPLICATION_CREDENTIALS points to your service account key file.\nPROJECT_ID = os.environ.get('GCP_PROJECT_ID', 'your-gcp-project-id')\nDATASET_ID = os.environ.get('BIGQUERY_DATASET_ID', 'your_dataset_id')\nTABLE_NAME = os.environ.get('BIGQUERY_TABLE_NAME', 'your_table_name')\n\n# Connection string format: 'bigquery://[PROJECT_ID]/[DATASET_ID]'\n# If PROJECT_ID is omitted, it will try to infer from the environment.\n# Authentication typically happens via GOOGLE_APPLICATION_CREDENTIALS env var or gcloud CLI.\nengine = create_engine(f'bigquery://{PROJECT_ID}/{DATASET_ID}')\n\ntry:\n    # Example 1: Execute a raw SQL query\n    with engine.connect() as connection:\n        result = connection.execute(text(f\"SELECT 1 FROM `{PROJECT_ID}.{DATASET_ID}.{TABLE_NAME}` LIMIT 1\"))\n        print(f\"Raw SQL query result: {result.scalar()}\")\n\n    # Example 2: Reflect table metadata and query using SQLAlchemy Core\n    metadata = MetaData()\n    my_table = Table(TABLE_NAME, metadata, autoload_with=engine)\n\n    with engine.connect() as connection:\n        # Select all columns from the table, limit to 5 rows\n        select_stmt = my_table.select().limit(5)\n        result = connection.execute(select_stmt)\n        print(\"\\nQuerying table via SQLAlchemy Core (first 5 rows):\")\n        for row in result:\n            print(row)\n\nexcept Exception as e:\n    print(f\"An error occurred: {e}\")\n    print(\"Please ensure GOOGLE_APPLICATION_CREDENTIALS is set, your project/dataset/table exist, and you have permissions.\")\n","lang":"python","description":"This quickstart demonstrates how to establish a connection to Google BigQuery using `sqlalchemy-bigquery` and perform basic queries using both raw SQL and SQLAlchemy Core expressions. It assumes Google Cloud authentication is configured, typically via the `GOOGLE_APPLICATION_CREDENTIALS` environment variable or `gcloud` CLI."},"warnings":[{"fix":"Upgrade Python to version 3.9 or newer. The current PyPI metadata indicates support up to `<3.15`.","message":"Version 1.16.0 removed support for Python 3.8. Users on Python 3.8 or older must upgrade their Python environment to at least 3.9 to use `sqlalchemy-bigquery` 1.16.0 and later. Python 3.7 support was removed in earlier versions.","severity":"breaking","affected_versions":">=1.16.0"},{"fix":"Ensure your SQLAlchemy installation is version 1.4.x or 2.0.x.","message":"Version 1.11.0 raised the minimum required SQLAlchemy version to 1.4.x and introduced full support for SQLAlchemy 2.0. Applications using older SQLAlchemy versions (pre-1.4) will need to upgrade SQLAlchemy when updating to `sqlalchemy-bigquery` 1.11.0 or newer.","severity":"breaking","affected_versions":">=1.11.0"},{"fix":"Use the full path in your connection string or `Table` definition, e.g., `Table('my_table', metadata, schema='project_id.dataset_id')` or `engine = create_engine('bigquery://project_id/dataset_id')`.","message":"When querying tables from non-default BigQuery projects or datasets, specify the full `project.dataset.table` format for table names or `project.dataset` in the `schema` parameter for `Table` objects.","severity":"gotcha","affected_versions":"All"},{"fix":"Install with `pip install sqlalchemy-bigquery[bqstorage]`.","message":"For optimal performance when handling large datasets, especially when reading results, install `sqlalchemy-bigquery` with the `bqstorage` extra (`pip install sqlalchemy-bigquery[bqstorage]`). This leverages the BigQuery Storage API.","severity":"gotcha","affected_versions":"All"},{"fix":"Currently, a workaround is necessary, such as defining JSON columns as `String` or `LargeBinary` and handling serialization/deserialization at the application level, or awaiting future dialect updates for native JSON type compilation.","message":"The `sqlalchemy.JSON()` column type is not natively supported for DDL (Data Definition Language) statements in `sqlalchemy-bigquery` as of version 1.16.0. Attempting to use it in `Table` definitions or Alembic migrations may result in an `sqlalchemy.exc.UnsupportedFeature` error.","severity":"gotcha","affected_versions":"All"},{"fix":"Ensure Application Default Credentials are configured. This typically involves setting the `GOOGLE_APPLICATION_CREDENTIALS` environment variable to the path of a service account key file, running `gcloud auth application-default login` for local development, or deploying to a Google Cloud environment where service accounts are automatically available. Consult the Google Cloud documentation on ADC for detailed setup instructions.","message":"The `sqlalchemy-bigquery` dialect relies on `google-auth` for authentication. By default, it attempts to use Application Default Credentials (ADC). If ADC are not properly configured in the execution environment, a `google.auth.exceptions.DefaultCredentialsError` will be raised when attempting to create an engine or connect to BigQuery.","severity":"breaking","affected_versions":"All"},{"fix":"Ensure your environment is properly authenticated for Google Cloud. This typically involves setting `GOOGLE_APPLICATION_CREDENTIALS` to a service account key file path, running `gcloud auth application-default login`, or deploying in an environment with managed identities (e.g., GCE, Cloud Run, GKE) where credentials are automatically provided.","message":"`sqlalchemy-bigquery` relies on `google-auth` to find credentials, often via Application Default Credentials (ADC). If ADC are not set up or accessible in the execution environment, connection attempts will fail with `google.auth.exceptions.DefaultCredentialsError`.","severity":"breaking","affected_versions":"All"}],"env_vars":null,"last_verified":"2026-05-12T16:19:56.858Z","next_check":"2026-06-26T00:00:00.000Z","problems":[{"fix":"pip install sqlalchemy-bigquery","cause":"The `sqlalchemy-bigquery` library is not installed or not accessible in the current Python environment, preventing SQLAlchemy from loading the BigQuery dialect.","error":"sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:bigquery"},{"fix":"pip install google-cloud-bigquery","cause":"The `sqlalchemy-bigquery` dialect relies on the `google-cloud-bigquery` client library, which is a required dependency but is not installed.","error":"ModuleNotFoundError: No module named 'google.cloud.bigquery'"},{"fix":"Set the `GOOGLE_APPLICATION_CREDENTIALS` environment variable to a service account key file path, or authenticate via `gcloud auth application-default login`.","cause":"The BigQuery client failed to find valid Google Cloud authentication credentials in the environment or through standard discovery mechanisms.","error":"google.auth.exceptions.DefaultCredentialsError: Could not automatically determine credentials. Please set GOOGLE_APPLICATION_CREDENTIALS or explicitly create credentials and attach them to the client."},{"fix":"Verify the dataset and table names are correct and match BigQuery's case-sensitivity, and ensure the service account or user has BigQuery Data Viewer role or similar permissions.","cause":"The BigQuery dataset or table referenced in your SQLAlchemy query does not exist, or the authenticated user lacks sufficient permissions to access it.","error":"google.api_core.exceptions.NotFound: 404 Not found: Table <project-id>:<dataset-id>.<table-name>"},{"fix":"Use a compatible SQLAlchemy type that maps correctly to BigQuery (e.g., `String` for UUID or ENUM), or implement a custom type with BigQuery-specific compilation rules.","cause":"The SQLAlchemy data type used in a model or schema definition (e.g., UUID, ENUM) does not have a direct or supported mapping to a native BigQuery data type by the `sqlalchemy-bigquery` dialect.","error":"sqlalchemy.exc.CompileError: (sqlalchemy.bigquery.BITSA_dialect.BigQueryDialect) Cannot compile type <type_name>"}],"ecosystem":"pypi","meta_description":null,"install_score":100,"install_tag":"verified","quickstart_score":0,"quickstart_tag":"stale","pypi_latest":"1.17.0","install_checks":{"last_tested":"2026-05-12","tag":"verified","tag_description":"installs cleanly on critical runtimes, fast import, recently tested","results":[{"runtime":"python:3.10-alpine","python_version":"3.10","os_libc":"alpine (musl)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"sdist","failure_reason":null,"install_time_s":null,"import_time_s":0.49,"mem_mb":15.5,"disk_size":"97.2M"},{"runtime":"python:3.10-alpine","python_version":"3.10","os_libc":"alpine (musl)","variant":"bqstorage","exit_code":0,"wheel_type":"sdist","failure_reason":null,"install_time_s":null,"import_time_s":0.48,"mem_mb":15.5,"disk_size":"274.9M"},{"runtime":"python:3.10-alpine","python_version":"3.10","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.49,"mem_mb":15.5,"disk_size":"95.9M"},{"runtime":"python:3.10-alpine","python_version":"3.10","os_libc":"alpine (musl)","variant":"bqstorage","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.48,"mem_mb":15.5,"disk_size":"268.3M"},{"runtime":"python:3.10-slim","python_version":"3.10","os_libc":"slim (glibc)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":8.4,"import_time_s":0.38,"mem_mb":15.5,"disk_size":"93M"},{"runtime":"python:3.10-slim","python_version":"3.10","os_libc":"slim (glibc)","variant":"bqstorage","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":10.5,"import_time_s":0.4,"mem_mb":15.5,"disk_size":"246M"},{"runtime":"python:3.10-slim","python_version":"3.10","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.37,"mem_mb":15.5,"disk_size":"92M"},{"runtime":"python:3.10-slim","python_version":"3.10","os_libc":"slim (glibc)","variant":"bqstorage","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.37,"mem_mb":15.5,"disk_size":"241M"},{"runtime":"python:3.11-alpine","python_version":"3.11","os_libc":"alpine (musl)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"sdist","failure_reason":null,"install_time_s":null,"import_time_s":0.77,"mem_mb":17.6,"disk_size":"105.6M"},{"runtime":"python:3.11-alpine","python_version":"3.11","os_libc":"alpine (musl)","variant":"bqstorage","exit_code":0,"wheel_type":"sdist","failure_reason":null,"install_time_s":null,"import_time_s":0.75,"mem_mb":17.6,"disk_size":"285.8M"},{"runtime":"python:3.11-alpine","python_version":"3.11","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.82,"mem_mb":17.6,"disk_size":"104.2M"},{"runtime":"python:3.11-alpine","python_version":"3.11","os_libc":"alpine (musl)","variant":"bqstorage","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.82,"mem_mb":17.6,"disk_size":"279.1M"},{"runtime":"python:3.11-slim","python_version":"3.11","os_libc":"slim (glibc)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":7.2,"import_time_s":0.67,"mem_mb":17.6,"disk_size":"101M"},{"runtime":"python:3.11-slim","python_version":"3.11","os_libc":"slim (glibc)","variant":"bqstorage","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":9.5,"import_time_s":0.68,"mem_mb":17.6,"disk_size":"257M"},{"runtime":"python:3.11-slim","python_version":"3.11","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.63,"mem_mb":17.6,"disk_size":"100M"},{"runtime":"python:3.11-slim","python_version":"3.11","os_libc":"slim (glibc)","variant":"bqstorage","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.64,"mem_mb":17.6,"disk_size":"252M"},{"runtime":"python:3.12-alpine","python_version":"3.12","os_libc":"alpine (musl)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"sdist","failure_reason":null,"install_time_s":null,"import_time_s":0.85,"mem_mb":17.4,"disk_size":"96.4M"},{"runtime":"python:3.12-alpine","python_version":"3.12","os_libc":"alpine (musl)","variant":"bqstorage","exit_code":0,"wheel_type":"sdist","failure_reason":null,"install_time_s":null,"import_time_s":0.88,"mem_mb":17.4,"disk_size":"276.2M"},{"runtime":"python:3.12-alpine","python_version":"3.12","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.87,"mem_mb":17.4,"disk_size":"95.0M"},{"runtime":"python:3.12-alpine","python_version":"3.12","os_libc":"alpine (musl)","variant":"bqstorage","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.88,"mem_mb":17.4,"disk_size":"269.6M"},{"runtime":"python:3.12-slim","python_version":"3.12","os_libc":"slim (glibc)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":6.2,"import_time_s":0.9,"mem_mb":17.4,"disk_size":"92M"},{"runtime":"python:3.12-slim","python_version":"3.12","os_libc":"slim (glibc)","variant":"bqstorage","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":8.3,"import_time_s":0.84,"mem_mb":17.4,"disk_size":"248M"},{"runtime":"python:3.12-slim","python_version":"3.12","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.84,"mem_mb":17.4,"disk_size":"91M"},{"runtime":"python:3.12-slim","python_version":"3.12","os_libc":"slim (glibc)","variant":"bqstorage","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.83,"mem_mb":17.4,"disk_size":"242M"},{"runtime":"python:3.13-alpine","python_version":"3.13","os_libc":"alpine (musl)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"sdist","failure_reason":null,"install_time_s":null,"import_time_s":0.81,"mem_mb":17.6,"disk_size":"95.8M"},{"runtime":"python:3.13-alpine","python_version":"3.13","os_libc":"alpine (musl)","variant":"bqstorage","exit_code":0,"wheel_type":"sdist","failure_reason":null,"install_time_s":null,"import_time_s":0.79,"mem_mb":17.6,"disk_size":"275.6M"},{"runtime":"python:3.13-alpine","python_version":"3.13","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.85,"mem_mb":17.6,"disk_size":"94.4M"},{"runtime":"python:3.13-alpine","python_version":"3.13","os_libc":"alpine (musl)","variant":"bqstorage","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.88,"mem_mb":17.6,"disk_size":"268.9M"},{"runtime":"python:3.13-slim","python_version":"3.13","os_libc":"slim (glibc)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":6.7,"import_time_s":0.77,"mem_mb":17.6,"disk_size":"92M"},{"runtime":"python:3.13-slim","python_version":"3.13","os_libc":"slim (glibc)","variant":"bqstorage","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":8.4,"import_time_s":0.76,"mem_mb":17.6,"disk_size":"247M"},{"runtime":"python:3.13-slim","python_version":"3.13","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.82,"mem_mb":17.6,"disk_size":"90M"},{"runtime":"python:3.13-slim","python_version":"3.13","os_libc":"slim (glibc)","variant":"bqstorage","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.81,"mem_mb":17.6,"disk_size":"242M"},{"runtime":"python:3.9-alpine","python_version":"3.9","os_libc":"alpine (musl)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"sdist","failure_reason":null,"install_time_s":null,"import_time_s":0.5,"mem_mb":15.1,"disk_size":"96.4M"},{"runtime":"python:3.9-alpine","python_version":"3.9","os_libc":"alpine (musl)","variant":"bqstorage","exit_code":0,"wheel_type":"sdist","failure_reason":null,"install_time_s":null,"import_time_s":0.45,"mem_mb":15.1,"disk_size":"255.3M"},{"runtime":"python:3.9-alpine","python_version":"3.9","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.46,"mem_mb":15.1,"disk_size":"95.1M"},{"runtime":"python:3.9-alpine","python_version":"3.9","os_libc":"alpine (musl)","variant":"bqstorage","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.46,"mem_mb":15.1,"disk_size":"254.0M"},{"runtime":"python:3.9-slim","python_version":"3.9","os_libc":"slim (glibc)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":10.1,"import_time_s":0.44,"mem_mb":15.1,"disk_size":"93M"},{"runtime":"python:3.9-slim","python_version":"3.9","os_libc":"slim (glibc)","variant":"bqstorage","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":12.2,"import_time_s":0.42,"mem_mb":15.1,"disk_size":"234M"},{"runtime":"python:3.9-slim","python_version":"3.9","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.42,"mem_mb":15.1,"disk_size":"92M"},{"runtime":"python:3.9-slim","python_version":"3.9","os_libc":"slim (glibc)","variant":"bqstorage","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.41,"mem_mb":15.1,"disk_size":"233M"}]},"quickstart_checks":{"last_tested":"2026-04-23","tag":"stale","tag_description":"widespread failures or data too old to trust","results":[{"runtime":"python:3.10-alpine","exit_code":1},{"runtime":"python:3.10-slim","exit_code":1},{"runtime":"python:3.11-alpine","exit_code":1},{"runtime":"python:3.11-slim","exit_code":1},{"runtime":"python:3.12-alpine","exit_code":1},{"runtime":"python:3.12-slim","exit_code":1},{"runtime":"python:3.13-alpine","exit_code":1},{"runtime":"python:3.13-slim","exit_code":1},{"runtime":"python:3.9-alpine","exit_code":1},{"runtime":"python:3.9-slim","exit_code":1}]}}