{RedshiftDB}Getting started with Amazon Redshift Spectrum

https://dev.classmethod.jp/articles/amazon-redshift-getting-started-with-spectrum/

aws s3 mb s3://bucket123
aws s3 ls

-- 2. IAMロール作成

{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "redshift.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
}

aws iam create-role \
--role-name role01 \
--assume-role-policy-document file://role01.json

-- 3. ポリシーをロールにアタッチ
aws iam attach-role-policy \
--policy-arn arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess \
--role-name role01

aws iam attach-role-policy \
--policy-arn arn:aws:iam::aws:policy/AWSGlueConsoleFullAccess \
--role-name role01

-- 4. クラスター作成
aws redshift create-cluster \
--db-name test \
--cluster-identifier redshift01 \
--cluster-type single-node \
--node-type dc2.large \
--master-username test \
--master-user-password 'password' \
--no-allow-version-upgrade \
--no-publicly-accessible \
--automated-snapshot-retention-period 0

aws redshift describe-clusters
aws redshift describe-clusters --cluster-identifier redshift01

-- 5. IAMロールをクラスターに関連付ける

aws redshift modify-cluster-iam-roles \
--cluster-identifier redshift01 \
--add-iam-roles arn:aws:iam::999999999999:role/role01

-- 6. S3にテストデータアップロード

aws s3 ls s3://bucket123

vim tab1.txt
1,AAA
2,BBB
3,CCC

aws s3 cp tab1.txt s3://bucket123/tab1/

-- 7. 外部スキーマと外部テーブルの作成

psql -h redshift01.xxxxxxxxxxxx.ap-northeast-1.redshift.amazonaws.com -p 5439 -d test -U test

create external schema spectrum
from data catalog
database 'spectrumdb'
iam_role 'arn:aws:iam::999999999999:role/role01'
create external database if not exists;

drop table spectrum.tab1;
create external table spectrum.tab1(
col1 integer,
col2 varchar(100))
row format delimited
fields terminated by ','
stored as textfile
location 's3://bucket123/tab1/'
;

select count(*) from spectrum.tab1;
select * from spectrum.tab1;

select * FROM pg_external_schema pe join pg_namespace pn ON pe.esoid = pn.oid;

Athenaにデータベースとテーブルが作成される

-- 8. クリーンアップ

-- クラスター削除

aws redshift delete-cluster \
--cluster-identifier redshift01 \
--skip-final-cluster-snapshot

-- ロールの一覧
aws iam list-roles | grep role01

-- ロールの削除

aws iam detach-role-policy \
--role-name role01 \
--policy-arn arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess

aws iam detach-role-policy \
--role-name role01 \
--policy-arn arn:aws:iam::aws:policy/AWSGlueConsoleFullAccess

aws iam delete-role --role-name role01

-- バケット一覧
aws s3 ls

-- バケット削除
aws s3 rb s3://bucket123 --force