Synapse SQL을 사용하여 Cosmos DB 쿼리

5분

Spark 풀을 사용하는 것 외에도 Azure Synapse Analytics에서 기본 제공 서버리스 SQL 풀을 사용하여 Azure Cosmos DB 분석 컨테이너를 쿼리할 수도 있습니다. 이렇게 하려면 OPENROWSET SQL 함수를 사용하여 Azure Cosmos DB 데이터베이스의 연결된 서비스에 연결할 수 있습니다.

OPENROWSET를 인증 키와 함께 사용

기본적으로 Azure Cosmos DB 계정에 대한 액세스는 인증 키로 인증됩니다. 다음 예제와 같이 OPENROWSET 문에서 연결 문자열의 일부로 이 키를 사용하여 SQL 풀에서 연결된 서비스를 통해 연결할 수 있습니다.

SELECT *
FROM OPENROWSET(
    'CosmosDB',
    'Account=my-cosmos-db;Database=my-db;Key=abcd1234....==',
    [my-container]) AS products_data

팁

Azure Portal의 키 페이지에서 Cosmos DB 계정에 대한 기본 및 보조 키를 찾을 수 있습니다.

이 쿼리의 결과는 Azure Cosmos DB 컨테이너의 항목으로부터의 메타데이터 및 애플리케이션 정의 필드를 포함하여 다음과 같을 수 있습니다.

_rid	_ts	productID	productName	id	_etag
mjMaAL...==	1655414791	123	위젯	7248f072-11c3-42b1-a368-...	54004b09-0000-2300-...
mjMaAL...==	1655414829	124	Wotsit	dc33131c-65c7-421a-a0f7-...	5400ca09-0000-2300-...
mjMaAL...==	1655414835	125	Thingumy	ce22351d-78c7-428a-a1h5-...	5400ca09-0000-2300-...
...	...	...	...	...	...

데이터는 분석 저장소에서 검색되며 쿼리는 운영 저장소에 영향을 주지 않습니다.

OPENROWSET를 자격 증명과 함께 사용

OPENROWSET에 대한 각 호출에 인증 키를 포함하는 대신 Cosmos DB 계정의 인증 정보를 캡슐화하는 자격 증명을 정의하고 후속 쿼리에서 자격 증명을 사용할 수 있습니다. 자격 증명을 만들려면 다음 예제와 같이 CREATE CREDENTIAL 문을 사용합니다.

 CREATE CREDENTIAL my_credential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = 'abcd1234....==';

자격 증명을 만들었으면 다음과 같이 OPENROWSET 함수에서 사용할 수 있습니다.

SELECT *
FROM OPENROWSET(PROVIDER = 'CosmosDB',
                CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                OBJECT = 'my-container',
                SERVER_CREDENTIAL = 'my_credential'
) AS products_data

이번에도 결과에는 분석 저장소의 메타데이터 및 애플리케이션 정의 필드가 포함됩니다.

_rid	_ts	productID	productName	id	_etag
mjMaAL...==	1655414791	123	위젯	7248f072-11c3-42b1-a368-...	54004b09-0000-2300-...
mjMaAL...==	1655414829	124	Wotsit	dc33131c-65c7-421a-a0f7-...	5400ca09-0000-2300-...
mjMaAL...==	1655414835	125	Thingumy	ce22351d-78c7-428a-a1h5-...	5400ca09-0000-2300-...
...	...	...	...	...	...

스키마 지정

OPENROWSET 구문에는 결과 행 집합에 대한 스키마를 정의하는 데 사용할 수 있는 WITH 절이 포함되어 있습니다. 다음 예제와 같이 개별 필드를 지정하고 데이터 형식을 할당하는 데 이를 사용할 수 있습니다.

 SELECT *
 FROM OPENROWSET(PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    productID INT,
    productName VARCHAR(20)
 ) AS products_data

이 경우 분석 저장소의 필드에 productID 및 productName이 포함되어 있다고 가정하면 결과 행 집합은 다음 표와 비슷합니다.

productID	productName
123	위젯
124	Wotsit
125	Thingumy
...	...

물론 SELECT 절에서 개별 열 이름을 지정할 수 있으므로(예: SELECT productID, productName ...) 개별 열을 지정하는 기능은 제한된 용도로 보일 수 있습니다. 그러나 다음 예제와 같이 운영 저장소에 저장된 원본 JSON 문서에 여러 수준의 필드가 포함된 경우를 생각해 보세요.

{
    "productID": 126,
    "productName": "Sprocket",
    "supplier": {
        "supplierName": "Contoso",
        "supplierPhone": "555-123-4567"
    }
    "id": "62588f072-11c3-42b1-a738-...",
    "_rid": "mjMaAL...==",
    ...
}

WITH 절은 명시적 JSON 경로를 포함하도록 지원하므로 이 예제와 같이 중첩된 필드를 처리하고 필드 이름에 별칭을 할당할 수 있습니다.

 SELECT *
 FROM OPENROWSET(PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    ProductNo INT '$.productID',
    ProductName VARCHAR(20) '$.productName',
    Supplier VARCHAR(20) '$.supplier.supplierName',
    SupplierPhoneNo VARCHAR(15) '$.supplier.supplierPhone'
 ) AS products_data

이 쿼리의 결과에는 제품 126에 대한 다음 행이 포함됩니다.

ProductNo	ProductName	공급업체	SupplierPhoneNo
126	Sprocket	Contoso	555-123-4567

데이터베이스에서 보기 만들기

동일한 데이터를 자주 쿼리해야 하거나 OPENROWSET 함수를 포함하지 않는 SELECT 문에 의존하는 보고 및 시각화 도구를 사용해야 하는 경우 보기를 사용하여 데이터를 추상화할 수 있습니다. 보기를 만들려면 다음 예제와 같이 보기를 정의할 새 데이터베이스를 만들어야 합니다(master 데이터베이스의 사용자 정의 보기는 지원되지 않음).

CREATE DATABASE sales_db
   COLLATE Latin1_General_100_BIN2_UTF8;
 GO;

 USE sales_db;
 GO;

 CREATE VIEW products
 AS
 SELECT *
 FROM OPENROWSET(PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    ProductNo INT '$.productID',
    ProductName VARCHAR(20) '$.productName',
    Supplier VARCHAR(20) '$.supplier.supplierName',
    SupplierPhoneNo VARCHAR(15) '$.supplier.supplierPhone'
 ) AS products_data
 GO

팁

Cosmos DB의 데이터에 액세스하는 데이터베이스를 만들 때 UTF-8 기반 데이터 정렬을 사용하여 Cosmos DB의 문자열과의 호환성을 보장하는 것이 가장 좋습니다.

보기를 만든 후에는 사용자 및 클라이언트 애플리케이션이 다른 SQL 보기 또는 테이블처럼 쿼리할 수 있습니다.

SELECT * FROM products;

서버리스 SQL 풀 및 Azure Cosmos DB에 대한 고려 사항

서버리스 SQL 풀을 사용하여 Azure Cosmos DB 분석 저장소의 데이터를 쿼리하려는 경우 다음 모범 사례를 고려하세요.