Azure Synapse 서버리스 SQL 풀을 사용하여 데이터 레이크의 데이터 변환 - Training
Azure Synapse 서버리스 SQL 풀을 사용하여 데이터 레이크의 데이터 변환
learn.microsoft.com
소개
CREATE EXTERNAL TABLE AS SELECT (CETAS문)을 사용하여 데이터를 변환하고 서버리스 SQL 데이터 베이스의 관계형 테이블을 통해 쿼리하거나 파일 시스템에서 직접 처리할 수 있는 데이터 레이크의 파일에 결과를 저장하는 방법을 알아본다.
목표
- CETAS문을 사용하여 데이터를 변환한다.
- 저장 프로세저에 CETAS문을 캡슐화 한다.
- 파이프라인에 데이터 변환 저장 프로시저를 포함한다.
[CREATE EXTERNAL TABLE AS SELECT 문을 사용하여 데이터 파일 변환]
- Azure Synapse 서버리스 SQL풀을 사용하여 데이터를 변환하고 추가 처리 또는 쿼리를 위해 데이터를 데이터 레이크에 파일로 유지하는 SQL문을 실행할 수 있다.
- Transact-SQL 구문에 익숙하면 원하는 특정 변환을 적용하는 SELECT문을 만들고 SQL을 사용하여 쿼리할 수 있는 메타데이터 테이블 스키마로 SELECT문으리 결과를 선택한 파일 형식으로 저장할 수 있다.
- CETAS문에는 유효한 데이터 원본의 데이터를 쿼리하고 조작하는 SELECT 문이 포함되어 있다. SELECT 문의 결과는 파일에 저장된 데이터를 통해 관계형 추상화 기능을 제공하는 데이터베이스의 메타데이터 개체인 외부 테이블에 유지된다.
- 이 기술을 적용하면 SQL을 사용하여 파일 또는 테이블에서 데ㅣ터를 추출 및 변환하고 다운스트림 처리 또는 분석을 위해 변환된 결과를 저장할 수 있다. 변환된 데이터의 후속 작업은 SQL 풀 데이터베이스의 관계형 테이블에 대해 수행하거나 기본 데이터 파일에 대해 직접 수행할 수 있다.
CETAS를 지원하는 외부 데이터베이스 개체 만들기
- CETAS 식을 사용하려면 서버리스 또는 전용 SQL풀의 데이터베이스에서 다음 유형의 개체를 만들어야 한다. 서버리스 SQL 풀을 사용하는 경우 기본 제공 데이터베이스가 아닌 사용자 지정 데이터베이스(CREATE DATABASE로 만든)에 이러한 개체를 만든다.
외부 데이터 원본
- 외부 데이터 원본은 데이터 레이크의 파일 시스템 위치에 대한 연결을 캡슐화한다. 그런다음, 이 연결을 사용하여 CETAS문에서 만든 외부 테이블의 데이터 파일이 저장되는 상대 경로를 지정할 수 있다.
- CETAS문의 원본 데이터가 동일한 데이터 레이크 경로의 파일에 있는 경우 OPENROWSET 함수에서 동일한 외부 데이터 원본을 사용하여 쿼리할 수 있다. 또는 원본 파일에 대한 별도의 외부 데이터 원본을 만들거나 OPENROWSET 함수에서 정규화된 파일 경로를 사용할 수 있다.
- 외부 데이터 원본을 만들려면 "CREATE EXTERNAL DATA SOURCE"문을 사용한다.
-- Create an external data source for the Azure storage account
CREATE EXTERNAL DATA SOURCE files
WITH (
LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/',
TYPE = HADOOP, -- For dedicated SQL pool
-- TYPE = BLOB_STORAGE, -- For serverless SQL pool
CREDENTIAL = storageCred
);
- 이전 예제에서는 외부 데이터 원본을 사용하는 쿼리를 실행하는 사용자에게 파일에 액세스할 수 있는 충분한 권한이 있다고 가정한다. 다른 방법은 모든 사용자에게 직접 읽을 수 있는 권한을 부여하지 않고 파일 데이터에 액세스하는데 사용할 수 있도록 외부 데이터 원본의 자격 증명을 캡슐화하는 것이다.
CREATE DATABASE SCOPED CREDENTIAL storagekeycred
WITH
IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = 'sv=xxx...';
CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
CREDENTIAL = storagekeycred
);
팁)
SAS 인증 외에도 관리 ID(Azure Synapse 작업 영역에서 사용하는 Microsoft Entra ID), 특정 Microsoft Entra 보안 주체 또는 쿼리를 실행하는 사용자의 ID 기반의 패스스루 인증(기본 인증 유형)을 사용하는 자격 증명을 정의할 수 있다.
외부 파일 형식 :
CETAS 문은 파일에 저장된 데이터가 있는 테이블을 만든다. 만들려는 파일 형식을 외부 파일 형식으로 지정해야 한다.
외부 파일 형식을 만들려면 다음 예제와 같이 CREATE EXTERNAL FILE FORMAT 문을 사용한다.
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
FORMAT_TYPE = PARQUET,
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
CETAS 문 사용
- 외부 데이터 원본 및 외부 파일 형식을 만든 후 CETAS 문을 사용하여 데이터를 변환하고 결과를 외부 테이블에 저장할 수 있다.
- 예를 들어 변환하려는 원본 데이터 레이크의 폴더에 저장된 쉼표로 구분된 텍스트 파일의 판매 주문으로 구성되어 있다고 가정한다. "특수 주문"으로 표시된 주문만 포함되도록 데이터를 필터링하고 변환된 데이터를 동일한 데이터 레이크의 다른 폴더에 Parquet 파일로 저장하려고 한다. 다음 예제와 같이 원본 폴더와 대상 폴더 모두에 동일한 외부 데이터 원본을 사용할 수 있다.
CREATE EXTERNAL TABLE SpecialOrders
WITH (
-- details for storing results
LOCATION = 'special_orders/',
DATA_SOURCE = files,
FILE_FORMAT = ParquetFormat
)
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
OPENROWSET(
-- details for reading source files
BULK 'sales_orders/*.csv',
DATA_SOURCE = 'files',
FORMAT = 'CSV',
PARSER_VERSION = '2.0',
HEADER_ROW = TRUE
) AS source_data
WHERE OrderType = 'Special Order';
- 이전 예제의 LOCATION 및 BULK 매개 변수는 가각 결과 및 소스 파일에 대한 상대 경로. 경로는 파일 외부 데이터 원본에서 참조하는 파일 시스템 위치를 기준으로 한다.
- 외부 데이터 원본을 사용하여 외부 테이블의 변환된 데이터를 저장할 위치를 지정해야 한다는 것이다. 파일 기반 원본 데이터가 동일한 폴더 계층 구조에 저장되는 경우 동일한 외부 데이터 원본을 사용할 수 있다. 그렇지 않으면 다음 예제와 같이 두번째 데이터 원본을 사용하여 원본 데이터에 대한 연결을 정의하거나 정규화된 경로를 사용할 수 있다.
외부 테이블 삭제
- 변환된 데이터를 포함하는 외부 테이블이 더 이상 필요하지 않은 경우 다음과 같이 DROP EXTERNAL TABLE 문을 사용하여 데이터베이스에서 해당 테이블을 삭제할 수 있다.
DROP EXTERNAL TABLE SpecialOrders;- 그러나 외부 테이블은 실제 데이터가 포함된 파일에 대한 메타데이터 추상화임을 이해하는 것이 중요하다. 외부 테이블을 삭제해도 기본 데이터는 삭제되지 않는다.
[저장 프로시저에서 데이터 변환 캡슐화]
- 데이터를 변환해야 할 때마다 스크립트에서 CREATE EXTERNAL TABLE AS SELECT (CETAS)문을 실행할 수 있지만 저장 프로시저에서 변환 작업을 캡슐화하는 것이 좋다. 이 방식을 사용하면 매개 변수를 제공하고 출력을 검색하고 단일 프로시저 호출에 추가 논리를 포함함으로써 데이터 변환을 보다 쉽게 운영할 수 있다.
클라이언트에서 서버 트워크로의 트래픽 감소
- 프로시저의 명령은 단일 코드 일괄 처리로 실행된다. 프로시저를 실행하는 호출만 네트워크를 통해 전송되므로 서버와 클라이언트간의 네트워크 트래픽을 크게 줄일 수 있다.
보안 경계 제공
- 여러 사용자 및 클라이언트 프로그램이 기본 데이터베이스 개체에 대한 직접적인 사용 권한이 없는 경우에도 프로시저를 통해 이러한 기본 개체에 대해 작업을 수행할 수 있다. 이 프로시저는 수행되는 프로세스 및 활동을 기본 데이터베이스 개체를 보호한다. 개별 개체 수준에서 권한을 부여해야 하는 요구 사항을 제거하고 보안 계층을 간소화한다.
용이한 유지 관리
- 데이터 변환과 관련된 논리 또는 파일 시스템 위치의 변경 내용은 저장 프로지서에서만 적용할 수 있다. 클라이언트 애플리케이션 또는 기타 호출 함수에 대한 업데이트가 필요하지 않는다.
성능 향상
- 저장 프로시저는 처음 실행될 때 컴파일되고 결과 실행 계획은 캐시에 보관되어 동일한 저장 프로시저의 후속 실행 시 다시 사용된다. 따라서 프로시저를 처리하는데 시간이 적게 걸린다.
[파이프라인에 데이터 변환 저장 프로시저 포함]
- 저장 프로시저에서 CREATE EXTERNAL TABLE AS SELECT (CETAS)문을 캡슐화하면 반복적으로 수행해야 할 수 있는 데이터 변환을 보다 쉽게 조작할 수 있다.
- Azure Synapse Analytics 및 Azure Data Factory에서는 데이터 레이크 파일을 호스트하는 Azure Data Lake Store Gen2 스토리지 계정 및 서버리스 SQL 풀을 포함하여 연결된 서비스에 연결하는 파이프라인을 만들 수 있다. 이를 통해 전체 ETL 파이프라인의 일부로 저장 프로시저를 호출할 수 있다.
- 예를 들어 다음 작업을 포함하는 파이프라인을 만들 수 있다.
- 데이터 레이크에서 변환된 데이터에 대한 대상 폴더를 삭제하는 삭제 작업
- 서버리스 SQL풀에 연결하고 CETAS 작업을 캡슐화하는 저장 프로시저를 실행하는 저장 프로시저 작업