Bigquery

В рамках набора группы учащихся на курс "Highload Architect" подготовили перевод интересной статьи.

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

У одного из наших клиентов возникла проблема с большой, постоянно растущей, таблицей в MySQL с более чем 2 миллиардами записей. Без модернизации инфраструктуры была опасность исчерпания дискового пространства, что потенциально могло сломать все приложение. С такой большой таблицей были и другие проблемы: низкая производительность запросов, плохая схема, и, из-за огромного количества записей, не было простого способа анализировать эти данные. Также нам нужно было решить эти проблемы без простоев в работе приложения.

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

Спасение в облаках

После оценки нескольких альтернативных решений мы решили отправлять данные в какое-нибудь облачное хранилище. И наш выбор пал на Google Big Query. Мы выбрали его, потому что клиент предпочитал облачные решения от Google, а также данные были структурированными, предназначались для аналитики и нам не требовалась низкая задержка передачи данных (low latency). Поэтому BigQuery, казалась, идеальным решением (см. диаграмму ниже).

После тестов, о которых вы можете прочитать в посте Анджея Людвиковски (Andrzej Ludwikowski), мы убедились, что Big Query достаточно хорошее решение, отвечающее потребностям наших клиентов и легко позволяет использовать аналитические инструменты для анализа данных. Но, как вы, возможно, уже знаете, большое количество запросов в BigQuery может привести к увеличению стоимости, поэтому мы хотели избежать запросов в BigQuery напрямую из приложения и использовать его только для аналитики и как что-то вроде резервной копии.

https://cloud.google.com/solutions/infrastructure-options-for-data-pipelines-in-advertising#storing_data

Передача данных в облако

Для передачи потока данных есть много разных способов, но наш выбор был очень прост. Мы использовали Apache Kafka просто потому, что она уже широко использовалась в проекте и не было смысла внедрять другое решение. Использование Kafka дало нам еще одно преимущество мы могли передавать все данные в Kafka и хранить их там в течение необходимого времени, а затем использовать для миграции в выбранное решение, которое справилось бы со всеми проблемами без большой нагрузки на MySQL. С таким подходом мы подготовили себе запасной вариант в случае проблем с BigQuery, например, слишком высокой стоимости или сложностей и с выполнением необходимых запросов. Как вы увидите ниже, это было важное решение, которое дало нам много преимуществ без каких-то серьезных накладных расходов.

Потоковая передача из MySQL

Итак, когда речь заходит о передаче потока данных из MySQL в Kafka, вы, вероятно, думаете о Debezium или Kafka Connect. Оба решения отличный выбор, но в нашем случае не было возможности их использовать. Версия сервера MySQL была настолько старой, что Debezium ее не поддерживал, а обновление MySQL было невозможным. Мы также не могли использовать Kafka Connect из-за отсутствия автоинкрементного столбца в таблице, который мог бы использоваться коннектором для запроса новых записей без потери каких-либо из них. Мы знали, что можно использовать timestamp-столбцы, но при этом подходе могли быть потери строк из-за того, что запрос использовал более низкую точность timestamp, чем указано в определении столбца.

Конечно, оба решения хороши, и если нет никаких препятствий для их использования, то я могу рекомендовать их для передачи данных из вашей базы данных в Kafka. В нашем случае нам нужно было разработать простого Kafka Producer, который запрашивал данные без потери каких-либо записей и передавал их в Kafka. И Kafka Consumer, отправляющего данные в BigQuery, как показано на диаграмме ниже.

Секционирование как способ экономии места

Итак, мы отправили все данные в Kafka (сжимая их для уменьшения полезной нагрузки), а затем в BigQuery. Это помогло нам решить проблемы с производительностью запросов и быстро анализировать большой объем данных. Но осталась проблема с доступным местом. Мы хотели найти решение с заделом на будущее, которое справилось бы с проблемой сейчас и могло быть легко использовано в будущем. Мы начали с разработки новой таблицы. Мы использовали serial id в качестве первичного ключа и секционирование по месяцам. Секционирование этой большой таблицы дало нам возможность создавать резервные копии старых секций и усекать (truncate) / удалять (drop) их, чтобы освободить место, когда секция больше не нужна. Итак, мы создали новую таблицу с новой схемой и использовали данные из Kafka для ее заполнения. После переноса всех записей мы развернули новую версию приложения, которая для INSERT использовала новую таблицу с секционированием и удалили старую, чтобы освободить место. Конечно, вам понадобится достаточно свободного места для переноса старых данных в новую таблицу, но в нашем случае во время миграции мы постоянно делали резервные копии и удаляли старые разделы, чтобы быть уверенными, что у нас хватит места для новых данных.

Сжатие данных как еще один способ освободить пространство

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

Одна из идей была посмотреть, как различные данные распределены по таблице. После нескольких запросов выяснилось, что почти 90% данных никому не нужны. Поэтому мы решили их сжать, написав Kafka Consumer, который отфильтровал бы ненужные записи и вставлял только нужные в еще одну таблицу. Назовем ее сжатой таблицей (compacted table), что показано на приведенной ниже диаграмме.

После сжатия (строки со значением "A" и "B" в колонке type были отфильтрованы во время миграции).

После этого мы обновили наше приложение и теперь выполняли чтение из новой таблицы (compacted table), а запись делали в секционированную таблицу (partitioned table), из которой мы непрерывно передавали данные с помощью Kafka в сжатую таблицу (compacted table).

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

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

Резюме

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

Узнать подробнее о курсе "Highload Architect".

Смотреть вебинар на тему Выбор архитектурного стиля.

Введение

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

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

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

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

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

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

• хранение данных

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

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

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

• Long-term. Плата за данные, хранящиеся в таблицах или разделах, которые не были изменены в течение последних 90 дней. Если таблица не редактируется в течение 90 дней подряд, стоимость хранения этой таблицы автоматически снижается примерно на 50%.

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

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

• Flat-rate. Фиксированная цена. В данной модели выделяется фиксированная мощность на выполнение запросов. Запросы используют эту мощность, и вам не выставляется счёт за обработанные байты. Мощность измеряется в слотах. Минимальное количество слотов100. Стоимость за 100 слотов 2000$ в месяц.

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

• Загрузка данных. Не нужно платить за загрузку данных из облачного хранилища или из локальных файлов в BigQuery.

• Копирование данных. Не нужно платить за копирование данных из одной таблицы BigQuery в другую.

• Экспорт данных. Не нужно платить за экспорт данных из других сервисов, например из Google Analytics (GA).

• Удаление наборов данных (датасетов), таблиц, представлений, партиций и функций.

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

• Чтение данных из метатаблиц __PARTITIONS_SUMMARY__ и __TABLES_SUMMARY__.

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

Wildcard-синтаксис

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

FROM`bq.test_dataset.table*`

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

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

• В настоящее время кешированные результаты не поддерживаются для запросов к нескольким таблицам через wildcard-синтаксис, даже если установлен флажок Использовать кешированные результаты. Если вы запускаете один и тот же запрос с подстановочными знаками несколько раз, вам будет выставлен счёт за каждый запрос.

• Запросы, содержащие операторы DML, не могут использовать wildcard-синтаксис в таблицах в качестве цели запроса. Например, wildcard-синтаксис для таблиц может использоваться в предложении FROM запроса UPDATE, но не может использоваться в качестве цели операции UPDATE.

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

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

select * from test_dataset.test_table_20200101

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

select * from test_dataset.test_table_202001*

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

select * from test_dataset.test_table_2020*

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

select * from test_dataset.test_table_*

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

select * from test_dataset.*

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

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

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

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

• Ingestion Time. Таблицы разбиваются на разделы в зависимости от времени загрузки или времени поступления данных, которые содержат дополнительные зарезервированные поля _PARTITIONTIME, _PARTITIONDATE, хранящие дату создания записи.

• Date/timestamp/datetime. Таблицы разбиты на разделы на основе столбца с типом timestamp, date или datetime. Если таблица партиционирована по столбцу с типом DATE, вы можете создавать партиции с ежедневной, ежемесячной или ежегодной гранулярностью. Каждый раздел содержит диапазон значений, где начало диапазона это начало дня, месяца или года, а интервал диапазона составляет один день, месяц или год в зависимости от степени детализации разделения. Если таблица партиционирована по столбцам с типом TIMESTAMP или DATETIME, вы можете создавать разделы с любым типом гранулярности в единицах времени, включая HOUR.

• Integer range. Таблицы разделены по целочисленному столбцу. BigQuery позволяет разбивать таблицы на разделы на основе определённого столбца INTEGER с указанием значений начала, конца и интервала. Запросы могут указывать фильтры предикатов на основе столбца секционирования, чтобы уменьшить объём сканируемых данных.

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

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

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

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

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

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

• fullVisitorId (string) уникальный идентификатор посетителя GA (также известный как идентификатор клиента)

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

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

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

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

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

[    {        "name": "fullVisitorId",        "type": "STRING",        "mode": "NULLABLE"    },    {        "name": "date",        "type": "STRING",        "mode": "NULLABLE"    },    {        "name": "customDimensions",        "type": "RECORD",        "mode": "REPEATED",        "fields": [            {                "name": "index",                "type": "INTEGER"            },            {                "name": "value",                "type": "STRING"            }        ]    }]

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

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

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

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

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

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

MERGE `dataset`.`ga_sessions_20200112` SUSING `dataset`.`opted_out_visitors` OON S.fullVisitorId = O.fullVisitorIdWHEN MATCHED and O.date='20200112' THENUPDATE SET S.customDimensions = O.customDimensions

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

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

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

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

Оптимизация

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

[    {        "name": "user_id",        "type": "STRING",        "mode": "REQUIRED",    },    {        "name": "anonymized_id",        "type": "STRING",        "mode": "REQUIRED",    }]

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

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

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

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

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

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

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

Итоги

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

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

• Избегайте SELECT *.
  Делайте запросы всегда только к тем полям, которые вам необходимы.

• Избегайте в таблицах полей с типами данных record, array (repeated record).
  Запросы, в которых присутствуют данные столбцы, будут потреблять больше трафика, т. к. BigQuery придётся обработать все данные этого столбца.

• Старайтесь создавать секционированные таблицы (partitioned tables).
  Если грамотно разбить таблицу по партициям, в запросах, в которых будет происходить фильтрация по партиционированному полю, можно значительно снизить потребление трафика, т. к. BigQuery обработает только партицию таблицы, указанную в фильтре запроса.

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

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

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

На днях смотрел вебинар OWOX, где Андрей Осипов (веб-аналитик, автор блога web-analytics.me и лектор Школы веб-аналитики Андрея Осипова) рассказал о своем опыте использования dbt. Говорил о том, кому будет полезен инструмент и какие проблемы решает, а самое главное как не свихнуться со сложной иерархией таблиц и быть уверенным, что все данные считаются корректно. Я решил расшифровать вебинар в статью, потому что так удобнее возвращаться к информации, а она тут, поверьте, того стоит.

Зачем нужен dbt

Зачем нужен еще один инструмент для управления SQL-запросами? Ведь у нас есть Google BigQuery, и вообще в Google Cloud много различных механизмов, которые могут решать задачи по формированию таблиц, например scheduled queries.

Если у вас маленький проект, пара источников данных (таблица с событиями плюс расходы) и вам нужно построить 2-4 отчета, то структура расчета этих таблиц будет довольно простой.

Но если мы говорим о реальном проекте, то все может выглядеть как на картинке ниже.

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

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

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

При такой сложной структуре стандартные механизмы управления (типа scheduled queries) не подходят по нескольким причинам:

1. Зависимости. Каждый scheduled query нужно запускать в определенное время, то есть у вас должна быть четкая иерархия запуска расчета таблиц. Нельзя, чтобы результирующая таблица считалась раньше, чем исходная. К примеру, в 5 утра у вас формируется отчет. После этого происходит какой-то сбой, и только в 7 утра данные о нем попадают в исходную таблицу. В результате вы теряете данные за вчера и нужно пересчитывать весь проект. А если у вас десятка два-три запросов, то пересчет займет минут 40.

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

3. Тестирование. Данные, которые попадают в исходные таблицы, не всегда корректны, а в Google BigQuery нет встроенных инструментов для их тестирования. Например, у вас была таблица с заказами. Потом в нее добавили рейтинг, который пользователи могут менять. Из-за этого могут дублироваться транзакции. То есть в таблице будет две записи с одинаковой транзакцией, но с разными timestamp и рейтингами.

4. Много одинаковых кусков SQL. Когда у вас несколько финальных таблиц, которые генерируются из базовых, одни и те же агрегации происходят много раз. Это сильно увеличивает стоимость использования BigQuery. На нашем вебинаре Андрей Осипов поделился примером, как с помощью dbt ему удалось снизить стоимость использования GBQ в 20 раз для одного из проектов, с которым он работал. Только за счет того, что обращение к базовой таблице с событиями происходило всего один раз.

Для решения описанных проблем есть большое количество похожих инструментов. В этой статье мы рассмотрим, как с ними справляется dbt.

Что такое dbt (data build tool)

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

Как выглядит процесс обработки данных:

Схема из презентации dbt.

На схеме мы видим:

1. Источники данных.

2. Сервисы, которые достают данные из этих источников и складывают в хранилище. Это могут быть Cloud Functions, Cloud Run, OWOX BI Pipeline и др.

3. Хранилище, куда мы складываем данные и где потом их нужно преобразовать: объединить с другими данными, проверить, валидировать.

4. После обработки мы можем отправлять данные в инструменты визуализации, BI-системы или использовать как-либо еще.

dbt это инструмент, который трансформирует сырые исходные данные, проверяет и валидирует. Он делает это на базе собственной логики, а не на базе scheduled queries.

Структура dbt

Фактически dbt состоит из двух сущностей: модель и файл конфигурации.

Модель это сам запрос, то есть отдельный файл, на базе которого будут формироваться view или table.

Модель (.sql) единица трансформации, выраженная SELECT-запросом.

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

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

Файл конфигурации (.yml) параметры, настройки, тесты, документация.

dbt CLI

dbt поставляется в двух версиях: консоль и cloud. То есть инструмент может быть как локальным, так и размещаться в Google Cloud и быть полноценным микросервисом.

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

Также у вас есть возможность привязать свой dbt проект к Cloud Source Repositories или GitHub. То есть фактически все будет вертеться вокруг вашего cloud-проекта, и сам dbt будет там работать.

Как работать с dbt консолью:

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

• После этого используете команду git push для выгрузки содержимого локального репозитория в удаленный репозиторий. И сам dbt с вашими новыми моделями через Cloud Build билдятся в новый контейнер и запускаются.

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

dbt Cloud

Если использовать консоль для вас сложно или нецелесообразно, вы можете попробовать dbt Cloud. По сути это веб-интерфейс той же самой консоли. Простой сайт, на котором можно вносить правки в свои модели, добавлять макросы. Вы можете запускать как весь проект, так и отдельные модели. Здесь же хостится документация по проекту. Регистрация для первого пользователя бесплатна, для каждого следующего $50 в месяц.

Функционал dbt

Зависимости (Refs)

О них мы говорили в начале статьи у вас должна быть четкая иерархия запуска расчета таблиц. dbt позволяет указывать эти зависимости прямо внутри каждого запроса. На базе этих зависимостей, во-первых, происходит расчет подряд необходимых табличек, а во-вторых, строится прямой асинхронный граф (Directed Acyclic Graph).

Прямой асинхронный граф (Directed Acyclic Graph)

Это часть документации, которая также формируется самим dbt. Благодаря этому графу вы можете посмотреть, как именно формируются ваши таблицы. Этого, конечно, очень не хватает в Google BigQuery. Потому что, если в вашем проекте больше 2-3 датасетов с несколькими таблицами, довольно сложно разобраться, как они все формируются.

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

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

Шаблоны (Loops)

Вторая полезная вещь это использование Jinja. Это такой язык шаблонов. Шаблонизатор, в котором есть циклы, переменные и все остальное. Их можно указывать для похожих сущностей.

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

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

Переменные (Variables)

Переменные в dbt двух типов: можно создать переменную в рамках всего проекта или в рамках конкретной модели.

Макросы (Macros)

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

Благодаря макросам вы можете преобразовывать базовые вещи, подстраивать dbt под себя и формировать результат расчета на порядок проще и удобнее.

Пакеты макросов

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

Materializations

Еще одна полезная функция, которая значительно экономит ресурсы.

Допустим, вы не используете dbt. Когда вы обращаетесь в BigQuery к своей партиционной таблице, например делаете select * from [название вашей таблицы], то вы обращаетесь ко всему датасету. Если у вас немного трафика, GA 4 или стриминг OWOX BI настроены недавно, то вы обработаете небольшое количество данных. Если же у вас много данных и большие таблицы, то каждый такой запрос будет дорого стоить.

По факту инкрементальная модель dbt позволяет вам сначала удалять данные в формируемой таблице (например, по дате или по order_id), а потом записывать новые.

Например, в GA 4 данные за вчера, которые образовались в табличке events, еще не полностью готовы. Если вы посмотрите на логи, то вы увидите, что система перезатирает данные. Она формирует из таблицы intraday таблицу events и перезаписывает данные еще на два дня назад.

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

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

Тесты

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

Примеры тестов, которые можно проводить в dbt:

• Not Null.

• Unique.

• Reference Integrity ссылочная целостность (например, customer_id в таблице orders соответствует id в таблице customers).

• Соответствие списку допустимых значений.

• Custom data tests.

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

Документация

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

Такая документация очень упрощает ввод в курс дела нового сотрудника или поиск ошибок и их причин.

DEV TEST PROD

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

Подключение Git

Вы можете подключить к своему dbt проекту любой Git, например GitHub или Google Cloud Source Repositories, и полноценно управлять всеми моделями, которые у вас есть.

Логгинг через вебхуки (Logging via webhooks)

Вы можете складывать результаты тестов и расчетов в какой-нибудь pop-up и потом отправлять через Google Cloud, куда вам надо. Или складывать их в отдельную таблицу GBQ и выводить визуализацию, как все посчиталось.

Как использовать dbt

dbt Сloud

В dbt cloud есть две опции: сама модель, конфигурационный файл, и что-то типа cron. Вы можете указать конкретную модель либо сделать для модели тег или лебл. Это позволит просчитывать, не определенную логически сгруппированную папку, а все папки по какому-то тегу. Например, все, что касается заказов или источников трафика.

Также здесь есть schedule, с помощью которого можно задать периодичность расчета данных.

Google Cloud Cloud Shell

Еще один вариант использования dbt. В Google Cloud есть такой инструмент, как Cloud Shell. Если вы его откроете, то фактически окажетесь внутри apenjin. То есть у вас есть возможность развернуть dbt не в доке, а прямо в apenjin.

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

Google Cloud Cloud Run

Схема работы с dbt, Google Cloud и Cloud Run выглядит примерно так:

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

2. После этого коммитим изменения и пушим в Cloud репозиторий.

3. По факту пуша через Cloud Build формируется новая версия нашего Cloud Run и запускается через Cloud Scheduler по необходимому расписанию.

4. Как результат работы dbt, который находится в Cloud Run, все это рассчитывается в BigQuery, откуда идет уже в Data Studio.

5. Логи можно складывать через pop-up в Telegram по определенным правилам например, пушить не все, а только какие-то важные изменения.

Такая инфраструктура позволяет довольно легко переносить запросы с одного Cloud проекта на другой и контролировать все происходящее с расчетами в dbt. Благодаря использованию Git вы четко понимаете, кто в вашей команде что запушил, куда, зачем и почему.

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

Мы использовали функцию UNNEST, потому что обычно параметры события хранятся как повторяющаяся запись (repeated record), которую вы можете рассматривать как массив, напоминающий JSON структуру. Вы можете разбить этот массив, и поместить каждый отдельный его элемент (параметр события) в новую строку, а затем скопировать исходную строку для каждого из этих отдельных параметров. Более понятно будет если посмотреть следующую анимацию:

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

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

Если вы посмотрите на событие level_complete_quickplay, то найдёте два интересующие нас параметра: параметр value, который сообщает нам окончательный счет игры (то есть сколько ходов потребовалось пользователю для прохождения игры), и параметр board, в котором хранится размер игрового поля.

Параметр value (количество ходов которое потребовалось пользователю для прохождения игры) вероятно зависит от размера игрового поля (параметра board). Поэтому, перед тем, как приступить к расчёту каких то статистик, например расчёту среднего количества ходов, необходимых для прохождения игры, имеет смысл сгруппировать наши события по размеру игрового поля.

Если мы просто будем использовать функцию UNNEST:

SELECT event_name, event_timestamp, user_pseudo_id, paramFROM `firebase-public-project.analytics_153293282.events_20181003`,UNNEST(event_params) AS paramWHERE event_name = "level_complete_quickplay"AND (param.key = "value" OR param.key = "board")

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

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

SELECT   MAX(if(param.key = "value", param.value.int_value, NULL)) AS score,  MAX(if(param.key = "board", param.value.string_value, NULL)) AS board_typeFROM (  SELECT event_name, event_timestamp, user_pseudo_id, param  FROM `firebase-public-project.analytics_153293282.events_20181003`,  UNNEST(event_params) AS param  WHERE event_name = "level_complete_quickplay"  AND (param.key = "value" OR param.key = "board")) GROUP BY user_pseudo_id, event_timestamp

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

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

SELECT FROM UNNEST в помощь!

К счастью, есть другой вариант решения, конструкция SELECT FROM UNNEST.

Используя конструкцию SELECT FROM UNNEST, вы говорите: Я хочу применить функцию UNNEST к повторяющейся записи в ее собственной маленькой временной таблице. Далее выбрать одну строку из неё и поместить ее в наши результаты, так же как если бы это было любое другое значение .

Объяснение приведённое выше звучит сложновато, давайте рассмотрим пример.

Первое, что я собираюсь сделать, это удалить в исходном варианте решения оператор UNNEST, чтобы просмотреть все события прохождения игры.

SELECT event_name, event_timestamp, user_pseudo_idFROM `firebase-public-project.analytics_153293282.events_20181003`WHERE event_name = "level_complete_quickplay"

Затем я запрашиваю столбец value.int_value из развёрнутого массива event_params, где поле key равно value.

SELECT event_name, event_timestamp, user_pseudo_id,   (SELECT value.int_value FROM UNNEST(event_params)     WHERE key = "value") AS scoreFROM `firebase-public-project.analytics_153293282.events_20181003`WHERE event_name = "level_complete_quickplay"

В итоге происходит что-то вроде того, что показывает приведённая ниже анимация. Сначала мы разбиваем массив event_params в его собственную небольшую временную таблицу, фильтруя одну запись, по условию key = "value", а затем забираем полеvalue.int_value.

После чего вы получаете следующий результат:

При использовании конструкции SELECT FROM UNNEST, следует помнить о трех важных моментах:

Во-первых, вы должны убедиться, что вы поместили вызов SELECT FROM UNNEST в круглые скобки. Если этого не сделать, BigQuery выдаст ошибку, т.к. вы используете два оператора SELECT в рамках одного запроса.

Во-вторых, похоже на то, что описанный приём будет работать, только если вы запрашиваете не более одного значения из каждого вызова SELECT FROM UNNEST.

В-третьих, вы можете использовать SELECT FROM UNNEST несколько раз в одном и том же операторе SELECT! Итак, теперь мы можем взять наш предыдущий запрос и добавить в него второй вызовSELECT FROM UNNEST, чтобы получить параметры board и value рядом, в одной строке.

SELECT event_name, event_timestamp, user_pseudo_id,    (SELECT value.int_value FROM UNNEST(event_params)     WHERE key = "value") AS score,  (SELECT value.string_value FROM UNNEST(event_params)     WHERE key = "board") AS board_sizeFROM `firebase-public-project.analytics_153293282.events_20181003`WHERE event_name = "level_complete_quickplay"

Приведённый выше запрос вернёт ожидаемый нами результат:

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

SELECT AVG(score) AS average, STDDEV(score) as std_dev, board_sizeFROM (    SELECT event_name, event_timestamp, user_pseudo_id,   (SELECT value.int_value FROM UNNEST(event_params)     WHERE key = "value") AS score,  (SELECT value.string_value FROM UNNEST(event_params)     WHERE key = "board") AS board_size  FROM `firebase-public-project.analytics_153293282.events_20181003`  WHERE event_name = "level_complete_quickplay") GROUP BY board_size

Анализируем параметры событий вместе со свойствами пользователей!

SELECT FROM UNNEST также можно использовать для совместного анализа параметров событий со свойствами пользователей.

Например, разработчики Flood-it используют событие trust_virtual_currency, чтобы отслеживать, когда пользователь тратит дополнительные шаги в конце раунда. Параметр value помогает отслеживать, сколько шагов они тратят на событие. Мы также отслеживаем количество дополнительных шагов, выполненных пользователем, с помощью свойства initial_extra_steps.

Допустим, мы хотим выяснить, есть ли какая-либо корреляция между тем, сколько шагов пользователю изначально дано, и сколько дополнительных шагов он делает во время события use extra steps. Для этого нам необходимо проанализировать параметр value вместе с пользовательским свойством initial_extra_steps. Опять же, эти параметры объеденены в повторяющиеся записи, как нам их правильно развернуть?

Теоретически это можно сделать, объединив два вызова UNNEST.

SELECT event_name, event_timestamp, user_pseudo_id,   param.value.int_value AS moves_used,  userprop.value.string_value AS initial_extra_stepsFROM `firebase-public-project.analytics_153293282.events_20181003`,UNNEST (event_params) as param,UNNEST (user_properties) as userpropWHERE event_name = "spend_virtual_currency"AND param.key = "value"AND userprop.key = "initial_extra_steps"

Это могло бы сработать, ход выполнения такого запроса будет следующим: сначала мы развернём spend_virtual_currency в отдельные строки, а затем то же самое повторим для каждой из этих строк для каждого записываемого нами свойства пользователя. Это означает, что мы, по сути, умножаем каждую строку в большом наборе данных запроса на (количество параметров * количество свойств пользователя). Очень быстро такой запрос может стать достаточно дорогой операцией!

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

Вновь нам на помощь приходит SELECT FROM UNNEST. Сначала я получу значение нашего параметра value, как в нашем первом примере.

SELECT event_name, event_timestamp, user_pseudo_id,   (SELECT value.int_value FROM UNNEST(event_params)     WHERE key = "value") AS steps_usedFROM `firebase-public-project.analytics_153293282.events_20181003`WHERE event_name = "spend_virtual_currency"

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

SELECT event_name, event_timestamp, user_pseudo_id,   (SELECT value.int_value FROM UNNEST(event_params)     WHERE key = "value") AS steps_used,  CAST(    (SELECT value.string_value FROM UNNEST(user_properties)      WHERE key = "initial_extra_steps")    AS int64) AS initial_stepsFROM `firebase-public-project.analytics_153293282.events_20181003`WHERE event_name = "spend_virtual_currency"

Теперь у меня есть все необходимые данные в одной строке!

Этот код не только более эффективен, но и прост для понимания.

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

SELECT AVG(steps_used) AS average_steps_used, initial_steps FROM (  SELECT event_name, event_timestamp, user_pseudo_id,     (SELECT value.int_value FROM UNNEST(event_params)       WHERE key = "value") AS steps_used,    CAST(      (SELECT value.string_value FROM UNNEST(user_properties)        WHERE key = "initial_extra_steps")      AS int64) AS initial_steps  FROM `firebase-public-project.analytics_153293282.events_20181003`  WHERE event_name = "spend_virtual_currency") WHERE initial_steps IS NOT NULLGROUP BY initial_stepsORDER BY initial_steps

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

SELECT CORR(steps_used, initial_steps) AS correlation FROM (SELECT event_name, event_timestamp, user_pseudo_id,   (SELECT value.int_value FROM UNNEST(event_params)     WHERE key = "value") AS steps_used,  CAST(    (SELECT value.string_value FROM UNNEST(user_properties)      WHERE key = "initial_extra_steps")    AS int64) AS initial_stepsFROM `firebase-public-project.analytics_153293282.events_20181003`WHERE event_name = "spend_virtual_currency") WHERE initial_steps IS NOT NULL

Итак, вы можете использовать UNNEST и SELECT FROM UNNEST для быстрой обработки повторяющихся записей, которые Google Analytics для Firebase любит использовать в своей схеме BigQuery. И, как оказалось, это те же самые повторяющиеся записи, которые отображаются в данных Crashlytics и Cloud Messaging. Поэтому я рекомендую потратить время на то, чтобы привыкнуть к этим методам, т.к. они заметно облегчат работу со сложными структурами данных.

Количество заболевших коронавирусом растёт, и Cloud4Y снова перешёл на удалённый формат работы. Мы видим, как наши врачи с трудом справляются с невероятной нагрузкой, и не хотим добавлять им хлопот. Печально, что даже в такой ситуации в нашей стране слабо используются современные технологии. А вот в США, например, медицинская организация построила облачную систему, позволяющую лучше заботиться о наиболее уязвимых жителях страны. Перевод этой истории мы и предлагаем вашему вниманию.

Когда в начале 2020 года разразилась пандемия COVID-19, организациям здравоохранения пришлось менять или ускорять свои планы по обслуживанию пациентов. Американская Commonwealth Care Alliance (CCA) использовала облачную аналитику данных, чтобы связать врачей и специалистов по медицинскому обслуживанию с участниками из группы риска. Вице-президент Валмик Кудесиа CCA, ответственный за клиническую информатику и передовую аналитику CCA, делится их историей.

CCA это медицинская организация, признанная на национальном уровне лидером в предоставлении и координации дорогостоящего ухода за нуждающимися людьми, которые имеют право на программы Medicaid и Medicare. CCA выполняет обязанности плательщика медицинских услуг, управления медицинским обслуживанием и непосредственно поставщика этих услуг пациентам. Подопечные CCA живут с проблемами со здоровьем, поведением и социализацией. У многих из них сложные жизненные условия, они уязвимы и маргинализированы. Когда зимой в США пришла новость о COVID-19 в организации быстро осознали, что этой категории людей понадобится особенно много заботы и внимания. Сотрудникам CCA необходимо было продолжать выполнять свои обязанности, учитывая множество новых, постоянно меняющихся факторов.

Нам требовались надёжные данные, которые можно было быстро получить, и которые были бы интегрированы в их рабочие процессы. CCA заранее создала облачную платформу расширенной аналитики с BigQuery и Looker. Спустя шесть месяцев, убедившись в надёжности и работоспособности решения, мы продолжаем предоставлять клиницистам более целостное представление о потребностях нуждающихся людей. CCA развивает ориентированное на человека использование данных и аналитики, чтобы справиться с предстоящим сезонным комбо сочетанию COVID-19 и гриппа.

Данные, необходимые для более быстрого принятия решений

Облачная платформа была создана для ситуаций, когда пользователи должны двигаться быстро и в разных направлениях, а также менять параметры запрашиваемой информации. Это означало, что когда пришёл COVID-19, CCA не пришлось многое менять в своих процессах.

Группа по анализу данных использовала Looker и BigQuery в сочетании с другими технологиями, чтобы выполнять разработку и развёртывание операций обработки данных в сочетании с возможностями машинного обучения. Облачные сервисы соответствовали требованиями HIPAA (своего рода аналог нашего ФЗ-152, но с медицинским уклоном прим. переводчика), а BigQuery был (и остаётся) эластичным и доступен как услуга. Это позволило небольшой команде по анализу данных сосредоточиться на работе с данными и быстро развивать проект, сохраняя его совместимость и обеспечивая отличную производительность платформы.

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

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

Использование ежедневных и даже почасовых данных, поступающих из разных источников, было необходимо для того, чтобы слабо защищённые слои населения могли получить то, что им нужно еду на дом, лекарства или другие услуги. В некоторых случаях у нас уже имелись все необходимые данные. Например, менее чем за 30 минут удалось внедрить определение высокого риска осложнений COVID-19, разработанное CDC, в LookML (слой абстракции запросов Looker) и связать понятие осложнения COVID-19 с высоким риском с нашей информационной моделью.

Также в течение рабочего дня мы создали наши основные информационные панели мониторинга COVID-19 и внедрили соответствующие данные о пандемии в другие клинические дашборды и Action boards. Гибкость решения, достигаемая за счёт абстракции и быстрой доставки данных, позволила нам быстро идентифицировать каждого человека с высоким риском неблагоприятного исхода COVID-19 и предоставить эти знания врачам CCA.

Некоторые из нужных данных получить было непросто. Например, в начале эпидемии не было репозиториев данных COVID-19 или сервисов передачи этих данных. Было важно собирать все возможные данные для обслуживания нуждающихся групп людей. И во многих случаях мы собирали использовали эти данные самостоятельно. Например, на ранних стадиях пандемии COVID-19 в Массачусетсе постепенно начали закрываться дневные центры здоровья для взрослых (ADH), общественные центры, которые предоставляют важные услуги для пожилых людей, а затем внезапно это приняло массовый характер. Но мы наловчились передавать эти знания каждому человеку, посещавшему эти учреждения, буквально спустя несколько минут после того, как узнавали про очередной закрытый ADH. Чуть позднее в CCA стали поступать данные Департамента общественного здравоохранения Массачусетса о положительных тестах, что позволило получить представление о концентрации людей из группы риска, проживающих в районах с высокой или растущей заражаемостью COVID-19.

Путь от просто данных к важнейшему элементу для лечения и поддержки

Поскольку пандемия COVID-19 продолжается, мы используем самую свежую доступную информацию, чтобы обновлять и менять стратегии поддержки и ухода за подопечными. Сотрудникам CCA стало намного удобнее работать с данными благодаря облаку. Обычно у нас более 450 активных пользователей в неделю, и сведения запрашиваются чуть ли не ежесекундно в течение рабочего дня. Благодаря большой собранной базе можно заметить, как часть данных соответствует общей картине или не вписывается в неё. Вместо того, чтобы использовать данные как отдельные кусочки мозаики, мы используем их комплексно. То есть благодаря построенному на облачной платформе решению мы используем данные в интересах пациентов, встроили новую технологию в свою повседневную жизнь.

Обладая столь удобным инструментом, команда специалистов, занимающихся наукой о данных, перешла к глубокому проектированию функций и причинно-следственным связям, чтобы сделать информацию, получаемую сотрудниками CCA и врачами, более полной и понятной. В свою очередь, врачи и наши другие сотрудники ждут, что Big Data поможет им лучше заботиться о людях, нуждающихся в помощи.

Ретроспектива решений на основе данных

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

Теперь человек взаимодействует с машиной, с поступающими ему данными. Если делать всё правильно, что данные можно включить в процесс принятия решений, не добавляя лишних этапов, затягивающих прохождение пути от задачи до результата. В конце концов, информация это естественная часть процесса принятия решения. В нашем случае заботы о человеке. Например, изучение болей в груди часто требует расшифровки электрокардиограммы (ЭКГ), которая несёт в себе массу полезных сведений. И врачи по вполне понятным причинам ждут, что ЭКГ поможет им лучше заботиться о человеке, а не сосредотачиваются на самих данных, которые генерируются при снятии ЭКГ.

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

Что ещё интересного есть в блогеCloud4Y

Найдено давно утерянное руководство к самому старому компьютеру в мире

Пограничный патруль США планирует 75 лет хранить данные из гаджетов путешественников

Определённо не Windows 95: какие операционные системы поддерживают работу в космосе?

Рассказываем про государственные защищенные сервисы и сети

Внутри центра обработки данных Bell Labs, 1960-е

Подписывайтесь на нашTelegram-канал, чтобы не пропустить очередную статью. Пишем не чаще двух раз в неделю и только по делу