Как хранить и обрабатывать данные в BigQuery

Кому нужен BigQuery?

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

Здесь-то и пригодится BigQuery - сервис облачного хранения и обработки данных. С помощью BigQuery можно:

• импортировать разрозненные данные в единое хранилище
  
• сматчить (объединить) статистику из разных систем
  
• очистить и обработать данные, построить отчеты, провести анализ
  

Благодаря тому, что BigQuery находится в облаке, маркетологи, аналитики и другие специалисты могут самостоятельно собирать и обрабатывать данные, не сталкиваясь с различными техническими сложностями (н-р, с развертыванием и администрированием баз данных).

Начало работы

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

Пока что имеем пустой аккаунт. Создадим первый проект. Нажимаем на кнопку "Создать".

Вбиваем название проекта и еще раз нажимаем на кнопку "Создать".

После этого попадаем в редактор запросов в рамках созданного проекта.

BQ автоматически создал идентификатор проекта (coral-broker-267309), и теперь он выводится в списке доступных ресурсов. Однако если мы кликнем по нему, то увидим, что он пуст. Поскольку мы не добавили в наш проект данные, бессмысленно писать какие-либо запросы. Поэтому перейдем к следующему шагу.

Импорт данных

Перед добавлением данных необходимо создать набор данных (dataset). Он объединяет в себе несколько таблиц. Создать его очень просто: находясь в проекте, жмем на кнопку "Создать набор данных".

В новом окне задаем название набора данных на латинице и жмем кнопку "Создать набор данных".

Созданный dataset появится при клике на идентификатор проекта.

Итак, мы имеем проект, а внутри него - набор данных. Осталось создать таблицу. Для этого, кликнув по набору данных, внутри которого хотим создать таблицу, нажимаем на кнопку "+" (Создать таблицу).

Импорт данных в BigQuery может происходить несколькими способами:

• указанием на путь к файлу в хранилище Google Cloud Storage
  
• загрузкой файла с вашего компьютера
  
• загрузкой файла с Google Диска
  
• подключением к базе данных Google Cloud BigTable
  

Попробуем загрузить файл с нашего локального диска. Для этого будем использовать файл-выгрузку из рекламного кабинета со статистикой по рекламным кампаниям. Формат файла - CSV.

Выгрузка содержит статистику по показам, кликам, CTR, расходам на рекламу и среднюю цену клика в разбивке по дням, типам кампаний, устройствам, операционным системам и половозрастным характеристикам.

После нажатия на кнопку "Создать таблицу" появится интерфейс задания настроек таблицы. На скриншоте ниже указаны все необходимые настройки для успешного импорта файла со статистикой.

Прежде всего, в поле "Источник" выбираем "Загрузить". Далее указываем путь к файлу со статистикой и выбираем формат этого файла (CSV).

Далее выбираем проект и датасет, в который будет помещена таблица, придумываем ей название.

В блоке "Схема" вручную задаем описание структуры таблицы. То есть нажимаем кнопку "Добавить поле" и для каждого столбца из исходного файла прописываем название, а также выбираем тип содержащихся в этом столбце данных и режим заполнения. Подробнее про типы данных и режимы можно прочесть в справке BigQuery (пока, к сожалению, только на английском языке).

Не забываем про "Дополнительные параметры".

Во-первых, указываем разделитель полей, используемый в нашем файле - точку с запятой. Во-вторых, задаем пропуск первой строки, поскольку в ней содержится не статистика по кампаниям, а заголовки столбцов. После заполнения всех нужных настроек жмем на кнопку "Создать таблицу".

Если создание таблицы прошло успешно, мы увидим ее в соответствующем датасете. Если же при создании таблицы возникли проблемы, можно открыть журнал заданий, а затем в информационном окне под редактором запросов нажать на соответствующее уведомление. Появится подробная информация об ошибке.

На примере выше создание таблицы оказалось неудачным, поскольку автор забыл в настройках таблицы задать исключение первой строки из загружаемых данных. Из-за этого при обработке первой строки BigQuery не мог привести значения (названия столбцов) к заданным типам данных. Кстати, если при создании таблицы возникла ошибка, необязательно задавать ее настройки с нуля. Можно просто нажать на кнопку "Повторить задание загрузки" в уведомлении. Откроется интерфейс создания таблицы с ранее введенными данными.

При клике на название таблицы в информационном окне справа можно посмотреть детальные сведения о ней:

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

Хотя BigQuery и дает нам возможность посмотреть на созданную таблицу в привычном виде, все операции с данными нам необходимо производить не с помощью графического интерфейса (как, например, в Excel), а с помощью языка запросов SQL.

Язык запросов SQL

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

В рамках этой статьи мы не сможем рассмотреть всё обилие возможностей SQL. Для этого можно изучить справку. Мы же попробуем разобрать пару несложных запросов, чтобы показать, как легко использовать SQL для получения нужных нам данных.

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

select * from `coral-broker-267309.test_dataset.campaigns`

И нажмем на кнопку "Выполнить". В информационном окне увидим результаты запроса.

Обратите внимание, что при вводе запроса BigQuery сразу пишет, какой объем данных будет обработан. Это важно, поскольку тарификация зависит от объема обрабатываемых данных. Рядом с этим уведомлением появляется зеленая галочка, если запрос корректен, и красный восклицательный знак, если в запросе содержатся ошибки.

Давайте подробнее рассмотрим наш запрос. Он состоит из следующих элементов:

SELECT - элемент, с помощью которого мы указываем, данные из каких полей необходимо извлечь. В нашем случае после SELECT идет *, которая подразумевает любые доступные значения. Таким образом, выражение SELECT * говорит о том, что необходимо извлечь данные из всех полей таблицы.

from `coral-broker-267309.test_dataset.campaigns` - эта часть выражения указывает на путь к таблице, из которой необходимо извлечь данные. При этом надо указать не просто название таблицы, а весь путь к ней, через точку: `идентификатор_проекта.название_датасета.название_таблицы`, не забыв заключить этот путь в одинарные кавычки.

Таким образом, наш SQL-запрос можно прочесть так: взять все поля из таблицы campaigns, которая расположена в датасете test_dataset из проекта coral-broker-267309.

Если мы не хотим расходовать трафик на получение всей таблицы, можно ограничить результаты запроса несколькими строками:

select * from `coral-broker-267309.test_dataset.campaigns` limit 5

С помощью элемента limit на примере выше мы ограничили отображаемые результаты первыми 5 строками.

Пожалуй, получать данные в том виде, в котором они содержались в изначальном файле, не очень интересно. Поэтому давайте попробуем что-то чуть более сложное. Например, вывести суммарное число кликов, показов и расходов с группировкой по возрасту и полу, отсортировав результат по убыванию расходов.

Как видим, запрос оказался не намного сложнее, зато теперь мы знаем, что наша реклама приводит на сайт преимущественно мужчин в возрасте 25-34 лет.

Конечно, мы могли бы обработать выгрузку в Excel или аналогичной программе, но когда дело касается больших данных, есть несколько важных ограничений офисных программ:

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

Поэтому при работе с большими таблицами, сведении и обработке данных из разных источников, BigQuery гораздо эффективнее обычных табличных редакторов.

Сохранение и экспорт данных

Результаты запроса можно сохранить в таблицу, нажав на кнопку "Сохранить представление".

Благодаря этому не надо будет каждый раз заново вводить запрос - данные в нужном виде всегда будут под рукой.

Также можно сохранить и сам SQL-запрос, нажав на кнопку "Сохранить запрос".

Если вы забыли сохранить какой-либо запрос - не печальтесь. Скорее всего, вы найдете его в разделе "История запросов".

Помимо работы с данными в интерфейсе BigQuery, можно экспортировать результаты в файлы различных форматов - CSV или JSON (скачиваемых локально или загружаемых на Google Диск), Google Таблиц либо копировать в буфер обмена.

BigQuery интегрирован с BI-системой Google Data Studio, благодаря чему можно быстро приступить к визуализации данных. Для этого нажимаем "Просмотреть данные" >> Открыть в Студии данных.

Стоимость

Начать знакомство с BigQuery можно с бесплатной версии, в режиме т.н. песочницы. В песочнице можно ежемесячно обрабатывать данные объемом до 1 терабайта и хранить до 10 GB информации. Но есть одно существенное ограничение - датасеты и входящие в них таблицы удаляются из песочницы через 60 дней.

Поэтому песочница отлично подойдет для изучения BQ, но для серьезной работы подключайте платный тариф. Тарифы зависят от:

• объема активно хранимых данных (тех, которые подвергались обработке за последние 90 дней)
  
• объема долгосрочно хранимых данных (не подвергавшихся обработке за последние 90 дней)
  
• объема данных, обработанных запросами
  
• использования некоторых других возможностей сервиса
  

Плата за обработанный объем данных может быть как в виде фиксированного пакета (заранее выкупаем определенный объем), так и в виде тарифа - $5 за TB.

Стоимость активно хранимых данных - $0.02 (2 цента) за GB в мес. Хранение долгосрочных данных в 2 раза дешевле - $0.010 за GB в мес. Тарифы приведены для регионов US и EU. Если при создании наборов данных вы выбрали другие регионы хранения, плата может незначительно отличаться.

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

Как видим, стоимость использования BigQuery вполне посильна не только крупным корпорациям, но даже небольшим стартапам и фрилансерам. Кстати, даже после того как вы попробуете инструмент в песочнице, привяжете карту и перейдете на платный тариф, BigQuery все равно подарит вам бесплатную пробную подписку с лимитом расходов в $300, которые можно использовать в течение 12 месяцев.

Автоматизация работы с BigQuery

В примере выше мы вручную скачивали статистику из рекламного кабинета, а затем импортировали в BigQuery. Автор статьи скромно умолчит о том, сколько времени было потрачено на то, чтобы привести файл выгрузки в удобоваримый для BQ вид.

Очевидно, что при регулярной аналитике данных заниматься ручным скачиванием и импортом весьма трудозатратно. Поэтому мы разработали инструмент для перекладки данных Garpun Feeds.

Работает он очень просто:

1. Регистрируемся на account.garpun.com;
   
2. Подключаем доступы к источникам и приемникам данных (например, к рекламным кабинетам и BigQuery);
   
3. Настраиваем поток по перекладке данных.
   
4. Наслаждаемся тем, что все нужные данные автоматически собираются в облачном хранилище по заданному расписанию.

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