Amazon Redshift Serverless에서 SQL 쿼리 스케줄링하기

Amazon Redshift는 데이터를 쉽고 빠르게 비용 효율적으로 분석할 수 있게 해주는 페타바이트 규모의 완전 관리형 데이터 웨어하우스 서비스입니다. 데이터 웨어하우스의 특성상 통합 분석을 위해 다양한 데이터를 수집해야 하고 수집 주기나 방식 등도 달라질 수 있습니다. 최근에 AWS 에서는 이와 같은 작업을 손쉽게 수행할 수 있도록 ETL 툴을 사용하는 기존의 전통적인 수집 방안 이외에도 실시간으로 데이터를 통합하기 위해 Zero-ETL 기능을 추가로 지원하고 있습니다.

이와 같이 다양한 워크로드를 데이터 웨어하우스 내에서 운영하다 보면 일정한 스케줄에 따라 SQL 워크로드를 수행하거나 업무 시간내에 수행하지 못했던 관리 작업 등을 특정 시간대에 진행해야 하는 경우도 발생합니다. 이를 위해서 Amazon Redshift는 스케줄러에 대한 전문적인 지식이 없이도 손쉽고 빠르게 콘솔에서 스케줄링을 구현할 수 있는 기능을 지원하고 있습니다.

데이터 웨어하우스의 스케줄링 기능은 다음과 같은 상황 등에서 유용하게 사용될 수 있습니다:

매일 저녁 특정 시간에 데이터 적재 작업이 필요한 경우
업무 시간 이외에 데이터베이스 관리 작업이 필요한 경우
데이터 보존 정책에 따라 오래된 데이터의 삭제 및 아카이브가 필요한 경우
구체화된 뷰(Materialized View)에 대해 수동으로 갱신이 필요한 경우

이미 Amazon Redshift 클러스터(프로비저닝 클러스터)를 사용하는 고객의 경우에는 Amazon Redshift 콘솔 내에 포함되어 있는 쿼리 에디터를 사용하여 스케줄링 기능들을 사용하고 있습니다. 관련된 기능의 설정 방식은 Scheduling SQL queries on your Amazon Redshift data warehouse를 참조하시면 됩니다. 하지만 이전 버전의 쿼리 에디터는 프로비저닝 클러스터만 지원하였습니다.

AWS는 사용자 편의성을 위해 지속적으로 새로운 기능들을 발표하고 있습니다. 여러 Amazon Redshift 클러스터를 하나의 화면에서 접속할 수 있도록 사용자 편의성을 대폭 개선한 쿼리 에디터 v2를 2021년 9월에 발표하였고, 2022년 7월에는 인프라에 대한 관리가 전혀 필요 없는 Amazon Redshift Serverless 에 대한 지원을 발표하였습니다. 그리고 2023년 6월에 Amazon Redshift Serverless에 대한 쿼리 스케줄링 기능을 지원하게 되었습니다.

최근에는 많은 고객들이 데이터 공유(Data Sharing) 환경과 Amazon Redshift Serverless를 활용하여 워크로드를 분리하고 사용한 만큼 비용을 지불하는 최적화 방안에 대해서 고려하고 있습니다. 이 글에서는 Amazon Redshift Serverless 환경에서 스케줄링을 구성하는 방법에 대해 알아보도록 하겠습니다.

Amazon Redshift에서 지원하는 쿼리 스케줄링 기능을 사용하면, 실제 내부적으로는 Amazon EventBridge 서비스와 연계하여 룰을 생성합니다. 이 룰은 작업을 수행할 일정과 호출할 타겟 클러스터의 정보(Amazon Redshift 호스트 이름, 로그인 정보, SQL 구문 등)를 포함합니다. 그리고 지정된 스케줄에 의해 EventBridge가 룰을 실행하면, 타겟 클러스터에 Amazon Redshift Data API가 호출되어 SQL 구문을 수행합니다.

이 글에서는 2가지 영역으로 나누어서 설명을 진행합니다.

사전 준비 단계 : 사용자 및 역할 생성, 그리고 필요한 권한을 부여하는 작업을 수행합니다.
스케줄링 단계 : 쿼리 스케줄링을 수행하고 모니터링 방법을 알아봅니다.

사전 준비 사항

그림에서 보는 것처럼 Amazon Redshift는 데이터 공유 환경(생산자와 소비자)으로 구성되어 있고, Amazon Redshift Serverless가 소비자 역할을 수행합니다. 이 글에서는 소비자 역할을 하는 Amazon Redshift Serverless을 중심으로 스케줄링 작업을 진행합니다. Amazon Redshift 의 구성 방식에 대해서는 Amazon Redshift 시작 안내서와 How to use Amazon Redshift data sharing을 참조합니다.

스케줄링을 구현하는 방법과 인증 방식에 따라 필요한 역할들이 달라지지만, 여기서는 2가지(스케줄링 작성자, Data API 수행 역할) 역할을 구분하여 구성하였습니다.

스케줄링 작성자(rsadmin) 생성 및 권한 부여

rsadmin은 IAM 사용자로 쿼리 에디터 v2를 통해 Amazon Redshift Serverless 에 대한 스케줄링을 작성하는 작업을 수행합니다. 관련 작업을 수행하기 위해서는 AmazonRedshiftQueryEditorV2FullAccess와 AmazonEventBridgeFullAccess 권한이 필요하고, 추가적으로 Amazon Redshift Serverless 리스트를 가져오기 위한 권한이 필요합니다. 이를 위해 AWS managed로 등록된 AmazonRedshiftFullAccess 권한을 사용할 수도 있으나, 읽기 기능이면 충분하기 때문에 AmazonRedshiftReadOnlyAccess 액세스 권한을 기준으로 하여 redshift-serverless에 대한 권한을 추가하여 다음의 권한을 사용할 수 있습니다.

{
"Sid": "AmazonRedshiftReadOnlyAccess",
"Action": [
"redshift:Describe*",
"redshift:List*",
"redshift-serverless:Describe*",
"redshift-serverless:List*",
"redshift-serverless:Get*",
"ec2:Describe*",
"cloudwatch:Describe*",
"cloudwatch:List*",
"cloudwatch:Get*",
"sns:Get*",
"sns:List*"
],
"Effect": "Allow",
"Resource": "*"
}

Data API 호출 역할(InvokeDataAPI) 생성 및 권한 부여

스케줄 된 작업에서 Amazon Redshift Serverless에 대하여 Data API를 호출하기 위해서는 관련 역할이 필요합니다. 여기서는 InvokeDataAPI라는 역할을 생성하여 AmazonRedshiftDataFullAccess 권한을 부여하였습니다. 추가로 EventBridge 서비스가 이 역할을 수행할 수 있도록 InvokeDataAPI 역할의 Trust relationships 에 다음과 같이 AssumeRole을 추가합니다.

{
"Effect": "Allow",
"Principal": {
"Service": "events.amazonaws.com"
},
"Action": "sts:AssumeRole"
}

Data API를 통해서 수행된 쿼리의 결과는 Data API 를 통해서만 확인이 가능합니다. 따라서 스케줄러 작성자 (rsadmin)가 수행된 쿼리의 이력을 볼 수 있도록 InvokeDataAPI에 대한 수행 권한이 필요합니다. 이를 위해 InvokeDataAPI 역할의 Trust relationships에 다음과 같이 AssumeRole를 추가합니다.

{
"Sid": "AssumeRole",
"Effect": "Allow",
"Principal": {
"AWS": "arn:aws:iam::${Account_ID}:user/rsadmin"
},
"Action": "sts:AssumeRole"
}

Data API 호출 역할(InvokeDataAPI) 을 Amazon Redshift Serverless에 연계

스케줄링을 작성하는 단계에서 Data API를 호출하는 역할을 지정하려면 Amazon Redshift Serverless에 해당 역할이 연계되어 있어야 합니다. 이를 위해서 먼저 InvokeDataAPI의 Trust relationships에 다음 항목이 등록되어 있는지를 확인 합니다.

{
"Effect": "Allow",
"Principal": {
"Service": [
"redshift.amazonaws.com",
"redshift-serverless.amazonaws.com"
]
},
"Action": "sts:AssumeRole"
}

다음으로 InvokeDataAPI 역할을 Amazon Redshift Serverless 에 연계하기 위해서 Redshift Serverless 대시보드에서 사용하려는 Namespace를 선택합니다. Security and encryption 탭에서 Manage IAM roles -> Associate IAM roles 을 선택하여 InvokeDataAPI를 추가합니다.

스케줄링 작성자(rsadmin) 에 InvokeDataAPI 역할 수행 권한 부여

스케줄링 작성자(rsadmin)가 Data API를 통해서 수행된 쿼리의 결과를 확인하기 위해서 InvokeDataAPI를 수행할 수 있는 권한을 부여하기 위해 다음과 같이 인라인 정책을 추가합니다.

{
"Sid": "AssumeIAMRole",
"Effect": "Allow",
"Action": "sts:AssumeRole",
"Resource": "arn:aws:iam::${Account_ID}:role/InvokeDataAPI"
}

쿼리 에디터 v2를 사용하여 쿼리 스케줄링

데이터 공유(Data Sharing)는 클러스터 간의 물리적인 데이터 복제 없이도 서로 다른 클러스터에서 데이터를 액세스할 수 있기 때문에 워크로드 분리가 필요한 다양한 환경에서 많이 사용중인 기능입니다. 효율적인 데이터 공유 환경을 구성하기 위해서는 Amazon Redshift data sharing best practices and considerations를 참조할 수 있습니다. 특히 생산자 쪽의 데이터에 잦은 변경이 발생하면 메타 데이터와 데이터 캐쉬 측면에서 소비자 쪽에서 동기화 과정이 필요합니다. 이를 보완하기 위해서 소비자 쪽에서 워크로드가 수행되기 이전에 주기적으로 관련 오브젝트에 조회 작업 등을 수행하여 동기화를 수행할 수 있습니다.

이 글에서는 소비자 쪽에 구체화된 뷰(Materialized View)를 생성하여 쿼리 성능을 개선하는 방법에 대해 알아보겠습니다. 테스트를 위해 클러스터에 TPC-H데이터를 적재하고, 데이터 공유 구성을 통해 소비자 쪽의 share_db.public.lineitem 테이블이 생산자 쪽의 lineitem 데이터를 바라보도록 구성하였습니다. 그리고 쿼리 성능을 개선하기 위해 다음의 구체화된 뷰( Materialized View)를 생성합니다.

CREATE MATERIALIZED VIEW lineitem_agg
AUTO REFRESH YES AS
SELECT l_suppkey, l_shipmode, datepart(year, L_SHIPDATE) l_shipyear,
  SUM(L_QUANTITY)	TOTAL_QTY,
  SUM(L_DISCOUNT) TOTAL_DISCOUNT,
  SUM(L_TAX) TOTAL_TAX,
  SUM(L_EXTENDEDPRICE) TOTAL_EXTENDEDPRICE  
FROM share_db.public.lineitem
GROUP BY 1,2,3;

다음으로 스케줄 된 작업의 로깅을 위해 구체화된 뷰를 수동으로 갱신하는 refresh materialized view lineitem_agg 구문을 포함하는 프로시저를 생성합니다.

CREATE TABLE accesslog (sqlstmt varchar, result varchar, end_time timestamp);

CREATE OR REPLACE PROCEDURE refresh_mv() AS
$$
DECLARE
query VARCHAR;
BEGIN
    query := 'refresh materialized view lineitem_agg';
    EXECUTE query;
    INSERT into accesslog values (query, 'success', sysdate);
EXCEPTION WHEN OTHERS THEN
    INSERT into accesslog values (query, 'fail', sysdate);
END;
$$ 
LANGUAGE plpgsql
SECURITY DEFINER;

마지막으로 InvokeDataAPI 역할이 프로시저를 호출할 수 있도록 다음과 같이 권한을 부여합니다.

GRANT EXECUTE ON PROCEDURE refresh_mv() TO "IAMR:InvokeDataAPI";

쿼리 스케줄링 작업을 통해 앞에서 작성한 refresh_mv() 프로시저를 주기적으로 호출하는 작업을 생성하고, 이를 모니터링 하는 방안에 대해서 알아보겠습니다.

단계 1: 쿼리 에디터 v2에서 쿼리 스케줄링

rsadmin 사용자로 쿼리 에디터 v2에 접속합니다.
쿼리 수행창을 열어서, 작업을 진행할 Redshift Serverless와 데이터베이스를 선택합니다.
SQL 작성하는 부분에 프로시저를 호출하기 위해 CALL refresh_mv();를 입력한 후에 Schedule 버튼을 선택합니다.
Scheduler permission 섹션에서, IAM role로 InvokeDataAPI 역할을 선택합니다.
Authentication 부분에서, Temporary credentials 를 선택합니다.
Cluster or workgroup 부분에서, 대상 Redshift Serverless를 선택합니다.
Database name 부분에서 사용할 DB 이름을 입력합니다.
Query information 섹션에서 Scheduled query name를 입력합니다. (예, refresh-mv)
쿼리 에디터 v2에서 작성된 스케줄은 prefix에 자동으로 QS2- 가 붙게 됩니다.
Scheduling options 섹션에서 기본 값인 Run frequency를 선택합니다.
Schedule repeats every 부분에서 작업을 수행할 시간 주기를 설정합니다. (예, 1 hour)
on 부분에 작업을 수행할 요일을 선택합니다.

스케줄링이 성공적으로 만들어지면 생성된 작업이 자동으로 활성화되어 있는 것을 확인할 수 있습니다.

단계2: 쿼리 에디터 v2 에서 스케줄 된 작업의 수행 이력 확인

스케줄 된 작업은 EventBridge에 룰로 등록이 되면 지정한 일정에 맞추어서 수행이 됩니다.

rsadmin 사용자로 쿼리 에디터 v2에 접속합니다.
쿼리 수행창에서accesslog 테이블을 조회하여 프로시저의 수행 실패 혹은 성공을 확인합니다.
쿼리 에디터 v2 의 왼쪽 메뉴바에서 Scheduled queries을 선택합니다.
Scheduled queries 창에서 현재 스케줄 된 쿼리와 활성화 상태를 확인합니다. 필요시에는 스케줄 된 쿼리의 내용을 수정하거나 비활성화 등의 작업을 수행할 수 있습니다.
쿼리를 선택하여 상세 정보 화면으로 넘어갑니다. Schedule history 섹션에서 쿼리의 수행 이력을 확인할 수 있습니다.

작성한 구체화된 뷰(Materialized View)로 인한 성능 개선을 확인하기 위해 원본 쿼리(133 초)를 수행하였을 때와 뷰를 사용하였을 때의 쿼리(42초)를 비교한 결과 약 3배 이상의 성능 개선이 있었습니다. 또한 최근 2024년 2월에 데이터 공유 환경에서 consumer table의 구체화된 뷰(Materialized View)에 대하여 incremental refresh 기능을 지원하기 시작하였습니다. 초기 full refresh는 전체 갱신을 수행하기 때문에 시간이 소요되지만 이후에 데이터의 변경분에 대하여 incremental refresh를 지원하여 뷰를 갱신하는데 소요되는 시간도 단축할 수 있습니다.

단계 3: 수행된 작업의 결과 값 확인

쿼리 에디터 v2에서는 수행된 작업의 성공/실패 여부만 확인할 수 있습니다. 예를 들어 작업이 실패한 경우에는 상세 결과를 확인하여 원인을 파악해야 할 때가 있습니다. 테스트를 위해 스케줄 된 작업에 있는 SQL 구문을 “CALL refresh_mv_test()”로 변경하여 일부러 실패를 발생시켰습니다.

Data API 를 호출하기 위한 환경으로 AWS Command Line Interface (AWS CLI)를 사용하기 위해서 AWS CloudShell 을 사용하였습니다. (rsadmin 사용자에게 사용 권한이 있어야 합니다.)

쿼리 에디터 v2창에서 왼쪽 아래쪽에 AWS CloudShell를 실행합니다.

InvokeDataAPI 역할에 대한 임시 접속 정보를 요청합니다.

aws sts assume-role --role-arn arn:aws:iam::${Account_ID}:role/InvokeDataAPI --role-session-name scheduler-test

호출의 결과값을 기반으로 해서 다음의 환경변수를 설정합니다.

export AWS_ACCESS_KEY_ID=xxxxxxxxxxx
export AWS_SECRET_ACCESS_KEY=xxxxxxxxxx
export AWS_SESSION_TOKEN=xxxxxxxxxx

앞에서 실패했던 작업의 ID를 가지고 describe-statement를 수행하는 Data API 를 호출합니다.

aws redshift-data describe-statement --id 1c193ab8-a9f9-49ad-9e04-dcca9ec94b9c
{   
    "CreatedAt": "2024-02-21T04:10:07.595000+00:00",
    "Duration": -1,
    "Error": "ERROR: procedure refresh_mv_test() does not exist\n  Hint: No procedure matches the given name and argument types. You may need to add explicit type casts.",
    "HasResultSet": false,
    "Id": "xxxxxxxx-8300-4fa0-893f-636c315c8129",
    "QueryString": "CALL refresh_mv_test();",
    …
    "SecretArn": "arn:aws:secretsmanager:us-east-1:<Account_ID>:secret:xxxxxxxxxx",
    "Status": "FAILED",
    "UpdatedAt": "2024-02-21T04:10:08.114000+00:00",
    "WorkgroupName": "xxxxxxxxxx"
}

에러 메시지에서 프로시저의 이름이 잘못 호출된 것을 확인할 수 있습니다. 이와 같이 에러가 발생한 부분에 대하여 조금 더 자세한 정보를 확보하여 문제를 해결하는데 활용할 수 있습니다.

마무리

지금까지 Amazon Redshift Serverless 환경에서 쿼리 스케줄링을 작성하는 방법에 대해서 알아보았습니다. 최근의 데이터 웨어하우스는 하나의 단일한 클러스터에서 모든 워크로드를 처리하기 보다는 워크로드의 특성과 비용 효율성을 고려하여 멀티 클러스터 환경을 지향하고 있습니다. 이와 같은 유기적인 데이터 공유 환경에서는 적합한 시점에 필요한 작업을 수행하도록 하는 스케줄링의 역할이 더 중요합니다.

이 글을 작성하는 시점에도 AWS 에서는 데이터 공유 환경에서 메타 데이터 동기화 관련하여 많은 개선 작업을 진행하고 적용하였습니다. 더불어 스케줄링 기능을 활용하여 주요 워크로드에 대하여 구체화된 뷰(Materialized View)를 사용하여 성능을 개선할 수 있습니다.

AWS 기술 블로그