Federated queries in BigQuery (Cloud SQL, Spanner, AlloyDB)

EXTERNAL_QUERY sends SQL to Cloud SQL, AlloyDB or Spanner and returns the result to BigQuery. You're billed for the bytes the database returns, maximum bytes billed doesn't apply, only filters and column lists are pushed down, and the load lands on the source database.

A federated query reads live data from an operational database, Cloud SQL (MySQL or PostgreSQL), AlloyDB or Spanner, inside a BigQuery query. It is the quickest way to join today’s orders in Postgres with years of history in BigQuery without building a pipeline. The source database does part of the work, so the query is only as fast as that database, and every federated query is a load on it.

EXTERNAL_QUERY

A connection holds the database’s address and credentials. EXTERNAL_QUERY sends a statement, in the database’s own SQL dialect, through that connection and returns the result as a table:

SELECT o.order_id, o.status, h.lifetime_value
FROM EXTERNAL_QUERY(
  'myproject.us.orders-postgres',
  'SELECT order_id, customer_id, status FROM orders WHERE created_at >= now() - interval ''1 day'''
) AS o
JOIN mydataset.customer_history AS h USING (customer_id);

The inner string is PostgreSQL, not BigQuery SQL: Postgres functions, Postgres quoting (quotes doubled inside the BigQuery string literal). The result columns are converted to BigQuery types. A column of a type BigQuery doesn’t support makes the query fail; cast it to text in the inner query.

For Spanner, a Spanner external dataset maps the whole database into BigQuery, so you query its tables in BigQuery SQL without EXTERNAL_QUERY. Those queries always use Spanner Data Boost.

What gets pushed down

BigQuery can rewrite the inner query to send less data back. For EXTERNAL_QUERY, it only does this when the inner query is exactly SELECT * FROM table, and only for column lists and filters:

SELECT COUNT(*)
FROM EXTERNAL_QUERY('myproject.us.ops-mysql', 'SELECT * FROM operations')
WHERE a = 'Y' AND b NOT IN ('COMPLETE', 'CANCELLED');

is sent to the database as

SELECT `a`, `b` FROM (SELECT * FROM operations) t
WHERE ((`a` = 'Y') AND (NOT `b` IN ('COMPLETE', 'CANCELLED')))

Aggregations, joins, ORDER BY and LIMIT are never pushed down, and filters only with literals of simple types. So write the aggregation, the join and the limit inside the inner query when the database can do them, and return as few rows as possible. A COUNT(*) outside EXTERNAL_QUERY makes the database send every matching row to BigQuery to be counted.

Cost

• On-demand, you pay for the bytes the external query returns, at the normal per-TiB price. Rows filtered out in the database cost nothing in BigQuery.
• On capacity pricing, you pay for the slots, as for any query.
• Maximum bytes billed doesn’t work for federated queries: BigQuery can’t know the size before the database answers, and the setting isn’t supported. A dry run reports a lower bound of 0 bytes, so a byte limit set from the estimate, such as Querylab.io’s cost limits, can’t catch a large result.
• The database’s own compute is billed by that service.

Limits

There is no BigQuery quota on federated queries themselves. The limit is the database: a large federated query competes with the application for CPU and connections. Google recommends pointing the connection at a read replica.

Federated query or replication