Запрос данных Cosmos DB с помощью Synapse SQL

5 мин

Помимо использования пула Spark, вы также можете запросить аналитический контейнер Azure Cosmos DB с помощью встроенного бессерверного пула SQL в Azure Synapse Analytics. Для этого можно использовать 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

Совет

Первичный и вторичный ключ для учетной записи Cosmos DB можно найти на странице Ключи на портале Azure.

Результаты этого запроса могут выглядеть примерно следующим образом, включая метаданные и определяемые приложением поля из элементов в контейнере 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	НаименованиеПродукта	Поставщик	SupplierPhoneNo
126	Sprocket	Contoso	555-123-4567

Создание представления в базе данных

Если требуется часто запрашивать одни и те же данные или использовать средства создания отчетов и визуализации, основанные на инструкциях SELECT, которые не включают функцию OPENROWSET, вы можете использовать представление для абстрагирования данных. Чтобы создать представление, необходимо создать новую базу данных, в которой его можно определить (определяемые пользователем представления в базе данных 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;

Использование OPENROWSET с ключом проверки подлинности

Использование OPENROWSET с учетными данными

Указание схемы

Создание представления в базе данных

Рекомендации по бессерверным пулам SQL и Azure Cosmos DB

Обратная связь