반정형 데이터 타입

반정형 데이터 타입 (Semi-structured data types)

Snowflake의 반정형 데이터 타입인 VARIANT, OBJECT, ARRAY를 다루는 문서예요. 이 타입들은 다른 데이터 타입의 값을 담을 수 있고, 계층적 데이터 구조를 표현해 JSON, Avro, ORC, Parquet, XML 같은 반정형 데이터를 로딩·운영하는 데 사용돼요. 각 타입의 특징, 삽입, 상수, 요소 접근 방법과 예제를 확인할 수 있어요.

출처: Snowflake SQL Reference

본문

다음 Snowflake 데이터 타입은 다른 데이터 타입을 담을 수 있어요.

• VARIANT (다른 어떤 데이터 타입의 값도 담을 수 있음).
• OBJECT (VARIANT 값을 직접 담을 수 있으므로, 자신을 포함해 다른 어떤 데이터 타입의 값도 간접적으로 담을 수 있음).
• ARRAY (VARIANT 값을 직접 담을 수 있으므로, 자신을 포함해 다른 어떤 데이터 타입의 값도 간접적으로 담을 수 있음).

이 데이터 타입들을 종종 반정형 데이터 타입(semi-structured data types) 이라고 불러요. 엄밀히 말하면, OBJECT만이 그 자체로 진정한 반정형 데이터 타입의 모든 특성을 가진 유일한 타입이에요. 하지만 이 타입들을 결합하면 임의의 계층적 데이터 구조(hierarchical data structures)를 명시적으로 표현할 수 있으며, 이는 JSON, Avro, ORC, Parquet, XML 같은 반정형 형식의 데이터를 로딩하고 운영하는 데 사용될 수 있어요.

참고: 정형 데이터 타입(Structured data types)(예: ARRAY(INTEGER), OBJECT(city VARCHAR), MAP(VARCHAR, VARCHAR))에 대한 정보는 정형 데이터 타입(Structured data types)을 참조하세요.

VARIANT

VARIANT 값은 OBJECT와 ARRAY 값을 포함해 다른 어떤 타입의 값도 저장할 수 있어요.

VARIANT 값의 특징 (Characteristics of a VARIANT value)

VARIANT 값은 최대 128 MB의 압축되지 않은 데이터까지 담을 수 있어요. 그러나 실제로는 내부 오버헤드 때문에 최대 크기는 보통 더 작아요. 최대 크기는 저장되는 객체에 따라서도 달라져요.

VARIANT 데이터 삽입 (Inserting VARIANT data)

VARIANT 데이터를 직접 삽입하려면 INSERT INTO ... SELECT를 사용해요. 다음 예제는 JSON 형식 데이터를 VARIANT 값에 삽입하는 방법을 보여줘요.

CREATE OR REPLACE TABLE variant_insert (v VARIANT);
INSERT INTO variant_insert (v)
  SELECT PARSE_JSON('{"key3": "value3", "key4": "value4"}');
SELECT * FROM variant_insert;

+---------------------+
| V                   |
|---------------------|
| {                   |
|   "key3": "value3", |
|   "key4": "value4"  |
| }                   |
+---------------------+

VARIANT 값 사용하기 (Using VARIANT values)

값을 VARIANT 데이터 타입으로/으로부터 변환하려면 CAST 함수, TO_VARIANT 함수, 또는 :: 연산자(예: _expression_ ::VARIANT)로 명시적으로 캐스팅할 수 있어요.

어떤 상황에서는 값이 VARIANT 값으로 암시적으로 캐스팅될 수 있어요. 자세한 내용은 데이터 타입 변환(Data type conversion)을 참조하세요.

다음 예제는 VARIANT 값을 사용하는 방법을 보여줘요. VARIANT 값에서와 VARIANT 값으로 변환하는 방법을 포함해요.

테이블을 만들고 값을 삽입해요.

CREATE OR REPLACE TABLE varia (float1 FLOAT, v VARIANT, float2 FLOAT);
INSERT INTO varia (float1, v, float2) VALUES (1.23, NULL, NULL);

첫 번째 UPDATE는 FLOAT 값을 VARIANT 값으로 변환해요. 두 번째 UPDATE는 VARIANT 값을 FLOAT 값으로 변환해요.

UPDATE varia SET v = TO_VARIANT(float1);  -- converts from a FLOAT value to a VARIANT value.
UPDATE varia SET float2 = v::FLOAT;       -- converts from a VARIANT value to a FLOAT value.

모든 값을 선택해요.

SELECT * FROM varia;

+--------+-----------------------+--------+
| FLOAT1 | V                     | FLOAT2 |
|--------+-----------------------+--------|
|   1.23 | 1.230000000000000e+00 |   1.23 |
+--------+-----------------------+--------+

이전 예제에서 보듯이, VARIANT 데이터 타입의 값을 변환하려면 VARIANT 값을 대상 데이터 타입으로 캐스팅해요. 예를 들어 다음 문은 :: 연산자를 사용해 VARIANT를 FLOAT로 변환해요.

SELECT my_variant_column::FLOAT * 3.14 FROM ...;

VARIANT 데이터는 값과 값의 데이터 타입을 모두 저장해요. 따라서 값의 데이터 타입이 유효한 표현식에서, VARIANT를 먼저 캐스팅하지 않고 VARIANT 값을 사용할 수 있어요. 예를 들어 VARIANT 열 my_variant_column이 숫자 값을 포함하면 my_variant_column을 다른 숫자 값으로 직접 곱할 수 있어요.

SELECT my_variant_column * 3.14 FROM ...;

TYPEOF 함수로 값의 고유 데이터 타입을 검색할 수 있어요.

기본적으로 VARCHAR, DATE, TIME, TIMESTAMP 값이 VARIANT 열에서 검색되면 값은 큰따옴표로 둘러싸여요. 값을 기본 데이터 타입으로 명시적으로 캐스팅하면(예: VARIANT에서 VARCHAR로) 큰따옴표를 제거할 수 있어요. 예를 들어:

SELECT 'Sample', 'Sample'::VARIANT, 'Sample'::VARIANT::VARCHAR;

+----------+-------------------+----------------------------+
| 'SAMPLE' | 'SAMPLE'::VARIANT | 'SAMPLE'::VARIANT::VARCHAR |
|----------+-------------------+----------------------------|
| Sample   | "Sample"          | Sample                     |
+----------+-------------------+----------------------------+

VARIANT 값은 누락될 수 있고(SQL NULL 포함), 이는 반정형 데이터에서 null 값을 나타내는 데 사용되는 실제 값인 VARIANT null 값과는 달라요. VARIANT null은 자기 자신과 같게 비교되는 진짜 값이에요. 자세한 내용은 NULL 값(NULL values)을 참조하세요.

JSON 형식에서 로딩되어 VARIANT 열에 저장된 데이터라면 다음 고려 사항이 적용돼요.

• 대부분 규칙적이고 문자열과 숫자 같은 네이티브 JSON 타입만 사용하는 데이터의 경우, 관계형 데이터와 VARIANT 열의 데이터에 대한 저장 및 쿼리 연산 성능은 매우 유사해요.
• 네이티브가 아닌 데이터(날짜, 타임스탬프 등)의 경우, VARIANT 열에 로딩되면 값이 문자열로 저장돼요. 따라서 이런 값에 대한 연산은 해당 데이터 타입을 가진 관계형 열에 저장된 경우보다 느리고 더 많은 공간을 소비할 수 있어요.

VARIANT 데이터 타입 사용에 대한 자세한 내용은 VARIANT에 저장된 반정형 데이터에 대한 고려 사항을 참조하세요.

VARIANT 열에 저장된 반정형 데이터를 쿼리하는 방법에 대한 자세한 내용은 반정형 데이터 쿼리(Querying Semi-structured Data)를 참조하세요.

VARIANT 데이터의 일반적인 용도 (Common uses for VARIANT data)

VARIANT 데이터는 보통 다음 경우에 사용돼요.

• 두 개 이상의 ARRAY 값이나 OBJECT 값을 포함하는 계층을 명시적으로 정의해 계층적 데이터(hierarchical data)를 만들고 싶을 때.
• 데이터의 계층적 구조를 명시적으로 설명하지 않고 JSON, Avro, ORC, Parquet 데이터를 직접 로딩하고 싶을 때.

Snowflake는 JSON, Avro, ORC, Parquet 형식의 데이터를 ARRAY, OBJECT, VARIANT 데이터의 내부 계층으로 변환하고 그 계층적 데이터를 VARIANT 값에 직접 저장할 수 있어요. 데이터 계층을 직접 구성할 수도 있지만, 보통은 Snowflake가 대신 처리하게 하는 것이 더 쉬워요.

반정형 데이터 로딩과 변환에 대한 자세한 내용은 반정형 데이터 로딩(Load semi-structured data)을 참조하세요.

OBJECT

Snowflake OBJECT 값은 JSON "object"와 유사해요. 다른 프로그래밍 언어에서는 해당 데이터 타입을 "dictionary", "hash", 또는 "map"이라고 부르는 경우가 많아요.

OBJECT 값은 키-값 쌍(key-value pairs)을 포함해요.

OBJECT 값의 특징 (Characteristics of an OBJECT value)

Snowflake 반정형 OBJECT 데이터에서 각 키는 VARCHAR 값이고, 각 값은 VARIANT 값이에요.

VARIANT 값은 다른 어떤 데이터 타입의 값도 저장할 수 있으므로, 서로 다른 VARIANT 값(서로 다른 키-값 쌍에서)은 서로 다른 기본 데이터 타입을 가질 수 있어요. 예를 들어 OBJECT 값은 사람 이름을 VARCHAR 값으로, 사람 나이를 INTEGER 값으로 담을 수 있어요. 다음 예제에서 이름과 나이 모두 VARIANT 값으로 캐스팅돼요.

SELECT OBJECT_CONSTRUCT(
  'name', 'Jones'::VARIANT,
  'age',  42::VARIANT);

OBJECT 데이터에는 다음 고려 사항이 적용돼요.

• 현재 Snowflake는 명시적으로 타입이 지정된 객체(explicitly-typed objects)를 지원하지 않아요.
• 키-값 쌍에서 키는 빈 문자열이어서는 안 되고, 키도 값도 NULL이어서는 안 돼요.
• OBJECT 값의 최대 길이는 128 MB예요.
• OBJECT 값은 반정형 데이터를 담을 수 있어요.
• OBJECT 값을 사용해 계층적 데이터 구조(hierarchical data structures)를 만들 수 있어요.

참고: Snowflake는 VARIANT 값 이외의 값도 허용하는 정형 OBJECT 데이터 타입도 지원해요. 정형 OBJECT 타입은 또한 그 타입의 OBJECT 값에 있어야 하는 키를 정의해요. 자세한 내용은 정형 데이터 타입(Structured data types)을 참조하세요.

OBJECT 데이터 삽입 (Inserting OBJECT data)

OBJECT 데이터를 직접 삽입하려면 INSERT INTO ... SELECT를 사용해요.

다음 예제는 OBJECT_CONSTRUCT 함수를 사용해 삽입하는 OBJECT 값을 구성해요.

CREATE OR REPLACE TABLE object_example (object_column OBJECT);
INSERT INTO object_example (object_column)
  SELECT OBJECT_CONSTRUCT('thirteen', 13::VARIANT, 'zero', 0::VARIANT);
SELECT * FROM object_example;

+-------------------+
| OBJECT_COLUMN     |
|-------------------|
| {                 |
|   "thirteen": 13, |
|   "zero": 0       |
| }                 |
+-------------------+

각 키-값 쌍에서 값은 VARIANT로 명시적으로 캐스팅됐어요. 이런 경우 명시적 캐스팅은 필수가 아니었어요. Snowflake는 VARIANT로 암시적으로 캐스팅할 수 있어요. 암시적 캐스팅에 대한 정보는 데이터 타입 변환(Data type conversion)을 참조하세요.

OBJECT 상수를 사용해 삽입할 OBJECT 값을 지정할 수도 있어요. 자세한 내용은 OBJECT 상수를 참조하세요.

OBJECT 상수 (OBJECT constants)

상수(Constant) (또는 리터럴(literal))는 고정된 데이터 값을 말해요. Snowflake는 상수를 사용해 OBJECT 값을 지정하는 것을 지원해요. OBJECT 상수는 중괄호({와 })로 구분돼요.

OBJECT 상수는 다음 구문을 가져요.

{ [<key>: <value> [, <key>: <value> , ...]] }

여기서:

_key_

키-값 쌍의 키예요. _key_는 문자열 리터럴이어야 해요.

_value_

키와 연관된 값이에요. _value_는 리터럴 또는 표현식일 수 있어요. _value_는 어떤 데이터 타입이든 될 수 있어요.

다음은 OBJECT 상수를 지정하는 예시예요.

• {}는 빈 OBJECT 값이에요.

• { 'key1': 'value1' , 'key2': 'value2' }는 값에 리터럴을 사용해 OBJECT 값의 지정된 키-값 쌍을 포함해요.

• { 'key1': c1+1 , 'key2': c1+2 }는 값에 표현식을 사용해 OBJECT 값의 지정된 키-값 쌍을 포함해요.

• {*}는 속성 이름을 키로, 연관된 값을 값으로 사용해 지정된 데이터에서 OBJECT 값을 구성하는 와일드카드예요.

객체 상수에서 지정될 때, 와일드카드는 테이블 이름이나 별칭으로 한정되지 않거나 한정될 수 있어요. 예를 들어 다음 와일드카드 지정은 모두 유효해요.

SELECT {*} FROM my_table;

SELECT {my_table1.*}
  FROM my_table1 INNER JOIN my_table2
    ON my_table2.col1 = my_table1.col1;

OBJECT 상수에서 ILIKE와 EXCLUDE 키워드를 사용할 수 있어요. 특정 열을 선택하려면 ILIKE 키워드를 사용해요. 예를 들어 다음 쿼리는 my_table 테이블에서 패턴 col1%와 일치하는 열을 선택해요.

SELECT {* ILIKE 'col1%'} FROM my_table;

특정 열을 제외하려면 EXCLUDE 키워드를 사용해요. 예를 들어 다음 쿼리는 my_table 테이블에서 col1을 제외해요.

SELECT {* EXCLUDE col1} FROM my_table;

다음 쿼리는 my_table 테이블에서 col1과 col2를 제외해요.

SELECT {* EXCLUDE (col1, col2)} FROM my_table;

와일드카드는 키-값 쌍과 섞일 수 없어요. 예를 들어 다음 와일드카드 지정은 허용되지 않아요.

SELECT {*, 'k': 'v'} FROM my_table;

하나의 객체 상수에 여러 와일드카드를 사용할 수 없어요. 예를 들어 다음 와일드카드 지정은 허용되지 않아요.

SELECT {t1.*, t2.*} FROM t1, t2;

다음 문은 OBJECT 상수와 OBJECT_CONSTRUCT 함수를 사용해 OBJECT 데이터를 테이블에 삽입해요. OBJECT 값은 캐나다 두 주(州)의 이름과 주도를 포함해요.

CREATE OR REPLACE TABLE my_object_table (my_object OBJECT);

INSERT INTO my_object_table (my_object)
  SELECT { 'PROVINCE': 'Alberta'::VARIANT , 'CAPITAL': 'Edmonton'::VARIANT };

INSERT INTO my_object_table (my_object)
  SELECT OBJECT_CONSTRUCT('PROVINCE', 'Manitoba'::VARIANT , 'CAPITAL', 'Winnipeg'::VARIANT );

SELECT * FROM my_object_table;

+--------------------------+
| MY_OBJECT                |
|--------------------------|
| {                        |
|   "CAPITAL": "Edmonton", |
|   "PROVINCE": "Alberta"  |
| }                        |
| {                        |
|   "CAPITAL": "Winnipeg", |
|   "PROVINCE": "Manitoba" |
| }                        |
+--------------------------+

다음 예제는 FROM 절에서 속성 이름과 값을 가져와 OBJECT 데이터를 삽입하기 위해 와일드카드({*})를 사용해요. 먼저 주와 주도 이름을 포함하는 VARCHAR 값으로 demo_ca_provinces라는 테이블을 만들어요.

CREATE OR REPLACE TABLE demo_ca_provinces (province VARCHAR, capital VARCHAR);
INSERT INTO demo_ca_provinces (province, capital) VALUES
  ('Ontario', 'Toronto'),
  ('British Columbia', 'Victoria');

SELECT province, capital
  FROM demo_ca_provinces
  ORDER BY province;

+------------------+----------+
| PROVINCE         | CAPITAL  |
|------------------+----------|
| British Columbia | Victoria |
| Ontario          | Toronto  |
+------------------+----------+

demo_ca_provinces 테이블의 데이터를 사용해 my_object_table에 객체 데이터를 삽입해요.

INSERT INTO my_object_table (my_object)
  SELECT {*} FROM demo_ca_provinces;

SELECT * FROM my_object_table;

+----------------------------------+
| MY_OBJECT                        |
|----------------------------------|
| {                                |
|   "CAPITAL": "Edmonton",         |
|   "PROVINCE": "Alberta"          |
| }                                |
| {                                |
|   "CAPITAL": "Winnipeg",         |
|   "PROVINCE": "Manitoba"         |
| }                                |
| {                                |
|   "CAPITAL": "Toronto",          |
|   "PROVINCE": "Ontario"          |
| }                                |
| {                                |
|   "CAPITAL": "Victoria",         |
|   "PROVINCE": "British Columbia" |
| }                                |
+----------------------------------+

다음 예제는 OBJECT 상수의 값에 표현식을 사용해요.

SET my_variable = 10;
SELECT {'key1': $my_variable+1, 'key2': $my_variable+2};

+--------------------------------------------------+
| {'KEY1': $MY_VARIABLE+1, 'KEY2': $MY_VARIABLE+2} |
|--------------------------------------------------|
| {                                                |
|   "key1": 11,                                    |
|   "key2": 12                                     |
| }                                                |
+--------------------------------------------------+

SQL 문은 Snowflake SQL의 다른 곳에서와 마찬가지로 OBJECT 값 안의 문자열 리터럴을 작은따옴표로 지정하지만, OBJECT 값 안의 문자열 리터럴은 큰따옴표로 표시돼요.

SELECT { 'Manitoba': 'Winnipeg' } AS province_capital;

+--------------------------+
| PROVINCE_CAPITAL         |
|--------------------------|
| {                        |
|   "Manitoba": "Winnipeg" |
| }                        |
+--------------------------+

키로 OBJECT 값의 요소 접근하기 (Accessing elements of an OBJECT value by key)

OBJECT 값의 값을 검색하려면 아래와 같이 대괄호(square brackets)에 키를 지정해요.

SELECT object_column['thirteen'] FROM object_example;

콜론 연산자도 사용할 수 있어요. 다음 명령은 대괄호를 사용하든 콜론을 사용하든 결과가 동일함을 보여줘요.

SELECT object_column['thirteen'],
       object_column:thirteen
  FROM object_example;

+---------------------------+------------------------+
| OBJECT_COLUMN['THIRTEEN'] | OBJECT_COLUMN:THIRTEEN |
|---------------------------+------------------------|
| 13                        | 13                     |
+---------------------------+------------------------+

콜론 연산자에 대한 자세한 내용은 중첩 데이터에 접근하는 : 및 . 연산자 사용을 설명하는 점 표기법(Dot Notation)을 참조하세요.

OBJECT 데이터의 일반적인 용도 (Common uses for OBJECT data)

OBJECT 데이터는 보통 다음 중 하나 이상이 성립할 때 사용돼요.

• 문자열로 식별되는 여러 데이터 조각이 있을 때. 예를 들어 주 이름으로 정보를 조회하고 싶다면 OBJECT 값을 사용하고 싶을 수 있어요.
• 데이터와 함께 데이터에 대한 정보를 저장하고 싶을 때. 이름(키)은 단순한 고유 식별자가 아니라 의미가 있어요.
• 정보에 자연스러운 순서가 없거나, 순서가 키에서만 추론될 수 있을 때.
• 데이터의 구조가 다양하거나, 데이터가 불완전할 수 있을 때. 예를 들어 보통 제목, 저자 이름, 출판 날짜를 포함하지만 때로 출판 날짜를 모르는 도서 카탈로그를 만들고 싶다면 OBJECT 값을 사용하고 싶을 수 있어요.

ARRAY

Snowflake 배열은 다른 많은 프로그래밍 언어의 배열과 유사해요. 배열은 0개 이상의 데이터 조각을 포함해요. 각 요소는 배열에서 위치를 지정해 접근돼요.

배열의 특징 (Characteristics of an array)

반정형 배열의 각 값은 VARIANT 타입이에요. VARIANT 값은 다른 어떤 데이터 타입의 값도 담을 수 있어요.

다른 데이터 타입의 값은 VARIANT 값으로 캐스팅된 다음 배열에 저장될 수 있어요. ARRAY_CONSTRUCT를 포함한 일부 배열 함수는 값을 VARIANT 값으로 암시적으로 캐스팅할 수 있어요.

배열은 VARIANT 값을 저장하고, VARIANT 값은 그 안에 다른 데이터 타입을 저장할 수 있기 때문에, 배열에 있는 값들의 기본 데이터 타입은 서로 다를 수 있어요. 그러나 대부분의 경우 데이터 요소는 동일하거나 호환되는 타입이므로 모두 같은 방식으로 처리될 수 있어요.

배열에는 다음 고려 사항이 적용돼요.

• Snowflake는 특정 non-VARIANT 타입의 요소로 이루어진 배열을 지원하지 않아요.
• Snowflake 배열은 요소 수를 지정하지 않고 선언돼요. 배열은 ARRAY_APPEND 같은 연산에 따라 동적으로 커질 수 있어요. Snowflake는 현재 고정 크기 배열을 지원하지 않아요.
• 배열은 SQL NULL 값과 JSON null 값을 모두 담을 수 있어요. 자세한 내용은 NULL 값(NULL values)을 참조하세요.
• 배열에 있는 모든 값의 이론적 최대 총 크기는 128 MB예요. 그러나 배열에는 내부 오버헤드가 있어요. 실제 최대 데이터 크기는 요소 수와 값에 따라 보통 더 작아요.

참고: Snowflake는 VARIANT 이외의 타입의 요소를 허용하는 정형 배열(structured arrays)도 지원해요. 자세한 내용은 정형 데이터 타입(Structured data types)을 참조하세요.

ARRAY 데이터 삽입 (Inserting ARRAY data)

ARRAY 데이터를 직접 삽입하려면 INSERT INTO ... SELECT를 사용해요.

다음 코드는 ARRAY_CONSTRUCT 함수를 사용해 삽입하는 배열을 구성해요.

CREATE OR REPLACE TABLE array_example (array_column ARRAY);
INSERT INTO array_example (array_column)
  SELECT ARRAY_CONSTRUCT(12, 'twelve', NULL);

ARRAY 상수를 사용해 삽입할 배열을 지정할 수도 있어요. 자세한 내용은 ARRAY 상수를 참조하세요.

ARRAY 상수 (ARRAY constants)

상수(Constant) (또는 리터럴(literal))는 고정된 데이터 값을 말해요. Snowflake는 상수를 사용해 ARRAY 값을 지정하는 것을 지원해요. ARRAY 상수는 대괄호([와 ])로 구분돼요.

ARRAY 상수는 다음 구문을 가져요.

[<value> [, <value> , ...]]

여기서:

_value_

배열 요소와 연관된 값이에요. _value_는 리터럴 또는 표현식일 수 있어요. _value_는 어떤 데이터 타입이든 될 수 있어요.

다음은 ARRAY 상수를 지정하는 예시예요.

• []는 빈 ARRAY 값이에요.
• [ 1 , 'value1' ]는 값에 리터럴을 사용해 ARRAY 상수의 지정된 값을 포함해요.
• [ c1+1 , c1+2 ]는 값에 표현식을 사용해 ARRAY 상수의 지정된 값을 포함해요.

다음 예제는 ARRAY 상수를 사용해 삽입할 배열을 지정해요.

INSERT INTO array_example (array_column)
  SELECT [ 12, 'twelve', NULL ];

다음 문은 ARRAY 상수와 ARRAY_CONSTRUCT 함수를 사용해 같은 작업을 수행해요.

UPDATE my_table SET my_array = [ 1, 2 ];

UPDATE my_table SET my_array = ARRAY_CONSTRUCT(1, 2);

다음 예제는 ARRAY 상수의 값에 표현식을 사용해요.

SET my_variable = 10;
SELECT [$my_variable+1, $my_variable+2];

+----------------------------------+
| [$MY_VARIABLE+1, $MY_VARIABLE+2] |
|----------------------------------|
| [                                |
|   11,                            |
|   12                             |
| ]                                |
+----------------------------------+

SQL 문은 Snowflake SQL의 다른 곳에서와 마찬가지로 배열 안의 문자열 리터럴을 작은따옴표로 지정하지만, 배열 안의 문자열 리터럴은 큰따옴표로 표시돼요.

SELECT [ 'Alberta', 'Manitoba' ] AS province;

+--------------+
| PROVINCE     |
|--------------|
| [            |
|   "Alberta", |
|   "Manitoba" |
| ]            |
+--------------+

인덱스나 슬라이스로 배열의 요소 접근하기 (Accessing elements of an array by index or by slice)

배열 인덱스는 0부터 시작하므로, 배열의 첫 번째 요소는 요소 0이에요.

배열의 값은 대괄호에 배열 요소의 인덱스 번호를 지정해 접근해요. 예를 들어 다음 쿼리는 my_array_column에 저장된 배열의 인덱스 위치 2의 값을 읽어요.

SELECT my_array_column[2] FROM my_table;

배열은 중첩될 수 있어요. 다음 쿼리는 중첩 배열의 0번째 요소의 0번째 요소를 읽어요.

SELECT my_array_column[0][0] FROM my_table;

배열 끝을 넘어서는 요소에 접근하려 하면 NULL을 반환해요.

배열의 _슬라이스(slice)_는 인접한 요소들의 시퀀스(즉 배열의 연속 부분 집합)예요.

ARRAY_SLICE 함수를 호출해 배열의 슬라이스에 접근할 수 있어요. 예를 들어:

SELECT ARRAY_SLICE(my_array_column, 5, 10) FROM my_table;

ARRAY_SLICE 함수는 지정된 시작 요소(위 예에서 5)부터 지정된 끝 요소(위 예에서 10)까지(끝 요소 미포함) 요소를 반환해요.

빈 배열이나 빈 슬라이스는 주로 사이에 아무것도 없는 대괄호 쌍([])으로 표시돼요.

조밀 배열과 희소 배열 (Dense and sparse arrays)

배열은 조밀(dense) 하거나 희소(sparse) 할 수 있어요.

조밀 배열에서 요소의 인덱스 값은 0에서 시작해 순차적이에요(0, 1, 2, ...). 그러나 희소 배열에서는 인덱스 값이 순차적이지 않을 수 있어요(예: 0, 2, 5). 값이 0에서 시작할 필요도 없어요.

인덱스에 해당하는 요소가 없으면 그 인덱스에 해당하는 값은 정의되지 않음(undefined) 이라고 해요. 예를 들어 희소 배열에 세 개의 요소가 있고 그 요소들이 인덱스 0, 2, 5에 있다면, 인덱스 1, 3, 4의 요소는 undefined이에요.

정의되지 않은 요소는 요소로 취급돼요. 예를 들어 인덱스 0, 2, 5에 요소가 있는(그리고 인덱스 5 이후에는 요소가 없는) 희소 배열의 앞선 예를 생각해 봐요. 인덱스 3과 4의 요소를 포함하는 슬라이스를 읽으면 출력은 다음과 같아요.

[ undefined, undefined ]

배열 끝을 넘어서는 슬라이스에 접근하려 하면 undefined 값의 배열이 아니라 빈 배열이 돼요. 다음 SELECT 문은 샘플 희소 배열의 마지막 요소를 넘어서 읽으려 해요.

SELECT ARRAY_SLICE(array_column, 6, 8) FROM table_1;

출력은 빈 배열이에요.

+---------------------------------+
| array_slice(array_column, 6, 8) |
+---------------------------------+
| [ ]                             |
+---------------------------------+

undefined는 NULL과 달라요. 배열의 NULL 값은 정의된 요소예요.

조밀 배열에서 각 요소는 그 값이 NULL이더라도 저장 공간을 소비해요. 희소 배열에서 undefined 요소는 직접 저장 공간을 소비하지 않아요.

조밀 배열에서 인덱스 값의 이론적 범위는 0에서 134217727이에요. (이론적 최대 요소 수는 134217728인데, 크기 상한이 128 MB, 즉 134217728 바이트이고 가장 작은 값이 1바이트이기 때문이에요.)

희소 배열에서 인덱스 값의 이론적 범위는 0에서 2^31 - 1이에요. 그러나 128 MB 제한 때문에 희소 배열은 2^31개의 값을 담을 수 없어요. 이론적 최대 값 수는 여전히 134217728로 제한돼요.

내부 오버헤드 때문에 조밀 배열과 희소 배열 모두의 실제 크기 제한은 이론적 최대값인 128 MB보다 적어도 약간 작아요.

ARRAY_INSERT 함수를 사용해 배열의 특정 인덱스 지점에 값을 삽입(다른 배열 요소는 undefined로 남김)해 희소 배열을 만들 수 있어요. ARRAY_INSERT는 요소를 오른쪽으로 밀어내므로 접근에 필요한 인덱스 값이 바뀌어요. 그래서 보통 희소 배열을 왼쪽에서 오른쪽으로(즉 0부터 위로, 삽입되는 각 새 값의 인덱스 값을 증가시키며) 채우는 것이 가장 좋아요.

ARRAY 데이터의 일반적인 용도 (Common uses for ARRAY data)

ARRAY 데이터는 보통 다음 중 하나 이상이 성립할 때 사용돼요.

• 데이터 모음이 있고, 모음의 각 조각이 동일하거나 유사하게 구조화되어 있을 때.
• 각 데이터 조각이 유사하게 처리될 때. 예를 들어 데이터를 반복하며 각 조각을 같은 방식으로 처리할 수 있어요.
• 데이터에 자연스러운 순서(예: 시간순)가 있을 때.

예제 (Examples)

다음 예제는 VARIANT, ARRAY, OBJECT 데이터가 있는 테이블에 대한 DESC TABLE 명령의 출력을 보여줘요.

CREATE OR REPLACE TABLE test_semi_structured(
  var VARIANT,
  arr ARRAY,
  obj OBJECT);

DESC TABLE test_semi_structured;

+------+---------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name | type    | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|------+---------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| VAR  | VARIANT | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| ARR  | ARRAY   | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| OBJ  | OBJECT  | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+------+---------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+

이 예제는 단순 값을 테이블에 로딩하는 방법과, 테이블을 쿼리할 때 그 값들이 어떻게 보이는지 보여줘요.

테이블을 만들고 데이터를 로딩해요.

CREATE OR REPLACE TABLE demonstration1 (
  ID INTEGER,
  array1 ARRAY,
  variant1 VARIANT,
  object1 OBJECT);

INSERT INTO demonstration1 (id, array1, variant1, object1)
  SELECT
    1,
    ARRAY_CONSTRUCT(1, 2, 3),
    PARSE_JSON(' { "key1": "value1", "key2": "value2" } '),
    PARSE_JSON(' { "outer_key1": { "inner_key1A": "1a", "inner_key1B": "1b" }, '
              ||
               '   "outer_key2": { "inner_key2": 2 } } ');

INSERT INTO demonstration1 (id, array1, variant1, object1)
  SELECT
    2,
    ARRAY_CONSTRUCT(1, 2, 3, NULL),
    PARSE_JSON(' { "key1": "value1", "key2": NULL } '),
    PARSE_JSON(' { "outer_key1": { "inner_key1A": "1a", "inner_key1B": NULL }, '
              ||
                '   "outer_key2": { "inner_key2": 2 } '
              ||
               ' } ');

이제 테이블의 데이터를 표시해요.

SELECT *
  FROM demonstration1
  ORDER BY id;

+----+-------------+---------------------+--------------------------+
| ID | ARRAY1      | VARIANT1            | OBJECT1                  |
|----+-------------+---------------------+--------------------------|
|  1 | [           | {                   | {                        |
|    |   1,        |   "key1": "value1", |   "outer_key1": {        |
|    |   2,        |   "key2": "value2"  |     "inner_key1A": "1a", |
|    |   3         | }                   |     "inner_key1B": "1b"  |
|    | ]           |                     |   },                     |
|    |             |                     |   "outer_key2": {        |
|    |             |                     |     "inner_key2": 2      |
|    |             |                     |   }                      |
|    |             |                     | }                        |
|  2 | [           | {                   | {                        |
|    |   1,        |   "key1": "value1", |   "outer_key1": {        |
|    |   2,        |   "key2": null      |     "inner_key1A": "1a", |
|    |   3,        | }                   |     "inner_key1B": null  |
|    |   undefined |                     |   },                     |
|    | ]           |                     |   "outer_key2": {        |
|    |             |                     |     "inner_key2": 2      |
|    |             |                     |   }                      |
|    |             |                     | }                        |
+----+-------------+---------------------+--------------------------+

추가 예제는 반정형 데이터 쿼리(Querying Semi-structured Data)를 참조하세요.

더 알아보기 (Learn more)

• 정형 데이터 타입 (Structured data types)
• 문자열 & 이진 데이터 타입 (String & binary data types)
• 비정형 데이터 타입 (Unstructured data types)