외부 데이터베이스 개체 만들기

완료됨

기본 제공 서버리스 SQL 풀의 기본 마스터 데이터베이스에서 실행되는 SQL 쿼리에서 OPENROWSET 함수를 사용하여 데이터 레이크의 데이터를 탐색할 수 있습니다. 그러나 때때로 데이터 레이크에서 자주 쿼리해야 하는 외부 데이터를 더 쉽게 사용할 수 있도록 해주는 일부 개체가 포함된 사용자 지정 데이터베이스를 만들려는 경우가 있습니다.

데이터베이스 만들기

그럴 때는 SQL Server 인스턴스에서와 마찬가지로 서버리스 SQL 풀에 데이터베이스를 만들 수 있습니다. Synapse Studio에서 그래픽 인터페이스 또는 CREATE DATABASE 문을 사용할 수 있습니다. 한 가지 고려 사항은 데이터베이스의 데이터 정렬을 설정할 때 파일의 텍스트 데이터를 적절한 Transact-SQL 데이터 형식으로 변환할 수 있도록 하는 것입니다.

다음 예제 코드는 UTF-8로 인코딩된 텍스트 데이터를 VARCHAR 열로 쉽게 가져올 수 있도록 해주는 데이터 정렬을 사용하여 salesDB라는 데이터베이스를 만듭니다.

CREATE DATABASE SalesDB
    COLLATE Latin1_General_100_BIN2_UTF8

외부 데이터 원본 만들기

BULK 경로와 함께 OPENROWSET 함수를 사용하여 마스터 데이터베이스에서와 마찬가지로 자체 데이터베이스의 파일 데이터를 쿼리할 수 있습니다. 그러나 동일한 위치에 있는 데이터를 자주 쿼리할 계획이라면 해당 위치를 참조하는 외부 데이터 원본을 정의하는 것이 더 효율적입니다. 예를 들어 다음 코드는 가상 https://mydatalake.blob.core.windows.net/data/files/ 폴더에 대해 files라는 데이터 원본을 만듭니다.

CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/'
)

외부 데이터 원본의 한 가지 이점은 데이터 원본과 쿼리하려는 폴더 또는 파일에 대한 상대 경로의 조합을 사용하도록 OPENROWSET 쿼리를 간소화할 수 있다는 것입니다.

SELECT *
FROM
    OPENROWSET(
        BULK 'orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'csv',
        PARSER_VERSION = '2.0'
    ) AS orders

이 예제에서 BULK 매개 변수는 orders 폴더의 모든 .csv 파일에 대한 상대 경로를 지정하는 데 사용되며, 이 경로는 데이터 원본에서 참조하는 files 폴더의 하위 폴더입니다.

데이터 원본을 사용하는 또 다른 이점은 기본 스토리지에 액세스할 때 사용할 데이터 원본에 대한 자격 증명을 할당하여 사용자가 스토리지 계정에서 직접 데이터에 액세스하도록 허용하지 않으면서 SQL을 통해 데이터에 대한 액세스를 제공할 수 있다는 것입니다. 예를 들어 다음 코드는 SAS(공유 액세스 서명)를 사용하는 자격 증명을 만들어서 데이터 레이크를 호스트하는 기본 Azure Storage 계정에 대한 인증을 합니다.

CREATE DATABASE SCOPED CREDENTIAL sqlcred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';
GO

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = sqlcred
);
GO

팁

SAS 인증 외에도 관리 ID(Azure Synapse 작업 영역에서 사용하는 Microsoft Entra ID), 특정 Microsoft Entra 보안 주체 또는 쿼리를 실행하는 사용자의 ID 기반의 패스스루 인증(기본 인증 유형)을 사용하는 자격 증명을 정의할 수 있습니다. 서버리스 SQL 풀에서 자격 증명을 사용하는 방법에 대한 자세한 내용은 Azure Synapse Analytics 설명서의 Azure Synapse Analytics에서 서버리스 SQL 풀에 대한 스토리지 계정 액세스 제어 문서를 참조하세요.

외부 파일 형식 만들기

외부 데이터 원본은 OPENROWSET 함수를 사용하여 파일에 액세스하는 데 필요한 코드를 간소화하지만 액세스 중인 파일에 대한 형식 세부 정보는 여전히 제공해야 하며, 세부 정보에는 구분된 텍스트 파일에 대한 여러 설정이 포함될 수 있습니다. 다음과 같이 이러한 설정을 외부 파일 형식으로 캡슐화할 수 있습니다.

CREATE EXTERNAL FILE FORMAT CsvFormat
    WITH (
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS(
            FIELD_TERMINATOR = ',',
            STRING_DELIMITER = '"'
        )
    );
GO

작업해야 하는 특정 데이터 파일에 대한 파일 형식을 만든 후에는 다음에 설명된 대로 파일 형식을 사용하여 외부 테이블을 만들 수 있습니다.

외부 테이블 만들기

데이터 레이크의 파일에서 많은 분석 또는 보고를 수행해야 하는 경우 OPENROWSET 함수를 사용하면 데이터 원본 및 파일 경로를 포함하는 복잡한 코드가 발생할 수 있습니다. 데이터에 대한 액세스를 간소화하기 위해 외부 테이블에 파일을 캡슐화할 수 있고, 다른 데이터베이스 테이블에서와 마찬가지로 사용자와 보고 애플리케이션은 표준 SQL SELECT 문을 사용하여 쿼리할 수 있습니다. 외부 테이블을 만들려면 CREATE EXTERNAL TABLE 문을 사용하여 표준 테이블에 대한 열 스키마를 지정하고 WITH 절을 포함하여 데이터에 대한 외부 데이터 원본, 상대 경로 및 외부 파일 형식을 지정합니다.

CREATE EXTERNAL TABLE dbo.products
(
    product_id INT,
    product_name VARCHAR(20),
    list_price DECIMAL(5,2)
)
WITH
(
    DATA_SOURCE = files,
    LOCATION = 'products/*.csv',
    FILE_FORMAT = CsvFormat
);
GO

-- query the table
SELECT * FROM dbo.products;

이 단원에서 설명하는 외부 개체가 포함된 데이터베이스를 만들면 데이터 레이크의 파일에 관계형 데이터베이스 레이어를 제공하여 많은 데이터 분석가 및 보고 도구가 표준 SQL 쿼리 의미 체계를 사용하여 데이터에 액세스하는 것을 더 쉽게 만들 수 있습니다.