Redshift创建外部架构external schema和外部表external table

最新推荐文章于 2023-04-21 15:14:44 发布

lusklusklusk

最新推荐文章于 2023-04-21 15:14:44 发布

阅读量1.9k

点赞数

分类专栏： postgres 文章标签：架构数据库 aws

本文链接：https://blog.csdn.net/lusklusklusk/article/details/121837063

版权

postgres 专栏收录该内容

24 篇文章 2 订阅

订阅专栏

本文介绍了Amazon Redshift中创建外部schema和外部表的语法，以及如何通过Athena查询外部数据库和表。在创建external schema时，如果指定的外部数据库已存在并包含表，那么这些表会自动关联到新schema。而如果没有表，则schema会为空。同时，文章提到了查询外部架构、外部表权限和外部数据库的方法。

摘要由CSDN通过智能技术生成

https://docs.aws.amazon.com/zh_cn/zh_cn/redshift/latest/dg/c-using-spectrum.html
https://docs.aws.amazon.com/zh_cn/zh_cn/athena/latest/ug/what-is.html
https://docs.aws.amazon.com/zh_cn/zh_cn/redshift/latest/dg/r_CREATE_EXTERNAL_SCHEMA.html
https://docs.aws.amazon.com/zh_cn/zh_cn/redshift/latest/dg/r_CREATE_EXTERNAL_TABLE.html

涉及的知识点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也就自动带上了这个外部数据库的外部表