Мы в Mello очень переживаем за корректность и безопасность данных наших клиентов. Для тех, кто ценит безопасность мы решили опубликовать эту статью и решение. Руководствуясь инструкцией вы можете сами развернуть передачу данных из Google Spreadsheets в базы.
Excel и Google Spreadsheets самые популярные сервисы среди маркетологов для сбора и анализа данных. Excel привлекает своими вычислительными возможностями, а Google Spreadsheets простотой (вам просто нужен браузер для работы), удобный совместной работой с файлом через интернет и отсутствием платы за лицензии для использования. С развитием программ для визуализации данных, появилась потребность подключаться к созданным таблицам и строить по ним визуализацию.
В этой статье в качестве программы для визуализации будет выступать Power BI. Когда мы говорим про подключению к файлу Excel, то все отлично:
- подключение идет к файлу, проблем с безопасностью нет;
- в Power BI Service с помощью настройки шлюза можно сделать автоматическое обновление отчета.
Когда речь заходит о подключении к Google Spreadsheets, то возникает одна проблема. Чтобы забрать данные из Google Spreadsheets, нужно опубликовать файл в интернете с доступом по ссылке. Другими словами, любой кто имеет ссылку может посмотреть данные. Вы можете сказать, что ссылка есть только у человека который делал отчет в Power BI, поэтому все в порядке. А вот и нет, проблема в том, что эта ссылка доступна для индексации поисковыми системами и может оказаться в результатах поиска. В итоге это может привести к печальным исходам, например, как было когда в сеть утекли открытые доски в Trello. Тогда в публичном доступе оказались задачи таких компаний как Ростелеком, Acronis, МТС. Приятного мало.
Использование Google Spreadsheets в качестве небольших баз данных
На первый взгляд может показаться, что большие компании и без Google Spreadsheets разберутся, а маленьким это не нужно, потому что ценность данных для них сильно меньше. Оба утверждения не корректны.
Среди маленьких компаний я встречал игроков которые очень жестко бились между собой, вплоть до промышленного шпионажа. Уверен, что руководству компании, которая ведет такую политику не понравятся их данные в общем доступе. Нашу команду можно тоже отнести к маленькой компании. В Google Spreadsheets мы заносим данные по клиентам. Мы не ведем шпионских игр, но все же не хотелось, чтобы данные по тому сколько клиенты платят нам за работы ушли в общий доступ.
Крупные компании используют Google Spreadsheets для хранения данных. Кто-то сводит там данные по результатам работы нескольких рекламных агентств, другие же хранят информацию по медийным кампаниям, по которым нет возможности выгружать данные через API. Все понимают риски публикации файлов для визуализации, но все-равно делают это.
Видя эту ситуацию, мы создали решение для проброса данных из Google Spreadsheets в Clickhouse и Google BigQuery. Удобство в том, что с файлами вы работате как и раньше, а если понадобится загрузить их для визуализации, то можно сделать это безопасно из базы данных.
Описание решения для проброса данных
- Требуется php версии не ниже 7.1.3.
- Работа скриптов в операционных системах Windows не проверялась.
- Установить как отдельный скрипт.
- Распаковать архив и внутри директории распакованного архива выполнить консольную команду (символ $ вводить не надо):
- Установить как бандл в symfony не ниже 4.2.
- Добавить в composer.json в секцию require.
Добавить в секцию repositories, если нет, то создать ее:
После чего выполнить в консоли:
- После установки в файл config/packages/mello_middlesource.yaml (если нет то его надо создать) записать содержимое файла config/services.yaml. Либо его можно скопировать с нужным именем.
Бандл регистрирует в Dependency Injection три сервиса:
- mello_middlesource.export.clickhouse – подключения к ClickHouse;
- mello_middlesource.export.google_bigquery – подключения к Google BigQuery;
- mello_middlesource.import.google_spreadsheets – подключения к Google Spreadsheets.
Настройка Google Spreadsheets
Для того, чтобы извлечь данные из Google таблиц необходимо получить токен авторизации OAuth. Самый простой способ – это открыть документацию и нажать “Enable the Google Sheets API”. Далее следовать инструкциям и когда ключ будет создан, будет предложено сохранить его в формате json, вот он нам и понадобится.
Сохранить ключ нужно в папку var/credentials/ (на самом деле можно в другое место, необходимо будет поправить путь к файлу в config/services.yaml). Имя для файла лучше задать более читаемое, чтобы не запутаться, например sheets.json или table.json.
Далее необходимо указать имя файла и путь к нему (если необходимо) в config/services.yaml в разделе google_spreadsheets параметре называется key_file_path, например:
Внимание! Файл services.yaml имеет yaml формат поэтому кол-во пробелов перед параметрами имеет важную роль. Следите, чтобы кол-во пробелов было кратно четырем, тогда ошибок быть не должно.
В строке есть переменная %kernel.project_dir%, которая заменится на корневую директорию с проектом, если ваши json-файлы с ключам лежать внутри проекта, то меня ее не нужно.
Примеры кода находятся в директории src/Processor/Spreadsheet.
Ссылка на таблицу для примера (загрузите ее к себе в таблицы): https://drive.google.com/file/d/1htoAlr-IVPClc9wLD7GHBgC3OZUBjdII/view?usp=sharing
Настройка Google BigQuery
По аналогии с таблицами нужно получить ключ доступа к BigQuery, делается похожим образом, но добавляется еще один шаг.
Необходимо включить API нажав на кнопку “Enable the API”. Затем создать сервисный аккаунт в Google Console, нажав на кнопку “Go to the Create Service Account Key page”.
В консоли выбрать аккаунт на который нужно создать ключ и выбрать формат json. После его создания, аналогично таблицам сохранить его в var/credentials (или куда удобно) и указать путь к этому ключу в config/services.yaml в параметр key_file_path раздела google_bigquery.
В файле config/services.yaml нужно будет указать dataset и project_id той базы данных, куда эту информацию можно грузить. Для тестов предлагаю создать другой, тестовый, dataset и при проверках изменений в импорте, заменять на него в файле конфигурации, после чего изменить на рабочий и работать с основным.
Настройка ClickHouse
Необходимо создать базу данных или использовать существующую и прописать параметры для подключения в формате DSN Url в файл .env лежащий в корне проекта
Описание примеров
Для получения данных из таблиц, нам понадобится их идентификатор. Его можно получить из ссылки — это ее часть (выделено):
https://docs.google.com/spreadsheets/d/1cF3nuaOF3hG125HQZbOCIDM4W5jkoeGzhe__zG-4SJI/edit
Этот идентификатор и ряд других параметров, таких как имя листа и диапазон указывается при получении данных (например: src/Processor/Spreadsheet/GoogleBigQuery.php):
Описание методов
- setSheetId() – устанавливает ID таблицы;
- setSheetList() – устанавливает лист, с которого производится чтение;
- setRange() – устанавливает диапазон ячеек в листе;
- setTable() – устанавливает имя таблицы куда будут записаны данные (BigQuery или ClickHouse).
В интерфейсе ProcessorInterface описан метод buildData, который и формирует данные. Ниже описаны отличия формирование данных для BigQuery и ClickHouse.
Google BigQuery
Массив данных должен быть формата:
Рекомендую записывать время записи данных, для отслеживания когда были последние изменения.
Минусы:
- Кеш на insertId — в документации сказано, что он может быть до 90 минут. В это время выполненные несколько раз запросы на вставку с установленным insertId будут идемпотентными, то есть выполнится только первый остальные будут считаться дублями и выполняться не будут, хотя и ошибок тоже не будет. Таким образом, регулярность выгрузки в BigQuery корректно отрабатывает раз в два часа, чаще нет смысла.
- Даже если не установлен insertId потоковая вставка может кэшировать результаты и если вы обновляете данные, они могут не обновиться, так как будут считаться дублями. Решение как и в предыдущем пункте — ждать около двух часов между запусками.
- Общий кеш. Даже если данные актуальные и загрузка прошла успешно, Google BigQuery может отдавать данные из кеша. Таким образом Power BI (в нашем случае) получал устаревшие, кэшированные данные.
- Запросы Insert/Update/Delete дорогие по квотам. Слишком узкие рамки для нас: не более 1000 операций над таблицей в день.
- Медленный.
ClickHouse
Массив данных проще:
- Нет возможности удалять записи, только использование движка CollapsingMergeTree может сократить расходы по хранению и избежания дублей. Мы периодически очищаем таблицы, а также активно используем функции агрегации, чтобы получать актуальные данные и без дублей.
- Нет возможности обновить данные. ClickHouse работает только на вставку.
Плюсы:
- Оооочень быстрый.
ProcessorInterface также требует реализацию метода getDDL(). Он требуется для описания схемы таблицы, чтобы создать ее если таблицы не существует. Примеры можно посмотреть в коде.
Подробности DDL для BigQuery
Строка с описание колонки имеет вид
Параметры разделяются двоеточием, где:
- Col1 — Имя столбца;
- STRING — Тип данных. Подробнее по типам в документации;
- NULLABLE — Говорит, что данные могут быть Null, обязательны к заполнению или быть повторяющимся набором, подробнее в документации.
Данный формат не официальный, а наш упрощенный, который при обработке генерируется в корректный. Соответственно, он не такой гибкий как этого хотелось, но на данный момент его более чем достаточно.
Запуск
В консоли, находясь в директории с проектом, выполнив
Будут выведены две команды для запуска соответствующего скрипта.
Обе консольные команды можно посмотреть в bin/console.php.
Запуск команды для выгрузки в ClickHouse будет выглядеть следующим образом:
А для BigQuery соответственно:
При первом запуске будет запрошена авторизация по ключу для Google Spreadsheets. Необходимо скопировать ссылку из консоли и перейти по ней. Выбрать аккаунт на котором был создан ключ. После авторизации в браузере, отобразится ключ подтверждения авторизации, его копируем в консоль и нажимаем Enter. Скрипты сами создадут файл токена в папке var/token/ (будет создана автоматически) и при регулярном запуске скриптов, дальнейших действий по активации токена не потребуется. По крайней мере до того, когда токен будет отозван или по другим причинам, когда он больше не может быть использован. В таком случае процедуру активации нужно будет пройти еще раз.
Остается дождаться выполнения. Все ошибки будут выводиться в консоль, а более подробный лог работы записывается в var/log/app-<date>.log. У лога ротация идет раз в 7 дней. То есть, каждые семь дней будет создан новый файл, а самый старый будет удаляться.
Пример cron
Запуск каждую ночь в 1:00
Для подбора параметров можно поиграть на https://crontab.guru/
При запуске будут созданы необходимые таблицы и записаны в них данные.Средство получилось универсальное для экспорта и в другие базы данных или сервисы с использованием их API. Достаточно реализовать соответствующие интерфейсы и указать, каким классом вести обработку. Таким образом можно переносить данные из одних систем в другие, которые между собой изначально не могли «общаться» напрямую.
Мы в Mello очень переживаем за корректность и безопасность данных наших клиентов. Для тех, кто ценит безопасность мы решили опубликовать эту статью и решение. Руководствуясь инструкцией вы можете сами развернуть передачу данных из Google Spreadsheets в базы. Ну а если у вас возникли сложности или вы хотите чтобы вам внедрили решение, то обращайтесь в команду Mello, мы поможем.