テキストファイル

今回の経緯

とある依頼を受けて、テキストファイルをデータベースにインポートして、整形したデータを 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万行）となります。データベースの運用の現場ではこういった時系列データの解析を求められることがしばしばあります。

対応方針

前提

実行環境は以下です。

やっかいなこと

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

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

方針

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

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

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

準備

はじめに

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

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

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 へ付与したらこんな感じになります。

ネットワーク

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

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

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

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

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

やってみる

作業の流れ

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

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

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

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

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

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

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

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

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

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

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

今回は以上です。