Сколько диалектов sql имеет bigquery от google

Строим аналитическое хранилище данных с готовыми модулями ML на Google BigQuery

Всем привет! Меня зовут Сергей Коньков — я архитектор данных в компании BR Systems. Когда есть возможность, я посещаю различные конференции по анализу данных, машинному обучению и разработке. Однажды мне пришла в голову мысль, что часто на сцене этих конференций мы видим представителей одних и тех же компаний из ТОП 5 крупнейших банков или ритейлеров страны. Они рассказывают очень интересные кейсы, как кластеры из десятков серверов обрабатывают терабайты данных, как тысячи сотрудников корпорации используют результаты этого анализа. А в зале сидят и слушают их айтишники из среднего бизнеса, для которых насущные проблемы, это выбить бюджет на дополнительны диски и память для единственного SQL сервера.

Тут же на этих конференциях нам предлагают записаться на курсы дата инженеров и аналитиков. Средний бизнес отправляют туда сотрудника и там преподаватель (иногда тот же сотрудник суперкорпорации) объясняет, что для того что бы работать с данными нужно на хорошем уровне знать Linux, Hadoop, Spark, Kafka, Airflow. Это так называемый классический стек технологий для работы с bigdata.

Как быть если у вас в ИТ отделе всего 5 человек и один из них отвечает за данные в нагрузку к прочей работе? Ему нужно достать данные из 1С, CRM, Google Analytics, соединить это все вместе и быстро построить отчетность, а завтра уже будет новые источники из новой производственной системы. И все это каждый день меняется. Он знает SQL, иногда Python и где в 1С найти данные. И у него нет времени на изучение Spark. Да даже у тех у кого в ИТ службе 50 человек проблематично иметь в штате инженера данных который знает все эти технологии.

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

Разбираемся в задачах

Итак мы компания среднего бизнеса. Торгуем оптом строительными материалами. Что у нас есть:

• ERP система в компании (например: 1С или Axapta)
• CRM система (Amo CRM или Bitrix 24)
• Облачная система для учета товаров которая используется в одном из филиалов (Мой склад)
• Интернет магазин написанный на PHP с подключенным Google Analytics

Что нам нужно: строить консолидированную отчетность на основании данных их всех этих источников.

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

Что хотим получить

Хотим загружать данные из разных источников в одну базу (хранилище данных) и строить на основании него отчеты. И все это желательно автоматически.

Многие компании используют технологию OLAP, как правило от Microsoft. Продукт SQL Server Analysis Services включен во все версии MS SQL Server, начиная со Standard и является отличной технологией для решения данной задачи. Однако если вы разворачиваете OLAP в компании вам нужен специалист который в нем хорошо разбирается, а это не так просто. И зачастую этот специалист становиться бутылочным горлышком в процессе создания отчетности.

Мы рассмотрим альтернативу OLAP и вышеупомянутому классическому стеку для работы с bigdata — Google BigQuery и посмотрим какие плюсы можно извлечь из этого.

Google BigQuery (BQ)

Что это?

Это база данных, она находится в облаке Google, точнее в Google Cloud Platform. Она поддерживает язык SQL для работы с данными. Эту технологию Google сделал специально для анализа данных. Архитектура и движок этой базы отличается от привычных MS SQL и MySQL. Колоночное хранение данных и ряд других особенностей делают все расчеты очень быстрыми. Например можно посчитать сумму продаж из розничной сети используя таблицу из 10 миллионов чеков за пару секунд.

Как все это работает?

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

Сколько это стоит?

Не дорого. Цены здесь: https://cloud.google.com/bigquery/pricing. Вы платите за хранение данных ($0.020 за GB, 10 GB бесплатно) и за обработку данных при анализе (5$ за TB, 1 TB в месяц бесплатно). Пример: вы загрузили в BQ данные объемом 10 GB и ваши аналитики делают SQL запросы к ним. Допустим за один запрос они обрабатывают в среднем 1 GB данных (это объем данных в таблиц который нужно обработать что бы получить результат. Ваши аналитики за месяц сделали 1000 запросов (в сумме 1 TB данных). В итоге использование BQ в этом месяце будет для вам бесплатным — вы уложились в лимиты на бесплатное использование.

Допустим вы загрузили 100 GB данных, тогда их хранение будет стоить $1.8 в месяц. Аналитики пусть сделали 1000 запросов по 10 GB, обработка будет вам стоить $45 в месяц.

Тестируем BigQuery шаг за шагом

Регистрация

Для начала нам нужна учетная запись в Google. Если у вас есть почта на gmail, то она у вас есть. Если нет, заведите: https://accounts.google.com.

Далее подключаем BigQuery. Есть два способа:

• Обычный платежный аккаунт Google Cloud Platform
• Песочница — для тех кто не хочет пока оставлять в Google Cloud данные своей банковской карты и хочет бесплатно протестировать систему

Платежный аккаунт подключаем по этой ссылке https://console.cloud.google.com/billing. Нужна будет банковская карта. Если вы впервые используете Google Cloud Platform вам будет предоставлен бесплатный пробный период 90 дней на использование ряда сервисов, в том числе и BigQuery в размере $300.

Если карту оставлять пока не хотите создаем песочницу. Переходим по ссылке https://console.cloud.google.com/bigquery, выбираем страну и подтверждаем условия. Все, среда создана, можно начинать работу.

Создаем проект, датасет

Данные в BigQuery хранятся в таблицах, таблицы находятся в датасетах, датасеты в проектах. На уровне проектов можно разграничивать доступ к данным. Например если у вас несколько независимых бизнесов, то для каждого можно сделать в проект. А внутри проекта сделать несколько датасетов по направлениям анализа, например датасет Производство и датасет Розница. На каждом уровне можно управлять правами доступа к объектам.

Создадим проект habrtest:

Нажмите три точки справа от названия проект и выберете опцию Create dataset, создайте датасет habrdata:

Загрузка данных в таблицу

• Скачайте архив с тестовыми данными: https://www.ssa.gov/OACT/babynames/names.zip
• Разархивируйте его у себя на пк
• Нажмите три точки справа от названия датасета и выберите опцию Open
• В открывшемся окне нажмите копку Create table

• В настройках Source для Create table from, выберете Upload.
• Для Select file, нажмите Browse, и выберете файл yob2014.txt из ранее разархивированной папке.
• Для File format, выберете CSV.
• В настройках Destination, для Table name, введите names_2014.
• В настройках Schema, выберете Edit as text и введите этот код:

• Нажмите Create table
• Подождите пока BigQuery загрузит данные
• В панели навигации нажмите созданную таблицу и в открывшемся окне выберете вкладку Preview что бы посмотреть загруженные нами данные:

Запросы к данным

• Нажмите Compose new query и в открывшемся редакторе введите этот код:

SELECT name, count FROM `habrdata.names_2014` WHERE gender = 'M' ORDER BY count DESC LIMIT 5

• нажмите Run, отобразятся результаты запроса

Строим отчет

• Сверху от результатов запроса нажмите Explore data
• Дайте приложению Google Data Studio доступ к данным
• В открывшемся дизайнере отчетов выберите тип диаграммы Кольцевая и в показатели добавьте поле count. Затем нажмите кнопку Сохранить

Выводы

Мы рассмотрели как за 10 минут зарегистрироваться в BigQuery, загрузить туда данные и построить отчет. Если вас заинтересовала эта технология — продолжайте читать. В следующей главе мы рассмотрим практические шаги по развертыванию BigQuery в компании.

Разворачиваем BigQuery в компании

Переходим на платный аккаунт

Ранее в примерах мы использовали режим песочницы. Часть функций в этом режиме не доступна. Например мы не можем выполнить sql инструкции Insert или Delete. Рекомендуем подключить платежный аккаунт. Напомню: вам будет предоставлен кредит на $300 и 90 дней для полноценного тестирования облачной платформы Google. Для начала перейдите по этой ссылке и следуйте инструкциям https://console.cloud.google.com/freetrial/signup.

Читаем литературу и документацию

• Основной источник информации, вот эта книжка: Google BigQuery. Всё о хранилищах данных, аналитике и машинном обучении
• А так же официальная документация.
• Здесь рассмотрены сценарии миграции существующего хранилища данных в BigQuery.

Проектируем таблицы в хранилище

Информацию об этом вы найдете в книге. Если вы ранее создавали витрины данных для аналитиков, вы можете следовать тем же принципам. Для тех у кого мало опыта в этом читайте книжку. Построение таблиц в хранилище данных отличается от обычных схем-снежинок, так например иногда данные могут храниться в ненормализованном виде. Например информация о заказе покупателя (дата, имя клиента, адрес клиента) и информация о строчках заказов (товар, количество, сумма): в 90% случаях эти данные используются в одном запросе и есть смысл хранить их в одной таблице, повторяя данные о клиенте в каждой строчкой с товаром. Таким образом можно увеличить быстродействие запросов к данным.

Загружаем данные

Так выглядят основные пути загрузки данных по рекомендациям Google:

Дадим краткие пояснения по ним:

• Можно загружать CSV и JSON файл с помощью консоли, как мы делали выше
• Есть множество готовых интеграций c BigQuery для различных сервисов, например Google Analytics, и их становится все больше. Например мы в нашей компании разработали за последний год интеграции с BigQuery для нескольких российских облачных сервисов
• Есть мощный ETL инструмент от Google — Cloud Data Fusion. Но он не дешевый.
• Есть клиентская библиотека Google Cloud Client Library для BigQuery. Сейчас она доступна для семи языков: Go, Java, Node.js, Python, Ruby, PHP и C++. Вот пример загрузки данных с помощью Python из MS SQL Server в BigQuery:

from sqlalchemy import create_engine from pandas import DataFrame from google.cloud import bigquery import pandas import pytz # подключаемся к MS SQL engine = create_engine("mssql+pyodbc://(localdb)\MSSQLLocalDB/habrtest?driver= \ ODBC Driver 17 for SQL Server", fast_executemany=True) connection = engine.connect() # подключаемся к BigQuery с помощью сервисного аккаунта client = bigquery.Client.from_service_account_json('habrtest.json') # определяем таблицу в BigQuery для загрузки данных table_id = "habrtest.habrdata.tb2" # запрос к MS SQL для выборки данных resoverall = connection.execute('select * from Test') df = DataFrame(resoverall.fetchall()) df.columns = resoverall.keys() # отправляем данные в BigQuery job = client.load_table_from_dataframe(df, table_id) job.result()

Данный код будет работать только для платного аккаунта BigQuery (в песочнице не будет).

В целом по загрузке данных такие рекомендации: используйте где можно готовые интеграции, в остальных случаях стройте свои интеграционные процессы используя Google Cloud Client Library. Разработчики которые поддерживают информационные системы в вашей компании могут загружать данные с помощью Cloud Client Library в указанные вами таблицы с необходимой периодичностью.

Настраиваем безопасность

В Google Clooud Platform механизм управления идентификацией и доступом (Identity and Access Management, IAM). Он позволяет разграничить доступ к данным: https://console.cloud.google.com/iam-admin. Вы можете дать доступ к данным для учетной записи @gmail.com или @example.com, где example.com — это домен G Suite. Например таким образом вы можете дать вашим аналитикам доступ для анализа данных.

Так же вы можете создавать сервисные аккаунты: https://console.cloud.google.com/iam-admin/serviceaccounts для доступа приложений. Например вы можете создать тестовый аккаунт для ваших разработчиков 1С, передать им ключ доступа (JSON файл), а они используя этот ключ смогут загружать данные в BigQuery.

Уровень доступа определяется ролями. Ниже представлены эти роли, в порядке увеличения прав:

1. metadataViewer (полное имя roles/bigquery.metadataViewer) предоставляет доступ только к метаданным наборов данных, таблиц и представлений.
2. dataViewer предоставляет право читать данные и метаданные.
3. dataEditor предоставляет право читать наборы данных, а также перечислять, создавать, изменять, читать и удалять таблицы в наборе данных.
4. dataOwner добавляет возможность удалить набор данных.
5. readSessionUser предоставляет доступ к BigQuery Storage API, оплачиваемый за счет проекта.
6. jobUser позволяет запускать задания (и выполнять запросы), оплачиваемые за счет проекта.
7. user позволяет запускать задания и создавать наборы данных, хранение которых оплачивается за счет проекта.
8. admin позволяет управлять всеми данными в проекте и отменять задания, запущенные другими пользователями.

Работаем с данными

Данные загрузили, права раздали, значит можно извлекать из данных пользу. Вот основные возможности:

• Делаем SQL запросы в консоли BigQuery. Документация по синтаксису запросов — https://cloud.google.com/bigquery/docs/reference/standard-sql а так же в книжке.
• Работаем с BiqQuery в ноутбуках на Python. Пример кода для выборки данных из ранее созданной нами таблицы

from google.colab import auth auth.authenticate_user() print('Authenticated') %load_ext google.colab.data_table %%bigquery --project yourprojectid SELECT * FROM `habrtest.habrdata.names_2014` LIMIT 1000

• Используем BI инструменты: Google DataStudio, MS Power BI, Tableau. У этих трех, и у многих других есть встроенные коннекторы к BigQuery

Источники данных Power BI

Ускоряем запросы с помощью BI Engine

Если есть таблицы, к которым вы часто обращаетесь из инструментов бизнес-аналитики, такие как информационные панели с агрегатами и фильтрами, для ускорения запросов можно воспользоваться движком BI Engine. Он автоматически сохраняет соответствующие фрагменты данных в памяти и использует специализированный процессор запросов. С помощью консоли администратора BigQuery Admin Console можно зарезервировать объем памяти (до 10 Гбайт), который BigQuery должна использовать для своего кеша: https://console.cloud.google.com/bigquery/admin/bi-engine. BI Engine платный: 1GB памяти будет стоить примерно $30 в месяц. Для пользователей Google DataStudio этот объем будет бесплатным. Здесь видео о том что такое BI Engine: https://youtu.be/kifUXfwqzVo

Подключаем модули машинного обучения

Модули BigQuery ML позволяют пользователям создавать и выполнять модели машинного обучения в BigQuery, используя стандартные запросы SQL.

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

CREATE OR REPLACE MODEL ch09eu.movie_recommender_16 options(model_type='matrix_factorization', user_col='userId', item_col='movieId', rating_col='rating', l2_reg=0.2, num_factors=16) AS SELECT userId, movieId, rating FROM ch09eu.movielens_ratings

Получаем данные из модели, найдем лучшие комедийные фильмы, которые можно рекомендовать пользователю с идентификатором (userId) 903:

SELECT * FROM ML.PREDICT(MODEL ch09eu.movie_recommender_16, ( SELECT movieId, title, 903 AS userId FROM ch09eu.movielens_movies, UNNEST(genres) g WHERE g = 'Comedy' )) ORDER BY predicted_rating DESC LIMIT 5

Вот список типов задач машинного обучения, которые можно решать с помощью BigQuery ML:

• Кластеризация
• Регрессия
• Рекомендации
• Таргетинг клиентов (Выбор целевой аудитории для предложения продукта)
• Бинарная классификация
• Многоклассовая классификация
• Классификация изображений, классификация текста, анализ эмоциональной окраски, извлечение сущностей
• Ответы на вопросы, аннотирование текста, генерирование подписей к изображениям

Так же вы можете использовать в BigQuery ML TensorFlow.

За использование BigQuery ML взымается отдельная плата. Информация по ценам здесь.

Заключение

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

Осваиваем SQL на примере данных интернет-магазина Google

На заре своей веб-аналитической молодости, работая над составлением отчетов, я использовал только Excel.

То есть, например, бизнес говорит:

«Хочу увидеть отчет с воронкой продаж, начиная от посещения сайта и заканчивая получением товара в офисе».

Это сейчас я знаю Data Studio, практикую Power BI, SQL и еще много чего. А раньше, что я делал в таком случае? Открывал Google Analytics, создавал кастомный отчет с необходимыми параметрами и показателями и выгружал его в Excel. Далее шел к аналитику (не веб, а обычному, в банках такие есть) и просил выгрузить из базы все в тот же Excel, данные по клиентам посетившим офис, а после сводил две таблицы в единый отчет.

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

А как надо, спросите вы? «Изучайте SQL» — отвечу я!

Google BigQuery

Осваивать SQL мы будем на примере реальных данных электронной торговли магазина Google Merchandise Store, который продает товары под торговой маркой Google. Публичный датасет совсем недавно был выложен в BigQuery — облачную базу данных, которая позволяет обрабатывать терабайты данных за считанные секунды.

Чтобы получить доступ к набору данных:

1. Перейдите на страницу http://bigquery.cloud.google.com.
2. Если вы новичок в BigQuery или у вас еще нет проекта, вам нужно будет создать проект.
3. Включить биллинг для проекта (нужно будет создать платежный аккаунт и привязать его к проекту, а также указать данные кредитной карты). Но не беспокойтесь, во-первых, Google предоставляет достаточно большой бесплатный пробный период, во-вторых, деньги без вашего ведома он снимать не будет.
4. После того как проект создан, можно переходить непосредственно к датасету Google Merchandise Store.

Набор данных содержит информацию о трафике, взаимодействии с контентом и транзакциях за период с 1 августа 2016 года по 1 августа 2017 года. Для каждого дня в наборе данных создается по одной таблице с названием в формате «ga_sessions_ГГГГММДД», а каждая строка таблицы содержит данные об одном сеансе (схема данных в помощь).

Пишем SQL-запросы

SQL или structured query language — это язык структурированных запросов применяемый для создания, модификации и управления данными в реляционной базе данных.

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

Оператор SELECT состоит из нескольких предложений:

1. SELECT — определяет список возвращаемых столбцов (как существующих, так и вычисляемых).
2. FROM — указывает откуда (из какой таблицы и какого датасета) брать данные.

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

Давайте потренируемся и попробуем получить общее количество просмотров страниц за 2017-01-01. Смотрим в схему данных, находим поле totals.pageviews и пишем запрос:

SELECT totals.pageviews FROM [bigquery-public-data:google_analytics_sample.ga_sessions_20170101]

И получаем вот такой результат:

Но что это? Вы ведь ожидали увидеть одну цифру с общим количеством просмотров страниц, а не 1528 строк. А все дело в том, что любая БД работает как КЭП (капитан очевидность) — что попросили, то и получили ��

В запросе вы сказали: «Выведи мне поле всего просмотров страниц из таблицы за 2017-01-01». На что в ответ и получили все строки таблицы, содержащие данные о количестве просмотров страниц.

А нужно было сформулировать запрос так: «Выведи мне СУММУ поля всего просмотров страниц из таблицы за 2017-01-01».

SELECT SUM(totals.pageviews) AS TotalPageviews FROM [bigquery-public-data:google_analytics_sample.ga_sessions_20170101]

Также в запросе, помимо агрегируещей функции SUM , вы могли заметить параметр AS , который отвечает за пользовательское название столбца (если его не указать, то у столбца не будет имени, точнее будет примерно такое «f0_»).

А что делать, если мы хотим вывести сумму просмотров страниц не за один день, а допустим за три дня? На помощь нам приходят операторы фильтрации, группировки и сортировки.

Параметры оператора SELECT

1. WHERE — фильтрует данные по заданным вами условиям.
2. GROUP BY — группирует строки по результатам агрегатных функций ( MAX , SUM , AVG , …).
3. ORDER BY — сортирует значения по одному или более столбцам. Сортировка может производиться как по возрастанию, так и по убыванию значений. Параметр ASC (по умолчанию) устанавливает порядок сортировки по возрастанию, DESC по убыванию.

Дополняем наш запрос:

SELECT date , SUM(totals.pageviews) AS TotalPageviews FROM [bigquery-public-data:google_analytics_sample.ga_sessions_20170101],[bigquery-public-data:google_analytics_sample.ga_sessions_20170102],[bigquery-public-data:google_analytics_sample.ga_sessions_20170103] GROUP BY date ORDER BY date ASC

И получаем просмотры страниц по дням, отсортированные по возрастанию:

Лирическое отступление

BigQuery поддерживает два SQL-диалекта — стандартный (standard SQL) и устаревший (legacy SQL). Оба работают, но между ними есть некоторые отличия. Поясню на примере выбора диапазона дат.

Legacy SQL

Как вы могли заметить, чтобы получить данные за период с 2017-01-01 по 2017-01-03, мне пришлось перечислить в предложении FROM три таблицы. Неужели, если потребуется отчет за неделю или месяц, придется перечислять все таблицы с датами? Совсем нет, для таких случаев в BigQuery существует подстановочная функция TABLE_DATE_RANGE , которая запрашивает несколько ежедневных таблиц, которые охватывают диапазон дат.

Функция TABLE_DATE_RANGE имеет следующий синтаксис:

TABLE_DATE_RANGE(prefix, timestamp1, timestamp1)

prefix — префикс (имя) таблиц без даты

timestamp1 — начальная дата

timestamp1 — конечная дата.

То есть имена таблиц должны иметь следующий формат: где находится формате YYYYMMDD .

Давайте попробуем получить отчет за неделю:

SELECT date , SUM(totals.pageviews) AS TotalPageviews FROM (TABLE_DATE_RANGE([bigquery-public-data:google_analytics_sample.ga_sessions_], TIMESTAMP('20170101'), TIMESTAMP('20170107'))) GROUP BY date ORDER BY date ASC

Просмотры страниц за неделю:

Также, помимо жестко установленных дат, вы можете использовать в TABLE_DATE_RANGE функции даты и времени для генерации параметров метки времени.

Например, следующее предложение вернет вам дату которая была неделю назад от текущей даты: DATE_ADD(CURRENT_TIMESTAMP(), -7, ‘DAY’) .

Standard SQL

Давайте включим стандартный диалект. Для этого нужно нажать на кнопку «Show Options» и убрать галочку «Use Legacy SQL».

И попробуем выбрать тот же диапазон дат.

SELECT date , SUM(totals.pageviews) AS TotalPageviews FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20170101' AND '20170107' GROUP BY date ORDER BY date ASC

В данном случае, вместо функции TABLE_DATE_RANGE в FROM используется шаблон названия таблицы, а в WHERE фильтр _TABLE_SUFFIX , который выбирает данные находящиеся в заданном диапазоне дат ( BETWEEN ).

Плюс обратите внимание на немного изменившуюся пунктуацию в предложении FROM .

Вместо квадратных скобок [ ] , используйте ` ` . А для того, чтобы отделить название проекта от названия таблицы, используйте точку . , вместо двоеточия : .

Результат тот же:

Подробнее о различиях между диалектами читайте в справке Google (на вражеском языке).

Теперь давайте применим все полученные навыки и сконструируем более сложный отчет.

Собираем отчет

Давайте посчитаем количество просмотров страниц, транзакций, конверсию из просмотра в транзакцию, средний чек и доход за неделю:

SELECT date , SUM(totals.pageviews) AS Pageviews , ROUND((SUM(totals.transactions)/SUM(totals.pageviews))*100,2) AS CR , SUM(totals.transactions) AS Transactions , ROUND(AVG(totals.totalTransactionRevenue)/1000000,2) AS AverageCheck , ROUND(SUM(totals.totalTransactionRevenue)/1000000,2) AS Revenue FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20170101' AND '20170107' GROUP BY date ORDER BY date ASC

ROUND — округляет число до стольких знаков после запятой, сколько указано во втором аргументе функции (в нашем случае до 2).

AVG — выводит среднее значение диапазона чисел.

Уже похоже на отчет:

Что дальше с этим делать? Пробуйте построить более сложные отчеты, скачивайте в CSV или экспортируйте в Google Sheets и Data Studio для анализа.

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

Полезные ссылки:

• Осваиваем SQL на примере данных интернет-магазина Google. Ч.2
• Справочник по BigQuery
• Устаревшие (но еще используемые) функции и операторы SQL
• Стандартные функции и операторы SQL
• 0x0b приемов работы с BigQuery на Standard SQL

Работа с Google BigQuery. Считаем деньги

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

Знакомство с BigQuery

BigQuery — это бессерверное, масштабируемое облачное хранилище данных с мощной инфраструктурой от Google, которое имеет на борту RESTful веб-сервис. Имеет тесное взаимодействие с другими сервисами от Google. Создатели обещают молниеносное выполнение запросов с максимальной задержкой в RESTful до 1 секунды. BigQuery поддерживает диалект Standard SQL. Имеется возможность контроля доступа к данным и разграничение прав пользователей. Также есть возможность задавать квоты и лимиты для операций с БД. Доступ к BigQuery возможен через Google Cloud Console, с помощью внутренней консоли BigQuery, а также через вызовы BigQuery REST API как напрямую, так и через различные клиентские библиотеки java, python, .net и многие другие.

Есть возможность подключения через ODBC/JDBC-драйвер с помощью Magnitude Simba ODBC. В состав BigQuery входит довольно мощный визуальный SQL-редактор, в котором можно увидеть историю выполнения запросов, проанализировать потребляемый объём данных, не выполняя запрос, что позволяет существенно сэкономить финансы.

Ценообразование

BigQuery предлагает несколько вариантов ценообразования в соответствии с техническими потребностями. Все расходы, связанные с выполнением заданий BigQuery в проекте, оплачиваются через привязанный платёжный аккаунт.
Затраты формируются из двух составляющих:

• хранение данных
• обработка данных во время выполнения запросов.

Стоимость хранения зависит от объёма данных, хранящихся в BigQuery.

• Active. Ежемесячная плата за данные, хранящиеся в таблицах или разделах, которые были изменены за последние 90 дней. Плата за активное хранение данных составляет 0,020 $ за 1 ГБ, первые 10 ГБ — бесплатно каждый месяц. Стоимость хранилища рассчитывается пропорционально за МБ в секунду.
• Long-term. Плата за данные, хранящиеся в таблицах или разделах, которые не были изменены в течение последних 90 дней. Если таблица не редактируется в течение 90 дней подряд, стоимость хранения этой таблицы автоматически снижается примерно на 50%.

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

• On-demand. Цена зависит от объёма данных, обрабатываемых каждым запросом. Стоимость каждого терабайта обработанных данных составляет 5,00 $. Первый обработанный 1 ТБ в месяц бесплатно, минимум 10 МБ обрабатываемых данных на таблицу, на которую ссылается запрос, и минимум 10 МБ обрабатываемых данных на запрос. Важный момент: оплата происходит за обработанные данные, а не за данные, полученные после выполнения запроса.
• Flat-rate. Фиксированная цена. В данной модели выделяется фиксированная мощность на выполнение запросов. Запросы используют эту мощность, и вам не выставляется счёт за обработанные байты. Мощность измеряется в слотах. Минимальное количество слотов — 100. Стоимость за 100 слотов — 2000 $ в месяц.

Стоит заметить, что хранение данных часто обходится значительно дешевле, чем обработка данных в запросах.
Бесплатные операции:

• Загрузка данных. Не нужно платить за загрузку данных из облачного хранилища или из локальных файлов в BigQuery.
• Копирование данных. Не нужно платить за копирование данных из одной таблицы BigQuery в другую.
• Экспорт данных. Не нужно платить за экспорт данных из других сервисов, например из Google Analytics (GA).
• Удаление наборов данных (датасетов), таблиц, представлений, партиций и функций.
• Операции с метаданными таблиц. Не нужно платить за редактирование метаданных.
• Чтение данных из метатаблиц __PARTITIONS_SUMMARY__ и __TABLES_SUMMARY__.
• Все операции UDF. Не нужно платить за операции создания, замены или вызова функций.

Wildcard-синтаксис

Wildcard-синтаксис позволяет выполнять запросы к нескольким таблицам, используя краткие операторы SQL. Wildcard-синтаксис доступен только в Standard SQL. Таблица с подстановочными знаками (wildcard) представляет собой объединение всех таблиц, соответствующих выражению с подстановочными знаками. Например, следующее предложение FROM использует выражение с подстановочными знаками table* для сопоставления всех таблиц в наборе данных test_dataset, которые начинаются со строки table:

FROM `bq.test_dataset.table*`

Запросы к таблице имеют следующие ограничения:

• Не поддерживаются представления. Если таблица подстановочных знаков соответствует любому представлению в наборе данных, запрос возвращает ошибку. Это верно независимо от того, содержит ли ваш запрос WHERE в псевдостолбце _TABLE_SUFFIX для фильтрации представления.
• В настоящее время кешированные результаты не поддерживаются для запросов к нескольким таблицам через wildcard-синтаксис, даже если установлен флажок «Использовать кешированные результаты». Если вы запускаете один и тот же запрос с подстановочными знаками несколько раз, вам будет выставлен счёт за каждый запрос.
• Запросы, содержащие операторы DML, не могут использовать wildcard-синтаксис в таблицах в качестве цели запроса. Например, wildcard-синтаксис для таблиц может использоваться в предложении FROM запроса UPDATE, но не может использоваться в качестве цели операции UPDATE.

Wildcard-синтаксис для таблиц полезен, когда набор данных содержит несколько таблиц с одинаковыми именами, которые имеют совместимые схемы. Обычно такие наборы данных содержат таблицы, каждая из которых представляет данные за один день, месяц или год. Такой синтаксис полезен для сегментированных таблиц (sharded tables) — не путать с партиционированными таблицами.

Примеры использования:
Допустим, в BQ существуют набор данных test_dataset c таблицами, которые сегментированы по датам: test_table_20200101 test_table_20200102 ……. test_table_20201231
Выборка всех записей за дату 2020-01-01:

select * from test_dataset.test_table_20200101

Выборка всех записей за месяц:

select * from test_dataset.test_table_202001*

Выборка всех записей за год:

select * from test_dataset.test_table_2020*

Выборка всех записей за весь период:

select * from test_dataset.test_table_*

Выборка всех записей из всего набора данных:

select * from test_dataset.*

Для ограничения запроса таким образом, чтобы он просматривал произвольный набор таблиц, можно использовать псевдостолбец _TABLE_SUFFIX в предложении WHERE. Псевдостолбец _TABLE_SUFFIX содержит значения, соответствующие подстановочному знаку *. Например, чтобы получить все данные за 1 и 5 января, можно выполнить следующий запрос:

select * from test_dataset.test_table_202001* where _TABLE_SUFFIX="01" or _TABLE_SUFFIX="05"

Партиционирование и шардирование таблиц

Партиционная таблица в BQ (partitioned table) — таблица, которая разделяется на сегменты (секции) по определённому признаку. Таблицы BigQuery можно разбивать на разделы по следующим признакам:

• Ingestion Time. Таблицы разбиваются на разделы в зависимости от времени загрузки или времени поступления данных, которые содержат дополнительные зарезервированные поля _PARTITIONTIME, _PARTITIONDATE, хранящие дату создания записи.
• Date/timestamp/datetime. Таблицы разбиты на разделы на основе столбца с типом timestamp, date или datetime. Если таблица партиционирована по столбцу с типом DATE, вы можете создавать партиции с ежедневной, ежемесячной или ежегодной гранулярностью. Каждый раздел содержит диапазон значений, где начало диапазона — это начало дня, месяца или года, а интервал диапазона составляет один день, месяц или год в зависимости от степени детализации разделения. Если таблица партиционирована по столбцам с типом TIMESTAMP или DATETIME, вы можете создавать разделы с любым типом гранулярности в единицах времени, включая HOUR.
• Integer range. Таблицы разделены по целочисленному столбцу. BigQuery позволяет разбивать таблицы на разделы на основе определённого столбца INTEGER с указанием значений начала, конца и интервала. Запросы могут указывать фильтры предикатов на основе столбца секционирования, чтобы уменьшить объём сканируемых данных.

Шард-таблица в BQ (sharded table) — совокупность таблиц, имеющих одну схему и сегментированных по датам. Другими словами, под шард-таблицами понимается разделение больших наборов данных на отдельные таблицы и добавление суффикса к имени каждой таблицы. Имена таблиц имеют шаблон tablename_YYMMDD, где YYMMDD — шаблон даты. В отличие от партиционированных таблиц, шард-таблица не имеет колонки, по которой будет происходить сегментация данных. Обращение к шард-таблицам возможно с помощью синтаксиса wildcard-table. Шард-таблицы и запрос к ним с помощью оператора UNION могут имитировать партиционирование.

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

Кластеризация таблиц

Когда вы создаёте кластеризованную таблицу в BigQuery, данные таблицы автоматически сортируются на основе содержимого одного или нескольких столбцов в схеме таблицы. Указанные столбцы используются для размещения связанных данных. При кластеризации таблицы с использованием нескольких столбцов важен порядок указанных столбцов. Порядок указанных столбцов определяет порядок сортировки данных. Кластеризация может улучшить производительность определённых типов запросов, таких как запросы, использующие предложения фильтра, и запросы, которые объединяют данные. Когда данные записываются в кластеризованную таблицу, BigQuery сортирует данные, используя значения в столбцах кластеризации. Когда вы выполняете запрос, содержащий в фильтре данные на основе столбцов кластеризации, BigQuery использует отсортированные блоки, чтобы исключить сканирование ненужных данных. Вы можете не увидеть значительной разницы в производительности запросов между кластеризованной и некластеризованной таблицей, если размер таблицы или раздела меньше 1 ГБ.

Пример из жизни. Работа с GDPR

Нам пришлось столкнуться с задачей анонимизации данных по регламенту GDPR для Google Analytics (GA) в BigQuery. Задача заключалась в том, что каждый день в BigQuery импортировались данные из Google Analytics (GA). Поскольку в GA хранились персональные данные пользователей, для удалённых пользователей необходимо было очищать персональную информацию по регламенту GDPR. Данные, которые необходимо было анонимизировать, находились в поле с типом array. Аккаунт BigQuery, который нам предоставили, работал с ценовой моделью On-demand, в которой стоимость рассчитывалась из обработанных данных каждого запроса. Данные хранились в шард-таблицах. Google не рекомендует использовать шардированные таблицы и предлагает взамен партиционирование + кластеризацию, но, к сожалению, в Google Analytics (GA) это стандартная структура хранения данных. При экспорте данных из Google Analytics в BQ создаётся шард-таблица ga_sessions_, сегментированная по датам. В таблице находится порядка 16 полей, для нашей задачи необходимы были поля:

• fullVisitorId (string) — уникальный идентификатор посетителя GA (также известный как идентификатор клиента)
• customDimensions (array) — поле с типом array, содержит пользовательские данные, которые устанавливаются для каждого сеанса пользователя.

В поле customDimensions хранятся значения (идентификаторы), которые нам необходимо анонимизировать. Значения хранятся под определёнными индексами в массиве customDimensions.

Решение задачи

Мы создали в BQ таблицу opted_out_visitors для хранения пользователей, информацию о которых необходимо анонимизировать.

Схема таблицы:

В данной таблице мы храним копию данных из ga_sessions_, которые подпадают под регламент GDPR.

При получении запроса на удаление пользователя в нашей системе мы находили в таблицах GA данные пользователя для анонимизации и добавляли значения в таблицу opted_out_visitors:

INSERT INTO `dataset`.opted_out_visitors ( SELECT fullVisitorId, date, customDimensions FROM `dataset`.`ga_sessions_*`, UNNEST(customDimensions) AS cd WHERE cd.index=11 and cd.value="1111111")

где cd.index=11 — индекс массива customDimensions, в котором хранятся идентификаторы пользователя, а cd.value=»1111111″ — идентификатор пользователя, данные которого необходимо почистить в таблицах GA. Собственно, в данной таблице мы имеем идентификатор пользователя в сеансе GA (fullVisitorId), данные пользователя (customDimensions) для анонимизации и дату, когда пользователь оставил за собой следы. После добавления данных в таблицу opted_out_visitors чистим значение, которое необходимо заменить.

UPDATE `dataset`.`opted_out_visitors` SET customDimensions = ARRAY( SELECT (index, IF(index = 3, "GDPR", value)) FROM UNNEST(customDimensions) ) where 1=1

Осталось почистить значения в оригинальных таблицах GA. Для этого мы раз в 7 дней запускаем скрипт, который обновляет данные в ga_sessions для каждой даты в opted_out_visitors:

MERGE `dataset`.`ga_sessions_20200112` S USING `dataset`.`opted_out_visitors` O ON S.fullVisitorId = O.fullVisitorId WHEN MATCHED and O.date='20200112' THEN UPDATE SET S.customDimensions = O.customDimensions

После этого очищаем таблицу opted_out_visitors.

Многие читатели могут подумать, зачем так запариваться, создавать отдельную таблицу opted_out_visitors, хранить там идентификаторы из ga_sessions с анонимизированными данными, а потом всё это мержить раз в N дней. Дело в том, что каждая таблица ga_sessions занимает около 10 ГБ, и с каждым днём количество таблиц увеличивалось. Если бы мы выполняли анонимизацию данных каждый раз при поступлении запроса на удаление, мы получили бы огромные затраты в BigQuery. Но и с данным подходом нам не удалось добиться минимальных затрат. После анализа всех запросов мы выявили, что проблемным местом является запрос поиска анонимных данных и добавления в таблицу opted_out_visitors.

INSERT INTO `dataset`.opted_out_visitors ( SELECT fullVisitorId, date, customDimensions FROM `dataset`.`ga_sessions_*`, UNNEST(customDimensions) AS cd WHERE cd.index=11 and cd.value="1111111" )

В данном запросе мы обращаемся к таблице ga_sessions_ за весь период, откуда получаем данные из столбца customDimensions (напомним, что этот столбец имеет тип array, который может хранить в себе любой объём данных). В итоге один запрос весил около 40 ГБ, так как BigQuery сканировал все таблицы ga_sessions_ и искал информацию в столбце customDimensions. Таких запросов в день было 50–80. Таким образом, меньше чем за день мы тратили весь бесплатный месячный трафик (1 ТБ).

Оптимизация

Поскольку проблема заключалась в чтении данных из customDimensions каждый раз, когда пользователь удалялся, было решено отказаться от постоянного обращения к данному столбцу при удалении пользователя и хранить только минимальную информацию, которая понадобится в дальнейшем для поиска и удаления анонимных данных. В итоге таблица opted_out_visitors была упразднена, и была создана новая таблица opted_out_users_id, в которую мы добавляем только идентификатор пользователя user_id и идентификатор, которым будем заменять анонимные данные.

Каждый раз, когда к нам приходит запрос на удаления пользователя, в opted_out_users_id мы добавляем user_id и anonymized_id

INSERT INTO `dataset`.opted_out_users_id (user_id, anonymized_id) SELECT user_id, anonymized_id FROM (select '' as user_id, '' as anonymized_id) WHERE NOT (EXISTS ( SELECT 1 FROM `dataset`.opted_out_users_id WHERE `dataset`.opted_out_users_id.user_id = '' ) )

Теперь при добавлении новой записи в opted_out_users_id мы тратим около 15 КБ, что значительно меньше, чем когда мы работали напрямую со столбцом customDimensions. Далее, раз в 7 дней нам необходимо запускать скрипт, который будет анонимизировать необходимые значения в ga_sessions_. Так как ga_sessions_ — это шард-таблицы, сегментированные по датам, мы не можем обратиться к таблицам через wildcard-синтаксис ga_sessions_* в операторах UPDATE, INSERT, MERGE. Придётся найти даты, где удалённые пользователи были замечены, и далее обращаться напрямую к каждой таблице ga_sessions_. Для этого перед анонимизацией данных мы находим все даты, где фигурируют удалённые пользователи.

SELECT ga.date, ARRAY_AGG(DISTINCT oous.user_id) AS accounts FROM `dataset.opted_out_users_id` AS oous LEFT JOIN ( SELECT `dataset.ga_sessions_*`.date, cd.index AS index, cd.value AS value FROM `dataset.ga_sessions_*`, unnest(`dataset.ga_sessions_*`.customDimensions) AS cd ) AS ga ON oous.user_id = ga.value AND ga.index = 3 GROUP BY ga.date

Запрос возвращает всех пользователей, сгруппированных по датам. В данном запросе мы потребляем порядка 30 ГБ трафика, что вполне допустимо для нас, т. к. запрос выполняется раз в 7 дней. Ну что же, теперь можно выполнить анонимизацию.

UPDATE `dataset`.`ga_sessions_` as ga SET customDimensions=ARRAY( SELECT ( index, CASE WHEN index = THEN ac.anonymized_id WHEN index in cleanup_fields THEN null else value end ) FROM unnest(ga.customDimensions) ) FROM `dataset`.`opted_out_users_id` as ac WHERE ac.user_id=( SELECT value FROM unnest(ga.customDimensions) WHERE index=3 )

В данном запросе для удалённых пользователей в столбце customDimensions производим замену user_id на anonymize_id, которые расположены в индексе с номером lookup_field, а для остальных индексов, которые расположены в cleanup_fields, чистим значения, устанавливая для них null. Запрос потребляет около 7 ГБ трафика, что также приемлемо для нас. В итоге в сумме все наши запросы не выходят за рамки бесплатного лимита 1 ТБ в месяц.

Итоги

Хочется сказать в конце, что BigQuery вполне достойное облачное хранилище данных. Для хранения небольшого количества данных можно уложиться в бесплатный лимит, но если ваши данные будут насчитывать терабайты и работать с данными вы будете часто, то и затраты будут высокими. С первого взгляда кажется, что 1 ТБ в месяц для запросов — это очень много, но подводный камень кроется в том, что BigQuery считает все данные, которые были обработаны во время выполнения запроса. И если вы работаете с обычными таблицами и попытаетесь выполнить какое-либо усечение данных в виде добавления WHERE либо LIMIT, то с грустью говорим вам, что BigQuery израсходует такой же объём трафика, как и при обычном запросе SELECT FROM. Однако если грамотно построить структуру вашей БД, вы сможете колоссально сэкономить свой бюджет в BigQuery.

Наши рекомендации:

• Избегайте SELECT *.
  Делайте запросы всегда только к тем полям, которые вам необходимы.
• Избегайте в таблицах полей с типами данных record, array (repeated record).
  Запросы, в которых присутствуют данные столбцы, будут потреблять больше трафика, т. к. BigQuery придётся обработать все данные этого столбца.
  Спасибо @ekoblov за дельное замечание.
• Старайтесь создавать секционированные таблицы (partitioned tables).
  Если грамотно разбить таблицу по партициям, в запросах, в которых будет происходить фильтрация по партиционированному полю, можно значительно снизить потребление трафика, т. к. BigQuery обработает только партицию таблицы, указанную в фильтре запроса.
• Старайтесь добавлять кластеризацию в ваших секционированных таблицах.
  Кластеризация позволяет отсортировать данные в ваших таблицах по заданным столбцам, что также сократит потребление трафика. При использовании фильтрации по кластеризованным столбцам в запросах BigQuery обработает только тот диапазон данных, который включает значения из вашего фильтра.
• Для подсчёта обработанных данных всегда используйте Cloud Console BigQuery.
  Когда вы вводите запрос в Cloud Console, валидатор запроса проверяет синтаксис запроса и предоставляет оценку количества прочитанных байтов. Эту оценку можно использовать для расчёта стоимости запроса в калькуляторе цен.

• Используйте калькулятор для оценки стоимости хранения данных и выполнения запросов: https://cloud.google.com/products/calculator/.
  Для оценки стоимости запросов в калькуляторе необходимо ввести количество байтов, обрабатываемых запросом, в виде Б, КБ, МБ, ГБ, ТБ. Если запрос обрабатывает менее 1 ТБ, оценка составит 0 долларов, поскольку BigQuery предоставляет 1 ТБ в месяц бесплатно для обработки запросов по требованию. Аналогичные действия можно выполнить и для оценки хранения данных.

• Блог компании Lesta Studio
• SQL
• Big Data
• Google Cloud Platform

Google BiqQuery для веб-аналитики

Интернет-маркетинг сегодня это огромное количество данных и цифр, и стандартных инструментов может быть недостаточно для их полного и глубокого анализа. В eLama.ru мы анализируем входящий трафик, эффективность многих маркетинговых каналов ( контекстная и таргетированная реклама, email, выступления на конференциях и обучающие вебинары, соцсети, блог, PR), а также активность наших клиентов. Есть задачи, для которых нам не хватает возможностей Google Analytics, Яндекс. Метрики и Excel.

В таких случаях мы используем Google BigQuery — реляционную систему управления базами данных ( СУБД), часть Google Cloud Platform, куда входит еще порядка 40-ка инструментов для вычисления, хранения и анализа данных.

В этом материале мы расскажем, как используем BigQuery в своей работе и расскажем в целом, какие возможности открывает инструмент.

Итак, рассмотрим одну типичную для нас задачу: определить эффективность обучающего вебинара. Для этого примера возьмем вебинар о ремаркетинге в Google AdWords, проведенный 28 марта. Нам нужно выяснить, сколько участников зарегистрировались в еЛаме после обучения и сколько из них подключили аккаунт AdWords.

Для этого в BigQuery мы сведем данные из трех источников:

• информация о посетителях вебинара выгружается в CSV-файле из сервиса ClickMeeting ( сторонняя платформа для проведения онлайн-конференций и вебинаров);
• список пользователей е Ламы в CSV-файле из нашей собственной MySQL-базы;
• информация о подключении клиентами аккаунта Google AdWords — события на фронтенде нашего сайта, которые фиксируются в Google Analytics.

Способы загрузки

Данные в BigQuery можно загружать с помощью:

• импорта файлов ( прямой или с помощью дополнительных инструментов);
• API. Доступны клиентские библиотеки для большинства популярных языков программирования;
• стриминга данных из Google Analytics.

Для обработки данных в BigQuery используется схожий с SQL собственный язык с очень высокой скоростью выполнения запросов.

Загрузка данных и выполнение запроса

Теперь опишем порядок действий и необходимый инструментарий для решения нашей задачи.

Интерфейс BigQuery, как и всего Cloud Platform, не доступен на русском языке, а рабочая среда выглядит так:

Чтобы начать работу, задаем название проекта и базы данных ( dataset). Остальные поля можно не редактировать.

• Дальше загружаем в BigQuery список пользователей, посетивших вебинар. Создадим таблицу в Google Sheets и импортируем ее в BigQuery с помощью бесплатного плагина для браузера OWOX BI BigQuery Reports.

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

При импорте нужно указать имена нужного проекта и базы данных, название импортируемой таблицы и схему данных. Каждая колонка в нашей таблице соответствует определенному типу данных, который нужно указать. Их не так много, как в традиционных СУБД, и они интуитивно понятны. Названия колонок автоматически подставляются из первой строки таблицы Google Sheets.

• Загружаем в BigQuery список пользователей еЛамы. Эти данные передаются в CSV-файле напрямую в BigQuery, так как Google Sheets не справляются с таким большим объемом данных:

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

• Настройка стриминга из Google Analytics. Для полного анализа нам нужно, чтобы в BigQuery хранились данные о хитах: просмотрах страниц и всех действиях пользователей, которые фиксирует Google Analytics. В данном примере нас интересуют события категории «BidderAdWords» ( этой категорией мы обозначаем события, связанные с нашими инструментами для работы с AdWords) и «Указал логин», сигнализирующие о подключении пользователем аккаунта AdWords.

Cуществует инструмент, импортирующий данные из Google Analytics 360 ( ранее Analytics Premium). Мы же используем OWOX BI Streaming. Для его настройки нужно установить на сайте дополнительные теги, и данные будут автоматически отправляться с фронтенда сайта на сервера BigQuery параллельно с отправкой данных на сервера Google Analytics.

• Чтобы получить ответ на наш вопрос о регистрациях в еЛаме и подключении AdWords участниками вебинара, нужно выполнить такой запрос:

Так выглядит отправка запроса и результат в интерфейсе BigQuery:

В таблице представлены 32 пользователя, которые зарегистрировались в еЛаме после вебинара. Один из них спустя три часа после регистрации подключил себе аккаунт AdWords. Эту таблицу можно дополнить финансовыми показателями, например, пополнениями баланса еЛамы новыми клиентами. Для этого нужно загрузить в BigQuery таблицу с транзакциями и дополнить запрос еще одним JOIN.

Другие возможности BigQuery

BigQuery позволяет строить разнообразные отчеты любой сложности. Например, мы можем выяснить, на какую сумму клиенты еЛамы пополняли счет до посещения вебинара и сколько эти же клиенты заплатили после посещения вебинара за аналогичный период времени.

Можно составить список заинтересованных пользователей, например, тех, кто совершали какие-то действия на сайте, но так и не пополнили счет. И затем передать такой список в отдел продаж для звонков.

Еще одна возможность — создание списков ремаркетинга по определенным условиям. Например, мы можем выделить тех, кто подключил аккаунт AdWords, но так и не пополнил баланс. Написав и выполнив соответствующие запросы, мы получим список user_id, по которому можем создать аудиторию и использовать ее в рекламе.

user_id ( здесь ID) — это пользовательский параметр в Google Analytics, который передается с каждым хитом.

В отличие от стандартного Google Analytics, BigQuery работает с полным объемом данных. Даже для небольших проектов Analytics сэмплирует данные, составляя обычные отчеты с периодом больше месяца. Думаю, многие видели такие предупреждения:

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

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

Также в BigQuery можно подключить инструменты визуализации данных, например, Tableau, QlikView и др. Они представляют информацию и изменения наглядно и обладают широким функционалом построения графических отчетов.

Мы хотели сравнить скорость обработки запросов в BigQuery и в MySQL на обычном хостинге. Но эксперимент потерпел неудачу. Мы сделали несколько попыток загрузить CSV-файл в MySQL, и каждый раз импорт прерывался из-за погрешностей, например, лишних кавычек в полях с данными. BigQuery корректно обрабатывает подобные ошибки. Кроме того, на мой взгляд, система MySQL сложнее в освоении, чем BigQuery, в ее использовании больше технических нюансов, и для нее нет готовых решений по стримингу данных из Google Analytics.

Заключение

Google BigQuery — универсальный инструмент для аналитики. Он несложный и интуитивно понятный в использовании. Поэтому, если вы подозреваете, что для необходимого анализа вам мало возможностей Analytics и Метрики, начинайте разбираться с BigQuery.

eLama.ru , руководитель группы веб-аналитики