使用Redshift Spectrum读取AWS Redshift中外部表中的数据 [英] Using Redshift Spectrum to read the data in external table in AWS Redshift

问题描述

我在AWS Redshift集群中执行以下操作以从S3中读取Parquet文件。

I did the below in AWS Redshift cluster to read the Parquet file from S3.

create external schema s3_external_schema 
from data catalog 
database 'dev' 
iam_role 'arn:aws:iam::<MyuniqueId>:role/<MyUniqueRole>'
create external database if not exists;

然后

CREATE external table s3_external_schema.SUPPLIER_PARQ_1 (
S_SuppKey BIGINT ,
S_Name varchar(64) ,
S_Address varchar(64) ,
S_NationKey int ,
S_Phone varchar(18) ,
S_AcctBal decimal(13, 2) ,
S_Comment varchar(105))
partitioned by (Supplier bigint, nation int)
stored as PARQUET
location 's3://<My Bucket>/<File partition>/';

以上创建语句均成功。当运行以下查询时，它返回0。

Both the above create statements were successful. When the ran the below query it is returning 0.

select * from s3_external_schema."supplier_parq_1"
limit 10;

No rows returned.

我错过了任何授予或访问权限的要求，以使其返回查询输出。

Am I missing any grants or access permission to make it return the query output.

推荐答案

调试非工作Redshift-Spectrum查询的步骤

Steps to debug a non-working Redshift-Spectrum query

1. 使用雅典娜尝试相同的查询：最简单的方法是在s3文件夹上运行粘合爬虫
   ，它应该创建一个配置单元metastore表，其中
   您可以直接查询（使用与您相同的sql


2. 使用s3 select：使用aws控制台导航到镶木
   文件之一，右键单击并选择，然后单击镶木（

如果1.或2.正常工作，那么它是您的定义可能在频谱上不正确。

If 1. or 2. are working, then it is likely your definition is not right in spectrum. instead just use the definition as created within the hive metastore like this.

create external schema some_schema from data catalog
database 'the_name_you_gave_the_hive_db'
iam_role 'whatever'
create external database if not exists;

然后，您可以使用新定义的红移频谱模式而无需进一步定义。

You can then just use the newly defined redshift spectrum schema without further definition.

这篇关于使用Redshift Spectrum读取AWS Redshift中外部表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！