Настроим бесплатный импорт данных в 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
Следующий шаг — указать 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
Теперь осталось подключиться к Big Query из Power BI и загрузить необходимые данные для последующей работы в нем.
На что обратить внимание
При создании таблицы в Google Big Query указывайте явно типы данных и названия столбцов, которые хотите получить в результате
Таблицы в Google Sheets приведите к “нормальному виду”, без объединенных ячеек, отдельных ячеек справа от таблицы с примечаниями и т.п. Если необходимо явно указывайте диапазон для импорта данных
При создании таблицы не забудьте указать сколько строк пропустить, которые являются заголовками таблицы в Sheets
В таблице Google Sheets в одном столбце храните один тип данных
