Local BigQuery API

Querylab.io Desktop can answer BigQuery's REST API on your computer, so bq, the Python client and other BigQuery tools run SQL on the Querylab.io Local engine instead of on BigQuery. Queries run on your computer and are not billed. Only programs on your computer can connect.

The Local engine is the BigQuery engine that runs in your browser (see Querylab.io Local). In the desktop app it can also be reached by programs on your computer, in BigQuery’s own REST dialect. That is useful for trying a script, a test or a tool against a BigQuery-shaped engine without a project, a login or a bill.

Turn it on

Open Settings → Local BigQuery API and turn on Serve the Local BigQuery API. It is off by default and the setting is kept on this computer. The app starts the API again at every launch while the switch is on.

Connect bq

bq --api=http://127.0.0.1:9060 --oauth_access_token=local --project_id=learn query --nouse_legacy_sql "SELECT 1"

--oauth_access_token=local is there because bq refuses to start without a login. The API ignores it. It is a dummy value for the CLI, not a local password. Direct HTTP requests need no token; Python can use AnonymousCredentials. You can use any project ID. Datasets and jobs support US, EU, and the regions in the local BigQuery region catalog, such as europe-west1.

Connect Python

# pip install google-cloud-bigquery pandas db-dtypes
from google.auth.credentials import AnonymousCredentials
from google.cloud import bigquery

client = bigquery.Client(
    project="learn",
    credentials=AnonymousCredentials(),
    client_options={"api_endpoint": "http://127.0.0.1:9060"},
)
print(client.query("SELECT 1 AS x").to_dataframe(create_bqstorage_client=False))

create_bqstorage_client=False keeps the client on the REST API. The Storage Read API is not served.

What works

• Queries (bq query, Client.query, query_and_wait), dry runs, paging and job lookups.
• Listing and reading projects, datasets, tables and jobs, and cancelling a job.
• Creating and deleting datasets and tables, changing a table’s description, adding columns.
• Reading rows (bq head, list_rows) and streaming rows in (bq insert, insert_rows_json).
• Loading files up to 16 MiB: CSV, newline-delimited JSON, Parquet and Avro (bq load, load_table_from_file, load_table_from_json, load_table_from_dataframe).

A request for something the engine does not do gets a 501 that says so: query parameters, writing a query’s result to a table, copy and extract jobs, changing a dataset after it is created.

Where the data is

The data stays in the Local window on this computer and is not sent anywhere. Only programs on this computer can connect: the API listens on 127.0.0.1, answers only requests addressed to it, and refuses requests that come from web pages.

For development

Setting QUERYLAB_LOCAL_BQ_API=1 in the environment starts the API at launch without the setting, and QUERYLAB_LOCAL_BQ_API_PORT picks its port.

Multiple projects and CI

Projects are independent local namespaces. Create datasets under the project IDs your application uses; tables and routines with identical names in different projects remain separate. The project that runs a job can differ from the projects containing its data.

CREATE SCHEMA `sales-prod.shop`;
CREATE SCHEMA `crm-prod.customers`;
CREATE TABLE `sales-prod.shop.orders` AS SELECT 1 AS customer_id;
CREATE TABLE `crm-prod.customers.people` AS SELECT 1 AS id, 'Ada' AS name;

SELECT p.name
FROM `sales-prod.shop.orders` o
JOIN `crm-prod.customers.people` p ON o.customer_id = p.id;

dataset.table resolves within the request’s default project. A bare table name requires defaultDataset (or bq --dataset_id). A fully qualified name keeps its explicit project. Job IDs are scoped to their job project, including lookups after reload. Local discovers projects containing datasets in the project tree; the course keeps its learn fixture.

For CI from a source checkout, install the frontend dependencies and start an ephemeral headless instance:

cd frontend
npm ci
npm run bq:serve -- --port 9060 --setup fixtures.sql

The command prints Querylab.io Local ready after the optional setup script succeeds and the endpoint is listening. It uses the real Local API handlers, repository and SQL engine through the existing Node test harness; no desktop window or Google login is required. The setup script can use the fully qualified SQL above. Omit --setup to start with the teaching fixtures only. Each start has fresh state; stop the process with SIGTERM or Ctrl-C after your tests.

Point existing clients at http://127.0.0.1:9060, choose a job project such as ci-runner, and use the dummy bq token or anonymous client credentials. Projects are accepted locally without Google Cloud provisioning. IAM and billing are not emulated. The headless command is a source-checkout harness, not a standalone packaged server.

Locations

Set a dataset’s location when creating it. Locations are metadata and validation rules; all execution stays on your computer.

CREATE SCHEMA `data-prod.europe` OPTIONS(location='europe-west1');
CREATE TABLE `data-prod.europe.orders` AS SELECT 1 AS id;

bq --api=http://127.0.0.1:9060 --oauth_access_token=local \
  --project_id=ci-runner --location=europe-west1 \
  query --nouse_legacy_sql 'SELECT * FROM `data-prod.europe.orders`'

The API accepts location on jobs.query and jobReference.location on jobs.insert. Without it, Local infers the location from static dataset or regional INFORMATION_SCHEMA references, then falls back to US when there are none. For dynamic SQL without static resource references, supply the location explicitly. New datasets without a location use the job location, or US by default. Existing teaching fixtures stay in US.

A job’s datasets must share its location. EU and europe-west1 are distinct, as are US and us-central1: an ordinary query cannot join them. Regional metadata and job history are filtered by location. Dataset and job locations survive desktop reloads. Single-region job lookups require the location parameter; US and EU lookups can omit it. Locations cannot be changed with ALTER SCHEMA.

Cross-region global queries, physical data placement, replication, and residency guarantees are not emulated. See BigQuery locations and job lookup locations.