Redshift建立外部架構external schema和外部表external table

涉及的知識點Athena
Amazon Athena是一種互動式查詢服務，可以使用標準SQL輕鬆分析Amazon Simple Storage Service（Amazon S3）中的資料。透過AWS管理控制檯中的一些操作，您可以將Athena指向儲存在Amazon S3中的資料。在 Athena 中，表和資料庫是為底層源資料定義架構的後設資料定義的容器。對於每個資料集，Athena 中都需要存在一個表。表中的後設資料讓 Athena 知道資料在 Amazon S3 中的位置，並指定資料的結構，例如列名稱、資料型別和表的名稱。資料庫是表的邏輯分組，並且只儲存資料集的後設資料和架構資訊。




建立external schema的標準語法
CREATE EXTERNAL SCHEMA [IF NOT EXISTS] local_schema_name
FROM { [ DATA CATALOG ] | HIVE METASTORE | POSTGRES | MYSQL }
DATABASE 'database_name'
[ REGION 'aws-region' ]
[ URI 'hive_metastore_uri' [ PORT port_number ] ]
IAM_ROLE 'iam-role-arn-string'
SECRET_ARN 'ssm-secret-arn'             
[ CATALOG_ROLE 'catalog-role-arn-string' ]
[ CREATE EXTERNAL DATABASE IF NOT EXISTS ]
--備註：上述DATA CATALOG中的DATABASE就是指外部資料庫，即可以Amazon Athena中看到的外部資料庫，而非pg_database裡面的資料庫


建立external table的標準語法
CREATE EXTERNAL TABLE
external_schema.table_name  
(column_name data_type [, …] )
[ PARTITIONED BY (col_name data_type [, … ] )]
[ { ROW FORMAT DELIMITED row_format |
  ROW FORMAT SERDE 'serde_name'
  [ WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ] } ]
STORED AS file_format
LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file' }
[ TABLE PROPERTIES ( 'property_name'='property_value' [, ...] ) ]



查詢外部架構external schema的語句
select * from SVV_EXTERNAL_SCHEMAS

查詢外部架構external schema對應的許可權的語句
select * from pg_namespace where nspname='XX'

查詢外部資料庫的語句
select * from SVV_EXTERNAL_DATABASES
備註：external database沒有獨立的語句來建立，即無法獨立create\delete EXTERNAL DATABASE dbname，只能隨external schema建立時加上 CREATE EXTERNAL DATABASE IF NOT EXISTS自動建立或在Athena介面手工建立或刪除

查詢外部表external table的語句
select * from SVV_EXTERNAL_TABLES




問題
1、如下語句建立後，intraday_spectrum下自動生成了一堆外部表external table
create external schema intraday_spectrum
from data catalog
database 'timeseries'
iam_role 'arn:aws:iam::807898748937:role/RedshiftS3IntraDayLoadProd';

2、如下語句建立後，intraday_spectrum2下是空的沒有外部表external table
create external schema intraday_spectrum2
from data catalog
database 'timeseries2'
iam_role 'arn:aws:iam::807898748937:role/RedshiftS3IntraDayLoadProd';


解答：因為在Athena介面看到AwsDataCatalog下面有個資料庫叫'timeseries'，它下面已經存在了很多現成的外部表，這個資料庫是外部資料庫和我們redshift中查詢的pg_database不一樣，SVV_EXTERNAL_DATABASES會記錄該外部資料庫，所以當這個外部資料已經存在外部表時，我們在建立外部架構external schema時指定這個外部資料庫，這個外部架構external schema也就自動帶上了這個外部資料庫的外部表