Как связать Microsoft Power BI и Google BigQuery

Если вы храните данные в Google BigQuery, то наверняка захотите визуализировать их. В этой статье я расскажу, как загрузить данные из Google BigQuery в Microsoft Power BI с помощью скрипта на языке R.

Картинки по запросу Как связать Microsoft Power BI и Google BigQuery

Интеграция Google BigQuery c BI-платформами и электронными таблицами

Ранее я уже рассказывал, как связывать Google BigQuery с различными инструментами визуализации, но интеграция Google BigQuery с Microsoft Power BI — отдельная тема.

Оба сервиса динамично развиваются, их возможности регулярно улучшаются, благодаря чему BigQuery и Power BI так популярны. При этом Google и Microsoft во многом конкурируют, поэтому, как правило, интеграции между их продуктами нет, и в ближайшем будущем не стоит ждать появления коннектора к Google BigQuery в программном интерфейсе Microsoft Power BI.

Как начать работу с BigData — загружаем данные в Google BigQuery

На самом деле есть несколько вариантов решения этой проблемы, в данной статье мы рассмотрим два способа загрузить данные из Google BigQuery в Microsoft Power BI:

  • с помощью ODBC драйвера для Google BigQuery;
  • с помощью R коннектора.

Как связать Google BigQuery и Microsoft Power BI с помощью ODBC драйвера?

Первый способ стал доступен в ноябре 2016 года вместе с выходом ODBC драйвера для Google BigQuery.

1. Скачайте и установите версию ODBC драйвера, соответствующую вашей операционной системе. Найти нужную версию можно, перейдя по этой ссылке.

2. Создайте источник данных ODBC.

2.1. Перейдите в Панель управления / Все элементы панели управления / Администрирование / Источники данных ODBC (нужной разрядности).

Создайте источник данных ODBC

2.2. В окне «Администратор источника данных ODBC» перейдите на вкладку «Пользовательский DSN» и нажмите «Добавить».

В окне «Администратор источника данных ODBC» перейдите на вкладку «Пользовательский DSN» и нажмите «Добавить»

2.3. В окне «Создание нового источника данных» выбираете драйвер «Simba ODBC Driver for Google BigQuery» и нажимаете «Готово».

В окне «Создание нового источника данных» выбираете драйвер «Simba ODBC Driver for Google BigQuery» и нажимаете «Готово»

2.4. Далее откроется окно настройки ODBC подключения. Необходимо заполнить поле Data Source Name, выбрать из выпадающего списка OAuth mechanism и нажать Sign in для прохождения процесса аутентификации.

Необходимо заполнить поле Data Source Name, выбрать из выпадающего списка OAuth mechanism и нажать Sign in для прохождения процесса аутентификации

2.5. Выбираете Google аккаунт, у которого есть доступ к нужному проекту в Google BigQuery, и подтверждаете разрешение на получение автономного доступа.

Выбираете Google аккаунт, у которого есть доступ к нужному проекту в Google BigQuery

Выбираете Google аккаунт, у которого есть доступ к нужному проекту в Google BigQuery, и подтверждаете разрешение на получение автономного доступа

2.6. Далее вам будет предоставлен токен доступа, который необходимо скопировать и вставить в поле Confirmation code, после чего просто кликните по полю Refresh token для генерации токена обновления.

Далее вам будет предоставлен токен доступа, который необходимо скопировать и вставить в поле Confirmation code

просто кликните по полю Refresh token для генерации токена обновления

2.7. Из выпадающего списка Catalog (Project) выберите нужный проект и нажмите Test.

Из выпадающего списка Catalog (Project) выберите нужный проект и нажмите Test

2.8. Драйвер сообщит, что тест соединения пройден успешно. После этого нажмите ОК в окне Test Result и повторно — в окне настройки драйвера.

После этого нажмите ОК в окне Test Result и повторно — в окне настройки драйвера.

3. Перейдите в Power BI и настройте подключение к созданному источнику данных.

3.1. На вкладке «Главная» основного меню Power BI нажмите «Получить данные», а в группе «Другое» выберите пункт ODBC.

На вкладке «Главная» основного меню Power BI нажмите «Получить данные», а в группе «Другое» выберите пункт ODBC

3.2. В выпадающем списке «Имя источника данных (DSN)» выберите созданный в пункте 2 источник. В данном случае я его назвал BQ ODBC.

В выпадающем списке «Имя источника данных (DSN)» выберите созданный в пункте 2 источник

3.3. В окне «Драйвер ODBC» выберите тип подключения «По умолчанию или пользовательский» и нажмите «Подключение».

В окне «Драйвер ODBC» выберите тип подключения «По умолчанию или пользовательский» и нажмите «Подключение»

3.4. В окне «Навигатор» выбирайте нужные проекты, наборы данных и таблицы. Нажмите «Загрузить», после этого данные из Google BigQuery будут загружены в Power BI.

В окне «Навигатор» выбирайте нужные проекты, наборы данных и таблицы. Нажмите «Загрузить», после этого данные из Google BigQuery будут загружены в Power BI

Как связать Google BigQuery и Microsoft Power BI с помощью R коннектора?

В марте 2016 года Microsoft объявил, что поддержка R-скриптов для загрузки данных в Power BI вышла из бета-тестирования. Безусловно, это одно из наиболее значимых обновлений Power BI, так как с помощью языка R можно подгружать данные практически из любого источника, включая Google BigQuery.

1. Установите пакет «bigrquery»

1.1. Первое, что необходимо сделать, — скачать и установить последнюю версию R. При установке не требуется задавать какие-то дополнительные параметры или настройки, в связи с чем затруднений возникнуть не должно.

1.2. Для более удобной работы Google BigQuery из R установите пакет «bigrquery». Для этого перейдите в папку «C:\Program Files\R\R-3.3.0\bin» и запустите файл «R.exe». Далее откроется консоль для работы с R.

Установите пакет «bigrquery»
В консоли установите нужный пакет с помощью команды «install.packages(«bigrquery»)».

Установите нужный пакет с помощью команды «install.packages(

1.3. На диске C создайте папку «BQ_Integration», в ней мы будем сохранять учетные данные для доступа к API Google BigQuery.

Создайте папку «BQ_Integration»

1.4. Далее в консоли R с помощью команды «setwd(«C:/BQ_Integration»)» укажите, что созданную папку будем использовать в качестве рабочей директории. Если вы все сделали правильно, можете проверить установленную рабочую директорию с помощью команды «getwd()».

Проверить установленную рабочую директорию

1.5. Далее подключаем пакет «bigrquery», установленный ранее с помощью «library(bigrquery)».

2. Предоставьте доступ к данным BigQuery

2.1. Для прохождения первой аутентификации запустите запрос к любой таблице BigQuery из консоли. Для этого воспользуйтесь следующим кодом:

projectID <- "XXXXXXX"  #Название проекта, получить его можно из URL
dataSetID <- "XXXXXXX" #Название набора данных, смотреть в интерфейсе BigQuery
tableID 

2.2. Далее в консоли на вопрос «Use a local file .httr-oauth to cache OAuth access credentials between R sessions?» ответьте «Yes». К этому моменту в консоли должен быть следующий код:

Предоставьте доступ к данным BigQuery

2.3. Далее выберите аккаунт Google и подтвердите разрешение на просмотр данных и управление ими в Google BigQuery, а также просмотр и изменение данных в сервисах Google облачной платформы.

Выберите аккаунт Google

подтвердите разрешение на просмотр данных и управление ими в Google BigQuery

2.4. После этого вы получите подтверждение, что аутентификация прошла удачно.
аутентификация прошла удачно

Получите подтверждение

2.5. На этом этапе работа с консолью R завершена. В папке «BQ_Integration», созданной на шаге 3, должен появится файл «.httr-oauth», который содержит ваши учётные данные.

3. Интеграция с Power BI

3.1. Перейдите в интерфейс Power BI и нажмите кнопку для получения данных. В группе «Другое» выберите коннектор «R-скрипт».

Скачать бесплатно последнюю версию Power BI можно по этой ссылке.

Нажмите кнопку для получения данных

3.2. Далее появится диалоговое окно «Выполнение сценария R». Подставьте в следующий код свои значения ID проекта, набора данных и таблицы и введите его в диалоговое окно.

library(bigrquery)
setwd("C:/BQ_Integration")
projectID <- "XXXXXXX"
dataSetID <- "XXXXXXXXX"
tableID <- "XXXXXXXX"

qry 

Выполнение сценария R

Если вы знакомы с SQL, то в кавычках вместо «qry

Как работать с оконными функциями в Google BigQuery — подробное руководство

Читайте об оконных функциях и принципах их работы в Google BigQuery.

3.3. По нажатию кнопки «ОК» начнется процесс загрузки данных, после чего появится окно «Навигатор» с загруженной таблицей и ее полями.

окно «Навигатор» с загруженной таблицей и ее полями

3.4. После нажатия кнопки «Загрузить», вы сможете строить визуализацию данных на основе загруженной таблицы в Microsoft Power BI.
Данные будут обновляться каждый раз при нажатии кнопки «Обновить».

Данные будут обновляться каждый раз при нажатии кнопки «Обновить»

Как работать с Microsoft Power BI — подробное руководство

Подробно узнать методы построения визуализации на основе загруженных в Power BI данных можно в статье «Как работать с Microsoft Power BI — подробное руководство».

Выводы

Три шага для связки Microsoft Power BI и Google BigQuery с помощью ODBC драйвера:

  1. Скачайте и установите ODBC драйвер.
  2. Создайте источник данных ODBC.
  3.  Перейдите в Power BI и настройте подключение к созданному источнику данных.

Три шага для связки Microsoft Power BI и Google BigQuery с помощью R коннектора:

  1. Установить язык R и подключить пакет «bigrquery».
  2. Предоставить доступ к данным BigQuery.
  3. Загрузить данные в Power BI и построить визуализацию.

В следующих постах поделюсь фишками загрузки данных в Power BI из других источников, к которым нет встроенных коннекторов.


Оригинал публикации на блоге Netpeak.

Как связать Microsoft Power BI и Google BigQuery: 6 комментариев

Добавьте свой

  1. Добрый день!

    При попытке подключится через R коннектов в консоли выдает ошибку

    Error in bq_auth()

    в чем может быть проблема ?

    Нравится

    1. Здравствуйте, статья несколько устарела, и в целом вы дали маловато информации что бы я мог вам подсказать.

      Попробуйте для начала весь написанный вами код на R выполнить в среде разработки RStudio.

      Она даст по меньшей мере больше информации об ошибке.

      Нравится

  2. Алексей, приветствую.
    Подскажите, пожалуйста.

    Переношу данные из R в bigquery

    library(bigrquery)
    setwd(«C:/direct/tokens»)
    insert_upload_job(«xxx», «yyy», «zzz», Table, «xxx», create_disposition = «CREATE_NEVER», write_disposition = «WRITE_APPEND»)

    далее у меня появляется вопрос:

    Is it OK to cache OAuth access credentials in the folder ‘C:/Users/\xc0\xeb\xe5\xea\xf1\xe0\xed\xe4\xf0/.R/gargle/gargle-oauth’ between R sessions?

    1: Yes
    2: No

    Selection:

    Если я отвечу yes,
    то появляется ошибка:

    Ошибка: Can’t get Google credentials.
    Are you running bigrquery in a non-interactive session? Consider:
    * Call bq_auth() directly with all necessary specifics.

    Если отвечу No,
    то далее аутентификация проходит нормально, данные отправляются.

    НО до следующего открытия R-Studio. Потом все заново

    Пока вручную включал скрипт — все устраивало, но теперь есть необходимость автоматизировать процесс, и здесь затык.

    Спасибо.

    Нравится

    1. Здравствуйте, эта стать уже от части устарела в bigrquery добавились другие функции, и поменялся процесс авторизации.

      Вам надо установить именно с гитхаба пакеты bigrquery и gargle, делается это через пакет devtools и функцию install_github().

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

      Там пример с гугл таблицами, но у вас будет примерно тоже самое, но через функцию bq_auth().

      https://habr.com/ru/post/488756/

      Нравится

      1. Все вышло и теперь очень удобно без авторизации через браузер. Наконец-то удалось подключить скрипт в планировщик задач!
        Спасибо, Алексей!

        Нравится

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Google photo

Для комментария используется ваша учётная запись Google. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Блог на WordPress.com. Тема: Baskerville 2, автор: Anders Noren.

Вверх ↑

%d такие блоггеры, как: