Google Big Query

Импорт данных из Google Sheets в Google Big Query

Материал обновлен:
20:11:2020

Настроим бесплатный импорт данных в Google Big Query из Google Sheets

Когда может понадобится

Для чего может понадобится такой импорт? Один из вариантов использования такого решения – безопасное хранение данных и их использование в Power BI. Схема такова:

  • изначально имеем таблицу в Google Sheets
  • настраиваем импорт из Google Sheets в Google Big Query
  • подключаемся из Power BI к Google Big Query
  • получаем данные таблицы Google Sheets для работы в Power BI через Google Big Query

Настройка импорта

Переходим к проекту в Big Query и создаем в нем новый набор данных:

Создание набора данных

Создание набора данных

Место хранения данных и его ID вы указываете исходя их ваших условий. Обратите внимание, что в ID набора данных можно использовать только буквы, цифры и  нижнее подчеркивание. Нажимаем Create Dataset.

Выбираем созданный набор данных и добавим в него таблицу:

Создание таблицы

Создание таблицы

Откроется блок с настройками для создания новой таблицы в Google Big Query:

Настройки при создании таблицы

Настройки при создании таблицы

Указываем, что таблица в Big Query должна быть создана на основе Sheets:

Таблица на основе Sheets

Таблица на основе Sheets

Следующий шаг – указать URL таблицы, воспользуемся подсказкой чтобы получить дополнительную информацию:

Подсказка по адресу таблицы

Подсказка по адресу таблицы

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

Выбор листа в таблице для импорта

Выбор листа в таблице для импорта

Тут также есть подсказка, которую как минимум один раз стоит изучить. Моя тестовая таблица выглядит так:

Исходная таблица

Исходная таблица

Все данные на одном листе Лист1, его я и укажу в этом поле. Далее переходим к настройкам Big Query, а именно проекта, набора данных, в которых будет создана таблица с данными, а также ее название:

Название таблицы

Название таблицы

Следующий этап – указание схемы для таблицы. Схема это названия столбцов и их типы (типы данных, которые будут в них храниться). Можно указать явно эти параметры, а можно использовать возможность автоматического определения. Я выбираю второй вариант:

Создание схемы для новой таблицы

Создание схемы для новой таблицы

При необходимости можно задать и дополнительные параметры, такие как:

  • игнорирование некоторых значений
  • пропуск строк, которые являются заголовками таблицы в Google Sheets (я укажу здесь 1, чтобы первая строка не попала в таблицу) и др.

После того как все настройки указаны нажимаем Create table. После того как таблица создана в левом нижнем углу появится уведомление:

Уведомление о создании таблицы

Уведомление о создании таблицы

Также таблица будет в списке доступных у выбранного набора данных:

Таблица в наборе данных

Таблица в наборе данных

Посмотрим что в ней содержится и какую структуру она имеет:

Структура новой таблицы

Структура новой таблицы

Как видно все столбцы имеют не самое понятное название и тип данных у них определен как STRING. Попробуем решить эту проблему. Исправить значения в Google Biq Query у нас не получится. Создадим новую таблицу, но предварительно перейдем в Google Sheets и изменим там типы данных в столбцах.

Повторим процесс создания таблицы, но теперь дополнительно укажем еще и ее схему:

Схема, созданная вручную

Схема, созданная вручную

Результат изменился. Столбцы имеют удобные названия и типы данных:

Результат для новой схемы

Результат для новой схемы

Выполним запрос на выборку всех данных и получим необходимые сведения из Google Sheets в Google BigQuery. Если понадобится добавить данные в таблицы Sheets – вносите их, в Big Query после этого ничего менять не нужно. При выполнении запросов обновленные данные автоматически подтянутся:

Перенесенные данные из Sheets в Big Query

Перенесенные данные из Sheets в Big Query

Теперь осталось подключиться к Big Query из Power BI и загрузить необходимые данные для последующей работы в нем.

На что обратить внимание

При создании таблицы в Google Big Query указывайте явно типы данных и названия столбцов, которые хотите получить в результате

Таблицы в Google Sheets приведите к “нормальному виду”, без объединенных ячеек, отдельных ячеек справа от таблицы с примечаниями и т.п. Если необходимо явно указывайте диапазон для импорта данных

При создании таблицы не забудьте указать сколько строк пропустить, которые являются заголовками таблицы в Sheets

В таблице Google Sheets в одном столбце храните один тип данных

Метки не заданы

Рассылка бесплатных кейсов, инструкций, обзоров

Настройки, интеграции, примеры реальных задач, пошаговые инструкции


mode_edit