イノベーション エンジニアブログ


株式会社イノベーションのエンジニアたちの技術系ブログです


このエントリーをはてなブックマークに追加

Amazon Redshift Spectrum を CSVフォーマットで試してみた

SREチームの城田です。
先日発表された Amazon Redshift Spectrum をCSVフォーマットで試してみました。

今回は、大量のgz圧縮されたCSVデータをS3に退避はしているけれど、 必要な時に効率良く参照できない。

という想定の元に、
データをS3に置いたままで、Redshift側から検索をして素早くデータを抽出できるか、

というのをやってみました。

前提

* Macを使っている
* IAMロールの設定ができる
* S3バケットを作成できる
* VPC、セキュリティグループの設定ができる

基本的には調べながら作成できれば問題ありません。

やったこと

* IAMロールを作成
* S3バケットを作成
* Redshiftクラスターを作成
* ローカルマシンにpsqlをインストール
* Redshiftスキーマを作成
* Redshiftテーブルを作成
* ダミーデータ作成+S3アップロード
* テスト実施

IAMロールを作成

今回試験するのに、以下の権限付きのRedshift用IAMロールを作成しました。

* AmazonS3ReadOnlyAccess
* AmazonAthenaFullAccess

redshiftspectrum_iamrole.png

S3バケットを作成

大量のデータを退避しているという想定の、S3バケットを作成しました。

Redshiftクラスターを作成

以下スペックのRedshiftクラスターを作成しました。

* dc1.large
* Single Node

redshiftspectrum_create_instance.png

また、 Redshift用に作成したIAMロールを関連付けて、

redshiftspectrum_roleset.png

Redshiftに付与したセキュリティグループで、 ローカルマシンからアクセスできるように、
5439番ポートを開けてアクセスできるようにしておきました。

ローカルマシンにpsqlをインストール

psqlコマンドが使えるようにPostgreSQLをインストールしておきます。

brew install postgresql

Homebrewがあるから簡単ですね。

Redshiftテーブルを作成

psqlコマンドでRedshiftクラスターに接続します。

$ psql -h my-dw-instance.xxxxxxxxxxxx.us-east-1.redshift.amazonaws.com -U awsuser -d dev -p 5439
Password for user awsuser: {{ Redshift作成時に設定したパスワードを入力 }}

Redshiftスキーマを作成

今回は s3 という名前で作成します。

dev=# create external schema if not exists s3
dev-# from data catalog database 's3' region 'us-east-1'
dev-# iam_role 'arn:aws:iam::xxxxxxxxxxxx:role/mySpectrumRole'
dev-# create external database if not exists;
INFO:  External database "s3" created
CREATE SCHEMA

iam_roleに先ほど作成したものを指定しています。

Redshiftテーブルを作成

今度はRedshiftのテーブルを作成します。
sample_log というテーブル名で作成しました。
LOCATION にデータを退避している(想定の)S3キーを指定してます。

dev=# create external table s3.sample_log
dev-# (
dev(# ID BIGINT,
dev(# SUBID INT,
dev(# RANDOM32 VARCHAR(32),
dev(# RANDOM36 VARCHAR(36)
dev(# )
dev-# ROW FORMAT DELIMITED
dev-# FIELDS TERMINATED BY ','
dev-# STORED AS TEXTFILE
dev-# LOCATION 's3://xxxxxxxxxxxxxxxxxx/data/';
CREATE EXTERNAL TABLE

ダミーデータ作成+S3アップスクリプトを作成

さて、ダミーデータを用意せねばなりません。
今回はちょっと雑ですが、以下のようなPythonスクリプトを作成して用意しました。

import random
import os

def random_string(length, seq='0123456789abcdefghijklmnopqrstuvwxyz'):
    sr = random.SystemRandom()
    return ''.join([sr.choice(seq) for i in xrange(length)])

for i in range(100):

    f = open('/tmp/tmp.txt', 'w')
    for j in range(1000000):
        f.write(str(i) + ',' + str(j) + ',' + random_string(32) + ',' + random_string(36) + "\n")

    f.close()

    os.system("gzip /tmp/tmp.txt")
    os.system("aws s3 cp /tmp/tmp.txt.gz s3://xxxxxxxxxxxxxxxxxx/data/dummy%05d.txt.gz" % i)

    os.remove('/tmp/tmp.txt.gz')

create_data.py という名前を付けて実行。

$ python create_data.py
upload: ../../../../tmp/tmp.txt.gz to s3://xxxxxxxxxxxxxxxxxx/data/dummy00000.txt.gz
upload: ../../../../tmp/tmp.txt.gz to s3://xxxxxxxxxxxxxxxxxx/data/dummy00001.txt.gz
upload: ../../../../tmp/tmp.txt.gz to s3://xxxxxxxxxxxxxxxxxx/data/dummy00002.txt.gz
upload: ../../../../tmp/tmp.txt.gz to s3://xxxxxxxxxxxxxxxxxx/data/dummy00003.txt.gz
upload: ../../../../tmp/tmp.txt.gz to s3://xxxxxxxxxxxxxxxxxx/data/dummy00004.txt.gz
.
.
.
upload: ../../../../tmp/tmp.txt.gz to s3://xxxxxxxxxxxxxxxxxx/data/dummy00095.txt.gz
upload: ../../../../tmp/tmp.txt.gz to s3://xxxxxxxxxxxxxxxxxx/data/dummy00096.txt.gz
upload: ../../../../tmp/tmp.txt.gz to s3://xxxxxxxxxxxxxxxxxx/data/dummy00097.txt.gz
upload: ../../../../tmp/tmp.txt.gz to s3://xxxxxxxxxxxxxxxxxx/data/dummy00098.txt.gz
upload: ../../../../tmp/tmp.txt.gz to s3://xxxxxxxxxxxxxxxxxx/data/dummy00099.txt.gz

データサンプル

0,0,1zqkkjvgnakwvhg96k1mwndliw3jptv7,u991jjq04hlr573dnmmc7wpssxnig831ipbn
0,1,k6k04g4gdx5e85yigfgtw5fvkptyolpc,h8s3j951ig4icdb4xb6dcnx4td38ybcchli4
0,2,3a09o5nc97r9pbb5ihtpivwq1niqkp84,6uoifqv93dy12cd7eg28vomyvb59qxkc8x1u
0,3,hfj8au2p1vewjjcal2toh068tz9kt7e5,2mps7ibn92rqz6y121ud4ehqxofkb4l1c66m
0,4,6dg00mgvrkj6f4hnrl7i38w0ulp8p5df,dfpuqfn7bupb0lt751qbd2th58v55tdciqhk
0,5,w393e4gvrzulhytut7tuvcdcr02swt2o,yt5me2nmyz3zx4brlicj8gfs9h0ok40xzrkl
0,6,fpn75xlon0dwus5s8ctcsm3yz9ynsp6k,8ixdou96g0f95ta3ux7974ngoplub1c2dcnu
0,7,4lfb2jj5lr4nax991pz6mk2ljcbykjn8,kw0k5vwg9sudkry1mu8apg956x7keckarf9k
0,8,2t63x4v88gnu2mwxod0anxfmfemqkalb,vpwlwtgzyqe9lgwn90ybt4z8n7cpoc1iqtrw
0,9,6la6ti0ozow73stz22c4fhn848pxtun1,4wv6avo6lttzlw7a7uuvkk85sr043owamupt
0,10,8sidi4fgcs1fqys127g0qjp67eqr4qgn,d3f637zpajcwmxkwbrco9cnomoyzvtchea0t
.
.
.
0,999990,otgrxipdpyhxjdcp3q49nmhj7b415hhp,vjtkiobr8uh32672f9xr6oo9qhjyf492l83o
0,999991,tpghiy111ble1z9a9jablylinfdge1hy,5r0df8nwghcv362liap2qc96ypaep0ucc359
0,999992,hkrg6eliomboikgpwksrket8ftsr4i2q,pt7dwvkara0dpw9xhaf3t8jp8bz274gbm1b5
0,999993,rdn8rr0gqcqir24wke4v3whyrlf2z65c,8bztjosad8ju0marle7gr7i9v4ghe27qu6es
0,999994,ec2w2fz0k2f4d8lhidnnmm3vkthop7uj,dnwe1g673bwmsg0f7irumn3x6dvcdsub7k3s
0,999995,mymg7v2jzrzojlapaj8k01cvgnqbnqxf,g50l8m2b9qtexwgi7r3dgyzc20ourna6syih
0,999996,c8tlcyr9bp0yagcf7by0mgal035rrcd5,mi0zz501tszdeuf9ngo3d71n24cru0ibper6
0,999997,1c0bcypwnp1bhqkok2lwnojg3wn7hm60,yo1e4gpfn41s9qexf901uotw7uibrx5emu6k
0,999998,5tu0nurxvfh5kn7cfrlp7fjw73u3g6mq,5peos6w1yylk2u2wurg8ppooyo4l9ngigfmr
0,999999,iqitu5k40kkmks49fufaacrsuswpawub,0vwp2l2suczgxed2lmxkjjh60drcos1hskqa

1ファイル100万行のダミーデータをgzip圧縮して(容量は1ファイル50MBくらい)S3に100個アップしました。
1億レコードで、圧縮した状態で5GBのデータで試すという形です。

テスト実施

まずは全カウントをしてみました。

dev=# select count(*) from s3.sample_log;
   count
-----------
 100000000
(1 row)

きちんと1億レコードを認識しています。

処理時間が表示されるように以下を発行後、

dev=# \timing
Timing is on.

RANDOM32カラム または RANDOM36カラムに abcdef があるレコードを曖昧検索で抽出
というだいぶ厳しそうな条件で発行してみました。

dev=# select count(*) from s3.sample_log where RANDOM32 like '%abcdef%' or RANDOM36 like '%abcdef%';
 count
-------
     4
(1 row)

Time: 20634.836 ms

4件存在し、 初回のカウントは20秒という結果でした。

引き続き3回程カウントを抜かしてselectしますと、

dev=# select * from s3.sample_log where RANDOM32 like '%abcdef%' or RANDOM36 like '%abcdef%';
 id | subid  |             random32             |               random36
----+--------+----------------------------------+--------------------------------------
 35 | 805353 | brsf6gzhxgeweu2oaaeyt65vyh1lz3bh | i1t1pihdh9qka5yo5fabcdefab7e30sxhov7
 77 | 846965 | x0541r83i6abcdefmrjumgke3rkl8bsp | 5go63m8ap5ytwipuq15enqwl94bmu90ikmqx
 71 | 816479 | 0sbdcftufibobtzydbkfo6ip0jo94gyy | brkxgxkw8h0skagskabcdefbno20rtiwz1q3
 67 |  11035 | b9jcvrwygbrcb381srlosnzabcdefyhc | dnsqm6s1egq01kprky44kva535m2p0or1qjx
(4 rows)

Time: 8120.441 ms

dev=# select * from s3.sample_log where RANDOM32 like '%abcdef%' or RANDOM36 like '%abcdef%';
 id | subid  |             random32             |               random36
----+--------+----------------------------------+--------------------------------------
 35 | 805353 | brsf6gzhxgeweu2oaaeyt65vyh1lz3bh | i1t1pihdh9qka5yo5fabcdefab7e30sxhov7
 67 |  11035 | b9jcvrwygbrcb381srlosnzabcdefyhc | dnsqm6s1egq01kprky44kva535m2p0or1qjx
 71 | 816479 | 0sbdcftufibobtzydbkfo6ip0jo94gyy | brkxgxkw8h0skagskabcdefbno20rtiwz1q3
 77 | 846965 | x0541r83i6abcdefmrjumgke3rkl8bsp | 5go63m8ap5ytwipuq15enqwl94bmu90ikmqx
(4 rows)

Time: 8253.445 ms

dev=# select * from s3.sample_log where RANDOM32 like '%abcdef%' or RANDOM36 like '%abcdef%';
 id | subid  |             random32             |               random36
----+--------+----------------------------------+--------------------------------------
 35 | 805353 | brsf6gzhxgeweu2oaaeyt65vyh1lz3bh | i1t1pihdh9qka5yo5fabcdefab7e30sxhov7
 71 | 816479 | 0sbdcftufibobtzydbkfo6ip0jo94gyy | brkxgxkw8h0skagskabcdefbno20rtiwz1q3
 77 | 846965 | x0541r83i6abcdefmrjumgke3rkl8bsp | 5go63m8ap5ytwipuq15enqwl94bmu90ikmqx
 67 |  11035 | b9jcvrwygbrcb381srlosnzabcdefyhc | dnsqm6s1egq01kprky44kva535m2p0or1qjx
(4 rows)

Time: 8366.372 ms

いずれも8秒程度で抽出ができました。

また、補足として以下もテスト

/* RANDOM32の値を指定してselect */
dev=# select * from s3.sample_log where RANDOM32 = 'brsf6gzhxgeweu2oaaeyt65vyh1lz3bh';
 id | subid  |             random32             |               random36
----+--------+----------------------------------+--------------------------------------
 35 | 805353 | brsf6gzhxgeweu2oaaeyt65vyh1lz3bh | i1t1pihdh9qka5yo5fabcdefab7e30sxhov7
(1 row)

Time: 7734.470 ms

/* SUBIDの値を指定してselect */
dev=# select * from s3.sample_log where subid = 805353;
 id | subid  |             random32             |               random36
----+--------+----------------------------------+--------------------------------------
 12 | 805353 | n53dsbz694zz89mh83du47qnv4b2nej1 | ldejx8egrl06i5qy0mww4avjjy4wst70t8cg
 17 | 805353 | oo7alkvelivqg6pct82matdhr5xxslhd | bvhnbn2dmqx8l6cwbppyouajtsuaksswzy53
  9 | 805353 | 5cv5z13xczyg82pouhrj8shpejmmsr67 | eg91h26hh7uv1iin1ob3kddbvzcgxoyr514n
  4 | 805353 | uac0hdlve3q8zp1z3crtud7qeeql970g | 2sw86zxgej4fia8lncfxjvjan7syb5bpjm1d
  6 | 805353 | tl9zr98po4wb693m1lk5pnug2fcmivao | bgpjqpu92pvimnefagzt01tzb82bjpix8elv
  3 | 805353 | dx33np1513y5vpkq802lzikgj63q9bpp | 8txcpvrzvs9anjkre8nutrzcsf1y75k4whr1
 15 | 805353 | 5a42klxrn2i76alez22emc8h4hsk4s8x | yqph5wq6g2n5redjpp68swd6yx5eee9m131v
 10 | 805353 | u36i65y7m01j9wom4dlc2i7tv57njbpa | zrviu7687l9yn6h1mnbbv00i1n0p976da5yb
  1 | 805353 | 30kwf6i0inx844r6yklrbpy6lpioem6d | kiehe0088ipfd9zao63jfp9pfjesv8jm9qhq
 19 | 805353 | 0m1lmtkbiqhmxqalghru6zpi6r1jnc3o | admn6yo627mboxpds4v4kraqsn832zdb2zhm
 32 | 805353 | td9gri5l69lmfkf2jjuka3xrxjlicaob | g29gdvm71szonzg41vb47f6g99xs59knfufd
 27 | 805353 | 44pc6r8vsho0qaeny46krb8mbpastejb | fyfrck74liycfk7g1m9h0o43vmjnoql6gqcy
 35 | 805353 | brsf6gzhxgeweu2oaaeyt65vyh1lz3bh | i1t1pihdh9qka5yo5fabcdefab7e30sxhov7
 29 | 805353 | fwh7dfst0jr0ong0oc1deetc8nuuqiv6 | l3kkk5lzcdcr8geamuxy3288m17kgq5eehol
 36 | 805353 | gs0tuugtyieyom2gwkvmm0mufl32zfjs | 81ft7mjd606xqkzdzwi5upzugytpztafk0ny
 34 | 805353 | 4halp6flopyahph02k4ugsa59409wz2o | kd6mz096qu6osovsmv6i49f6fw8n2ivi133f
 33 | 805353 | 2xezecspfr7mefxqbzcvmd5ii7ofe1xo | 9fnfczevuwiz52tt4mszupo8fcr6uqjk501q
 22 | 805353 | 66jq4zhzvlipld2hkvgix0gyoeortkyj | j4g3r3vx0y6dkurq146167aygkpgwmy4150a
 38 | 805353 | vo1dtk9aabbehge3cepfe7vrznf03cvw | 2qelonyufg6zoxlimn7ua4zjyiqjiez87bfk
 39 | 805353 | xwpkuaozx1yj9keds0ztp3dpfwjk1n3u | fp03vzti5k66eb63z5lpyslbfpt7lqiwqhpr
 43 | 805353 | kh5tk35vbz9pfdko48ukumbhww17vqyi | jb1cbs4xzlbd3jt6zq7nwqhg0sih4jas07oj
 50 | 805353 | 0m5p1pfrg7491g623c36atptxqbb4pcd | mph287obxn958qlfuym52ktgbt4ounjechav
 49 | 805353 | 2q8p33mmagvaajoeyjp8fp4if4t0thuk | 9s7q1t4ud7zq00dzk8qi9z7ym9htpa4vcgnm
 48 | 805353 | jdrm2lwg8ifpzpx5e7gad8my028i78qs | an7ka7nv0ie7193hmcw961ts228eakfr1k24
 51 | 805353 | jcsmy5xy77cwfz8kwn33dsxzhobwmp28 | qcib39x7g9kxvtfkvliot9xv5t93jdblqnil
 56 | 805353 | exptg0l0gxvaq6tp7x7sefd06uee7hxz | 1ge6ghmyvkjxnrqjruyfm98emzm7ofx593mt
 52 | 805353 | b2dy2vk6mjqq4t1dl7ctvwy53irxc38r | xj1rol1lqqk7mq2c5tgqmbuxilhi914jzuoz
 55 | 805353 | k1i9hqfik66gua1j3m3gbw3lzkr204ff | wufhgn0cb8v7dy20v66n0f11llycb38r4nru
 57 | 805353 | ekgshv3i5rju6pyfijnedc234vkizozl | diyreoqqd5yonxqoz65j7h8y6i87im8tmp8a
 58 | 805353 | 1sg7u21snmo85vlrk7gqgk8esqyg9xrq | a0ntb1t1sbrwzmpzgezfp8stfstozycipx4w
.
.
.
Time: 7923.497 ms

いずれも8秒程度でデータを抽出できました。

感じたこと

今回は一番非力なRedshiftのシングルノードで試しましたが、
このスペックで、S3に圧縮退避してある1億行のCSVデータを
Redshiftに放り込むことなく、
SQLベースで数秒でデータ抽出できるのは、とても有用だと感じました。

次回機会があれば、この100倍の100億行ぐらいのデータを準備して試してみたいと思います。