다음을 통해 공유


CREATE EXTERNAL TABLE AS SELECT(CETAS)(Transact-SQL)

적용 대상: SQL Server 2022(16.x) 이상 Azure Synapse Analytics Analytics Platform System(PDW)

외부 테이블을 만든 다음, Transact-SQL SELECT 문의 결과를 병렬로 내보냅니다.

  • Azure Synapse Analytics 및 Analytics Platform System은 Hadoop 또는 Azure Blob Storage를 지원합니다.
  • SQL Server 2022(16.x) 이상 버전에서는 CREATE EXTERNAL TABLE AS SELECT(CETAS)를 지원하여 외부 테이블을 만든 다음 Transact-SQL SELECT 문의 결과를 ADLS(Azure Data Lake Storage) Gen2, Azure Storage 계정 V2 및 S3 호환 개체 스토리지로 병렬로 내보냅니다.

참고 항목

Azure SQL Managed Instance에 대한 CETAS의 기능 및 보안은 SQL Server 또는 Azure Synapse Analytics와 다릅니다. 자세한 내용은 CREATE EXTERNAL TABLE AS SELECT의 Azure SQL Managed Instance 버전을 참조하세요.

참고 항목

Azure Synapse Analytics의 서버리스 풀에 대한 CETAS의 기능과 보안은 SQL Server와 다릅니다. 자세한 내용은 Synapse SQL을 사용한 CETAS를 참조하세요.

Transact-SQL 구문 표기 규칙

구문

CREATE EXTERNAL TABLE { [ [ database_name . [ schema_name ] . ] | schema_name . ] table_name }
    [ (column_name [ , ...n ] ) ]
    WITH (
        LOCATION = 'hdfs_folder' | '<prefix>://<path>[:<port>]' ,
        DATA_SOURCE = external_data_source_name ,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ , ...n ] ]
    )
    AS <select_statement>
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value
}

<select_statement> ::=
    [ WITH <common_table_expression> [ , ...n ] ]
    SELECT <select_criteria>

인수

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

데이터베이스에 만들려는 테이블의 한 부분에서 세 부분으로 이루어진 이름입니다. 외부 테이블의 경우 관계형 데이터베이스는 테이블 메타데이터만 저장합니다.

[ ( column_name [ ,...n ] ) ]

테이블 열의 이름입니다.

위치

적용 대상: Azure Synapse Analytics 및 Analytics Platform System

'hdfs_folder'**
SELECT 문의 결과를 외부 데이터 원본에 기록할 위치를 지정합니다. 위치는 폴더 이름이며 Hadoop 클러스터 또는 Blob Storage의 루트 폴더에 대한 상대 경로를 포함할 수 있습니다(선택 사항). PolyBase가 아직 없는 경우 경로 및 폴더를 만듭니다.

외부 파일은 hdfs_folder에 기록되고 QueryID_date_time_ID.format로 명명되며, 여기서 ID는 증분 식별자이고 format은 내보낸 데이터 형식입니다. 예제는 QID776_20160130_182739_0.orc입니다.

LOCATION은 폴더를 가리키고 후행 /이 있어야 합니다(예: aggregated_data/.

적용 대상: SQL Server 2022(16.x) 이상

prefix://path[:port] 는 SELECT 문의 결과가 기록되는 외부 데이터 원본에 연결 프로토콜(접두사), 경로 및 선택적으로 포트를 제공합니다.

대상이 S3 호환 개체 스토리지인 경우 버킷이 먼저 존재해야 하지만 필요한 경우 PolyBase는 하위 폴더를 만들 수 있습니다. SQL Server 2022(16.x)는 Azure Data Lake Storage Gen2, Azure Storage 계정 V2 및 S3 호환 개체 스토리지를 지원합니다. ORC 파일은 현재 지원되지 않습니다.

DATA_SOURCE = external_data_source_name

외부 데이터가 저장된 또는 저장될 위치를 포함하는 외부 데이터 원본 개체의 이름을 지정합니다. 위치는 Hadoop 클러스터 또는 Azure Blob Storage 중 하나입니다. 외부 데이터 원본을 만들려면 CREATE EXTERNAL DATA SOURCE(Transact-SQL)를 사용합니다.

FILE_FORMAT = external_file_format_name

외부 데이터 파일에 대한 형식을 포함하는 외부 파일 형식 개체의 이름을 지정합니다. 외부 파일 형식을 만들려면 CREATE EXTERNAL FILE FORMAT (Transact-SQL)을 사용합니다.

REJECT 옵션

이 CREATE EXTERNAL TABLE AS SELECT 문이 실행되면 REJECT 옵션이 적용되지 않습니다. 그 대신, 나중에 데이터베이스가 외부 테이블에서 데이터를 가져올 때 사용할 수 있도록 이곳에 지정됩니다. 나중에 CREATE TABLE AS SELECT 문이 외부 테이블에서 데이터를 선택할 때 데이터베이스는 거부 옵션을 사용하여 가져오기를 중지하는 기준이 되는 가져오기 실패의 행 수 또는 백분율을 결정합니다.

  • REJECT_VALUE = reject_value

    데이터베이스가 가져오기를 중지하는 기준이 되는 가져오기 실패 값 또는 행 백분율을 지정합니다.

  • REJECT_TYPE = value | percentage

    REJECT_VALUE 옵션이 리터럴 값인지 백분율인지를 명확히 합니다.

    • value

      REJECT_VALUE가 백분율이 아닌 리터럴 값인 경우 사용됩니다. 실패한 행 수가 reject_value 초과하면 데이터베이스가 외부 데이터 파일에서 행 가져오기를 중지합니다.

      예를 들어 5개의 행을 가져오지 못한 후 데이터베이스가 행 가져오기를 중지하는 경우 REJECT_VALUE = 5 REJECT_TYPE = value를 예로 들 수 있습니다.

    • percentage

      REJECT_VALUE가 리터럴 값이 아닌 백분율인 경우 사용됩니다. 실패한 행의 백분율이 reject_value 초과하면 데이터베이스가 외부 데이터 파일에서 행 가져오기를 중지합니다. 실패한 행의 백분율은 일정 간격으로 계산됩니다. 전용 SQL 풀에서만 유효합니다.TYPE=HADOOP

  • REJECT_SAMPLE_VALUE = reject_sample_value

    필요한 경우 REJECT_TYPE = percentage 데이터베이스에서 실패한 행의 백분율을 다시 계산하기 전에 가져올 행 수를 지정합니다.

    예를 들어 REJECT_SAMPLE_VALUE = 1000인 경우, 데이터베이스는 외부 데이터 파일에서 행 1000개 가져오기를 시도한 후 실패한 행의 백분율을 계산합니다. 실패한 행의 백분율이 reject_value 미만이면 데이터베이스는 다른 1000개의 행을 로드하려고 시도합니다. 계속해서 데이터베이스는 추가로 행 1000개의 가져오기를 시도한 후마다 실패한 행의 백분율을 다시 계산합니다.

    참고

    데이터베이스는 실패한 행의 백분율을 일정 간격으로 계산하므로 실패한 행의 실제 백분율은 reject_value를 초과할 수 있습니다.

    예제:

    이 예제에서는 REJECT 옵션 세 개가 서로 상호 작용하는 방법을 보여 줍니다. 예를 들어 다음과 REJECT_TYPE = percentage, REJECT_VALUE = 30, REJECT_SAMPLE_VALUE = 100같은 시나리오가 발생할 수 있습니다.

    • 데이터베이스가 처음 100개 행의 로드를 시도하며, 이 중 25개가 실패하고 75개가 성공합니다.
    • 실패한 행의 백분율은 25%로 계산되며, 이는 거부 값 30%보다 작습니다. 따라서 로드를 중지할 필요가 없습니다.
    • 데이터베이스가 다음 100개 행의 로드를 시도합니다. 이번에는 25개가 성공하고 75개가 실패합니다.
    • 실패한 행의 백분율은 50%로 다시 계산됩니다. 실패한 행의 이 백분율은 30% 거부 값을 초과했습니다.
    • 행 200개의 로드를 시도한 후 실패한 행이 50%이고, 이는 지정된 30% 한도보다 크므로 로드가 실패합니다.

WITH common_table_expression

CTE(공통 테이블 식)라고도 하는 임시로 이름이 지정된 결과 집합을 지정합니다. 자세한 내용은 WITH common_table_expression(Transact-SQL)을 참조하세요.

SELECT <select_criteria>

새 테이블을 SELECT 문의 결과로 채웁니다. select_criteria는새 테이블에 복사할 데이터를 결정하는 SELECT 문의 본문입니다. SELECT 문에 대한 자세한 내용은 SELECT (Transact-SQL)을 참조하세요.

참고 항목

SELECT의 ORDER BY 절은 CETAS에 영향을 주지 않습니다.

열 옵션

  • column_name [ ,...n ]

    열 이름은 CREATE TABLE에서 설명한 열 옵션을 허용하지 않습니다. 그 대신, 새 테이블에 대해 하나 이상의 열 이름으로 이루어진 선택적 목록을 제공할 수 있습니다. 새 테이블의 열은 지정한 이름을 사용합니다. 열 이름을 지정하는 경우 열 목록의 열 수가 선택 결과의 열 수와 일치해야 합니다. 열 이름을 지정하지 않으면 새 대상 테이블에서 select 문 결과의 열 이름을 사용합니다.

    데이터 형식, 데이터 정렬 또는 Null 허용 여부 등의 다른 열 옵션을 지정할 수 없습니다. 이러한 각각의 특성은 SELECT 문의 결과에서 파생됩니다. 그러나 SELECT 문을 사용하여 특성을 변경할 수 있습니다. 예제는 CETAS를 사용하여 열 특성 변경을 참조하세요.

사용 권한

이 명령을 실행하려면 데이터베이스 사용자에게 다음 권한 또는 멤버 자격이 모두 필요합니다.

  • db_ddladmin 고정 데이터베이스 역할에 새 테이블 또는 멤버 자격을 포함할 로컬 스키마에 대한 ALTER SCHEMA 권한.
  • db_ddladmin 고정 데이터베이스 역할의 CREATE TABLE 권한 또는 멤버 자격.
  • select_criteria에 참조된 임의의 개체에 대한 SELECT 권한.

로그인하려면 다음 권한이 모두 필요합니다.

  • ADMINISTER BULK OPERATIONS
  • ALTER ANY EXTERNAL DATA SOURCE
  • ALTER ANY EXTERNAL FILE FORMAT
  • 일반적으로 폴더 콘텐츠를 나열하고 CETAS용 LOCATION 폴더에 쓸 수 있는 권한이 있어야 합니다.
  • Azure Synapse Analytics 및 Analytics 플랫폼 시스템에서 Hadoop 클러스터 또는 Azure Blob Storage의 외부 폴더를 읽고 쓸 수 있는 쓰기 권한을 씁니다 .
  • SQL Server 2022(16.x)에서는 외부 위치에 대한 적절한 권한을 설정해야 합니다. 위치에 데이터를 출력할 수 있는 쓰기 권한과 데이터에 액세스할 수 있는 읽기 권한입니다.
  • Azure Blob Storage 및 Azure Data Lake Gen2의 SHARED ACCESS SIGNATURE 경우 토큰에 컨테이너에 대한 읽기, 쓰기, 목록, 만들기 권한이 부여되어야 합니다.
  • Azure Blog Storage의 경우 SAS 토큰을 Allowed Services생성하려면 다음 Blob 확인란을 선택해야 합니다.
  • Azure Data Lake Gen2의 Allowed Services경우 SAS 토큰을 생성하려면 : ContainerObject 확인란을 선택해야 합니다.

Important

ALTER ANY EXTERNAL DATA SOURCE 권한은 보안 주체에 대해 외부 데이터 원본 개체를 만들고 수정하는 권한을 부여하므로 데이터베이스의 모든 데이터베이스 범위 자격 증명에 액세스하는 권한 또한 부여합니다. 이 권한은 높은 수준의 권한으로 간주되어야 하며 시스템의 신뢰할 수 있는 보안 주체에만 부여되어야 합니다.

오류 처리

CREATE EXTERNAL TABLE AS SELECT가 데이터를 텍스트로 분리된 파일로 내보내는 경우 내보내기에 실패한 행에 대한 거부 파일이 없습니다.

외부 테이블을 만들 때 데이터베이스는 외부 위치에 연결을 시도합니다. 연결이 실패하면 명령이 실패하고 외부 테이블이 만들어지지 않습니다. 데이터베이스는 연결을 최소 3회 다시 시도하므로 이 명령이 실패하려면 1분 이상 걸릴 수 있습니다.

CREATE EXTERNAL TABLE AS SELECT가 취소되거나 실패하면 데이터베이스는 외부 데이터 원본에 이미 만들어진 새 파일 및 폴더를 한 번 제거하려고 시도합니다.

Azure Synapse Analytics 및 Analytics 플랫폼 시스템에서 데이터베이스는 데이터 내보내기 중에 외부 데이터 원본에서 발생하는 Java 오류를 보고합니다.

설명

CREATE EXTERNAL TABLE AS SELECT 문이 완료된 후 외부 테이블에 대해 Transact-SQL 쿼리를 실행할 수 있습니다. 이러한 작업은 CREATE TABLE AS SELECT 문을 사용하여 가져오지 않는 한 쿼리 기간 동안 데이터베이스로 데이터를 가져옵니다.

외부 테이블 이름과 정의는 데이터베이스 메타데이터에 저장됩니다. 데이터는 외부 데이터 원본에 저장됩니다.

CREATE EXTERNAL TABLE AS SELECT 문은 원본 테이블이 분할되었더라도 언제나 분할되지 않은 테이블을 만듭니다.

SQL Server 2022(16.x)의 경우 sp_configure를 사용하여 allow polybase export 옵션을 사용하도록 설정해야 합니다. 자세한 내용은 allow polybase export 구성 옵션 설정을 참조하세요.

EXPLAIN를 사용하여 만든 Azure Synapse Analytics 및 Analytics Platform System의 쿼리 계획의 경우 데이터베이스는 외부 섞기 이동, 외부 브로드캐스트 이동, 외부 파티션 이동과 같은 외부 테이블에 대해 이러한 쿼리 계획 작업을 사용합니다.

외부 테이블을 만들기 위한 필수 구성 요소로서, Analytics Platform System은 Hadoop 연결을 구성해야 합니다. 자세한 내용은 Microsoft 다운로드 센터에서 다운로드할 수 있는 분석 플랫폼 시스템 설명서의 "외부 데이터 연결 구성(분석 플랫폼 시스템)"을 참조하세요.

제한 사항

외부 테이블 데이터는 데이터베이스 외부에 있으므로 백업 및 복원 작업은 데이터베이스에 저장된 데이터에 대해서만 작동합니다. 따라서 메타데이터만 백업되고 복원됩니다.

데이터베이스는 외부 테이블이 포함된 데이터베이스 백업을 복원할 때 외부 데이터 원본에 대한 연결을 확인하지 않습니다. 원래 원본에 액세스할 수 없는 경우 외부 테이블의 메타데이터 복원은 여전히 성공하지만 외부 테이블에 대한 SELECT 작업은 실패합니다.

데이터베이스는 데이터베이스와 외부 데이터 간의 데이터 일관성을 보장하지 않습니다. 고객만이 외부 데이터와 데이터베이스 간의 일관성을 유지할 책임이 있습니다.

DML(데이터 조작 언어) 작업은 외부 테이블에 대해서는 지원되지 않습니다. 예를 들어 Transact-SQL 업데이트, 삽입 또는 삭제 Transact-SQL 문을 사용하여 외부 데이터를 수정할 수 없습니다.

CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW 및 DROP VIEW는 외부 테이블에 대해서만 허용되는 DDL(데이터 정의 언어) 작업입니다.

Azure Synapse Analytics에 대한 제한 사항

  • Azure Synapse Analytics 전용 SQL 풀 및 분석 플랫폼 시스템에서 PolyBase는 32개의 동시 PolyBase 쿼리를 실행할 때 폴더당 최대 33,000개의 파일을 사용할 수 있습니다. 이 최대 개수는 각 HDFS 폴더의 파일과 하위 폴더를 모두 포함합니다. 동시성 수준이 32보다 작은 경우 사용자는 33,000개보다 많은 파일을 포함하는 HDFS의 폴더에 대해 PolyBase 쿼리를 실행할 수 있습니다. Hadoop 및 PolyBase 사용자에 대해 파일 경로를 짧게 유지하고 HDFS 폴더당 30,000개 이하의 파일을 사용할 것을 권장합니다. 너무 많은 파일이 참조되면 JVM 메모리 부족 예외가 발생할 수 있습니다.

  • 서버리스 SQL 풀에서는 현재 데이터가 있는 위치에 외부 테이블을 만들 수 없습니다. 데이터를 저장하는 데 사용된 위치를 다시 사용하려면 ADLS에서 위치를 수동으로 삭제해야 합니다. 더 많은 제한 사항 및 모범 사례는 필터 최적화 모범 사례를 참조 하세요.

Azure Synapse Analytics 전용 SQL 풀 및 분석 플랫폼 시스템에서 CREATE EXTERNAL TABLE AS SELECT가 RCFile에서 선택하면 RCFile의 열 값에 파이프() 문자가| 포함되어서는 안 됩니다.

SET ROWCOUNT(Transact-SQL) 는 CREATE EXTERNAL TABLE AS SELECT에 영향을 주지 않습니다. 비슷한 동작을 얻으려면 TOP(Transact-SQL)을 사용합니다.

파일 이름에 대한 제한 사항은 컨테이너, Blob 및 메타데이터 명명 및 참조를 검토합니다.

문자 오류

데이터에 있는 다음 문자는 CREATE EXTERNAL TABLE AS SELECT를 사용하여 Parquet 파일에 대해 거부된 레코드를 포함하여 오류를 일으킬 수 있습니다.

Azure Synapse Analytics 및 Analytics Platform System에서는 ORC 파일에도 적용됩니다.

  • |
  • " (따옴표 문자)
  • \r\n
  • \r
  • \n

위의 문자가 포함된 CREATE EXTERNAL TABLE AS SELECT를 사용하려면 먼저 CREATE EXTERNAL TABLE AS SELECT 문을 실행하여 데이터를 구분된 텍스트 파일로 내보내야 합니다. 그런 다음, 외부 도구를 사용하여 Parquet 또는 ORC로 변환할 수 있습니다.

parquet 작업

parquet 파일을 CREATE EXTERNAL TABLE AS SELECT 사용하는 경우 MAXDOP(구성된 최대 병렬 처리 수준)까지 사용 가능한 CPU당 하나의 parquet 파일을 생성합니다. 각 파일은 최대 190GB까지 증가할 수 있으며, 그 후에 SQL Server는 필요에 따라 더 많은 Parquet 파일을 생성합니다.

쿼리 힌트 OPTION (MAXDOP n) 는 SELECT 부분에 CREATE EXTERNAL TABLE AS SELECT만 영향을 하며 parquet 파일의 양에 영향을 주지 않습니다. 데이터베이스 수준 MAXDOP 및 인스턴스 수준 MAXDOP만 고려됩니다.

잠금

SCHEMARESOLUTION 개체에 대한 공유 잠금을 실행합니다.

지원되는 데이터 유형

CETAS를 사용하여 다음 SQL 데이터 형식으로 결과 집합을 저장할 수 있습니다.

  • binary
  • varbinary
  • char
  • varchar
  • nchar
  • nvarchar
  • smalldate
  • date
  • datetime
  • datetime2
  • datetimeoffset
  • 시간
  • decimal
  • numeric
  • float
  • real
  • bigint
  • tinyint
  • smallint
  • int
  • bigint
  • bit
  • money
  • smallmoney

예제

A. CREATE EXTERNAL TABLE AS SELECT를 사용하여 Hadoop 테이블 만들기

적용 대상: Azure Synapse Analytics 및 Analytics Platform System

다음 예제에서는 원본 테이블 dimCustomer의 열 정의 및 데이터를 사용하여 hdfsCustomer라는 새 외부 테이블을 만듭니다.

테이블 정의는 데이터베이스에 저장되며 SELECT 문의 결과는 Hadoop 외부 데이터 원본 customer_ds 파일로 /pdwdata/customer.tbl 내보내집니다. 파일은 외부 파일 형식 customer_ff에 따라 서식 지정됩니다.

파일 이름은 데이터베이스에 의해 생성되며 파일을 생성한 쿼리와 파일을 정렬하기 쉽도록 쿼리 ID를 포함합니다.

Customer 디렉터리 앞의 경로 hdfs://xxx.xxx.xxx.xxx:5000/files/는 이미 존재합니다. Customer 디렉터리가 없으면 데이터베이스에서 디렉터리를 만듭니다.

참고 항목

이 예제에서는 5000을 지정합니다. 포트가 지정되지 않은 경우 데이터베이스에서 8020을 기본 포트로 사용합니다.

결과 Hadoop 위치 및 파일 이름은 hdfs:// xxx.xxx.xxx.xxx:5000/files/Customer/ QueryID_YearMonthDay_HourMinutesSeconds_FileIndex.txt.입니다.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE hdfsCustomer
    WITH (
            LOCATION = '/pdwdata/customer.tbl',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT *
FROM dimCustomer;
GO

B. CREATE EXTERNAL TABLE AS SELECT와 함께 쿼리 힌트 사용

적용 대상: Azure Synapse Analytics 및 Analytics Platform System

이 쿼리는 CREATE EXTERNAL TABLE AS SELECT 문에 쿼리 조인 힌트를 사용하는 기본 구문을 보여줍니다. 쿼리가 제출된 후 데이터베이스는 해시 조인 방법을 사용하여 쿼리 계획을 생성합니다. 조인 힌트 및 OPTION 절을 사용하는 방법에 대한 자세한 내용은 OPTION 절(Transact-SQL)을 참조하세요.

참고

이 예제에서는 5000을 지정합니다. 포트가 지정되지 않은 경우 데이터베이스에서 8020을 기본 포트로 사용합니다.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.*
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

C. CETAS를 사용하여 열 특성 변경

적용 대상: Azure Synapse Analytics 및 Analytics Platform System

이 예제에서는 CETAS를 사용하여 FactInternetSales 테이블의 여러 열에 대해 데이터 형식, NULL 허용 여부 및 데이터 정렬을 변경합니다.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.ProductKey AS ProductKeyNoChange,
    T1.OrderDateKey AS OrderDate,
    T1.ShipDateKey AS ShipDate,
    T1.CustomerKey AS CustomerKeyNoChange,
    T1.OrderQuantity AS Quantity,
    T1.SalesAmount AS MONEY
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

D. CREATE EXTERNAL TABLE AS SELECT를 사용하여 데이터를 parquet로 내보내기

적용 대상: SQL Server 2022(16.x)

다음 예제에서는 테이블의 AdventureWorks2022데이터를 사용하는 새 ext_sales 외부 테이블을 SalesOrderDetail 만듭니다. allow polybase export 구성 옵션을 사용하도록 설정해야 합니다.

SELECT 문의 결과는 이전에 구성된 s3_eds라는 S3 호환 개체 스토리지에 저장되며, 적절한 자격 증명은 s3_dsc로 생성됩니다. parquet 파일 위치는 <ip>:<port>/cetas/sales.parquet이며, 여기서 cetas는 이전에 만든 스토리지 버킷이 있는 위치입니다.

참고

델타 형식은 현재 읽기 전용으로만 지원됩니다.

-- Credential to access the S3-compatible object storage
CREATE DATABASE SCOPED CREDENTIAL s3_dsc
    WITH IDENTITY = 'S3 Access Key',
        SECRET = '<accesskeyid>:<secretkeyid>'
GO

-- S3-compatible object storage data source
CREATE EXTERNAL DATA SOURCE s3_eds
    WITH (
            LOCATION = 's3://<ip>:<port>',
            CREDENTIAL = s3_dsc
            )

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE ext_sales
    WITH (
            LOCATION = '/cetas/sales.parquet',
            DATA_SOURCE = s3_eds,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM AdventureWorks2022.[Sales].[SalesOrderDetail];
GO

E. 델타 테이블에서 parquet로 CREATE EXTERNAL TABLE AS SELECT 사용

적용 대상: SQL Server 2022(16.x)

다음 예제에서는 s3_delta라는 S3 호환 개체 스토리지에 있는 델타 테이블 형식의 데이터를 사용하는 Delta_to_Parquet라는 새 외부 테이블을 만들고 결과를 parquet 파일로 s3_parquet라는 다른 데이터 원본에 씁니다. 이 예제에서는 OPENROWSET 명령을 사용합니다. allow polybase export 구성 옵션을 사용하도록 설정해야 합니다.

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE Delta_to_Parquet
    WITH (
            LOCATION = '/backup/sales.parquet',
            DATA_SOURCE = s3_parquet,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM OPENROWSET(BULK '/delta/sales_fy22/', FORMAT = 'DELTA', DATA_SOURCE = 's3_delta') AS [r];
GO

F. 뷰를 원본으로 사용하여 CREATE EXTERNAL TABLE AS SELECT 사용

적용 대상: Azure Synapse Analytics 서버리스 SQL 풀 및 전용 SQL 풀.

이 예제에서는 사용자 정의 뷰를 원본으로 사용하고, 관리 ID를 인증으로 사용하고, CETAS를 작성하기 위한 템플릿 코드의 예를 볼 수 있습니다 wasbs:.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'wasbs://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

G. 뷰를 원본으로 사용하여 CREATE EXTERNAL TABLE AS SELECT 사용

적용 대상: Azure Synapse Analytics 서버리스 SQL 풀 및 전용 SQL 풀.

이 예제에서는 사용자 정의 뷰를 원본으로 사용하고, 관리 ID를 인증으로 사용하고, CETAS를 작성하기 위한 템플릿 코드의 예를 볼 수 있습니다 https:.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'https://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

다음 단계

적용 대상: Azure SQL Managed Instance

외부 테이블을 만든 다음, Transact-SQL SELECT 문의 결과를 병렬로 내보냅니다.

CREATE EXTERNAL TABLE AS SELECT(CETAS)를 사용하여 다음 작업을 완료할 수 있습니다.

  • Azure Blob Storage 또는 ADLS(Azure Data Lake Storage) Gen2에서 Parquet 또는 CSV 파일 위에 외부 테이블을 만듭니다.
  • T-SQL SELECT 문의 결과를 병렬로 만든 외부 테이블로 내보냅니다.
  • Azure SQL Managed Instance의 더 많은 데이터 가상화 기능은 Azure SQL Managed Instance를 사용한 데이터 가상화를 참조 하세요.

참고 항목

이 콘텐츠는 Azure SQL Managed Instance에만 적용됩니다. 다른 플랫폼의 경우 드롭드론 선택기에서 적절한 버전의 CREATE EXTERNAL TABLE AS SELECT 를 선택합니다.

Transact-SQL 구문 표기 규칙

구문

CREATE EXTERNAL TABLE [ [database_name  . [ schema_name ] . ] | schema_name . ] table_name
    WITH (
        LOCATION = 'path_to_folder/',  
        DATA_SOURCE = external_data_source_name,  
        FILE_FORMAT = external_file_format_name
        [, PARTITION ( column_name [ , ...n ] ) ]
)
    AS <select_statement>  
[;]

<select_statement> ::=  
    [ WITH <common_table_expression> [ ,...n ] ]  
    SELECT <select_criteria>

인수

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

만들려는 테이블의 한 부분에서 세 부분으로 이루어진 이름입니다. 외부 테이블의 경우 테이블 메타데이터만 저장됩니다. 실제 데이터는 이동되거나 저장되지 않습니다.

LOCATION = 'path_to_folder'

SELECT 문의 결과를 외부 데이터 원본에 기록할 위치를 지정합니다. 루트 폴더는 외부 데이터 원본에 지정된 데이터 위치입니다. LOCATION은 폴더를 가리키고 후행 /이 있어야 합니다. 예: aggregated_data/

CETAS의 대상 폴더는 비어 있어야 합니다. 경로 및 폴더가 아직 없으면 자동으로 만들어집니다.

DATA_SOURCE = external_data_source_name

외부 데이터를 저장할 위치를 포함하는 외부 데이터 원본 개체의 이름을 지정합니다. 외부 데이터 원본을 만들려면 CREATE EXTERNAL DATA SOURCE(Transact-SQL)를 사용합니다.

FILE_FORMAT = external_file_format_name

외부 데이터 파일에 대한 형식을 포함하는 외부 파일 형식 개체의 이름을 지정합니다. 외부 파일 형식을 만들려면 CREATE EXTERNAL FILE FORMAT (Transact-SQL)을 사용합니다. 현재 FORMAT_TYPE=PARQUET 및 FORMAT_TYPE=DELIMITEDTEXT가 있는 외부 파일 형식만 지원됩니다. DELIMITEDTEXT 형식에 대한 GZip 압축은 지원되지 않습니다.

[, PARTITION ( column name [ , ... n ] ) ]

출력 데이터를 여러 parquet 파일 경로로 분할합니다. 분할은 지정된 열()column_name에 따라 발생하며 LOCATION의 와일드카드(*)를 각 분할 열과 일치합니다. PARTITION 파트의 열 수는 LOCATION의 와일드카드 수와 일치해야 합니다. 분할에 사용되지 않는 열이 하나 이상 있어야 합니다.

WITH <common_table_expression>

CTE(공통 테이블 식)라고도 하는 임시로 이름이 지정된 결과 집합을 지정합니다. 자세한 내용은 WITH common_table_expression (Transact-SQL)을 참조하세요.

SELECT <select_criteria>

새 테이블을 SELECT 문의 결과로 채웁니다. select_criteria는새 테이블에 복사할 데이터를 결정하는 SELECT 문의 본문입니다. SELECT 문에 대한 자세한 내용은 SELECT (Transact-SQL)을 참조하세요.

참고 항목

SELECT의 ORDER BY 절은 CETAS에서 지원되지 않습니다.

사용 권한

스토리지의 사용 권한

폴더 콘텐츠를 나열하고 CETAS가 작동하려면 위치 경로에 쓸 수 있는 권한이 있어야 합니다.

지원되는 인증 방법은 관리 ID 또는 SAS(공유 액세스 서명) 토큰입니다.

  • 인증에 관리 ID를 사용하는 경우 SQL 관리형 인스턴스의 서비스 주체에 대상 컨테이너에 대한 Storage Blob 데이터 기여자 역할이 있는지 확인합니다.
  • SAS 토큰을 사용하는 경우 읽기, 쓰기목록 권한이 필요합니다.
  • Azure Blog Storage의 경우 SAS 토큰을 Allowed Services생성하려면 다음 Blob 확인란을 선택해야 합니다.
  • Azure Data Lake Gen2의 Allowed Services경우 SAS 토큰을 생성하려면 : ContainerObject 확인란을 선택해야 합니다.

사용자 할당 관리 ID는 지원되지 않습니다. Microsoft Entra 통과 인증은 지원되지 않습니다. Microsoft Entra ID는 (이전의 Azure Active Directory)입니다.

SQL 관리되는 인스턴스의 권한

이 명령을 실행하려면 데이터베이스 사용자에게 다음 권한 또는 멤버 자격이 모두 필요합니다.

  • db_ddladmin 고정 데이터베이스 역할에 새 테이블 또는 멤버 자격을 포함할 로컬 스키마에 대한 ALTER SCHEMA 권한.
  • db_ddladmin 고정 데이터베이스 역할의 CREATE TABLE 권한 또는 멤버 자격.
  • select_criteria에 참조된 임의의 개체에 대한 SELECT 권한.

로그인하려면 다음 권한이 모두 필요합니다.

  • ADMINISTER BULK OPERATIONS
  • ALTER ANY EXTERNAL DATA SOURCE
  • ALTER ANY EXTERNAL FILE FORMAT

Important

ALTER ANY EXTERNAL DATA SOURCE 권한은 보안 주체에 대해 외부 데이터 원본 개체를 만들고 수정하는 권한을 부여하므로 데이터베이스의 모든 데이터베이스 범위 자격 증명에 액세스하는 권한 또한 부여합니다. 이 권한은 높은 수준의 권한으로 간주되어야 하며 시스템의 신뢰할 수 있는 보안 주체에만 부여되어야 합니다.

지원되는 데이터 유형

CETAS는 다음과 같은 SQL 데이터 형식을 사용하여 결과 집합을 저장합니다.

  • binary
  • varbinary
  • char
  • varchar
  • nchar
  • nvarchar
  • smalldatetime
  • date
  • datetime
  • datetime2
  • datetimeoffset
  • 시간
  • decimal
  • numeric
  • float
  • real
  • bigint
  • tinyint
  • smallint
  • int
  • bigint
  • bit
  • money
  • smallmoney

참고 항목

1MB보다 큰 LOB는 CETAS와 함께 사용할 수 없습니다.

제한 사항

  • Azure SQL Managed Instance에 대한 CREATE EXTERNAL TABLE AS SELECT(CETAS)는 기본적으로 사용하지 않도록 설정됩니다. 자세한 내용은 다음 섹션인 기본적으로 사용 안 함 섹션을 참조하세요.
  • Azure SQL Managed Instance의 데이터 가상화에 대한 제한 사항 또는 알려진 문제에 대한 자세한 내용은 제한 사항 및 알려진 문제를 참조하세요.

외부 테이블 데이터는 데이터베이스 외부에 있으므로 백업 및 복원 작업은 데이터베이스에 저장된 데이터에 대해서만 작동합니다. 따라서 메타데이터만 백업되고 복원됩니다.

데이터베이스는 외부 테이블이 포함된 데이터베이스 백업을 복원할 때 외부 데이터 원본에 대한 연결을 확인하지 않습니다. 원래 원본에 액세스할 수 없는 경우 외부 테이블의 메타데이터 복원은 여전히 성공하지만 외부 테이블에 대한 SELECT 작업은 실패합니다.

데이터베이스는 데이터베이스와 외부 데이터 간의 데이터 일관성을 보장하지 않습니다. 고객만이 외부 데이터와 데이터베이스 간의 일관성을 유지할 책임이 있습니다.

DML(데이터 조작 언어) 작업은 외부 테이블에 대해서는 지원되지 않습니다. 예를 들어 Transact-SQL 업데이트, 삽입 또는 삭제Transact-SQL 문을 사용하여 외부 데이터를 수정할 수 없습니다.

CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW 및 DROP VIEW는 외부 테이블에 대해서만 허용되는 DDL(데이터 정의 언어) 작업입니다.

현재 데이터가 있는 위치에는 외부 테이블을 만들 수 없습니다. 데이터를 저장하는 데 사용된 위치를 다시 사용하려면 ADLS에서 위치를 수동으로 삭제해야 합니다.

SET ROWCOUNT(Transact-SQL) 는 CREATE EXTERNAL TABLE AS SELECT에 영향을 주지 않습니다. 비슷한 동작을 얻으려면 TOP(Transact-SQL)을 사용합니다.

파일 이름에 대한 제한 사항은 컨테이너, Blob 및 메타데이터 명명 및 참조를 검토합니다.

스토리지 유형

파일은 Azure Data Lake Storage Gen2 또는 Azure Blob Storage에 저장할 수 있습니다. 파일을 쿼리하려면 특정 형식의 위치를 제공하고 다음 예제와 같이 외부 원본 및 엔드포인트/프로토콜 형식에 해당하는 위치 형식 접두사를 사용해야 합니다.

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

--Data Lake endpoint
adls://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

중요

제공된 위치 유형 접두사는 통신을 위한 최적의 프로토콜을 선택하고 특정 스토리지 유형에서 제공하는 고급 기능을 활용하는 데 사용됩니다. 제네릭 https:// 접두사 사용은 비활성화되어 있습니다. 항상 엔드포인트별 접두사를 사용합니다.

기본적으로 사용 안 함

CETAS(CREATE EXTERNAL TABLE AS SELECT)를 사용하면 SQL 관리형 인스턴스의 데이터를 외부 스토리지 계정으로 내보낼 수 있으므로 이러한 기능으로 데이터 반출 위험이 발생할 수 있습니다. 따라서 AZURE SQL Managed Instance에 대해 CETAS는 기본적으로 사용하지 않도록 설정됩니다.

CETAS 사용

Azure SQL Managed Instance용 CETAS는 상승된 Azure 권한이 필요하고 T-SQL을 통해 사용하도록 설정할 수 없는 메서드를 통해서만 사용하도록 설정할 수 있습니다. 무단 데이터 반출의 위험 때문에 CETAS는 T-SQL 저장 프로시저를 통해 sp_configure 사용하도록 설정할 수 없으며 대신 SQL 관리형 인스턴스 외부에서 사용자 작업을 수행해야 합니다.

CETAS를 사용하도록 설정할 수 있는 권한

Azure PowerShell을 통해 사용하도록 설정하려면 명령을 실행하는 사용자에게 SQL 관리형 인스턴스에 대한 기여자 또는 SQL Security Manager Azure RBAC 역할이 있어야 합니다.

이에 대한 사용자 지정 역할도 만들 수 있으므로 작업에 대한 읽기 및 쓰기 작업이 요구됩니다Microsoft.Sql/managedInstances/serverConfigurationOptions.

CETAS를 사용하도록 설정하는 메서드

컴퓨터 에서 PowerShell 명령을 호출하려면 Az 패키지 버전 9.7.0 이상이 로컬로 설치되어야 합니다. 또는 Azure Cloud Shell을 사용하여 shell.azure.com에서 Azure PowerShell을 실행하는 방법도 고려합니다.

먼저 Azure에 로그인하고 구독에 대한 적절한 컨텍스트를 설정합니다.

Login-AzAccount
$SubscriptionID = "<YourSubscriptionIDHERE>"
Select-AzSubscription -SubscriptionName $SubscriptionID

서버 구성 옵션 "allowPolybaseExport"를 관리하려면 다음 PowerShell 스크립트를 구독 및 SQL 관리형 인스턴스 이름으로 조정한 다음 명령을 실행합니다. 자세한 내용은 Set-AzSqlServerConfigurationOptionGet-AzSqlServerConfigurationOption을 참조하세요.

# Enable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 1

서버 구성 옵션 "allowPolybaseExport"를 사용하지 않도록 설정하려면:

# Disable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 0

서버 구성 옵션 "allowPolybaseExport"의 현재 값을 얻으려면 다음을 수행합니다.

# Get ServerConfigurationOptions with name "allowPolybaseExport"
Get-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport"

CETAS 상태 확인

언제든지 CETAS 구성 옵션의 현재 상태를 확인할 수 있습니다.

SQL 관리형 인스턴스에 연결합니다. 다음 T-SQL을 실행하고 응답의 value 열을 관찰합니다. 서버 구성 변경이 완료되면 이 쿼리의 결과가 원하는 설정과 일치해야 합니다.

SELECT [name], [value] FROM sys.configurations WHERE name ='allow polybase export';

문제 해결

Azure SQL Managed Instance에서 데이터 가상화 문제를 해결하는 자세한 단계는 문제 해결을 참조 하세요. Azure SQL Managed Instance의 CETAS에 대한 오류 처리 및 일반적인 오류 메시지는 다음과 같습니다.

오류 처리

CREATE EXTERNAL TABLE AS SELECT가 데이터를 텍스트로 분리된 파일로 내보내는 경우 내보내기에 실패한 행에 대한 거부 파일이 없습니다.

외부 테이블을 만들 때 데이터베이스는 외부 위치에 연결을 시도합니다. 연결이 실패하면 명령이 실패하고 외부 테이블이 만들어지지 않습니다. 데이터베이스는 연결을 최소 3회 다시 시도하므로 이 명령이 실패하려면 1분 이상 걸릴 수 있습니다.

일반적인 오류 메시지

이러한 일반적인 오류 메시지에는 Azure SQL Managed Instance용 CETAS에 대한 빠른 설명이 있습니다.

  1. 스토리지에 이미 존재하는 위치를 지정합니다.

    해결 방법: 스토리지 위치(스냅샷 포함)를 지우거나 쿼리에서 위치 매개 변수를 변경합니다.

    샘플 오류 메시지: Msg 15842: Cannot create external table. External table location already exists.

  2. JSON 개체를 사용하여 서식이 지정된 열 값입니다.

    해결 방법: 값 열을 단일 VARCHAR 또는 NVARCHAR 열 또는 명시적으로 정의된 형식의 열 집합으로 변환합니다.

    샘플 오류 메시지: Msg 16549: Values in column value are formatted as JSON objects and cannot be written using CREATE EXTERNAL TABLE AS SELECT.

  3. 위치 매개 변수가 잘못되었습니다(예: 여러 //).

    해결 방법: 위치 매개 변수를 수정합니다.

    샘플 오류 메시지: Msg 46504: External option 'LOCATION' is not valid. Ensure that the length and range are appropriate.

  4. 필요한 옵션 중 하나(DATA_SOURCE, FILE_FORMAT, LOCATION)가 없습니다.

    해결 방법: 누락된 매개 변수를 CETAS 쿼리에 추가합니다.

    샘플 오류 메시지: Msg 46505: Missing required external DDL option 'FILE_FORMAT'

  5. 액세스 문제(권한이 부족한 잘못된 자격 증명, 만료된 자격 증명 또는 자격 증명). 대체 가능성은 SQL 관리형 인스턴스가 스토리지에서 오류 404를 수신한 잘못된 경로입니다.

    해결 방법: 자격 증명 유효성 및 사용 권한을 확인합니다. 또는 경로가 유효하고 스토리지가 있는지 확인합니다. URL 경로를 adls://<container>@<storage_account>.blob.core.windows.net/<path>/사용합니다.

    샘플 오류 메시지: Msg 15883: Access check for '<Read/Write>' operation against '<Storage>' failed with HRESULT = '0x...'

  6. DATA_SOURCE 위치 부분에는 와일드카드가 포함되어 있습니다.

    해결 방법: 위치에서 와일드카드를 제거합니다.

    샘플 오류 메시지: Msg 16576: Location provided by DATA_SOURCE '<data source name>' cannot contain any wildcards.

  7. LOCATION 매개 변수의 와일드카드 수와 분할된 열 수가 일치하지 않습니다.

    해결 방법: LOCATION에서 파티션 열과 동일한 수의 와일드카드를 확인합니다.

    샘플 오류 메시지: Msg 16577: Number of wildcards in LOCATION must match the number of columns in PARTITION clause.

  8. PARTITION 절의 열 이름이 목록의 열과 일치하지 않습니다.

    해결 방법: PARTITION의 열이 유효한지 확인합니다.

    샘플 오류 메시지: Msg 16578: The column name '<column name>' specified in the PARTITION option does not match any column specified in the column list

  9. PARTITION 목록에 두 번 이상 지정된 열입니다.

    해결 방법: PARTITION 절의 열이 고유한지 확인합니다.

    샘플 오류 메시지: Msg 16579: A column has been specified more than once in the PARTITION list. Column '<column name>' is specified more than once.

  10. 열이 PARTITION 목록에서 두 번 이상 지정되었거나 SELECT 목록의 열과 일치하지 않습니다.

    해결 방법: 파티션 목록에 중복 항목이 없고 파티션 열이 SELECT 부분에 있는지 확인합니다.

    샘플 오류 메시지: Msg 11569: Column <column name> has been specified more than once in the partition columns list. Please try again with a valid parameter. 또는 Msg 11570: Column <column name> specified in the partition columns list does not match any columns in SELECT clause. Please try again with a valid parameter.

  11. PARTITION 목록의 모든 열 사용

    해결 방법: SELECT 파트의 열 중 하나 이상이 쿼리의 PARTITION 부분에 있으면 안 됩니다.

    샘플 오류 메시지: Msg 11571: All output columns in DATA_EXPORT query are declared as PARTITION columns. DATA_EXPORT query requires at least one column to export.

  12. 기능을 사용할 수 없습니다.

    해결 방법: 이 문서의 기본 사용 안 함 섹션을 사용하여 기능을 사용하도록 설정합니다.

    샘플 오류 메시지: Msg 46552: Writing into an external table is disabled. See 'https://go.microsoft.com/fwlink/?linkid=2201073' for more information

잠금

SCHEMARESOLUTION 개체에 대한 공유 잠금을 실행합니다.

A. 뷰와 함께 CETAS를 사용하여 관리 ID를 사용하여 외부 테이블 만들기

이 예제에서는 시스템 관리 ID 인증을 사용하여 뷰를 원본으로 사용하여 CETAS를 작성하기 위한 코드를 제공합니다.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

B. 보기와 함께 CETAS를 사용하여 SAS 인증을 사용하여 외부 테이블 만들기

이 예제에서는 SAS 토큰을 인증으로 사용하여 뷰를 원본으로 사용하여 CETAS를 작성하는 코드를 제공합니다.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL SAS_token
WITH
  IDENTITY = 'SHARED ACCESS SIGNATURE',
  -- Remove ? from the beginning of the SAS token
  SECRET = '<azure_shared_access_signature>' ;
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [SAS_token]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

C. 스토리지의 단일 parquet 파일에 외부 테이블 만들기

다음 두 예제에서는 로컬 테이블의 일부 데이터를 Azure Blob Storage 컨테이너의 parquet 파일로 저장된 외부 테이블로 오프로드하는 방법을 보여 줍니다. 데이터베이스와 함께 AdventureWorks2022 작동하도록 설계되었습니다. 이 예제에서는 외부 테이블을 단일 parquet 파일로 만드는 방법을 보여 줍니다. 다음 예제에서는 외부 테이블을 만들고 parquet 파일을 사용하여 여러 폴더로 분할하는 방법을 보여 줍니다.

아래 예제는 인증에 관리 ID를 사용하여 작동합니다. 따라서 Azure SQL Managed Instance 서비스 주체가 Azure Blob Storage 컨테이너에서 Storage Blob 데이터 기여자 역할을 가지고 있는지 확인합니다. 또는 예제를 수정하고 인증에 SAS(공유 액세스 비밀) 토큰을 사용할 수 있습니다.

다음 샘플에서는 2014년 1월 1일 이전 주문에 대해 테이블에서 선택하여 Azure Blob Storage의 SalesOrderHeader 단일 parquet 파일에 외부 테이블을 만듭니다.

--Example 1: Creating an external table into a single parquet file on the storage, selecting from SalesOrderHeader table for orders older than 1-Jan-2014:
USE [AdventureWorks2022]
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong Password';
GO

CREATE DATABASE SCOPED CREDENTIAL [CETASCredential]
    WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL DATA SOURCE [CETASExternalDataSource]
WITH (
    LOCATION = 'abs://container@storageaccount.blob.core.windows.net',
    CREDENTIAL = [CETASCredential] );
GO

CREATE EXTERNAL FILE FORMAT [CETASFileFormat]
WITH(
    FORMAT_TYPE=PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );
GO

-- Count how many rows we plan to offload
SELECT COUNT(*) FROM [AdventureWorks2022].[Sales].[SalesOrderHeader] WHERE
        OrderDate < '2013-12-31';

-- CETAS write to a single file, archive all data older than 1-Jan-2014:
CREATE EXTERNAL TABLE SalesOrdersExternal
WITH (
    LOCATION = 'SalesOrders/',
    DATA_SOURCE = [CETASExternalDataSource],
    FILE_FORMAT = [CETASFileFormat])
AS 
    SELECT 
        *
    FROM 
        [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';

-- you can query the newly created external table
SELECT COUNT (*) FROM SalesOrdersExternal;

D. 폴더 트리에 저장된 여러 parquet 파일로 분할된 외부 테이블 만들기

이 예제에서는 외부 테이블을 만들고 parquet 파일이 있는 여러 폴더로 분할하는 방법을 보여 주는 이전 예제를 기반으로 합니다. 데이터 집합이 큰 경우 분할된 테이블을 사용하여 성능 이점을 얻을 수 있습니다.

예제 B의 단계를 사용하여 데이터에서 SalesOrderHeader 외부 테이블을 만들지만 외부 테이블을 연도 및 월별로 OrderDate 분할합니다. 분할된 외부 테이블을 쿼리할 때 성능을 위해 파티션 제거를 활용할 수 있습니다.

--CETAS write to a folder hierarchy (partitioned table):
CREATE EXTERNAL TABLE SalesOrdersExternalPartitioned
WITH (
    LOCATION = 'PartitionedOrders/year=*/month=*/', 
    DATA_SOURCE = CETASExternalDataSource,
    FILE_FORMAT = CETASFileFormat,
    --year and month will correspond to the two respective wildcards in folder path    
    PARTITION (
        [Year],
        [Month]
        ) 
    )
AS
    SELECT
        *,
        YEAR(OrderDate) AS [Year],
        MONTH(OrderDate) AS [Month]
    FROM [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';
GO

-- you can query the newly created partitioned external table
SELECT COUNT (*) FROM SalesOrdersExternalPartitioned;

다음 단계