Выполните запрос данных Azure Cosmos DB, используя бессерверный пул SQL.

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

Для запроса Azure Cosmos DB полная SELECT область поверхности поддерживается с помощью функции OPENROWSET, которая включает большинство функций и операторов SQL. Вы также можете сохранять результаты запроса, который считывает данные из Azure Cosmos DB, вместе с данными в Azure Blob Storage или 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 с помощью SQL Serverless для Azure Synapse Analytics.

Предварительные условия

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

Обзор

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

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

Строка подключения SQL для Azure Cosmos DB включает в себя следующие компоненты:

• account — имя целевой учетной записи Azure Cosmos DB.
• database - Имя контейнера, указанное без кавычек в синтаксисе OPENROWSET. Если имя контейнера содержит специальные символы (например, тире -), оно должно быть заключено в квадратные скобки ([]).
• region (необязательно) — регион аналитического хранилища Cosmos DB. Если этот параметр опущен, используется основной регион контейнера.
• endpoint (необязательно) — URI конечной точки Cosmos DB (например https://<account name>.documents.azure.us), — это параметр, который требуется, если ваша учетная запись Cosmos DB не использует стандартный формат *.documents.azure.com.

Эти свойства можно определить из стандартной строки подключения Cosmos DB, например:

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

Эта строка подключения SQL может быть отформатирована следующим образом:

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

Эта connection string не включает сведения о проверке подлинности, необходимые для подключения к аналитическому хранилищу Cosmos DB. Дополнительная информация необходима в зависимости от используемого типа аутентификации:

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

Эти параметры описаны ниже.

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

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>

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

Примеры в этой статье основаны на данных Европейского центра профилактики и контроля заболеваний (ECDC) COVID-19 Cases and COVID-19 Open Research Dataset (CORD-19).

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

• Учетная запись базы данных Azure Cosmos DB Azure Synapse Link включена
• База данных Azure Cosmos DB с именем covid
• Два контейнера Azure Cosmos DB с именем Ecdc и Cord19 загружены с использованием предыдущих примеров наборов данных

Обратите внимание, что это подключение не гарантирует производительность, так как эта учетная запись может находиться в удаленном регионе по сравнению с конечной точкой Synapse SQL.

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

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

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       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 = 'yourcosmosdbkey';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=your-cosmosdb;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=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       Cord19) as cord19

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

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

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

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

{"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=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       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 = 'yourcosmosdbkey';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=your-cosmosdb;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows
   

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

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

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

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

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

CREATE CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'yourcosmosdbkey';
GO
CREATE OR ALTER VIEW Ecdc
AS SELECT *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=your-cosmosdb;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=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       Cord19)
WITH (  paper_id    varchar(8000),
        title        varchar(1000) '$.metadata.title',
        metadata     varchar(max),
        authors      varchar(max) '$.metadata.authors'
) AS docs;

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

Дополнительные сведения смотрите в статье Анализ сложных типов данных в Azure Synapse Analytics или Запрос вложенных типов в файлах Parquet и JSON с помощью бессерверного SQL пула.

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

Данные Azure Cosmos DB могут содержать вложенные подмассивы, такие как массив автора из набора данных 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>
            },
            ...
        ],
        ...
}

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

SELECT
    *
FROM
    OPENROWSET(
      'CosmosDB',
      'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       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 number, string, Boolean, null, nested object или array. Нужно выбрать типы 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 и бессерверный пул Synapse SQL для анализа данных Azure Cosmos DB
• Создание и использование представлений с помощью бессерверного пула SQL
• Учебник: Изучайте и анализируйте хранилища данных с использованием бессерверного пула SQL
• Если возникают ошибки или возникают проблемы с производительностью, см. статью "Устранение неполадок бессерверного пула SQL"
• Module: реализация Azure Synapse Link с помощью Azure Cosmos DB