Azure SQL Managed Instance를 사용한 데이터 가상화

아티클
07/08/2024

Azure SQL Managed Instance 데이터 가상화 기능을 사용하면 Azure Data Lake Storage Gen2 또는 Azure Blob Storage의 공통 데이터 형식 내 파일 저장 데이터에 대해 T-SQL(Transact-SQL) 쿼리를 실행하고 조인을 사용하여 로컬에 저장된 관계형 데이터와 결합할 수 있습니다. 이렇게 하면 원본 형식 및 위치(데이터 가상화라고도 함)로 유지하면서 읽기 전용 모드로 외부 데이터에 투명하게 액세스할 수 있습니다.

개요

데이터 가상화는 다양한 시나리오 집합을 위한 파일을 쿼리하는 두 가지 방법을 제공합니다.

OPENROWSET 구문 - 파일의 임시 쿼리에 최적화되어 있습니다. 일반적으로 새 파일 집합의 콘텐츠 및 구조를 빠르게 탐색하는 데 사용됩니다.
CREATE EXTERNAL TABLE 구문 - 데이터가 데이터베이스에 로컬로 저장된 것처럼 동일한 구문을 사용하여 파일을 반복적으로 쿼리하는 데 최적화되었습니다. 외부 테이블에는 OPENROWSET 구문에 비해 몇 가지 준비 단계가 필요하지만 데이터 액세스를 보다 자세히 제어할 수 있습니다. 외부 테이블은 일반적으로 분석 워크로드 및 보고에 사용됩니다.

두 경우 모두 이 문서에서 설명한 대로 CREATE EXTERNAL DATA SOURCE T-SQL 구문을 사용하여 외부 데이터 원본을 생성해야 합니다.

T-SQL SELECT 문의 결과를 Azure Blob Storage 또는 Azure Data Lake Storage(ADLS) Gen 2의 Parquet 또는 CSV 파일로 내보내고 해당 파일 위에 외부 테이블을 생성하기 위해 AZURE SQL Managed Instance에서 CREATE EXTERNAL TABLE AS SELECT 구문도 사용할 수 있습니다.

파일 형식

Parquet 및 구분된 텍스트(CSV) 파일 형식이 직접 지원됩니다. JSON 파일 형식은 쿼리가 모든 문서를 별도의 행으로 반환하는 CSV 파일 형식을 지정하여 간접적으로 지원됩니다. JSON_VALUE 및 OPENJSON을 사용하여 행을 추가로 구문 분석할 수 있습니다.

스토리지 유형

파일은 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>.dfs.core.windows.net/<path>/<file_name>.parquet

중요

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

시작

데이터 가상화에 익숙하지 않고 기능을 빠르게 테스트하려면 먼저 익명 액세스를 허용하는 Bing 코로나19 데이터 세트와 같이 Azure Open Datasets에서 사용할 수 있는 퍼블릭 데이터 세트를 쿼리합니다.

다음 엔드포인트를 사용하여 Bing 코로나19 데이터 세트를 쿼리합니다.

Parquet: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
CSV: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

빠른 시작을 위해 이 간단한 T-SQL 쿼리를 실행하여 데이터 세트에 대한 첫 번째 인사이트를 가져옵니다. 이 쿼리는 OPENROWSET을 사용하여 공개적으로 사용 가능한 스토리지 계정에 저장된 파일을 쿼리합니다.

--Quick query on a file stored in a publicly available storage account:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet',
 FORMAT = 'parquet'
) AS filerows

첫 번째 쿼리의 결과 집합에 따라 WHERE, GROUP BY 및 기타 절을 추가하여 데이터 세트 탐색을 계속할 수 있습니다.

관리형 인스턴스에서 첫 번째 쿼리가 실패하는 경우 해당 인스턴스는 Azure Storage 계정에 대한 액세스가 제한될 수 있으며, 쿼리를 진행하기 전에 액세스를 사용하도록 설정하기 위해 네트워킹 전문가에게 문의해야 합니다.

공용 데이터 세트 쿼리에 익숙해지면 자격 증명을 제공하고 액세스 권한을 부여하며 방화벽 규칙을 구성해야 하는 비공용 데이터 세트로 전환하는 것이 좋습니다. 다수의 실제 시나리오에서는 주로 프라이빗 데이터 세트를 사용하여 작동합니다.

비공용 스토리지 계정에 대한 액세스

관리형 인스턴스에 로그인한 사용자는 비공용 스토리지 계정에 저장된 파일에 액세스하고 쿼리할 수 있는 권한을 부여받아야 합니다. 권한 부여 단계는 관리되는 인스턴스가 스토리지에 인증하는 방법에 따라 달라집니다. 인증 유형 및 모든 관련 매개 변수는 각 쿼리와 함께 직접 제공되지 않습니다. 사용자 데이터베이스에 저장된 데이터베이스 범위 자격 증명 개체에 캡슐화됩니다. 자격 증명은 데이터베이스에서 쿼리가 실행될 때마다 스토리지 계정에 액세스하는 데 사용됩니다. Azure SQL Managed Instance는 다음 두 가지 인증 형식을 지원합니다.

관리 ID
공유 액세스 서명

관리 ID는 Azure 서비스(예: Azure SQL Managed Instance)에 Microsoft Entra ID에서 관리하는 ID를 제공하는 Microsoft Entra ID(이전의 Azure Active Directory) 기능입니다. 이 ID를 사용하여 비공용 스토리지 계정의 데이터 액세스 요청에 권한을 부여할 수 있습니다. Azure SQL Managed Instance와 같은 서비스에는 시스템 할당 관리 ID가 있으며 하나 이상의 사용자가 할당한 관리 ID가 있을 수도 있습니다. Azure SQL Managed Instance를 사용하여 데이터 가상화에 시스템 할당 관리 ID 또는 사용자가 할당한 관리 ID를 사용할 수 있습니다.

먼저 Azure Storage 관리자가 데이터에 액세스할 수 있는 권한을 관리 ID에 부여해야 합니다. 다른 Microsoft Entra 사용자에게 권한을 부여하는 것과 동일한 방식으로 관리형 인스턴스의 시스템이 할당한 관리 ID에 권한을 부여합니다. 예시:

Azure Portal에서 스토리지 계정의 액세스 제어(IAM) 창에서 역할 할당 추가를 선택합니다.
Storage Blob 데이터 읽기 권한자 기본 제공 Azure RBAC 역할을 선택합니다. 이렇게 하면 필요한 Azure Blob Storage 컨테이너에 대한 관리 ID에 대해 읽기 액세스 권한이 제공됩니다.
- 관리 ID에 Storage Blob 데이터 읽기 권한자 Azure RBAC 역할을 부여하는 대신, 파일 하위 집합에 대해 더 세분화된 권한을 부여할 수 있습니다. 이 컨테이너의 개별 파일 데이터에 대한 읽기 액세스 권한이 필요한 모든 사용자는 루트(컨테이너)까지의 모든 부모 폴더에 대한 실행 권한도 있어야 합니다. Azure Data Lake Storage Gen2에서 ACL을 설정하는 방법에 대해 자세히 알아봅니다.
다음 페이지에서 관리 ID에 대한 액세스 할당을 선택합니다. + 구성원 선택의 관리 ID 드롭다운 목록에서 원하는 관리 ID를 선택합니다. 자세한 내용은 Azure Portal을 사용하여 Azure 역할 할당을 참조하세요.
그러면 관리 ID 인증을 위한 데이터베이스 범위 자격 증명을 간단히 만들 수 있습니다. 다음 예제에서 'Managed Identity'는 하드 코딩된 문자열입니다.

-- Optional: Create MASTER KEY if it doesn't exist in the database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>'
GO
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'Managed Identity'

SAS(공유 액세스 서명)는 스토리지 계정의 파일에 대한 위임된 액세스를 제공합니다. SAS는 유효성 간격, 부여된 권한, 허용되는 IP 주소 범위를 포함하여 부여하는 액세스 유형에 대한 세부적인 제어를 제공합니다. SAS 토큰이 생성되면 철회 또는 삭제할 수 없으며 유효 기간이 만료될 때까지 액세스를 허용합니다.

SAS 토큰을 다음과 같은 여러 가지 방법으로 가져올 수 있습니다.
- Azure Portal -><Your_Storage_Account> -> 공유 액세스 서명 -> 권한 구성 -> SAS 및 연결 문자열 생성으로 차례로 이동합니다. 자세한 내용은 공유 액세스 서명 생성을 참조하세요. - Azure Storage Explorer를 사용하여 SAS를 만들고 구성합니다. - PowerShell, Azure CLI, .NET 및 REST API를 통해 프로그래밍 방식으로 SAS를 만들 수 있습니다. 자세한 내용은 SAS(공유 액세스 서명)를 사용하여 Azure Storage 리소스에 대한 제한된 액세스 권한 부여를 참조하세요.

외부 데이터에 액세스하기 위해 SAS를 통해 읽기 및 나열 권한을 부여합니다. 현재, Azure SQL Managed Instance를 사용한 데이터 가상화는 읽기 전용입니다.

SAS 토큰이 생성되면 토큰의 시작 부분에 물음표(?)가 포함됩니다. 토큰을 사용하려면 자격 증명을 생성할 때 물음표(?)를 제거해야 합니다. 예시:

-- Optional: Create MASTER KEY if it doesn't exist in the database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>'
GO
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2018-03-28&ss=bfqt&srt=sco&sp=rwdlacup&se=2019-04-18T20:42:12Z&st=2019-04-18T12:42:12Z&spr=https&sig=lQHczNvrk1KoYLCpFdSsMANd0ef9BrIPBNJ3VYEIq78%3D'

외부 데이터 원본

외부 데이터 원본은 여러 쿼리에서 파일 위치를 쉽게 참조할 수 있는 추상화입니다. 퍼블릭 위치를 쿼리하려면 외부 데이터 원본을 만드는 동안 파일 위치를 지정해야 합니다.

CREATE EXTERNAL DATA SOURCE MyExternalDataSource
WITH (
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
)

위치와 함께 비공용 스토리지 계정에 액세스할 때 캡슐화된 인증 매개 변수를 사용하여 데이터베이스 범위 자격 증명을 참조해야 합니다. 다음 스크립트는 파일 경로를 가리키고 데이터베이스 범위 자격 증명을 참조하는 외부 데이터 원본을 생성합니다.

--Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource
WITH (
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
        CREDENTIAL = [MyCredential];
)

OPENROWSET를 사용하여 데이터 원본 쿼리

OPENROWSET 구문을 사용하면 필요한 최소 개수의 데이터베이스 개체만 생성하면서 즉시 임시 쿼리를 수행할 수 있습니다.

OPENROWSET에서는 외부 파일 형식 및 외부 테이블 자체가 필요한 외부 테이블 접근 방식과 달리 외부 데이터 원본(및 자격 증명)만 만들어야 합니다.

DATA_SOURCE 매개 변수 값이 BULK 매개 변수 앞에 자동으로 추가되어 파일의 전체 경로를 형성합니다.

OPENROWSET을 사용할 경우 단일 파일을 쿼리하는 다음 예제와 같은 파일 형식을 제공합니다.

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'bing_covid-19_data.parquet',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

여러 파일 및 폴더 쿼리

또한 OPENROWSET 명령은 BULK 경로에서 와일드카드를 사용하여 여러 파일 또는 폴더를 쿼리할 수 있습니다.

다음 예제에서는 NYC 노란색 택시 여정 레코드 열기 데이터 세트를 사용합니다.

먼저 외부 데이터 원본을 생성합니다.

--Create the data source first:
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

이제 폴더에서 .parquet 확장자의 모든 파일을 쿼리할 수 있습니다. 예를 들어 여기서는 이름 패턴과 일치하는 파일만 쿼리합니다.

--Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

여러 파일 또는 폴더를 쿼리할 때 단일 OPENROWSET로 액세스하는 모든 파일은 동일한 구조(예: 동일한 수의 열 및 데이터 형식)를 가져야 합니다. 폴더는 재귀적으로 트래버스할 수 없습니다.

스키마 유추

자동 스키마 유추는 파일 스키마를 알지 못하는 경우 신속하게 쿼리를 작성하고 데이터를 탐색하는 데 도움을 줍니다. 스키마 유추는 parquet 파일에서만 작동합니다.

유추된 데이터 형식은 편리하지만 실제 데이터 형식보다 클 수 있습니다. 적절한 데이터 형식을 사용하기 위해 원본 파일에 충분한 정보가 있을 수 있기 때문입니다. 이로 인해 쿼리 성능이 저하 될 수 있습니다. 예를 들어 parquet 파일에는 최대 문자 열 길이에 대한 메타데이터가 없으므로 인스턴스는 이것을 varchar(8000)로 유추합니다.

sp_describe_first_results_set 저장 프로시저를 사용하여 다음 예제와 같이 쿼리의 결과 데이터 형식을 확인합니다.

EXEC sp_describe_first_result_set N'
 SELECT
 vendorID, tpepPickupDateTime, passengerCount
 FROM
 OPENROWSET(
  BULK ''yellow/*/*/*.parquet'',
  DATA_SOURCE = ''NYCTaxiExternalDataSource'',
  FORMAT=''parquet''
 ) AS nyc';

데이터 형식을 알고 나면 WITH 절을 사용하여 데이터 형식을 지정하여 성능을 향상시킬 수 있습니다.

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount
FROM
OPENROWSET(
 BULK 'yellow/*/*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT='PARQUET'
 )
WITH (
vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000
tpepPickupDateTime datetime2,
passengerCount int
) AS nyc;

CSV 파일의 스키마를 자동으로 확인할 수 없으므로 항상 WITH 절을 사용하여 열을 지정해야 합니다.

SELECT TOP 10 id, updated, confirmed, confirmed_change
FROM OPENROWSET(
 BULK 'bing_covid-19_data.csv',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'CSV',
 FIRSTROW = 2
)
WITH (
 id int,
 updated date,
 confirmed int,
 confirmed_change int
) AS filerows;

파일 메타데이터 함수

여러 파일 또는 폴더를 쿼리할 때 filepath() 및 filename() 함수를 사용하여 파일 메타데이터를 읽고 결과 집합의 행이 시작되는 파일의 경로 일부 또는 전체 경로 및 이름을 가져올 수 있습니다.

--Query all files and project file path and file name information for each row:
SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder],
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;
--List all paths:
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;

매개 변수 없이 호출되면 filepath() 함수는 행이 시작되는 파일 경로를 반환합니다. DATA_SOURCE가 OPENROWSET에 사용되는 경우 DATA_SOURCE에 해당하는 경로를 반환하고, 그렇지 않으면 전체 파일 경로를 반환합니다.

매개 변수를 사용하여 호출하면 매개 변수에 지정된 위치에 있는 와일드카드와 일치하는 경로의 일부를 반환합니다. 예를 들어 매개 변수 값 1은 첫 번째 와일드카드와 일치하는 경로의 일부를 반환합니다.

filepath() 함수는 행을 필터링하고 집계하는 데도 사용할 수 있습니다.

SELECT
 r.filepath() AS filepath
 ,r.filepath(1) AS [year]
 ,r.filepath(2) AS [month]
 ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
DATA_SOURCE = 'NYCTaxiExternalDataSource',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2017')
 AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
 r.filepath()
 ,r.filepath(1)
 ,r.filepath(2)
ORDER BY
 filepath;

OPENROWSET 위에 보기 만들기

기본 쿼리를 쉽게 다시 사용할 수 있도록 뷰를 만들고 사용하여 OPENROWSET 쿼리를 래핑할 수 있습니다.

CREATE VIEW TaxiRides AS
SELECT *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows

또한 더 쉽고 성능이 좋은 필터링을 위해 filepath() 함수를 사용하여 파일 위치 데이터가 있는 열을 보기에 추가하는 것이 편리합니다. 뷰를 사용하면 파일 수와 뷰 위에 있는 쿼리가 해당 열로 필터링될 때 읽고 처리해야 하는 데이터의 양을 줄일 수 있습니다.

CREATE VIEW TaxiRides AS
SELECT *
 , filerows.filepath(1) AS [year]
 , filerows.filepath(2) AS [month]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows

또한 뷰를 사용하면 Power BI와 같은 보고 및 분석 도구에서 OPENROWSET 결과를 사용할 수 있습니다.

외부 테이블

외부 테이블은 사용자 테이블에 저장된 로컬 관계형 데이터를 쿼리하는 것과 거의 동일한 쿼리 환경을 만드는 파일에 대한 액세스를 캡슐화합니다. 외부 테이블을 만들려면 외부 데이터 원본 및 외부 파일 형식 개체가 있어야 합니다.

--Create external file format
CREATE EXTERNAL FILE FORMAT DemoFileFormat
WITH (
 FORMAT_TYPE=PARQUET
)
GO

--Create external table:
CREATE EXTERNAL TABLE tbl_TaxiRides(
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
 tpepPickupDateTime DATETIME2,
 tpepDropoffDateTime DATETIME2,
 passengerCount INT,
 tripDistance FLOAT,
 puLocationId VARCHAR(8000),
 doLocationId VARCHAR(8000),
 startLon FLOAT,
 startLat FLOAT,
 endLon FLOAT,
 endLat FLOAT,
 rateCodeId SMALLINT,
 storeAndFwdFlag VARCHAR(8000),
 paymentType VARCHAR(8000),
 fareAmount FLOAT,
 extra FLOAT,
 mtaTax FLOAT,
 improvementSurcharge VARCHAR(8000),
 tipAmount FLOAT,
 tollsAmount FLOAT,
 totalAmount FLOAT
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);
GO

외부 테이블이 만들어지면 다른 테이블과 마찬가지로 쿼리할 수 있습니다.

SELECT TOP 10 *
FROM tbl_TaxiRides;

OPENROWSET과 마찬가지로 외부 테이블은 와일드카드를 사용하여 여러 파일 및 폴더를 쿼리할 수 있습니다. 스키마 유추는 외부 테이블에서 지원되지 않습니다.

성능 고려 사항

쿼리할 수 있는 파일 수 또는 데이터 양에는 하드 제한이 없지만 쿼리 성능은 데이터 양, 데이터 형식 및 쿼리 및 조인의 구성 방식 및 복잡성에 따라 달라집니다.

분할된 데이터 쿼리

데이터는 파티션이라고도 하는 하위 폴더로 구성되는 경우가 많습니다. 관리되는 인스턴스에 특정 폴더와 파일을 쿼리하도록 지시할 수 있습니다. 이렇게 하면 쿼리에서 읽고 처리하는 데 필요한 파일 수와 데이터 양이 줄어들어 성능이 향상됩니다. 이러한 유형의 쿼리 최적화를 파티션 정리 또는 파티션 제거라고 합니다. 쿼리의 WHERE 절에서 메타데이터 함수 filepath()를 사용하여 쿼리 실행에서 파티션을 제거할 수 있습니다.

다음 샘플 쿼리에서는 2017년 마지막 3개월 동안의 NYC Yellow Taxi 데이터 파일을 읽습니다.

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'yellow/puYear=*/puMonth=*/*.parquet',
        DATA_SOURCE = 'NYCTaxiExternalDataSource',
        FORMAT = 'parquet'
    )
WITH (
    vendorID INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

저장된 데이터가 분할되지 않은 경우 쿼리 성능을 향상시키기 위해 분할하는 것이 좋습니다.

외부 테이블을 사용하는 경우 filename() 및 filepath() 함수는 지원되지만 WHERE 절에서는 지원되지 않습니다. 계산 열에서 사용하는 경우 filename 또는 filepath로 계속 필터링할 수 있습니다. 다음은 이에 대한 예입니다.

CREATE EXTERNAL TABLE tbl_TaxiRides (
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
 tpepPickupDateTime DATETIME2,
 tpepDropoffDateTime DATETIME2,
 passengerCount INT,
 tripDistance FLOAT,
 puLocationId VARCHAR(8000),
 doLocationId VARCHAR(8000),
 startLon FLOAT,
 startLat FLOAT,
 endLon FLOAT,
 endLat FLOAT,
 rateCodeId SMALLINT,
 storeAndFwdFlag VARCHAR(8000),
 paymentType VARCHAR(8000),
 fareAmount FLOAT,
 extra FLOAT,
 mtaTax FLOAT,
 improvementSurcharge VARCHAR(8000),
 tipAmount FLOAT,
 tollsAmount FLOAT,
 totalAmount FLOAT,
 [Year]  AS CAST(filepath(1) AS INT), --use filepath() for partitioning
 [Month]  AS CAST(filepath(2) AS INT) --use filepath() for partitioning
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);
GO

SELECT *
      FROM tbl_TaxiRides
WHERE
      [year]=2017            
      AND [month] in (10,11,12);

저장된 데이터가 분할되지 않은 경우 쿼리 성능을 향상시키기 위해 분할하는 것이 좋습니다.

통계

쿼리 최적화를 위해 할 수 있는 가장 중요한 작업은 외부 데이터에 대한 통계를 수집하는 것입니다. 인스턴스에서 데이터에 대해 많이 알수록 쿼리를 빠르게 실행할 수 있습니다. SQL 엔진 쿼리 최적화 프로그램은 비용을 기반으로 하는 최적화 프로그램입니다. 다양한 쿼리 계획의 비용을 비교한 다음, 비용이 가장 낮은 계획을 선택합니다. 대부분의 경우 가장 빠르게 실행되는 계획을 선택합니다.

통계 자동 생성

Azure SQL Managed Instance는 수신 사용자 쿼리를 분석하여 누락된 통계가 있는지 확인합니다. 누락된 통계가 있는 경우 쿼리 최적화 프로그램이 쿼리 조건자 또는 조인 조건의 개별 열에 대한 통계를 자동으로 만들어서 쿼리 계획의 카디널리티 추정값을 개선합니다. 통계 자동 생성은 동기식으로 수행되므로 열에 통계가 없는 경우 쿼리 성능이 약간 저하될 수 있습니다. 단일 열에 대한 통계를 만드는 데 걸리는 시간은 대상으로 지정된 파일의 크기에 따라 달라집니다.

OPENROWSET 수동 통계

단일 열을 매개 변수로 사용하여 선택 쿼리를 전달하여 sys.sp_create_openrowset_statistics 저장 프로시저를 사용하여 OPENROWSET 경로에 대한 단일 열 통계를 만들 수 있습니다.

EXEC sys.sp_create_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
';

기본적으로 인스턴스는 데이터 세트에 제공된 데이터의 100%를 사용하여 통계를 만듭니다. 필요에 따라 TABLESAMPLE 옵션을 사용하여 샘플 크기를 백분율로 지정할 수 있습니다. 여러 열에 대한 단일 열 통계를 생성하려면 각 열에 대해 sys.sp_create_openrowset_statistics를 실행합니다. OPENROWSET 경로에 대한 여러 열 통계를 만들 수 없습니다.

기존 통계를 업데이트하려면 sys.sp_drop_openrowset_statistics 저장 프로시저를 사용하여 먼저 삭제한 다음, sys.sp_create_openrowset_statistics를 사용하여 다시 만듭니다.

EXEC sys.sp_drop_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
';

외부 테이블 수동 통계

외부 테이블에 대한 통계를 만드는 구문은 일반 사용자 테이블에 사용되는 구문과 유사합니다. 열에 대해 통계를 만들려면 통계 개체의 이름과 열 이름을 제공합니다.

CREATE STATISTICS sVendor
ON tbl_TaxiRides (vendorID)
WITH FULLSCAN, NORECOMPUTE;

WITH 옵션은 필수이며 샘플 크기의 경우 허용되는 옵션은 FULLSCAN 및 SAMPLE n 백분율입니다.

여러 열에 대한 단일 열 통계를 생성하려면 각 열에 대해 CREATE STATISTICS를 실행합니다.
여러 열 통계는 지원되지 않습니다.

문제 해결

쿼리 실행 문제는 일반적으로 관리되는 인스턴스가 파일 위치에 액세스할 수 없기 때문에 발생합니다. 관련 오류 메시지는 액세스 권한 부족, 존재하지 않는 위치 또는 파일 경로, 다른 프로세스에서 사용되는 파일 또는 해당 디렉터리를 나열할 수 없음을 보고할 수 있습니다. 대부분의 경우 이는 네트워크 트래픽 제어 정책 또는 액세스 권한 부족으로 인해 파일에 대한 액세스가 차단됨을 나타냅니다. 다음은 확인해야 할 사항입니다.

잘못되거나 잘못 입력된 위치 경로
SAS 키 유효성: 만료되었거나, 오타를 포함하거나, 물음표로 시작될 수 있습니다.
허용되는 SAS 키 권한: 최소 읽기 권한, 와일드카드가 사용되는 경우 나열 권한
스토리지 계정의 인바운드 트래픽이 차단됨 자세한 내용은 Azure Storage에 대한 가상 네트워크 규칙 관리를 확인하고 관리되는 인스턴스 VNet의 액세스가 허용되는지 확인합니다.
스토리지 엔드포인트 정책을 사용하여 관리되는 인스턴스에서 차단된 아웃바운드 트래픽 스토리지 계정에 대한 아웃바운드 트래픽을 허용합니다.
관리 ID 액세스 권한: 스토리지 계정에 대한 액세스 권한이 인스턴스의 관리 ID에 부여되었는지 확인합니다.
데이터 가상화 쿼리가 작동하려면 데이터베이스의 호환성 수준이 130 이상이어야 합니다.

CETAS(CREATE EXTERNAL TABLE AS SELECT)

CETAS(CREATE EXTERNAL TABLE AS SELECT)를 사용하면 SQL Managed Instance의 데이터를 외부 스토리지 계정으로 내보낼 수 있습니다. CETAS를 사용하여 Parquet이나 CSV 파일 Azure Blob Storage 또는 Azure Data Lake Storage(ADLS) Gen2에서 외부 테이블을 생성할 수 있습니다. CETAS는 생성한 외부 테이블에 Transact-SQL SELECT 문의 결과를 병렬로 내보낼 수도 있습니다. 이러한 기능으로 인해 데이터 반출 위험 가능성이 있으므로 AZURE SQL Managed Instance에 대해 CETAS를 기본값으로 사용하지 않도록 설정됩니다. 사용하도록 설정하려면 CETAS(CREATE EXTERNAL TABLE AS SELECT)를 참조하세요.

제한 사항

행 수준 보안 기능은 외부 테이블에서 지원되지 않습니다.
외부 테이블의 열에 대해 동적 데이터 마스킹 규칙을 정의할 수 없습니다.

알려진 문제

SSMS(SQL Server Management Studio)에서 Always Encrypted에 대한 매개 변수화를 사용하도록 설정하면 데이터 가상화 쿼리가 Incorrect syntax near 'PUSHDOWN' 오류 메시지를 나타내며 실패합니다.

다음을 통해 공유

Azure SQL Managed Instance를 사용한 데이터 가상화

개요

파일 형식

스토리지 유형

시작

비공용 스토리지 계정에 대한 액세스

외부 데이터 원본

OPENROWSET를 사용하여 데이터 원본 쿼리

여러 파일 및 폴더 쿼리

스키마 유추

파일 메타데이터 함수

OPENROWSET 위에 보기 만들기

외부 테이블

성능 고려 사항

분할된 데이터 쿼리

통계

통계 자동 생성

OPENROWSET 수동 통계

외부 테이블 수동 통계

문제 해결

CETAS(CREATE EXTERNAL TABLE AS SELECT)

제한 사항

알려진 문제

피드백

추가 리소스

다음을 통해 공유

Azure SQL Managed Instance를 사용한 데이터 가상화

개요

파일 형식

스토리지 유형

시작

비공용 스토리지 계정에 대한 액세스

외부 데이터 원본

OPENROWSET를 사용하여 데이터 원본 쿼리

여러 파일 및 폴더 쿼리

스키마 유추

파일 메타데이터 함수

OPENROWSET 위에 보기 만들기

외부 테이블

성능 고려 사항

분할된 데이터 쿼리

통계

통계 자동 생성

OPENROWSET 수동 통계

외부 테이블 수동 통계

문제 해결

CETAS(CREATE EXTERNAL TABLE AS SELECT)

제한 사항

알려진 문제

관련 콘텐츠

피드백

추가 리소스