Запрос данных Azure Cosmos DB с помощью бессерверого пула SQL в Azure Synapse Link

Бессерверный пул SQL позволяет анализировать данные в контейнерах Azure Cosmos DB, включенных с помощью Azure Synapse Link, практически в реальном времени и без влияния на производительность транзакционных рабочих нагрузок. Он располагает знакомым синтаксисом T-SQL для запроса данных из аналитического хранилища, а также встроенными возможностями подключения к широкому спектру средств бизнес-аналитики и произвольных запросов через интерфейс T-SQL.

Для запросов Azure Cosmos DB обеспечена полная контактная зона SELECT с помощью функции OPENROWSET, которая включает большинство функций и операторов SQL. Вы также можете сохранять результаты запроса, считывающего данные из Azure Cosmos DB, вместе с данными в хранилище BLOB-объектов Azure или Azure Data Lake Storage с помощью инструкции create external table as select (CETAS). В настоящее время нельзя сохранять результаты запросов бессерверного пула SQL в Azure Cosmos DB с помощью CETAS.

В этой статье вы узнаете, как написать запрос с использованием бессерверного пула SQL, который будет запрашивать данные из контейнеров Azure Cosmos DB, включенных с помощью Azure Synapse Link. Затем в этом учебнике вы можете просмотреть дополнительную информацию о создании представлений бессерверных пулов SQL в контейнерах Azure Cosmos DB и их подключении к моделям Power BI. В этом учебнике используется контейнер с четко определенной схемой Azure Cosmos DB. Вы также можете ознакомиться с модулем Learn о том, как запрашивать Azure Cosmos DB с помощью SQL Serverless для Azure Synapse Analytics.

Необходимые компоненты

• Убедитесь, что вы подготовили аналитическое хранилище:
  • Включите аналитическое хранилище в контейнерах Azure Cosmos DB.
  • Получите строку подключения с помощью ключа только для чтения, который будет использоваться для опроса хранилища аналитических данных.
  • Получение ключа только для чтения, который будет использоваться для доступа к контейнеру Azure Cosmos DB
• Убедитесь, что вы применили все рекомендации, такие как:
  • Убедитесь, что аналитическое хранилище Azure Cosmos DB находится в том же регионе, что и бессерверный пул SQL.
  • Убедитесь, что клиентское приложение (Power BI, служба анализа) находится в том же регионе, что и бессерверный пул SQL.
  • Если вы возвращаете большой объем данных (больше 80 ГБ), рассмотрите возможность использования уровня кэширования, например служб анализа, и загрузки секций размером менее 80 ГБ в модели служб анализа.
  • При фильтрации данных с помощью строковых столбцов убедитесь, что используется функция OPENROWSET с явным предложением WITH с наименьшими типами из возможных (например, не используйте VARCHAR (1000), если известно, что свойство содержит не более 5 символов).

Обзор

Бессерверный пул SQL позволяет запрашивать аналитическое хранилище Azure Cosmos DB с помощью функции OPENROWSET.

• OPENROWSET со встроенным ключом. Этот синтаксис можно использовать для запроса коллекций Azure Cosmos DB без необходимости подготовки учетных данных.
• OPENROWSET с помощью учетных данных, содержащих ключ учетной записи Azure Cosmos DB. Этот синтаксис можно использовать для создания представлений в коллекциях Azure Cosmos DB.

OPENROWSET( 
       'CosmosDB',
       '<SQL connection string for Azure Cosmos DB>',
       <Container name>
    )  [ < with clause > ] AS alias

AccountEndpoint=https://<database account name>.documents.azure.com:443/;AccountKey=<database account master key>;

'account=<database account name>;database=<database name>;region=<region name>;key=<database account master key>'

OPENROWSET( 
       PROVIDER = 'CosmosDB',
       CONNECTION = '<SQL connection string for Azure Cosmos DB without account key>',
       OBJECT = '<Container name>',
       [ CREDENTIAL | SERVER_CREDENTIAL ] = '<credential name>'
    )  [ < with clause > ] AS alias

'account=<database account name>;database=<database name>;region=<region name>'

Пример набора данных

Примеры в этой статье основаны на данных из наблюдений за COVID-19 Европейского центра по профилактике и контролю заболеваний (ECDC) и набора данных открытого исследования COVID-19 (CORD-19) doi:10.5281/zenodo.3715505.

На этих страницах можно увидеть лицензию и структуру данных. Вы также можете скачать примеры данных для наборов данных ECDC и CORD-19.

Для работы с этой статьей, показывающей, как выполнять запросы данных Azure Cosmos DB с помощью бессерверного пула SQL, вам потребуются следующие ресурсы:

• учетная запись базы данных Azure Cosmos DB в которой включена функция Azure Synapse Link;
• база данных Azure Cosmos DB с именем covid;
• два контейнера Azure Cosmos DB с именами Ecdc и Cord19, в которые загружены приведенные выше демонстрационные наборы данных.

Для целей тестирования можно использовать следующую строку подключения: Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==. Обратите внимание, что это подключение не гарантирует высокую производительность, так как учетная запись может находиться в удаленном регионе по сравнению с вашей конечной точкой Synapse SQL.

Исследование Azure Cosmos DB данных с помощью автоматического вывода схемы

Самый простой способ исследовать данные в Azure Cosmos DB — использовать функцию автоматического вывода схемы. Пропустив предложение WITH в инструкции OPENROWSET, вы можете указать бессерверному пулу SQL, что необходимо автоматически обнаружить (вывести) схему аналитического хранилища контейнера Azure Cosmos DB.

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       Ecdc) as documents

/*  Setup - create server-level or database scoped credential with Azure Cosmos DB account key:
    CREATE CREDENTIAL MyCosmosDbAccountCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

В предыдущем примере мы дали бессерверному пулу SQL указание подключиться к базе данных covid в учетной записи Azure Cosmos DB MyCosmosDbAccount, прошедшей проверку подлинности с помощью ключа Azure Cosmos DB (фиктивного в предыдущем примере). Затем мы обратились к аналитическому хранилищу контейнера Ecdc в регионе West US 2. Так как нет проекции определенных свойств, функция OPENROWSET возвратит все свойства из элементов Azure Cosmos DB.

Если исходить из предположения, что элементы в контейнере Azure Cosmos DB имеют свойства date_rep, cases и geo_id, то результаты запроса будут следующие.

Если нужно исследовать данные из другого контейнера в той же базе данных Azure Cosmos DB, то можно использовать ту же строку подключения и сослаться на необходимый контейнер в третьем параметре:

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       Cord19) as cord19

Явное указание схемы

Функция автоматического вывода схемы в OPENROWSET предоставляет простой и удобный интерфейс запросов, но в ваших бизнес-сценариях может требоваться явное указание схемы для получения нужных свойств только для чтения из данных Azure Cosmos DB.

Функция OPENROWSET позволяет явно указать, какие свойства нужно прочитать из данных в контейнере, и указать их типы данных.

Предположим, что мы импортировали в Azure Cosmos DB некоторые данные из набора данных ECDC COVID со следующей структурой:

{"date_rep":"2020-08-13","cases":254,"countries_and_territories":"Serbia","geo_id":"RS"}
{"date_rep":"2020-08-12","cases":235,"countries_and_territories":"Serbia","geo_id":"RS"}
{"date_rep":"2020-08-11","cases":163,"countries_and_territories":"Serbia","geo_id":"RS"}

Эти плоские документы JSON в Azure Cosmos DB могут быть представлены в виде набора строк и столбцов в Synapse SQL. Функция OPENROWSET позволяет указать подмножество свойств, которые вы хотите прочитать, и точные типы столбцов в предложении WITH:

SELECT TOP 10 *
FROM OPENROWSET(
      'CosmosDB',
      'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       Ecdc
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

/*  Setup - create server-level or database scoped credential with Azure Cosmos DB account key:
    CREATE CREDENTIAL MyCosmosDbAccountCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows
   

Результат этого запроса может выглядеть, как показано в следующей таблице:

Дополнительные сведения о типах SQL, которые следует использовать для значений Azure Cosmos DB, см. в разделе Правила сопоставления типов SQL в конце этой статьи.

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

Создание представлений в базе данных master или базах данных по умолчанию не рекомендуется и не поддерживается. Поэтому вам необходимо создать пользовательскую базу данных для ваших представлений.

Определив схему, вы можете подготовить представление для ваших данных Azure Cosmos DB. Ключ учетной записи Azure Cosmos DB следует разместить в отдельных учетных данных и ссылаться на эти учетные данные из функции OPENROWSET. Не храните ключ учетной записи в определении представления.

CREATE CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
GO
CREATE OR ALTER VIEW Ecdc
AS SELECT *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

Не используйте OPENROWSET без явно определенной схемы, так как это может повлиять на производительность. Убедитесь, что используются наименьшие возможные размеры столбцов (например, VARCHAR (100) вместо VARCHAR (8000) по умолчанию). Необходимо использовать некоторые параметры сортировки UTF-8 в качестве параметров сортировки базы данных по умолчанию или задать их как явные параметры сортировки столбцов, чтобы избежать проблем с преобразованием UTF-8. Параметры сортировки Latin1_General_100_BIN2_UTF8 обеспечивают лучшую производительность при фильтрации данных с использованием нескольких строковых столбцов.

При запросе представления могут возникнуть ошибки или непредвиденные результаты. Это, вероятно, означает, что представление ссылается на столбцы или объекты, которые были изменены или больше не существуют. Необходимо вручную настроить определение представления, чтобы выровнять изменения базовой схемы. Имейте в виду, что это может произойти как при использовании автоматического вывода схемы в представлении, так и при явном указании схемы.

Запрос вложенных объектов

С помощью Azure Cosmos DB можно представлять более сложные модели данных, составляя их как вложенные объекты или массивы. Функция автосинхронизации Azure Synapse Link для Azure Cosmos DB управляет представлением схемы в готовом аналитическом хранилище, что включает обработку вложенных типов данных, обеспечивающую выполнение расширенных запросов из бессерверного пула SQL.

Например, набор данных CORD-19 содержит документы JSON, имеющие следующую структуру:

{
    "paper_id": <str>,                   # 40-character sha1 of the PDF
    "metadata": {
        "title": <str>,
        "authors": <array of objects>    # list of author dicts, in order
        ...
     }
     ...
}

Вложенные объекты и массивы в Azure Cosmos DB представлены в результатах запроса в виде строк JSON, когда функция OPENROWSET считывает их. При использовании предложения WITH можно указать пути к вложенным значениям в объектах:

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       Cord19)
WITH (  paper_id    varchar(8000),
        title        varchar(1000) '$.metadata.title',
        metadata     varchar(max),
        authors      varchar(max) '$.metadata.authors'
) AS docs;

Результат этого запроса может выглядеть, как показано в следующей таблице:

Узнайте больше об анализе сложных типов данных, таких как файлы и контейнеры Parquet, в Azure Synapse Link для Azure Cosmos DB или вложенных структур в бессерверном пуле SQL.

Преобразование вложенных массивов в плоскую структуру

Данные Azure Cosmos DB могут иметь вложенные подмассивы, как, например, массив authors в наборе данных CORD-19:

{
    "paper_id": <str>,                      # 40-character sha1 of the PDF
    "metadata": {
        "title": <str>,
        "authors": [                        # list of author dicts, in order
            {
                "first": <str>,
                "middle": <list of str>,
                "last": <str>,
                "suffix": <str>,
                "affiliation": <dict>,
                "email": <str>
            },
            ...
        ],
        ...
}

В некоторых случаях требуется "присоединить" свойства верхнего элемента (metadata) ко всем элементам этого массива (authors). Бессерверный пул SQL позволяет преобразовать вложенные структуры в плоскую структуру путем применения функции OPENJSON к вложенному массиву:

SELECT
    *
FROM
    OPENROWSET(
      'CosmosDB',
      'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       Cord19
    ) WITH ( title varchar(1000) '$.metadata.title',
             authors varchar(max) '$.metadata.authors' ) AS docs
      CROSS APPLY OPENJSON ( authors )
                  WITH (
                       first varchar(50),
                       last varchar(50),
                       affiliation nvarchar(max) as json
                  ) AS a

Результат этого запроса может выглядеть, как показано в следующей таблице:

Сопоставления типов Azure Cosmos DB и SQL

Транзакционное хранилище Azure Cosmos DB не зависит от схемы, однако аналитическое хранилище схематизировано для оптимизации производительности аналитических запросов. Благодаря возможности автосинхронизации Azure Synapse Link Azure Cosmos DB управляет представлением схемы в готовом аналитическом хранилище, в том числе обработкой вложенных типов данных. Бессерверный пул SQL запрашивает аналитическое хранилище, поэтому важно понимать, как сопоставить типы входных данных Azure Cosmos DB с типами данных SQL.

Учетные записи Azure Cosmos DB API SQL (Core) поддерживают типы свойств JSON: число, строка, логическое значение, значение NULL, вложенный объект или массив. Нужно выбрать типы SQL, соответствующие этим типам JSON, если вы используете предложение WITH в функции OPENROWSET. В следующей таблице показаны типы столбцов SQL, которые следует использовать для различных типов свойств в Azure Cosmos DB.

Схема полной точности

Схема полной точности Azure Cosmos DB записывает как значения, так и их типы наилучшего соответствия для каждого свойства в контейнере. Функция OPENROWSET в контейнере со схемой полной точности предоставляет как тип, так и фактическое значение в каждой ячейке. Предположим, что следующий запрос считывает элементы из контейнера со схемой полной точности:

SELECT *
FROM OPENROWSET(
      'CosmosDB',
      'account=MyCosmosDbAccount;database=covid;region=westus2;key=C0Sm0sDbKey==',
       Ecdc
    ) as rows

Результат этого запроса будет возвращать типы и значения в формате текста JSON:

Для каждого значения можно увидеть тип, определенный в элементе контейнера Azure Cosmos DB. Большинство значений для свойства date_rep содержат значения date, но некоторые из них неправильно сохранены как строки в Azure Cosmos DB. Схема полной точности вернет как правильно типизированные значения date, так и неверно отформатированные значения string. Количество случаев — это данные, сохраненные в виде значений int32, но есть одно значение, которое введено как десятичное число. Это значение имеет тип float64. При наличии некоторых значений, превышающих максимальное число int32, они будут храниться как значения с типом int64. Все значения geo_id в этом примере хранятся в виде типов string.

Чтобы запросить учетные записи Azure Cosmos DB для MongoDB, вы можете узнать больше о полном представлении схемы точности в аналитическом хранилище и расширенных именах свойств, используемых в Аналитическом хранилище Azure Cosmos DB?.

Элементы запроса со схемой полной точности

При запросе схемы полной точности необходимо явно указать тип SQL и ожидаемый тип свойств Azure Cosmos DB в предложении WITH.

В следующем примере предполагается, что string — правильный тип для свойства geo_id, а int32 — правильный тип для свойства cases:

SELECT geo_id, cases = SUM(cases)
FROM OPENROWSET(
      'CosmosDB'
      'account=MyCosmosDbAccount;database=covid;region=westus2;key=C0Sm0sDbKey==',
       Ecdc
    ) WITH ( geo_id VARCHAR(50) '$.geo_id.string',
             cases INT '$.cases.int32'
    ) as rows
GROUP BY geo_id

Значения для geo_id и cases других типов будут возвращены как значения NULL. Этот запрос будет ссылаться только на cases с указанным типом в выражении (cases.int32).

Если имеются значения с другими типами (cases.int64, cases.float64), которые нельзя очистить в контейнере Azure Cosmos DB, то нужно явно указать их в предложении WITH и объединить результаты. В следующем запросе суммируются значения int32, int64 и float64, хранящиеся в столбце cases:

SELECT geo_id, cases = SUM(cases_int) + SUM(cases_bigint) + SUM(cases_float)
FROM OPENROWSET(
      'CosmosDB',
      'account=MyCosmosDbAccount;database=covid;region=westus2;key=C0Sm0sDbKey==',
       Ecdc
    ) WITH ( geo_id VARCHAR(50) '$.geo_id.string', 
             cases_int INT '$.cases.int32',
             cases_bigint BIGINT '$.cases.int64',
             cases_float FLOAT '$.cases.float64'
    ) as rows
GROUP BY geo_id

В этом примере количество случаев сохраняется как значения int32, int64 или float64. Все значения должны быть извлечены для вычисления количества случаев в каждой стране или регионе.

Устранение неполадок

Просмотрите страницу самостоятельной помощи, чтобы найти известные проблемы или устранить неполадки, которые помогут устранить потенциальные проблемы с запросами Azure Cosmos DB.

Следующие шаги

Дополнительные сведения см. в следующих статьях:

• Использование Power BI и бессерверного пула SQL с Azure Synapse Link
• Создание и использование представлений в бессерверном пуле SQL
• Руководство по созданию представлений бессерверного пула SQL в Azure Cosmos DB и подключению их к моделям Power BI с помощью DirectQuery
• Перейдите по ссылке Azure Synapse на страницу самостоятельной справки Azure Cosmos DB, если возникают некоторые ошибки или возникают проблемы с производительностью.
• Ознакомьтесь с модулем Learn по запросу Azure Cosmos DB с помощью SQL Serverless для Azure Synapse Analytics.