Aurora Postgre SQL-Compatible에서 BLOB 파일 인코딩TEXT을 사용하여 에 파일 로드

작성자: Bhanu Ganesh Gudivada(AWS) 및 Jeevan Shetty(AWS)

환경: 프로덕션	소스: 온프레미스 Oracle 데이터베이스	대상: Aurora Postgre SQL-호환 가능
R 타입: 리아키텍트	워크로드: Oracle, 오픈 소스	기술: 마이그레이션, 데이터베이스
AWS 서비스: Amazon Aurora

요약

마이그레이션 중에 로컬 파일 시스템의 파일에서 로드되는 비정형 및 정형 데이터를 처리해야 하는 경우가 종종 있습니다. 데이터가 데이터베이스 문자 집합과 다른 문자 집합으로 되어 있을 수도 있습니다.

이러한 파일에는 다음과 같은 유형의 데이터가 들어 있습니다.

• 메타데이터 — 이 데이터는 파일 구조를 설명합니다.

• 반정형 데이터 - 또는 와 같은 특정 형식의 텍스트 문자열입니다JSONXML. 이러한 데이터에 대해 “항상 '< '로 시작” 또는 “줄 바꿈 문자를 포함하지 않음”과 같은 주장을 할 수 있습니다.

• 전체 텍스트 — 이 데이터에는 일반적으로 줄 바꿈 및 따옴표 문자를 비롯한 모든 유형의 문자가 포함됩니다. UTF-8의 멀티바이트 문자로 구성될 수도 있습니다.

• 바이너리 데이터 - 이 데이터에는 바이트 또는 null, 마커를 end-of-file 포함한 바이트 조합이 포함될 수 있습니다.

이러한 유형의 데이터를 혼합하여 로드하는 것은 어려울 수 있습니다.

이 패턴은 온프레미스 Oracle 데이터베이스 , Amazon Web Services(EC2) Cloud의 Amazon Elastic Compute Cloud(Amazon ) 인스턴스에 있는 Oracle 데이터베이스 AWS및 Oracle 데이터베이스용 Amazon Relational Database Service(Amazon RDS)와 함께 사용할 수 있습니다. 예를 들어 이 패턴은 Amazon Aurora Postgre SQL-Compatible Edition을 사용합니다.

Oracle Database에서는 BFILE (이진 파일) 포인터, DBMS_LOB 패키지 및 Oracle 시스템 함수를 사용하여 파일에서 를 로드하고 문자 인코딩CLOB을 사용하여 로 변환할 수 있습니다. PostgreSQL는 Amazon Aurora Postgre SQL호환 버전 데이터베이스로 마이그레이션할 때 BLOB 데이터 유형을 지원하지 않으므로 이러한 함수는 Postgre SQL호환 스크립트로 변환해야 합니다.

이 패턴은 Amazon Aurora Postgre SQL호환 데이터베이스의 단일 데이터베이스 열에 파일을 로드하는 두 가지 접근 방식을 제공합니다.

• 접근 방식 1 – 인코드 옵션과 함께 aws_s3 확장 프로그램의 table_import_from_s3 함수를 사용하여 Amazon Simple Storage Service(S3) 버킷에서 데이터를 가져옵니다.

• 접근 방식 2 – 데이터베이스를 벗어나 16진수로 인코딩한 다음 데이터베이스 내부에서 TEXT(을)를 볼 수 있도록 디코딩합니다.

Aurora Postgre SQL-Compatible은 aws_s3 확장과 직접 통합되므로 접근 방식 1을 사용하는 것이 좋습니다.

이 패턴은 멀티바이트 문자와 고유한 형식이 있는 이메일 템플릿이 포함된 플랫 파일을 Amazon Aurora Postgre SQL호환 데이터베이스에 로드하는 예를 사용합니다.

사전 조건 및 제한 사항

사전 조건 

• 활성 AWS 계정

• Amazon RDS 인스턴스 또는 Aurora Postgre SQL호환 인스턴스

• SQL 및 관계형 데이터베이스 관리 시스템에 대한 기본 이해(RDBMS)

• Amazon Simple Storage Service(S3) 버킷

• Oracle 및 Postgre의 시스템 함수에 대한 지식SQL

• RPM 패키지 HexDump-XXD-0.1.1(Amazon Linux 2에 포함됨)

  참고 : Amazon Linux 2의 지원 종료가 임박했습니다. 자세한 내용은 Amazon Linux 2 FAQs를 참조하세요.

제한 사항

• TEXT 데이터 유형의 경우 저장할 수 있는 가장 긴 문자열은 약 1GB입니다.

제품 버전

• Aurora는 Amazon Aurora PostgreSQL 업데이트 에 나열된 Postgre 버전을 지원합니다. SQL

아키텍처

대상 기술 스택  

• Aurora Postgre SQL- 호환

대상 아키텍처 

접근 방식 1 –aws_s3.table_import_from_s3 사용 

온프레미스 서버에서 멀티바이트 문자와 사용자 지정 형식이 있는 이메일 템플릿이 포함된 파일이 Amazon S3으로 전송됩니다. 이 패턴에서 제공하는 사용자 지정 데이터베이스 함수는 file_encoding(와)과 함께 aws_s3.table_import_from_s3 함수를 사용하여 파일을 데이터베이스에 로드하고 쿼리 결과를 TEXT 데이터 유형으로 반환합니다.

1. 파일이 스테이징 S3 버킷으로 전송됩니다.

2. 파일은 Amazon Aurora Postgre SQL호환 데이터베이스에 업로드됩니다.

3. pgAdmin 클라이언트를 사용하면 사용자 지정 함수가 Aurora 데이터베이스에 배포load_file_into_clob됩니다.

4. 사용자 지정 함수는 내부적으로 file_encoding과 함께 table_import_from_s3(을)를 사용합니다. 함수의 출력은 array_to_string 및 array_agg(을)를 TEXT 출력으로 사용하여 얻습니다.

접근 방식 2 - 데이터베이스 외부에서 16진수로 인코딩하고 데이터베이스 TEXT 내부에서 볼 수 있도록 디코딩

온프레미스 서버 또는 로컬 파일 시스템의 파일은 16진수 덤프로 변환됩니다. 그런 다음 파일을 PostgreSQL로 TEXT 필드로 가져옵니다.

1. xxd -p 옵션을 사용하여 명령줄에서 파일을 16진수 덤프로 변환합니다.

2. \copy 옵션을 사용하여 Aurora Postgre SQL-Compatible에 16진수 덤프 파일을 업로드한 다음 16진수 덤프 파일을 바이너리로 디코딩합니다.

3. 바이너리 데이터를 인코딩하여 TEXT(으)로 반환합니다.

도구

AWS 서비스

• Amazon Aurora Postgre SQL-Compatible Edition은 PostgreSQL 배포를 설정, 운영 및 확장하는 데 도움이 되는 완전 관리형 ACID규정 준수 관계형 데이터베이스 엔진입니다.

• AWS 명령줄 인터페이스(AWS CLI)는 명령줄 쉘의 명령을 통해 AWS 서비스와 상호 작용하는 데 도움이 되는 오픈 소스 도구입니다.

기타 도구

• pgAdmin4는 Postgre 의 오픈 소스 관리 및 개발 플랫폼입니다SQL. pgAdmin4는 Linux, Unix, mac OS 및 Windows에서 Postgre 를 관리하는 데 사용할 수 있습니다SQL. 

에픽

접근 방식 1: Amazon S3에서 Aurora Postgre로 데이터 가져오기 SQL-호환 가능

작업	설명	필요한 기술
EC2 인스턴스를 시작합니다.	인스턴스 시작 지침은 인스턴스 시작을 참조하십시오.	DBA
PostgreSQL 클라이언트 pgAdmin 도구를 설치합니다.	pgAdmin를 다운로드하고 설치합니다.	DBA
IAM 정책을 생성합니다.	파일이 저장될 S3 버킷에 대한 액세스 권한을 aurora-s3-access-pol 부여하는 라는 AWS Identity and Access Management(IAM) 정책을 생성합니다. 다음 코드를 사용하여 S3 버킷의 이름으로 <bucket-name>(을)를 바꿉니다. { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:GetObject", "s3:AbortMultipartUpload", "s3:DeleteObject", "s3:ListMultipartUploadParts", "s3:PutObject", "s3:ListBucket" ], "Resource": [ "arn:aws:s3:::<bucket-name>/*", "arn:aws:s3:::<bucket-name>" ] } ] }	DBA
Amazon S3에서 Aurora Postgre SQL-Compatible로 객체를 가져올 IAM 역할을 생성합니다.	다음 코드를 사용하여 AssumeRole 신뢰 관계aurora-s3-import-role로 라는 IAM 역할을 생성합니다. AssumeRole 는 Aurora가 사용자를 대신하여 다른 AWS 서비스에 액세스할 수 있도록 허용합니다. { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow","Principal": { "Service": "rds.amazonaws.com" },"Action": "sts:AssumeRole" } ] }	DBA
IAM 역할을 클러스터에 연결합니다.	IAM 역할을 Aurora Postgre SQL호환 데이터베이스 클러스터와 연결하려면 다음 AWS CLI 명령을 실행합니다. Aurora Postgre SQL호환 데이터베이스를 호스팅하는 AWS 계정의 <Account-ID> ID를 변경합니다. 이렇게 하면 Aurora Postgre SQL호환 데이터베이스가 S3 버킷에 액세스할 수 있습니다. aws rds add-role-to-db-cluster --db-cluster-identifier aurora-postgres-cl --feature-name s3Import --role-arn arn:aws:iam::<Account-ID>:role/aurora-s3-import-role	DBA
예제를 Amazon S3으로 업로드합니다.	이 패턴의 추가 정보 섹션에서 이메일 템플릿 코드를 salary.event.notification.email.vm(이)라 불리는 파일에 복사합니다. 파일을 S3 버킷으로 업로드합니다.	DBA, 앱 소유자
사용자 지정 함수 배포.	추가 정보 섹션에서 사용자 지정 함수 load_file_into_clob SQL 파일 콘텐츠를 임시 테이블에 복사합니다. Aurora Postgre SQL호환 데이터베이스에 로그인하고 pgAdmin 클라이언트를 사용하여 데이터베이스 스키마에 배포합니다.	앱 소유자, DBA
데이터를 데이터베이스로 가져오기 위한 사용자 지정 함수를 실행합니다.	다음 SQL 명령을 실행하여 앵글 브래킷의 항목을 적절한 값으로 바꿉니다. select load_file_into_clob('aws-s3-import-test'::text,'us-west-1'::text,'employee.salary.event.notification.email.vm'::text); 명령을 실행하기 전에 다음 예제와 같이 꺾쇠 괄호 안의 항목을 적절한 값으로 바꿉니다. Select load_file_into_clob('aws-s3-import-test'::text,'us-west-1'::text,'employee.salary.event.notification.email.vm'::text); 이 명령은 Amazon S3에서 파일을 로드하고 출력을 TEXT(을)로 반환합니다.	앱 소유자, DBA

접근 방식 2: 로컬 Linux 시스템에서 템플릿 파일을 16진수 덤프로 변환

작업	설명	필요한 기술
템플릿 파일을 16진수 덤프로 변환합니다.	Hexdump 유틸리티는 16진수, 십진수, 옥탈 또는 로 바이너리 파일의 내용을 표시합니다ASCII. hexdump 명령은 util-linux 패키지의 일부이며 Linux 배포판에 사전 설치되어 제공됩니다. Hexdump RPM 패키지도 Amazon Linux 2의 일부입니다. (참고: Amazon Linux 2가 거의 지원 종료되었습니다. 자세한 내용은 Amazon Linux 2 FAQs를 참조하세요.) 파일 내용을 16진 덤프로 변환하려면 다음 쉘 명령을 실행합니다. xxd -p </path/file.vm> | tr -d '\n' > </path/file.hex> 다음 예제와 같이 경로와 파일을 적절한 값으로 바꿉니다. xxd -p employee.salary.event.notification.email.vm | tr -d '\n' > employee.salary.event.notification.email.vm.hex	DBA
hexdump 파일을 데이터베이스 스키마에 로드합니다.	다음 명령을 사용하여 헥스덤프 파일을 Aurora Postgre SQL호환 데이터베이스에 로드합니다. Aurora PostgreSQL 데이터베이스에 로그인하고 라는 새 테이블을 생성합니다email_template_hex. CREATE TABLE email_template_hex(hex_data TEXT); 다음 명령을 사용하여 로컬 파일 시스템의 파일을 DB 스키마에 로드합니다. \copy email_template_hex FROM '/path/file.hex'; 경로를 로컬 파일 시스템의 위치로 바꿉니다. \copy email_template_hex FROM '/tmp/employee.salary.event.notification.email.vm.hex'; email_template_bytea(이)라 불리는 테이블을 하나 더 생성합니다. CREATE TABLE email_template_bytea(hex_data bytea); email_template_hex의 데이터를 email_template_bytea에 삽입합니다. INSERT INTO email_template_bytea (hex_data) (SELECT decode(hex_data, 'hex') FROM email_template_hex limit 1); 16진수 bytea 코드를 TEXT 데이터로 반환하려면 다음 명령을 실행합니다. SELECT encode(hex_data::bytea, 'escape') FROM email_template_bytea;	DBA

관련 리소스

참조

• PostgreSQL 데이터베이스를 AWS 데이터베이스 마이그레이션 서비스의 대상으로 사용

• Oracle Database 19c에서 Amazon Aurora로의 PostgreSQL 호환성(12.4) 마이그레이션 플레이북

• IAM 정책 생성

• Amazon Aurora MySQL DB 클러스터에 IAM 역할 연결

• pgAdmin

자습서

• Amazon 시작하기 RDS

• Oracle에서 Amazon Aurora로 마이그레이션

추가 정보

load_file_into_clob 사용자 지정 함수

CREATE OR REPLACE FUNCTION load_file_into_clob(
    s3_bucket_name text,
    s3_bucket_region text,
    file_name text,
    file_delimiter character DEFAULT '&'::bpchar,
    file_encoding text DEFAULT 'UTF8'::text)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    blob_data BYTEA;
    clob_data TEXT;
    l_table_name CHARACTER VARYING(50) := 'file_upload_hex';
    l_column_name CHARACTER VARYING(50) := 'template';
    l_return_text TEXT;
    l_option_text CHARACTER VARYING(150);
    l_sql_stmt CHARACTER VARYING(500);
        
BEGIN
    
    EXECUTE format ('CREATE TEMPORARY TABLE %I (%I text, id_serial serial)', l_table_name, l_column_name);
    
    l_sql_stmt := 'select ''(format text, delimiter ''''' || file_delimiter || ''''', encoding ''''' || file_encoding ||  ''''')'' ';
    
    EXECUTE FORMAT(l_sql_stmt)
    INTO l_option_text;
    
    EXECUTE FORMAT('SELECT aws_s3.table_import_from_s3($1,$2,$6, aws_commons.create_s3_uri($3,$4,$5))')
    INTO l_return_text
    USING l_table_name, l_column_name, s3_bucket_name, file_name,s3_bucket_region,l_option_text;
    
    EXECUTE format('select array_to_string(array_agg(%I order by id_serial),E''\n'') from %I', l_column_name, l_table_name)
    INTO clob_data;
    
    drop table file_upload_hex;
    
    RETURN clob_data;
END;
$BODY$;

이메일 템플릿

######################################################################################
##                                                                                    ##
##    johndoe Template Type: email                                                    ##
##    File: johndoe.salary.event.notification.email.vm                                ##
##    Author: Aimée Étienne    Date 1/10/2021                                                ##
##  Purpose: Email template used by EmplmanagerEJB to inform a johndoe they         ##
##        have been given access to a salary event                                    ##
##    Template Attributes:                                                             ##
##        invitedUser - PersonDetails object for the invited user                        ##
##        salaryEvent - OfferDetails object for the event the user was given access    ##
##        buyercollege - CompDetails object for the college owning the salary event    ##
##        salaryCoordinator - PersonDetails of the salary coordinator for the event    ##
##        idp - Identity Provider of the email recipient                                ##
##        httpWebRoot - HTTP address of the server                                    ##
##                                                                                    ##
######################################################################################

$!invitedUser.firstname $!invitedUser.lastname,

Ce courriel confirme que vous avez ete invite par $!salaryCoordinator.firstname $!salaryCoordinator.lastname de $buyercollege.collegeName a participer a l'evenement "$salaryEvent.offeringtitle" sur johndoeMaster Sourcing Intelligence.

Votre nom d'utilisateur est $!invitedUser.username

Veuillez suivre le lien ci-dessous pour acceder a l'evenement.

${httpWebRoot}/myDashboard.do?idp=$!{idp}

Si vous avez oublie votre mot de passe, utilisez le lien "Mot de passe oublie" situe sur l'ecran de connexion et entrez votre nom d'utilisateur ci-dessus.

Si vous avez des questions ou des preoccupations, nous vous invitons a communiquer avec le coordonnateur de l'evenement $!salaryCoordinator.firstname $!salaryCoordinator.lastname au ${salaryCoordinator.workphone}.

*******

johndoeMaster Sourcing Intelligence est une plateforme de soumission en ligne pour les equipements, les materiaux et les services.

Si vous avez des difficultes ou des questions, envoyez un courriel a [e-mail] pour obtenir de l'aide.