# 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.

Source: https://docs.querylab.io/desktop-app/local-bigquery-api/

The Local engine is the BigQuery engine that runs in your browser (see [Querylab.io Local](https://local.querylab.io)). 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.

| Control               | Does                                                                                                                                                         |
| --------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| **Port**              | The port the API listens on, from 1024 to 65535. The default is 9060. Changing it restarts the API.                                                          |
| Status line           | Shows `Running at http://127.0.0.1:9060`, or `Stopped`, or why it could not start. If the port is taken, the status line says so and no other port is tried. |
| **Open Local window** | Shows the window that holds the engine and its tables. The app keeps it hidden otherwise.                                                                    |
| Connect a client      | A `bq` command and a Python example for the current port, each with a copy button.                                                                           |

## Connect `bq`

```bash
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

```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.

```sql
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:

```bash
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.

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

```bash
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](https://docs.cloud.google.com/bigquery/docs/locations) and [job lookup locations](https://docs.cloud.google.com/bigquery/docs/reference/rest/v2/jobs/get).