SQL EditorCompletions

Hierarchical completions

BigQuery names things in four levels, project.dataset.table.column. Type a dot after any level and the list shows only the next one, so you can walk down to a table without knowing its name in advance.

one dot in: only datasets of that project

SELECT * FROM `bigquery-public-data.

agent_analytic_sample_data BigQuery Dataset

america_health_rankings

austin_311

austin_bikeshare

austin_crime

baseball

bbc_news

bitcoin_blockchain

Walking the hierarchy

Each dot shows one level: datasets after a project, tables after a dataset, columns after a table. Accepting a project or a dataset inserts the dot and opens the next list on its own.

SELECT * FROM `ace-analytics.warehouse.

events BigQuery Table

users

fact_mot_test

dim_defect_type

dim_vehicle

INFORMATION_SCHEMA

No dot yet

Before any dot, FROM ¦ lists every table you have access to as a full path. Filtering matches any part of it, so users finds ace-analytics.warehouse.users and warehouse_dev finds the dev copy of the dataset.

SELECT * FROM

ace-analytics.warehouse.events

ace-analytics.warehouse.users

ace-analytics.warehouse_dev.users

ace-analytics.warehouse_test.users

bigquery-public-data.austin_311.311_service_requests

This is the quickest way to switch between _dev, _test, and production datasets: type the table name, pick the variant.

STRUCTs

The same rule holds inside a row. data. shows the STRUCT’s immediate fields; data.user. shows the next level, and so on down.

SELECT device.
FROM `ace-analytics.warehouse.events`

category STRING

operating_system STRING

language STRING

web_info STRUCT

One name, two meanings

FROM analytics. could be a project (next: datasets) or a dataset in your default project (next: tables). Querylab.io shows both, tables of the dataset first:

SELECT * FROM analytics.

sessions BigQuery Table

pageviews BigQuery Table

INFORMATION_SCHEMA

analytics.raw BigQuery Dataset

analytics.staging BigQuery Dataset

region-us. is handled the same way and adds INFORMATION_SCHEMA for region-scoped views.

Backticks and dashes

Dashes in project IDs don’t need backticks: FROM ace-analytics. and FROM `ace-analytics`. both open the dataset list. Inside a backtick pair the whole path completes without adding more quotes. Names with spaces or other special characters need backticks around each part: `my project`.`my dataset`.

Whether accepted names get backticks is the Identifier Formatting setting — always, or only when the name needs them. See auto-completions.

BigFunctions

BigFunctions are community UDFs deployed per region. With Enable BigFunctions on (Settings → Editor → SQL Editor → Completions; off by default) the bigfunctions namespace joins the function lists and navigates by dot:

SELECT bigfunctions.us. FROM `ace-analytics.warehouse.reviews`

sentiment_score BigFunction

detect_language

translate

parse_url

is_email_valid

bigfunctions. lists the regions BigFunctions is deployed to; bigfunctions.us. lists the functions with their documentation.