CREATE EXTERNAL TABLE(Transact-SQL)

외부 테이블을 만듭니다.

이 아티클에서는 원하는 SQL 제품에 대한 구문, 인수, 설명, 사용 권한 및 예제를 제공합니다.

구문 표기 규칙에 대한 자세한 내용은 Transact-SQL 구문 표기 규칙을 참조하세요.

제품 선택

다음 행에서 관심 있는 제품 이름을 선택하면 해당 제품의 정보만 표시됩니다.

* SQL Server *  

SQL 데이터베이스

Azure SQL Managed Instance

Azure Synapse
분석

Analytics Platform
시스템(PDW)

 

개요: SQL Server

이 명령은 Hadoop 클러스터나 Azure Blob Storage에 저장된 데이터를 참조하는 Hadoop 클러스터나 Azure Blob Storage PolyBase 외부 테이블에 저장된 데이터에 액세스하는 PolyBase용 외부 테이블을 만듭니다.

적용 대상: SQL Server 2016 이상

PolyBase 쿼리에 대한 외부 데이터 원본이 있는 외부 테이블을 사용합니다. 외부 데이터 원본은 연결을 설정하고 다음과 같은 기본 사용 사례를 지원하는 데 사용됩니다.

• PolyBase를 사용하여 데이터 가상화 및 데이터 로드
• BULK INSERT 또는 OPENROWSET를 통해 SQL Server 또는 SQL Database를 사용한 대량 로드 작업

CREATE EXTERNAL DATA SOURCE 및 DROP EXTERNAL TABLE도 참조하세요.

구문

-- Create a new external table
CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        [ FILE_FORMAT = external_file_format_name ]
        [ , <reject_options> [ ,...n ] ]
    )
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

인수

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

만들려는 테이블의 한 부분에서 세 부분으로 이루어진 이름입니다. 외부 테이블의 경우 SQL은 Hadoop 또는 Azure Blob Storage에서 참조되는 파일이나 폴더에 대한 기본 통계와 함께 메타데이터만 저장합니다. 실제 데이터가 이동되거나 SQL Server에 저장되지 않습니다.

중요

최상의 성능을 위해 외부 데이터 원본 드라이버가 세 부분으로 된 이름을 지원하는 경우 세 부분으로 된 이름을 제공하는 것이 좋습니다.

<column_definition> [ ,...n ]

CREATE EXTERNAL TABLE은 열 이름, 데이터 형식, Null 허용 여부 및 데이터 정렬을 구성하는 기능을 지원합니다. 외부 테이블에 DEFAULT CONSTRAINT를 사용할 수 없습니다.

데이터 형식 및 열 수를 포함한 열 정의는 외부 파일의 데이터와 일치해야 합니다. 불일치가 있는 경우 실제 데이터를 쿼리할 때 파일 행이 거부됩니다.

LOCATION = 'folder_or_filepath'

Hadoop 또는 Azure Blob Storage의 실제 데이터에 대한 폴더 또는 파일 경로 및 파일 이름을 지정합니다. 또한 S3 호환 개체 스토리지는 SQL Server 2022(16.x)부터 지원됩니다. 위치는 루트 폴더에서 시작합니다. 루트 폴더는 외부 데이터 원본에 지정된 데이터 위치입니다.

SQL Server에서는 CREATE EXTERNAL TABLE 문이 경로 및 폴더가 없으면 만듭니다. 그런 다음, INSERT INTO를 사용하여 로컬 SQL Server 테이블에서 외부 데이터 원본으로 데이터를 내보냅니다. 자세한 내용은 PolyBase 쿼리를 참조하세요.

위치(LOCATION)를 폴더로 지정할 경우, 외부 테이블에서 선택하는 PolyBase 쿼리는 폴더 및 해당 폴더의 모든 하위 폴더에서 파일을 검색합니다. Hadoop과 마찬가지로 PolyBase도 숨겨진 폴더를 반환하지 않습니다. 또한 파일 이름이 밑줄(_) 또는 마침표(.)로 시작하는 파일을 반환하지 않습니다.

다음 이미지 예제에서는 LOCATION='/webdata/'이면 PolyBase 쿼리는 mydata.txt 및 mydata2.txt에서 행을 반환합니다. mydata3.txt는 숨겨진 하위 폴더의 파일이므로 반환하지 않습니다. 그리고 _hidden.txt는 숨겨진 파일이므로 반환하지 않습니다.

기본값을 변경하고 루트 폴더에서 읽기만 하려면 core-site.xml 구성 파일에서 <polybase.recursive.traversal> 특성을 'false'로 설정합니다. 이 파일은 SQL Server의 bin 루트 아래에 <SqlBinRoot>\PolyBase\Hadoop\Conf 있습니다. 예들 들어 C:\Program Files\Microsoft SQL Server\MSSQL13.XD14\MSSQL\Binn입니다.

DATA_SOURCE = external_data_source_name

외부 데이터 위치가 포함된 외부 데이터 원본의 이름을 지정합니다. 이 위치는 HDFS(Hadoop 파일 시스템), Azure Blob Storage 컨테이너 또는 Azure Data Lake Store입니다. 외부 데이터 원본을 만들려면 CREATE EXTERNAL DATA SOURCE를 사용합니다.

FILE_FORMAT = external_file_format_name

외부 데이터에 대한 파일 형식과 압축 방법을 저장하는 외부 파일 형식 개체의 이름을 지정합니다. 외부 파일 형식을 만들려면 CREATE EXTERNAL FILE FORMAT을 사용합니다.

여러 유사한 외부 파일에서 외부 파일 형식을 다시 사용할 수 있습니다.

거부 옵션

이 옵션은 TYPE = HADOOP인 외부 데이터 원본에서만 사용할 수 있습니다.

PolyBase가 외부 데이터 원본에서 검색하는 더티 레코드를 처리하는 방법을 결정하는 거부 매개 변수를 지정할 수 있습니다. 데이터 레코드는 실제 데이터 형식 또는 열 수가 외부 테이블의 열 정의와 일치하지 않으면 '더티'로 간주됩니다.

거부 값을 지정하거나 변경하지 않으면 PolyBase는 기본값을 사용합니다. 거부 매개 변수에 관한 이 정보는 CREATE EXTERNAL TABLE 문을 사용하여 외부 테이블을 만들 때 추가 메타데이터로 저장됩니다. 후속 SELECT 문 또는 SELECT INTO SELECT 문이 외부 데이터에서 데이터를 선택하는 경우, PolyBase는 거부 옵션을 사용하여 실제 쿼리가 실패할 때까지 거부될 수 있는 행의 개수 또는 비율을 결정합니다. 이 쿼리는 거부된 임계값이 초과될 때까지 (부분) 결과를 반환합니다. 그런 다음, 적절한 오류 메시지와 함께 실패합니다.

REJECT_TYPE = value | percentage

REJECT_VALUE 옵션이 리터럴 값으로 지정되는지 또는 백분율로 지정되는지 구체화합니다.

value

REJECT_VALUE는 백분율이 아닌 리터럴 값입니다. 쿼리는 거부된 행 수가 reject_value를 초과하면 실패합니다.

예를 들어 if REJECT_VALUE = 5 및 REJECT_TYPE = value, 5개의 행이 거부된 후 SELECT 쿼리가 실패합니다.

percentage

REJECT_VALUE는 리터럴 값이 아닌 백분율입니다. 실패한 행의 백분율이 reject_value 초과하면 쿼리가 실패합니다. 실패한 행의 백분율은 일정 간격으로 계산됩니다.

REJECT_VALUE = reject_value

쿼리가 실패할 때까지 거부될 수 있는 값 또는 행의 백분율을 지정합니다.

REJECT_TYPE = value인 경우, reject_value는 0에서 2,147,483,647 사이의 정수여야 합니다.

REJECT_TYPE = percentage인 경우, reject_value는 0에서 100 사이의 부동 소수점 수이어야 합니다.

REJECT_SAMPLE_VALUE = reject_sample_value

이 특성은 REJECT_TYPE = percentage를 지정한 경우에 필요하며 PolyBase가 거부된 행의 백분율을 다시 계산하기 전까지 검색을 시도하는 행 수를 결정합니다.

reject_sample_value 매개 변수는 0에서 2,147,483,647 사이의 정수여야 합니다.

예를 들어 REJECT_SAMPLE_VALUE = 1000인 경우, PolyBase는 외부 데이터 파일에서 행 1000개 가져오기를 시도한 후 실패한 행의 백분율을 계산합니다. 실패한 행의 백분율이 reject_value보다 작은 경우, PolyBase는 또 다른 행 1000개의 검색을 시도합니다. 계속해서 추가로 행 1000개의 가져오기를 시도한 후마다 실패한 행의 백분율을 다시 계산합니다.

참고

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

예제:

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

• PolyBase가 처음 100개 행의 검색을 시도하며, 25개가 실패하고 75개가 성공합니다.
• 실패한 행의 백분율은 25%로 계산되며, 이는 거부 값 30%보다 작습니다. 결과적으로 PolyBase는 외부 데이터 원본에서 데이터를 계속 검색합니다.
• PolyBase는 다음 100개 행의 로드를 시도합니다. 이번에는 25개 행이 성공하고 75개 행이 실패합니다.
• 실패한 행의 백분율은 50%로 다시 계산됩니다. 실패한 행의 이 백분율은 30% 거부 값을 초과했습니다.
• PolyBase 쿼리는 처음 200개 행의 반환을 시도한 후 거부된 행이 50%여서 실패합니다. PolyBase 쿼리가 거부 임계값이 초과되었음을 감지하기 전에 일치하는 행이 반환되었습니다.

REJECTED_ROW_LOCATION = 디렉터리 위치

적용 대상: SQL Server 2019 CU6 이상 버전, Azure Synapse Analytics.

거부된 행과 해당 오류 파일을 작성해야 하는 외부 데이터 원본 내 디렉터리를 지정합니다.

지정된 경로가 존재하지 않을 경우 PolyBase는 사용자를 대신하여 경로를 만듭니다. "_rejectedrows"라는 이름의 자식 디렉터리가 생성됩니다. "_" 문자는 위치 매개 변수에 명시적으로 명명되지 않는 한 다른 데이터 처리를 위해 디렉터리를 이스케이프합니다. 이 디렉터리 내에는 로드 제출 시간을 기준으로 형식(예20230330-173205: )으로 만든 폴더가 YearMonthDay -HourMinuteSecond 있습니다. 이 폴더에서 _reason 파일과 data 파일이라는 두 가지 파일에 데이터가 기록됩니다. 이 옵션은 TYPE = HADOOP인 외부 데이터 원본 및 DELIMITEDTEXT FORMAT_TYPE을 사용하는 외부 테이블에서만 사용할 수 있습니다. 자세한 내용은 CREATE EXTERNAL DATA SOURCE 및 CREATE EXTERNAL FILE FORMAT을 참조하세요.

reason 파일과 data 파일에는 모두 CTAS 문과 연결된 queryID가 있습니다. data와 reason은 별도의 파일에 있으므로 해당 파일의 접미사가 일치해야 합니다.

사용 권한

다음과 같은 권한이 필요합니다.

• CREATE TABLE
• ALTER ANY SCHEMA
• ALTER ANY EXTERNAL DATA SOURCE
• ALTER ANY EXTERNAL FILE FORMAT(Hadoop 및 Azure Storage 외부 데이터 원본에만 적용됨)
• CONTROL DATABASE(Hadoop 및 Azure Storage 외부 데이터 원본에만 적용됨)

CREATE EXTERNAL TABLE 명령에서 사용하는 DATABASE SCOPED CREDENTIAL에 지정된 원격 로그인에는 LOCATION 매개 변수에 지정된 외부 데이터 원본의 경로/테이블/컬렉션에 대한 읽기 원한이 있어야 합니다. 이 EXTERNAL TABLE을 사용하여 Hadoop 또는 Azure Storage 외부 데이터 원본으로 데이터를 내보내려는 경우에는, 지정된 로그인에 LOCATION에 지정된 경로에 대한 쓰기 권한이 있어야 합니다. Hadoop은 현재 SQL Server 2022(16.x)에서 지원되지 않습니다.

Azure Blob Storage 경우, Azure Portal, Azure Blob Storage 또는 ADLS Gen2 스토리지 계정에서 액세스 키 및 SAS(공유 액세스 서명)를 구성할 때는 최소한 읽기 및 쓰기 권한을 부여하도록 허용된 권한을 구성해야 합니다. 여러 폴더를 검색할 때는 목록 권한이 필요할 수도 있습니다. 또한 컨테이너와 개체 모두를 허용되는 리소스 유형으로 선택해야 합니다.

중요

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

오류 처리

CREATE EXTERNAL TABLE 문을 실행하는 동안 PolyBase는 외부 데이터 원본에 연결을 시도합니다. 연결 시도가 실패하면 이 명령문이 실패하고 외부 테이블이 만들어지지 않습니다. PolyBase는 쿼리가 결국 실패할 때까지 연결을 다시 시도하므로 이 명령이 실패하려면 1분 이상 걸릴 수 있습니다.

설명

SELECT FROM EXTERNAL TABLE과 같은 임시 쿼리 시나리오에서 PolyBase는 외부 데이터 원본에서 검색된 행을 임시 테이블에 저장합니다. 쿼리가 완료된 후 PolyBase는 임시 테이블을 제거하고 삭제합니다. 영구적 데이터가 SQL 테이블에 저장되지 않습니다.

이와는 대조적으로 SELECT INTO FROM EXTERNAL TABLE과 같은 가져오기 시나리오에서 PolyBase는 외부 데이터 원본에서 검색된 행을 영구 데이터로 SQL 테이블에 저장합니다. 새 테이블은 PolyBase에서 외부 데이터를 검색할 때 쿼리 실행 중에 만들어집니다.

PolyBase는 쿼리 성능을 향상시키기 위해 쿼리 계산 중 일부를 Hadoop에 푸시할 수 있습니다. 이 작업을 조건자 푸시 다운이라 합니다. 이를 활성화하려면 CREATE EXTERNAL DATA SOURCE에 Hadoop 리소스 관리자 위치 옵션을 지정합니다.

동일하거나 서로 다른 외부 데이터 원본을 참조하는 많은 외부 테이블을 만들 수 있습니다.

제한 사항

외부 테이블의 데이터가 SQL Server의 직접 관리 제어를 받고 있지 않으므로 언제든지 외부 프로세스에 의해 변경 또는 제거될 수 있습니다. 따라서 외부 테이블에 대한 쿼리 결과는 결정적인 것으로 보증되지 않습니다. 즉, 외부 테이블에 대해 실행할 때마다 같은 쿼리가 서로 다른 결과를 반환할 수 있습니다. 마찬가지로 외부 데이터를 이동하거나 제거하면 쿼리가 실패할 수 있습니다.

각각 서로 다른 외부 데이터 원본을 참조하는 여러 개의 외부 테이블을 만들 수 있습니다. 서로 다른 Hadoop 데이터 원본에 대해 동시에 쿼리를 실행하는 경우 각 Hadoop 원본은 동일한 'hadoop 연결' 서버 구성 설정을 사용해야 합니다. 예를 들어 Cloudera Hadoop 클러스터 및 Hortonworks Hadoop 클러스터는 서로 다른 구성 설정을 사용하므로 이들에 대해 하나의 쿼리를 동시에 실행할 수 없습니다. 구성 설정 및 지원되는 결합에 대해서는 PolyBase 연결 구성을 참조하세요.

외부 테이블이 데이터 형식으로 사용하거나 DELTA 데이터 형식으로 사용하는 PARQUETCSVDELIMITEDTEXT경우 외부 테이블은 명령당 CREATE STATISTICS 하나의 열에 대한 통계만 지원합니다.

다음 DDL(Data Definition Language) 문은 외부 테이블에 대해 허용됩니다.

• CREATE TABLE 및 DROP TABLE
• CREATE STATISTICS 및 DROP STATISTICS
• CREATE VIEW 및 DROP VIEW

지원되지 않는 구문 및 작업:

• 외부 테이블 열에 대한 DEFAULT 제약 조건
• 삭제, 삽입 및 업데이트의 DML(데이터 조작 언어) 작업

쿼리 제한 사항

PolyBase는 32개의 동시 PolyBase 쿼리를 실행할 때 폴더당 최대 3만 3천 개의 파일을 사용할 수 있습니다. 이 최대 개수는 각 HDFS 폴더의 파일과 하위 폴더를 모두 포함합니다. 동시성 수준이 32보다 작은 경우 사용자는 33k보다 많은 파일을 포함하는 HDFS의 폴더에 대해 PolyBase 쿼리를 실행할 수 있습니다. 외부 파일 경로를 짧게 유지하고 HDFS 폴더당 30k 이하의 파일을 사용하는 것이 좋습니다. 너무 많은 파일이 참조되면 JVM(Java Virtual Machine) 메모리 부족 예외가 발생할 수 있습니다.

테이블 너비 제한 사항

SQL Server 2016의 PolyBase에는 테이블 정의에 의해 유효한 단일 행의 최대 크기를 기반으로 32KB의 행 너비 한도가 적용됩니다. 열 스키마의 합계가 32KB보다 큰 경우 PolyBase는 데이터를 쿼리할 수 없습니다.

데이터 형식 제한 사항

다음 데이터 형식은 PolyBase 외부 테이블에 사용할 수 없습니다.

• geography
• geometry
• hierarchyid
• image
• text
• nText
• xml
• 모든 사용자 정의 형식

데이터 원본 관련 제한 사항

Oracle

Oracle 동의어의 경우 PolyBase 사용이 지원되지 않습니다.

배열을 포함하는 MongoDB 컬렉션 외부 테이블

배열을 포함하는 MongoDB 컬렉션 외부 테이블을 만들려면 Azure Data Studio용 데이터 가상화 확장을 사용하여 MongoDB용 PolyBase ODBC 드라이버에 의해 감지된 스키마를 기준으로 CREATE EXTERNAL TABLE 문을 생성해야 합니다. 평면화 작업은 드라이버에 의해 자동으로 수행됩니다. 또는 sp_data_source_objects(Transact-SQL)를 사용하여 컬렉션 스키마(열)를 감지하고 외부 테이블을 수동으로 만들 수도 있습니다. sp_data_source_table_columns 저장 프로시저도 MongoDB 드라이버용 PolyBase ODBC 드라이버를 통해 자동으로 평면화를 수행합니다. Azure Data Studio용 데이터 가상화 확장 및 sp_data_source_table_columns는 동일한 내부 저장 프로시저를 사용하여 외부 스키마를 쿼리합니다.

잠금

SCHEMARESOLUTION 개체에 대한 공유 잠금입니다.

보안

외부 테이블에 대한 데이터 파일은 Hadoop 또는 Azure Blob Storage에 저장됩니다. 이러한 데이터 파일은 사용자 고유의 프로세스에 의해 만들어지고 관리됩니다. 외부 데이터의 보안을 관리하는 것은 사용자의 책임입니다.

예제

A. 텍스트로 구분된 형식의 데이터를 사용하여 외부 테이블 만들기

이 예제에서는 텍스트로 구분된 파일 형식의 데이터를 가진 외부 테이블을 만드는 데 필요한 모든 단계를 보여 줍니다. 즉, 외부 데이터 원본 mydatasource 및 외부 파일 형식 myfileformat을 만듭니다. 이러한 데이터베이스 수준 개체는 나중에 CREATE EXTERNAL TABLE 문에 참조됩니다. 자세한 내용은 CREATE EXTERNAL DATA SOURCE 및 CREATE EXTERNAL FILE FORMAT을 참조하세요.

CREATE EXTERNAL DATA SOURCE mydatasource
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (FIELD_TERMINATOR ='|')
);

CREATE EXTERNAL TABLE ClickStream (
    url varchar(50),
    event_date date,
    user_IP varchar(50)
)
WITH (
        LOCATION='/webdata/employee.tbl',
        DATA_SOURCE = mydatasource,
        FILE_FORMAT = myfileformat
    )
;

B. RCFile 형식의 데이터를 사용하여 외부 테이블 만들기

이 예제에서는 RCFile 형식의 데이터를 가진 외부 테이블을 만드는 데 필요한 모든 단계를 보여 줍니다. 즉, 외부 데이터 원본 mydatasource_rc 및 외부 파일 형식 myfileformat_rc를 만듭니다. 이러한 데이터베이스 수준 개체는 나중에 CREATE EXTERNAL TABLE 문에 참조됩니다. 자세한 내용은 CREATE EXTERNAL DATA SOURCE 및 CREATE EXTERNAL FILE FORMAT을 참조하세요.

CREATE EXTERNAL DATA SOURCE mydatasource_rc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_rc
WITH (
    FORMAT_TYPE = RCFILE,
    SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
)
;

CREATE EXTERNAL TABLE ClickStream_rc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/employee_rc.tbl',
        DATA_SOURCE = mydatasource_rc,
        FILE_FORMAT = myfileformat_rc
    )
;

C. ORC 형식의 데이터를 사용하여 외부 테이블 만들기

이 예제에서는 ORC 형식의 데이터를 가진 외부 테이블을 만드는 데 필요한 모든 단계를 보여 줍니다. 즉, 외부 데이터 원본 mydatasource_orc와 외부 파일 형식 myfileformat_orc를 정의합니다. 이러한 데이터베이스 수준 개체는 나중에 CREATE EXTERNAL TABLE 문에 참조됩니다. 자세한 내용은 CREATE EXTERNAL DATA SOURCE 및 CREATE EXTERNAL FILE FORMAT을 참조하세요.

CREATE EXTERNAL DATA SOURCE mydatasource_orc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_orc
WITH (
    FORMAT = ORC,
    COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)
;

CREATE EXTERNAL TABLE ClickStream_orc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/',
        DATA_SOURCE = mydatasource_orc,
        FILE_FORMAT = myfileformat_orc
    )
;

D. Hadoop 데이터 쿼리

ClickStream은 Hadoop 클러스터의 구분된 텍스트 파일 employee.tbl에 연결하는 외부 테이블입니다. 다음 쿼리는 표준 테이블에 대한 쿼리와 유사해 보입니다. 그러나 이 쿼리는 Hadoop에서 데이터를 검색한 다음, 결과를 계산합니다.

SELECT TOP 10 (url) FROM ClickStream WHERE user_ip = 'xxx.xxx.xxx.xxx';

E. Hadoop 데이터를 SQL 데이터와 조인

이 쿼리는 SQL 테이블 두 개에 대한 표준 JOIN과 유사해 보입니다. 차이점은 PolyBase가 Hadoop에서 clickstream 데이터를 검색한 다음, UrlDescription 테이블에 조인한다는 것입니다. 한 테이블은 외부 테이블이며 다른 테이블은 표준 SQL 테이블입니다.

SELECT url.description
FROM ClickStream cs
JOIN UrlDescription url ON cs.url = url.name
WHERE cs.url = 'msdn.microsoft.com';

F. SQL 테이블로 Hadoop의 데이터 가져오기

이 예제에서는 표준 SQL 테이블 user와 외부 테이블 ClickStream 간 조인의 결과를 영구적으로 저장하는 새 SQL 테이블 ms_user를 만듭니다.

SELECT DISTINCT user.FirstName, user.LastName
INTO ms_user
FROM user INNER JOIN (
    SELECT * FROM ClickStream WHERE cs.url = 'www.microsoft.com'
    ) AS ms
ON user.user_ip = ms.user_ip;

G. SQL Server용 외부 테이블 만들기

데이터베이스 범위 자격 증명을 만들기 전에 자격 증명을 보호하려면 사용자 데이터베이스에 마스터 키가 있어야 합니다. 자세한 내용은 CREATE MASTER KEY 및 CREATE DATABASE SCOPED CREDENTIAL을 참조하세요.

     -- Create a Master Key
      CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';
    GO
     /*  specify credentials to external data source
     *  IDENTITY: user name for external source.
     *  SECRET: password for external source.
     */
     CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials
     WITH IDENTITY = 'username', Secret = 'password';
    GO

다음과 같은 SQLServerInstance 새 외부 데이터 원본 및 sqlserver.customer 외부 테이블을 만듭니다.

    /* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE SQLServerInstance
    WITH (
    LOCATION = 'sqlserver://SqlServer',
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = SQLServerCredentials
    );
    GO

    CREATE SCHEMA sqlserver;
    GO

     /* LOCATION: sql server table/view in 'database_name.schema_name.object_name' format
     * DATA_SOURCE: the external data source, created above.
     */
     CREATE EXTERNAL TABLE sqlserver.customer(
     C_CUSTKEY INT NOT NULL,
     C_NAME VARCHAR(25) NOT NULL,
     C_ADDRESS VARCHAR(40) NOT NULL,
     C_NATIONKEY INT NOT NULL,
     C_PHONE CHAR(15) NOT NULL,
     C_ACCTBAL DECIMAL(15,2) NOT NULL,
     C_MKTSEGMENT CHAR(10) NOT NULL,
     C_COMMENT VARCHAR(117) NOT NULL
      )
      WITH (
      LOCATION='tpch_10.dbo.customer',
      DATA_SOURCE=SqlServerInstance
     );

9\. Oracle용 외부 테이블 만들기

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

   /*
   * LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
   * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
   * CONNECTION_OPTIONS: Specify driver location
   * CREDENTIAL: the database scoped credential, created above.
   */
   CREATE EXTERNAL DATA SOURCE external_data_source_name
   WITH (
     LOCATION = 'oracle://<server address>[:<port>]',
     -- PUSHDOWN = ON | OFF,
     CREDENTIAL = credential_name)

   /*
   * LOCATION: Oracle table/view in '<database_name>.<schema_name>.<object_name>' format. Note this may be case sensitive in the Oracle database.
   * DATA_SOURCE: the external data source, created above.
   */
   CREATE EXTERNAL TABLE customers(
   [O_ORDERKEY] DECIMAL(38) NOT NULL,
   [O_CUSTKEY] DECIMAL(38) NOT NULL,
   [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
   [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
   [O_ORDERDATE] DATETIME2(0) NOT NULL,
   [O_ORDERPRIORITY] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_CLERK] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_SHIPPRIORITY] DECIMAL(38) NOT NULL,
   [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
   )
   WITH (
    LOCATION='DB1.mySchema.customer',
    DATA_SOURCE= external_data_source_name
   );

J. Teradata용 외부 테이블 만들기

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

    /* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CONNECTION_OPTIONS: Specify driver location
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = teradata://<server address>[:<port>],
   -- PUSHDOWN = ON | OFF,
    CREDENTIAL =credential_name
    );


     /* LOCATION: Teradata table/view in '<database_name>.<object_name>' format
      * DATA_SOURCE: the external data source, created above.
      */
     CREATE EXTERNAL TABLE customer(
      L_ORDERKEY INT NOT NULL,
      L_PARTKEY INT NOT NULL,
     L_SUPPKEY INT NOT NULL,
     L_LINENUMBER INT NOT NULL,
     L_QUANTITY DECIMAL(15,2) NOT NULL,
     L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
     L_DISCOUNT DECIMAL(15,2) NOT NULL,
     L_TAX DECIMAL(15,2) NOT NULL,
     L_RETURNFLAG CHAR NOT NULL,
     L_LINESTATUS CHAR NOT NULL,
     L_SHIPDATE DATE NOT NULL,
     L_COMMITDATE DATE NOT NULL,
     L_RECEIPTDATE DATE NOT NULL,
     L_SHIPINSTRUCT CHAR(25) NOT NULL,
     L_SHIPMODE CHAR(10) NOT NULL,
     L_COMMENT VARCHAR(44) NOT NULL
     )
     WITH (
     LOCATION='customer',
     DATA_SOURCE= external_data_source_name
     );

11. MongoDB용 외부 테이블 만들기

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

     /* LOCATION: Location string should be of format '<type>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CONNECTION_OPTIONS: Specify driver location
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = mongodb://<server>[:<port>],
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = credential_name
    );

     /* LOCATION: MongoDB table/view in '<database_name>.<schema_name>.<object_name>' format
     * DATA_SOURCE: the external data source, created above.
     */
     CREATE EXTERNAL TABLE customers(
     [O_ORDERKEY] DECIMAL(38) NOT NULL,
     [O_CUSTKEY] DECIMAL(38) NOT NULL,
     [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
     [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
     [O_ORDERDATE] DATETIME2(0) NOT NULL,
     [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
     )
     WITH (
     LOCATION='customer',
     DATA_SOURCE= external_data_source_name
     );

12. 외부 테이블을 통해 S3 호환 개체 스토리지 쿼리

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

다음 예제에서는 T-SQL을 사용하여 외부 테이블 쿼리를 통해 S3 호환 개체 스토리지에 저장된 parquet 파일을 쿼리하는 방법을 보여 줍니다. 이 샘플에서는 외부 데이터 원본 내의 상대 경로를 사용합니다.

CREATE EXTERNAL DATA SOURCE s3_ds
WITH
(   LOCATION = 's3://<ip_address>:<port>/'
,   CREDENTIAL = s3_dc
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
GO
CREATE EXTERNAL TABLE Region(
r_regionkey BIGINT,
r_name CHAR(25),
r_comment VARCHAR(152) )
WITH (LOCATION = '/region/', DATA_SOURCE = 's3_ds',
FILE_FORMAT = ParquetFileFormat);
GO

다음 단계

다음 문서에서 관련 개념에 대해 자세히 알아보세요.

• CREATE EXTERNAL DATA SOURCE
• CREATE EXTERNAL FILE FORMAT
• sp_data_source_objects(Transact-SQL)
• Azure Data Studio용 데이터 가상화 확장