google bigquery api php
Обзор основных функций Google BigQuery и примеры запросов для маркетинг-анализа
Google BigQuery – это быстрое, экономичное и масштабируемое хранилище для работы с Big Data, которое вы можете использовать, если у вас нет возможности или желания содержать собственные серверы. В нем можно писать запросы с помощью SQL-like синтаксиса, стандартных и пользовательских функций (User-defined function).
В статье я расскажу про основные функции BigQuery и покажу их возможности на конкретных примерах. Вы сможете писать базовые запросы, и опробовать их на demo данных.
Что такое SQL и какие у него диалекты
SQL (Structured Query Language) — язык структурированных запросов для работы с базами данных. С его помощью можно получать, добавлять в базу и изменять большие массивы данных. Google BigQuery поддерживает два диалекта: Standard SQL и устаревший Legacy SQL.
Какой диалект выбрать, зависит от ваших предпочтений, но Google рекомендует использовать Standard SQL из-за ряда преимуществ:
По умолчанию запросы в Google BigQuery запускаются на Legacy SQL.
Переключиться на Standard SQL можно несколькими способами:
С чего начать
Чтобы вы смогли потренироваться запускать запросы параллельно с чтением статьи, я подготовила для вас таблицу с demo-данными. Загрузите данные из таблицы в ваш проект Google BigQuery.
Если у вас еще нет проекта в GBQ, создайте его. Для этого понадобится активный биллинг-аккаунт в Google Cloud Platform. Понадобится привязать карту, но без вашего ведома деньги с нее списываться не будут, к тому же при регистрации вы получите 300$ на 12 месяцев, который сможете потратить на хранение и обработку данных.
Функции Google BigQuery
При построении запросов чаще всего используются следующие группы функций: Aggregate function, Date function, String function и Window function. Теперь подробнее о каждой из них.
Функции агрегирования данных (Aggregate function)
Функции агрегирования позволяют получить сводные значения по всей таблице. Например, рассчитать средний чек, суммарный доход за месяц или выделить сегмент пользователей, совершивших максимальное количество покупок.
Вот самые популярные функции из этого раздела:
| Legacy SQL | Standard SQL | Что делает функция |
|---|---|---|
| AVG(field) | AVG([DISTINCT] (field)) | Возвращает среднее значение столбца field.В Standard SQL при добавлении условия DISTINCT среднее считается только для строк с уникальными (не повторяющимися) значениями из столбца field |
| MAX(field) | MAX(field) | Возвращает максимальное значение из столбца field |
| MIN(field) | MIN(field) | Возвращает минимальное значение из столбца field |
| SUM(field) | SUM(field) | Возвращает сумму значений из столбца field |
| COUNT(field) | COUNT(field) | Возвращает количество строк в столбце field |
| EXACT_COUNT_DISTINCT(field) | COUNT([DISTINCT] (field)) | Возвращает количество уникальных строк в столбце field |
С перечнем всех функций вы можете ознакомиться в справке: Legacy SQL и Standard SQL.
Давайте посмотрим на примере demo данных, как работают перечисленные функции. Вычислим средний доход по транзакциям, покупки с наибольшей и наименьшей суммой, общий доход и количество всех транзакций. Чтобы проверить, не дублируются ли покупки, рассчитаем также количество уникальных транзакций. Для этого пишем запрос, в котором указываем название своего Google BigQuery проекта, набора данных и таблицы.
В итоге получаем такие результаты:
Проверить результаты расчетов вы можете в исходной таблице с demo данными, используя стандартные функции Google Sheets (SUM, AVG и другие) или сводные таблицы.
Как видим из скриншота выше, количество транзакций и уникальных транзакций отличается.
Это говорит о том, что в нашей таблице есть 2 транзакции, у которых дублируется transactionId:
Поэтому, если вас интересуют именно уникальные транзакции, используйте функцию, которая считает уникальные строки. Либо вы можете сгруппировать данные с помощью выражения GROUP BY, чтобы избавиться от дублей перед тем, как применять функцию агрегации.
Функции для работы с датами (Date function)
Эти функции позволяют обрабатывать даты: менять их формат, выбирать необходимую часть (день, месяц или год), смещать дату на определенный интервал.
Они могут вам пригодится в следующих случаях:
| Legacy SQL | Standard SQL | Что делает функция |
|---|---|---|
| CURRENT_DATE() | CURRENT_DATE() | Возвращает текущую дату в формате %ГГГГ-%ММ-%ДД |
| DATE(timestamp) | DATE(timestamp) | Преобразует дату из формата %ГГГГ-%ММ-%ДД %Ч:%M:%С. в формат %ГГГГ-%ММ-%ДД |
| DATE_ADD(timestamp, interval, interval_units) | DATE_ADD(timestamp, INTERVAL interval interval_units) | Возвращает дату timestamp, увеличивая ее на указанный интервал interval.interval_units. |
В Legacy SQL может принимать значения YEAR, MONTH, DAY, HOUR, MINUTE и SECOND, а в Standard SQL — YEAR, QUARTER, MONTH, WEEK, DAY
В Legacy SQL возвращает разницу в днях, а в Standard SQL — в зависимости от указанного значения date_part (день, неделя, месяц, квартал, год)
Список всех функций – в справке: Legacy SQL и Standard SQL.
Рассмотрим на demo данных, как работает каждая из приведенных функций. К примеру, получим текущую дату, приведем дату из исходной таблицы в формат %ГГГГ-%ММ-%ДД, отнимем и прибавим к ней по одному дню. Затем рассчитаем разницу между текущей датой и датой из исходной таблицы и разобьем текущую дату отдельно на год, месяц и день. Для этого вы можете скопировать примеры запросов ниже и заменить в них название проекта, набора данных и таблицы с данными на свои.
После применения запроса вы получите вот такой отчет:
Функции для работы со строками (String function)
Строковые функции позволяют формировать строку, выделять и заменять подстроки, рассчитывать длину строки и порядковый номер индекса подстроки в исходной строке.
Например, с их помощью можно:
| Legacy SQL | Standard SQL | Что делает функция |
|---|---|---|
| CONCAT(‘str1’, ‘str2’) или’str1’+ ‘str2’ | CONCAT(‘str1’, ‘str2’) | Объединяет несколько строк ‘str1’ и ‘str2’ в одну |
| ‘str1’ CONTAINS ‘str2’ | REGEXP_CONTAINS(‘str1’, ‘str2’) или ‘str1’ LIKE ‘%str2%’ | Возвращает true если строка ‘str1’ содержит строку ‘str2’. В Standard SQL строка ‘str2’ может быть записана в виде регулярного выражения с использованием библиотеки re2 |
| LENGTH(‘str’ ) | CHAR_LENGTH(‘str’ ) или CHARACTER_LENGTH(‘str’ ) | Возвращает длину строки ‘str’ (количество символов в строке) |
| SUBSTR(‘str’, index [, max_len]) | SUBSTR(‘str’, index [, max_len]) | Возвращает подстроку длиной max_len, начиная с символа с индексом index из строки ‘str’ |
| LOWER(‘str’) | LOWER(‘str’) | Приводит все символы строки ‘str’ к нижнему регистру |
| UPPER(str) | UPPER(str) | Приводит все символы строки ‘str’ к верхнему регистру |
| INSTR(‘str1’, ‘str2’) | STRPOS(‘str1’, ‘str2’) | Возвращает индекс первого вхождения строки ‘str2’ в строку ‘str1’, иначе — 0 |
| REPLACE(‘str1’, ‘str2’, ‘str3’) | REPLACE(‘str1’, ‘str2’, ‘str3’) | Заменяет в строке ‘str1’ подстроку ‘str2’ на подстроку ‘str3’ |
Разберем на примере demo данных, как использовать описанные функции. Предположим, у нас есть 3 отдельных столбца, которые содержат значения дня, месяца и года:
Работать с датой в таком формате не очень удобно, поэтому объединим ее в один столбец. Чтобы сделать это, используйте SQL-запросы, приведенные ниже, и не забудьте подставить в них название своего проекта, набора данных и таблицы в Google BigQuery.
После выполнения запроса мы получим дату в одном столбце:
Часто при загрузке определенной страницы на сайте в URL записываются значения переменных, которые выбрал пользователь. Это может быть способ оплаты или доставки, номер транзакции, индекс физического магазина, в котором покупатель хочет забрать товар и т. д. С помощью SQL-запроса можно выделить эти параметры из адреса страницы.
Рассмотрим два примера, как и зачем это делать.
Пример 1. Предположим, мы хотим узнать количество покупок, при которых пользователи забирают товар из физических магазинов. Для этого нужно посчитать количество транзакций, отправленных со страниц, в URL которых есть подстрока shop_id (индекс физического магазина). Делаем это с помощью следующих запросов:
Из полученной в результате таблицы мы видим, что со страниц, содержащих shop_id, отправлено 5502 транзакции (check = true):
Пример 2. Допустим, вы присвоили каждому способу доставки свой delivery_id и прописываете значение этого параметра в URL страницы. Чтобы узнать, какой способ доставки выбрал пользователь, нужно выделить delivery_id в отдельный столбец.
Используем для этого следующие запросы:
В результате получаем в Google BigQuery такую таблицу:
Функции для работы с подмножествами данных или оконные функции (Window function)
Эти функции похожи на функции агрегирования, которые мы рассмотрели выше. Их основное отличие заключается в том, что вычисления проводятся не на всем множестве данных, выбранных с помощью запроса, а на его части — подмножестве или окне.
С помощью оконных функций вы можете агрегировать данные в разрезе групп, не используя оператор JOIN для объединения нескольких запросов. Например, рассчитать средний доход в разрезе рекламных кампаний, количество транзакций в разрезе устройств. Добавив еще одно поле в отчет, вы сможете легко узнать, к примеру, долю дохода от рекламной кампании на Black Friday или долю транзакций, сделанных из мобильного приложения.
Вместе с каждой функцией в запросе необходимо прописывать выражение OVER, которое определяет границы окна. OVER содержит 3 компоненты, с которыми вы можете работать:
| Legacy SQL | Standard SQL | Что делает функция |
|---|---|---|
| AVG(field) COUNT(field) COUNT(DISTINCT field) MAX() MIN() SUM() | AVG([DISTINCT] (field)) COUNT(field) COUNT([DISTINCT] (field)) MAX(field) MIN(field) SUM(field) | Возвращает среднее значение, количество, максимальное, минимальное и суммарное значение из столбца field в рамках выбранного подмножества. |
DISTINCT используется, если нужно посчитать только уникальные (неповторяющиеся) значения
В Standard SQL строка ‘str2’ может быть записана в виде регулярного выражения с использованием библиотеки re2
По умолчанию строки с пустыми значениями из столбца field включаются в расчет. RESPECT или IGNORE NULLS определяет, включать или игнорировать строки со значением NULL
По умолчанию строки с пустыми значениями из столбца field включаются в расчет. RESPECT или IGNORE NULLS определяет, включать или игнорировать строки со значением NULL
Offset определяет количество строк, на которое необходимо смещаться вниз по отношению к текущей строке. Является целым числом.
Default_expression — значение, которое будет возвращать функция, если в рамках подмножества нет необходимой строки
Offset определяет количество строк, на которое необходимо смещаться вверх по отношению к текущей строке. Является целым числом.
Default_expression — значение, которое будет возвращать функция, если в рамках текущего подмножества нет необходимой строки
Список всех функций вы можете посмотреть в справке для Legacy SQL и для Standard SQL: Aggregate Analytic Functions, Navigation Functions.
Пример 1. Допустим, мы хотим проанализировать активность покупателей в рабочее и нерабочее время. Для этого необходимо разделить транзакции на 2 группы и рассчитать интересующие нас метрики:
| Подмножество (окно) | clientId | DayTime |
|---|---|---|
| 1 окно | clientId 1 | Рабочее время |
| 2 окно | clientId 2 | Нерабочее время |
| 3 окно | clientId 3 | Рабочее время |
| 4 окно | clientId 4 | Нерабочее время |
| N окно | clientId N | Рабочее время |
| N+1 окно | clientId N+1 | Нерабочее время |
Давайте на demo данных рассчитаем средний, максимальный, минимальный, и суммарный доход, количество транзакций и количество уникальных транзакций по каждому пользователю в рабочее и нерабочее время. Сделать это нам помогут запросы, приведенные ниже.
Посмотрим, что получилось в результате, на примере одного из пользователей с clientId=’102041117.1428132012′. В исходной таблице по этому пользователю у нас были следующие данные:
Применив запрос, мы получили отчет, который содержит средний, минимальный, максимальный и суммарный доход с этого пользователя, а также количество транзакций. Как видно на скриншоте ниже, обе транзакции пользователь совершил в рабочее время:
Пример 2. Теперь немного усложним задачу:
Результаты расчетов проверим на примере уже знакомого нам пользователя с clientId=’102041117.1428132012′:
Из скриншота выше мы видим, что:
Выводы
В этой статье мы рассмотрели самые популярные функции из разделов Aggregate function, Date function, String function, Window function. Однако в Google BigQuery есть еще много полезных функций, например:
Как загрузить данные в Google BigQuery
Перевод статьи подготовлен в преддверии старта курса «Нереляционные базы данных».
В этой статье мы рассмотрим варианты загрузки данных в облачное хранилище Google BigQuery. Сюда входят простые способы загрузки данных из CSV/JSON файлов и способы загрузки через API или расширение.
С помощью Google BigQuery (GBQ) можно собирать данные из разных источников и анализировать их с помощью SQL-запросов. Среди преимуществ GBQ — высокая скорость вычислений даже на больших объемах данных и низкая стоимость.
Зачем нужно загружать данные в единое хранилище? Если вы хотите использовать сквозную аналитику, генерировать отчеты из сырых данных и оценивать эффективность вашего маркетинга, то вам нужен Google BigQuery.
Если вам нужно проанализировать терабайты данных за секунды, Google BigQuery — самый простой и доступный выбор. Вы можете узнать больше об этом сервисе, посмотрев короткий видеоролик на YouTube-канале Google Developers.
Создание набора данных и таблицы
Перед загрузкой каких-либо данных, сперва необходимо создать набор данных и таблицу в Google BigQuery. Для этого на главной странице BigQuery выберите ресурс, в котором вы хотите создать набор данных.
Изображения, используемые в статье, предоставлены автором
Укажите в окне «Create dataset» идентификатор набора данных, выберите место обработки данных и установите срок хранения таблицы по умолчанию.
Примечание: Если вы выберете «Never» в качестве истечения срока хранения таблицы, физическое хранилище не будет определено. Для временных таблиц вы можете указать количество дней их хранения.
Затем создайте таблицу в наборе данных.
Готово! Теперь можете начать загрузку данных.
Загрузка данных с помощью Google Таблиц (расширение OWOX BI BigQuery Reports).
Если вам нужно загрузить данные из Google Таблиц в Google BigQuery, самый простой способ сделать это — установить бесплатное расширение OWOX BI BigQuery Reports.
Вы можете установить это расширение прямо из Google Таблиц или из Chrome Web Store.
После его установки появится диалоговое окно с подсказками и запросом разрешений.
Укажите проект, набор данных и имя таблицы, в которую нужно загрузить данные. И все 🙂
Неоспоримым преимуществом расширения OWOX BI BigQuery Reports является простота использования. Вы также можете использовать расширение для настройки запланированных отчетов.
Чтобы создавать отчеты на основе точных сырых данных из всех источников и автоматически загружать их в репозиторий Google BigQuery, мы рекомендуем использовать сервис OWOX BI Pipeline.
С помощью Pipeline вы можете настроить автоматический сбор данных из рекламных сервисов, из систем отслеживания звонков и CRM. Это позволяет быстро и легко получать готовые наборы полных данных из выбранных вами источников.
Просто выберите источники данных и разрешите доступ; остальное оставьте OWOX BI.
С OWOX BI вы можете создавать отчеты на любой вкус и цвет, от ROI, ROPO-эффекта и когортного анализа до LTV и RFM анализа.
Загрузка данных из CSV-файлов
Чтобы загрузить данные из CSV-файла, в окне «Create table» нужно выбрать источник данных и использовать опцию «Upload».
Затем выберите файл и его формат.
Далее нужно определить место назначения для данных, указав имя проекта и набор данных.
Примечание: В Google BigQuery вы можете выбрать два типа таблиц: в нативном формате и внешнем (external).
Google BigQuery автоматически определит структуру таблицы, но если вы хотите добавить поля вручную, вы можете использовать либо функцию редактирования текста, либо кнопку + Add field.
Примечание: Если вы хотите вмешаться в процесс парсинга данных из CSV-файла в Google BigQuery, вы можете воспользоваться расширенными параметрами.
Для получения дополнительной информации о формате CSV см. подробную документацию от Internet Society.
Загрузка данных из JSON-файлов
Чтобы загрузить данные из JSON-файла, повторите все шаги, приведенные выше: создайте или выберите набор данных и таблицу, с которыми вы работаете, — только в качестве формата файла выберете JSON.
Вы можете загрузить JSON-файл со своего компьютера, Google Cloud Storage или диска Google Drive.
Примечание: Дополнительную информацию о формате JSON см. в документации Google Cloud.
Загрузка данных из Google Cloud Storage.
Google Cloud Storage позволяет безопасно хранить и передавать данные онлайн.
Полезная информация о работе с этим сервисом:
Вы можете загружать файлы из Google Cloud Storage в Google BigQuery в следующих форматах:
Подробнее об использовании Cloud Storage с big data можно прочитать в официальной документации.
Вы также можете узнать об ограничениях на загрузку данных и разрешениях Cloud Storage в справочном центре Google Cloud.
Загрузка данных из других сервисов Google, таких как Google Ads и Google Ad Manager.
Чтобы загрузить данные из различных сервисов Google, сначала необходимо настроить BigQuery Data Transfer Service. Прежде чем вы сможете его использовать, вы должны выбрать или создать проект данных и, в большинстве случаев, включить для него биллинг. Например, биллинг является обязательным для следующих служб:
Примечание: Подробнее о настройке и изменении оплаты вы можете узнать в справочном центре Google Cloud.
Для того чтобы запустить BigQuery Data Transfer Service, на главной странице BigQuery выберите пункт «Transfers» в меню слева.
Примечание: Вам потребуются права администратора для создания Transfer’а.
В следующем окне все, что вам нужно сделать, это выбрать нужный источник данных.
После настройки служба будет автоматически и регулярно загружать данные в BigQuery. Однако вы не можете использовать ее для выгрузки данных из BigQuery.
Загрузка данных с помощью API
Благодаря Cloud Client Libraries вы можете использовать свой любимый язык программирования для работы с API Google BigQuery.
Примечание: Более подробную информацию о загрузке данных с помощью API можно найти в документации Google Cloud.
Для начала вам необходимо создать или выбрать проект, с которым вы будете работать. Затем на главной странице перейдите в раздел API.
В окне обзора API вы можете подключить API и службы. Нужно выбрать необходимый вам API из библиотеки.
В библиотеке вы можете использовать поиск по полю или отфильтровать API по категории.
Можно использовать набор скриптов на Python из OWOX BI для автоматизации импорта данных в Google BigQuery.
Там есть скрипты для автоматизации импорта данных в Google BigQuery из следующих источников:
Эти Python-скрипты можно загрузить с GitHub.
Примечание: Узнайте, как использовать Python при работе с Google API, из этого видеоурока от Google Developers на YouTube.
Выводы
В этой статье мы рассмотрели наиболее популярные способы загрузки данных в Google BigQuery. От простой загрузки файла с данными до загрузки данных через API — любой пользователь сможет найти подходящий ему вариант.