CREATE EXTERNAL DATA SOURCE(Transact-SQL)

SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, 분석 플랫폼 시스템(PDW) 또는 Azure SQL Edge를 사용하여 쿼리용 외부 데이터 원본을 만듭니다.

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

제품 선택

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

* SQL Server *  

SQL 데이터베이스

SQL Managed
인스턴스

Azure Synapse
분석

Analytics Platform
시스템(PDW)

 

개요: SQL Server 2022

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

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

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

참고

이 구문은 SQL Server 버전마다 다릅니다. 버전 선택기 드롭다운을 사용하여 적절한 버전을 선택합니다. 이 콘텐츠는 SQL Server 2022(16.x) 이상에 적용됩니다.

SQL Server 2022 이상 구문

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

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] PUSHDOWN = { ON | OFF } ]
  )
[ ; ]

인수

data_source_name

데이터 원본에 대한 사용자 정의 이름을 지정합니다. 이름은 SQL Server의 데이터베이스 내에서 반드시 고유해야 합니다.

LOCATION = '<prefix>://<path[:port]>'

외부 데이터 원본에 대한 연결 프로토콜 및 경로를 제공합니다.

외부 데이터 원본	커넥터 위치 접두사	위치 경로	제품 / 서비스별로 지원되는 위치	인증
Azure Storage 계정(V2)	abs	abs://<container_name>@<storage_account_name>.blob.core.windows.net/ 또는 abs://<storage_account_name>.blob.core.windows.net/<container_name>	SQL Server 2022(16.x)부터 계층 구조 네임스페이스가 지원됩니다.	공유 액세스 서명(SAS)
Azure Data Lake Storage Gen2	adls	adls://<container_name>@<storage_account_name>.dfs.core.windows.net/ 또는 adls://<storage_account_name>.dfs.core.windows.net/<container_name>	SQL Server 2022(16.x)부터	공유 액세스 서명(SAS)
SQL Server	sqlserver	<server_name>[\<instance_name>][:port]	SQL Server 2019(15.x)부터	SQL 인증 전용
Oracle	oracle	<server_name>[:port]	SQL Server 2019(15.x)부터	기본 인증 전용입니다.
Teradata	teradata	<server_name>[:port]	SQL Server 2019(15.x)부터	기본 인증 전용입니다.
MongoDB 또는 Cosmos DB API for MongoDB	mongodb	<server_name>[:port]	SQL Server 2019(15.x)부터	기본 인증 전용입니다.
일반 ODBC	odbc	<server_name>[:port]	SQL Server 2019(15.x)부터 - Windows만 해당	기본 인증 전용입니다.
대량 작업	https	<storage_account>.blob.core.windows.net/<container>	SQL Server 2017(14.x)부터	공유 액세스 서명(SAS)
S3 호환 개체 스토리지	s3	- S3 호환: s3://<server_name>:<port>/ - AWS S3: s3://<bucket_name>.S3.amazonaws.com[:port]/<folder> 또는 s3://s3.amazonaws.com[:port]/<bucket_name>/<folder>	SQL Server 2022(16.x)부터	기본 또는 통과(STS) *

* IDENTITY가 하드 코딩 IDENTITY = 'S3 Access Key' 되고 SECRET 인수가 STS(통과) 권한 부여 형식 = '<AccessKeyID>:<SecretKeyID>' 이거나 사용하는 데이터베이스 범위 자격 증명이어야 합니다. 자세한 내용은 S3 호환 개체 스토리지의 외부 데이터에 액세스하도록 PolyBase 구성을 참조하세요.

위치 경로:

• port = 외부 데이터 원본이 수신 대기 중인 포트입니다. 대부분의 경우 네트워크 구성에 따라 선택 사항입니다.
• <container_name> = 데이터를 보관하는 스토리지 계정의 컨테이너입니다. 루트 컨테이너는 읽기 전용이므로 데이터를 컨테이너에 다시 쓸 수 없습니다.
• <storage_account> = Azure 리소스의 스토리지 계정 이름입니다.
• <server_name> = 호스트 이름입니다.
• <instance_name> = SQL Server 명명된 인스턴스의 이름입니다. 대상 인스턴스에서 SQL Server Browser Service가 실행 중인 경우에 사용됩니다.
• <ip_address>:<port> = S3 호환 개체 스토리지인 경우에만(SQL Server 2022(16.x)부터) S3 호환 스토리지에 연결하는 데 사용되는 엔드포인트 및 포트입니다.
• <bucket_name> = 스토리지 플랫폼과 관련된 S3 호환 개체 스토리지(SQL Server 2022(16.x)부터)만 해당합니다.
• <region> = 스토리지 플랫폼과 관련된 S3 호환 개체 스토리지(SQL Server 2022(16.x)부터)만 해당합니다.
• <folder> = 스토리지 URL 내의 스토리지 경로의 일부입니다.

위치 설정 시 추가 참고 사항 및 지침:

• SQL Server 데이터베이스 엔진은 개체가 생성될 때 외부 데이터 원본이 존재하는지 확인하지 않습니다. 유효성을 검사하려면 외부 데이터 원본을 사용하여 외부 테이블을 만듭니다.
• sqlserver 커넥터를 사용하여 SQL Server 2019(15.x)를 다른 SQL Server 또는 Azure SQL Database에 연결할 수 있습니다.
• ODBC를 통해 연결할 때 Driver={<Name of Driver>}를 지정합니다.
• adls 접두사를 사용하는 Azure Storage 계정(V2)의 계층 구조 네임스페이스 옵션은 SQL Server 2022(16.x)에서 Azure Data Lake Storage Gen2를 통해 지원됩니다.

• HDFS Cloudera(CDP) 및 Hortonworks(HDP) 외부 데이터 원본에 대한 SQL Server 지원은 사용 중지되며 SQL Server 2022(16.x)에 포함되지 않습니다. SQL Server 2022(16.x)에서는 TYPE 인수를 사용할 필요가 없습니다.
• SQL Server 2022(16.x)부터 S3 호환 개체 스토리지 및 PolyBase에 대한 자세한 내용은 S3 호환 개체 스토리지의 외부 데이터에 액세스하도록 PolyBase 구성을 참조하세요. S3 호환 개체 스토리지 내에서 parquet 파일을 쿼리하는 예제는 PolyBase를 사용하여 S3 호환 개체 스토리지에서 parquet 파일 가상화를 참조하세요.
• 이전 버전과 달리 SQL Server 2022(16.x)에서 Azure Storage 계정(v2)에 사용되는 접두사는 wasb[s]에서 abs로 변경되었습니다.
• 이전 버전과 달리 SQL Server 2022(16.x)에서 Azure Data Lake Storage Gen2에 사용되는 접두사는 abfs[s]에서 adls로 변경되었습니다.
• PolyBase를 사용하여 Azure Storage에서 CSV 파일을 가상화하는 예제는 PolyBase를 사용하여 CSV 파일 가상화를 참조하세요.
• PolyBase를 사용하여 ADLS Gen2에서 델타 테이블을 가상화하는 예제는 PolyBase를 사용하여 델타 테이블 가상화를 참조하세요.
• SQL Server 2022(16.x)는 Azure Storage 계정 v2() 및 Azure Data Lake Gen2adls(abs)에 대해 두 개의 URL 형식을 완전히 지원합니다.
  • LOCATION 경로는 형식 <container>@<storage_account_name>.. (권장) 또는 <storage_account_name>../<container>.를 사용할 수 있습니다. 예:
    • Azure Storage 계정 v2: abs://<container>@<storage_account_name>.blob.core.windows.net (권장) 또는 abs://<storage_account_name>.blob.core.windows.net/<container>.
    • Azure Data Lake Gen2는 다음을 지원합니다( adls://<container>@<storage_account_name>.blob.core.windows.net 권장) 또는 adls://<storage_account_name>.dfs.core.windows.net/<container>.

CONNECTION_OPTIONS = key_value_pair

SQL Server 2019(15.x) 이상에 지정됩니다. ODBC를 외부 데이터 원본에 연결할 때 추가 옵션을 지정합니다. 여러 연결 옵션을 사용하려면 세미콜론으로 구분합니다.

SQL Server, Oracle, Teradata, MongoDB 및 Azure Cosmos DB API for MongoDB용으로 기본 제공되는 ODBC 커넥터뿐만 아니라 일반 ODBC 연결에 적용됩니다.

key_value_pair는 키워드이며 특정 연결 옵션 값입니다. 사용 가능한 키워드 및 값은 외부 데이터 원본 유형에 따라 달라집니다. 최소한 드라이버 이름이 필요하지만, 설정하면 유용하고 문제 해결에 도움이 되는 APP='<your_application_name>', ApplicationIntent= ReadOnly|ReadWrite 등의 다른 옵션도 있습니다.

가능한 키 값 쌍은 드라이버에 따라 다릅니다. 각 공급자에 대한 자세한 내용은 외부 데이터 원본 만들기(Transact-SQL) 연결 옵션을 참조하세요.

적용 대상: SQL Server 2022(16.x) 누적 업데이트 2부터 Oracle TNS 파일을 지원하기 위한 추가 키워드가 도입되었습니다.

• 키워드 TNSNamesFile 는 Oracle 서버에 있는 파일에 대한 파일 경로를 tnsnames.ora 지정합니다.
• 키워드 ServerName 는 호스트 이름과 포트를 바꾸는 데 사용할 내부 tnsnames.ora 별칭을 지정합니다.

PUSHDOWN = ON | OFF

적용 대상: SQL Server 2019(15.x) 이상. 외부 데이터 원본에 푸시 다운할 수 있는지 여부를 알려줍니다. 기본적으로 설정되어 있습니다.

PUSHDOWN은 외부 데이터 원본 수준에서 SQL Server, Oracle, Teradata, MongoDB, Azure Cosmos DB API for MongoDB 또는 ODBC에 연결할 때 지원됩니다.

쿼리 수준에서 푸시 다운을 활성화 또는 비활성화는 힌트를 통해 구현됩니다.

CREDENTIAL = credential_name

외부 데이터 원본에 대해 인증하기 위한 데이터베이스 범위 자격 증명을 지정합니다.

자격 증명 생성 시 추가 참고 사항 및 지침:

• CREDENTIAL은 데이터 보안이 설정된 경우에만 필요합니다. 익명 액세스를 허용하는 데이터 세트에는 CREDENTIAL이 필요하지 않습니다.
• Azure Storage 계정(V2) 또는 Azure Data Lake Storage Gen2 액세스할 때 IDENTITY는 SHARED ACCESS SIGNATURE여야 합니다.
  • 예제는 대량 작업을 실행하고 Azure Storage에서 SQL Database로 데이터를 검색하기 위한 외부 데이터 원본 만들기를 참조하세요.

공유 액세스 서명을 만드는 방법에는 여러 가지가 있습니다.

• Azure Portal -><Your_Storage_Account> -> 공유 액세스 서명 -> 권한 구성 -> SAS 및 연결 문자열 생성으로 이동하여 SAS 토큰을 만들 수 있습니다. 자세한 내용은 공유 액세스 서명 생성을 참조하세요.

• Azure Storage Explorer를 사용하여 SAS를 만들고 구성할 수 있습니다.

• PowerShell, Azure CLI, .NET 및 REST API를 통해 프로그래밍 방식으로 SAS를 만들 수 있습니다. 자세한 내용은 SAS(공유 액세스 서명)를 사용하여 Azure Storage 리소스에 대한 제한된 액세스 권한 부여를 참조하세요.

• SAS 토큰은 다음과 같이 구성되어야 합니다.

  • SAS 토큰이 생성되면 토큰의 시작 부분에 물음표('?')가 포함됩니다. 비밀로 구성된 경우 앞에 오는 ? 제외
  • 유효한 만료 기간을 사용합니다(모든 날짜는 UTC 시간임).
  • 로드해야 하는 파일에 대해 적어도 읽기 권한을 승인해야 합니다(예: srt=o&sp=r). 다양한 사용 사례에 대해 여러 공유 액세스 서명을 만들 수 있습니다. 권한은 다음과 같이 부여되어야 합니다.

  작업	사용 권한
  파일에서 데이터 읽기	읽기
  여러 파일 및 하위 폴더에서 데이터를 읽습니다.	읽기 및 목록
  CETAS(Create External Table as Select) 사용	읽기, 만들기, 나열 및 쓰기

• Azure Blob Storage 및 Azure Data Lake Gen 2의 경우:

  • 허용되는 서비스: Blob SAS 토큰을 생성하려면 선택해야 합니다.
  • 허용되는 리소스 종류: Container Object SAS 토큰을 생성하려면 선택해야 합니다.

S3 호환 개체 스토리지 및 PolyBase에서 CREDENTIAL을 사용하는 예제는 S3 호환 개체 스토리지의 외부 데이터에 액세스하도록 PolyBase 구성을 참조하세요.

데이터베이스 범위 지정 자격 증명을 만들려면 CREATE DATABASE SCOPED CREDENTIAL(Transact-SQL)을 참조하세요.

사용 권한

SQL Server의 데이터베이스에 대한 CONTROL 권한이 필요합니다.

잠금

EXTERNAL DATA SOURCE 개체에 대한 공유 잠금을 수행합니다.

보안

PolyBase는 대부분의 외부 데이터 원본에 대해 프록시 기반 인증을 지원합니다. 데이터베이스 범위 자격 증명을 생성하여 프록시 계정을 만듭니다.

SQL Server 2022로 업그레이드

2022년 SQL Server(16.x)부터 Hadoop 외부 데이터 원본은 지원되지 않습니다. TYPE = HADOOP를 이용해 이전에 만든 외부 데이터 원본 및 이 외부 데이터 원본을 사용하는 외부 테이블을 수동으로 다시 만들어야 합니다.

또한 사용자는 Azure Storage에 연결할 때 새 커넥터를 사용하도록 외부 데이터 원본도 구성해야 합니다.

외부 데이터 원본	시작	대상
Azure Blob Storage	wasb[s]	abs
ADLS Gen2	abfs[s]	adls

예

중요

PolyBase를 설치하고 사용하도록 설정하는 방법에 대한 자세한 내용은 Windows에 PolyBase 설치를 참조하세요.

A. Oracle을 참조하는 SQL Server에서 외부 데이터 원본 만들기

Oracle을 참조하는 외부 데이터 원본을 만들려면 데이터베이스 범위 자격 증명이 있는지 확인합니다. 이 데이터 원본에 대한 계산 푸시 다운을 활성화하거나 비활성화할 수도 있습니다.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL OracleProxyAccount
    WITH IDENTITY = 'oracle_username',
    SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE MyOracleServer
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    CREDENTIAL = OracleProxyAccount,
    PUSHDOWN = ON
);

필요에 따라 Oracle에 대한 외부 데이터 원본은 프록시 인증을 사용하여 세분화된 액세스 제어를 제공할 수 있습니다. 가장된 사용자에 비해 제한된 액세스 권한을 갖도록 프록시 사용자를 구성할 수 있습니다.

CREATE DATABASE SCOPED CREDENTIAL [OracleProxyCredential]
    WITH IDENTITY = 'oracle_username',
    SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    CONNECTION_OPTIONS = 'ImpersonateUser=%CURRENT_USER',
    CREDENTIAL = [OracleProxyCredential]
);

또는 TNS를 사용하여 인증할 수 있습니다.

적용 대상: SQL Server 2022(16.x) 누적 업데이트 2 CREATE EXTERNAL DATA SOURCE 부터 이제 Oracle에 연결할 때 TNS 파일 사용을 지원합니다. 매개 변수가 CONNECTION_OPTIONS 확장되어 이제 파일을 찾아보 tnsnames.ora 고 ServerName 서버와의 연결을 설정하는 변수로 사용됩니다TNSNamesFile.

아래 예제에서 런타임 동안 SQL Server는 지정된 TNSNamesFile 파일 위치를 검색 tnsnames.ora 하고 지정된 호스트 및 네트워크 포트ServerName를 검색합니다.

CREATE EXTERNAL DATA SOURCE [external_data_source_name]
WITH (
    LOCATION = N'oracle://XE',
    CREDENTIAL = [OracleCredentialTest],
    CONNECTION_OPTIONS = N'TNSNamesFile=C:\Temp\tnsnames.ora;ServerName=XE'
);

B. Polybase 연결을 통해 SQL Server 명명된 인스턴스를 참조하는 외부 데이터 원본 만들기

적용 대상: SQL Server 2019(15.x) 이상

CONNECTION_OPTIONS의 명명된 인스턴스를 참조하는 외부 데이터 원본을 만들려면 SQL Server를 사용하여 인스턴스 이름을 지정합니다.

먼저 데이터베이스 범위 자격 증명을 만들고 SQL 인증된 로그인에 대한 자격 증명을 저장합니다. PolyBase용 SQL ODBC 커넥터는 기본 인증만 지원하고 Kerberos 인증은 지원하지 않습니다. 데이터베이스 범위 자격 증명을 만들기 전에 자격 증명을 보호하려면 데이터베이스에 마스터 키가 있어야 합니다. 자세한 내용은 CREATE MASTER KEY를 참조하세요. 다음 샘플에서는 데이터베이스 범위 자격 증명을 만들고 사용자 고유의 로그인 및 암호를 제공합니다.

CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
    WITH IDENTITY = 'username',
    SECRET = 'password';

다음 예제 WINSQL2019 에서는 호스트 이름이며 SQL2019 인스턴스 이름입니다. 'Server=%s\SQL2019'는 키 값 쌍입니다.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019',
    CONNECTION_OPTIONS = 'Server=%s\SQL2019',
    CREDENTIAL = SQLServerCredentials
);

또는 포트를 사용하여 SQL Server 기본 인스턴스에 연결할 수 있습니다.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019:58137',
    CREDENTIAL = SQLServerCredentials
);

C. Always On 가용성 그룹의 읽기 가능한 보조 복제본을 참조하는 외부 데이터 원본 만들기

적용 대상: SQL Server 2019(15.x) 이상

SQL Server의 읽기 가능한 보조 복제본을 참조하는 외부 데이터 원본을 만들려면 CONNECTION_OPTIONS을(를) 사용하여 ApplicationIntent=ReadOnly을(를) 지정합니다. 또한 가용성 데이터베이스를 있는 그대로 Database={dbname} CONNECTION_OPTIONS설정하거나 가용성 데이터베이스를 데이터베이스 범위 자격 증명에 사용되는 로그인의 기본 데이터베이스로 설정해야 합니다. 가용성 그룹의 모든 가용성 복제본에서 이 작업을 수행해야 합니다.

먼저 데이터베이스 범위 자격 증명을 만들고 SQL 인증된 로그인에 대한 자격 증명을 저장합니다. PolyBase용 SQL ODBC 커넥터는 기본 인증만 지원하고 Kerberos 인증은 지원하지 않습니다. 데이터베이스 범위 자격 증명을 만들기 전에 자격 증명을 보호하려면 데이터베이스에 마스터 키가 있어야 합니다. 자세한 내용은 CREATE MASTER KEY를 참조하세요. 다음 샘플에서는 데이터베이스 범위 자격 증명을 만들고 사용자 고유의 로그인 및 암호를 제공합니다.

CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
    WITH IDENTITY = 'username',
    SECRET = 'password';

다음으로 새 외부 데이터 원본을 만듭니다.

가용성 데이터베이스를 CONNECTION_OPTIONS 데이터베이스 범위 자격 증명의 로그인에 대한 기본 데이터베이스로 포함 Database=dbname 하거나 설정하든 관계없이 LOCATION 매개 변수 내에서 CREATE EXTERNAL TABLE 문의 세 부분으로 구성된 이름을 통해 데이터베이스 이름을 제공해야 합니다. 예제는 외부 테이블 만들기를 참조하세요.

다음 예제 WINSQL2019AGL 에서는 가용성 그룹 수신기 이름이며 dbname CREATE EXTERNAL TABLE 문의 대상이 될 데이터베이스의 이름입니다.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly; Database=dbname',
    CREDENTIAL = SQLServerCredentials
);

시스템 뷰 sys.servers에서 ApplicationIntent을(를) 지정하고 외부 테이블을 만들어 가용성 그룹의 리디렉션 동작을 보여 줄 수 있습니다. 다음 샘플 스크립트에서는 두 개의 외부 데이터 원본이 만들어지고 각각에 대해 하나의 외부 테이블이 만들어집니다. 뷰를 사용하여 연결에 응답하는 서버를 테스트합니다. 읽기 전용 라우팅 기능을 통해 유사한 결과를 달성할 수도 있습니다. 자세한 내용은 Always On 가용성 그룹에 대한 읽기 전용 라우팅 구성을 참조하세요.

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadOnlyIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly; Database=dbname',
    CREDENTIAL = [SQLServerCredentials]
);
GO

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadWriteIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadWrite',
    CREDENTIAL = [SQLServerCredentials]
);
GO

가용성 그룹의 데이터베이스 내에서 sys.servers을(를) 반환할 뷰와 로컬 인스턴스의 이름을 만들어 쿼리에 응답하는 복제본을 식별할 수 있습니다. 자세한 내용은 sys.servers를 참조하세요.

CREATE VIEW vw_sys_servers
AS
SELECT [name]
FROM sys.servers
WHERE server_id = 0;
GO

그런 다음 원본 인스턴스에 외부 테이블을 만듭니다.

CREATE EXTERNAL TABLE vw_sys_servers_ro (name SYSNAME NOT NULL)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadOnlyIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

CREATE EXTERNAL TABLE vw_sys_servers_rw (name SYSNAME NOT NULL)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadWriteIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

SELECT [name]
FROM dbo.vw_sys_servers_ro;--should return secondary replica instance

SELECT [name]
FROM dbo.vw_sys_servers_rw;--should return primary replica instance
GO

D. S3 호환 개체 스토리지에서 PolyBase를 통해 parquet 파일을 쿼리하기 위한 외부 데이터 원본 만들기

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

다음 샘플 스크립트는 SQL Server의 원본 사용자 데이터베이스에 s3_ds 외부 데이터 원본을 만듭니다. 외부 데이터 원본은 s3_dc 데이터베이스 범위 자격 증명을 참조합니다.

CREATE DATABASE SCOPED CREDENTIAL s3_dc
    WITH IDENTITY = 'S3 Access Key', -- for S3-compatible object storage the identity must always be S3 Access Key
    SECRET = '<access_key_id>:<secret_key_id>' -- provided by the S3-compatible object storage
GO

CREATE EXTERNAL DATA SOURCE s3_ds
WITH (
    LOCATION = 's3://<ip_address>:<port>/',
    CREDENTIAL = s3_dc
);
GO

sys.external_data_sources를 사용하여 새 외부 데이터 원본을 확인합니다.

SELECT * FROM sys.external_data_sources;

다음 예제에서는 T-SQL을 사용하여 OPENROWSET 쿼리를 통해 S3 규격 개체 스토리지에 저장된 parquet 파일을 쿼리하는 방법을 보여 줍니다. 자세한 내용은 PolyBase를 사용하여 S3 호환 개체 스토리지에서 parquet 파일 가상화를 참조하세요.

SELECT *
FROM OPENROWSET (
    BULK '/<bucket>/<parquet_folder>',
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_ds'
) AS [cc];

E. 일반 ODBC에서 PostgreSQL로 외부 데이터 원본 만들기

이전 예제와 마찬가지로 먼저 데이터베이스 마스터 키 및 데이터베이스 범위 자격 증명을 만듭니다. 데이터베이스 범위 자격 증명은 외부 데이터 원본에 사용됩니다. 또한 이 예제에서는 PostgreSQL용 제네릭 ODBC 데이터 공급자가 서버에 설치되어 있다고 가정합니다.

이 예제에서는 일반 ODBC 데이터 공급자를 사용하여 동일한 네트워크의 PostgreSQL 데이터베이스 서버에 연결합니다. 여기서 PostgreSQL 서버의 정규화된 도메인 이름은 TCP 5432의 기본 포트를 사용하여 POSTGRES1입니다.

CREATE EXTERNAL DATA SOURCE POSTGRES1
WITH (
    LOCATION = 'odbc://POSTGRES1.domain:5432',
    CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};',
    CREDENTIAL = postgres_credential
);

Azure Storage

공유 액세스 서명 만들기

Azure Blob Storage와 Azure Data Lake Gen2의 경우 지원되는 인증 방법은 SAS(공유 액세스 서명)입니다. 공유 액세스 서명 토큰을 생성하는 간단한 방법 중 하나는 다음 단계를 따릅니다. 자세한 내용은 CREDENTIAL을 참조하세요.

1. Azure Portal의 원하는 스토리지 계정으로 이동합니다.
2. 데이터 스토리지 메뉴에서 원하는 컨테이너로 이동합니다.
3. 공유 액세스 토큰을 선택합니다.
4. 원하는 작업에 따라 적절한 권한을 선택합니다. 아래 테이블을 참조합니다.

작업	사용 권한
파일에서 데이터 읽기	읽기
여러 파일 및 하위 폴더에서 데이터를 읽습니다.	읽기 및 목록
CETAS(Create External Table as Select) 사용	읽기, 만들기 및 쓰기

5. 토큰 만료 날짜를 선택합니다.
6. SAS 토큰 및 URL을 생성합니다.
7. SAS 토큰을 복사합니다.

F. abs://인터페이스를 사용하여 Azure Blob Storage의 데이터에 액세스하는 외부 데이터 원본을 만듭니다.

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

SQL Server 2022(16.x)부터 Azure Storage 계정 v2에 새 접두사 abs를 사용합니다. abs 접두사는 SHARED ACCESS SIGNATURE를 사용한 인증을 지원합니다. abs 접두사는 이전 버전에서 사용된 wasb 접두사를 대체합니다. HADOOP가 더 이상 지원되지 않으므로 TYPE = BLOB_STORAGE는 사용하지 않아도 됩니다.

다음 예제에서 볼 수 있듯 Azure Storage 계정 키가 더 이상 필요하지 않고 대신 SAS 토큰을 사용합니다.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredentialv2
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', -- to use SAS the identity must be fixed as-is
    SECRET = '<Blob_SAS_Token>';
GO

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'abs://<container>@<storage_account_name>.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredentialv2,
);

Azure Blob Storage에 저장된 CSV 파일에 액세스하는 방법에 대한 자세한 예제는 PolyBase를 사용하여 CSV 파일 가상화를 참조하세요.

G. Azure Data Lake Gen2에서 데이터에 액세스하는 외부 데이터 원본 만들기

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

SQL Server 2022(16.x)부터 Azure Data Lake Gen2에 대해 새 접두사 adls를 abfs(이전 버전에서 사용됨) 대신 사용합니다. 접두사는 adls 다음 예제와 같이 SAS 토큰을 인증 방법으로 지원합니다.

--Create a database scoped credential using SAS Token
CREATE DATABASE SCOPED CREDENTIAL datalakegen2
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '<DataLakeGen2_SAS_Token>';
GO

CREATE EXTERNAL DATA SOURCE data_lake_gen2_dfs
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = datalakegen2
);

Azure Data Lake Gen2에 저장된 델타 파일에 액세스하는 방법에 대한 자세한 예제는 PolyBase를 사용하여 델타 테이블 가상화를 참조하세요.

예제: 대량 작업

중요

대량 작업을 위해 외부 데이터 원본을 구성할 때 LOCATION URL 끝에 추적 / , 파일 이름 또는 공유 액세스 서명 매개 변수를 추가하지 마세요.

H. Azure Storage에서 데이터를 검색하는 대량 작업을 위한 외부 데이터 원본 만들기

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

BULK INSERT 또는 OPENROWSET을 사용하여 대량 작업에 대한 다음 데이터 원본을 만듭니다. 자격 증명은 SHARED ACCESS SIGNATURE를 ID로 설정해야 하며 SAS 토큰에서 앞에 ?가 없어야 하며, 적어도 로드할 파일에 대한 읽기 권한이 있어야 하고(예: srt=o&sp=r) 만료 기간이 유효해야 합니다(모든 날짜는 UTC 시간임). 공유 액세스 서명에 대한 자세한 내용은 SAS(공유 액세스 서명) 사용을 참조하세요.

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

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
    LOCATION = 'abs://<container>@<storage_account_name>.blob.core.windows.net/',
    CREDENTIAL = AccessAzureInvoices,
);

다음 단계

• ALTER EXTERNAL DATA SOURCE(Transact-SQL)
• CREATE DATABASE SCOPED CREDENTIAL(Transact-SQL)
• CREATE EXTERNAL FILE FORMAT(Transact-SQL)
• CREATE EXTERNAL TABLE(Transact-SQL)
• sys.external_data_sources(Transact-SQL)
• SAS(공유 액세스 서명) 사용
• PolyBase 연결 구성