1
2
3
4
5
6
7
8
9
10
11
12
13
# Install gcloud CLI
curl https://sdk.cloud.google.com | bash

# Initialize and authenticate
gcloud init
gcloud auth login

# Set application default credentials (for client libraries)
gcloud auth application-default login

# Set active project
gcloud config set project YOUR_PROJECT_ID

1
2
3
4
5
6
7
8
9
-- GoogleSQL (the default and recommended dialect)
CREATE SCHEMA `project.dataset`
OPTIONS (
  description = 'My analytics dataset',
  location = 'US',
  labels = [('environment', 'production')],
  default_table_expiration_days = 30
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Create table with schema
CREATE TABLE `project.dataset.table` (
  id INT64,
  name STRING,
  created_at TIMESTAMP
);

-- Create table from query
CREATE TABLE `project.dataset.derived_table` AS
SELECT column1, column2
FROM `source_table`
WHERE condition;

-- Create or replace with primary/foreign key (informational, non-enforced)
CREATE OR REPLACE TABLE `project.dataset.orders` (
  order_id INT64 NOT NULL,
  customer_id INT64 NOT NULL,
  PRIMARY KEY (order_id) NOT ENFORCED,
  FOREIGN KEY (customer_id) REFERENCES `project.dataset.customers`(id) NOT ENFORCED
);

1
2
3
4
5
6
7
8
9
10
-- Creating a STRUCT
SELECT STRUCT(
  'John' AS first_name,
  'Doe' AS last_name
) AS person;

-- Accessing STRUCT fields
SELECT person.first_name
FROM table;

1
2
3
4
5
6
7
-- Creating an ARRAY
SELECT ['apple', 'banana', 'cherry'] AS fruits;

-- Unnesting ARRAY
SELECT fruit
FROM table, UNNEST(fruits) AS fruit;

1
2
3
4
5
6
7
8
9
10
-- Native JSON type (preferred over STRING for semi-structured data)
SELECT JSON '{"id": 1, "name": "Alice"}' AS payload;

-- Extracting fields with the dot/subscript accessors
SELECT
  payload.id AS id,
  STRING(payload.name) AS name,
  INT64(payload.id) AS id_int
FROM table;

1
2
3
4
5
6
7
-- Creating a geographic point
SELECT ST_GEOGPOINT(-122.4194, 37.7749) AS san_francisco;

-- Calculating distance (meters)
SELECT ST_DISTANCE(point1, point2) AS distance_m
FROM geo_table;

1
2
3
-- RANGE<DATE>, RANGE<DATETIME>, RANGE<TIMESTAMP>
SELECT RANGE<DATE> '[2026-01-01, 2026-04-01)' AS q1_window;

1
2
3
4
5
6
7
8
9
10
SELECT
  column1,
  column2
FROM `project.dataset.table`
WHERE
  column1 IS NOT NULL
  AND column2 BETWEEN 10 AND 100
ORDER BY column1 DESC
LIMIT 1000;

1
2
3
4
5
6
7
FROM `project.dataset.table`
|> WHERE column1 IS NOT NULL
|> AGGREGATE COUNT(*) AS n, AVG(column2) AS avg_val
   GROUP BY column1
|> ORDER BY n DESC
|> LIMIT 100;

1
2
3
4
5
INSERT INTO `project.dataset.table`
  (id, name, created_at)
VALUES
  (1, 'Test', CURRENT_TIMESTAMP());

1
2
3
4
5
6
7
8
MERGE `project.dataset.target` T
USING `project.dataset.source` S
ON T.id = S.id
WHEN MATCHED THEN
  UPDATE SET name = S.name, updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
  INSERT (id, name, created_at) VALUES (S.id, S.name, CURRENT_TIMESTAMP());

1
2
3
4
5
6
UPDATE `project.dataset.table`
SET
  status = 'active',
  updated_at = CURRENT_TIMESTAMP()
WHERE id = 123;

1
2
3
4
DELETE FROM `project.dataset.table`
WHERE status = 'inactive'
  AND created_at < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY);

1
2
TRUNCATE TABLE `project.dataset.staging`;

1
2
3
4
5
6
7
8
-- Ranking within partitions
SELECT
  department,
  employee_name,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

1
2
3
4
5
6
7
8
9
-- Exact + approximate aggregation
SELECT
  AVG(value) AS mean,
  STDDEV(value) AS standard_deviation,
  APPROX_QUANTILES(value, 4) AS quartiles,
  APPROX_COUNT_DISTINCT(user_id) AS approx_users,
  APPROX_TOP_COUNT(country, 5) AS top_countries
FROM metrics;

1
2
3
4
5
6
7
-- Transform rows to array
SELECT
  user_id,
  ARRAY_AGG(DISTINCT product ORDER BY product) AS purchased_products
FROM purchases
GROUP BY user_id;

1
2
3
4
5
6
7
8
9
10
-- Complex struct operations
SELECT
  ARRAY(
    SELECT AS STRUCT
      name,
      age,
      ARRAY(SELECT score FROM UNNEST(scores)) AS test_scores
    FROM users
  ) AS user_details;

1
2
3
4
5
6
7
8
-- Date arithmetic and parsing
SELECT
  DATE_ADD(CURRENT_DATE(), INTERVAL 30 DAY) AS future_date,
  EXTRACT(YEAR FROM ts) AS year,
  FORMAT_DATE('%Y-%m', CURRENT_DATE()) AS month_year,
  DATE_TRUNC(CURRENT_DATE(), MONTH) AS month_start
FROM table;

1
2
3
4
5
6
7
8
9
10
-- Find points within a region
SELECT
  location,
  ST_WITHIN(location, region) AS is_inside
FROM geo_data
WHERE ST_WITHIN(
  location,
  ST_GEOGFROMTEXT('POLYGON((-122.5 37.7, -122.3 37.7, -122.3 37.8, -122.5 37.8, -122.5 37.7))')
);

1
2
3
4
5
6
7
CREATE OR REPLACE MODEL `project.dataset.model`
OPTIONS(
  model_type = 'linear_reg',
  input_label_cols = ['target']
) AS
SELECT * FROM training_data;

1
2
3
4
5
6
SELECT *
FROM ML.PREDICT(
  MODEL `project.dataset.model`,
  (SELECT * FROM prediction_data)
);

1
2
3
4
5
6
7
8
9
10
11
12
-- Call a Vertex AI / Gemini model from SQL
CREATE OR REPLACE MODEL `project.dataset.gemini`
REMOTE WITH CONNECTION `project.us.my_connection`
OPTIONS (endpoint = 'gemini-2.0-flash');

SELECT *
FROM ML.GENERATE_TEXT(
  MODEL `project.dataset.gemini`,
  (SELECT review AS prompt FROM `project.dataset.reviews`),
  STRUCT(0.2 AS temperature, TRUE AS flatten_json_output)
);

1
2
3
4
5
6
7
8
9
10
11
-- Generate embeddings and run approximate nearest-neighbor search
CREATE OR REPLACE VECTOR INDEX docs_idx
ON `project.dataset.docs`(embedding)
OPTIONS (index_type = 'IVF', distance_type = 'COSINE');

SELECT base.id, distance
FROM VECTOR_SEARCH(
  TABLE `project.dataset.docs`, 'embedding',
  (SELECT embedding FROM `project.dataset.query_embedding`),
  top_k => 10);

1
2
3
4
5
6
-- Time-unit, ingestion-time, or integer-range partitioning
CREATE TABLE `project.dataset.partitioned_table`
PARTITION BY DATE(timestamp)
OPTIONS (require_partition_filter = TRUE)
AS SELECT * FROM source_table;

1
2
3
4
5
6
-- Up to 4 clustering columns; order matters
CREATE TABLE `project.dataset.clustered_table`
PARTITION BY DATE(event_ts)
CLUSTER BY user_id, country
AS SELECT * FROM source_table;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE MATERIALIZED VIEW `project.dataset.mv_daily`
PARTITION BY day
AS
SELECT DATE(event_ts) AS day, country, COUNT(*) AS n
FROM `project.dataset.events`
GROUP BY day, country;

-- Accelerate text / needle-in-haystack lookups
CREATE SEARCH INDEX events_idx
ON `project.dataset.events`(ALL COLUMNS);

SELECT * FROM `project.dataset.events`
WHERE SEARCH(events, 'error_code:500');

1
2
3
4
5
6
CREATE EXTERNAL TABLE `project.dataset.external_table`
OPTIONS (
  format = 'CSV',
  uris = ['gs://bucket/path/*.csv']
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- BigLake table over Cloud Storage with fine-grained access control
CREATE EXTERNAL TABLE `project.dataset.biglake_parquet`
WITH CONNECTION `project.us.my_connection`
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://bucket/data/*.parquet']
);

-- BigQuery-managed Apache Iceberg table (read/write, open format on GCS)
CREATE TABLE `project.dataset.iceberg_orders` (
  order_id INT64,
  amount NUMERIC,
  order_date DATE
)
WITH CONNECTION `project.us.my_connection`
OPTIONS (
  table_format = 'ICEBERG',
  storage_uri = 'gs://bucket/iceberg/orders'
);

1
2
3
4
5
-- Query data in Amazon S3 or Azure Blob via a BigQuery Omni connection
SELECT *
FROM `project.aws_us_east_1.s3_table`
WHERE event_date = CURRENT_DATE();