09 May 20 · npack · #Bigquery ·   Bookmark   ×

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

npack

posted on 09 May 20

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds