本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # 在 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 (Amazon 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 ## Architecture **目標技術堆疊** + Amazon Aurora PostgreSQL 相容 + Amazon CloudWatch + Amazon Simple Notification Service (Amazon SNS) + Amazon S3 **目標架構** 下圖顯示解決方案的高階表示法。 ![\[資料檔案會上傳至 S3 儲存貯體，使用 aws_s3 副檔名處理，並傳送至 Aurora 執行個體。\]](http://docs.aws.amazon.com/zh_tw/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 (Amazon 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 (Amazon SNS) 會協調和管理發佈者和用戶端之間的訊息傳遞或傳送。在此模式中，Amazon SNS 用於傳送通知。 + [pgAdmin](https://www.pgadmin.org/docs/) – pgAdmin 是 Postgres 的開放原始碼管理工具。pgAdmin 4 提供圖形界面，用於建立、維護和使用資料庫物件。 **Code** 為了實現所需的功能， 模式會使用類似於 的命名來建立多個函數`UTL_FILE`。*其他資訊*區段包含這些函數的程式碼基底。 在程式碼中，將 取代`testaurorabucket`為測試 S3 儲存貯體的名稱。`us-east-1` 將 取代為測試 S3 儲存貯體所在的 AWS 區域。 ## 史詩 ### 整合 Amazon S3 和 Aurora PostgreSQL 相容 | 任務 | Description | 所需的技能 | | --- | --- | --- | | 設定 IAM 政策。 | 建立 AWS Identity and Access Management (IAM) 政策，以授予 S3 儲存貯體和其中物件的存取權。如需程式碼，請參閱*其他資訊*一節。 | AWS 管理員，DBA | | 將 Amazon S3 存取角色新增至 Aurora PostgreSQL。 | 建立兩個 IAM 角色：一個角色用於讀取，一個角色用於寫入存取 Amazon S3。將兩個角色連接到 Aurora PostgreSQL 相容叢集： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/prescriptive-guidance/latest/patterns/set-up-oracle-utl_file-functionality-on-aurora-postgresql-compatible.html)如需詳細資訊，請參閱 Aurora PostgreSQL 相容文件，了解如何[將資料匯入](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_PostgreSQL.S3Import.html)和[匯出](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/postgresql-s3-export.html)至 Amazon S3。 | AWS 管理員，DBA | ### 在 Aurora PostgreSQL 相容中設定擴充功能 | 任務 | Description | 所需的技能 | | --- | --- | --- | | 建立 aws\$1commons 延伸模組。 | `aws_commons` 延伸模組是`aws_s3`延伸模組的相依性。 | DBA、開發人員 | | 建立 aws\$1s3 延伸模組。 | `aws_s3` 延伸模組會與 Amazon S3 互動。 | DBA、開發人員 | ### 驗證 Amazon S3 和 Aurora PostgreSQL 相容整合 | 任務 | Description | 所需的技能 | | --- | --- | --- | | 測試將檔案從 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 公用程式，請建立包裝函式 | 任務 | Description | 所需的技能 | | --- | --- | --- | | 建立 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` 函數會初始化常見的變數，例如 `bucket`或 `region`。如需程式碼，請參閱*其他資訊*一節。 | DBA/開發人員 | | 建立包裝函式。 | 建立包裝函式 `fopen`、 `put_line`和 `fclose`。如需程式碼，請參閱*其他資訊*一節。 | DBA、開發人員 | ### 測試包裝函式 | 任務 | Description | 所需的技能 | | --- | --- | --- | | 在寫入模式下測試包裝函式。 | 若要在寫入模式下測試包裝函式，請使用*其他資訊*區段中提供的程式碼。 | 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"
            ]
        }
    ]
}
| **建立初始化函數** 若要初始化常見變數，例如 `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$; ``` *關閉* ``` 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)。