[Snowflake] Snowflake SnowPro Core 시험 Cheat Sheet 5 (데이터 변환)

5. 데이터 변환

5.1 표준 데이터로 작업하는 방법에 대해 설명하기

Snowflake에서 표준 데이터는 정형화된 테이블 형태의 데이터를 의미하며, 다양한 내장 함수와 명령어를 통해 이러한 데이터를 변환하고 분석할 수 있습니다. Snowflake는 특히 대규모 데이터셋을 효과적으로 처리할 수 있는 기능을 제공하여 성능을 높이고 데이터 관리에 효율성을 더합니다.

1) 추정 함수 (Approximation Functions)

 

• 설명: 추정 함수는 데이터 집합에 대해 근사 계산을 수행하여 성능을 최적화하는 기능을 제공합니다. 큰 데이터 집합에서는 정확한 결과를 계산하는 데 많은 리소스와 시간이 소요될 수 있습니다. Snowflake의 추정 함수는 이를 효율적으로 처리하기 위해 근사값을 계산합니다. 성능이 중요한 상황에서는 정확도보다 속도를 우선하여 빠른 결과를 제공할 수 있습니다.
• 예제 함수:
  • APPROX_COUNT_DISTINCT: 데이터 집합 내 고유 값 개수를 근사 계산합니다.

예제:

SELECT APPROX_COUNT_DISTINCT(column_name) FROM my_table;

 

이 함수는 COUNT(DISTINCT column_name)보다 빠르게 결과를 반환할 수 있으나, 정확도에서 약간의 차이가 있을 수 있습니다.

 

2) Sampling

샘플링은 데이터 분석을 위한 데이터 집합을 추출할 때 전체 데이터에서 일부만 선택하여 분석하는 방법으로, 대규모 데이터셋의 경우 특히 유용합니다. Snowflake는 SAMPLE 및 TABLESAMPLE 명령어를 사용하여 다양한 샘플링 방법을 제공합니다.

• SAMPLE 명령어: 테이블에서 일정 비율의 데이터를 무작위로 선택하여 가져옵니다. 사용자가 지정한 비율에 따라 데이터를 샘플링합니다.

SELECT * FROM my_table SAMPLE (10);  -- 데이터의 약 10% 샘플링

 

• TABLESAMPLE 명령어: SQL 표준을 따르는 샘플링 방식으로, 일정 비율 또는 개수를 지정하여 데이터를 선택합니다. 일반적으로 다른 데이터베이스에서도 사용하는 표준화된 방식입니다.

SELECT * FROM my_table TABLESAMPLE (10);  -- 10% 샘플링

• Sampling 방법:
  • Fraction-based: 데이터의 일정 비율을 샘플링합니다. 예를 들어 SAMPLE (0.1)은 데이터의 약 10%를 무작위로 선택합니다.
  • Fixed-size: 샘플링할 데이터의 개수를 고정하여 지정할 수 있습니다. 예를 들어 SAMPLE (10 ROWS)는 데이터의 일부에서 10개의 행을 선택합니다. 이는 데이터를 일정 개수로 제한하여 더 작은 샘플로 작업해야 할 때 유용합니다.

3) 지원되는 함수 유형

Snowflake는 다양한 유형의 함수를 제공하여 데이터 처리를 효율적으로 수행할 수 있도록 합니다. 각 함수 유형은 고유의 특성을 가지며, 데이터 변환, 분석, 외부 연동 등에 활용됩니다.

• 시스템 함수 (System Functions): Snowflake가 제공하는 기본적인 시스템 관련 함수로, 현재 시간과 같은 시스템 정보를 반환하는 CURRENT_TIMESTAMP와 같은 함수가 포함됩니다.
• 테이블 함수 (Table Functions): 테이블 형식으로 데이터를 반환하는 함수로, 결과를 쿼리의 일부분으로 사용할 수 있습니다. 예를 들어, SPLIT_TO_TABLE 함수는 텍스트 데이터를 분할하여 각 분할된 값을 테이블 형식으로 반환할 수 있습니다.

SELECT * FROM TABLE(SPLIT_TO_TABLE('A,B,C', ',')) AS T(column1);

 

• 외부 함수 (External Functions): Snowflake 외부에서 실행되는 함수로, AWS Lambda와 같은 외부 서비스와 연동하여 복잡한 계산이나 데이터를 호출할 수 있습니다. 외부 함수를 사용하면 Snowflake가 직접 수행하지 않는 복잡한 연산을 API를 통해 처리하고 결과를 Snowflake로 가져올 수 있습니다.
• 사용자 정의 함수 (User-Defined Function, UDF): 사용자가 특정 데이터 처리 로직을 SQL 또는 JavaScript로 작성하여 재사용할 수 있는 함수입니다. 복잡한 계산을 캡슐화하여 코드 중복을 줄이고 가독성을 높입니다.

 

CREATE OR REPLACE FUNCTION my_udf(x INT)
RETURNS INT
LANGUAGE SQL
AS
$$
  x * 2
$$;

4) 저장 프로시저(Stored Procedure)

 

• 설명: 저장 프로시저는 데이터베이스 내에서 복잡한 데이터 처리 로직을 절차적으로 작성하고, 여러 번 재사용할 수 있는 프로시저입니다. 조건문과 반복문을 사용해 다단계 데이터 변환 작업을 자동화하고, 필요할 때마다 호출할 수 있습니다.
• 예제:
  • 아래 예제에서는 my_procedure라는 저장 프로시저를 생성하고 실행하는 방법을 보여줍니다. 이 프로시저는 간단히 "Hello, Snowflake!" 문자열을 반환합니다.

CREATE OR REPLACE PROCEDURE my_procedure()
RETURNS STRING
LANGUAGE SQL
AS 
$$
BEGIN
  RETURN 'Hello, Snowflake!';
END;
$$;
CALL my_procedure();

 

 

5) 스트림

 

• 설명: 스트림은 테이블에서 발생한 데이터 변경 사항(추가, 삭제, 수정)을 추적하여 CDC(변경 데이터 캡처) 작업을 수행할 수 있도록 합니다. 이를 통해 데이터 변경 사항을 기반으로 증분 데이터를 추출하고 필요한 작업을 수행할 수 있습니다.

CREATE STREAM my_stream ON TABLE my_table;

 

 

스트림을 설정하면, my_table에서 데이터가 변경될 때마다 해당 변경 사항이 my_stream을 통해 기록됩니다. 이를 활용해 특정 시점 이후의 데이터 변경 사항을 추적할 수 있습니다.

6) 태스크

설명: 태스크는 주기적으로 SQL 명령어를 자동 실행하도록 설정할 수 있는 기능으로, 정해진 일정에 따라 데이터 변환, 분석, 업데이트 작업을 자동화합니다. 지정한 시간 간격이나 특정 이벤트에 따라 트리거될 수 있습니다.

사용 예제:

• 아래 예제에서는 1시간마다 my_table의 데이터를 my_table_copy로 복사하는 작업을 수행하도록 태스크를 설정합니다.

CREATE TASK my_task
WAREHOUSE = my_warehouse
SCHEDULE = '1 hour'
AS
INSERT INTO my_table_copy SELECT * FROM my_table;

 

태스크가 생성되면 Snowflake가 이를 예약하여 자동으로 실행하며, 이를 통해 일정한 데이터 업데이트나 변환 작업을 지속적으로 수행할 수 있습니다.

5.2 반정형 데이터로 작업하는 방법에 대해 설명하기

Snowflake는 JSON, Avro, Parquet와 같은 반정형 데이터를 효율적으로 처리할 수 있는 다양한 기능을 제공하며, 특히 VARIANT 데이터 유형을 통해 이러한 데이터를 저장하고 쿼리할 수 있습니다. 반정형 데이터는 구조가 유연하고 중첩된 구조를 가지며, Snowflake의 다양한 함수와 명령어로 조작할 수 있습니다.

1) 지원되는 데이터 형식, 데이터 유형 및 크기

Snowflake는 여러 유형의 반정형 데이터를 지원하여 다양한 형태의 비정형 데이터를 저장하고 처리할 수 있습니다.

• 지원되는 데이터 형식:
  • JSON: 웹 애플리케이션과 시스템 간 데이터 전송에 일반적으로 사용됩니다.
  • Avro: 주로 Hadoop 및 Kafka와 같은 빅데이터 플랫폼에서 사용되며, 스키마를 포함하는 바이너리 데이터 형식입니다.
  • Parquet: 열 형식의 저장소로, 대규모 분석에 최적화된 형식입니다.
  • ORC: Parquet과 유사하게 열 형식의 데이터 저장 방식으로, Hadoop 에코시스템에서 자주 사용됩니다.
• 데이터 유형:
  • VARIANT: 반정형 데이터를 저장하는 주요 유형으로, JSON, Avro, Parquet 데이터를 저장할 수 있습니다.
  • OBJECT: 키-값 쌍을 저장하며, JSON 객체와 유사합니다.
  • ARRAY: 배열 형식의 데이터를 저장하며, JSON 배열과 유사합니다.
• 데이터 크기:
  • Snowflake의 VARIANT 컬럼에는 최대 16MB 크기의 데이터를 저장할 수 있습니다. 데이터가 이 크기를 초과하면 오류가 발생하므로 대용량 반정형 데이터를 저장할 때는 데이터 크기를 고려해야 합니다.

2) VARIANT 컬럼

설명: VARIANT는 Snowflake에서 반정형 데이터를 저장하기 위한 컬럼 유형으로, JSON 및 기타 반정형 데이터 형식의 데이터 저장에 최적화되어 있습니다. Snowflake는 반정형 데이터를 원래 구조 그대로 저장할 수 있고, 쿼리를 통해 데이터를 평면화하여 읽어올 수 있습니다.

CREATE TABLE my_table (data VARIANT);

 

위 예제에서는 data 컬럼을 VARIANT 타입으로 정의하여 JSON 또는 반정형 데이터를 저장할 수 있습니다.

3) 중첩 구조 평면화

반정형 데이터는 중첩된 구조를 가지는 경우가 많으며, 이를 분석하려면 데이터를 평면화해야 할 때가 많습니다. Snowflake에서는 FLATTEN 명령어를 사용하여 중첩된 데이터를 평면화할 수 있습니다.

• FLATTEN 명령어:
  • 중첩된 JSON 객체나 배열을 평면화하여 행 단위로 변환하는 명령어입니다.
  • FLATTEN을 통해 데이터의 특정 배열이나 중첩된 객체의 값을 추출하여 테이블 형식으로 변환할 수 있습니다.
  • 아래 쿼리는 my_table의 data 컬럼에 저장된 JSON 데이터의 중첩 배열을 평면화하여 테이블 형태로 변환합니다.

SELECT * 
FROM my_table, LATERAL FLATTEN(input => my_table.data);

• LATERAL FLATTEN 명령어:
  • LATERAL 조인과 함께 FLATTEN을 사용하여 중첩된 데이터에서 배열과 객체를 평면화할 수 있습니다.
  • LATERAL FLATTEN은 쿼리의 나머지 부분과 동일한 레벨에서 평면화된 데이터를 반환하여, 여러 중첩된 배열이나 객체의 값을 조인하고 분석하는 데 유용합니다.

4) 반정형 데이터 함수

반정형 데이터 함수는 JSON 객체나 배열과 같은 데이터 구조를 다루고, 값을 추출하며 데이터 유형을 검사하는 데 유용합니다. Snowflake는 여러 내장 함수를 제공하여 이러한 작업을 간편하게 수행할 수 있습니다.

ARRAY 및 OBJECT 생성 및 조작:

• Snowflake에서는 ARRAY와 OBJECT 함수를 사용해 반정형 데이터 내에서 배열과 객체를 생성하거나 조작할 수 있습니다.

SELECT ARRAY_AGG(column) FROM my_table;

 

 

위 예제에서는 column의 모든 값을 배열 형태로 변환하여 반환합니다.

• 값 추출:
  • : 연산자를 사용하여 JSON 객체 내 특정 키의 값을 추출할 수 있습니다. 이 기능을 통해 JSON 데이터를 SQL 쿼리에서 편리하게 조작할 수 있습니다.

 

SELECT data:key FROM my_table;

 

위 예제는 data 컬럼에서 특정 키의 값을 추출하여 반환합니다.

 

형식 조건자:

• Snowflake는 IS_ARRAY, IS_OBJECT 등 함수로 데이터의 형식을 검사하여 조건으로 활용할 수 있습니다. 이를 통해 데이터 유형에 따른 처리가 가능해지며, 반정형 데이터를 분석하고 변환하는 데 매우 유용합니다.

SELECT 
  CASE 
    WHEN IS_ARRAY(data) THEN 'Array'
    WHEN IS_OBJECT(data) THEN 'Object'
    ELSE 'Other'
  END AS data_type
FROM my_table;

 

위 예제는 data 컬럼이 배열인지 객체인지 확인하여 해당 형식을 반환합니다.

5.3 비정형 데이터로 작업하는 방법에 대해 설명하기

비정형 데이터는 Snowflake에서 디렉터리 테이블과 파일 함수를 통해 관리할 수 있으며, 일반적으로 이미지, 오디오, 비디오와 같은 파일 형식을 포함합니다.

1) 디렉터리 테이블의 정의 및 사용

 

• 설명: 디렉터리 테이블은 비정형 데이터를 Snowflake 내에서 관리하기 위한 테이블 유형으로, 외부 파일과 연계하여 파일 메타데이터를 관리합니다.
• 사용 상황: 이미지 파일, 문서 파일 등을 관리할 때 비정형 데이터를 처리하기 위해 디렉터리 테이블을 활용할 수 있습니다.

 

2) SQL 파일 함수

SQL 파일 함수는 Snowflake 내에서 파일을 조작하거나 불러오는 기능을 제공합니다.

• 파일 액세스를 가능하게 하는 URL의 유형: Snowflake는 파일 액세스를 위해 Amazon S3, Google Cloud Storage, Microsoft Azure Blob Storage와 같은 외부 스토리지와 연동할 수 있습니다.

GET @my_stage/file.png file://local_directory/;

3) 데이터 분석용 User-Defined Function (UDF)의 목적에 대해 간략히 설명하기

비정형 데이터 분석을 위해 사용자 정의 함수(UDF)를 작성할 수 있으며, 이를 통해 특정 비정형 데이터의 특징을 추출하거나 전처리를 수행할 수 있습니다.

• 설명: 데이터 분석용 UDF는 비정형 데이터의 특정 속성이나 패턴을 분석하는 데 사용됩니다.

CREATE FUNCTION my_udf(file VARIANT)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS $$
  return "Processed Data";
$$;

Reference

https://docs.snowflake.com/ko/guides

가이드 - Snowflake 설명서