やること
※以下記事の続きです。
Redshift Spectrumを利用してS3データをクエリする。
（EC2から操作します。）
Redshift Spectrumとは
RedshiftテーブルにデータをロードすることなくS3に対してクエリする機能のこと。
前提
S3にtodayとyesterdayという2つのプレフィックスを切り、それぞれにtoday.csv、yesterday.csvというファイルを配置しています。
それぞれの中身は以下の通りです。
- today.csv
1,ALEX,23
2,HANNA,24
3,TARO,30
6,JHON,28
7,JOJI,29
- yesterday.csv
1,ALEX,23;
2,HANNA,24;
3,TARO,25;
4,HANAKO,26;
5,STEVE,27;
上記2ファイルに対してRedshift Spectrumでクエリを実行します。
手順
①Redshift Spectrum用スキーマ作成
②Redshift Spectrum用テーブル作成
③クエリ実行
①Redshift Spectrum用スキーマ作成
Redshift Spectrumを利用しS3に配置されたファイルに対してクエリを実行するには、Redshift Spectrum用のスキーマとテーブルを作成する必要があります。
スキーマ作成にはCREATE EXTERNAL SCHEMA文、テーブル作成にはCREATE EXTERNAL TABLE文を使用します。
まずはスキーマを作成します。
以下条件にてCREATE EXTERNAL SCHEMA文を作成します。
（今回の条件）
- スキーマ名：spectrum_test
- 使用するデータベース：dev
（CREATE EXTERNAL SCHEMA文）
CREATE EXTERNAL SCHEMA spectrum_test
FROM data catalog
DATABASE dev
iam_role '作成したIAMロールのARN'
CREATE EXTERNAL DATABASE IF NOT EXISTS;
IAMロールの作成は、本記事の前提記事である以下をご覧ください。
また、IAMロールのARNの確認方法は以下の通りです。
アクセス管理>IAMロール>作成したIAMロールをクリック
ARNをコピー
実行結果
dev=# CREATE EXTERNAL SCHEMA spectrum_test
FROM data catalog
DATABASE dev
iam_role '作成したIAMロールのARN'
CREATE EXTERNAL DATABASE IF NOT EXISTS;
INFO: External database "dev" already exists
CREATE SCHEMA
dev=#
\dnコマンドでスキーマが作成されていることを確認
dev=# \dn
List of schemas
name | owner
-----------------+-------
catalog_history | rdsdb
public | rdsdb
spectrum_test | admin
(3 rows)
dev=#
以上にて、Redshift Spectrum用スキーマ作成は完了です。
②Redshift Spectrum用テーブル作成
スキーマ同様、CREATE EXTERNAL TABLE文にてRedshift Spectrum用テーブルを作成します。
（今回の条件）
- スキーマ：spectrum_test
- テーブル名：today（もしくはyesterday）
- カラム
-
id：INTEGER型 -
name：VARCHAR(50)型（文字数制限：50文字） -
age：INTEGER型
※CREATE EXTERNAL TABLE文ではPrimary KeyやNot Null等が使えないので注意！
-
- フォーマット：CSV（org.apache.hadoop.hive.serde2.OpenCSVSerdeを使用）
- S3パス：s3://バケット名/today（もしくはyesterday）/
（CREATE EXTERNAL TABLE文）
# today
CREATE EXTERNAL TABLE spectrum_test.today
(
id INTEGER,
name VARCHAR(50),
age INTEGER
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
STORED AS TEXTFILE
LOCATION 's3://バケット名/today/';
# yesterday
CREATE EXTERNAL TABLE spectrum_test.yesterday
(
id INTEGER,
name VARCHAR(50),
age INTEGER
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
STORED AS TEXTFILE
LOCATION 's3://バケット名/yesterday/';
実行結果
dev=# CREATE EXTERNAL TABLE spectrum_test.today
(
id INTEGER,
name VARCHAR(50),
age INTEGER
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
STORED AS TEXTFILE
LOCATION 's3://バケット名/today/';
CREATE EXTERNAL TABLE spectrum_test.yesterday
(
id INTEGER,
name VARCHAR(50),
age INTEGER
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
STORED AS TEXTFILE
LOCATION 's3://バケット名/yesterday/';
CREATE EXTERNAL TABLE
CREATE EXTERNAL TABLE
dev=#
CREATE EXTERNAL TABLE文の制約等は以下公式ドキュメントから確認できます。
以上にて、Redshift Spectrum用テーブル作成は完了です。
③クエリ実行
以下SELECT文でS3ファイルに対してクエリを実行します。
（today）
SELECT id, name, age FROM spectrum_test.today;
（yesterday）
SELECT id, name, age FROM spectrum_test.yesterday;
実行結果
dev=# SELECT id, name, age FROM spectrum_test.today;
id | name | age
----+-------+-----
1 | ALEX | 23
2 | HANNA | 24
3 | TARO | 30
6 | JHON | 28
7 | JOJI | 29
(5 rows)
dev=# SELECT id, name, age FROM spectrum_test.yesterday;
id | name | age
----+--------+-----
1 | ALEX | 23
2 | HANNA | 24
3 | TARO | 25
4 | HANAKO | 26
5 | STEVE | 27
(5 rows)
S3にアップロードしたCSVファイルの中身をSQLで抽出することができました！
以上にて、クエリ実行は完了です。
参考にさせていただいた記事