Aurora PostgreSQL で S3 上のテキストファイルを Import Export

  • PostgreSQL
  • RDS
  • SQL
  • スポンサーリンク

    テキストファイル

    今回の経緯

    とある依頼を受けて、テキストファイルをデータベースにインポートして、整形したデータを csv 形式でエクスポートするところまで行いました。

    Aurora PostgreSQL でテキストデータのインポートとエクスポートを行ったのですが、その際にハマったポイントなどを書いていきたいと思います。

    どんなテキストか

    取り込むテキストは以下のようなファイルです。

    DateTime^session_id^start_time^status^command^cpu_time^reads^writes^user_id^connection_id^total_elapsed_time^logical_reads^text_size^row_count^objectid^encrypted^text
    --------^----------^----------^------^-------^--------^-----^------^-------^-------------^------------------^-------------^---------^---------^--------^---------^----
    2020-12-02 03:59:59.913^60^2020-12-02 03:53:20.567^running^SELECT INTO^334747^238623^5097920^1^034493F5-66C0-4597-9474-EEC2DE07DCBE^399400^16762815^1024^1^NULL^NULL^SELECT * INTO BK1201_13_WEBCORE実行後_SD_JSSK_KBT FROM SD_JSSK_KBT WITH (NOLOCK);
    2020-12-02 03:59:59.913^63^2020-12-02 03:59:59.870^running^SELECT^31^0^0^1^22B0405A-27CE-4EF3-9553-19C7A1FA2BC9^42^3^4096^0^NULL^NULL^           SELECT GETDATE() AS [DateTime],                  resource_type AS type --オブジェクトの種類                  ,resource_associated_entity_id as entity_id  --エンティティID                  ,( CASE WHEN resource_type = 'OBJECT' THEN                        OBJECT
    2020-12-02 03:59:59.913^65^2020-12-02 03:59:59.877^suspended^WAITFOR^0^0^0^1^CCB3FF25-76EC-400A-85D1-B374732A819B^37^0^4096^0^NULL^NULL^SET NOCOUNT ON /*********************************************/ -- ブロッキングが発生しているセッションの取得 /*********************************************/ SELECT GETDATE() AS [DateTime], 	session_id,  	wait_duration_ms,  	wait_type, blocking_session_id  FROM  	sys.dm_os_wait
    2020-12-02 03:59:59.913^67^2020-12-02 03:59:59.887^running^SELECT^16^0^0^1^24292939-4952-420C-8B6A-F43A23F5B81C^27^105^4096^0^NULL^NULL^SET NOCOUNT ON /*********************************************/ -- ブロッキングが発生しているセッションのクエリ取得 /*********************************************/ SELECT GETDATE() AS [DateTime], 	session_id,  	wait_duration_ms,  	wait_type,   	( 		SELECT SUBSTRING(qt.TEXT, r.stat
    2020-12-02 03:59:59.913^68^2020-12-02 03:59:59.890^suspended^WAITFOR^16^23^0^1^11106085-A2B6-4D97-92DB-6BFA8BB22864^24^35^4096^65^NULL^NULL^ /********************************************/ -- パフォーマンスモニタからメモリ情報を取得 /********************************************/  DECLARE @InstanceName AS sysname DECLARE @MSSQLVersion AS sysname SELECT @MSSQLVersion  = CONVERT(sysname, SERVERPROPERTY ('ProductVersi
    2020-12-02 03:59:59.913^71^2020-12-02 03:59:59.893^running^SELECT^16^0^0^1^E099ABB5-75EE-4C85-829A-948E1B791781^18^0^4096^0^NULL^NULL^-- https://msdn.microsoft.com/ja-JP/library/ms191246.aspx SET NOCOUNT ON BEGIN -- リソースプールの情報を取得 	SELECT 		instance_name, 		CAST(CAST([CPU usage %] AS float) / CAST([CPU usage % base] AS float) * 100  AS int) AS [CPU Usage], 		[Max memory (KB)] / 1024 AS [M
    2020-12-02 03:59:59.913^72^2020-12-02 03:59:59.903^suspended^WAITFOR^0^0^0^1^19B7FFAD-7621-44D5-9DC5-EC185089E69D^13^0^4096^492^NULL^NULL^SET NOCOUNT ON /*********************************************/ --- Wait Stats の取得 /*********************************************/ 	---このクエリの実行前にはCLEAR.sqlファイルを実行して 	---sys.dm_os_wait_stats (累積値)を初期化してから、以下の情報を取得する  SELECT GETDATE() AS [DateTime], [wait_typ
    2020-12-02 03:59:59.913^73^2020-12-02 03:59:59.903^suspended^WAITFOR^0^0^9^1^6D2E8C9A-5950-4A1E-88ED-44184497CB30^12^738^4096^8^NULL^NULL^SET NOCOUNT ON  /*********************************************/ ---現在実行中のクエリ一覧を取得 /*********************************************/ SELECT  GETDATE() AS [DateTime], session_id, request_id, start_time, status, command, text, blocking_session_id, wait_type, wa
    2020-12-02 03:59:59.913^74^2020-12-02 03:59:59.913^running^SELECT^15^0^0^1^1CF9EB27-00C4-49E0-AF81-1FBA284D3E07^4^0^4096^0^NULL^NULL^SET NOCOUNT ON /*********************************************/ -- 30秒以上ロック待ちしているセッション情報を取得  /*********************************************/             SELECT GETDATE() AS [DateTime],                    db.NAME DBName,                    wt.wait_duration_m
    2020-12-02 03:59:59.913^75^2020-12-02 03:59:59.913^running^SELECT^15^0^0^1^6755FC7E-4364-4EAF-9375-2EC8290CC765^4^0^4096^0^NULL^NULL^SET NOCOUNT ON  /*********************************************/ ---現在実行中のクエリ一覧を取得 /*********************************************/ SELECT  GETDATE() AS [DateTime], session_id, --request_id, start_time, status, command, --blocking_session_id, --wait_type, --
    DateTime^session_id^start_time^status^command^cpu_time^reads^writes^user_id^connection_id^total_elapsed_time^logical_reads^text_size^row_count^objectid^encrypted^text
    --------^----------^----------^------^-------^--------^-----^------^-------^-------------^------------------^-------------^---------^---------^--------^---------^----
    2020-12-02 04:00:29.937^60^2020-12-02 03:53:20.567^running^SELECT INTO^356977^560623^5429745^1^034493F5-66C0-4597-9474-EEC2DE07DCBE^429416^17585390^1024^1^NULL^NULL^SEL

    ファイルの中身はデータベースに接続しているセッション情報を30秒間隔で取得している内容(約5万行)となります。データベースの運用の現場ではこういった時系列データの解析を求められることがしばしばあります。

    対応方針

    前提

    実行環境は以下です。

    項目 詳細
    OS Amazon Linux2
    DB Aurora POstgreSQL 11.8
    テキストの文字コード UTF/LF

    やっかいなこと

    テキストの内容としては、区切り文字 "^" が固定ですので単純にデータベースへインポートするだけであれば難しくなさそうです。

    ただし、ヘッダーと「--------」が毎回出力されているので、列毎のデータ型が定まっていません。そのため、解析するためにはちょっと工夫が必要になります。

    方針

    以下の方針で取り込み、解析したいと思います。

    1. 全列を varchar としてデータベースに取り込む。
    2. 不要な行を削除する。
    3. 見やすく整形してエクスポートする。

    OS 上でテキストファイルの編集を行ってもよいのですが、ファイルサイズが大きくなるとそうも言ってられません。こういうのは、さっさとデータベースに取り込んでから加工処理するのが早いです。

    準備

    はじめに

    PostgreSQL では区切り文字が決まっているテキストであれば、COPYコマンドで取り込むことができます。

    しかし、Aurora PostgreSQL では接続元(psql で接続)の AmazonLinux2 のローカルにあるファイルをCOPYコマンドでインポートしよとするとエラーとなります。

    xx00=> copy sqldata_20201202 from '/home/koizumi/github/sql_syntax/postgresql/work/sample.log' with delimiter '^' ; ERROR: must be superuser or a member of the pg_read_server_files role to COPY from a file HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

    aws_s3.table_import_from_s3

    対象のテキストファイルがローカルにあるとインポートできませんが、S3上にある場合は可能です。

    Aurora PostgreSQL からS3上のファイルをインポートして、エクスポートするためには、2つの関数を使うことになります。「aws_s3.table_import_from_s3」と「aws_s3.query_export_to_s3」です。

    関数の使い方についてはAWS公式ドキュメントをご確認ください。

    IAMロールを用意する

    インポート用のIAMロール、エクスポート用のIAMロールと2つのIAMロールを Aurora PostgreSQL へ付与しておく必要があります

    インポートで必要なポリシーはこちらです。

    {
         "Version": "2012-10-17",
         "Statement": [
           {
             "Sid": "s3import",
             "Action": [
               "s3:GetObject",
               "s3:ListBucket"
             ],
             "Effect": "Allow",
             "Resource": [
               "arn:aws:s3:::your-s3-bucket", 
               "arn:aws:s3:::your-s3-bucket/*"
             ] 
           }
         ] 
    }

    エクスポートで必要なポリシーはこちらです。

    {
         "Version": "2012-10-17",
         "Statement": [
           {
             "Sid": "s3export",
             "Action": [
               "S3:PutObject"
             ],
             "Effect": "Allow",
             "Resource": [
               "arn:aws:s3:::your-s3-bucket/*"
             ] 
           }
         ] 
    }

    それぞれのIAMロールを Aurora PostgreSQL へ付与したらこんな感じになります。

    postgresql-import-text-01

    ネットワーク

    Aurora PostgreSQL から S3 へのアクセスは外部のインターネットを通してか、VPCエンドポイントを通しての通信となります。

    そのため、以下の3パターンのいずれかのネットワーク構成であることが条件となります。

    • パブリック接続を有効にしている
    • NAT接続を有効にしている
    • S3のVPCエンドポイントを経由してアクセスできる

    ネットワークの設定が不十分である場合は、以下のようなエラーが出てしまいます。

    NOTICE: CURL error code: 28 when attempting to validate pre-signed URL, 1 attempt(s) remaining NOTICE: HINT: make sure your instance is able to connect with S3.

    長かったですが、ここまでで準備が完了です。

    やってみる

    作業の流れ

    このような流れで行います。

    1. 全列を varchar としてデータベースに取り込む。
    2. 不要な行を削除する。
    3. 見やすく整形してエクスポートする。

    1. 全列を varchar としてデータベースに取り込む。

    入れ物となるテーブルを用意します。

    xx00=> create table sqldata_20201202 ( DateTime VARCHAR, session_id VARCHAR, start_time VARCHAR, status VARCHAR, command VARCHAR, cpu_time VARCHAR, reads VARCHAR, writes VARCHAR, user_id VARCHAR, connection_id VARCHAR, total_elapsed_time VARCHAR, logical_reads VARCHAR, text_size VARCHAR, row_count VARCHAR, objectid VARCHAR, encrypted VARCHAR, text VARCHAR) ; CREATE TABLE

    権限の関係から以下の操作は Aurora PostgreSQL のマスターユーザーで実行します。

    xx00=> CREATE EXTENSION aws_s3 CASCADE; NOTICE: installing required extension "aws_commons" CREATE EXTENSION

    引き続き、マスターユーザーでインポートのコマンドを実行します。途中で遭遇したエラーと対応策についても一緒に記載しています。

    xx00=> SELECT aws_s3.table_import_from_s3( 'xx_adm.sqldata_20201202', 'DateTime,session_id,start_time,status,command,cpu_time,reads,writes,user_id,connection_id,total_elapsed_time,logical_re ads,text_size,row_count,objectid,encrypted,text', 'DELIMITER ''^''', aws_commons.create_s3_uri('koizumi-dev-data', 'sample/postgresql/20210211/sample.log', 'eu-north-1 ') ); ERROR: The credentials stored with the database instance can’t be accessed. Make sure that the desired Amazon Resource Name (ARN) is associated with the feature-name: s3Import. CONTEXT: SQL function "table_import_from_s3" statement 1 xx00=> -- IAM ROle を Aurora PostgreSQL にアタッチしていないと権限不足でエラーとなります。 xx00=> xx00=> SELECT aws_s3.table_import_from_s3( 'xx_adm.sqldata_20201202', 'DateTime,session_id,start_time,status,command,cpu_time,reads,writes,user_id,connection_id,total_elapsed_time,logical_reads,textt_size,row_count,objectid,encrypted,text', 'DELIMITER ''^''', aws_commons.create_s3_uri('koizumi-dev-data', 'sample/postgresql/20210211/sample.log', 'eu-north-1') ); ERROR: HTTP 403. Permission denied. Check bucket or provided credentials as they may no longer be valid. CONTEXT: SQL function "table_import_from_s3" statement 1 xx00=> -- IAM ROle を Aurora PostgreSQL にアタッチしているけど権限不足でエラーになっています。IAM Role のポリシーを確認しましょう。 xx00=> xx00=> SELECT aws_s3.table_import_from_s3( 'xx_adm.sqldata_20201202', 'DateTime,session_id,start_time,status,command,cpu_time,reads,writes,user_id,connection_id,total_elapsed_time,logical_reads,textt_size,row_count,objectid,encrypted,text', 'DELIMITER ''^''', aws_commons.create_s3_uri('koizumi-dev-data', 'sample/postgresql/20210211/sample.log', 'eu-north-1') ); ERROR: HTTP 403. Permission denied. Check bucket or provided credentials as they may no longer be valid. CONTEXT: SQL function "table_import_from_s3" statement 1 xx00=> -- ファイルパスに / が含まれていると上記のエラーとなります。(原因不明) xx00=> -- バケット直下に対象ファイルを置くと上手くいきます。 xx00=> xx00=> SELECT aws_s3.table_import_from_s3( 'xx_adm.sqldata_20201202', 'DateTime,session_id,start_time,status,command,cpu_time,reads,writes,user_id,connection_id,total_elapsed_time,logical_re ads,text_size,row_count,objectid,encrypted,text', 'DELIMITER ''^''', aws_commons.create_s3_uri('koizumi-dev-data', 'sample.log', 'eu-north-1') ); table_import_from_s3 -------------------------------------------------------------------------------------------------------------------------- 51379 rows imported into relation "xx_adm.sqldata_20201202" from file sample.log of 21107680 bytes (1 row)

    2. 不要な行を削除する。

    ヘッダーと「--------」が毎回出力されているので、削除します。単純な delete 文でいけます。

    xx00=> select count(*) from sqldata_20201202; count ------- 51379 (1 row) xx00=> xx00=> delete from sqldata_20201202 where datetime = 'DateTime'; DELETE 2761 xx00=> delete from sqldata_20201202 where datetime = '--------'; DELETE 2761 xx00=> xx00=> select count(*) from sqldata_20201202; count ------- 45857 (1 row)

    3. 見やすく整形してエクスポートする。

    エクスポートのコマンドは以下です。csv 形式でヘッダーを付けてエクスポートしています。

    xx00=> SELECT * from aws_s3.query_export_to_s3( 'select count(*)/2 as "run_time(min)",session_id,start_time,text from xx_adm.sqldata_20201202 group by session_id,start_time,text order by 1 desc', aws_commons.create_s3_uri('koizumi-dev-data', 'sample/postgresql/20210211/sqldata_20201202.csv', 'eu-north-1'), options :='format csv, HEADER' ); rows_uploaded | files_uploaded | bytes_uploaded ---------------+----------------+---------------- 29675 | 1 | 9587996 (1 行)

    あとは、対象のテキストファイルの数だけ繰り返します。やっていることは簡単ですので、スクリプト化すれば、今後同じような依頼がきてもすぐに対応できますね。

    postgresql-import-text-02

    今回は以上です。

  • PostgreSQL
  • RDS
  • SQL
  • スポンサーリンク