Amazon Redshift Spectrum에서 외부 테이블을 생성하고 쿼리하려면 어떻게 해야 합니까?

해결 방법

Amazon Redshift Spectrum을 사용하면 Amazon Redshift 테이블로 데이터를 로드할 필요 없이 Amazon Simple Storage Service(Amazon S3)에서 데이터를 쿼리할 수 있습니다. Amazon Redshift Spectrum은 데이터가 Amazon S3 버킷에 남아 있는 동안 모든 쿼리를 처리합니다.

중요: 시작하기 전에 Amazon Redshift가 S3 버킷 및 외부 데이터 카탈로그에 액세스할 수 있는 권한이 있는지 확인하십시오. 또한 Amazon Redshift 클러스터와 S3 버킷은 동일한 AWS 리전에 있어야 합니다.

Amazon Redshift Spectrum에서 외부 테이블을 생성하려면 다음 단계를 수행하십시오.

1.    Amazon Redshift에 대한 AWS Identity and Access Management(IAM) 역할을 생성합니다.

2.    IAM 정책 첨부:
AWS Glue 데이터 카탈로그를 사용하는 경우 AmazonS3ReadOnlyAccess 및 AWSGlueConsoleFullAccess IAM 정책을 역할에 연결합니다.
Amazon Athena Data Catalog를 사용 중인 경우 AmazonAthenaFullAccess IAM 정책을 역할에 연결합니다.

3.    IAM 역할을 Amazon Redshift 클러스터에 연결합니다.

4.    외부 스키마를 생성합니다. 외부 스키마는 외부 데이터 카탈로그의 데이터베이스를 참조합니다. 또한 외부 스키마는 S3에 대한 Amazon Redshift 액세스를 승인하는 Amazon 리소스 이름(ARN)을 IAM 역할에 제공합니다.

다음 예에서는 S3(tickitdb.zip)의 샘플 데이터 파일을 사용합니다. 다음과 같이 개별 파일의 압축을 풀고 AWS 리전의 S3 버킷에 파일을 로드합니다.

s3://<bucket_name>/tickit/spectrum/event/' and 's3://<bucket_name>/tickit/spectrum/sales/

아래와 비슷한 명령을 사용하여 외부 스키마를 생성할 수 있습니다.

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

참고: IAM 역할의 ARN을 생성한 ARN으로 바꿉니다. 데이터베이스 파라미터에 대한 외부 데이터베이스 이름(예: “spectrumdb”)을 지정해야 합니다.

5.    외부 테이블을 생성합니다. 예를 들어 다음과 같이 EVENT 데이터에 대한 외부 테이블을 만들 수 있습니다.

create external table spectrum.event(     eventid integer,
         venueid smallint,
         catid smallint,
         dateid smallint,
         eventname varchar(200),
         starttime timestamp)row format delimitedfields terminated by '|'stored as textfile location 's3://<bucket_name>/tickit/spectrum/event/';

외부 테이블에 대한 자세한 내용은 Redshift Spectrum에 대한 외부 테이블 생성을 참조하십시오.

AWS Glule를 사용하여 외부 테이블을 생성하려면 AWS Glue 데이터 카탈로그에 테이블 정의를 추가해야 합니다. 여러 가지 방법으로 AWS Glue 데이터 카탈로그에 테이블 정의를 추가할 수 있습니다. 테이블 정의 추가에 대한 자세한 내용은 AWS Glueconsole의 테이블 사용을 참조하십시오.

Amazon Athena를 사용하여 외부 테이블을 만들려면 다음과 같이 테이블 정의를 추가하십시오.

CREATE EXTERNAL TABLE `spectrumdb.event`(  `eventid` int, 
      `venueid` smallint, 
      `catid` smallint, 
      `dateid` smallint, 
      `eventname` string, 
      `starttime` timestamp)
    ROW FORMAT DELIMITED 
      FIELDS TERMINATED BY '|' 
    STORED AS INPUTFORMAT 
      'org.apache.hadoop.mapred.TextInputFormat' 
    OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'LOCATION  's3://<bucket_name>/tickit/spectrum/event';

6.    SVV_EXTERNAL_TABLES에 대해 다음 쿼리를 실행하여 외부 스키마에서 참조하는 모든 외부 테이블을 봅니다.

select schemaname , tablename , location from svv_external_tables where schemaname = 'spectrum';

schemaname | tablename | location
----------------+---------------------------------+-----------------------------------------------------------------------
spectrum | event | s3://<bucket-name>/<file-location>

7.    외부 테이블(예: Amazon Redshift Spectrum 테이블)을 쿼리하려면 SELECT 문을 사용합니다.

select top 3 spectrum.sales.eventid, sum(spectrum.sales.pricepaid) from spectrum.sales, spectrum.event
where spectrum.sales.eventid = spectrum.event.eventid
and spectrum.sales.pricepaid > 30
group by spectrum.sales.eventid
order by 2 desc;

 eventid |   sum
---------+----------
     289 | 51846.00
    7895 | 51049.00
    1602 | 50301.00

이 예제 쿼리는 외부 EVENT 테이블과 외부 SALES 테이블을 조인합니다.

---

관련 정보

Amazon Redshift Spectrum을 사용하여 외부 데이터 쿼리

Amazon Redshift Spectrum의 쿼리 문제 해결