데이터 웨어하우스 테이블 만들기
이제 관계형 데이터 웨어하우스 스키마에 대한 기본 아키텍처 원칙을 이해했으므로 데이터 웨어하우스를 만드는 방법을 살펴보겠습니다.
전용 SQL 풀 만들기
Azure Synapse Analytics에서 관계형 데이터 웨어하우스를 만들려면 전용 SQL 풀을 만들어야 합니다. 기존 Azure Synapse Analytics 작업 영역에서 이 작업을 수행하는 가장 간단한 방법은 다음과 같이 Azure Synapse Studio의 관리 페이지를 사용하는 것입니다.
전용 SQL 풀을 프로비전할 때 다음 구성 설정을 지정할 수 있습니다.
- 전용 SQL 풀의 고유한 이름.
- DW100c에서 DW30000c까지의 범위일 수 있고, 풀 실행 시 시간당 비용을 결정하는 SQL 풀의 성능 수준.
- 빈 풀로 시작할지, 백업에서 기존 데이터베이스를 복원할지 여부.
- 데이터베이스에 대한 정렬 순서 및 문자열 비교 규칙을 결정하는 SQL 풀의 데이터 정렬. (만든 후에는 데이터 정렬을 변경할 수 없습니다).
전용 SQL 풀을 만든 후 Synapse Studio의 관리 페이지에서 실행 상태를 제어할 수 있으며, 필요 없을 때는 일시 중지하여 불필요한 비용을 방지할 수 있습니다.
풀이 실행 중일 때 데이터 페이지에서 풀을 탐색하고 SQL 스크립트를 만들어 풀에서 실행할 수 있습니다.
테이블 만들기의 고려 사항
전용 SQL 풀에서 테이블을 만들려면 CREATE TABLE (또는 때로 CREATE EXTERNAL TABLE) Transact-SQL 문을 사용합니다 . 문에 사용되는 특정 옵션은 만들고 있는 테이블 유형에 따라 달라지며 다음을 포함할 수 있습니다.
- 팩트 테이블
- 차원 테이블
- 준비 테이블
참고
데이터 웨어하우스는 앞에서 설명한 대로 팩트 및 차원 테이블로 구성됩니다. 준비 테이블은 원본 시스템에서 데이터를 수집하기 위한 데이터 웨어하우징 로드 프로세스의 일부로 사용되는 경우가 많습니다.
중소 규모의 데이터 세트용 별모양 스키마 모델을 설계할 때는 Azure SQL 같은 기본 데이터베이스를 사용할 수 있습니다. 규모가 더 큰 데이터 세트는 SQL Server 대신 Azure Synapse Analytics에서 데이터 웨어하우스를 구현하는 것이 유리할 수 있습니다. Synapse Analytics에서 테이블을 만들 때 몇 가지 주요 차이점을 아는 것이 중요합니다.
데이터 무결성 제약 조건
Synapse Analytics의 전용 SQL 풀은 SQL Server 같은 다른 관계형 데이터베이스 시스템에서 볼 수 있는 외래 키 및 고유 제약 조건을 지원하지 않습니다. 즉, 데이터를 로드하는 데 사용되는 작업은 키에 대한 고유성 및 참조 무결성을 유지해야 하며, 이를 위해 데이터베이스의 테이블 정의에 의존하지 않아야 합니다.
팁
Azure Synapse Analytics 전용 SQL 풀의 제약 조건에 대한 자세한 내용은 Azure Synapse Analytics의 전용 SQL 풀을 사용하는 기본 키, 외래 키 및 고유 키를 참조하세요.
인덱스
Synapse Analytics 전용 SQL 풀은 SQL Server에서 볼 수 있는 클러스터형 인덱스를 지원하지만 기본 인덱스 유형은 클러스터형 columnstore입니다. 이 인덱스 형식은 일반적인 데이터 웨어하우스 스키마에서 대량의 데이터를 쿼리할 때 상당한 성능 이점을 제공하며 가능한 경우 사용해야 합니다. 그러나 일부 테이블에는 클러스터형 columnstore 인덱스에 포함될 수 없는 데이터 형식(예: VARBINARY(MAX))이 포함될 수 있으며, 이 경우에는 클러스터형 인덱스를 대신 사용할 수 있습니다.
팁
Azure Synapse Analytics 전용 SQL 풀의 인덱싱에 대한 자세한 내용은 Azure Synapse Analytics의 전용 SQL 풀 테이블에 대한 인덱스를 참조하세요.
분포
Azure Synapse Analytics 전용 SQL 풀은 대부분의 OLTP 데이터베이스 시스템에서 사용되는 SMP(대칭 다중 처리) 아키텍처와 달리 MPP(대규모 병렬 처리) 아키텍처를 사용합니다. MPP 시스템에서 테이블의 데이터는 처리를 위해 노드 풀 전반에 배포됩니다. Synapse Analytics는 다음과 같은 종류의 배포를 지원합니다.
- 해시: 지정된 열에 대해 결정적 해시 값이 계산되고 컴퓨팅 노드에 행을 할당하는 데 사용됩니다.
- 라운드 로빈: 행은 모든 컴퓨팅 노드에 균등하게 배포됩니다.
- 복제됨: 테이블의 복사본이 각 컴퓨팅 노드에 저장됩니다.
테이블 형식에 따라 선택할 테이블 배포 옵션이 결정되는 경우가 많습니다.
| 테이블 유형 | 권장 배포 옵션 |
|---|---|
| 차원 | 더 작은 테이블에 복제된 배포를 사용하여 배포된 팩트 테이블에 조인할 때 데이터 순서가 섞이는 것을 방지합니다. 테이블이 너무 커서 각 컴퓨팅 노드에 저장할 수 없는 경우 해시 배포를 사용합니다. |
| 팩트 | 클러스터형 columnstore 인덱스와 함께 해시 배포를 사용하여 팩트 테이블을 컴퓨팅 노드에 배포합니다. |
| 준비 | 준비 테이블에 라운드 로빈 배포를 사용하여 컴퓨팅 노드 간에 데이터를 균등하게 배포합니다. |
팁
Azure Synapse Analytics의 테이블에 대한 배포 전략의 자세한 내용은 Azure Synapse Analytics에서 전용 SQL 풀을 사용하여 배포 테이블을 디자인하기 위한 지침을 참조하세요.
차원 테이블 만들기
차원 테이블을 만들 때 테이블 정의에 서로게이트 및 대체 키뿐만 아니라 집계를 그룹화하는 데 사용할 차원의 특성을 위한 열이 포함되어 있는지 확인합니다. IDENTITY 열을 사용하여 증분 서로게이트 키를 자동으로 생성하는 것이 가장 쉬운 경우가 많습니다(그렇지 않으면 데이터를 로드할 때마다 고유 키를 생성해야 함). 다음 예제에서는 가상 DimCustomer 차원 테이블에 대한 CREATE TABLE 문을 보여줍니다.
CREATE TABLE dbo.DimCustomer
(
CustomerKey INT IDENTITY NOT NULL,
CustomerAlternateKey NVARCHAR(15) NULL,
CustomerName NVARCHAR(80) NOT NULL,
EmailAddress NVARCHAR(50) NULL,
Phone NVARCHAR(25) NULL,
StreetAddress NVARCHAR(100),
City NVARCHAR(20),
PostalCode NVARCHAR(10),
CountryRegion NVARCHAR(20)
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
참고
원하는 경우 특정 스키마를 테이블의 네임스페이스로 만들 수 있습니다. 이 예제에서는 기본 dbo 스키마가 사용됩니다.
차원 테이블이 서로 관련된 눈송이 스키마를 사용하려는 경우 자식 차원 테이블 정의에 부모 차원에 대한 키를 포함해야 합니다. 예를 들어 다음 SQL 코드를 사용하여 DimCustomer 테이블에서 별도의 DimGeography 차원 테이블로 지리적 주소 세부 정보를 이동할 수 있습니다.
CREATE TABLE dbo.DimGeography
(
GeographyKey INT IDENTITY NOT NULL,
GeographyAlternateKey NVARCHAR(10) NULL,
StreetAddress NVARCHAR(100),
City NVARCHAR(20),
PostalCode NVARCHAR(10),
CountryRegion NVARCHAR(20)
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
CREATE TABLE dbo.DimCustomer
(
CustomerKey INT IDENTITY NOT NULL,
CustomerAlternateKey NVARCHAR(15) NULL,
GeographyKey INT NULL,
CustomerName NVARCHAR(80) NOT NULL,
EmailAddress NVARCHAR(50) NULL,
Phone NVARCHAR(25) NULL
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
시간 차원 테이블
대부분의 데이터 웨어하우스에는 시간 간격의 여러 계층적 수준으로 데이터를 집계할 수 있는 시간 차원 테이블이 포함되어 있습니다. 예를 들어 다음 예제에서는 특정 날짜와 관련된 특성을 사용하여 DimDate 테이블을 만듭니다.
CREATE TABLE dbo.DimDate
(
DateKey INT NOT NULL,
DateAltKey DATETIME NOT NULL,
DayOfMonth INT NOT NULL,
DayOfWeek INT NOT NULL,
DayName NVARCHAR(15) NOT NULL,
MonthOfYear INT NOT NULL,
MonthName NVARCHAR(15) NOT NULL,
CalendarQuarter INT NOT NULL,
CalendarYear INT NOT NULL,
FiscalQuarter INT NOT NULL,
FiscalYear INT NOT NULL
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
팁
날짜에 대한 차원 테이블을 만들 때 일반적인 패턴은 DDMMYYYYY 또는 YYYYMMDD 형식의 숫자 날짜를 정수 서로게이트 키로 사용하고, 날짜를 DATE 또는 대체 키인 DATETIME 데이터 형식으로 사용하는 것입니다.
팩트 테이블 만들기
팩트 테이블에는 관련된 각 차원의 키와 분석하려는 특정 이벤트 또는 관찰에 대한 특성 및 숫자 측정값이 포함됩니다.
다음 코드 예제에서는 키 열(날짜, 고객, 제품 및 매장)을 통해 여러 차원과 관련된 FactSales라는 가상 팩트 테이블을 만듭니다.
CREATE TABLE dbo.FactSales
(
OrderDateKey INT NOT NULL,
CustomerKey INT NOT NULL,
ProductKey INT NOT NULL,
StoreKey INT NOT NULL,
OrderNumber NVARCHAR(10) NOT NULL,
OrderLineItem INT NOT NULL,
OrderQuantity SMALLINT NOT NULL,
UnitPrice DECIMAL NOT NULL,
Discount DECIMAL NOT NULL,
Tax DECIMAL NOT NULL,
SalesAmount DECIMAL NOT NULL
)
WITH
(
DISTRIBUTION = HASH(OrderNumber),
CLUSTERED COLUMNSTORE INDEX
);
준비 테이블 만들기
준비 테이블은 데이터가 데이터 웨어하우스에 로딜 중일 때 임시 스토리지로 사용됩니다. 일반적인 패턴은 테이블을 구조화하여 가능한 한 효율적으로 외부 원본(데이터 레이크의 파일)에서 관계형 데이터베이스로 데이터를 수집한 다음, SQL 문을 사용하여 준비 테이블에서 차원 및 팩트 테이블로 데이터를 로드하는 것입니다.
다음 코드 예제에서는 궁극적으로 차원 테이블에 로드될 제품 데이터에 대한 준비 테이블을 만듭니다.
CREATE TABLE dbo.StageProduct
(
ProductID NVARCHAR(10) NOT NULL,
ProductName NVARCHAR(200) NOT NULL,
ProductCategory NVARCHAR(200) NOT NULL,
Color NVARCHAR(10),
Size NVARCHAR(10),
ListPrice DECIMAL NOT NULL,
Discontinued BIT NOT NULL
)
WITH
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED COLUMNSTORE INDEX
);
외부 테이블 사용
로드할 데이터가 적절한 구조를 가진 파일에 있는 일부 경우에는 파일 위치를 참조하는 외부 테이블을 만드는 것이 더 효과적일 수 있습니다. 이렇게 하면 관계형 저장소에 로드되는 대신 원본 파일에서 직접 데이터를 읽을 수 있습니다. 다음 예제에서는 Synapse 작업 영역과 연결된 데이터 레이크의 파일을 참조하는 외부 테이블을 만드는 방법을 보여 줍니다.
-- External data source links to data lake location
CREATE EXTERNAL DATA SOURCE StagedFiles
WITH (
LOCATION = 'https://mydatalake.blob.core.windows.net/data/stagedfiles/'
);
GO
-- External format specifies file format
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
FORMAT_TYPE = PARQUET,
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO
-- External table references files in external data source
CREATE EXTERNAL TABLE dbo.ExternalStageProduct
(
ProductID NVARCHAR(10) NOT NULL,
ProductName NVARCHAR(200) NOT NULL,
ProductCategory NVARCHAR(200) NOT NULL,
Color NVARCHAR(10),
Size NVARCHAR(10),
ListPrice DECIMAL NOT NULL,
Discontinued BIT NOT NULL
)
WITH
(
DATA_SOURCE = StagedFiles,
LOCATION = 'products/*.parquet',
FILE_FORMAT = ParquetFormat
);
GO
참고
외부 테이블 사용에 대한 자세한 내용은 Azure Synapse Analytics 설명서에서 Synapse SQL 외부 테이블 사용을 참조하세요.