Using Variables in Google Bigquery

BigQuery scripting enables you to send multiple statements to BigQuery in one request, to use variables, and to use control flow statements.

There are steps to use variables, You will first declare them, Then assign a value to them using SET command, and finally use them in your SQL queries. Look at the below example:

--declaration
DECLARE target_word STRING DEFAULT 'methinks';
DECLARE corpus_count, word_count INT64;

--assigning values
SET (corpus_count, word_count) = (
  SELECT AS STRUCT COUNT(DISTINCT corpus), SUM(word_count)
  FROM `bigquery-public-data`.samples.shakespeare
  WHERE LOWER(word) = target_word
);

--using the variables
SELECT
  FORMAT('Found %d occurrences of "%s" across %d Shakespeare works',
         word_count, target_word, corpus_count) AS result;

Output of the above program:

Found 151 occurrences of "methinks" across 38 Shakespeare works

nVector

posted on 09 May 20