BigQuery query creation without variables?

It is now possible to declare and set variables using SQL. For more information, see the documentation, but here is an example:

-- Declare a variable to hold names as an array.
DECLARE top_names ARRAY<STRING>;
-- Build an array of the top 100 names from the year 2017.
SET top_names = (
  SELECT ARRAY_AGG(name ORDER BY number DESC LIMIT 100)
  FROM `bigquery-public-data`.usa_names.usa_1910_current
  WHERE year = 2017
);
-- Which names appear as words in Shakespeare's plays?
SELECT
  name AS shakespeare_name
FROM UNNEST(top_names) AS name
WHERE name IN (
  SELECT word
  FROM `bigquery-public-data`.samples.shakespeare
);

There is currently no way to set/declare variables in BigQuery. If you need variables, you'll need to cut and paste them where you need them. Feel free to file this as a feature request here.

Its not elegant, and its a a pain, but...

The way we handle it is using a python script that replaces a "variable placeholder" in our query and than sending the amended query via the API.

I have opened a feature request asking for "Dynamic SQL" capabilities.

If you want to avoid BQ scripting, you can sometimes use an idiom which utilizes WITH and CROSS JOIN.

In the example below:

• the events table contains some timestamped events
• the reports table contain occasional aggregate values of the events
• the goal is to write a query that only generates incremental (non-duplicate) aggregate rows

This is achieved by

• introducing a state temp table that looks at a target table for aggregate results
• to determine parameters (params) for the actual query
• the params are CROSS JOINed with the actual query
• allowing the param row's columns to be used to constrain the query
• this query will repeatably return the same results
• until the results themselves are appended to the reports table

WTIH state AS (
  SELECT
    -- what was the newest report's ending time?
    COALESCE(
        SELECT MAX(report_end_ts) FROM `x.y.reports`, 
        TIMESTAMP("2019-01-01")
      ) AS latest_report_ts,
    ...
),
params AS (
  SELECT
    -- look for events since end of last report
    latest_report_ts AS event_after_ts,
    -- and go until now
    CURRENT_TIMESTAMP() AS event_before_ts
)

SELECT 
  MIN(event_ts) AS report_begin_ts,
  MAX(event_ts) AS report_end_ts
  COUNT(1) AS event_count, 
  SUM(errors) AS error_total
FROM `x.y.events`
CROSS JOIN params
WHERE event_ts > event_after_ts
  AND event_ts < event_before_ts
)

This approach is useful for bigquery scheduled queries.