표준 SQL의 스크립팅

BigQuery 스크립팅

BigQuery 스크립팅을 통해 하나의 요청에서 여러 문을 BigQuery로 전송하고, 변수를 사용하고, IFWHILE 같은 제어 흐름 명령문을 사용할 수 있습니다. 예를 들어 변수를 선언하고 값을 할당한 다음 세 번째 문에서 변수를 참조할 수 있습니다.

BigQuery에서 스크립트는 순서대로 실행될 SQL 문 목록입니다. SQL 문 목록은 세미콜론으로 구분된 유효한 BigQuery 문 목록입니다.

예를 들면 다음과 같습니다.

-- 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
);

BigQuery는 단일 최종 쿼리 문을 포함한 CREATE TEMP FUNCTION 문으로 구성되지 않는 한 여러 개의 문을 포함한 요청을 스크립트로 해석합니다. 예를 들어 다음은 스크립트로 간주되지 않습니다.

CREATE TEMP FUNCTION Add(x INT64, y INT64) AS (x + y);

SELECT Add(3, 4);

DECLARE

구문

DECLARE variable_name[, ...] variable_type [ DEFAULT expression];

variable_name은 유효한 식별자여야 하며, variable_type은 BigQuery 유형입니다.

설명

지정된 유형의 변수를 선언합니다. DEFAULT 절이 지정되면 변수가 표현식의 값으로 초기화됩니다. 지정된 DEFAULT 절이 없으면 변수가 NULL 값으로 초기화됩니다.

변수 선언은 스크립트 시작 부분에, 다른 문 앞에 또는 BEGIN으로 선언된 블록의 시작 부분에 나타나야 합니다. 변수 이름은 대소 문자를 구분하지 않습니다.

단일 DECLARE 문에 여러 변수 이름이 나타날 수 있지만 variable_typeexpression은 하나만 나타날 수 있습니다.

현재 블록이나 포함 블록에서 이전에 선언된 변수와 이름이 같은 변수를 선언하는 것은 오류입니다.

DEFAULT 절이 있으면 표현식의 값을 지정된 유형으로 강제 변환해야 합니다. 표현식은 이전에 동일한 블록 또는 포함 블록 내에서 선언된 다른 변수를 참조할 수 있습니다.

변수의 최대 크기는 1MB이며, 스크립트에 사용되는 모든 변수의 최대 크기는 10MB입니다.

예시

다음 예시는 x 변수를 값이 NULL인 INT64로 초기화합니다.

DECLARE x INT64;

다음 예시는 d 변수를 값이 현재 날짜인 DATE로 초기화합니다.

DECLARE d DATE DEFAULT CURRENT_DATE();

다음 예시는 x, y, z 변수를 값이 0인 INT64로 초기화합니다.

DECLARE x, y, z INT64 DEFAULT 0;

SET

구문

SET name = expression;
SET (variable_name_1, variable_name_2, …, variable_name_n) =
  (expression_1, expression_2, …, expression_n);

설명

제공된 표현식의 값을 갖도록 변수를 설정하거나 여러 표현식의 결과에 따라 여러 변수를 동시에 설정합니다.

SET 문은 스크립트 본문 내 어디에나 나타날 수 있습니다.

예시

다음 예시는 값 5를 갖도록 x 변수를 설정합니다.

SET x = 5;

다음 예시는 값 4를 갖도록 a 변수를 설정하고, 값 'foo'를 갖도록 b 변수를 설정하고, 값 false를 갖도록 c 변수를 설정합니다.

SET (a, b, c) = (1 + 3, 'foo', false);

다음 예시는 쿼리의 결과를 여러 변수에 할당합니다. 먼저 두 변수, target_wordcorpus_count를 선언한 다음 SELECT AS STRUCT 쿼리의 결과를 이 두 변수에 할당합니다. 쿼리의 결과는 두 개의 필드가 있는 STRUCT를 포함하는 단일 행입니다. 첫 번째 요소는 첫 번째 변수에 할당되고 두 번째 요소는 두 번째 변수에 할당됩니다.

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

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
);

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

이 문 목록은 다음 문자열을 출력합니다.

Found 151 occurrences of "methinks" across 38 Shakespeare works

BEGIN

구문

BEGIN
  sql_statement_list
END;

설명

BEGIN은 선언된 변수가 존재하는 문 블록을 시작하며 END에서 끝납니다. sql_statement_list는 세미콜론으로 끝나는 0개 이상의 SQL 문 목록입니다.

변수 선언은 다른 문 유형보다 앞서 블록의 시작 부분에 나타나야 합니다. 블록 내에서 선언된 변수는 블록 내 및 중첩된 블록에서만 참조될 수 있습니다. 동일한 블록이나 외부 블록에서 선언된 변수와 같은 이름으로 변수를 선언하는 것은 오류입니다.

BEGIN/END, IF/ELSE/END IF, WHILE/END WHILE 같은 블록 및 조건문의 최대 중첩 수준은 50입니다.

예시

다음 예시는 기본값이 10인 x 변수를 선언한 다음 y 변수에 값(10)이 할당되는 블록을 시작하고 이 값을 반환합니다. 그 다음 END 문이 블록을 종료하고 변수 y의 범위를 끝냅니다. 마지막으로 값을 반환합니다.

DECLARE x INT64 DEFAULT 10;
BEGIN
  DECLARE y INT64;
  SET y = x;
  SELECT y;
END;
SELECT x;

END

BEGIN에 의해 시작된 블록을 종료합니다.

IF

구문

IF condition THEN
  [if_statement_list]
[ELSE
  else_statement_list
]
END IF;

설명

condition이 true이면 if_statement_list를 실행합니다. 그렇지 않으면 선택적 ELSE 절이 제공되는 경우 else_statement_list을 실행합니다.

BEGIN/END, IF/ELSE/END IF, WHILE/END WHILE 같은 블록 및 조건문의 최대 중첩 수준은 50입니다.

예시

다음 예시는 기본값이 103인 INT64 변수 target_product_id를 선언한 다음 dataset.products 테이블에 값과 일치하는 product_id 열이 있는 행이 포함되어 있는지 검사합니다. 포함되어 있다면 default_product_id 값과 함께 제품을 찾았다고 표시하는 문자열을 출력합니다. 포함되지 않은 경우 default_product_id 값과 함께 제품을 찾지 못했다고 표시하는 문자열을 출력합니다.

DECLARE target_product_id INT64 DEFAULT 103;
IF EXISTS (SELECT 1 FROM dataset.products
           WHERE product_id = target_product_id) THEN
  SELECT CONCAT('found product ', CAST(target_product_id AS STRING));
ELSE
  SELECT CONCAT('did not find product ', CAST(target_product_id AS STRING));
END IF;

루프

LOOP

구문

LOOP
  sql_statement_list
END LOOP;

설명

BREAK 또는 LEAVE 문이 루프를 종료할 때까지 sql_statement_list를 실행합니다. sql_statement_list는 세미콜론으로 끝나는 0개 이상의 SQL 문 목록입니다.

예시

다음 예시는 기본값이 0인 x 변수를 선언한 다음 LOOP 문을 사용하여 변수가 10보다 크거나 같을 때까지 실행되는 루프를 생성합니다. 루프가 종료된 후에는 값을 출력합니다.

DECLARE x INT64 DEFAULT 0;
LOOP
  SET x = x + 1;
  IF x >= 10 THEN
    LEAVE;
  END IF;
END LOOP;
SELECT x;

이 예시는 다음을 출력합니다.

+----+
| x  |
+----+
| 10 |
+----+

WHILE

구문

WHILE boolean_expression DO
  sql_statement_list
END WHILE;

BEGIN/END, IF/ELSE/END IF, WHILE/END WHILE 같은 블록 및 조건문의 최대 중첩 수준은 50입니다.

설명

boolean_expression이 true인 동안 sql_statement_list를 실행합니다. boolean_expression은 루프가 반복될 때마다 평가됩니다.

BREAK

설명

현재 루프를 종료합니다.

루프 외부에서 BREAK를 사용하는 것은 오류입니다.

예시

다음 예시는 두 변수, headsheads_count를 선언합니다. 그 다음으로 무작위 부울 값을 에 할당하는 루프를 시작한 다음 가 true인지 확인합니다. true인 경우 'Heads!'를 출력하고 heads_count를 증분합니다. true가 아닌 경우 'Tails!'를 출력하고 루프를 종료합니다. 마지막으로 'coin flip' 결과가 몇 번이나 'heads'였는지 표시하는 문자열을 출력합니다.

DECLARE heads BOOL;
DECLARE heads_count INT64 DEFAULT 0;
LOOP
  SET heads = RAND() < 0.5;
  IF heads THEN
    SELECT 'Heads!';
    SET heads_count = heads_count + 1;
  ELSE
    SELECT 'Tails!';
    BREAK;
  END IF;
END LOOP;
SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');

LEAVE

BREAK의 동의어입니다.

CONTINUE

설명

현재 루프에서 다음 문을 건너뛰고 루프의 시작 부분으로 돌아갑니다.

루프 외부에서 CONTINUE를 사용하는 것은 오류입니다.

예시

다음 예시는 두 변수 headsheads_count를 선언합니다. 그 다음으로 무작위 부울 값을 에 할당하는 루프를 시작한 다음 가 true인지 확인합니다. true인 경우 'Heads!'를 출력하고, heads_count를 증분한 다음 루프를 다시 시작하고 나머지 문을 건너뜁니다. true가 아닌 경우 'Tails!'를 출력하고 루프를 종료합니다. 마지막으로 'coin flip' 결과가 몇 번이나 'heads'였는지 표시하는 문자열을 출력합니다.

DECLARE heads BOOL;
DECLARE heads_count INT64 DEFAULT 0;
LOOP
  SET heads = RAND() < 0.5;
  IF heads THEN
    SELECT 'Heads!';
    SET heads_count = heads_count + 1;
    CONTINUE;
  END IF;
  SELECT 'Tails!';
  BREAK;
END LOOP;
SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');

ITERATE

CONTINUE의 동의어입니다.

RETURN

BigQuery 스크립트에서 RETURN은 현재 스크립트의 실행을 종료합니다.

CALL

구문

CALL procedure_name (procedure_argument[, …])

설명

인수 목록을 사용하여 프로시져를 호출합니다. procedure_argument는 변수이거나 표현식일 수 있습니다. OUT 또는 INOUT 인수의 경우 인수로 전달되는 변수는 적절한 BigQuery 유형이어야 합니다.

프로시져의 인수 목록에서 동일 변수가 OUT 또는 INOUT 인수로 여러 번 나타나지 않을 수 있습니다.

프로시져 호출의 최대 깊이는 50프레임입니다.

예시

다음 예시는 retCode 변수를 선언합니다. 그런 다음 myDataset 데이터세트에서 updateSomeTables 프로시져를 호출하여 'someAccountId'retCode 인수를 전달합니다. 마지막으로 retCode 값을 반환합니다.

DECLARE retCode INT64;
-- Procedure signature: (IN account_id STRING, OUT retCode INT64)
CALL myDataset.UpdateSomeTables('someAccountId', retCode);
SELECT retCode;