[BigQuery] 절차적 언어1(Procedural language) (DECLARE/EXECUTE/SET/BEGIN)

Hello! ·2023년 3월 9일

BigQuery sql

BigQuery

목록 보기

1/1

참고 : https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language

절차적 언어란?

one query에서 다중 statement를 multi statements query로 실행할 수 있음
multiple statements 쿼리를 사용하여 다음을 수행
- 순차적으로 multi statements를 수행함
- 테이블들을 생성/삭제와 같은 관리 작업을 자동화함
- IF와 WHILE과 같은 프로그래밍 구조를 사용하여 복잡한 로직을 실행함

1. DECLARE

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

variable_name은 타당한 구분자가 되어야만 하며, variable type은 GoogleSQL type 중 하나 (링크)
DECLARE 변수명을 기재하는 구문
DEFAULT
- 지정되면, 변수는 그 expression의 값으로 초기화 되지만, 만약 default값이 지정되지 않으면 변수는 null값으로 초기화됨
- variable_type이 생략된다면, DEFAULT가 반드시 반드시 상세화되어야 함
  - 변수의 유형은 DEFAULT expression으로 추론됨
주의점 및 특징
- 변수의 선언은 다른 절차적 선언문(procedural statuemnets)에서 전에 노출되어야만 함(혹은 BEGIN 블록의 시작점에서)
- 변수의 이름은 case-insensitive(대소문자 구분 없음 ↔ case-sensitive)
- 여러 개의 변수가 DECLARE 에서 선언될 수 있지만, 변수 유형이나 expression은 하나만 가능함
- 현재 블록 또는 포함 블럭에서 이전에 선언한 변수와 같은 이름을 가진 변수를 선언하는 것은 오류가 남
- 다중 쿼리문의 실행동안 오류 메시지 등 정보를 체크하기 위한 system variable을 사용할 수 있음(링크)

코드 예시

DECLARE x INT64;
DECLARE d DATE DEFAULT CURRENT_DATE();
DECLARE x, y, z INT64 DEFAULT 0;

# 테이블 스키마로부터 item 변수의 유형을 추론하여 default expression과 type 적용  
DECLARE item DEFAULT (SELECT item FROM schema1.products LIMIT 1);

2. SET

선언한 변수에 대한 설정값
- 값을 가질 수 있는 변수를 설정하고(디폴트 말고 초기값 셋팅임), 다중 표현식의 결과에 기반하여 다중 변수를 셋팅함

코드 예시

# x변수는 5로 설정
SET x = 5;

# a = 4, b = 'foo', c = false 로 설정됨 
SET (a, b, c) = (1 + 3, 'foo', false);

다중 변수에 쿼리의 결과를 할당하는 예시
1. 두 변수를 선언 (target_word, corpus_count)
2. SELECT AS STRUCT 쿼리의 결과로 선언한 두 변수에 할당함
  - 쿼리의 결과는 두 필드를 가진 STRUCT의 단일값을 포함
3. 첫번째 요소는 첫번째 변수에, 두번째 요소는 두번째 변수에 할당함

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

3. EXECUTE IMMEDIATE

동적 SQL문을 즉시 실행시킴
Syntax
```
EXECUTE IMMEDIATE sql_expression [ INTO variable[, ...] ] [ USING identifier[, ...] ];

sql_expression:
  { "query_statement" | expression("query_statement") }

identifier:
  { variable | value } [ AS alias ]
```
- sql_expression : 쿼리문 표현문. 단일 DDL or DML 구문. IF와 같은 컨트롤 구문은 사용할 수 없음
- expression : 함수, 조건식 혹은 서브쿼리의 표현식임
- query_statement
  - 실행하기 위한 standalone SQL 구문
  - 만약 값을 리턴한다면, INTO 절은 반드시 같은 유형의 값을 포함해야함
- INTO
  - SQL 표현식이 실행된 이후에, 한 개 또는 그 이상의 변수에서 결과를 저장할 수 있음
- USING
  - SQL 표현식을 실행하기 전에, USING 절의 한 개 또는 그 이상의 구분자를 쿼리로 전달할 수 있음
  - 이 식별자는 쿼리 파라미터와 유사하게 작동하여 쿼리문에 노출됨
  - 하나의 구분자는 variable 혹은 value가 될 수 있음

예제

? : palaceholder(자리표시자)로 USING 절에 인덱스로 구분자와 묶임

-- y = 1 * (3 + 2) = 5
EXECUTE IMMEDIATE "SELECT ? * (? + 2)" INTO y USING 1, 3;

@identifier: USING절에 이름으로부터 구분자와 묶임

-- y = 1 * (3 + 2) = 5
EXECUTE IMMEDIATE "SELECT @a * (@b + 2)" INTO y USING 1 as a, 3 as b;

예제2

-- create some variables
DECLARE book_name STRING DEFAULT 'Ulysses';
DECLARE book_year INT64 DEFAULT 1922;
DECLARE first_date INT64;

-- Create a temporary table called Books.
EXECUTE IMMEDIATE
  "CREATE TEMP TABLE Books (title STRING, publish_date INT64)";

-- Add a row for Hamlet (less secure)
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES('Hamlet', 1599)";

-- add a row for Ulysses, using the variables declared and the ? placeholder
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(?, ?)"
  USING book_name, book_year;

-- add a row for Emma, using the identifier placeholder
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(@name, @year)"
  USING 1815 as year, "Emma" as name;

-- add a row for Middlemarch, using an expression
EXECUTE IMMEDIATE
  CONCAT(
    "INSERT INTO Books (title, publish_date)", "VALUES('Middlemarch', 1871)"
  );

-- save the publish date of the first book, Hamlet, to a variable called
-- first_date
EXECUTE IMMEDIATE "SELECT publish_date FROM Books LIMIT 1" INTO first_date;

+------------------+------------------+
| title            | publish_date     |
+------------------+------------------+
| Hamlet           | 1599             |
| Ulysses          | 1922             |
| Emma             | 1815             |
| Middlemarch      | 1871             |
+------------------+------------------+

4. BEGIN...END

BEGIN
  sql_statement_list
END;

BEGIN : END가 나올 때까지만 선언된 변수가 존재하는 곳의 구문의 블록을 초기화한다.
sql_statement_list : 0~그 이상의 ; 으로 끝나는 SQL문의 리스트
선언된 변수
- 변수 선언은 구문들 증 다른 유형의 변수 이전에 있는 블록의 시작점에서 나옴
- 블록 안에서 선언된 변수들은 그 블록과 포함되고 있는 블록 안에 참조될 수 있음
  - 즉 , BEGIN…END 구문 안에서 선언된 변수 이 안에서만 활용 가능하고, 구문 밖에서는 선언이 안됨 (하지만 같은 변수명으로 선언할 수 없음)
  - 예시
```
BEGIN
  DECLARE y INT64;
  DECLARE x INT64 DEFAULT 10;
  SET y = x;
  SELECT y, x;
END;
SELECT x;
```

- 같은 블록 혹은 다른 블록에서 선언된 변수와 같은 이름을 선언하는 것은 오류가 남

블록당 최대 50개 수준을 포함할 수 있고, BEGIN/END, IF/ELSE/END/IF, WHILE/END와 같은 조건문이 있음
BEGIN/END 는 포함된 요소들과 같이 동적으로 실행됨으로써 제한됨
Labels
- BEGINE…END 구문에는 label을 생성 할 수 있음(링크)
```
label_name: BEGIN
  block_statement_list
END [label_name];
```
예시

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

5. BEGIN...EXCEPTION...END

BEGIN
  sql_statement_list
EXCEPTION WHEN ERROR THEN
  sql_statement_list
END;

BEGIN … EXCEPTION : 블록안에 있는 구문들을 실행함
- 블록 내 구문에서 에러가 발생한다면, 블록의 나머지 구문은 무시하고, EXCEPTION절이 실행됨
EXCEPTION 시스템 변수를 이용하여 에러에 대한 세부 정보를 확인할 수 있음

예제

BEGIN
  declare x string default 'exam' ;
  declare y int64 default 12 ;
  select x + y ; # string + int 로 에러 상황 만들기 
EXCEPTION WHEN ERROR THEN
  SELECT
    @@error.message,
    @@error.stack_trace,
    @@error.statement_text,
    @@error.formatted_stack_trace;
END;

첫 번째 결과 보기
두 번째 결과 보기

Hello!

Let's study

[BigQuery] 절차적 언어1(Procedural language) (DECLARE/EXECUTE/SET/BEGIN)

BigQuery

절차적 언어란?

1. DECLARE

2. SET

3. EXECUTE IMMEDIATE

4. BEGIN...END

5. BEGIN...EXCEPTION...END

0개의 댓글