AWS 기반 데이터 웨어하우징 - Amazon Redshift

Table of Contents

과제 1: Amazon Redshift client 구성
과제 2: Amazon Redshift 생성
과제 3: Amazon Redshift에 연결
과제 4: 외부 테이블 생성
과제 5: Amazon S3에 저장된 데이터를 쿼리
과제 6: 파티션된 데이터 사용
실습 완료

Note

Amazon Redshift Spectrum을 사용하면 데이터를 Amazon Redshift 테이블로 로드할 필요 없이 Amazon S3에 있는 파일의 정형 및 반정형 데이터를 효율적으로 쿼리하고 검색할 수 있습니다. Redshift Spectrum 쿼리는 대규모 병렬 처리를 사용하여 대규모 데이터 세트를 매우 빠르게 실행합니다. 처리 대부분이 Redshift Spectrum 계층에서 발생하며, 데이터 대부분이 Amazon S3에 유지됩니다. 여러 개의 클러스터가 클러스터별로 데이터 복사본을 만들지 않고도 Amazon S3에 있는 같은 데이터 세트를 동시에 쿼리할 수 있습니다.

이 자습서에서는 Amazon Redshift Spectrum을 사용하여 Amazon S3에 있는 파일에서 바로 데이터를 쿼리하고 Amazon Redshift 클러스터에 저장된 데이터에 이를 조인하는 방법을 배웁니다.

목표

본 실습을 완료하면 다음을 할 수 있게 됩니다.

Amazon S3에 저장된 데이터를 가리키는 외부 테이블을 생성
Amazon S3에 저장된 데이터를 쿼리
Amazon Redshift 테이블의 데이터와 Amazon S3에 저장된 데이터를 조인
파티션된 데이터 사용

소요 시간

본 실습은 완료하는 데 50분이 소요됩니다.

과제 1: Amazon Redshift client 구성

Redshift 클러스터에 접근하기 위하여 client 를 구성합니다. 크게 3가지 방식으로 Redshift 클러스터에 접근 할 수 있습니다.

Amazon Redshift JDBC 또는 ODBC 드라이버
SQL Workbench/J (SQL Workbench/j 설치)
pgweb

이번 과제에서는 간편하게 Redshift 클러스터의 동작을 알아보기 위해서 pgweb 을 이용하여 접근을 합니다.

pgweb 을 구동하기 위한 ec2 인스턴스를 생성합니다.

인스턴스의 ami 는 최신 linux ami 를 선택하십시오.

인스턴스 타입을 적절한 타입으로 선택하십시오.

인스턴스가 초기화 될때 pgweb이 자동으로 설치 될 수 있도록 다음의 내용을 userdata 에 추가 하시기 바랍니다.

#!/bin/bash
wget https://github.com/sosedoff/pgweb/releases/download/v0.9.12/pgweb_linux_amd64.zip
unzip pgweb_linux_amd64.zip
mv pgweb_linux_amd64 pgweb
echo "/pgweb --bind=0.0.0.0 > stdout.txt 2> stderr.txt" > pgweb.sh
echo "source /pgweb.sh" >> /etc/rc.local
chmod +x /etc/rc.local
chmod +x /pgweb.sh
source /pgweb.sh

테그를 추가하여 다른 인스턴스와 구분이 가능하도록 Name 으로 pgweb 을 설정합니다.

인스턴스가 초기화 된 이후에 웹브라우저로 접근을 하기 위하여 8081 포트를 접속 가능하도록 보안그룹을 수정합니다.

인스턴스가 생성되면, 다음과 같이 ec2 대시보드에서 정보를 확인 가능합니다. 아래 화면에서 우측 하단에 있는 Public DNS 항목에 마우스 커서를 가져가면, url 을 클립보드에 복사 하기가 가능합니다. 해당 주소를 클리보드에 복사한 후 브라우저에서 접속을 시도합니다.

접속이 성공하게 되면 다음의 화면을 보시게 됩니다.

과제 2: Amazon Redshift 생성

실습을 위하여 Redshift 클러스터를 생성합니다. 클러스터는 셈플 데이터가 있는 us-west-2 와 동일한 리전에 생성해야 합니다. 또한 Redshift 의 접속을 의하여 보안 그룹 설정을 주의하여 생성 하시기 바랍니다.

과제 3: Amazon Redshift에 연결

이 과제에서는 Amazon Redshift 클러스터에 연결합니다.

과제 2에서 생성한 pgweb 인스턴스의 IP 주소를 복사합니다.

이는 pgweb 소프트웨어를 실행하는 웹 서버의 IP 주소입니다.

웹 브라우저에서 새 탭을 열고 복사한 IP 주소를 붙여넣은 후 \[Enter\]를 누릅니다.

pgweb 로그인 화면이 표시됩니다.

다음 설정을 구성합니다.
- Host: 는 과제 1에서 생성한 Redshift 의 End point 주소 값을 복사해 붙여넣습니다.
- Username:
- Password:
- Database:
- Port: (기본값과 다름)
- Connect를 클릭

Port 값을 변경할 수 없는 경우 SSL을 disable로 설정한 후 다시 시도합니다.

과제 4: 외부 테이블 생성

이 과제에서는 외부 테이블을 생성합니다. 일반 Redshift 테이블과는 달리 외부 테이블은 Amazon S3에 저장된 데이터를 참조합니다.

먼저 외부 스키마를 정의합니다. 외부 스키마는 외부 데이터 카탈로그에 있는 _데이터베이스_를 참조하고, 클러스터가 사용자 대신 Amazon S3에 액세스할 수 있도록 권한을 부여하는 IAM 역할 식별자(ARN)를 제공합니다.

INSERT\-YOUR\-REDSHIFT\-ROLE을 앞선 과제에서 생성한 RedshiftRole 의 ARN 값으로 대체하고 _pgweb_에서 이 명령을 실행합니다.
```
CREATE EXTERNAL SCHEMA spectrum
FROM DATA CATALOG
DATABASE 'spectrumdb'
IAM_ROLE 'INSERT-YOUR-REDSHIFT-ROLE'
CREATE EXTERNAL DATABASE IF NOT EXISTS
```

_pgweb_에는 아무런 정보가 표시되지 않고 "No records found"라는 메시지만 수신하게 됩니다.

_Schema "spectrum" already exists_라는 메시지를 수신하면, 다음 단계로 진행하십시오.

이제 _spectrum 스키마_에 저장될 _외부 테이블_을 생성합니다.

_pgweb_에서 이 명령을 실행하여 외부 테이블을 생성합니다.

CREATE EXTERNAL TABLE spectrum.sales(
    salesid INTEGER,
    listid INTEGER,
    sellerid INTEGER,
    buyerid INTEGER,
    eventid INTEGER,
    dateid SMALLINT,
    qtysold SMALLINT,
    pricepaid DECIMAL(8,2),
    commission DECIMAL(8,2),
    saletime TIMESTAMP
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION 's3://yjeong-sampledata-uswest2/tickit/spectrum/sales/'
TABLE PROPERTIES ('numRows'='172000')

pgweb_에는 아무런 정보가 표시되지 않습니다. 외부 테이블은 테이블의 _pgweb 목록에 표시되지 않기 때문입니다.

이 문이 Amazon S3에 있는 디렉터리를 가리키는 테이블 정의를 생성했습니다. 디렉터리에는 172,456개의 행이 있는 11MB 텍스트 파일 1개가 포함되어 있습니다. 다음은 파일 콘텐츠 샘플입니다.

2   4   8117    11498   4337    1983    2   76.00   11.40   2008-06-06 05:00:16
6   10  24858   24888   3375    2023    2   394.00  59.10   2008-07-16 11:59:24
7   10  24858   7952    3375    2003    4   788.00  118.20  2008-06-26 00:56:06
8   10  24858   19715   3375    2017    1   197.00  29.55   2008-07-10 02:12:36

각 줄에는 수량, 가격 및 판매 날짜와 같은 판매 정보가 있습니다.

과제 5: Amazon S3에 저장된 데이터를 쿼리

이 과제에서는 외부 테이블에 대해 쿼리를 실행합니다. 이 쿼리는 Redshift Spectrum을 사용하여 Amazon S3에서 바로 데이터를 처리합니다.

이 명령을 실행하여 S3에 저장된 행의 수를 쿼리합니다.
```
SELECT COUNT(*) FROM spectrum.sales
```

출력값은 파일에 172,456개의 레코드가 있음을 보여줍니다.

이 명령을 실행하여 외부 테이블에 저장된 데이터 샘플을 확인합니다.
```
SELECT * FROM spectrum.sales LIMIT 10
```

S3에 저장된 탭으로 분리된 데이터가 일반 Redshift 테이블과 정확히 동일하게 표시되는 것을 확인할 수 있습니다. Spectrum은 S3에서 데이터를 읽지만 마치 Redshift에서 직접 읽는 것처럼 표시합니다.

또한, 쿼리는 합계 계산과 같은 일반 SQL 문을 포함할 수 있습니다.

이 명령을 실행하여 해당 일의 매출을 계산합니다.

SELECT SUM(pricepaid)
FROM spectrum.sales
WHERE saletime::date = '2008-06-26'

Amazon Redshift Spectrum은 임시 Amazon Redshift 테이블로 데이터를 로드할 필요 없이 Amazon S3에 저장된 데이터에 직접 쿼리를 실행합니다.

또한, S3에 저장된 데이터와 Amazon Redshift에 저장된 데이터를 조인할 수 있습니다. 이를 보여주기 위해 _event_라는 일반 Redshift 테이블을 생성하고 이 테이블로 데이터를 로드합니다.

이 명령을 실행하여 일반 Redshift 테이블을 생성합니다.

CREATE TABLE event(
    eventid INTEGER NOT NULL DISTKEY,
    venueid SMALLINT NOT NULL,
    catid   SMALLINT NOT NULL,
    dateid  SMALLINT NOT NULL SORTKEY,
    eventname VARCHAR(200),
    starttime TIMESTAMP
)

event 테이블이 페이지 왼쪽의 테이블 목록에 표시됩니다.

INSERT\-YOUR\-REDSHIFT\-ROLE을 앞선 과제에서 생성한 RedshiftRole 의 ARN 값으로 대체하고 pgweb_에서 이 명령을 실행하여 데이터를 _events 테이블로 로드합니다.
```
COPY event
FROM 's3://yjeong-sampledata-uswest2/tickit/allevents_pipe.txt'
IAM_ROLE 'INSERT-YOUR-REDSHIFT-ROLE'
DELIMITER '|'
TIMEFORMAT 'YYYY-MM-DD HH:MI:SS'
REGION 'us-west-2'
```

로드하는 데 30초 정도 걸립니다.

이 명령을 실행하여 event 데이터의 샘플을 확인합니다.
```
SELECT * FROM event LIMIT 10
```

이제 이 새로운 event 테이블의 데이터(Amazon Redshift 클러스터에 저장된)와 외부 sales 테이블의 데이터(Amazon S3에 저장된)를 조인하는 쿼리를 실행할 수 있습니다.

이 명령을 통해 로컬 event 테이블과 외부 sales 테이블을 조인하여 상위 10개 이벤트의 총 매출을 확인합니다.

SELECT TOP 10
  spectrum.sales.eventid,
  SUM(spectrum.sales.pricepaid)
FROM spectrum.sales, event
WHERE spectrum.sales.eventid = event.eventid
  AND spectrum.sales.pricepaid > 30
GROUP BY spectrum.sales.eventid
ORDER BY 2 DESC

이 쿼리는 가격이 30 USD가 넘는 이벤트별로(Redshift에 저장된) 그룹화된 총 매출(S3에 저장된)을 나열합니다.

이 명령을 실행하여 위의 쿼리에 대한 _쿼리 플랜_을 봅니다.

EXPLAIN
SELECT TOP 10
    spectrum.sales.eventid,
    sum(spectrum.sales.pricepaid)
FROM spectrum.sales, event
WHERE spectrum.sales.eventid = event.eventid
  AND spectrum.sales.pricepaid > 30
GROUP BY spectrum.sales.eventid
ORDER BY 2 DESC

이 쿼리 플랜은 Redshift가 어떻게 해당 쿼리를 실행할지 보여줍니다. Amazon S3에 있는 데이터에 대해 S3 Seq Scan, S3 HashAggregate 및 S3 Query Scan 단계가 실행됩니다.

과제 6: 파티션된 데이터 사용

외부 테이블은 디렉터리로 _사전에 파티션_될 수 있으며, 각 디렉터리는 데이터 하위 집합을 포함합니다.

데이터를 파티션할 때 _파티션 키_를 필터링하여 Redshift Spectrum가 스캔하는 데이터 양을 제한할 수 있습니다.

시간에 따라 데이터를 파티션하는 것이 일반적입니다. 예를 들어 연, 월, 날짜 및 시간에 따라 파티션할 수 있습니다. 데이터가 여러 소스에서 수신되는 경우, 데이터 소스 식별자와 날짜로 파티션할 수 있습니다.

다음은 분할된 데이터를 보여주는 디렉터리 목록으로, 디렉터리에 월별로 파티션된 S3 파일 집합을 표시합니다.

$ aws s3 ls s3://yjeong-sampledata-uswest2/tickit/spectrum/sales_partition/

PRE saledate=2008-01/
PRE saledate=2008-02/
PRE saledate=2008-03/
PRE saledate=2008-04/
PRE saledate=2008-05/
PRE saledate=2008-06/
PRE saledate=2008-07/
PRE saledate=2008-08/
PRE saledate=2008-09/
PRE saledate=2008-10/
PRE saledate=2008-11/
PRE saledate=2008-12/

이제 이 데이터를 사용하는 외부 테이블을 정의합니다.

이 명령을 실행하여 파티션된 데이터에 따라 새로운 sales\_partitioned 테이블을 정의합니다.

CREATE EXTERNAL TABLE spectrum.sales_partitioned(
    salesid INTEGER,
    listid INTEGER,
    sellerid INTEGER,
    buyerid INTEGER,
    eventid INTEGER,
    dateid SMALLINT,
    qtysold SMALLINT,
    pricepaid DECIMAL(8,2),
    commission DECIMAL(8,2),
    saletime TIMESTAMP
)
PARTITIONED BY (saledate DATE)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 's3://yjeong-sampledata-uswest2/tickit/spectrum/sales_partition/'
TABLE PROPERTIES ('numRows'='172000')

(이 쿼리를 실행하면 화면에 응답이 표시되지는 않지만, 테이블 정의가 생성됩니다.)

saledate 필드에 따라 테이블이 파티션됨을 Redshift Spectrum에 알려주는 문이 추가되었습니다.

그런 다음 Redshift Spectrum은 기존 파티션에 대한 정보를 받아야 어떤 디렉터리를 사용할지 알 수 있습니다.

이 명령 블록을 실행하여 파티션을 추가합니다.

ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-01-01')
LOCATION 's3://yjeong-sampledata-uswest2/tickit/spectrum/sales_partition/saledate=2008-01/';
ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-02-01')
LOCATION 's3://yjeong-sampledata-uswest2/tickit/spectrum/sales_partition/saledate=2008-02/';
ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-03-01')
LOCATION 's3://yjeong-sampledata-uswest2/tickit/spectrum/sales_partition/saledate=2008-03/';
ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-04-01')
LOCATION 's3://yjeong-sampledata-uswest2/tickit/spectrum/sales_partition/saledate=2008-04/';
ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-05-01')
LOCATION 's3://yjeong-sampledata-uswest2/tickit/spectrum/sales_partition/saledate=2008-05/';
ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-06-01')
LOCATION 's3://yjeong-sampledata-uswest2/tickit/spectrum/sales_partition/saledate=2008-06/';
ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-07-01')
LOCATION 's3://yjeong-sampledata-uswest2/tickit/spectrum/sales_partition/saledate=2008-07/';
ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-08-01')
LOCATION 's3://yjeong-sampledata-uswest2/tickit/spectrum/sales_partition/saledate=2008-08/';
ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-09-01')
LOCATION 's3://yjeong-sampledata-uswest2/tickit/spectrum/sales_partition/saledate=2008-09/';
ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-10-01')
LOCATION 's3://yjeong-sampledata-uswest2/tickit/spectrum/sales_partition/saledate=2008-10/';
ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-11-01')
LOCATION 's3://yjeong-sampledata-uswest2/tickit/spectrum/sales_partition/saledate=2008-11/';
ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-12-01')
LOCATION 's3://yjeong-sampledata-uswest2/tickit/spectrum/sales_partition/saledate=2008-12/';

이제 특정 _saledate_를 사용하는 모든 쿼리에서 해당 날짜와 관련된 디렉터리만 스캔합니다.

비교를 위해 2개의 서로 다른 데이터 소스에 쿼리를 실행합니다.

원래 sales 테이블에 이 명령을 실행하고 실행에 걸리는 시간을 적어둡니다.

SELECT TOP 10
    spectrum.sales.eventid,
    SUM(pricepaid)
FROM spectrum.sales, event
WHERE spectrum.sales.eventid = event.eventid
  AND pricepaid > 30
  AND date_trunc('month', saletime) = '2008-12-01'
GROUP BY spectrum.sales.eventid
ORDER BY 2 DESC

파티션된 데이터에 이 명령을 실행하고 실행에 걸리는 시간을 적어둡니다.

SELECT TOP 10
    spectrum.sales_partitioned.eventid,
    SUM(pricepaid)
FROM spectrum.sales_partitioned, event
WHERE spectrum.sales_partitioned.eventid = event.eventid
  AND pricepaid > 30
  AND saledate = '2008-12-01'
GROUP BY spectrum.sales_partitioned.eventid
ORDER BY 2 DESC

두 번째 쿼리가 더 빠르게 실행됩니다. Amazon S3에서 읽는 데이터가 더 적기 때문입니다. 데이터 볼륨이 더 큰 경우에 그 차이가 더 분명해집니다. 또한, Amazon S3에서 읽는 데이터 양에 따라 Redshift Spectrum에 대한 요금이 부과되므로, 쿼리 실행 비용도 줄어듭니다.

파티션에 대한 정보는 SVV_EXTERNAL_PARTITIONS 시스템 뷰에서 확인할 수 있습니다.

이 명령을 실행하여 sales_partitioned 테이블에 대한 파티션을 봅니다.
```
SELECT *
FROM SVV_EXTERNAL_PARTITIONS
WHERE tablename = 'sales_partitioned'
```
exernal tables_에 대한 정보는 _SVV_EXTERNAL_COLUMNS 시스템 뷰에서 확인할 수 있습니다.
이 명령을 실행하여 sales_partitioned 테이블에 정의된 열을 봅니다.
```
SELECT *
FROM SVV_EXTERNAL_COLUMNS
WHERE tablename = 'sales_partitioned'
```

시간 여유가 있다면, 이 실습에 주어진 SQL 문을 검토하여 외부 테이블이 어떻게 생성되고 사용되는지 파악합니다.

실습 완료

축하합니다! 이번 실습을 완료했습니다. 비용 발생을 최소화 하기 위하여 실습 환경을 정리하십시오.

Name		Name	Last commit message	Last commit date
Latest commit History 13 Commits
images		images
README.adoc		README.adoc

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Repository files navigation

AWS 기반 데이터 웨어하우징 - Amazon Redshift

과제 1: Amazon Redshift client 구성

과제 2: Amazon Redshift 생성

과제 3: Amazon Redshift에 연결

과제 4: 외부 테이블 생성

과제 5: Amazon S3에 저장된 데이터를 쿼리

과제 6: 파티션된 데이터 사용

실습 완료

About

Uh oh!

Releases

Packages

sola-scriptura-zz/redshift-workshop

Folders and files

Latest commit

History

Repository files navigation

AWS 기반 데이터 웨어하우징 - Amazon Redshift

과제 1: Amazon Redshift client 구성

과제 2: Amazon Redshift 생성

과제 3: Amazon Redshift에 연결

과제 4: 외부 테이블 생성

과제 5: Amazon S3에 저장된 데이터를 쿼리

과제 6: 파티션된 데이터 사용

실습 완료

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Packages