Bigquery что это: Обзор основных функций Google BigQuery и примеры запросов для маркетинг-анализа / Хабр

Содержание

Обзор основных функций 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 из-за ряда преимуществ:

Гибкость и функциональность при работе с вложенными и повторяющимися полями.

Поддержка языков DML и DDL, которые позволяют менять данные в таблицах, а также управлять таблицами и представлениями в GBQ.

Скорость обработки больших объемов данных выше, чем у Legasy SQL.

Поддержка всех текущих и будущих обновлений в BigQuery.

Подробнее о разнице между диалектами вы можете узнать в справке.

По умолчанию запросы в Google BigQuery запускаются на Legacy SQL.

Переключиться на Standard SQL можно несколькими способами:

В интерфейсе BigQuery в окне редактирования запроса выберите «Show Options» и снимите галочку возле опции «Use Legacy SQL»

Перед запросом добавьте строку #standardSQL и начните запрос с новой строки

С чего начать

Чтобы вы смогли потренироваться запускать запросы параллельно с чтением статьи, я подготовила для вас таблицу с 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.

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

#legasy SQL

SELECT
  AVG(revenue) as average_revenue,
  MAX(revenue) as max_revenue,
  MIN(revenue) as min_revenue,
  SUM(revenue) as whole_revenue,
  COUNT(transactionId) as transactions,
  EXACT_COUNT_DISTINCT(transactionId) as unique_transactions
FROM
  [owox-analytics:t_kravchenko.Demo_data]

#standard SQL

SELECT
  AVG(revenue) as average_revenue,
  MAX(revenue) as max_revenue,
  MIN(revenue) as min_revenue,
  SUM(revenue) as whole_revenue,
  COUNT(transactionId) as transactions,
  COUNT(DISTINCT(transactionId)) as unique_transactions
FROM
  `owox-analytics.t_kravchenko.Demo_data`

В итоге получаем такие результаты:

Проверить результаты расчетов вы можете в исходной таблице с demo данными, используя стандартные функции Google Sheets (SUM, AVG и другие) или сводные таблицы.

Как видим из скриншота выше, количество транзакций и уникальных транзакций отличается.

Это говорит о том, что в нашей таблице есть 2 транзакции, у которых дублируется transactionId:

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

Функции для работы с датами (Date function)

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

Они могут вам пригодится в следующих случаях:

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

При создании автоматически обновляемых отчетов или триггерных рассылок. Например, когда нужны данные за последние 2 часа, неделю или месяц.

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

Функции для работы с датами, используемые чаще всего:
Список всех функций – в справке: Legacy SQL и Standard SQL.

Рассмотрим на demo данных, как работает каждая из приведенных функций. К примеру, получим текущую дату, приведем дату из исходной таблицы в формат %ГГГГ-%ММ-%ДД, отнимем и прибавим к ней по одному дню. Затем рассчитаем разницу между текущей датой и датой из исходной таблицы и разобьем текущую дату отдельно на год, месяц и день. Для этого вы можете скопировать примеры запросов ниже и заменить в них название проекта, набора данных и таблицы с данными на свои.

#legasy SQL

SELECT
    CURRENT_DATE() AS today,
    DATE( date_UTC ) AS date_UTC_in_YYYYMMDD,
    DATE_ADD( date_UTC,1, 'DAY') AS date_UTC_plus_one_day,
    DATE_ADD( date_UTC,-1, 'DAY') AS date_UTC_minus_one_day,
    DATEDIFF(CURRENT_DATE(), date_UTC ) AS difference_between_date,
    DAY( CURRENT_DATE() ) AS the_day,
    MONTH( CURRENT_DATE()) AS the_month,
    YEAR( CURRENT_DATE()) AS the_year
  FROM
    [owox-analytics:t_kravchenko.Demo_data]

#standard SQL

SELECT
  today,
  date_UTC_in_YYYYMMDD,
  DATE_ADD( date_UTC_in_YYYYMMDD, INTERVAL 1 DAY) AS date_UTC_plus_one_day,
  DATE_SUB( date_UTC_in_YYYYMMDD,INTERVAL 1 DAY) AS date_UTC_minus_one_day,
  DATE_DIFF(today, date_UTC_in_YYYYMMDD, DAY) AS difference_between_date,
  EXTRACT(DAY FROM today ) AS the_day,
  EXTRACT(MONTH FROM today ) AS the_month,
  EXTRACT(YEAR FROM today ) AS the_year
FROM (
  SELECT
    CURRENT_DATE() AS today,
    DATE( date_UTC ) AS date_UTC_in_YYYYMMDD
  FROM
    `owox-analytics.t_kravchenko.Demo_data`)

После применения запроса вы получите вот такой отчет:

Функции для работы со строками (String function)

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

Например, с их помощью можно:

Сделать в отчете фильтры по UTM-меткам, которые передаются в URL страницы.

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

Заменить некорректные данные в отчете, например, если название кампании передалось с опечаткой.

Самые популярные функции для работы со строками:

Детальнее – в справке: Legacy SQL и Standard SQL.

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

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

#legasy SQL

SELECT
  CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1,
  the_day+'-'+the_month+'-'+the_year AS mix_string2
FROM (
  SELECT
    '31' AS the_day,
    '12' AS the_month,
    '2018' AS the_year
  FROM
    [owox-analytics:t_kravchenko.Demo_data])
GROUP BY
  mix_string1,
  mix_string2

#standard SQL

SELECT
  CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1
FROM (
  SELECT
    '31' AS the_day,
    '12' AS the_month,
    '2018' AS the_year
  FROM
    `owox-analytics.t_kravchenko.Demo_data`)
GROUP BY
  mix_string1

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

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

Рассмотрим два примера, как и зачем это делать.

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

#legasy SQL

SELECT
  COUNT(transactionId) AS transactions,
  check
FROM (
  SELECT
    transactionId,
    page CONTAINS 'shop_id' AS check
  FROM
    [owox-analytics:t_kravchenko.Demo_data])
GROUP BY
  check

#standard SQL

SELECT
  COUNT(transactionId) AS transactions,
  check1,
  check2
FROM (
  SELECT
    transactionId,
    REGEXP_CONTAINS( page, 'shop_id') AS check1,
    page LIKE '%shop_id%' AS check2
  FROM
    `owox-analytics.t_kravchenko.Demo_data`)
GROUP BY
  check1,
  check2

Из полученной в результате таблицы мы видим, что со страниц, содержащих shop_id, отправлено 5502 транзакции (check = true):

Пример 2. Допустим, вы присвоили каждому способу доставки свой delivery_id и прописываете значение этого параметра в URL страницы. Чтобы узнать, какой способ доставки выбрал пользователь, нужно выделить delivery_id в отдельный столбец.

Используем для этого следующие запросы:

#legasy SQL

SELECT
  page_lower_case,
  page_length,
  index_of_delivery_id,
  selected_delivery_id,
  REPLACE(selected_delivery_id, 'selected_delivery_id=', '') as delivery_id
FROM (
  SELECT
    page_lower_case,
    page_length,
    index_of_delivery_id,
    SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id
  FROM (
    SELECT
      page_lower_case,
      LENGTH(page_lower_case) AS page_length,
      INSTR(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id
    FROM (
      SELECT
        LOWER( page) AS page_lower_case,
        UPPER( page) AS page_upper_case
      FROM
        [owox-analytics:t_kravchenko.Demo_data])))
ORDER BY
  page_lower_case ASC

#standard SQL

SELECT
  page_lower_case,
  page_length,
  index_of_delivery_id,
  selected_delivery_id,
  REPLACE(selected_delivery_id, 'selected_delivery_id=', '') AS delivery_id
FROM (
  SELECT
    page_lower_case,
    page_length,
    index_of_delivery_id,
    SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id
  FROM (
    SELECT
      page_lower_case,
      CHAR_LENGTH(page_lower_case) AS page_length,
      STRPOS(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id
    FROM (
      SELECT
        LOWER( page) AS page_lower_case,
        UPPER( page) AS page_upper_case
      FROM
        `owox-analytics.t_kravchenko.Demo_data`)))
ORDER BY
  page_lower_case ASC

В результате получаем в Google BigQuery такую таблицу:

Функции для работы с подмножествами данных или оконные функции (Window function)

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

С помощью оконных функций вы можете агрегировать данные в разрезе групп, не используя оператор JOIN для объединения нескольких запросов. Например, рассчитать средний доход в разрезе рекламных кампаний, количество транзакций в разрезе устройств. Добавив еще одно поле в отчет, вы сможете легко узнать, к примеру, долю дохода от рекламной кампании на Black Friday или долю транзакций, сделанных из мобильного приложения.

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

PARTITION BY — определяет признак, по которому вы будете делить исходные данные на подмножества, например PARTITION BY clientId, DayTime.

ORDER BY — определяет порядок строк в подмножестве, например ORDER BY hour DESC.

WINDOW FRAME — позволяет обрабатывать строки внутри подмножества по определенному признаку. Например, можно посчитать сумму не всех строк в окне, а только первых пяти перед текущей строкой.

В этой таблице собраны оконные функции, используемые чаще всего:

Список всех функций вы можете посмотреть в справке для Legacy SQL и для Standard SQL: Aggregate Analytic Functions, Navigation Functions.

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

1 группа — покупки в рабочее время с 9:00 до 18:00 часов.

2 группа — покупки в нерабочее время с 00:00 до 9:00 и с 18:00 до 00:00.

Кроме рабочего и нерабочего времени, еще одним признаком для формирования окна будет clientId, то есть на каждого пользователя у нас получится по два окна:

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

#legasy SQL

SELECT
  date,
  clientId,
  DayTime,
  avg_revenue,
  max_revenue,
  min_revenue,
  sum_revenue,
  transactions,
  unique_transactions
FROM (
  SELECT
    date,
    clientId,
    DayTime,
    AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue,
    MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue,
    MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue,
    SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue,
    COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions,
    COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions
  FROM (
    SELECT
      date,
      date_UTC,
      clientId,
      transactionId,
      revenue,
      page,
      hour,
      CASE
        WHEN hour>=9 AND hour<=18 THEN 'рабочее время'
        ELSE 'нерабочее время'
      END AS DayTime
    FROM
      [owox-analytics:t_kravchenko.Demo_data]))
GROUP BY
  date,
  clientId,
  DayTime,
  avg_revenue,
  max_revenue,
  min_revenue,
  sum_revenue,
  transactions,
  unique_transactions
ORDER BY
  transactions DESC

#standard SQL

SELECT
  date,
  clientId,
  DayTime,
  avg_revenue,
  max_revenue,
  min_revenue,
  sum_revenue,
  transactions,
  unique_transactions
FROM (
  SELECT
    date,
    clientId,
    DayTime,
    AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue,
    MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue,
    MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue,
    SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue,
    COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions,
    COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions
  FROM (
    SELECT
      date,
      date_UTC,
      clientId,
      transactionId,
      revenue,
      page,
      hour,
      CASE
        WHEN hour>=9 AND hour<=18 THEN 'рабочее время'
        ELSE 'нерабочее время'
      END AS DayTime
    FROM
      `owox-analytics.t_kravchenko.Demo_data`))
GROUP BY
  date,
  clientId,
  DayTime,
  avg_revenue,
  max_revenue,
  min_revenue,
  sum_revenue,
  transactions,
  unique_transactions
ORDER BY
  transactions DESC

Посмотрим, что получилось в результате, на примере одного из пользователей с clientId=’102041117.1428132012′. В исходной таблице по этому пользователю у нас были следующие данные:

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

Пример 2. Теперь немного усложним задачу:

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

Выведем в отчет доход следующей / предыдущей транзакции (относительно текущей) в рамках окна.

Выведем доход первой и последней транзакций в окне.

Для этого используем следующие запросы:

#legasy SQL

SELECT
  date,
  clientId,
  DayTime,
  hour,
  rank,
  revenue,
  lead_revenue,
  lag_revenue,
  first_revenue_by_hour,
  last_revenue_by_hour
FROM (
  SELECT
    date,
    clientId,
    DayTime,
    hour,
    DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank,
    revenue,
    LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue,
    LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue,
    FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour,
    LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour
  FROM (
    SELECT
      date,
      date_UTC,
      clientId,
      transactionId,
      revenue,
      page,
      hour,
      CASE
        WHEN hour>=9 AND hour<=18 THEN 'рабочее время'
        ELSE 'нерабочее время'
      END AS DayTime
    FROM
      [owox-analytics:t_kravchenko.Demo_data]))
GROUP BY
  date,
  clientId,
  DayTime,
  hour,
  rank,
  revenue,
  lead_revenue,
  lag_revenue,
  first_revenue_by_hour,
  last_revenue_by_hour
ORDER BY
  date,
  clientId,
  DayTime,
  hour,
  rank,
  revenue,
  lead_revenue,
  lag_revenue,
  first_revenue_by_hour,
  last_revenue_by_hour

#standard SQL

SELECT
  date,
  clientId,
  DayTime,
  hour,
  rank,
  revenue,
  lead_revenue,
  lag_revenue,
  first_revenue_by_hour,
  last_revenue_by_hour
FROM (
  SELECT
    date,
    clientId,
    DayTime,
    hour,
    DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank,
    revenue,
    LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue,
    LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue,
    FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour,
    LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour
  FROM (
    SELECT
      date,
      date_UTC,
      clientId,
      transactionId,
      revenue,
      page,
      hour,
      CASE
        WHEN hour>=9 AND hour<=18 THEN 'рабочее время'
        ELSE 'нерабочее время'
      END AS DayTime
    FROM
      `owox-analytics.t_kravchenko.Demo_data`))
GROUP BY
  date,
  clientId,
  DayTime,
  hour,
  rank,
  revenue,
  lead_revenue,
  lag_revenue,
  first_revenue_by_hour,
  last_revenue_by_hour
ORDER BY
  date,
  clientId,
  DayTime,
  hour,
  rank,
  revenue,
  lead_revenue,
  lag_revenue,
  first_revenue_by_hour,
  last_revenue_by_hour

Результаты расчетов проверим на примере уже знакомого нам пользователя с clientId=’102041117.1428132012′:

Из скриншота выше мы видим, что:

Первая транзакция была в 15:00, а вторая — в 16:00.

После текущей транзакции в 15:00 была транзакция в 16:00, доход которой равен 25066 (столбец lead_revenue).

Перед текущей транзакцией в 16:00 была транзакция в 15:00, доход которой равен 3699 (столбец lag_revenue).

Первой в рамках окна была транзакция в 15:00, доход по которой равен 3699 (столбец first_revenue_by_hour).

Запрос обрабатывает данные построчно, поэтому для рассматриваемой транзакции последней в окне будет она сама и значения в столбцах last_revenue_by_hour и revenue будут совпадать.

Выводы

В этой статье мы рассмотрели самые популярные функции из разделов Aggregate function, Date function, String function, Window function. Однако в Google BigQuery есть еще много полезных функций, например:

Casting functions — позволяют приводить данные к определенному формату.

Table wildcard functions — позволяют обращаться к нескольким таблицам из набора данных.

Regular expression functions — позволяют описывать модель поискового запроса, а не его точное значение.

Пишите в комментариях, есть ли смысл писать так же подробно и о них.

Google BigQuery — зачем нужна облачная база данных

Ранее я публиковал цикл материалов о работе с Google BigQuery. В этой статье расскажу о преимуществах и особенностях сервиса, а также о дополнительных инструментах для BigQuery.

Google BigQuery — это облачная база данных с высочайшей скоростью обработки огромных массивов данных.

Как начать работу в Google BigQuery

Войдите в Google Cloud Platform. При первом запуске система предложит активировать бесплатный пробный период и получить кредит $300 на 12 месяцев. Честно говоря, чтобы потратить за год в BigQuery эту сумму, вам придется очень сильно постараться.

Для дальнейшей работы введите платежные данные.

Нажмите «Выбрать проект».

Затем — «Создать проект».

Примите условия использования платформы.

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

После подтверждения подождите несколько минут.

Вскоре вы получите оповещение, что проект создан.

Перейдите в раздел оплаты и привяжите платежный аккаунт.

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

Перейдите в интерфейс Google BigQuery и напишите свой первый запрос.

Чтобы открыть редактор запросов, нажмите «Compose query» или сочетание клавиш «Ctrl + Space».

Рассмотрим, как написать первый запрос на примере публичных данных в BigQuery. Возьмите первые 15 строк из таблицы project_tycho_reports, которая находится в наборе публичных данных lookerdata.

SELECT *
FROM [lookerdata:cdc.project_tycho_reports]
LIMIT 25

Запрос вернет результат:

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

{«0»:{«lid»:»1573230077755″,»ls»:»10″,»loff»:»»,»li_type»:»em»,»li_name»:»email»,»li_ph»:»Email»,»li_req»:»y»,»li_nm»:»email»},»1″:{«lid»:»1596820612019″,»ls»:»20″,»loff»:»»,»li_type»:»hd»,»li_name»:»country_code»,»li_nm»:»country_code»}}

Истории бизнеса и полезные фишки

Почему стоит выбрать именно Google BigQuery

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

Стоимость BigQuery значительно ниже стоимости аренды самого примитивного сервера: даже если вы очень постараетесь и будете ежедневно записывать в эту базу данных миллионы строк, все равно вряд ли сможете потратить более $5.

Следующее преимущество — простота использования. В любой другой системе управления базами данных (СУБД) помимо знания SQL придется долго разбираться с тонкостями администрирования и настройками базы.

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

У BigQuery всю административную часть на себя взял Google. В этом сервисе нет никаких настроек, индексов, движков таблиц, тайм-аутов или внешних ключей. Реализована поддержка только одной кодировки UTF-8.

Для работы с BigQuery достаточно знать, как загрузить данные в BigQuery, и иметь базовые знания в SQL.

Несмотря на простоту, в BigQuery реализована поддержка практически всех функций СУБД:

Правда, на момент публикации статьи сервис не поддерживает:

рекурсивные запросы;

создание хранимых процедур и функций;

транзакции.

Особенности SQL для Google BigQuery

BigQuery умеет переключаться между стандартным SQL и диалектами.

DML-операции INSERT, UPDATE и DELETE на данный момент поддерживаются только при использовании стандартного SQL.

Еще одно отличие между этими диалектами — способ вертикального объединения таблиц. В стандартном SQL для этого служит оператор UNION и ключевое слов ALL или DISTINCT:

SELECT 12 AS A, 32 AS B
UNION ALL
SELECT 2 AS A, 29 AS B

В собственном SQL-диалекте функционал для вертикального объединения таблиц значительно шире. Существует специальный набор функций подстановки таблиц (Table Wildcard Functions).

Этот способ объединения таблиц я уже подробно описывал ранее.

Для простого объединения достаточно просто перечислить названия нужных таблиц или подзапросы через запятую. Объединение запросов из примера выше на внутреннем диалекте SQL в BigQuery будет выглядеть так:

SELECT *
FROM (SELECT 12 AS A, 32 AS B), (SELECT 2 AS A, 29 AS B)

Переключатель между SQL-диалектами в BigQuery находится в интерфейсе в блоке опций: нажмите кнопку Show options под редактором запросов.

С помощью галочки «SQL Dialect» переключитесь на нужный диалект.

Инструменты для работы с BigQuery

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

Как получить данные в различных BI платформах, я писал в статьях об интеграции с электронными таблицами и Microsoft Power BI.

Microsoft Power BI, как и большинство популярных BI-систем и электронных таблиц, с июля 2017 года поддерживает интеграцию с Google BigQuery из коробки. У коннектора довольно скудные возможности: он не умеет обращаться к сохраненным представлениям или отправлять в BigQuery запросы. Пока что с помощью встроенного коннектора можно вытягивать только плоские таблицы.

Simba Drivers

Если вам необходимо получить данные из Google BigQuery в электронной таблице или BI-системе, которая из коробки не поддерживает интеграцию, воспользуйтесь бесплатным Simba Drivers.

Этот драйвер поддерживает все необходимые возможности, включая переключения SQL-диалектов. Подробности настройки ищите в моей статье о связке Microsoft Power BI и Google BigQuery.

Язык R

Язык R — один из самых мощных инструментов для работы с данными. Он умеет как получать данные из Google BigQuery, так и записывать их. Для этого удобнее всего пакет bigrquery.

Для начала установите язык R. Также для удобства работы с R я рекомендую установить интегрированную среду разработки RStudio.

Запустите RStudio и с помощью сочетания клавиш «Ctrl+Alt+Shift+0» откройте все доступные в ней панели. Чаще всего понадобятся панели Source и Console.

Для установки develop-версии пакета bigrquery из репозитория на GitHub предварительно установите пакет devtools. Введите в окно Source код, затем выделите его (зажмите левой кнопки мыши) и нажмите «Ctrl+Enter» для выполнения команды:

install.packages("devtools")

Теперь установите пакет bigrquery:

devtools::install_github("rstats-db/bigrquery")

Чтобы в R были доступны функции пакета, после установки подключите их с помощью команды library или require. Например, подключим пакет bigrquery с помощью кода:

library(bigrquery)

Структура данных в Google BigQuery состоит из проекта с набором данных, содержащим таблицы. Проект вы уже создали, а теперь для передачи информации создайте набор данных. Выберите в интерфейсе из выпадающего меню «Create new dataset».

Чтобы создать набор данных с помощью языка R, воспользуйтесь командой insert_dataset. Команда требует всего 2 аргумента:

project — ID проекта (возьмите из URL в BigQuery).

dataset — название нового набора данных.

Давайте создадим первый набор данных с названием myFirstDataSet. Введите в область Source приведенный ниже код, выделите команду с помощью мыши и нажмите «Ctrl+Enter» для выполнения.

insert_dataset(project = "myfirstproject-185308", dataset = "myFirstDataSet")

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

Введите на запрос Selection в Console ответ Yes и нажмите Enter. Откроется браузер — разрешите доступ к данным и получите авторизационный код.

Скопируйте сгенерированный код. Затем вставьте его в Console RStudio в ответ на запрос авторизационного кода и нажмите Enter.

Отлично, вы создали набор данных.

Теперь запишите встроенную в R таблицу mtcars в набор данных myFirstDataSet. Для передачи данных из R в BigQuery в пакете bigrquery есть функция insert_upload_job. Она принимает такие аргументы:

project — ID проекта (смотрите либо в URL проекта, либо в режиме переключения проектов).

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

table — название таблицы с записанными данными.

values — data frame (таблица данных) в R с данными для передачи в BigQuery.

billing = project аккаунта для оплаты операции. По умолчанию — платежный аккаунт, который привязан к проекту.

create_disposition — опция для определения необходимых действий.
Если в BigQuery нет таблицы с заданным в аргументе table названием, укажите «CREATE_IF_NEEDED» — система создаст новую таблицу.
Если указать «CREATE_NEVER» и таблица с заданным именем не найдется в наборе данных, будет возвращена соответствующая ошибка.

write_disposition — опция для выбора добавления данных в существующую таблицу.
«WRITE_APPEND» — дописать данные в таблицу.
«WRITE_TRUNCATE» — перезаписать данные в таблице.
«WRITE_EMPTY» — записать данные для пустой таблицы.

Код для передачи в BigQuery встроенной в R таблицы mtcars:

insert_upload_job(project = "myfirstproject-185308",
                 dataset = "myFirstDataSet",
                 table = "mtcars_bigquery",
                 values = mtcars,
                 create_disposition = "CREATE_IF_NEEDED",
                 write_disposition = "WRITE_APPEND")

При успешном выполнении операции в консоли R появится дополнительная информация, а в интерфейсе BigQuery — созданная таблица mtcars_bigquery.

Для запроса данных из BigQuery в R в пакете bigrquery предназначена функция query_exec. Основные аргументы:

query — текст SQL-запроса, результат которого вы хотите загрузить в R.

project — ID проекта для запроса данных.

page_size — максимальный размер возвращаемого результата в строках (по умолчанию 10 000).

max_pages — максимальное количество страниц возврата запросом (по умолчанию 10).

use_legacy_sql — выбор SQL-диалекта для обработки запроса.
По умолчанию задано значение TRUE с внутренним диалектом BigQuery.
Для стандартного диалекта SQL задайте в этом аргументе значение FALSE.

Для обратного запроса данных, которые вы отправили в BigQuery на прошлом шаге, задайте команду:

bq_mtcars_table <- query_exec(query = "SELECT * FROM myFirstDataSet.mtcars_bigquery",
                             project = "myfirstproject-185308",
                             use_legacy_sql = TRUE)

В рабочем окружении (описание которого вы видите в окне Environment) появится новый объект bq_mtcars_table.

Выводы

Google BigQuery — простой и в то же время мощнейший инструмент для хранения и обработки данных. Это облачная база данных с поддержкой большинства функций СУБД.

Сервис обходится значительно дешевле содержания, поддержки и администрирования сервера для бесплатных баз данных (MySQL или PostgreSQL).

Надеюсь, мой цикл инструкций для начала работы с Google BigQuery упростит ваши будни.

Успехов в работе с большими данными!

Как анализировать данные в Google BigQuery

Очень часто маркетологам не хватает обычного набора инструментов для обработки больших массивов информации. Когда Google Analytics, Яндекс.Метрики и Excel недостаточно, на помощь приходят сторонние системы управления базами данных (СУБД).

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

Итак, обо всём по порядку.

Начало работы в Google BigQuery

Google BigQuery – часть Google Cloud Platform, куда входит еще порядка 40 инструментов для вычисления, хранения и анализа данных. Поэтому начинаем с входа в платформу.

Первый шаг – соглашение с условиями пользования. Если вы не авторизованы в своем аккаунте Google, для начала нужно туда зайти.

После этого система предлагает активировать пробный период:

Не пугайтесь! 300 долларов в кредит – условная вещь. Система не станет списывать с вашего счета средства, пока вы не решите перейти на полную версию. А для того, чтобы исчерпать лимит $300, надо грузить информацию в промышленных масштабах, ибо расценки идут за объем данных. Через 12 месяцев предоставленная в кредит сумма сгорает. Дальше оплата по прайсу ($5 за 1Tb).

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

Нужно оставить свои контакты и сведения о банковской карте:

Дальше создаем проект под определенным названием и снова подтверждаем условия использования:

После оповещения о создании проекта, привязываем его к зарегистрированному ранее платежному аккаунту в платформе:

И наконец-то переходим в интерфейс Google BigQuery, где открываем редактор запросов.

Обратите внимание: рабочая среда сервиса на английском языке. Чтобы открыть редактор запросов, нажмите «Compose query» или сочетание «Ctrl + Space»:

Загрузка данных в Google BigQuery

Три самых простых способа импорта данных в Google BigQuery:

из CSV файла
из JSON файла
из Google Spreadsheets.

Ниже мы подробнее рассмотрим последний способ, с помощью Google Spreadsheets.

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

Данные из Google Таблиц можно загрузить двумя способами.

1) Из интерфейса Google BigQuery.

Для этого необходимо выбрать из выпадающего списка Location пункт «Google Drive» и указать полную ссылку на нужную Google Таблицу.

Если вы хотите указать ссылку на конкретный лист, введите полный URL, включая параметр gid. Далее задаём имя таблицы и описываем её схему.

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

2) Загрузка с помощью OWOX BI.

Для этого сначала устанавливаем приложение OWOX BI BigQuery Reports для браузера Google Chrome.

После установки оно появляется в вашем списке дополнений в Google таблицах. Выбираем его в меню и кликаем на «Upload data to BigQuery»:

В открывшимся окне «Set destination & schema» указываем проект, набор данных и название таблицы, в которую будут загружены данные:

Кликаем на «Start Upload» и получаем сообщение, что таблица успешно загружена. После этого нажимаем на «Show table in BigQuery» и переходим в интерфейс Google BigQuery. Там уже будет присутствовать только что загруженная таблица «load_from_spreadsheets»:

По алгоритму загрузки статистики из рекламных систем Яндекс.Директ, Google Ads, MyTarget, ВКонтакте и Facebook рекомендуем статью коллег из eLama:

Как загрузить статистику из рекламных систем в Google BigQuery

Среди прочих способов импорта данных в Google BigQuery – клиентские библиотеки API; стриминг данных из Google Analytics.

Замечательное достоинство Google BigQuery – здесь можно сводить данные из разных источников.

Снова обратимся к опыту eLama: они поставили задачу определить эффективность обучающего вебинара. А именно – сколько участников зарегистрировались в сервисе после обучения и сколько из них подключили аккаунт Google Ads.

Для этого аналитики скомбинировали информацию из трех источников:

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

Как мы уже упомянули, система позволяет:

Строить отчеты любой сложности

Например, составить список пользователей, совершивших определенные действия на сайте, но не дошедших до оплаты. Для того, чтобы передать его в отдел продаж – прозванивать. Актуальная фича для e-commerce и онлайн-сервисов.

Создавать списки ремаркетинга по определенным условиям

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

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

Ну а для того, чтобы упаковать итоговые отчеты в красивые графики, в облачной платформе Google есть интеграция с более 20 системами визуализации данных (вкладка Data Analytics and Visualization):

Резюме

Вместо заключения перечислим еще раз преимущества системы — почему стоит выбрать Google BigQuery.

1) Вы не привязаны к локальным системам. Вся информация хранится и обрабатывается в «облаке».

2) Скорость на значительных объемах данных.

3) Простота. В любой другой системе управления базами данных (СУБД) помимо знания SQL придется долго разбираться с тонкостями администрирования и настройками базы.

У BigQuery всю административную часть на себя взял Google. Здесь нет никаких настроек, индексов, движков таблиц, тайм-аутов или внешних ключей.

Достаточно знать, как загрузить данные в BigQuery, и иметь базовые знания в SQL.

4) Стоимость. Даже если вы очень постараетесь и будете ежедневно записывать в эту базу данных миллионы строк, все равно вряд ли сможете потратить более $5.

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

Акции
Продукт
Продукт
Отчеты BI
Лингвогенератор
Импорт лидов из соцсетей
Бид-менеджер
Автоматические рекомендации
Мониторинг конкурентов
в Facebook Ads
Генератор UTM‑меток
Все инструменты
Услуги
Услуги агентства
Помощь на старте
Тарифы
Агентствам и фрилансерам
Обучение
Вебинары
Курсы
Блог
Контакты

Вход

Акции
Продукт
Продукт
Отчеты BI
Лингвогенератор
Импорт лидов из соцсетей
Бид-менеджер
Автоматические рекомендации
Мониторинг конкурентов
в Facebook Ads
Генератор UTM‑меток
Все инструменты
Услуги
Услуги агентства
Помощь на старте
Тарифы
Агентствам и фрилансерам
Обучение
Вебинары
Курсы
Блог

Вход

Структура данных в Google BigQuery

Материалы для скачивания

Все доклады конференций: Analyze!, GoAnalytics!, Ecommerce

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

Содержание

Узнайте, какие кампании приносят прибыль, а какие не окупаются

Автоматически импортируйте расходы из рекламных сервисов в Google Analytics.
Сравнивайте затраты, CPC и ROAS разных кампаний в одном отчете.

Что такое набор данных и как его создать

Чтобы использовать Google BigQuery, вам нужно создать проект в Google Cloud Platform. При регистрации вы получите бесплатный пробный период, доступ ко всем продуктам Cloud Platform и $300, которые можно потратить на эти продукты в течение года.

После создания проекта в Google Cloud Platform (GCP) вам нужно добавить в Google BigQuery хотя бы один набор данных (dataset).

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

Откройте свой проект в GCP, перейдите на вкладку «BigQuery» и нажмите «Создать набор данных»:

В открывшемся окне укажите название для набора данных и срок хранения таблицы. Если вы хотите, чтобы таблицы с данными удалялись автоматически, укажите, через какое время. Или оставьте вариант «Бессрочно», чтобы таблицы никогда не удалялись автоматически, а только вручную.

Поле «Место обработки» заполнять необязательно. По умолчанию в нем указан мультирегион US. Подробнее о возможных регионах для хранения данных вы можете узнать в справке.

Как добавить таблицу для загрузки данных в Google BigQuery

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

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

Вы можете вручную создать пустую таблицу и задать для нее схему данных.
Можно создать таблицу, используя результат предварительно рассчитанного SQL-запроса.
Загрузить файл с вашего компьютера (в формате csv, avro, json, parquet, orc, google sheets).
Вместо загрузки или потоковой передачи данных можно создать таблицу, которая ссылается на внешний источник: Cloud Bigtable, Cloud Storage или Google Drive.

В этой статье мы детальнее рассмотрим первый способ — создание таблицы вручную.

Шаг 1. Выберите набор данных, в который хотите добавить таблицу, и нажмите «Создать таблицу»:

Шаг 2. В поле «Источник» выберите «Пустую таблицу», а в поле «Тип таблицы» — «Таблица в собственном формате целевого объекта». Придумайте название таблицы.

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

Шаг 3. Укажите схему таблицы. Схема таблицы состоит из четырех компонентов: двух обязательных (название столбца и тип данных) и двух опциональных (режим столбца и описание). Правильно подобранные типы и режимы полей в дальнейшем облегчат работу с данными.

Пример схемы в BigQuery:

Названия столбцов
В названии столбца нужно указать параметр, за который он отвечает, например, date, user_id или products. Названия могут содержать только буквы латинского алфавита, цифры и нижние подчеркивания. Максимум — 128 символов. Одинаковые имена полей не допускаются, даже если у них отличается регистр.

Тип данных
При создании таблицы в BigQuery вы можете использовать следующие типы данных:

Режимы
BigQuery поддерживает следующие режимы для столбцов в ваших таблицах:

Примечание: Заполнять поле «Режим» не обязательно. Если режим не указан, столбец по умолчанию принимает значение NULLABLE.

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

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

Нажмите кнопку «Добавить поле» и заполните название, тип и режим для каждого столбца:

Введите схему таблицы в виде массива JSON с помощью переключателя «Редактировать как текст»:

Кроме того, в Google BigQuery можно использовать автоматическое определение схемы при загрузке данных из файлов CSV и JSON формата:

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

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

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

Как внести изменения в схему таблицы

После загрузки данных в Google BigQuery схема таблицы может немного отличаться от исходной. Например, название поля изменилось из-за символа, который не поддерживается в BigQuery или указан тип поля INTEGER вместо STRING. В таком случае вы можете вручную подкорректировать схему.

Как изменить название столбца

С помощью SQL-запроса выберите все столбцы в таблице и укажите новое название для столбца, который необходимо переименовать. При этом вы можете перезаписать существующую таблицу или создать новую. Примеры запроса:

    
#legacySQL
Select
date,
order_id,
order___________ as order_type, -- новое название поля
product_id
from [project_name:dataset_name.owoxbi_sessions_20190314]

    
#standardSQL
Select
* EXCEPT (orotp, ddat),
orotp as order_id,
ddat as date
from `project_name.dataset_name.owoxbi_sessions_20190314`

Как изменить тип данных в схеме

С помощью SQL-запроса выберите все данные из таблицы и преобразуйте соответствующий столбец в другой тип данных. Вы можете использовать результаты запроса, чтобы перезаписать существующую таблицу или создать новую. Пример запроса:

    
#standardSQL
Select
CAST (order_id as STRING) as order_id,
CAST (date as TIMESTAMP) as date
from `project_name.dataset_name.owoxbi_sessions_20190314`

Как изменить режим столбца

Вы можете изменить режим столбца с REQUIRED на NULLABLE, как описано в справке. Второй вариант — это экспортировать данные в Cloud Storage, а оттуда вернуть их в BigQuery с правильным режимом для всех столбцов.

Как удалить столбец из схемы данных

Используйте SELECT * EXCEPT запрос, чтобы исключить столбец (или столбцы), а затем запишите результаты запроса в старую таблицу или создайте новую. Пример запроса:

    
#standardSQL
Select
* EXCEPT (order_id)
from `project_name.dataset_name.owoxbi_sessions_20190314`

Кроме того, есть второй способ изменить схему, который подходит для всех описанных выше задач — экспорт данных и загрузка их в новую таблицу. Чтобы переименовать столбец, вы можете выгрузить данные из BigQuery в Cloud Storage, а затем загрузить их в BigQuery в новую таблицу или перезаписать данные в старой, используя «Дополнительные параметры»:

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

Экспорт и импорт данных в Google BigQuery

Загружать и выгружать данные в BigQuery без помощи разработчиков можно через интерфейс или специальный аддон от OWOX BI. Рассмотрим каждый способ подробнее.

Импорт данных через интерфейс Google BigQuery

Чтобы загрузить в хранилище необходимую информацию, например, данные о пользователях и офлайн-заказах, откройте ваш набор данных, нажмите «Создать таблицу» и выберите источник данных: Cloud Storage, ваш компьютер, Google Drive или Cloud Bigtable. Укажите путь к файлу, его формат и название таблицы, куда будут загружаться данные:

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

Экспорт данных через интерфейс Google BigQuery

Выгрузить обработанные данные из BigQuery, например, для создания отчета, также можно через интерфейс системы. Для этого откройте нужную таблицу с данными и нажмите кнопку «Экспорт»:

Система предложит два варианта: посмотреть данные в Google Data Studio или выгрузить их в Google Cloud Storage. Выбрав первый вариант, вы тут же попадете в Data Studio, где вам останется сохранить отчет.

При выборе экспорта в GCS откроется новое окно. В нем вам нужно указать, куда сохранить данные и в каком формате:

Экспорт и импорт данных с помощью аддона от OWOX BI

Бесплатный OWOX BI BigQuery Reports Add-on позволяет быстро и удобно передавать данные напрямую из Google BigQuery в Google Sheets и наоборот. Благодаря этому, вам не нужно готовить CSV-файлы или использовать сторонние платные сервисы.

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

Установите аддон в ваш браузер.
Откройте ваш файл с данными в Google Sheets и на вкладке «Дополнения» выберите «OWOX BI BigQuery Reports → Upload data to BigQuery»:

В открывшемся окне выберите свой проект и набор данных в BigQuery, введите желаемое название для таблицы. Также выберите поля, значения которых вы хотите загрузить. По умолчанию типом всех полей является «STRING», но мы рекомендуем выбирать тип данных в соответствии с контекстом (например, для полей числовых идентификаторов — «INTEGER», для цен — «FLOAT»):

Нажмите кнопку «Start Upload» и ваши данные загрузятся в Google BigQuery.

Также вы можете использовать этот аддон для экспорта данных из BigQuery в Google Sheets. Например, чтобы визуализировать данные или поделиться ими с коллегами, у которых нет доступов к BigQuery. Для этого:

Откройте Google Sheets и на вкладке «Дополнения» выберите «OWOX BI BigQuery Reports → Add a new report»:

Затем укажите свой проект в Google BigQuery и выберите «Add new query».
В новом окне вставьте свой SQL-запрос. Это может быть запрос, который выгружает данные из таблицы в BigQuery, или запрос, который вытягивает и рассчитывает необходимые данные.
Переименуйте запрос, чтобы его было легко найти, и запустите, нажав кнопку «Save & Run».

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

На вкладке «Дополнения» выберите «OWOX BI BigQuery Reports → Schedule report»:

В открывшемся окне задайте время и частоту обновления отчета и нажмите «Save»:

Зачем собирать данные в Google BigQuery

Если вы еще не оценили все преимущества облачного хранилища Google BigQuery, рекомендуем попробовать. С помощью OWOX BI вы можете объединить в BigQuery ваши данные с сайта, из рекламных источников и внутренних CRM-систем (например Salesforce), чтобы:

У OWOX BI есть бесплатный trial-период, во время которого вы можете попробовать все возможности сервиса

ПОПРОБОВАТЬ OWOX BI

Использованные инструменты

Google BigQuery для аналитики в SEO и интернет-маркетинге. Как и для каких целей применяется?

Сервисы Google Analytics и Yandex.Метрика решают не все задачи по анализу данных при продвижении проектов в интернете.

На многих успешных проектах используются системы тотальной аналитики.

Как и для каких целей применяются такие системы?

Рассмотрим примеры задач по SEO и интернет-маркетингу, которые решаются с использованием таких систем.

На многих проектах аналитика не используется ввиду отсутствия видения по реализации на практике.

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

Итак, разберемся с темой аналитики далее.

Системы для аналитики данных

Использование популярных систем например таких, как Google Analytics и Яндекс.Метрика является лишь начальным этапом в анализе данных. Данные сервисы позволяют решать такие задачи как:

Анализ конверсий;
Сегментирование;
Создание выводов об эффективности внесенных изменений на сайте и прочее.

Применение систем для глубокой аналитики является следующим этапом в анализе данных.

Почему подобные системы аналитики востребованы на рынке и почему использование привычных сервисов является недостаточным? Сервисы Google Analytics и Yandex.Метрика предназначены для анализа данных на основе просмотров страниц и регистраций событий в рамках сайта. Как результат, такие системы покрывают не все задачи.

Недостатки в следующем:

Нет механизма для импорта и учета данных из иных источников. Например, нельзя учитывать активность в оффлайн каналах, таких как мероприятия, звонки и так далее. Нет решений для интеграции с данными из SEO сервисов и так далее;
Анализ проводится на базе сэмплированных данных.

Любая аналитика применяется в цикле:

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

Какие вопросы решаются с применением таких решений? К примеру, купит пользователь премиальный тариф без скидки или нет. В любых вариантах, такие системы используются как инструмент для максимизации прибыли.

Рассмотрим самые простые примеры задач.

Практические примеры

Пример 1. В сервис добавлена новая функциональность. Задача заключается в проверке гипотезы на предмет, увеличивает ли добавленная функциональность метрику lifetime value.

Как проверить данные? Весь процесс заключается в следующем:

Выгрузка из Analytics сегмента пользователей, использующих инструмент;
Выгрузка по User ID данных о платежах из внутренней системы;
Анализ динамики до и после внедрения функциональности.

Если гипотеза подтвердилась, то затем в целях увеличения прибыли следует сделать следующие:

Выгрузить из Analytics сегмент пользователей, которые не использовали добавленную функциональность;
Выгрузить по User ID данные о e-mail из внутренней системы;
Сделать рассылку.

В Google Analytics хранить контактные данные запрещается правилами использования сервиса. В Google BigQuery нет проблем с хранением контактных данных.

Пример 2. Был проведен онлайн вебинар. Задача в создании ремаркетинга на аудиторию вебинара, которая не конвертировались в пользователей или покупателей.

Как решить данную задачу? Решение в следующем:

Настраиваем событие на регистрацию на мероприятие;
Включаем параллельную загрузку данных User ID в BigQuery. Напрямую такие данные получить из Google Analytics нельзя;
Выбираем требуемый сегмент и импортируем список в систему ремаркетинга.

Пример 3. На сайте агрегатора есть 5 миллионов страниц. Требуется найти страницы которые не приносят пользу.

Как решить данную задачу? Решение в следующем:

Выгрузка поисковой видимости страниц посредством использования API из базы MegaIndex или иных источников;
Выгрузка частотности ключевых фраз из базы MegaIndex или иных источников;
Выгрузка значений о трафике на страницах из Google Analytics;
Импорт данных в BigQuery;
Анализ данных.

Итак, страницы разделены на эффективные и прочие. Далее следует внести правки на сайт. Например, удалить страницы без трафика или разместить на таких страницах 301 редирект.

Рекомендованный материал в блоге MegaIndex по ссылке далее — Как удалить страницы из индекса поисковых систем? Какие страницы нужно удалить из выдачи? Зачем?

Пример 4. Сайт создан под партнерские сети и на сайте есть большой объем трафика.

Как решить задачу по увеличению прибыли? Весь процесс заключается в следующем:

Создание слепка из контента на странице;
Импорт из партнерских сетей данных о результатах по конверсиям;
Анализ данных.

Такая простая аналитика дает понимание об наиболее эффективных вариантах контента и способна увеличить прибыль на десятки процентов.

На больших сайтах и SaaS сервисах прибыль составляет сотни тысяч долларов в месяц. Увеличение прибыли на проценты являются значимыми.

В Google Analytics и Yandex.Метрике нет способа проводить подобную аналитику.

Пример 5. Например, проект размещается на YouTube. Сайта нет.

Данный проект рекламируется в Google, YouTube и Facebook. В данном случае имеет смысл выгрузить данные из рекламных площадок в Google BigQuery, и провести анализ эффективности рекламных кампаний в сравнении.

В результате следует сделать выводы, и распределить затраты в пользу наиболее эффективных каналов продвижения и роликов.

Еще сферы применения:

Как инструмент для быстрой обработки логов в облаке;
Как инструмент для быстрой обработки данных из SEO сервисов, таких как Screeming Frog;
Работа с отчетами по внешним ссылкам.

BigQuery в SEO еще используется как инструмент для хранения и анализа данных, которые удаляются из сервисов поисковых систем. Например, Google Search Console и подобные сервисы сохраняют данные на небольшой срок, а после данные удаляются.

Как реализовать подобную аналитику на практике?

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

Оптимальной базой для реализации таких решений являются облачные сервисы Amazon Redshift и Google BigQuery.

Рассмотрим решение Google BigQuery. Преимущества:

Низкий порог входа;
Низкая стоимость поддержки решения;
Быстрая обработка;
Импорт данных из разных API;
Простота в использовании.

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

Различные скрипты по интеграции упрощают импорт данных. Такие скрипты есть для разных источников данных:

Рекламные отчеты из Yandex.Директ;
Рекламные отчеты из Facebook;
Данные из менеджера задач;
Данные из приложений для мобильных устройств;
Данные из мероприятий в оффлайне.

Доступен импорт из таких файлов:
Для Google BigQuery есть готовые решения по интеграции с API различных сервисов. Например:

Google Analytics API;
Google Search Console API.

Нюансы по Google Analytics:

Данные по протоколу Measurement Protocol следует передавать в реальном времени, иначе будет потеряна привязка пользователя к сессии;
При выгрузке User ID следует выделить отдельную пользовательскую переменную.

Как добавлять данные? Два способа:

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

Сервис BigQuery работает со структурированными данными.

На этапе загрузки данных в Google BigQuery следует использовать систему для мониторинга.

Что такое мониторинг? Мониторингом является система, которая отслеживать корректность отправляем данных в Google BigQuery. Например, система Google Stackdriver. В системе есть встроенный коннектор к BigQuery. Сервис бесплатный.

Пример данных, по которым проводится мониторинг:

Количество запросов;
Расход денег. Есть опция задания порога на затраты;
Количество прочитанных данных.

Далее данные следует сформировать в отчет и проанализировать.

Формируются отчеты так:

Через использование скрипта по выгрузке данных на сайт;
OWOX Query Editor позволяет выгрузить в Google Таблицы. Сервис является бесплатным;
Google App Script. Есть интеграция с Big Query и возможность сформировать любые отчеты для открытия в Google Таблицах. Есть возможность настроить запускать скрипты по расписанию.

Как визуализировать данные? К примеру, использовать BigQuery BI Engine. Данное решение не требует знаний на тему языка запросов к базе данных. Инструмент позволяет соединить Google BigQuery c Google Data Studio.

Google Data Studio является сервисом, который позволяет создавать различные графики, формировать отчеты. Сервис бесплатный.

Цена низкая. Например, цена за использование сервиса BigQuery в целях аналитики сайта агрегатора на предмет внутренней поисковой оптимизации составила менее $10.

Итак, почему Google BigQuery является оптимальным вариантом?

Главные плюсы Google BigQuery следующие:

Скорость обработки near-real-time;
Распараллеливание процессов на разные сервера;
Низкая цена.

Данный сервис является простым и дешевым в применении на практике.

Значит данный инструмент позволяет сократить риски от создания дорогой инфраструктуры для аналитики.

Вопросы и ответы

По каким причинам не подходит Google Analytics 360?

Google Analytics 360 расширяет возможности Google Analytics.

Ряд проблем решается. Но есть и значимые недостатки.

Условия использования сервиса Google Analytics 360 запрещают сбор контактных данных.

Еще в сервисе Google Analytics 360 есть такие ограничения:

200 тысяч хитов на пользователя в сутки;
500 миллионов хитов в месяц;
500 хитов на сессию;
200 показателей;
200 пользовательских параметров.

Далее, если пользователь перешел на сайт два раза в рамках одной и той же рекламной кампании, то Google Analytics 360 определит второй переход как direct трафик.

Главные недостатки сервиса такие:

Цена подписки составляет десятки тысяч долларов за год;
Решаются не все проблемы по импорту данных.

Альтернативным вариантом мог бы быть Excel.

По каким причинам не подходит Excel или Google Таблицы?

Excel никак не подходит для анализа большого объема данных. Недостатки продукта следующие:

Применимы не для всех задач;
При обработке серьезных объемов данных обработка занимает много времени и требует наличие системных ресурсов;
В Google Таблицах есть ограничение на 2 миллиона ячеек.
В Excel есть ограничения на объем столбцов и строк;
Нет автоматического обновления данных;
Данные нельзя получить в моменте.

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

Выделенный сервер и система под аналитику?

Данный вариант интересный. Но разработка системы является дорогим вариантом.

Есть готовые решения, такие например как Vertica и Yandex ClickHouse.

Преимущества:

Безопасность данных;
Низкая цена на больших объемах.

Недостатки:

Инвестиции в инфраструктуру;
Затраты на настройку;
Не просто масштабировать.

Выводы

Бесплатные системы для аналитики решают не все задачи. Например, какие минусы есть у Google Analytics? Главное недостатки:

Семплирование;
Проблемы с импортом данных. Например, запрет на хранения контактных данных. Нет учета отменных заказов, нет учета заказов по телефону и из оффлайн источников;
Неудобно обрабатывать большие объемы данных.

Все подобные сервисы ограничены структурой и интерфейсом.

Список всех требований к сервису по аналитике данных следующий:

Импорт данных по API из различных источников;
Легкий и гибкий в применении. Значит, с агрегацией всех данных для анализа в единой структуре и с применением SQL запросов для гибкости;
Быстрый при обработке большого объема данных;

Зачастую цена разработки таких систем составляет сотни тысяч долларов и больше. Но есть способ, который дешевый в плане инвестиций и потребляемых ресурсов. Использование данного способа позволяет решить значимую часть всех задач по аналитике. Данный способ следует использовать для обработки больших объемов данных. Способ заключается в использовании распределенной серверной инфраструктуры Google BigQuery как базы данных и среды для вычислений.

Если данных по посещаемости страниц сайта достаточно, то имеет смысл использовать простые и бесплатные системы. Например такие, как MegaIndex. Данный сервис не передает данные в поисковые системы.

Ссылка на сервис — MegaIndex Stat.

Сервис бесплатный.

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

Как начать работать с Google BigQuery — Alexey Seleznev blog

Google BigQuery — это облачная база данных с высочайшей скоростью обработки огромных массивов данных.

Как начать работу в Google BigQuery

Для дальнейшей работы введите платежные данные.

Нажмите «Выбрать проект».

Затем — «Создать проект».

Примите условия использования платформы.

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

После подтверждения подождите несколько минут.

Вскоре вы получите оповещение, что проект создан.

Перейдите в раздел оплаты и привяжите платежный аккаунт.

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

Перейдите в интерфейс Google BigQuery и напишите свой первый запрос.

Чтобы открыть редактор запросов, нажмите «Compose query» или сочетание клавиш «Ctrl + Space».

SELECT *
FROM [lookerdata:cdc.project_tycho_reports]
LIMIT 25

Запрос вернет результат:

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

Преимущества Google BigQuery

Для работы с BigQuery достаточно знать, как загрузить данные в BigQuery, и иметь базовые знания в SQL.

Несмотря на простоту, в BigQuery реализована поддержка практически всех функций СУБД:

Правда, на момент публикации статьи сервис не поддерживает:

рекурсивные запросы;
создание хранимых процедур и функций;
транзакции.

Особенности SQL для Google BigQuery

BigQuery умеет переключаться между стандартным SQL и своим диалектами. Между ними также есть разница.

DML-операции INSERT, UPDATE и DELETE на данный момент поддерживаются только при использовании стандартного SQL.

SELECT 12 AS A, 32 AS B
UNION ALL
SELECT 2 AS A, 29 AS B

Как обрабатывать данные из множества таблиц в Google BigQuery

Этот способ объединения таблиц я уже подробно описывал ранее.

SELECT *
FROM (SELECT 12 AS A, 32 AS B), (SELECT 2 AS A, 29 AS B)

С помощью галочки «SQL Dialect» переключитесь на нужный диалект.

Инструменты для работы с BigQuery

Simba Drivers

Язык R

Запустите RStudio и с помощью сочетания клавиш «Ctrl + Alt + Shift + 0» откройте все доступные в ней панели. Чаще всего понадобятся панели Source и Console.

Для установки develop-версии пакета bigrquery из репозитория на GitHub предварительно установите пакет devtools. Введите в окно Source код, затем выделите его (зажмите левой кнопки мыши) и нажмите «Ctrl + Enter» для выполнения команды:

install.packages("devtools")

Теперь установите пакет bigrquery:

devtools::install_github("rstats-db/bigrquery")

library(bigrquery)

project — ID проекта (возьмите из URL в BigQuery).

dataset — название нового набора данных.

insert_dataset(project = "myfirstproject-185308", dataset = "myFirstDataSet")

Отлично, вы создали набор данных.

project — ID проекта (смотрите либо в URL проекта, либо в режиме переключения проектов).

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

table — название таблицы с записанными данными.

values — data frame (таблица данных) в R с данными для передачи в BigQuery.

billing = project аккаунта для оплаты операции. По умолчанию — платежный аккаунт, который привязан к проекту.

Код для передачи в BigQuery встроенной в R таблицы mtcars:

insert_upload_job(project = "myfirstproject-185308",
                 dataset = "myFirstDataSet",
                 table = "mtcars_bigquery",
                 values = mtcars,
                 create_disposition = "CREATE_IF_NEEDED",
                 write_disposition = "WRITE_APPEND")

Для запроса данных из BigQuery в R в пакете bigrquery предназначена функция query_exec. Основные аргументы:

query — текст SQL-запроса, результат которого вы хотите загрузить в R.

project — ID проекта для запроса данных.

page_size — максимальный размер возвращаемого результата в строках (по умолчанию 10000).

max_pages — максимальное количество страниц возврата запросом (по умолчанию 10).

Для обратного запроса данных, которые вы отправили в BigQuery в прошлом шаге, задайте команду:

bq_mtcars_table <- query_exec(query = "SELECT * FROM myFirstDataSet.mtcars_bigquery",
                             project = "myfirstproject-185308",
                             use_legacy_sql = TRUE)

В рабочем окружении (описание которого вы видите в окне Environment) появится новый объект bq_mtcars_table.

Выводы

Google BigQuery — простой в работе и в то же время мощнейший инструмент для хранения и обработки данных. Это облачная база данных с поддержкой большинства функций СУБД.

Надеюсь, мой цикл инструкций для начала работы с Google BigQuery упростит ваши будни.

Успехов в работе с большими данными!

Оригинал публикации в блоге Netpeak.

Поделиться ссылкой:

Понравилось это:

Нравится Загрузка…

Похожее

Что такое BigQuery и почему это такая горячая тема? — InfoTrust

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

Чтобы лучше понять, как платформа Google BigQuery может помочь вашей компании, давайте сначала начнем с основ — что такое BigQuery на самом деле?

Самое простое определение исходит от самого Google: « BigQuery — это платформа бессерверного облачного хранилища Google, предназначенная для больших наборов данных .

Теперь давайте распакуем это, чтобы внести некоторую ясность. «Бессерверный» означает более дешевое хранение данных и более быстрое их масштабирование. BigQuery может обрабатывать лотов данных очень быстро и с низкими затратами. Платформа предназначена для того, чтобы помочь вам собрать все ваши данные в одном месте для более быстрого анализа и получения более быстрых результатов.

Другие полезные преимущества BigQuery включают:

Встроенные интеграции, которые делают создание озера данных в BigQuery простым, быстрым и экономичным.
Централизация данных для автоматической интеграции с инструментами машинного обучения Google Cloud для расширенных отчетов по науке о данных.
Интеграция с Data Studio в один клик означает, что визуализация обработанных таблиц выполняется просто и быстро.
Решения ETL, такие как DataFlow и DataProc, которые сокращают накладные расходы на преобразование данных.

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

Интеграция с Google Таблицами и Аналитикой 360

Из любого запроса вы можете выбрать извлечение непосредственно в Google Таблицы, что позволит вам исследовать свои данные в любое время одним нажатием кнопки.Если ваша команда хочет познакомиться с данными, но не имеет навыков SQL для написания сложных запросов, они могут просто экспортировать любую таблицу в Таблицы и приступить к изучению. Фактически, Analytics 360 можно интегрировать с BigQuery одним щелчком мыши, что позволяет выполнять анализ на уровне обращений и многое другое. Отображение необработанных обращений в BigQuery позволяет создавать отчеты с расширенной аналитикой, и это на одну платформу меньше, которую вы должны добавить в свое озеро данных (потому что Google сделает это за вас!).

Озера данных стали проще

Ознакомьтесь с кратким обзором озера данных!

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

Наука о данных и машинное обучение

Хранение озера данных в BigQuery обеспечивает автоматическую интеграцию с набором инструментов искусственного интеллекта и машинного обучения Google Cloud Console. Это позволяет выполнять расширенный анализ данных, например, пожизненной ценности клиента. Вы можете использовать эти продукты со своими данными, чтобы начать прогнозировать такие точки данных, как будущий доход или сочетание продуктов. Google Cloud Console позволяет вам получать доступ к данным BigQuery в Compute Engine и других серверных решениях, где вы можете запускать процессы в облаке, не платя за дорогие машины.Хранение данных в той же системе, что и облачные вычисления, не дает вам потреблять весь сетевой трафик при передаче больших наборов данных, что делает большие данные быстрыми и дешевыми.

Простая и «сексуальная» панель инструментов с Data Studio

BigQuery делает визуализацию данных одним из приоритетов. Фактически настолько, что они помещают кнопку прямо на ваши таблицы, которая ведет прямо в Студию данных, где вы можете начать агрегирование и визуализацию точек данных за секунды.Нет необходимости настраивать конфигурации базы данных или иметь дело с VPN. Google взял на себя все этапы между хранением данных и визуализацией данных, чтобы ускорить визуализацию. Data Studio не только доступна из любой таблицы BigQuery, но также позволяет настраивать отчеты в соответствии с вашим брендом, делая их одновременно «привлекательными» и ценными.

[ Подробнее: 5 вопросов, которые следует задать перед покупкой этой новой «сексуальной» маркетинговой панели]

DataFlow и DataProc упрощают обработку таблиц

Если ваши данные необходимо обработать, прежде чем их можно будет сохранить, вам необходимо решение ETL для автоматической обработки ваших данных.ETL (извлечение, преобразование, загрузка) — это система, которая будет читать, обрабатывать и загружать ваши данные в любой источник. DataFlow и DataProc в Google Cloud Console — это два решения ETL, которые подключают пользователей к BigQuery. DataFlows Apache Beam и DataProcs Hadoop помогают распределить обработку потоковой передачи и сохраненных данных, чтобы вы могли настроить каналы данных и никогда больше не касаться их. Независимо от того, насколько сложны данные, решения Google ETL помогут вам обработать их в BigQuery, чтобы упростить хранение данных.

Подробнее о BigQuery

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

Что это такое и как оно вписывается в облако Google?

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

Что такое Google BigQuery?

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

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

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

Где BigQuery вписывается в облако Google?

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

Мощный пакет визуализации, который позволяет пользователям работать с Cloud Datalab и BigQuery, чтобы сделать данные более удобоваримыми.
Настройка интеграции, обеспечивающая полную совместимость с платформой больших данных apache с использованием BigQuery наряду с Cloud Dataproc и Cloud Dataflow.
Пограничная среда машинного обучения в облаке
. Возможность беспрепятственного извлечения данных из файлов в Cloud Bigtable, Cloud Storage или Google Drive для анализа информации, хранящейся в различных системах.
Пакет бизнес-аналитики, который работает в Data Studio и позволяет пользователям легко создавать визуализации и отчеты, делая данные более удобными для использования на панели управления BigQuery.

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

«BigQuery может выполнять анализ данных почти в реальном времени».

Учитывая объем BigQuery

Визуальный анализ и анализ изображений становятся ключевыми требованиями для платформ больших данных сегодня, особенно в свете роста автономных транспортных средств, робототехники и связанных с ними технологий, которые их поддерживают.BigQuery является естественным подходом для таких вариантов использования, и проект Метрополитен-музея в Нью-Йорке подчеркивает эту возможность. Met загрузил всю свою лицензию Creative Commons Zero в Google Cloud Vision API, позволяя использовать эти изображения для визуальной аналитики на платформе. Развертывание BigQuery позволяет пользователям анализировать метаданные, относящиеся к работам в Метрополитене, и использовать Data Studio для создания визуализаций. Кроме того, Cloud Vision API может выполнять процессы JSON для отслеживания того, что находится в разных изображениях, и создавать надежный каталог коллекции в таблице BigQuery.

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

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

1. Что такое Google BigQuery?

В конце 2010 года директор сайта офиса Google в Сиэтле отозвал несколько инженеров (один из которых является автором этой книги) из своих проектов и дал им задание: создать рынок данных. Мы постарались найти лучший способ создать жизнеспособный рынок. Основной проблемой были размеры данных, потому что мы не хотели предоставлять только ссылку для скачивания. Рынок данных невозможен, если людям нужно загружать терабайты данных для работы с ними.Как бы вы создали рынок данных, который не требовал бы, чтобы пользователи начинали с загрузки наборов данных на свои машины?

Введите принцип, популяризированный Джимом Греем, пионером баз данных. Когда у вас есть «большие данные», — сказал Грей, — «вы хотите переместить вычисления в данные, а не перемещать данные в вычисления». Грей уточняет:

Другая ключевая проблема заключается в том, что по мере того, как наборы данных становятся больше, больше невозможно использовать только FTP или grep. Петабайт данных — это очень сложно для FTP! Итак, в какой-то момент вам понадобятся индексы и параллельный доступ к данным, и здесь вам могут помочь базы данных.Для анализа данных одна возможность — переместить данные к вам, а другая возможность — переместить ваш запрос к данным. Вы можете переместить свои вопросы или данные. Часто оказывается более эффективным переместить вопросы, чем перемещать данные. ¹²

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

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

На каком языке пользователи должны писать свои вычисления при переносе вычислений в данные в облаке? Мы выбрали SQL из-за трех ключевых характеристик. Во-первых, SQL — это универсальный язык, который позволяет большому кругу людей, а не только разработчикам, задавать вопросы и решать проблемы со своими данными.Эта простота использования была для нас чрезвычайно важна. Во-вторых, SQL является «относительно полным», что означает, что любые вычисления над данными могут выполняться с использованием SQL. SQL — это не просто просто и доступно. Это также очень мощно. Наконец, что очень важно для выбора языка облачных вычислений, SQL не является «полным по Тьюрингу» в ключевом смысле: он всегда завершается. ¹⁴ Поскольку он всегда завершается, можно размещать вычисления SQL, не беспокоясь о том, что кто-то напишет бесконечный цикл и монополизирует всю вычислительную мощность в центре обработки данных.

Затем нам нужно было выбрать механизм SQL. У Google был ряд внутренних механизмов SQL, которые могли оперировать данными, в том числе очень популярные. Самый продвинутый двигатель назывался Дремель; он активно использовался в Google и мог обрабатывать терабайты журналов за секунды. Dremel быстро убедил людей создавать собственные конвейеры MapReduce, чтобы они задавали вопросы об их данных.

Dremel был создан в 2006 году инженером Андреем Губаревым, который устал ждать завершения работы MapReduces.Столбцовое хранилище стало популярным в академической литературе, и он быстро придумал формат хранения столбцов (рис. 1-4), который мог обрабатывать буферы протокола (Protobufs), которые повсеместно распространены в Google.

Рисунок 1-4. Хранилища столбцов могут уменьшить объем данных, считываемых запросами, которые обрабатывают все строки, но не все столбцы

Хотя хранилища столбцов в целом отлично подходят для аналитики, они особенно полезны для анализа журналов в Google, потому что многие команды работают с типом Protobuf, который имеет сотни тысяч столбцов.Если бы Андрей использовал типичное хранилище, ориентированное на записи, пользователям пришлось бы читать файлы строка за строкой, таким образом, считывая огромное количество данных в виде полей, которые они все равно собирались отбросить. Сохраняя данные столбец за столбцом, Андрей сделал так, что если пользователю нужно всего несколько из тысяч полей в журнале Protobufs, ему нужно будет прочитать только небольшую часть общего размера данных. Это была одна из причин, по которой Dremel смог обработать терабайты журналов за секунды.

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

К 2010 году Google сканировал петабайты данных в день с помощью Dremel, и многие люди в компании использовали его в той или иной форме. Это был идеальный инструмент для нашей молодой команды по маркетингу данных, которую можно было подобрать и использовать.

Когда команда разработала Dremel, добавила систему хранения, сделала ее самонастраивающейся и представила ее внешним пользователям, команда поняла, что облачная версия Dremel, возможно, даже более интересна, чем их первоначальная миссия. Команда переименовала себя в BigQuery в соответствии с соглашением об именах для Bigtable, базы данных Google NoSQL.

В Google Dremel используется для запроса файлов в Colossus, файловом хранилище Google для хранения данных. BigQuery добавил систему хранения, которая предоставляет абстракцию таблицы, а не только абстракцию файла.Эта система хранения сыграла ключевую роль в том, чтобы сделать BigQuery простым в использовании и всегда быстрым, поскольку она позволяла выполнять ключевые функции, такие как транзакции ACID (атомарность, согласованность, изоляция, долговечность) и автоматическая оптимизация, и это означало, что пользователям не нужно было управлять файлами.

Изначально BigQuery сохранил свои корни Dremel и сосредоточился на сканировании журналов. Однако по мере того, как все больше клиентов хотели создавать хранилища данных и более сложные запросы, BigQuery добавил улучшенную поддержку объединений и расширенные функции SQL, такие как аналитические функции.В 2016 году Google запустил поддержку стандартного SQL в BigQuery, что позволило пользователям выполнять запросы с использованием совместимого со стандартами SQL вместо неудобного начального диалекта «DremelSQL».

BigQuery не задумывался как хранилище данных, но со временем превратился в него. В этой эволюции есть как хорошие, так и плохие стороны. С другой стороны, BigQuery был разработан для решения проблем, с которыми люди сталкиваются с данными, даже если они плохо вписываются в модели хранилищ данных. Таким образом, BigQuery — это больше, чем просто хранилище данных.Однако с другой стороны, до недавнего времени отсутствовали некоторые функции хранилища данных, которые люди ожидают, например язык определения данных (DDL; например, операторы CREATE) и язык обработки данных (DML; например, операторы INSERT). Тем не менее, BigQuery сосредоточился на двойном пути: во-первых, добавлении дифференцированных функций, которые Google может предоставить; и, во-вторых, стать отличным хранилищем данных в облаке.

Когда использовать Google BigQuery?

(Обновление) Недавно мы загрузили новые учебные материалы по большим данным с использованием сервисов Amazon Web Services, Microsoft Azure и Google Cloud Platform в учебной библиотеке Cloud Academy. Кроме того, мы были заняты добавлением нового контента в блог Cloud Academy о том, как лучше всего обучить себя и свою команду работе с большими данными.

Большие данные становится неотъемлемой частью бизнес-аналитики предприятия. Hadoop — это платформа и технология, лежащая в основе больших данных.Он предлагает различные инструменты для приема, обработки и анализа больших наборов данных, размер которых обычно составляет несколько терабайт. Хотя Hadoop стал зрелым, он все еще рассматривается для пакетной обработки. Запрашивать и анализировать данные в реальном времени с помощью Hadoop сложно и дорого. В основе Hadoop лежит платформа MapReduce, которая не подходит для интерактивных запросов. В недавнем прошлом такие технологии, как Impala от Cloudera и Apache Spark, начали дополнять MapReduce для работы с данными в реальном времени.

Хотя именно Google внес большой вклад в парадигму MapReduce, он также одним из первых выявил недостатки MapReduce. Инженеры Google поняли, что MapReduce не идеален для запроса больших распределенных наборов данных в реальном времени. Чтобы решить эту проблему, Google представил внутренний инструмент под названием Dremel, который позволил инженерам выполнять SQL-запросы к большим наборам данных в реальном времени. Dremel был разработан для обеспечения невероятно высокой производительности запросов к распределенным наборам данных, которые хранятся на тысячах серверов.Он поддерживает подмножество SQL для запроса и извлечения данных.
На Google I / O 2012 Google анонсировал BigQuery, открывший Dremel внешнему миру как облачный сервис. С тех пор BigQuery превратился в высокопроизводительный и масштабируемый механизм запросов в облаке.

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

Когда использовать Google BigQuery?

Итак, когда вы используете BigQuery? Это замена традиционной СУБД? Это служба OLAP? Это замена Apache Hadoop?

BigQuery обычно находится в конце конвейера больших данных.Это не замена существующим технологиям, но очень хорошо их дополняет. После обработки данных с помощью Apache Hadoop полученный набор данных может быть загружен в BigQuery для анализа. Потоки в реальном времени, представляющие данные датчиков, журналы веб-сервера или графики социальных сетей, могут быть загружены в BigQuery для запроса в реальном времени. После выполнения заданий ETL в традиционной СУБД результирующий набор данных можно сохранить в BigQuery. Данные могут быть получены из наборов данных, хранящихся в Google Cloud Storage, путем прямого импорта файлов или потоковой передачи данных.Итак, если Apache Hadoop — это средство для больших данных, BigQuery — это конец.

После недавнего анонса Google Cloud Pub / Sub и Google Cloud Dataflow BigQuery будет играть важную роль в облачной стратегии Google. В следующих статьях мы рассмотрим, как можно объединить Cloud Dataflow и BigQuery для эффективного запроса потоков данных в реальном времени.

Автор

Джанакирам MSV

Джанакирам MSV возглавляет службы облачной инфраструктуры в Aditi Technologies.Он пишет статьи по облачным технологиям на YourStory.com. Джанакирам, бывший сотрудник Microsoft и Amazon, построил консалтинговую компанию по облачным технологиям, которую недавно приобрела Aditi Technologies. Он является аналитиком Gigaom Research и занимается исследованиями и анализом рынка облачных технологий. С ним можно связаться по адресу [email protected].

Декабрь 2024
Пн	Вт	Ср	Чт	Пт	Сб	Вс
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

Bigquery что это: Обзор основных функций Google BigQuery и примеры запросов для маркетинг-анализа / Хабр

Обзор основных функций Google BigQuery и примеры запросов для маркетинг-анализа / Хабр

Что такое SQL и какие у него диалекты

С чего начать

Функции Google BigQuery

Функции агрегирования данных (Aggregate function)

Функции для работы с датами (Date function)

Функции для работы со строками (String function)

Функции для работы с подмножествами данных или оконные функции (Window function)

Выводы

Google BigQuery — зачем нужна облачная база данных

Как начать работу в Google BigQuery

Почему стоит выбрать именно Google BigQuery

Особенности SQL для Google BigQuery

Инструменты для работы с BigQuery

Simba Drivers

Язык R

Выводы

Как анализировать данные в Google BigQuery

Начало работы в Google BigQuery

Загрузка данных в Google BigQuery

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

Резюме

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

Структура данных в Google BigQuery

Содержание

Что такое набор данных и как его создать

Как добавить таблицу для загрузки данных в Google BigQuery

Как внести изменения в схему таблицы

Как изменить название столбца

Как изменить тип данных в схеме

Как изменить режим столбца

Как удалить столбец из схемы данных

Экспорт и импорт данных в Google BigQuery

Импорт данных через интерфейс Google BigQuery

Экспорт данных через интерфейс Google BigQuery

Экспорт и импорт данных с помощью аддона от OWOX BI

Зачем собирать данные в Google BigQuery

Использованные инструменты

Google BigQuery для аналитики в SEO и интернет-маркетинге. Как и для каких целей применяется?

Системы для аналитики данных

Практические примеры

Как реализовать подобную аналитику на практике?

Вопросы и ответы

По каким причинам не подходит Google Analytics 360?

По каким причинам не подходит Excel или Google Таблицы?

Выделенный сервер и система под аналитику?

Выводы

Как начать работать с Google BigQuery — Alexey Seleznev blog

Как начать работу в Google BigQuery

Преимущества Google BigQuery

Особенности SQL для Google BigQuery

Инструменты для работы с BigQuery

Simba Drivers

Язык R

Выводы

Поделиться ссылкой:

Понравилось это:

Похожее

Что такое BigQuery и почему это такая горячая тема? — InfoTrust

Интеграция с Google Таблицами и Аналитикой 360

Озера данных стали проще

Наука о данных и машинное обучение

Простая и «сексуальная» панель инструментов с Data Studio

DataFlow и DataProc упрощают обработку таблиц

Подробнее о BigQuery

Что это такое и как оно вписывается в облако Google?

Что такое Google BigQuery?

Где BigQuery вписывается в облако Google?

Учитывая объем BigQuery

1. Что такое Google BigQuery?

Рисунок 1-4. Хранилища столбцов могут уменьшить объем данных, считываемых запросами, которые обрабатывают все строки, но не все столбцы

Когда использовать Google BigQuery?

Когда использовать Google BigQuery?

Добавить комментарий Отменить ответ