[데이터 웨어하우스 - 중급] 2. Redshift - ⑥ Redshift Spectrum으로 S3 외부 테이블 조작해보기
1. Redshift Spectrum 사용 유스 케이스
Redshift Spectrum는 다음과 같은 유스 케이스에서 유용하게 활용될 수 있습니다
- 대용량 Fact 테이블이 S3에 파일 형태로 존재: 대용량의 데이터가 S3에 저장되어 있고, 이를 Redshift에 복사하지 않고 직접 활용하고자 할 때 Redshift Spectrum을 사용할 수 있습니다. Redshift Spectrum은 S3 파일들을 외부 테이블로 처리하여 SQL 쿼리를 수행할 수 있게 해줍니다.
- Redshift에 소규모 Dimension 테이블이 존재: Redshift에 저장된 작은 규모의 Dimension 테이블과 S3에 있는 Fact 테이블을 조인하고자 할 때 Redshift Spectrum을 활용할 수 있습니다. 이를 통해 Redshift 테이블과 S3 외부 테이블을 조인하여 분석 및 쿼리 작업을 수행할 수 있습니다.
- 비용 부담 최소화: Redshift Spectrum을 사용하면 S3에 저장된 데이터를 쿼리하는 비용만 지불하면 됩니다. Redshift 클러스터를 사용하여 별도의 데이터 복사나 이동 없이 S3 데이터에 접근할 수 있으므로 비용 부담을 최소화할 수 있습니다. 또한, Redshift Spectrum은 사용한 데이터 양에 따라 비용이 책정되기 때문에 필요한 만큼의 자원을 사용할 수 있습니다.
Redshift Spectrum을 활용하면 Redshift의 확장 기능으로써 S3에 저장된 데이터를 마치 테이블처럼 쿼리할 수 있습니다. 이를 통해 데이터 레이크 아키텍처를 구축하고 다양한 데이터를 효율적으로 분석하고 조인할 수 있습니다.
2. 외부 테이블(External Table)이란?
외부 테이블(External Table)은 데이터베이스 엔진이 외부에 저장된 데이터를 내부 테이블처럼 사용하는 방법입니다. 외부 테이블은 보통 클라우드 스토리지인 S3와 같은 외부 위치에 대량의 데이터가 저장되어 있을 때 임시로 활용하는 목적으로 사용됩니다.
SQL 명령어를 사용하여 데이터베이스에 외부 테이블을 생성할 수 있습니다. 이때 데이터는 복사되는 것이 아니라 참조만하게 됩니다. 외부 테이블은 CSV, JSON, XML 등의 파일 형식뿐만 아니라 ODBC 또는 JDBC 드라이버를 통해 액세스하는 원격 데이터베이스와 같은 다양한 데이터 소스에 대해서도 사용할 수 있습니다.
외부 테이블을 사용하면 데이터를 처리한 후 결과를 데이터베이스에 적재할 수 있습니다. 예를 들어, 외부 테이블을 사용하여 로그 파일을 읽고 정제된 내용을 데이터베이스의 내부 테이블에 적재할 수 있습니다.
외부 테이블의 사용은 보안 및 성능과 관련된 고려사항이 있습니다. 외부 테이블은 데이터의 위치와 액세스 권한을 고려해야 하며, 데이터의 크기와 분산도 성능에 영향을 미칠 수 있습니다.
외부 테이블은 Hive와 같은 시스템에서 처음 도입된 개념으로, 지금은 대부분의 빅 데이터 시스템에서 지원하고 활용되고 있습니다. 이를 통해 다양한 데이터 소스를 유연하게 활용하고 데이터 처리 작업을 효율적으로 수행할 수 있습니다.
3. Redshift Spectrum 사용 방식
Redshift Spectrum는 S3에 저장된 파일들을 SQL로 처리할 수 있는 기능입니다. 이를 위해 S3 파일들을 외부 테이블(external table)로 정의하고 Redshift 테이블과 조인할 수 있습니다. 일반적으로 S3 외부 테이블은 Fact 테이블로 사용되며, Redshift 테이블은 Dimension 테이블로 사용됩니다.
Redshift Spectrum를 사용하기 위해서는 Redshift 클러스터가 필요합니다. 또한, S3와 Redshift 클러스터는 동일한 AWS 리전에 위치해야 합니다.
S3에 저장된 Fact 데이터를 Redshift Spectrum에서 활용하기 위해서는 해당 데이터를 외부 테이블로 정의해야 합니다. 외부 테이블은 SQL 명령어를 사용하여 정의할 수 있으며, 데이터의 형식에 따라 적절한 스키마와 형식 정보를 지정해야 합니다. 이렇게 외부 테이블로 정의된 S3 파일들은 Redshift Spectrum을 통해 SQL 쿼리를 실행할 때 참조됩니다.
이렇게 Redshift Spectrum를 사용하면 S3에 저장된 대량의 데이터를 Redshift 클러스터와 연동하여 유연하게 처리할 수 있습니다. SQL 쿼리를 사용하여 S3 파일들을 직접 조인하거나 필터링할 수 있으며, Redshift의 강력한 분석 기능과 함께 활용할 수 있습니다. 이를 통해 더 큰 규모의 데이터 처리와 분석 작업을 수행할 수 있습니다.
4. Redshift Spectrum 실습을 위한 외부 테이블 용 스키마 설정
- 먼저, "redshift.read.s3"라는 IAM 역할에 "AWSGlueConsoleFullAccess" 권한을 할당해야 합니다. 이는 AWS Management Console 또는 AWS CLI 또는 SDK를 사용하여 수행할 수 있습니다.
- 다음으로, 다음과 같은 SQL 문을 실행하여 외부 스키마를 생성합니다.
CREATE EXTERNAL SCHEMA external_schema
FROM DATA CATALOG
DATABASE 'myspectrum_db'
IAM_ROLE 'arn:aws:iam::521227329883:role/redshift.read.s3'
CREATE EXTERNAL DATABASE IF NOT EXISTS;위의 SQL 문에서 'myspectrum_db'를 자신의 Redshift 데이터베이스 이름으로, 'arn:aws:iam::123456700:role/redshift.read.s3'를 자신의 IAM 역할 ARN으로 대체해야 합니다.
이 SQL 문은 지정된 Redshift 데이터베이스에 "external_schema"라는 외부 스키마를 생성합니다. 해당 스키마는 S3에 저장된 데이터를 참조하는 외부 테이블과 관련됩니다. 'IAM_ROLE' 절은 S3 액세스 권한을 갖는 IAM 역할과 스키마를 연결합니다. CREATE EXTERNAL DATABASE IF NOT EXISTS 절은 외부 데이터베이스가 이미 존재하지 않을 경우 생성되도록 합니다.
위의 SQL 문을 실행함으로써 Redshift Spectrum을 위한 외부 스키마를 설정할 수 있습니다. 이 외부 스키마는 S3에 저장된 데이터를 참조하는 외부 테이블을 포함하는 컨테이너 역할을 수행합니다.
※ AWS Glue란?
AWS Glue는 AWS의 Serverless ETL(Extract, Transform, Load) 서비스입니다. 다음과 같은 주요 기능을 제공합니다
1. 데이터 카탈로그
- AWS Glue Data Catalog는 데이터 소스와 대상의 메타데이터를 저장하고 검색하는 기능을 제공합니다.
- 데이터 소스는 주로 S3 또는 다른 AWS 서비스에 저장된 데이터를 대상으로 합니다. 예를 들어, Redshift Spectrum의 경우 AWS Glue Data Catalog를 사용하여 S3에 있는 외부 테이블의 메타데이터를 관리합니다.
2. ETL 작업 생성: AWS Glue Studio
- AWS Glue Studio는 간단한 드래그 앤 드롭 인터페이스를 제공하여 ETL 작업을 생성할 수 있습니다.
- 사용자는 데이터 소스와 대상을 선택하고 데이터 변환 단계를 정의하는 스크립트를 생성할 수 있습니다.
- AWS Glue Studio를 통해 ETL 작업을 시각적으로 설계하고 관리할 수 있습니다.
3. 작업 모니터링 및 로그
- AWS Glue 콘솔을 통해 사용자는 ETL 작업의 실행 상태와 로그를 모니터링할 수 있습니다.
- 실행 중인 작업의 진행 상황을 확인하고 오류를 해결하는 데 도움이 됩니다.
4. 서버리스 실행
- AWS Glue는 서버리스 아키텍처를 기반으로 하여 작업을 실행합니다.
- 사용자는 작업을 실행하기 위해 필요한 인프라를 관리할 필요가 없으며, 자동으로 스케일링되어 처리량에 맞게 자원을 할당합니다.
AWS Glue를 사용하면 데이터 소스 간의 ETL 작업을 효율적으로 수행할 수 있습니다. 데이터 카탈로그를 활용하여 데이터 소스의 메타데이터를 관리하고, 시각적인 인터페이스를 통해 ETL 작업을 생성하고 실행할 수 있습니다. 또한, 서버리스 아키텍처를 사용하여 유연하게 작업을 실행하고 모니터링할 수 있습니다.
5. Redshift Spectrum 실습을 위한 외부 Fact 테이블 정의
- S3 버킷 아래에 'usc'라는 이름의 폴더를 생성합니다.
- 'usc' 폴더로 'user_session_channel.csv' 파일을 복사합니다.
- 다음 SQL 문을 실행하여 외부 테이블을 생성합니다.
CREATE EXTERNAL TABLE external_schema.user_session_channel (
userid integer,
sessionid varchar(32),
channel varchar(32)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://user-test-bucket/usc/';위의 SQL 문에서는 외부 테이블의 구조를 정의하고, 필드 구분자로 쉼표(,)를 사용하며, 텍스트 파일로 저장되어 있는 데이터를 읽어올 것을 명시합니다. 또한, 's3://user-test-bucket/usc/' 경로에 있는 데이터를 읽도록 설정합니다. 여기서 'external_schema'는 앞서 생성한 외부 테이블 용 스키마를 나타냅니다.
이제 외부 테이블이 정의되었으며, Redshift Spectrum을 통해 S3에 있는 'user_session_channel.csv' 파일의 데이터를 SQL 쿼리를 사용하여 조회할 수 있습니다.
6. Redshift Spectrum 실습을 위한 내부 Dimension 테이블
'raw_data' 스키마 아래에 'user_property' 테이블을 생성합니다.
CREATE TABLE raw_data.user_property AS
SELECT
userid,
CASE WHEN CAST(RANDOM() * 2 AS INT) = 0 THEN 'male' ELSE 'female' END gender,
(CAST(RANDOM() * 50 AS INT) + 18) age
FROM (
SELECT DISTINCT userid
FROM raw_data.user_session_channel
);위의 쿼리는 'user_session_channel' 테이블에서 고유한 사용자 ID(userid)를 가져와 'user_property' 테이블을 생성합니다. 생성되는 'user_property' 테이블은 'userid', 'gender', 'age' 열로 구성되며, 'gender' 열은 'male' 또는 'female'로 무작위로 설정되고, 'age' 열은 18부터 67까지의 난수로 생성됩니다.
이제 'user_property' 테이블은 Redshift Spectrum을 통해 외부 테이블과 조인 등의 작업에 활용될 수 있습니다.
7. Redshift Spectrum Fact + Dimension 테이블 조인
다음 SQL 문은 Redshift Spectrum에서 외부 Fact 테이블과 내부 Dimension 테이블을 조인하여 성별별로 세션의 수를 계산하는 쿼리입니다.
SELECT gender, COUNT(1)
FROM external_schema.user_session_channel usc
JOIN raw_data.user_property up ON usc.userid = up.userid
GROUP BY 1;위의 쿼리는 'user_session_channel' 테이블과 'user_property' 테이블을 조인하여 성별(gender)에 따른 세션 수를 계산합니다. 'external_schema.user_session_channel'은 Redshift Spectrum을 통해 S3의 외부 테이블로 정의된 테이블이며, 'raw_data.user_property'은 내부 Dimension 테이블입니다. 조인 조건은 두 테이블의 'userid' 열을 기준으로 하고, 결과는 성별(gender)로 그룹화하여 각 그룹의 세션 수를 계산합니다.
이 쿼리를 실행하면 외부 Fact 테이블과 내부 Dimension 테이블을 조인하여 원하는 결과를 얻을 수 있습니다.