이것은 일련의 게시물 중 다섯 번째입니다. 스키마 변환을 자동화하는 수십 가지의 새로운 기능을 공유하게 되어 기쁩니다. 기존 스크립트, 보고서 및 애플리케이션에 대한 투자를 보존합니다. 쿼리 성능을 가속화합니다. 레거시 데이터 웨어하우스에서 아마존 레드 시프트.

Amazon Redshift는 최고의 클라우드 데이터 웨어하우스입니다. 데이터에서 새로운 통찰력을 쉽게 얻을 수 있는 데이터 웨어하우스는 없습니다. Amazon Redshift를 사용하면 표준 SQL을 사용하여 데이터 웨어하우스, 운영 데이터 스토어 및 데이터 레이크에서 엑사바이트 규모의 데이터를 쿼리할 수 있습니다. 다음과 같은 다른 AWS 서비스를 통합할 수도 있습니다. 아마존 EMR, 아마존 아테나, 아마존 세이지 메이커, AWS 접착제, AWS Lake 형성및 아마존 키네 시스 AWS 클라우드의 모든 분석 기능을 사용합니다.

지금까지 데이터 웨어하우스를 AWS로 마이그레이션하는 것은 상당한 수작업을 수반하는 복잡한 작업이었습니다. 구문 차이를 수동으로 수정하고 독점 기능을 대체하기 위해 코드를 삽입하고 새 플랫폼에서 쿼리 및 보고서의 성능을 수동으로 조정해야 합니다.

레거시 워크로드는 Amazon Redshift와 같은 최신 데이터베이스에서 직접 지원하지 않는 비 ANSI, 독점 기능에 의존할 수 있습니다. 예를 들어, 많은 Teradata 애플리케이션은 전체 행 고유성을 적용하는 SET 테이블을 사용합니다. 테이블에 모든 속성 값이 동일한 행이 두 개 있을 수는 없습니다.

Amazon Redshift 사용자인 경우 SET 의미 체계를 구현하고 싶지만 기본 데이터베이스 기능에 의존할 수 없습니다. 이 게시물의 디자인 패턴을 사용하여 SQL 코드에서 SET 의미론을 에뮬레이트할 수 있습니다. 또는 워크로드를 Amazon Redshift로 마이그레이션하는 경우 다음을 사용할 수 있습니다. AWS 스키마 변환 도구 (AWS SCT)를 사용하여 코드 변환의 일부로 디자인 패턴을 자동으로 적용합니다.

이 게시물에서는 SQL 디자인 패턴을 설명하고 성능을 분석하며 AWS SCT가 데이터 웨어하우스 마이그레이션의 일부로 이를 자동화할 수 있는 방법을 보여줍니다. Teradata에서 SET 테이블이 어떻게 작동하는지 이해하는 것으로 시작하겠습니다.

테라데이타 SET 테이블

언뜻 보기에 SET 테이블은 모든 열에 정의된 기본 키가 있는 테이블과 유사하게 보일 수 있습니다. 그러나 기존 기본 키와 몇 가지 중요한 의미론적 차이가 있습니다. Teradata에서 다음 테이블 정의를 고려하십시오.

CREATE SET TABLE testschema.sales_by_month ( sales_dt DATE
, amount DECIMAL(8,2)
);

다음과 같이 XNUMX개의 데이터 행으로 테이블을 채웁니다.

select * from testschema.sales_by_month order by sales_dt; *** Query completed. 4 rows found. 2 columns returned. *** Total elapsed time was 1 second. sales_dt amount
-------- ----------
22/01/01 100.00
22/01/02 200.00
22/01/03 300.00
22/01/04 400.00

테이블에 UNIQUE PRIMARY INDEX(기본 키와 유사)를 정의하지 않았습니다. 이제 기존 행과 중복되는 테이블에 새 행을 삽입하려고 하면 삽입이 실패합니다.

INSERT IGNORE INTO testschema.sales_by_month values (20220101, 100); *** Failure 2802 Duplicate row error in testschema.sales_by_month. Statement# 1, Info =0 *** Total elapsed time was 1 second.

마찬가지로 기존 행을 업데이트하여 다른 행과 중복되도록 하면 업데이트가 실패합니다.

UPDATE testschema.sales_by_month SET sales_dt = 20220101, amount = 100
WHERE sales_dt = 20220104 and amount = 400; *** Failure 2802 Duplicate row error in testschema.sales_by_month. Statement# 1, Info =0 *** Total elapsed time was 1 second.

즉, 단순 INSERT-VALUE 및 UPDATE 문이 Teradata SET 테이블에 중복 행을 도입하면 실패합니다.

이 규칙에는 주목할만한 예외가 있습니다. 대상 테이블과 속성이 동일한 다음 스테이징 테이블을 고려하십시오.

CREATE MULTISET TABLE testschema.sales_by_month_stg ( sales_dt DATE
, amount DECIMAL(8,2)
);

준비 테이블은 MULTISET 테이블이며 중복 행을 허용합니다. 준비 테이블에 XNUMX개의 행을 채웁니다. 첫 번째 행은 대상 테이블에 있는 행의 복제본입니다. 두 번째 및 세 번째 행은 서로 중복되지만 대상 행은 중복되지 않습니다.

select * from testschema.sales_by_month_stg; *** Query completed. 3 rows found. 2 columns returned. *** Total elapsed time was 1 second. sales_dt amount
-------- ----------
22/01/01 100.00
22/01/05 500.00
22/01/05 500.00

이제 스테이징 데이터를 대상 테이블(SET 테이블)에 성공적으로 삽입합니다.

INSERT IGNORE INTO testschema.sales_by_month (sales_dt, amount)
SELECT sales_dt, amount FROM testschema.sales_by_month_stg; *** Insert completed. One row added. *** Total elapsed time was 1 second.

대상 테이블을 살펴보면 (2022-01-05, 500)에 대한 단일 행이 삽입되었고 (2022-01-01, 100)에 대한 중복 행이 삭제되었음을 알 수 있습니다. 기본적으로 Teradata는 INSERT-SELECT 문을 수행할 때 중복 행을 자동으로 삭제합니다. 여기에는 준비 테이블에 있는 중복 항목과 준비 테이블과 대상 테이블 간에 공유되는 중복 항목이 포함됩니다.

select * from testschema.sales_by_month order by sales_dt; *** Query completed. 6 rows found. 2 columns returned. *** Total elapsed time was 1 second. sales_dt amount
-------- ----------
22/01/01 100.00
22/01/02 200.00
22/01/03 300.00
22/01/03 200.00
22/01/04 400.00
22/01/05 500.00

기본적으로 SET 테이블은 실행 중인 작업 유형에 따라 다르게 동작합니다. INSERT-VALUE 또는 UPDATE 작업은 대상에 중복 행을 도입하면 실패합니다. INSERT-SELECT 작업은 준비 테이블에 중복 행이 포함되어 있거나 중복 행이 준비 테이블과 테이블 테이블 간에 공유되는 경우 실패하지 않습니다.

이 게시물에서는 INSERT-VALUE 또는 UPDATE 문을 변환하는 방법에 대해 자세히 설명하지 않습니다. 이러한 문은 일반적으로 하나 또는 몇 개의 행을 포함하며 INSERT-SELECT 문보다 성능 면에서 덜 영향을 미칩니다. INSERT-VALUE 또는 UPDATE 문의 경우 생성 중인 행을 구체화하고 해당 세트를 대상 테이블에 조인하여 중복 여부를 확인할 수 있습니다.

삽입-선택

이 게시물의 나머지 부분에서는 INSERT-SELECT 문을 주의 깊게 분석합니다. 고객은 INSERT-SELECT 작업이 SET 테이블에 대한 INSERT 작업 부하의 최대 78%를 구성할 수 있다고 말했습니다. 우리는 다음 형식의 진술에 관심이 있습니다.

INSERT into <target table> SELECT * FROM <staging table>

스테이징 테이블의 스키마는 열 단위로 대상 테이블과 동일합니다. 앞서 언급했듯이 중복 행은 두 가지 다른 상황에서 나타날 수 있습니다.

• 준비 테이블이 고유하지 않습니다. 즉, 준비 데이터에 두 개 이상의 전체 행 중복이 있습니다.
• 준비 테이블에 행 x가 있고 대상 테이블에 동일한 행 x가 있습니다.

Amazon Redshift는 다중 집합 테이블 의미 체계를 지원하기 때문에 스테이징 테이블에 중복이 포함될 수 있습니다(우리가 나열한 첫 번째 상황). 따라서 자동화는 두 경우 모두를 해결해야 합니다. 어느 쪽이든 Amazon Redshift 테이블에 중복을 도입할 수 있기 때문입니다.

이 분석을 기반으로 다음 알고리즘을 구현했습니다.

• 마이너스 – SQL MINUS를 사용하여 전체 집합 논리 중복 제거를 구현합니다. MINUS는 준비 테이블이 고유하지 않은 경우와 준비 테이블과 대상 테이블의 교차점이 비어 있지 않은 경우를 포함하여 모든 경우에 작동합니다. MINUS는 또한 NULL 값이 NULL 대 NULL 비교를 극복하기 위해 특별한 비교 논리가 필요하지 않다는 장점이 있습니다. MINUS의 구문은 다음과 같습니다.

  INSERT IGNORE INTO <target table> (<column list>)
  SELECT <column list> FROM <staging table> MINUS
  SELECT <column list> FROM <target table>;

• 마이너스-최소-최대 – 이것은 스테이지 테이블의 값을 기반으로 대상 테이블 스캔을 제한하는 필터를 통합하는 MINUS에 대한 최적화입니다. 최소/최대 필터를 사용하면 쿼리 엔진이 테이블 스캔 중에 많은 수의 블록을 건너뛸 수 있습니다. 보다 정렬 키 작업 자세한 내용은.

  INSERT IGNORE INTO <target table>(<column list>)
  SELECT <column list> FROM <staging table> MINUS
  SELECT <column list> FROM <target table>
  WHERE <target table>.<sort key> >= (SELECT MIN(<sort key>) FROM <staging table>) AND <target table>).<sort key> <= (SELECT MAX(<sort key>) FROM <staging table>)
  );

다른 알고리즘도 고려했지만 사용하지 않는 것이 좋습니다. 예를 들어 GROUP BY를 수행하여 준비 테이블에서 중복을 제거할 수 있지만 MINUS 연산자를 사용하는 경우 이 단계가 필요하지 않습니다. 또한 왼쪽(또는 오른쪽) 외부 조인을 수행하여 스테이징 테이블과 대상 테이블 간에 공유 중복을 찾을 수 있지만 NULL = NULL 조건을 설명하려면 추가 논리가 필요합니다.

퍼포먼스

Amazon Redshift에서 MINUS 및 MINUS-MIN-MAX 알고리즘을 테스트했습니다. 두 개의 Amazon Redshift 클러스터에서 알고리즘을 실행했습니다. 첫 번째 구성은 6개의 ra3.4xlarge 노드로 구성되었습니다. 두 번째 노드는 12개의 ra3.4xlarge 노드로 구성되었습니다. 각 노드에는 12개의 CPU와 96GB의 메모리가 포함되어 있습니다.

데이터 이동을 최소화하기 위해 동일한 정렬 및 배포 키를 사용하여 스테이지 및 대상 테이블을 생성했습니다. 동일한 대상 데이터 세트를 두 클러스터에 로드했습니다. 대상 데이터 세트는 1.1억 개의 데이터 행으로 구성되었습니다. 그런 다음 20천만 행 증분으로 200천만에서 20억 행 범위의 스테이징 데이터 세트를 만들었습니다.

다음 그래프는 결과를 보여줍니다.

테스트 데이터는 인위적으로 생성되었으며 배포 키 값에 약간의 왜곡이 있었습니다. 이것은 성능의 선형성에서 약간의 편차로 나타납니다.

그러나 기본 MINUS 알고리즘보다 MINUS-MIN-MAX 알고리즘이 제공되는 성능 향상을 관찰할 수 있습니다(주황색 선 또는 파란색 선을 자체적으로 비교). Amazon Redshift에서 SET 테이블을 구현하는 경우 MINUS-MIN-MAX를 사용하는 것이 좋습니다. 이 알고리즘은 간단하고 가독성이 좋은 코드와 우수한 성능의 행복한 수렴을 제공하기 때문입니다.

자동화

모든 Amazon Redshift 테이블은 중복 행을 허용합니다. 즉, 기본적으로 MULTISET 테이블입니다. Teradata 워크로드를 Amazon Redshift에서 실행하도록 변환하는 경우 데이터베이스 외부에서 SET 의미 체계를 적용해야 합니다.

AWS SCT가 SET 테이블에 대해 작동하는 SQL 코드를 자동으로 변환한다는 사실을 알려드리게 되어 기쁩니다. AWS SCT는 위에서 설명한 재작성 패턴을 통합하기 위해 SET 테이블을 로드하는 INSERT-SELECT를 재작성합니다.

이것이 어떻게 작동하는지 봅시다. Teradata에 다음과 같은 대상 테이블 정의가 있다고 가정합니다.

CREATE SET TABLE testschema.fact ( id bigint NOT NULL
, se_sporting_event_id INTEGER NOT NULL
, se_sport_type_name VARCHAR(15) NOT NULL
, se_home_team_id INTEGER NOT NULL
, se_away_team_id INTEGER NOT NULL
, se_location_id INTEGER NOT NULL
, se_start_date_time DATE NOT NULL
, se_sold_out INTEGER DEFAULT 0 NOT NULL
, stype_sport_type_name varchar(15) NOT NULL
, stype_short_name varchar(10) NOT NULL
, stype_long_name varchar(60) NOT NULL
, stype_description varchar(120)
, sd_sport_type_name varchar(15) NOT NULL
, sd_sport_league_short_name varchar(10) NOT NULL
, sd_short_name varchar(10) NOT NULL
, sd_long_name varchar(60)
, sd_description varchar(120)
, sht_id INTEGER NOT NULL
, sht_name varchar(30) NOT NULL
, sht_abbreviated_name varchar(10)
, sht_home_field_id INTEGER , sht_sport_type_name varchar(15) NOT NULL
, sht_sport_league_short_name varchar(10) NOT NULL
, sht_sport_division_short_name varchar(10)
, sat_id INTEGER NOT NULL
, sat_name varchar(30) NOT NULL
, sat_abbreviated_name varchar(10)
, sat_home_field_id INTEGER , sat_sport_type_name varchar(15) NOT NULL
, sat_sport_league_short_name varchar(10) NOT NULL
, sat_sport_division_short_name varchar(10)
, sl_id INTEGER NOT NULL
, sl_name varchar(60) NOT NULL
, sl_city varchar(60) NOT NULL
, sl_seating_capacity INTEGER
, sl_levels INTEGER
, sl_sections INTEGER
, seat_sport_location_id INTEGER
, seat_seat_level INTEGER
, seat_seat_section VARCHAR(15)
, seat_seat_row VARCHAR(10)
, seat_seat VARCHAR(10)
, seat_seat_type VARCHAR(15)
, pb_id INTEGER NOT NULL
, pb_full_name varchar(60) NOT NULL
, pb_last_name varchar(30)
, pb_first_name varchar(30)
, ps_id INTEGER NOT NULL
, ps_full_name varchar(60) NOT NULL
, ps_last_name varchar(30)
, ps_first_name varchar(30)
)
PRIMARY INDEX(id)
;

스테이지 테이블은 Teradata에서 MULTISET 테이블로 생성된다는 점을 제외하고 대상 테이블과 동일합니다.

다음으로 스테이지 테이블에서 팩트 테이블을 로드하는 프로시저를 만듭니다. 프로시저에는 단일 INSERT-SELECT문이 포함됩니다.

REPLACE PROCEDURE testschema.insert_select() BEGIN INSERT IGNORE INTO testschema.test_fact SELECT * FROM testschema.test_stg;
END;

이제 AWS SCT를 사용하여 Teradata 저장 프로시저를 Amazon Redshift로 변환합니다. 먼저 소스 데이터베이스 트리에서 저장 프로시저를 선택한 다음 마우스 오른쪽 버튼을 클릭하고 스키마 변환.

AWS SCT는 MINUS-MIN-MAX 재작성 패턴을 사용하여 저장 프로시저(및 내장 INSERT-SELECT)를 변환합니다.

그리고 그게 다야! 현재 AWS SCT는 INSERT-SELECT에 대해서만 재작성을 수행합니다. 이러한 명령문은 ETL 워크로드에서 많이 사용되며 성능에 가장 큰 영향을 미치기 때문입니다. 우리가 사용한 예제는 저장 프로시저에 포함되었지만 BTEQ 스크립트, 매크로 또는 애플리케이션 프로그램에 있는 경우 AWS SCT를 사용하여 동일한 명령문을 변환할 수도 있습니다. 다운로드 최신 버전의 AWS SCT 시도해보십시오!

결론

이 게시물에서는 Amazon Redshift에서 SET 테이블 의미 체계를 구현하는 방법을 보여주었습니다. 설명된 디자인 패턴을 사용하여 SET 의미 체계가 필요한 새 애플리케이션을 개발할 수 있습니다. 또는 기존 Teradata 워크로드를 변환하는 경우 AWS SCT를 사용하여 SET 테이블 의미 체계를 유지하도록 INSERT-SELECT 문을 자동으로 변환할 수 있습니다.

곧 이 시리즈의 다음 편으로 돌아오겠습니다. Teradata에서 Amazon Redshift로의 마이그레이션 자동화에 대한 자세한 내용은 다시 확인하십시오. 그 동안에 대해 자세히 알아볼 수 있습니다. 아마존 레드 시프트 과 AWS SCT. 행복한 이주!

저자에 관하여

마이클 수 AWS Database Migration Service 팀의 수석 데이터베이스 엔지니어입니다. 그는 고객이 데이터베이스 워크로드를 AWS 클라우드로 마이그레이션하는 데 도움이 되는 제품과 서비스를 구축합니다.

홍포박사, 현대 데이터 아키텍처 GSP(Global Specialty Practice), AWS Professional Services의 수석 데이터 설계자입니다. 그는 고객이 혁신적인 솔루션을 채택하고 대규모 MPP 데이터 웨어하우스에서 AWS 최신 데이터 아키텍처로 마이그레이션하도록 돕는 데 열정을 가지고 있습니다.

• 코인스마트. 유럽 ​​최고의 비트코인 ​​및 암호화폐 거래소.
• 플라토 블록체인. Web3 메타버스 인텔리전스. 지식 증폭. 무료 액세스.
• 크립토호크. 알트코인 레이더. 무료 시험판.
• 출처: https://aws.amazon.com/blogs/big-data/part-5-accelerate-your-data-warehouse-migration-to-amazon-redshift/