AWS - Redshift - Spectrum 外部数据

1. 创建IAM Role
   在 Select your use case 下，选择 Redshift - Customizable，然后选择 Next: Permissions。
   此时显示 Attach permissions policy 页面。
   选择 AmazonS3ReadOnlyAccess 和 AWSGlueConsoleFullAccess（如果使用的是 AWS Glue 数据目录）。或选择 AmazonAthenaFullAccess（如果使用的是 Athena Data Catalog）。
   选择 Next: Review。

2. 将Role 赋给 Redshift Cluster。
   选中 集群，点击“管理IAM角色”。添加一个role。

3. 创建外部 schema 及 外部表 （glue）
   3.1 创建外部 schema

testdb=# create external schema chen_glue_db_schema from data catalog
testdb-# database 'chen_glue_db'
testdb-# iam_role 'arn:aws-cn:iam::524560386974:role/chen-redshift-glue-role'
testdb-# create external database if not exists;
INFO:  External database "chen_glue_db" already exists
CREATE SCHEMA

查看是否导入

testdb=# select * from pg_catalog.svv_external_tables;
     schemaname      |      tablename       |                              location                              |               input_format               |                       output_format                        |                 se
rialization_lib                  |                 serde_parameters                 | compressed |                                 parameters
---------------------+----------------------+--------------------------------------------------------------------+------------------------------------------+------------------------------------------------------------+-------------------
---------------------------------+--------------------------------------------------+------------+-----------------------------------------------------------------------------
 chen_glue_db_schema | chen_glue_table_0603 | s3://chen-redshift-spectrum/testdb1/spectrum/chen_glue_table_0603/ | org.apache.hadoop.mapred.TextInputFormat | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat | org.apache.hadoop.
hive.serde2.OpenCSVSerde         | {"separatorChar":","}                            |          0 | {"classification":"csv"}
 spectrum            | sales                | s3://chen-redshift-spectrum/testdb1/spectrum/sales                 | org.apache.hadoop.mapred.TextInputFormat | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat | org.apache.hadoop.
hive.serde2.lazy.LazySimpleSerDe | {"field.delim":"\t","serialization.format":"\t"} |          0 | {"EXTERNAL":"TRUE","numRows":"172000","transient_lastDdlTime":"1587466643"}
 spectrum            | test01               | s3://chen-redshift-spectrum/testdb1/spectrum/test01                | org.apache.hadoop.mapred.TextInputFormat | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat | org.apache.hadoop.
hive.serde2.lazy.LazySimpleSerDe | {"field.delim":"\t","serialization.format":"\t"} |          0 | {"EXTERNAL":"TRUE","numRows":"172000","transient_lastDdlTime":"1587466757"}
 spectrum            | test02               | s3://chen-redshift-spectrum/testdb1/spectrum/test02                | org.apache.hadoop.mapred.TextInputFormat | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat | org.apache.hadoop.
hive.serde2.lazy.LazySimpleSerDe | {"field.delim":"\t","serialization.format":"\t"} |          0 | {"EXTERNAL":"TRUE","numRows":"172000","transient_lastDdlTime":"1587467742"}
(4 rows)

查询表

testdb=# select * from chen_glue_db_schema.chen_glue_table_0603;
 id | col1
----+------
  1 | a
  2 | b
(2 rows)

当在创建external schema 时，出现 这个role，说明创建role 时，没有选择对的 service。
重新创建role，Service 选择 Redshift。添加 policy。

AmazonS3FullAccess
AmazonRedshiftFullAccess
AWSGlueConsoleFullAccess
AWSGlueServiceRole
AmazonAthenaFullAccess

错误

testdb=# create external schema s_test0604 from data catalog database 'spectrumdb_db_0604' iam_role 'arn:aws-cn:iam::XXXXXXXXXXXX:role/chen-glue-role' create external database if not exists;
ERROR:  User arn:aws-cn:redshift:cn-northwest-1:XXXXXXXXXXXX:dbuser:chen-test/dbadmin is not authorized to assume IAM Role arn:aws-cn:iam::XXXXXXXXXXXX:role/chen-glue-role

之后再创建时就可以了。