기계 번역으로 제공되는 번역입니다. 제공된 번역과 원본 영어의 내용이 상충하는 경우에는 영어 버전이 우선합니다. # Aurora PostgreSQL 호환에서 Oracle UTL\$1FILE 기능 설정 *Rakesh Raghav, anuradha chintha, Amazon Web Services* ## 요약 Oracle에서 Amazon Web Services(AWS) 클라우드의 Amazon Aurora PostgreSQL 호환 버전으로 마이그레이션하는 과정에서 여러 가지 문제가 발생할 수 있습니다. 예를 들어, Oracle `UTL_FILE` 유틸리티를 사용하는 코드를 마이그레이션하는 것은 항상 어려운 일입니다. Oracle PL/SQL에서 `UTL_FILE` 패키지는 기본 운영 체제와 함께 읽기 및 쓰기와 같은 파일 작업에 사용됩니다. 이 `UTL_FILE` 유틸리티는 서버 및 클라이언트 기기 시스템 모두에서 작동합니다.  Amazon Aurora PostgreSQL 호환은 관리형 데이터베이스 오퍼링입니다. 이 때문에 데이터베이스 서버의 파일에 액세스할 수 없습니다. 이 패턴은 Amazon Simple Storage Service(S3)와 Amazon Aurora PostgreSQL 호환의 통합 과정을 안내하여 `UTL_FILE` 기능의 일부를 구현합니다. 이 통합을 사용하면 타사 추출, 전환, 적재(ETL) 도구 또는 서비스를 사용하지 않고도 파일을 생성하고 사용할 수 있습니다. 선택적으로 Amazon CloudWatch 모니터링 및 Amazon SNS 알림을 설정할 수 있습니다. 프로덕션 환경에 구현하기 전에 이 솔루션을 철저하게 테스트하는 것이 좋습니다. ## 사전 조건 및 제한 사항 **사전 조건 ** + 활성 상태의 AWS 계정 + AWS Database Migration Service(AWS DMS) 전문 지식 + PL/PgSQL 코딩에 대한 전문 지식 + Amazon Aurora PostgreSQL 호환 클러스터 + S3 버킷 **제한 사항 ** 이 패턴은 Oracle `UTL_FILE` 유틸리티를 대체하는 기능을 제공하지 않습니다. 하지만 데이터베이스 현대화 목표를 달성하기 위해 단계와 샘플 코드를 더욱 개선할 수 있습니다. **제품 버전** + Amazon Aurora PostgreSQL 호환 버전 11.9 ## 아키텍처 **대상 기술 스택** + Amazon Aurora PostgreSQL 호환 + Amazon CloudWatch() + Amazon Simple Notification Service(SNS) + Amazon S3 **대상 아키텍처 ** 다음은 솔루션의 고급 표현을 보이는 다이어그램입니다. ![\[데이터 파일은 S3 버킷에 업로드되고, aws_s3 확장자를 사용하여 처리되어, Aurora 인스턴스로 전송됩니다.\]](http://docs.aws.amazon.com/ko_kr/prescriptive-guidance/latest/patterns/images/pattern-img/3aeecd46-1f87-41f9-a9cd-f8181f92e83f/images/4a6c5f5c-58fb-4355-b243-d09a15c1cec6.png) 1. 파일은 애플리케이션에서 S3 버킷으로 업로드됩니다. 1. `aws_s3` 확장은 PL/pgSQL을 사용하여 데이터에 액세스하고 데이터를 Aurora PostgreSQL 호환에 업로드합니다. ## 도구 + [Amazon Aurora PostgreSQL 호환 버전](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html)-Amazon Aurora PostgreSQL 호환 버전은 완전 관리형으로, PostgreSQL 호환 및 ACID 준수 관계형 데이터베이스 엔진입니다. 이는 고급 상용 데이터베이스의 속도와 신뢰성을 오픈 소스 데이터베이스의 비용 효율성과 결합합니다. + [AWS CLI](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html)-AWS Command Line Interface(AWS CLI)는 AWS 서비스를 관리하는 통합 도구입니다. 도구 하나만 다운로드하여 구성하면 여러 AWS 서비스를 명령줄에서 관리하고 스크립트를 통해 자동화할 수 있습니다. + [Amazon CloudWatch](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/WhatIsCloudWatch.html)-Amazon CloudWatch는 Amazon S3 리소스 및 사용을 모니터링합니다. + [Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html) – Amazon Simple Storage Service(S3)는 인터넷에 대한 스토리지입니다. 이 패턴에서 Amazon S3는 Aurora PostgreSQL 호환 클러스터를 나가고 들어오는 소비 및 전송 파일을 수신하고 저장하는 스토리지 계층을 제공합니다. + [aws\$1s3](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html#aws_s3.table_import_from_s3)-`aws_s3` 확장은 Amazon S3와 Aurora PostgreSQL 호환을 통합합니다. + [Amazon SNS](https://docs.aws.amazon.com/sns/latest/dg/welcome.html)-Amazon Simple Notification Service(SNS)는 게시자와 클라이언트 간에 메시지를 전달 또는 전송하는 것을 조정하고 관리합니다. 이 패턴에서는 Amazon SNS를 사용하여 알림을 전송합니다. + [pgAdmin](https://www.pgadmin.org/docs/)-pgAdmin은 Postgres를 위한 오픈 소스 관리 도구입니다. pgAdmin 4는 데이터베이스 개체를 생성, 유지 관리 및 사용하기 위한 그래픽 인터페이스를 제공합니다. **코드** 필요한 기능을 구현하기 위해 패턴은 `UTL_FILE`과 비슷한 이름을 가진 여러 함수를 생성합니다. *추가 정보* 섹션에는 이러한 함수의 코드 베이스가 포함되어 있습니다. 코드에서 `testaurorabucket`을 테스트 S3 버킷의 이름으로 바꿉니다. `us-east-1`을 테스트 S3 버킷이 위치한 AWS 리전으로 바꿉니다. ## 에픽 ### Amazon S3와 Aurora PostgreSQL 호환 통합 | 작업 | 설명 | 필요한 기술 | | --- | --- | --- | | IAM 정책을 설정합니다. | S3 버킷에 대한 액세스 권한을 부여하는 AWS Identity and Access Management(IAM) 정책 및 정책 내의 객체를 생성합니다. 코드에 대한 내용은 *추가 정보* 섹션을 참조하세요. | AWS 관리자, DBA | | Aurora PostgreSQL에 Amazon S3 액세스 역할을 추가합니다. | 두 개의 IAM 역할, 즉 하나는 Amazon S3에 대한 읽기 액세스 역할이고 다른 하나는 쓰기 액세스 역할을 생성합니다. Aurora PostgreSQL 호환 클러스터에 다음의 두 가지 역할을 연결합니다. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ko_kr/prescriptive-guidance/latest/patterns/set-up-oracle-utl_file-functionality-on-aurora-postgresql-compatible.html)자세한 내용은 Amazon S3로 데이터를 [가져오고](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_PostgreSQL.S3Import.html) [내보내는](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/postgresql-s3-export.html) 방법에 대한 Aurora PostgreSQL 호환 설명서를 참조하세요. | AWS 관리자, DBA | ### Aurora PostgreSQL 호환에서 확장 설정 | 작업 | 설명 | 필요한 기술 | | --- | --- | --- | | aws\$1commons 확장을 생성합니다. | `aws_commons` 확장은 `aws_s3` 확장의 종속성입니다. | DBA, 개발자 | | aws\$1s3 확장을 생성합니다. | `aws_s3` 확장은 Amazon S3와 상호 작용합니다. | DBA, 개발자 | ### Amazon S3와 Aurora PostgreSQL 호환 통합 검증 | 작업 | 설명 | 필요한 기술 | | --- | --- | --- | | Amazon S3에서 Aurora PostgreSQL로 파일 가져오기를 테스트합니다. | 파일을 Aurora PostgreSQL 호환으로 가져오는 것을 테스트하려면 샘플 CSV 파일을 생성하여 S3 버킷에 업로드하세요. CSV 파일을 기반으로 테이블 정의를 생성하고 `aws_s3.table_import_from_s3` 함수를 사용하여 파일을 테이블에 로드합니다. | DBA, 개발자 | | Aurora PostgreSQL에서 Amazon S3로 파일 내보내기를 테스트합니다. | 파일을 Aurora PostgreSQL 호환으로 내보내는 것을 테스트하려면 테스트 테이블을 생성하고 데이터로 채운 후에 `aws_s3.query_export_to_s3` 함수를 이용하여 데이터를 내보냅니다. | DBA, 개발자 | ### UTL\$1FILE 유틸리티를 모방하려면 래퍼 함수를 생성하세요. | 작업 | 설명 | 필요한 기술 | | --- | --- | --- | | utl\$1file\$1utility 스키마를 생성합니다. | 스키마는 래퍼 함수를 함께 유지합니다. 스키마를 생성하려면 다음의 명령을 실행하세요.
CREATE SCHEMA utl_file_utility;
| DBA, 개발자 | | file\$1type 유형을 생성합니다. | `file_type` 유형을 생성하려면, 다음의 코드를 사용하세요.
CREATE TYPE utl_file_utility.file_type AS (
    p_path character varying(30),
    p_file_name character varying
);


| DBA/개발자 | | init 함수를 생성합니다. | 이 `init` 함수는 `bucket` 또는 `region`과 같은 공통 변수를 초기화합니다. 코드에 대한 내용은 *추가 정보* 섹션을 참조하세요. | DBA/개발자 | | 래퍼 함수를 생성합니다. | 래퍼 함수 `fopen`, `put_line` 및 `fclose`를 생성합니다. 코드에 대한 내용은 *추가 정보* 섹션을 참조하세요. | DBA, 개발자 | ### 래퍼 함수 테스트 | 작업 | 설명 | 필요한 기술 | | --- | --- | --- | | 쓰기 모드에서 래퍼 함수를 테스트합니다. | 쓰기 모드에서 래퍼 함수를 테스트하려면 *추가 정보* 섹션에 제공된 코드를 사용하세요. | DBA, 개발자 | | 추가 모드에서 래퍼 함수를 테스트합니다. | 추가 모드에서 래퍼 함수를 테스트하려면 *추가 정보* 섹션에 제공된 코드를 사용하세요. | DBA, 개발자 | ## 관련 리소스 + [Amazon S3 통합](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_PostgreSQL.S3Import.html) + [Amazon S3](https://aws.amazon.com/s3/) + [Aurora](https://aws.amazon.com/rds/aurora/?nc2=h_ql_prod_db_aa&aurora-whats-new.sort-by=item.additionalFields.postDateTime&aurora-whats-new.sort-order=desc) + [Amazon CloudWatch](https://aws.amazon.com/cloudwatch/)() + [Amazon SNS](https://aws.amazon.com/sns/?nc2=h_ql_prod_ap_sns&whats-new-cards.sort-by=item.additionalFields.postDateTime&whats-new-cards.sort-order=desc) ## 추가 정보 **IAM 정책 설정** 다음의 정책을 생성합니다. | | | 정책 이름 | JSON | | --- |--- | | S3IntRead | 
{
    "Version": "2012-10-17",		 	 	 
    "Statement": [
        {
            "Sid": "S3integrationtest",
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Resource": [
         "arn:aws:s3:::testaurorabucket/*",
         "arn:aws:s3:::testaurorabucket"
            ]
        }
    ]
}
| | S3IntWrite | 
{
    "Version": "2012-10-17",		 	 	 
    "Statement": [
        {
            "Sid": "S3integrationtest",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",                
                "s3:ListBucket"
            ],
            "Resource": [                "arn:aws:s3:::testaurorabucket/*",                "arn:aws:s3:::testaurorabucket"
            ]
        }
    ]
}
| **init 함수 생성** `bucket` 또는 `region`과 같은 일반 변수를 초기화하려면 다음 코드를 사용하여 `init` 함수를 생성하세요. ``` CREATE OR REPLACE FUNCTION utl_file_utility.init(     )     RETURNS void     LANGUAGE 'plpgsql'     COST 100     VOLATILE AS $BODY$ BEGIN       perform set_config       ( format( '%s.%s','UTL_FILE_UTILITY', 'region' )       , 'us-east-1'::text       , false );       perform set_config       ( format( '%s.%s','UTL_FILE_UTILITY', 's3bucket' )       , 'testaurorabucket'::text       , false ); END; $BODY$; ``` **래퍼 함수 생성** `fopen`,`put_line` 및 `fclose` 래퍼 함수를 생성합니다. *fopen* ``` CREATE OR REPLACE FUNCTION utl_file_utility.fopen(     p_file_name character varying,     p_path character varying,     p_mode character DEFAULT 'W'::bpchar,     OUT p_file_type utl_file_utility.file_type)     RETURNS utl_file_utility.file_type     LANGUAGE 'plpgsql'     COST 100     VOLATILE AS $BODY$ declare     v_sql character varying;     v_cnt_stat integer;     v_cnt integer;     v_tabname character varying;     v_filewithpath character varying;     v_region character varying;     v_bucket character varying; BEGIN     /*initialize common variable */     PERFORM utl_file_utility.init();     v_region := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 'region' ) );     v_bucket :=  current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 's3bucket' ) );         /* set tabname*/     v_tabname := substring(p_file_name,1,case when strpos(p_file_name,'.') = 0 then length(p_file_name) else strpos(p_file_name,'.') - 1 end );     v_filewithpath := case when NULLif(p_path,'') is null then p_file_name else concat_ws('/',p_path,p_file_name) end ;     raise notice 'v_bucket %, v_filewithpath % , v_region %', v_bucket,v_filewithpath, v_region;         /* APPEND MODE HANDLING; RETURN EXISTING FILE DETAILS IF PRESENT ELSE CREATE AN EMPTY FILE */     IF p_mode = 'A' THEN         v_sql := concat_ws('','create temp table if not exists ', v_tabname,' (col1 text)');         execute v_sql;         begin         PERFORM aws_s3.table_import_from_s3             ( v_tabname,             '',               'DELIMITER AS ''#''',             aws_commons.create_s3_uri             (     v_bucket,                 v_filewithpath ,                 v_region)             );         exception             when others then              raise notice 'File load issue ,%',sqlerrm;              raise;         end;         execute concat_ws('','select count(*) from ',v_tabname) into v_cnt;         IF v_cnt > 0         then             p_file_type.p_path := p_path;             p_file_type.p_file_name := p_file_name;         else                     PERFORM aws_s3.query_export_to_s3('select ''''',                             aws_commons.create_s3_uri(v_bucket, v_filewithpath, v_region)                                           );             p_file_type.p_path := p_path;             p_file_type.p_file_name := p_file_name;                 end if;         v_sql := concat_ws('','drop table ', v_tabname);                 execute v_sql;                 ELSEIF p_mode = 'W' THEN             PERFORM aws_s3.query_export_to_s3('select ''''',                             aws_commons.create_s3_uri(v_bucket, v_filewithpath, v_region)                                           );             p_file_type.p_path := p_path;             p_file_type.p_file_name := p_file_name;     END IF;         EXCEPTION         when others then             p_file_type.p_path := p_path;             p_file_type.p_file_name := p_file_name;             raise notice 'fopenerror,%',sqlerrm;             raise; END; $BODY$; ``` *put\$1line* ``` CREATE OR REPLACE FUNCTION utl_file_utility.put_line(     p_file_name character varying,     p_path character varying,     p_line text,     p_flag character DEFAULT 'W'::bpchar)     RETURNS boolean     LANGUAGE 'plpgsql'     COST 100     VOLATILE AS $BODY$ /************************************************************************** * Write line, p_line in windows format to file, p_fp - with carriage return * added before new line. **************************************************************************/ declare     v_sql varchar;     v_ins_sql varchar;     v_cnt INTEGER;     v_filewithpath character varying;     v_tabname  character varying;     v_bucket character varying;     v_region character varying;     BEGIN  PERFORM utl_file_utility.init(); /* check if temp table already exist */  v_tabname := substring(p_file_name,1,case when strpos(p_file_name,'.') = 0 then length(p_file_name) else strpos(p_file_name,'.') - 1 end );  v_sql := concat_ws('','select count(1) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace where n.nspname like ''pg_temp_%'''                          ,' AND pg_catalog.pg_table_is_visible(c.oid) AND Upper(relname) = Upper( '''                          ,  v_tabname ,''' ) ');    execute v_sql into v_cnt;     IF v_cnt = 0 THEN          v_sql := concat_ws('','create temp table ',v_tabname,' (col text)');         execute v_sql;         /* CHECK IF APPEND MODE */         IF upper(p_flag) = 'A' THEN             PERFORM utl_file_utility.init();                                     v_region := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 'region' ) );             v_bucket :=  current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 's3bucket' ) );                         /* set tabname*/                         v_filewithpath := case when NULLif(p_path,'') is null then p_file_name else concat_ws('/',p_path,p_file_name) end ;                                     begin                PERFORM aws_s3.table_import_from_s3                      ( v_tabname,                           '',                          'DELIMITER AS ''#''',                         aws_commons.create_s3_uri                            ( v_bucket,                                v_filewithpath,                                v_region    )                     );             exception                 when others then                     raise notice  'Error Message : %',sqlerrm;                     raise;             end;             END IF;         END IF;     /* INSERT INTO TEMP TABLE */                   v_ins_sql := concat_ws('','insert into ',v_tabname,' values(''',p_line,''')');     execute v_ins_sql;     RETURN TRUE;     exception             when others then                 raise notice  'Error Message : %',sqlerrm;                 raise; END; $BODY$; ``` *fclose* ``` CREATE OR REPLACE FUNCTION utl_file_utility.fclose(     p_file_name character varying,     p_path character varying)     RETURNS boolean     LANGUAGE 'plpgsql'     COST 100     VOLATILE AS $BODY$ DECLARE     v_filewithpath character varying;     v_bucket character varying;     v_region character varying;     v_tabname character varying; v_sql character varying; BEGIN       PERFORM utl_file_utility.init();       v_region := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 'region' ) );     v_bucket :=  current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 's3bucket' ) );     v_tabname := substring(p_file_name,1,case when strpos(p_file_name,'.') = 0 then length(p_file_name) else strpos(p_file_name,'.') - 1 end );     v_filewithpath := case when NULLif(p_path,'') is null then p_file_name else concat_ws('/',p_path,p_file_name) end ;     raise notice 'v_bucket %, v_filewithpath % , v_region %', v_bucket,v_filewithpath, v_region ;         /* exporting to s3 */     perform aws_s3.query_export_to_s3         (concat_ws('','select * from ',v_tabname,'  order by ctid asc'),             aws_commons.create_s3_uri(v_bucket, v_filewithpath, v_region)         );    v_sql := concat_ws('','drop table ', v_tabname);     execute v_sql;        RETURN TRUE; EXCEPTION        when others then      raise notice 'error fclose %',sqlerrm;      RAISE; END; $BODY$; ``` **래퍼 함수 설정 및 테스트** 다음 익명 코드 블록을 사용하여 설정을 테스트합니다. *쓰기 모드 테스트* 다음 코드는 S3 버킷에 `s3inttest`라는 파일을 작성합니다. ``` do $$ declare l_file_name varchar := 's3inttest' ; l_path varchar := 'integration_test' ; l_mode char(1) := 'W'; l_fs utl_file_utility.file_type ; l_status boolean; begin select * from utl_file_utility.fopen( l_file_name, l_path , l_mode ) into l_fs ; raise notice 'fopen : l_fs : %', l_fs; select * from utl_file_utility.put_line( l_file_name, l_path ,'this is test file:in s3bucket: for test purpose', l_mode ) into l_status ; raise notice 'put_line : l_status %', l_status; select * from utl_file_utility.fclose( l_file_name , l_path ) into l_status ; raise notice 'fclose : l_status %', l_status; end; $$ ``` *추가 모드 테스트* 다음 코드는 이전 테스트에서 만든 `s3inttest` 파일에 줄을 추가합니다. ``` do $$ declare l_file_name varchar := 's3inttest' ; l_path varchar := 'integration_test' ; l_mode char(1) := 'A'; l_fs utl_file_utility.file_type ; l_status boolean; begin select * from utl_file_utility.fopen( l_file_name, l_path , l_mode ) into l_fs ; raise notice 'fopen : l_fs : %', l_fs; select * from utl_file_utility.put_line( l_file_name, l_path ,'this is test file:in s3bucket: for test purpose : append 1', l_mode ) into l_status ; raise notice 'put_line : l_status %', l_status; select * from utl_file_utility.put_line( l_file_name, l_path ,'this is test file:in s3bucket : for test purpose : append 2', l_mode ) into l_status ; raise notice 'put_line : l_status %', l_status; select * from utl_file_utility.fclose( l_file_name , l_path ) into l_status ; raise notice 'fclose : l_status %', l_status; end; $$ ``` **Amazon SNS 알림** 선택적으로 S3 버킷에서 Amazon CloudWatch 모니터링 및 Amazon SNS 알림을 설정할 수 있습니다. 자세한 내용은 [Amazon S3 모니터링](https://docs.aws.amazon.com/AmazonS3/latest/userguide/monitoring-overview.html) 및 [Amazon SNS 알림 설정](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/US_SetupSNS.html)을 참조하세요.