빅 데이터 분석 워크샵 > 데이터 웨어하우스 > 파티션된 데이터 사용

파티션된 데이터 사용

외부 테이블은 디렉터리로 사전에 파티션될 수 있으며, 각 디렉터리는 데이터 하위 집합을 포함합니다. 데이터를 파티션할 때 파티션 키를 필터링하여 Redshift Spectrum이 스캔하는 데이터의 양을 제한할 수 있습니다. 시간에 따라 데이터를 파티션하는 것이 일반적입니다. 예를 들어, 년, 월, 일 및 시간에 따라 파티션할 수 있습니다. 데이터가 여러 소스에서 수신되는 경우, 데이터 소스 식별자와 날짜로 파티션할 수 있습니다.

다음은 분할된 데이터를 보여주는 디렉터리 목록으로, 디렉터리에 월별로 파티션된 S3 파일 집합을 표시합니다. (참고: AWS Cli가 설치된 로컬 머신에서 확인 가능합니다.)

$ aws s3 ls s3://awssampledbuswest2/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/

이제 이 데이터를 사용하는 외부 테이블을 정의합니다. 다음 명령을 실행하여 파티션된 데이터에 따라 새로운 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://awssampledbuswest2/tickit/spectrum/sales_partition/'
TABLE PROPERTIES ('numRows'='172000')

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

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

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

다음 명령을 실행하여 파티션을 추가합니다. (실행할 때는 한 개씩 블록을 잡아서 query를 수행해야 정상 동작합니다.)

ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-01-01')
LOCATION 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01/';

ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-02-01')
LOCATION 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02/';

ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-03-01')
LOCATION 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-03/';

ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-04-01')
LOCATION 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-04/';

ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-05-01')
LOCATION 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-05/';

ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-06-01')
LOCATION 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-06/';

ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-07-01')
LOCATION 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-07/';

ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-08-01')
LOCATION 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-08/';

ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-09-01')
LOCATION 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-09/';

ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-10-01')
LOCATION 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-10/';

ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-11-01')
LOCATION 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-11/';

ALTER TABLE spectrum.sales_partitioned
ADD PARTITION (saledate='2008-12-01')
LOCATION 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-12/';

이제 특정 sales date를 사용하는 모든 쿼리에서 해당 날짜와 관련된 디렉터리만 스캔합니다. 비교를 위해 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'

External tables에 대한 정보는 _SVV_EXTERNAL_COLOUMS 시스템 뷰에서 확인할 수 있습니다.
다음의 명령을 실행하여 sales_partitioned 테이블에 정의된 열을 확인합니다.

SELECT *
FROM SVV_EXTERNAL_COLUMNS
WHERE tablename = 'sales_partitioned'