Использование драйвера ODBC Azure Cosmos DB для подключения к средствам бизнес-аналитики и аналитики данных

  • Статья

ОБЛАСТЬ ПРИМЕНЕНИЯ: NoSQL

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

Azure Cosmos DB — это база данных без схемы, которая позволяет быстро разрабатывать приложения и выполнять итерацию моделей данных без ограничений строгой схемы. Отдельная база данных Azure Cosmos DB может содержать документы JSON разной структуры. Чтобы проанализировать или составить отчет по этим данным, вам может потребоваться выполнить сведение данных, чтобы они соответствовали схеме.

Драйвер ODBC нормализует данные Azure Cosmos DB в таблицы и представления, соответствующие вашим потребностям в аналитике данных и отчетности. Нормализованные схемы позволяют использовать инструменты, совместимые с ODBC, для доступа к данным. Схемы не влияют на базовые данные и не требуют от разработчиков их соблюдения. Драйвер ODBC помогает сделать базы данных Azure Cosmos DB полезными для аналитиков данных и групп разработчиков.

Вы можете выполнять операции SQL с нормализованными таблицами и представлениями, включая группировку по запросам, вставку, обновление и удаление. Драйвер совместим с ODBC 3.8 и поддерживает синтаксис ANSI SQL-92.

Важно!

Рассмотрите возможность использования Azure Synapse Link для Azure Cosmos DB для создания таблиц и представлений для данных. Synapse Link имеет явные преимущества производительности для больших наборов данных по сравнению с драйвером ODBC. Вы также можете подключить нормализованные данные Azure Cosmos DB к другим программным решениям, таким как SQL Server Integration Services (SSIS), QlikSense, Tableau и другим аналитическим программным обеспечением, бизнес-аналитикой и средствами интеграции данных. Вы можете использовать эти решения для анализа, перемещения, преобразования и создания визуализаций с данными Azure Cosmos DB.

Важно!

  • Подключение к Azure Cosmos DB с помощью драйвера ODBC в настоящее время поддерживается только для Azure Cosmos DB для NoSQL.
  • Текущий драйвер ODBC не поддерживает агрегатные процессы pushdown и имеет известные проблемы с некоторыми средствами аналитики. Пока не будет выпущена новая версия, можно использовать один из следующих вариантов:
    • Azure Synapse Link — это предпочтительное решение для аналитики для Azure Cosmos DB. С помощью Azure Synapse Link и Azure Synapse бессерверных пулов SQL можно использовать любое средство бизнес-аналитики для извлечения аналитических сведений практически в реальном времени из SQL Azure Cosmos DB или API для данных MongoDB.
    • Для Power BI можно использовать соединитель Azure Cosmos DB для Power BI.
    • Сведения о Qlik Sense см. в статье Подключение Qlik Sense к Azure Cosmos DB.

Установка драйвера ODBC и подключение к базе данных

  1. Скачайте драйверы для своей среды:

    Установщик Поддерживаемые операционные системы
    Microsoft Azure Cosmos DB ODBC 64-bit.msi для 64-разрядной версии Windows. 64-разрядные версии Windows 8.1 или более поздней версии, Windows 8, Windows 7. 64-разрядные версии Windows Server 2012 R2, Windows Server 2012 и Windows Server 2008 R2.
    Microsoft Azure Cosmos DB ODBC 32x64-bit.msi для 32-разрядной версии на 64-разрядной версии Windows. 64-разрядные версии Windows 8.1 или более поздней версии, Windows 8, Windows 7, Windows XP, Windows Vista. 64-разрядные версии Windows Server 2012 R2, Windows Server 2012, Windows Server 2008 R2 и Windows Server 2003.
    Microsoft Azure Cosmos DB ODBC 32-bit.msi для 32-разрядной версии Windows. 32-разрядные версии Windows 8.1 или более поздней версии, Windows 8, Windows 7, Windows XP и Windows Vista.

  2. Запустите файл .msi локально. После этого запустится мастер установки драйвера ODBC для Microsoft Azure Cosmos DB.

  3. Завершите работу с мастером установки, введя значения по умолчанию.

  4. После установки драйвера введите Источники данных ODBC в поле поиска Windows и откройте раздел Администратор источника данных ODBC.

  5. Убедитесь, что драйвер Microsoft Azure DocumentDB ODBC Driver указан на вкладке Драйверы.

    Снимок экрана: окно

  6. Перейдите на вкладку Пользовательское имя DSN и щелкните Добавить, чтобы создать новое имя источника данных (DSN). Вы также можете создать системное имя DSN.

  7. В окне Создание нового источника данных выберите Microsoft Azure DocumentDB ODBC Driver и щелкните Готово.

  8. В окне Настройка имени DSN DocumentDB ODBC Driver укажите следующее:

    Снимок экрана: окно настройки сервера доменных имен (DNS).

    • Имя источника данных — понятное имя DSN ODBC. Имя является уникальным для этой учетной записи Azure Cosmos DB.
    • Описание — краткое описание источника данных.
    • Узел — универсальный код ресурса (URI) для учетной записи Azure Cosmos DB. Эти сведения можно получить на странице Ключи в учетной записи Azure Cosmos DB на портале Azure.
    • Ключ доступа — первичный или вторичный ключ для чтения и записи или только для чтения на странице Ключи Azure Cosmos DB на портале Azure. Рекомендуется использовать ключи только для чтения, если вы используете DSN для обработки данных и создания отчетов только для чтения.

    Чтобы избежать ошибки проверки подлинности, используйте кнопки копирования, чтобы скопировать URI и ключ с портала Azure.

    Снимок экрана: страница

    • Зашифровать ключ доступа для — выберите наилучший вариант исходя из того, кто использует этот компьютер.

  9. Щелкните Тестировать, чтобы подключиться к учетной записи Azure Cosmos DB.

  10. Щелкните Дополнительные параметры и задайте следующе:

    • Версия REST API: выберите версию REST API для выполнения своих операций. Значение по умолчанию — 2015-12-16.

      Если у вас есть контейнеры с большими ключами секций , которым требуется версия 2018-12-31REST API , введите 2018-12-31, а затем выполните действия, описанные в конце этой процедуры.

    • Query Consistency (Согласованность запросов). Выберите уровень согласованности операций. Значение по умолчанию — Сеанс.

    • Число повторных попыток — укажите число повторных попыток выполнения операции, если первоначальный запрос не был выполнен из-за ограничений скорости службы.

    • Файл схемы — если вы не выберете файл схемы, драйвер отсканирует первую страницу данных для каждого контейнера, чтобы определить его схему, называемую сопоставлением контейнера, для каждого сеанса. Этот процесс может вызвать длительный запуск приложений, которые используют DSN. Лучше всего связать файл схемы с DSN.

      • Если у вас уже есть файл схемы, выберите Обзор, перейдите к файлу, а затем щелкните Сохранить и ОК.

      • Если у вас еще нет файла схемы, щелкните ОК, а затем выполните действия, описанные в следующем разделе, чтобы создать определение схемы. После создания схемы вернитесь в окно Дополнительные параметры, чтобы добавить файл схемы.

После нажатия кнопки ОК для завершения настройки и закрытия окна Настройка DSN ODBC DocumentDB Driver новое пользовательское имя DSN появится на вкладке Пользовательское имя DSN в окне Администратор источника данных ODBC.

Снимок экрана: новое пользовательское имя D S N на вкладке пользовательских имен D S N.

Изменение реестра Windows для поддержки REST API версии 2018-12-31

Если у вас есть контейнеры с большими разделами, для которых требуется REST API версии 2018-12-31, выполните следующие действия, чтобы обновить реестр Windows для поддержки этой версии.

  1. В меню Пуск введите regedit, чтобы найти и открыть приложение Редактор реестра.

  2. В редакторе реестра используйте путь Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI.

  3. Создайте новый вложенный ключ с тем же именем, что и имя DSN, например Contoso Account ODBC DSN.

  4. Перейдите к новому вложенному ключу Contoso Account ODBC DSN и щелкните правой кнопкой мыши, чтобы добавить новое значение String.

    • Имя значения: IgnoreSessionToken

    • Данные значения: 1

    Снимок экрана: параметры редактора реестра Windows.

Создание определения схемы

Есть два типа методов выборки, которые можно использовать для создания схемы: сопоставление контейнеров или сопоставление разделителей таблиц. Сеанс выборки может использовать оба метода выборки, но каждый контейнер может использовать только один из методов. Какой метод следует использовать, зависит от характеристик данных.

  • Сопоставление контейнеров извлекает данные на странице контейнера, чтобы определить структуру данных, и транспонирует контейнер в таблицу на стороне ODBC. Данный метод выборки работает эффективно и быстро при использовании в контейнерах однородных данных.

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

    Например, если документ содержит свойство Тип, можно ограничить выборку значениями этого свойства. В результате выборки вы получите набор таблиц для каждого выбранного значения Type. Type = Car создает таблицу Car, а Type = Plane — таблицу Plane.

Чтобы определить схему, выполните следующие действия. Для метода сопоставления разделителей таблиц необходимо выполнить дополнительные шаги для определения атрибутов и значений схемы.

  1. На вкладке Пользовательское имя DSN в окне Администратор источника данных ODBC выберите свое пользовательское имя DSN Azure Cosmos DB и щелкните Настроить.

  2. В окне Настройка DSN DocumentDB ODBC Driver щелкните Редактор схем.

    Снимок экрана: кнопка редактора схем в окне настройки D S N.

  3. В окне Редактор схем щелкните Создать.

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

  5. Чтобы использовать метод сопоставления контейнеров, щелкните Выборка.

    Или, чтобы использовать сопоставление разделителей таблиц, выполните следующие действия, чтобы определить атрибуты и значения для ограничения выборки.

    1. Щелкните Изменить в столбце Определение сопоставления для имени DSN.

    2. Затем в окне Определение сопоставления в разделе Метод сопоставления щелкните Разделители таблиц.

    3. В поле Атрибуты введите имя свойства разделителя в документе, для которого требуется ограничить выборку, например City. Нажмите клавишу ВВОД.

    4. Если вы хотите ограничить выборку определенными значениями для введенного атрибута, выберите атрибут, а затем введите значение в поле Значение, например Seattle, и нажмите клавишу ВВОД. Вы можете добавить несколько значений для атрибутов. Просто убедитесь, что при вводе значений выбран правильный атрибут.

    5. Завершив ввод атрибутов и значений, щелкните ОК.

    6. В окне Создание схемы щелкните Выборка.

  6. На вкладке Конструктор укажите схему. Конструктор представляет базу данных, схему и таблицу. В табличном представлении отображается набор свойств, связанных с именами столбцов, таких как Имя SQL и Имя источника.

    Для каждого столбца вы можете изменить значения Имя SQL, Тип SQL, Длина SQL, Масштаб, Точность и Допускает значение NULL.

    Для параметра Скрыть столбец можно задать значение true, если необходимо исключить этот столбец из результатов запроса. Столбцы с пометкой Hide Column = true не возвращаются для выбора и проекции, хотя они по-прежнему являются частью схемы. Например, можно скрыть все обязательные системные свойства Azure Cosmos DB, начинающиеся с _. Столбец id является единственным полем, которое нельзя скрыть, так как это первичный ключ в нормализованной схеме.

  7. Завершив определение схемы, выберите Файл>Сохранить, перейдите в каталог, чтобы сохранить схему, и щелкните Сохранить.

  8. Чтобы использовать эту схему с DSN, в окне Настройка DSN DocumentDB ODBC Driver выберите Дополнительные параметры. Выберите поле Файл схемы, перейдите к сохраненной схеме, щелкните ОК, а затем еще раз щелкните ОК. Сохранение файла схемы изменяет подключение DSN для определения области данных и структуры, определенных схемой.

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

При необходимости можно определить и создать представления в окне Редактор схем во время выборки. Эти представления аналогичны представлениям SQL. Представления доступны только для чтения и охватывают выборки и проекции определенного SQL-запроса Azure Cosmos DB.

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

  1. На вкладке Представление выборки в окне Редактор схем выберите контейнеры для выборки и щелкните Добавить в столбце Определение представления.

    Снимок экрана: создание представления в драйвере.

  2. В окне Определения представлений щелкните Создать. Присвойте имя представлению, например EmployeesfromSeattleView и щелкните ОК.

  3. В окне Изменение представления введите запрос Azure Cosmos DB, например:

    SELECT c.City, c.EmployeeName, c.Level, c.Age, c.Manager FROM c WHERE c.City = "Seattle"

  4. Щелкните ОК.

    Снимок экрана: добавление запроса при создании представления.

Вы можете создать столько представлений, сколько захотите. Завершив определение представлений, щелкните Выборка, чтобы начать выборку данных.

Важно!

Текст запроса в определении представления не должен содержать разрывы строк. В противном случае при предварительном просмотре представления появится общая ошибка.

Отправка запросов с помощью SQL Server Management Studio

После настройки пользовательского имени DSN драйвера ODBC Azure Cosmos DB вы можете запрашивать Azure Cosmos DB из SQL Server Management Studio (SSMS), настроив подключение к связанному серверу.

  1. Установите SQL Server Management Studio и подключитесь к первому серверу SQL Server.

  2. В редакторе запросов SSMS создайте объект связанного сервера для источника данных, выполнив следующие команды. Замените DEMOCOSMOS именем связанного сервера, а SDS Name — именем источника данных.

    USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'DEMOCOSMOS', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'SDS Name'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DEMOCOSMOS', @useself=N'False', @locallogin=NULL, @rmtuser=NULL, @rmtpassword=NULL

GO

Чтобы увидеть имя нового связанного сервера, обновите список связанных серверов.

Снимок экрана: связанный сервер в S S M S.

Чтобы запросить связанную базу данных, введите запрос SSMS. В этом примере запрос выбирает данные из таблицы в контейнере с именем customers:

SELECT * FROM OPENQUERY(DEMOCOSMOS, 'SELECT *  FROM [customers].[customers]')

выполнение запроса. Результаты должны выглядеть примерно так:

attachments/  1507476156    521 Bassett Avenue, Wikieup, Missouri, 5422   "2602bc56-0000-0000-0000-59da42bc0000"   2015-02-06T05:32:32 +05:00 f1ca3044f17149f3bc61f7b9c78a26df
attachments/  1507476156    167 Nassau Street, Tuskahoma, Illinois, 5998   "2602bd56-0000-0000-0000-59da42bc0000"   2015-06-16T08:54:17 +04:00 f75f949ea8de466a9ef2bdb7ce065ac8
attachments/  1507476156    885 Strong Place, Cassel, Montana, 2069       "2602be56-0000-0000-0000-59da42bc0000"   2015-03-20T07:21:47 +04:00 ef0365fb40c04bb6a3ffc4bc77c905fd
attachments/  1507476156    515 Barwell Terrace, Defiance, Tennessee, 6439     "2602c056-0000-0000-0000-59da42bc0000"   2014-10-16T06:49:04 +04:00      e913fe543490432f871bc42019663518
attachments/  1507476156    570 Ruby Street, Spokane, Idaho, 9025       "2602c156-0000-0000-0000-59da42bc0000"   2014-10-30T05:49:33 +04:00 e53072057d314bc9b36c89a8350048f3

Просмотр данных в Power BI Desktop

Вы можете использовать DSN для подключения к Azure Cosmos DB с помощью любых средств, совместимых с ODBC. В этой процедуре показано, как подключиться к Power BI Desktop для создания визуализации Power BI.

  1. В Power BI Desktop выберите Получение данных.

    Снимок экрана: получение данных в Power B I Desktop.

  2. В окне Получение данных выберите Другое>ODBC и щелкните Подключиться.

    Снимок экрана: выбор источника данных ODBC в Power BI Get Data.

  3. В окне Из ODBC выберите созданное имя DSN и щелкните ОК.

    Снимок экрана: выбор D S N в интерфейсе получения данных Power B I.

  4. В окне Получение доступа к источнику данных с помощью драйвера ODBC выберите По умолчанию или пользовательский и щелкните Подключиться.

  5. В окне Навигатор в области слева разверните базу данных и схему, а затем выберите таблицу. Область результатов включает данные, которые используют созданную вами схему.

    Снимок экрана: выбор таблицы в интерфейсе получения данных Power B I.

  6. Чтобы визуализировать данные в Power BI Desktop, установите флажок рядом с именем таблицы и щелкните Загрузить.

  7. В Power BI Desktop перейдите на вкладку Данные в левой части экрана, чтобы подтвердить импорт данных.

  8. Перейдите на вкладку Отчет слева от экрана, щелкните Создать визуальный элемент на ленте и настройте визуальный элемент.

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

  • Проблема. При попытке подключения возникает следующая ошибка:

    [HY000]: [Microsoft][Azure Cosmos DB] (401) HTTP 401 Authentication Error: {"code":"Unauthorized","message":"The input authorization token can't serve the request. Please check that the expected payload is built as per the protocol, and check the key being used. Server used the following payload to sign: 'get\ndbs\n\nfri, 20 jan 2017 03:43:55 gmt\n\n'\r\nActivityId: 9acb3c0d-cb31-4b78-ac0a-413c8d33e373"}

    Решение. Убедитесь, что значения Узел и Ключ доступа, скопированные на портале Azure, правильные, и повторите попытку.

  • Проблема. Возникает следующая ошибка в SSMS при попытке создать связанный сервер Azure Cosmos DB:

    Msg 7312, Level 16, State 1, Line 44

Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "DEMOCOSMOS". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

    Решение. Связанный сервер базы данных Azure Cosmos DB не поддерживает четырехкомпонентное именование.

Дальнейшие действия