Amazon Redshiftへのデータロード処理をリモートホスト(Amazon EC2)経由で行う

しんや

2014.06.16

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Amazon Redsihftへデータをロードする際に最もポピュラーな手段はAmazon S3にデータをアップロードし、RedshiftからのCOPYコマンドでアップロードしたS3上のファイルを指定、というものになりますが、Amazon S3にファイルをアップロードすると言う事はインターネット回線を通じて情報が送信される訳で、分析に利用する重要なデータがそう言った経路で移動してしまうというのはセキュリティ面から考えて不安要素となります。Amazon S3に於けるSSL対応はクライアント側・サーバ側共に対応されていますが、やはりこの点は気になる所ではありますね。

そこで今回、EC2にファイルをアップロードし、そこから直接Redshiftにデータをロードする(S3経由では無くEC2経由でのデータロード)手段についてご紹介し、その手順を見て行きたいと思います。

解説

0.概要＆事前準備

1台または復数のリモートホストからデータをロードするためのコマンド、COPYコマンドを使用する事が出来ます。ここで言う『リモートホスト』はAWS上のEC2でも良いですし、またSSH接続を受け入れる設定が成されたUnix又はLinuxコンピュータでも構いません。

Amazon Redshiftは復数のホストにSSH接続する事が可能です。Amazon Redshiftはテキストファイルと同じように読めるような形でリモートホストの標準出力に対してテキストを生成する為に、それぞれの接続を介して独自にコマンドを送っています。

1.Amazon RedshiftクラスタのパブリックキーとノードのIPアドレスを確認

クラスタのプロパティ欄に記載されている『SSH Ingestion Settings』欄から、以下内容を控えておきます。

Cluster Public Key
Node IPアドレス

これらのIPアドレス情報は、Step3にて、ホストマシーンがAmazon Redshiftからのアクセスを許可させる際に利用します。 VPC内orVPC外のどちらにマシンが存在するかに拠ってPublic/Private IPは使い分けましょう。管理コンソール上でも確認出来ますし、以下の様にAWS CLIからでも取得が可能です。

$ aws redshift describe-clusters --cluster-identifier xxxxxxxxxxxxxxxxx | jq '.Clusters[0].ClusterNodes[]'
{
  "PublicIPAddress": "54.201.xxx.xxx",
  "PrivateIPAddress": "10.0.xxx.xxx",
  "NodeRole": "LEADER"
}
{
  "PublicIPAddress": "54.201.xxx.xxx",
  "PrivateIPAddress": "10.0.xxx.xxx",
  "NodeRole": "COMPUTE-0"
}
{
  "PublicIPAddress": "54.187.xxx.xxx",
  "PrivateIPAddress": "10.0.xxx.xxx",
  "NodeRole": "COMPUTE-1"
}

2.Amazon Redshiftクラスタのパブリックキーをホストサーバーの認証キーファイルに追加

Amazon Redshiftがアクセスを行う事になる対象のリモートサーバにSSHログイン。今回は検証用にECインスタンス(Amazon Linux AMI)を用意しました。

$ ssh -i xxxxxxxxxxx.pem ec2-user@54.xxx.xxx.xxx
Last login: Fri Jun 13 15:17:01 2014 from xxx.xxx.xxx.xxx.dy.iij4u.or.jp

       __|  __|_  )
       _|  (     /   Amazon Linux AMI
      ___|\___|___|

https://aws.amazon.com/amazon-linux-ami/2014.03-release-notes/
$

ユーザー名は後程使いますので控えておく必要があります。

Step1で取得出来るPublic Key情報を管理コンソールからコピーし、以下設定ファイルに追記します。

 $ vi /home/ec2-user/.ssh/authorized_keys
ssh-rsa AAAA..(中略)..6vUn Amazon-Redshift1

3.ホストサーバーの設定(Amazon RedshiftのIPアドレスからの全てのアクセスを許可)

EC2を用いている場合、セキュリティグループで対象のRedshiftクラスタからの接続(インバウンド)を受け入れる設定を追加しておきます。

プライベートIPアドレスを用いるケースとpublicを用いるケースがあるかと思いますが、どちらの値を利用するかは以下の様な判断基準になっているようです。現時点だと主に後者を採用する事になりそうですね。

Public IP：VPCの外にRedshiftクラスタとEC2クラシックを持ち、それらが同一リージョンに有る場合。
Private IP：VPC内にRedshiftとEC2-VPCインスタンスを持ち、それらが同一リージョン同一VPC内に有る場合。

4.ホストサーバーのパブリックキーを取得

Amazon Redshiftがホストを識別出来るように、マニフェストファイルの中にホストのパブリックキーを設定出来ます(※オプションです)。

COPYコマンドでのホストのパブリックキーは必須ではありませんが、セキュリティ上の観点から、この公開鍵を使用する事を強くお勧めします。

対象ファイルの場所は以下となります。(EC2インスタンスの場合) ちなみにAmazon RedshiftではRSSキーのみサポートしています。DSAはサポートしていません。

後の手順でマニフェストファイルを作成する際にこの内容を”Public Key”としてペーストします。なのでこの値も控えておきましょう。

$ pwd
/etc/ssh
$ ll
合計 284
-rw------- 1 root root     242153  3月 29 02:58 moduli
-rw-r--r-- 1 root root       2174  3月 29 02:58 ssh_config
-rw------- 1 root root        668  4月 12 06:35 ssh_host_dsa_key
-rw-r--r-- 1 root root        608  4月 12 06:35 ssh_host_dsa_key.pub
-rw-r----- 1 root ssh_keys    227  4月 12 06:38 ssh_host_ecdsa_key
-rw-r--r-- 1 root root        162  4月 12 06:38 ssh_host_ecdsa_key.pub
-rw-r----- 1 root ssh_keys    965  4月 12 06:38 ssh_host_key
-rw-r--r-- 1 root root        630  4月 12 06:38 ssh_host_key.pub
-rw------- 1 root root       1675  4月 12 06:35 ssh_host_rsa_key
-rw-r--r-- 1 root root        400  4月 12 06:35 ssh_host_rsa_key.pub
-rw-r--r-- 1 root root       4543  4月 12 06:35 sshd_config
$ cat ssh_host_rsa_key.pub
ssh-rsa AAAAB3N...(中略)...xZn root@ip-10-0-xxx-xxx
$

5.マニフェストファイルの追加

COPYコマンドでは、SSHを用いて復数のホストに接続し、各ホスト毎にSSH接続を作成する事が出来ます。COPYコマンドは各ホストへの接続を介してコマンドを実行し、並列にテーブルにコマンドを出力をロードします。

マニフェストファイルでは、SSHホストのエンドポイントとAmazon Redshiftにデータを返す為にホスト上で実行されるコマンドを指定します。この中には必要に応じてホストの公開鍵やログインユーザー名、各種エントリで必要とされるフラグを含める事ができます。ファイル形式は以下の通りとなります。

用意されているフィールドは以下の5個。

endpoint(エンドポイント)：接続するホストのURL若しくはIPアドレス。
command(コマンド内容)：テキストまたはバイナリ(gzip or lzop)出力を生成するためにホストによって実行されるコマンド。このコマンドは、設定に用いたユーザーが実行権限を持つものであれば何でも実行する事が出来ます。この出力(テキストファイル、gzipやlzopのバイナリファイル)はAmazon Redshiftが認識、取得出来る形式で無ければなりません。詳細はPreparing your input data - Amazon Redshiftを参照。
publickey(パブリックキー(公開鍵))：ホストのパブリックキー。Amazon Redshiftがホスト識別に使います。『ssh-rsa 』の後に続く文字列が値として設定される形となります。
mandatory(COPYコマンド実行時の障害発生を知らせるか否か)：接続に失敗した場合、COPYコマンドに障害が発生したか否かを表示します。(デフォルト: false)1つでも接続に失敗した場合、COPYコマンドは失敗とみなされます。
username(ユーザー名)：ホストにログインしリモートコマンドを実行する為のユーザー名。step2でログインに用いたユーザー名と同じになります。デフォルト:redshift

マニフェストファイルは1つの”entries”を持ち、各SSH接続で構成されています。単一ホスト、又は復数ホストに対しての接続を記載する事が可能です。フィールド名と値はダブルクォートで括って記載します。(boolean値を除く)その他詳細は以下参照。

Step 5: Create a manifest file - Amazon Redshift

{ 
   "entries": [ 
     {"endpoint":"<ssh_endpoint_or_IP>", 
       "command": "<remote_command>",
       "mandatory":true, 
       “publickey”: “<public_key>”, 
       "username": “<host_user_name>”}, 
     {"endpoint":"<ssh_endpoint_or_IP>", 
       "command": "<remote_command>",
       "mandatory":true, 
       “publickey”: “<public_key>”, 
       "username": “host_user_name”} 
    ]
}

6.マニフェストファイルをAmazon S3バケットにアップロード

Amazon Redshiftクラスタと同じリージョンに準備したS3バケットに、マニフェストファイルをアップロードします。詳細は以下参照。

Get Started With Amazon Simple Storage Service - Amazon Simple Storage Service

7.COPYコマンドの実行(データのロード)

ホストに接続し、データをAmazon RedshiftへロードするためにCOPYコマンドを実行します。COPYコマンドではマニフェストファイルのAmazon S3のオブジェクトパスとsshオプションを含んでいます。以下実行例。

copy sales
from 's3://mybucket/ssh_manifest' credentials 
'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>'
ssh;

自動圧縮を使用する場合、COPYコマンドは都合2回、データを読み込みます。これはリモートコマンドが2回実行されるという意味となります。1回目の読み取りは圧縮分析のサンプリング取得用に、2回目の読み取りで実際にデータをロードします。自動圧縮を無効にする場合、COPYコマンド実行の際のオプションでCOMPUPDATEをOFFにします。詳細は以下参照。

Loading tables with automatic compression - Amazon Redshift

実践

では、実際にどのような流れで実現に至るか、実践してみましょう。ファイルの形態によって、2つのテーマで試してみます。

実践1：基礎編(小さめのファイルで非圧縮形式で試してみる)

まずはデータのCOPY(インポート)対象となるRedshiftクラスタのクラスタパブリックキー(Cluster Public Key)及び各ノードのIPアドレス情報を控えます。管理コンソールから以下情報を確認。

AWS CLIコマンドとjqを組み合わせるとこんな感じで1発で取得出来ます。

$ aws redshift describe-clusters --cluster-identifier (クラスタID) | jq '.Clusters[0].ClusterPublicKey, .Clusters[0].ClusterNodes[].PrivateIPAddress'
"ssh-rsa AAAAB3...(中略)... vUn Amazon-Redshift"
"10.0.xxx.Xxx"
"10.0.xxx.Xxx"
"10.0.xxx.Xxx"

Redshiftがリモートアクセスするホストサーバー(今回は同一VPC内にあるEC2)にログインし、上記で控えたパブリックキー情報を所定のファイルに追記します。

$ ssh -i cm-xxx-yyy-zzz-key.pem ec2-user@54.xxx.xxx.xxx
Last login: Sat Jun 14 11:07:01 2014 from xxxxxxx.xxxxx.xx.jp

       __|  __|_  )
       _|  (     /   Amazon Linux AMI
      ___|\___|___|

https://aws.amazon.com/amazon-linux-ami/2014.03-release-notes/
$ 
$ vi /home/ec2-user/.ssh/authorized_keys
ssh-rsa AAA...(中略)...XXX xxx-xxx-xxx-key
ssh-rsa BBB...(中略)...YYY yyy-yyy-yyy-key
ssh-rsa CCC...(中略)...ZZZ zzz-zzz-zzz-key
ssh-rsa AAA...(中略)...vUn Amazon-Redshift

続いて、ホストサーバーにアクセスするRedshiftからのリモート操作を許可する設定を行います。EC2の場合はEC2に対するセキュリティグループでアクセス制御を行うのが主な手段の1つですので、今回はここで制御設定を行う形になる訳です。

EC2に設定されているセキュリティグループを見ると、現状『SSHアクセスは全て通す』にしています。一応これで通るは通るので、今回はサンプル実行というのもありますし、ここはこのままで進めましょう。

実利用で厳格にアクセスを制御させる場合には、以下の様にクラスタノードのIPアドレス(今回はVPC同一内部のものなのでプライベートな方)を設定する形となります。

セキュリティを高める為に、リモートホスト対象のパブリックキー情報を控えます。サーバーにログイン後、以下のパスにある情報を表示し、控えておいてください。

$ cd /etc/ssh
$ cat ssh_host_rsa_key.pub
ssh-rsa AAA...(中略)...xZn root@ip-10-0-xxx-xxx
$

マニフェストファイルを作成する前に、今回検証で使うデータを用意したいと思います。Amazon Redshiftの以下ドキュメントには、入門編で扱う事の出来るサンプルデータ及びテーブル定義が用意されています。

Step 5: Load Sample Data from Amazon S3 - Amazon Redshift

今回検証に用いた環境はOregon(us-west-2)ですので、対応するURLからファイルを落としておきます(ダウンロード先はEC2の所定フォルダ配下となります。

$ ssh -i xxx-yyy-zzz-key.pem ec2-user@54.xxx.xxx.xxx
$ mkdir tickit
$ cd tickit
$
$ aws s3 cp s3://awssampledbuswest2/tickit/allusers_pipe.txt allusers_pipe.txt
download: s3://awssampledbuswest2/tickit/allusers_pipe.txt to ./allusers_pipe.txt
$ wc allusers_pipe.txt
   49990  115673 5893626 allusers_pipe.txt

$ aws s3 cp s3://awssampledbuswest2/tickit/venue_pipe.txt venue_pipe.txt
download: s3://awssampledbuswest2/tickit/venue_pipe.txt to ./venue_pipe.txt
$ wc venue_pipe.txt
     202     661    7988 venue_pipe.txt
$ 

$ aws s3 cp s3://awssampledbuswest2/tickit/category_pipe.txt category_pipe.txt
download: s3://awssampledbuswest2/tickit/category_pipe.txt to ./category_pipe.txt
$ wc category_pipe.txt
      11      42     465 category_pipe.txt
$ 

$ aws s3 cp s3://awssampledbuswest2/tickit/date2008_pipe.txt date2008_pipe.txt
download: s3://awssampledbuswest2/tickit/date2008_pipe.txt to ./date2008_pipe.txt
$ wc date2008_pipe.txt
     365     365   14534 date2008_pipe.txt
$ 

$ aws s3 cp s3://awssampledbuswest2/tickit/allevents_pipe.txt allevents_pipe.txt
download: s3://awssampledbuswest2/tickit/allevents_pipe.txt to ./allevents_pipe.txt
$ wc allevents_pipe.txt
    8798   29124  445838 allevents_pipe.txt
$ 

$ aws s3 cp s3://awssampledbuswest2/tickit/listings_pipe.txt listings_pipe.txt
download: s3://awssampledbuswest2/tickit/listings_pipe.txt to ./listings_pipe.txt
$ wc listings_pipe.txt
  192497  384994 11585036 listings_pipe.txt
$ 

$ aws s3 cp s3://awssampledbuswest2/tickit/sales_tab.txt sales_tab.txt
download: s3://awssampledbuswest2/tickit/sales_tab.txt to ./sales_tab.txt
$ wc sales_tab.txt 
  172456 1897016 11260097 sales_tab.txt
$

これでロード対象のファイルが準備出来ました。

$ pwd
/home/ec2-user/tickit
$ ll
合計 28536
-rw-rw-r-- 1 ec2-user ec2-user   445838  4月  8  2013 allevents_pipe.txt
-rw-rw-r-- 1 ec2-user ec2-user  5893626  6月  5  2013 allusers_pipe.txt
-rw-rw-r-- 1 ec2-user ec2-user      465  4月  8  2013 category_pipe.txt
-rw-rw-r-- 1 ec2-user ec2-user    14534  4月  8  2013 date2008_pipe.txt
-rw-rw-r-- 1 ec2-user ec2-user 11585036  4月  8  2013 listings_pipe.txt
-rw-rw-r-- 1 ec2-user ec2-user 11260097  4月 15 19:37 sales_tab.txt
-rw-rw-r-- 1 ec2-user ec2-user     7988  4月  8  2013 venue_pipe.txt
$

ファイルが投入されるテーブルも併せて作っておきます。Redshiftクラスタにログインし、前以て以下のSQLスクリプトを実行しておいてください。

create table users(
	userid integer not null distkey sortkey,
	username char(8),
	firstname varchar(30),
	lastname varchar(30),
	city varchar(30),
	state char(2),
	email varchar(100),
	phone char(14),
	likesports boolean,
	liketheatre boolean,
	likeconcerts boolean,
	likejazz boolean,
	likeclassical boolean,
	likeopera boolean,
	likerock boolean,
	likevegas boolean,
	likebroadway boolean,
	likemusicals boolean);

create table venue(
	venueid smallint not null distkey sortkey,
	venuename varchar(100),
	venuecity varchar(30),
	venuestate char(2),
	venueseats integer);

create table category(
	catid smallint not null distkey sortkey,
	catgroup varchar(10),
	catname varchar(10),
	catdesc varchar(50));

create table date(
	dateid smallint not null distkey sortkey,
	caldate date not null,
	day character(3) not null,
	week smallint not null,
	month character(5) not null,
	qtr character(5) not null,
	year smallint not null,
	holiday boolean default('N'));

create table event(
	eventid integer not null distkey,
	venueid smallint not null,
	catid smallint not null,
	dateid smallint not null sortkey,
	eventname varchar(200),
	starttime timestamp);

create table listing(
	listid integer not null distkey,
	sellerid integer not null,
	eventid integer not null,
	dateid smallint not null  sortkey,
	numtickets smallint not null,
	priceperticket decimal(8,2),
	totalprice decimal(8,2),
	listtime timestamp);

create table sales(
	salesid integer not null,
	listid integer not null distkey,
	sellerid integer not null,
	buyerid integer not null,
	eventid integer not null,
	dateid smallint not null sortkey,
	qtysold smallint not null,
	pricepaid decimal(8,2),
	commission decimal(8,2),
	saletime timestamp);

そして次はマニフェストファイルの作成です。まず最初に処理が最後まで通せる事を確認したいので、上記ファイルの中で一番件数の少ない(11件)categoryテーブルで試してみることにします。

$ cat category_pipe.txt
1|Sports|MLB|Major League Baseball
2|Sports|NHL|National Hockey League
3|Sports|NFL|National Football League
4|Sports|NBA|National Basketball Association
5|Sports|MLS|Major League Soccer
6|Shows|Musicals|Musical theatre
7|Shows|Plays|All non-musical theatre
8|Shows|Opera|All opera and light opera
9|Concerts|Pop|All rock and pop music concerts
10|Concerts|Jazz|All jazz singers and bands
11|Concerts|Classical|All symphony, concerto, and choir concerts
$

ファイルの中身はこのように書いてみました。非圧縮のテキストファイルなので、コマンドはサンプル同様cat フォルダ名/ファイル名の形にしています。

$ vi tickit_upload_category.manifest
{
  "entries": [
       {"endpoint":"ec2-54-xxx-xxx-xxx.us-west-2.compute.amazonaws.com",
          "command": "cat tickit/category_pipe.txt",
          "mandatory":true,
          "publickey" : "AAA...(中略)...xZn",
          "username": "ec2-user"}
     ]
}

作成したファイルをS3の所定のバケットにアップロードします。必要なバケットは前以て作っておいてください。

$ aws s3 cp /(任意のフォルダ名)/tickit_upload_category.manifest s3://(バケット名)/(フォルダ名)/tickit_upload_category.manifest
upload: ./tickit_upload_category.manifest to s3://xxxxxxx/tickit_upload_category.manifest
$

アップロード出来ました。

そしていよいよ投入実行です。EC2経由でRedshiftにログインし、マニフェストファイルを指定する形でCOPY処理を実行してみます。上手く投入出来ていますね！

$ psql -h xxxxxxxxxxx.yyyyyyyyyyy.us-west-2.redshift.amazonaws.com -U root -d cmtestdb -p 5439
ユーザ root のパスワード: 
psql (9.2.7, サーバー 8.0.2)
注意： psql バージョン 9.2, サーバーバージョン 8.0.
         psql の機能の中で、動作しないものがあるかもしれません。
SSL 接続 (暗号化方式: ECDHE-RSA-AES256-SHA, ビット長: 256)
"help" でヘルプを表示します.

cmtestdb=#
cmtestdb=# \timing
タイミングは on です。
cmtestdb=# COPY category
cmtestdb-# FROM 's3://xxxxxxxxxxxx/tickit_upload_category.manifest'
cmtestdb-# CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYY'
cmtestdb-# DELIMITER '|'
cmtestdb-# SSH;
INFO:  Load into table 'category' completed, 11 record(s) loaded successfully.
COPY
時間: 4592.616 ms
cmtestdb=# 
cmtestdb=# SELECT COUNT(*) FROM category;
 count 
-------
    11
(1 行)

時間: 740.862 ms
cmtestdb=# SELECT * FROM category ORDER BY catid;
 catid | catgroup |  catname  |                  catdesc                   
-------+----------+-----------+--------------------------------------------
     1 | Sports   | MLB       | Major League Baseball
     2 | Sports   | NHL       | National Hockey League
     3 | Sports   | NFL       | National Football League
     4 | Sports   | NBA       | National Basketball Association
     5 | Sports   | MLS       | Major League Soccer
     6 | Shows    | Musicals  | Musical theatre
     7 | Shows    | Plays     | All non-musical theatre
     8 | Shows    | Opera     | All opera and light opera
     9 | Concerts | Pop       | All rock and pop music concerts
    10 | Concerts | Jazz      | All jazz singers and bands
    11 | Concerts | Classical | All symphony, concerto, and choir concerts
(11 行)

時間: 1391.212 ms
cmtestdb=#

実践2：応用編(大きめのファイルを圧縮形式でロード出来るか試す)

『実践1』では、まず1回通しで上手く行くかどうかを試してみました。上手く行ったので、次はより実践的なケースとして『大きいファイルを圧縮した形で投入出来るのかどうか』についても試してみたいと思います。Redshiftで扱うファイルは得てしてサイズが大きい場合も多く、1ファイル数百万行、数百メガというのもザラにあります。そんな場合、gz等の圧縮方式でファイルを圧縮する事で、ロード後のサイズであったり、ロード処理そのものの短縮化を図る事が出来ます。

まずはファイル圧縮。今回サンプルで用いた中で一番件数の多い(19万件)listingテーブルで一連の流れを試してみたいと思います。

$ pwd
/home/ec2-user/tickit
$ gzip listings_pipe.txt
$ ll *.gz
-rw-rw-r-- 1 ec2-user ec2-user 4162257  4月  8  2013 listings_pipe.txt.gz
$

試しにcatで中を除いて見ます。予想通り残念な結果となってしまいました...

$ cat listings_pipe.txt.gz
???5??\?????O>????????{Ii?o??[??M?V?+??????R?i+??;?̟R+?(以下略)

替わりにzcatというコマンドを使うと、圧縮したファイルも読めました。これで行ってみましょう。

zcat 圧縮ファイルの内容を表示する - UNIXコマンド辞典：CodeZine（コードジン）

$ zcat listings_pipe.txt.gz
1|36861|7872|1850|10|182.00|1820.00|2008-01-24 06:43:29
2|16002|4806|1890|7|233.00|1631.00|2008-03-05 12:25:29
3|21461|4256|2131|2|182.00|364.00|2008-11-01 07:35:33
4|8117|4337|1970|8|38.00|304.00|2008-05-24 01:18:37
5|1616|8647|1963|4|175.00|700.00|2008-05-17 02:29:11
:
:

マニフェストファイルを作成し、アップロード。

$ vi tickit_upload_listing.manifest
{
  "entries": [
       {"endpoint":"ec2-xxx-xxx-xxx-xxx.us-west-2.compute.amazonaws.com",
          "command": "zcat tickit/listings_pipe.txt.gz",
          "mandatory":true,
          "publickey" : "AAA...(中略)...xZn",
          "username": "ec2-user"}
     ]
}

$ aws s3 cp /path/to/manifest-file-folder/tickit_upload_listing.manifest s3://(任意のバケット名及びフォルダ名)/tickit_upload_listing.manifest
upload: ./tickit_upload_listing.manifest to s3://(任意のバケット名及びフォルダ名)/tickit_upload_listing.manifest
$

Redshiftにログインし、COPY処理実行。19万件、ファイル容量にするとおよそ4メガ(容量そんなに大きくなかった)ですが、約20秒程で投入出来ました。圧縮形式のファイルでも問題無く扱えそうですね。

cmtestdb=# \timing
タイミングは on です。
cmtestdb=# 
cmtestdb=# COPY listing
cmtestdb-# FROM 's3://xxxxxxxxxxxxx/tickit_upload_listing.manifest'
cmtestdb-# CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYY'
cmtestdb-# DELIMITER '|'
cmtestdb-# SSH;
INFO:  Load into table 'listing' completed, 192497 record(s) loaded successfully.
COPY
時間: 18007.349 ms
cmtestdb=#

また、2回目以降の実施では1回目の時よりも処理時間がかなり早くなっていました。

cmtestdb=# \timing
タイミングは on です。
cmtestdb=# COPY listing
cmtestdb-# FROM 's3://xxxxxxxxxxxxx/tickit_upload_listing.manifest'
cmtestdb-# CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYY'
cmtestdb-# DELIMITER '|'
cmtestdb-# SSH;
INFO:  Load into table 'listing' completed, 192497 record(s) loaded successfully.
COPY
時間: 1525.460 ms
cmtestdb=# SELECT COUNT(*) FROM listing;
 count  
--------
 192497
(1 行)

ちなみに上記ではgz圧縮形式なのにGZIPオプションを付けていない状態で実行した(んで上手く行った)ので、『GZIPオプション付けたらどうなるんだろう？』と思い、試してみました。すると以下の様な結果に。今回のケースの場合は逆にGZIPオプションは不要なんですね。この辺、実行方法による処理や性能の違いなどはあるものなのでしょうか。少し気になります。

(S3経由でのデータロードの場合):S3バケット上にgz圧縮ファイルを用意→Redshift:COPY処理によるロード(GZIPオプション必要)
(EC2経由でのデータロードの場合):EC2上にgz圧縮ファイルを用意→Redshift:COPY処理によるロード(GZIPオプション無し)

cmtestdb=# COPY listing
cmtestdb-# FROM 's3://xxxxxxxxxx/tickit_upload_listing.manifest'
cmtestdb-# CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYY'
cmtestdb-# DELIMITER '|'
cmtestdb-# GZIP
cmtestdb-# SSH;
ERROR:  ErrorMessage: Failed to inflate ErrorCode: -3
DETAIL:  
  -----------------------------------------------
  error:  ErrorMessage: Failed to inflate ErrorCode: -3
  code:      9007
  context:   ec2-user@ec2-xxx-xxx-xxx-xxx.us-west-2.compute.amazonaws.com 'zcat tickit/listings_pipe.txt.gz'
  query:     13976
  location:  sshrequest.cpp:151
  process:   query0_22 [pid=12004]
  -----------------------------------------------

cmtestdb=#

まとめ＆考察

以上、Amazon S3を介さずに、EC2にアップロードしたファイルをそのままRedshiftへインポートする処理についてまとめてみました。

一連の流れを図示してみると、以下の様な感じでしょうか。

COPY処理(Redshiftへのインポート)対象となるファイルをサーバ(EC2)の所定の場所にアップロード。(→ 上記フロー: 1.)
COPY処理時に用いるマニフェストファイルをS3の所定のバケット/フォルダにアップロード。(→ 上記フロー: 2.)
サーバ(EC2)を介してRedshiftにログイン、COPY処理を実行。(→ 上記フロー: 3.)
Redshiftがマニフェストファイル作成の為の情報を収集。(→ 上記フロー: 4.)
- endpoint: Redshiftがアクセスを行うサーバ(EC2)を識別するURL or IP。
- command: Redshiftが中身を識別出来るような、サーバ(EC2)上で実行可能なコマンド。(cat等)
- username: サーバ(EC2)にログインする為に利用するユーザー名。
サーバ(EC2)からRedshiftにログインし、COPY文実行。
Redshiftはマニフェストファイルを読みに行く。
読みに行ったマニフェストファイルを元に、対象となるサーバ(EC2)へのアクセスを行う。
- サーバ(EC2)では、authorized_keysへRedshiftのPublic Keyを追加してるのでアクセス出来る。(→ 上記フロー: 5.)
- 更に、対象となるノードのアクセス制御もサーバ(EC2)のセキュリティグループで許可されているので大丈夫。(→ 上記フロー: 6.)
- usernameとpublickeyの情報でサーバ(EC2)へのアクセスを行う。(→ 上記フロー: 6.)
- commandに指定されている処理を行う事で、Redshiftが内容を認識出来る。
Redshiftは受け取った内容を判別し、処理を行う。(→ 上記フロー: 7.)
ロード完了。(→ 上記フロー: 7.)

実戦投入する際に気になる事

経路としてはこれで『EC2から直接Redshiftにデータをインポート』する事が出来そうですが、実践で利用するには少し工夫が必要になるのかな〜という気もしています。上記までの内容であれば、稼働させるスクリプトを定期的に実行する事で

所定のテーブルに対し、所定のサーバ内フォルダにあるファイルを全件投入する

事は出来るでしょう。しかし実際の環境ではそういうテーブルばかりでは無いはずです。

日次・もしくは時間単位で生成されるファイルはどうやって取り込むか？
あるタイミングで取り込むファイルが復数の場合はどうするか？可変の場合はどうするか？
投入前に何らかのデータベースに対する処理(指定条件でDBレコードを削除しておく)が必要になった場合、どうするか？

見た感じマニフェストファイルには変数的なものは差し込めなさそうなので、1つのファイルで取り込む処理を使いまわすのは無理そうです。そうすると都度作成する必要がありそうです。ファイル(テーブル)毎に取り込むサイクルや内容は異なるでしょうから、ファイル(テーブル)毎にファイルが生成→サーバ(EC2)上に生成されたタイミングでそのファイル内容に即した形でマニフェストファイルを作成し、S3バケットに所定のルールでアップロード、辺りがイメージ出来そうな流れでしょうか。従来の手法であればS3に可変条件でインポートするファイルを上げていたところをマニフェストファイルに置き換えるイメージです。

また、事前にデータベースの処理が必要な場合はどうしましょうか。こちらも恐らく、あるタイミング・トリガーでSQLを実行するスクリプト処理が走り、その中でSQLが実行され、COPY処理を走らせるはずです。そのような処理が必要となった場合はCOPY処理の前に任意の処理を実行させる事で対応出来るでしょう。

この辺り、いずれにしてもテーブル(ファイル)毎に処理を洗い出し、それぞれに必要なルール・サイクルを実現する為の仕組みを整えていく必要がありそうです。私からは以上です。

参考情報

Loading data from remote hosts - Amazon Redshift

Amazon Redshiftへのデータロード処理をリモートホスト(Amazon EC2)経由で行う

目次

解説

0.概要＆事前準備

1.Amazon RedshiftクラスタのパブリックキーとノードのIPアドレスを確認

2.Amazon Redshiftクラスタのパブリックキーをホストサーバーの認証キーファイルに追加

3.ホストサーバーの設定(Amazon RedshiftのIPアドレスからの全てのアクセスを許可)

4.ホストサーバーのパブリックキーを取得

5.マニフェストファイルの追加

6.マニフェストファイルをAmazon S3バケットにアップロード

7.COPYコマンドの実行(データのロード)

実践

実践1：基礎編(小さめのファイルで非圧縮形式で試してみる)

実践2：応用編(大きめのファイルを圧縮形式でロード出来るか試す)

まとめ＆考察

実戦投入する際に気になる事

参考情報

イベント

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【4/18リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS/システムアーキテクト/Android案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/19（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩

EVENT【4/24（水）リモート】会社員からフリーランスエンジニアへ、変化の時の体験談 [パネルトーク] フリーランストーク#10

Amazon Redshiftへのデータロード処理をリモートホスト(Amazon EC2)経由で行う

目次

解説

0.概要＆事前準備

1.Amazon RedshiftクラスタのパブリックキーとノードのIPアドレスを確認

2.Amazon Redshiftクラスタのパブリックキーをホストサーバーの認証キーファイルに追加

3.ホストサーバーの設定(Amazon RedshiftのIPアドレスからの全てのアクセスを許可)

4.ホストサーバーのパブリックキーを取得

5.マニフェストファイルの追加

6.マニフェストファイルをAmazon S3バケットにアップロード

7.COPYコマンドの実行(データのロード)

実践

実践1：基礎編(小さめのファイルで非圧縮形式で試してみる)

実践2：応用編(大きめのファイルを圧縮形式でロード出来るか試す)

まとめ＆考察

実戦投入する際に気になる事

参考情報

イベント

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【4/18リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜AWS/システムアーキテクト/Android案件特集〜 を開催します

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/19（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制 はじめの一歩

EVENT【4/24（水）リモート】会社員からフリーランスエンジニアへ、変化の時の体験談 [パネルトーク] フリーランストーク#10

関連記事

CDKでNatInstanceProviderV2を使ってNATインスタンスを構築できるようになりました

NICE DCV サーバーをセットアップした Ubuntu インスタンスを AWS CDK で構築してみた

AWS CDK での EC2 インスタンス構築で cfn-signal を使って user data 実行完了を待機してみた

Amazon EC2 インスタンスを AWS CDK で構築する際のインスタンスプロファイル作成では grant メソッドを使うのが便利

EVENT【4/18リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS/システムアーキテクト/Android案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩