바인드 변수

바인드 변수 (Bind variables)

애플리케이션은 사용자로부터 데이터를 입력받아 그 데이터를 SQL 문에서 사용할 수 있어요. 예를 들어 주소나 전화번호 같은 연락처 정보를 입력받는 경우가 있죠. 이런 사용자 입력을 SQL 문에 지정하는 방법 중 하나가 바인드 변수(bind variable)예요. SQL 문에 플레이스홀더(placeholder)를 넣고 각 플레이스홀더에 사용할 값을 지정하는 방식이에요.

출처: Snowflake SQL Reference

본문

애플리케이션은 사용자로부터 데이터를 받아 그 데이터를 SQL 문에서 사용할 수 있어요. 예를 들어 애플리케이션이 사용자에게 주소와 전화번호 같은 연락처 정보를 입력하도록 요청할 수 있죠.

이 사용자 입력을 SQL 문에 지정하려면, 사용자 입력과 문의 다른 부분을 이어 붙여 SQL 문의 문자열을 프로그래밍 방식으로 만들 수 있어요. 또는 바인드 변수(bind variables) 를 사용할 수도 있어요. 바인드 변수를 사용하려면 SQL 문의 텍스트에 하나 이상의 플레이스홀더를 넣고, 각 플레이스홀더에 사용할 변수(값)를 지정해요.

바인드 변수 개요

바인드 변수를 사용하면 SQL 문의 리터럴을 플레이스홀더로 바꿔요. 예를 들어 다음 SQL 문은 삽입할 값에 리터럴을 사용해요.

INSERT INTO t (c1, c2) VALUES (1, 'Test string');

다음 SQL 문은 삽입할 값에 플레이스홀더를 사용해요.

INSERT INTO t (c1, c2) VALUES (?, ?);

애플리케이션 코드는 SQL 문의 각 플레이스홀더에 데이터를 바인드해요. 데이터를 플레이스홀더에 바인드하는 방법은 프로그래밍 언어에 따라 달라요. 플레이스홀더 문법도 언어마다 달라서, ? 또는 :_varname_ 또는 %_varname_ 중 하나예요.

JavaScript 저장 프로시저에서 바인드 변수 사용하기

JavaScript로 SQL을 실행하는 저장 프로시저를 만들 수 있어요.

JavaScript 코드에서 바인드 변수를 지정하려면 ? 플레이스홀더를 사용해요. 예를 들어 다음 INSERT 문은 테이블 행에 삽입되는 값에 대한 바인드 변수를 지정해요.

INSERT INTO t (col1, col2) VALUES (?, ?)

JavaScript 코드에서는 대부분의 SQL 문의 값에 바인드 변수를 사용할 수 있어요. 제한 사항은 바인드 변수 제한 사항을 참고해요.

JavaScript에서 바인드 변수를 사용하는 자세한 내용은 변수 바인딩(Binding variables)을 참고해요.

Snowflake Scripting에서 바인드 변수 사용하기

Snowflake Scripting으로 SQL을 실행하는 프로시저 코드(코드 블록, 저장 프로시저)를 만들 수 있어요. Snowflake Scripting 코드에서 바인드 변수를 지정하려면 변수 이름 앞에 콜론(:)을 붙여요. 예를 들어 다음 INSERT 문은 variable1이라는 이름의 바인드 변수를 지정해요.

INSERT INTO t (c1) VALUES (:variable1)

EXECUTE IMMEDIATE 명령 또는 커서 OPEN 명령에서 SQL을 실행할 때 USING 절로 변수를 바인드할 수 있어요.

이 예제는 EXECUTE IMMEDIATE 명령에서 USING 절로 변수를 바인드해요.

EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price);

이 코드를 포함한 전체 예제는 바인드 변수를 포함한 문 실행을 참고해요.

커서를 선언할 때 SELECT 문에서 바인드 파라미터(? 문자)를 지정할 수 있어요. 그런 다음 커서를 열 때 USING 절에서 이 파라미터들을 변수에 바인드할 수 있어요.

다음 예제는 커서를 선언하고 바인드 파라미터를 지정한 다음, USING 절로 커서를 엽니다.

LET c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? AND price < ?;
OPEN c1 USING (minimum_price, maximum_price);

Snowflake Scripting은 위치에 따라 바인드 변수에 번호를 매기고, SQL 문에서 바인드 변수를 재사용하는 것도 지원해요. 번호가 매겨진 바인드 변수의 경우 각 변수 선언에 인덱스가 할당되고, :_n_으로 n번째 선언 변수를 참조할 수 있어요. 예를 들어 다음 Snowflake Scripting 블록은 i 변수에 바인드 변수 :1을, v 변수에 :2를 지정하고, SQL 문에서 :1 바인드 변수를 재사용해요.

EXECUTE IMMEDIATE $$
DECLARE
  i INTEGER DEFAULT 1;
  v VARCHAR DEFAULT 'SnowFlake';
  r RESULTSET;
BEGIN
  CREATE OR REPLACE TABLE snowflake_scripting_bind_demo (id INTEGER, value VARCHAR);
  EXECUTE IMMEDIATE 'INSERT INTO snowflake_scripting_bind_demo (id, value)
    SELECT :1, (:2 || :1)' USING (i, v);
  r := (SELECT * FROM snowflake_scripting_bind_demo);
  RETURN TABLE(r);
END;
$$
;

+----+------------+
| ID | VALUE      |
|----+------------|
|  1 | SnowFlake1 |
+----+------------+

Snowflake Scripting 코드에서는 대부분의 SQL 문의 값에 바인드 변수를 사용할 수 있어요. 제한 사항은 바인드 변수 제한 사항을 참고해요.

Snowflake Scripting에서 바인드 변수를 사용하는 자세한 내용은 SQL 문에서 변수 사용(바인딩)과 SQL 문에서 인자 사용(바인딩)을 참고해요.

SQL API에서 바인드 변수 사용하기

Snowflake SQL API로 Snowflake 데이터베이스의 데이터에 접근하고 업데이트할 수 있어요. SQL API를 사용해 SQL 문을 제출하고 배포를 관리하는 애플리케이션을 만들 수 있어요.

SQL 문을 실행하는 요청을 제출할 때, 문의 값에 바인드 변수를 사용할 수 있어요. 자세한 내용은 문에서 바인드 변수 사용을 참고해요.

드라이버에서 바인드 변수 사용하기

Snowflake 드라이버를 사용하면 Snowflake에서 연산을 수행하는 애플리케이션을 작성할 수 있어요. 드라이버는 Go, Java, Python 같은 프로그래밍 언어를 지원해요. 특정 드라이버의 애플리케이션에서 바인드 변수를 사용하는 방법은 해당 드라이버 링크를 따라가요.

Note

PHP 드라이버는 바인드 변수를 지원하지 않아요.

값 배열(array)로 바인드 변수 사용하기

SQL 문의 변수에 값 배열을 바인드할 수 있어요. 이 기법을 사용하면 여러 행을 단일 배치(batch)로 삽입해 성능을 개선할 수 있는데, 네트워크 왕복과 컴파일을 피할 수 있기 때문이에요. 배열 바인드 사용은 "벌크 삽입(bulk insert)" 또는 "배치 삽입(batch insert)"이라고도 불려요.

Note

Snowflake는 배열 바인드 대신 사용하길 권장하는 다른 데이터 로딩 방법을 지원해요. 자세한 내용은 Snowflake에 데이터 로드와 데이터 로딩·언로딩 명령을 참고해요.

다음은 Python 코드의 배열 바인드 예제예요.

conn = snowflake.connector.connect( ... )
rows_to_insert = [('milk', 2), ('apple', 3), ('egg', 2)]
conn.cursor().executemany(
            "insert into grocery (item, quantity) values (?, ?)",
            rows_to_insert)

이 예제는 바인드 목록 [('milk', 2), ('apple', 3), ('egg', 2)]을 지정해요. 애플리케이션이 바인드 목록을 지정하는 방식은 프로그래밍 언어에 따라 달라요.

이 코드는 테이블에 세 개의 행을 삽입해요.

+-------+----+
| C1    | C2 |
|-------+----|
| milk  |  2 |
| apple |  3 |
| egg   |  2 |
+-------+----+

특정 드라이버의 애플리케이션에서 배열 바인드를 사용하는 방법은 해당 드라이버 링크를 따라가요.

Note

PHP 드라이버는 배열 바인드를 지원하지 않아요.

배열 바인드 사용의 제한 사항

배열 바인드에는 다음 제한 사항이 적용돼요.

INSERT INTO … VALUES 문만 배열 바인드 변수를 포함할 수 있어요.
VALUES 절은 바인드 변수의 단일 행 목록이어야 해요. 예를 들어 다음 VALUES 절은 허용되지 않아요.

VALUES (?,?), (?,?)

배열 바인드 없이 여러 행 삽입하기

INSERT 문은 배열 바인드를 사용하지 않고 바인드 변수로 여러 행을 삽입할 수 있어요. 다음 예제는 두 행에 값을 삽입하지만 배열 바인드를 사용하지 않아요.

INSERT INTO t VALUES (?,?), (?,?);

예를 들어 애플리케이션이 플레이스홀더에 대해 순서대로 [1,'String1',2,'String2']와 동일한 바인드 목록을 지정할 수 있어요. VALUES 절이 둘 이상의 행을 지정하므로, 이 문은 동적인 수의 행이 아니라 정확히 지정된 수의 값(예제에서는 4개)만 삽입해요.

반구조적(semi-structured) 데이터로 바인드 변수 사용하기

반구조적 데이터로 변수를 바인드하려면 변수를 문자열 타입으로 바인드하고, PARSE_JSON이나 ARRAY_CONSTRUCT 같은 함수를 사용해요.

다음 예제는 VARIANT 컬럼이 하나 있는 테이블을 만들고, PARSE_JSON 함수를 호출해 바인드 변수로 반구조적 데이터를 테이블에 삽입해요.

CREATE TABLE t (a VARIANT);
-- Code that supplies a bind value for ? of '{'a': 'abc', 'x': 'xyz'}'
INSERT INTO t SELECT PARSE_JSON(a) FROM VALUES (?);

다음 예제는 테이블을 쿼리해요:

SELECT * FROM t;

쿼리는 다음 출력을 반환해요.

+---------------+
| A             |
|---------------|
| {             |
|   "a": "abc", |
|   "x": "xyz"  |
| }             |
+---------------+

다음 문은 ARRAY_CONSTRUCT 함수를 호출해 바인드 변수로 반구조적 데이터 배열을 VARIANT 컬럼에 삽입해요.

INSERT INTO t SELECT ARRAY_CONSTRUCT(column1) FROM VALUES (?);

이 두 예제 모두 단일 행을 삽입할 수 있고, 배열 바인드를 사용해 한 배치에 여러 행을 삽입할 수도 있어요. 이 기법은 VARIANT 컬럼에서 유효한 모든 유형의 반구조적 데이터를 삽입하는 데 사용할 수 있어요.

바인드 변수 값 검색

실행된 쿼리에서 바인드 변수의 값을 검색하려면 INFORMATION_SCHEMA 스키마의 BIND_VALUES 테이블 함수를 사용할 수 있어요. 이 함수로 JavaScript와 Snowflake Scripting 코드를 포함해 바인드 변수를 지원하는 모든 코드에서 바인드 변수 값을 검색할 수 있어요.

또한 QUERY_HISTORY Account Usage 뷰, QUERY_HISTORY Organization Usage 뷰, QUERY_HISTORY 함수의 출력에서 bind_values 컬럼으로 이 바인드 변수 값에 접근할 수 있어요.

사용자가 바인드 값에 접근하지 못하게 하려면 ALLOW_BIND_VALUES_ACCESS 계정 수준 파라미터를 FALSE로 설정해요.

다음 경우에 바인드 변수 값을 검색하고 싶을 수 있어요.

쿼리 문제 해결 — 쿼리에 사용된 정확한 바인드 값을 알면 쿼리를 최적화하고 다음 유형의 문제를 디버깅하기 쉬워요.
- 쿼리가 실행되지 않는 경우
- 쿼리 성능이 나쁜 경우
- 쿼리가 캐시나 예상 실행 계획을 사용하지 않는 경우
테스트를 위한 쿼리 재현 — 개발자와 DBA는 바인드 변수 값으로 사용자 생성 쿼리를 재현해 문제를 복제하고 부하 테스트를 할 수 있어요.
감사(audit)와 컴플라이언스 — 보안과 컴플라이언스 목적으로 조직은 사용자가 접근하는 데이터를 감사해야 해요. 바인드 변수 값을 사용해 사용자가 정확히 어떤 데이터를 검색했는지 파악할 수 있어요.

바인드 변수 값을 검색하는 예제

다음 쿼리는 이전 쿼리의 바인드 변수 값을 반환해요.

SELECT * FROM TABLE(
  INFORMATION_SCHEMA.BIND_VALUES('<query_id_value>'));

SELECT bind_values
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE query_id = '<query_id_value>';

_query_id_value_를 쿼리 ID로 바꿔요. LAST_QUERY_ID 함수로 이전 쿼리의 ID를 반환할 수 있어요.

Note

QUERY_HISTORY 뷰의 지연 시간(latency)은 최대 45분일 수 있어요.

다음 예제들은 BIND_VALUES 함수를 사용해요.

이름이 지정된 바인드 변수를 검색하는 Snowflake Scripting 예제
위치(positional) 바인드 변수를 검색하는 Python Connector 예제

이름이 지정된 바인드 변수를 검색하는 Snowflake Scripting 예제

바인드 변수를 사용하는 문을 포함한 다음 Snowflake Scripting 익명 블록을 실행해요.

DECLARE
  name STRING;
  temperature FLOAT;
  res RESULTSET;
BEGIN
  name := 'Snowman';
  temperature := -20.14;
  res := (
    SELECT
      CONCAT('Hello ', :NAME, '!') as greeting,
      CONCAT('It is ', :TEMPERATURE, 'deg C today.') as weather
  );
  RETURN LAST_QUERY_ID();
END;

Note: Snowflake CLI, SnowSQL, Classic Console, 또는 Python Connector 코드의 execute_stream, execute_string 메서드를 사용한다면, 대신 이 예제를 사용해요(Snowflake CLI, SnowSQL, Python Connector에서 Snowflake Scripting 사용):

EXECUTE IMMEDIATE
$$
DECLARE
  name STRING;
  temperature FLOAT;
  res RESULTSET;
BEGIN
  name := 'Snowman';
  temperature := -20.14;
  res := (
    SELECT
      CONCAT('Hello ', :NAME, '!') as greeting,
      CONCAT('It is ', :TEMPERATURE, 'deg C today.') as weather
  );
  RETURN LAST_QUERY_ID();
END;
$$
;

블록은 바인드 변수를 사용하는 문의 쿼리 ID를 반환해요.

Note

문은 여기에 표시된 것과 다른 쿼리 ID를 반환할 거예요.

+--------------------------------------+
| anonymous block                      |
|--------------------------------------|
| 01bbe3d6-0109-0863-0000-a99502ffa062 |
+--------------------------------------+

익명 블록에서 사용된 바인드 변수를 검색하려면 다음 쿼리를 실행해요. 익명 블록을 실행한 뒤 출력에서 01bbe3d6-0109-0863-0000-a99502ffa062를 쿼리 ID로 바꿔요.

SELECT * FROM TABLE(
  INFORMATION_SCHEMA.BIND_VALUES('01bbe3d6-0109-0863-0000-a99502ffa062'));

+--------------------------------------+----------+-------------+------+---------+
| QUERY_ID                             | POSITION | NAME        | TYPE | VALUE   |
|--------------------------------------+----------+-------------+------+---------|
| 01bbe3d6-0109-0863-0000-a99502ffa062 |     NULL | TEMPERATURE | REAL | -20.14  |
| 01bbe3d6-0109-0863-0000-a99502ffa062 |     NULL | NAME        | TEXT | Snowman |
+--------------------------------------+----------+-------------+------+---------+

위치 바인드 변수를 검색하는 Python Connector 예제

다음 Python Connector 코드는 BIND_VALUES 함수를 사용해 출력에서 위치 바인드 변수의 값을 보여 줘요.

cursor = conn.cursor()
print(cursor.execute(
            """
            SELECT
                CONCAT('Hello ', ?, '!') as greeting,
                CONCAT('It is ', ?, 'deg C today.') as weather
            """,
            params=["Snowman", -20.14],
        ).fetch_pandas_all())

query_id = cursor.sfqid
print(f"Bind values for query {query_id} are:")
print(cursor.execute("SELECT * FROM TABLE(INFORMATION_SCHEMA.BIND_VALUES(?))", params=[query_id]).fetch_pandas_all())


        GREETING                   WEATHER
0  Hello Snowman!  It is -20.14deg C today.

Bind values for query 01bbe918-0200-0001-0000-000000101145 are:

                               QUERY_ID POSITION  NAME  TYPE    VALUE
0  01bbe918-0200-0001-0000-000000101145        1  None  TEXT  Snowman
1  01bbe918-0200-0001-0000-000000101145        2  None  REAL   -20.14

바인드 변수 제한 사항

바인드 변수에는 다음 제한 사항이 적용돼요.

SELECT 문의 제한 사항:
- 바인드 변수는 데이터 타입 정의의 일부인 숫자(예: NUMBER(?))나 정렬 규칙 지정(예: COLLATE ?)을 대체할 수 없어요.
- 바인드 변수는 스테이지(stage)의 파일을 쿼리하는 SELECT 문의 소스로 사용될 수 없어요.
DDL 명령의 제한 사항:
- 다음 DDL 명령에서는 바인드 변수를 사용할 수 없어요.
  - CREATE/ALTER INTEGRATION
  - CREATE/ALTER REPLICATION GROUP
  - CREATE/ALTER PIPE
  - CREATE TABLE … USING TEMPLATE
- 다음 절에서는 바인드 변수를 사용할 수 없어요.
  - ALTER COLUMN
  - COMMENT ON CONSTRAINT
- CREATE/ALTER 명령에서 다음 파라미터의 값에는 바인드 변수를 사용할 수 없어요.
  - CREDENTIALS
  - DIRECTORY
  - ENCRYPTION
  - IMPORTS
  - PACKAGES
  - REFRESH
  - TAG
  - 외부 테이블(external tables)에 특화된 파라미터
- 바인드 변수는 FILE FORMAT 값의 일부인 속성에 사용할 수 없어요.
COPY INTO 명령에서 다음 파라미터의 값에는 바인드 변수를 사용할 수 없어요.
- CREDENTIALS
- ENCRYPTION
- FILE_FORMAT
SHOW 명령에서 STARTS WITH 파라미터에는 바인드 변수를 사용할 수 없어요.
EXECUTE IMMEDIATE FROM 명령에서는 바인드 변수를 사용할 수 없어요.
바인드 변수 값은 다음에서 사용될 때 하나의 데이터 타입에서 다른 타입으로 자동 변환될 수 없어요.
- 데이터 타입을 명시적으로 지정하는 Snowflake Scripting 코드
- DDL 문
- 스테이지 이름

바인드 변수의 보안 고려 사항

바인드 변수는 모든 경우에 민감 데이터를 가리지(mask) 않아요. 예를 들어 바인드 변수의 값이 오류 메시지와 다른 아티팩트에 나타날 수 있어요.

바인드 변수는 사용자 입력으로 SQL 문을 구성할 때 SQL 인젝션 공격을 막는 데 도움이 될 수 있어요. 그러나 바인드 변수는 잠재적인 보안 위험을 제시할 수도 있어요. SQL 문에 대한 입력이 외부 소스에서 온다면 반드시 검증(validate)되어야 해요. 자세한 내용은 SQL 인젝션을 참고해요.

더 알아보기 (Learn more)

EXECUTE IMMEDIATE — 동적 SQL 실행
Snowflake Scripting 변수 — 변수 선언과 바인딩
저장 프로시저 — JavaScript 저장 프로시저
데이터 타입 변환 — 타입 간 변환 규칙