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

Учебное пособие по BigQuery – как повысить гибкость вашего бизнеса

Из чего должно состоять идеальное учебное пособие по BigQuery? Скорее всего, в нем должны быть ответы на три вопроса: что, почему и как? Мы осмелились написать такое прекрасное произведение и пойти дальше. Наша цель – взглянуть на BigQuery с точки зрения бизнеса: какие преимущества для бизнеса дает это облачное хранилище данных по сравнению с конкурентами. Читайте дальше и скажите, хорошо ли у нас получилось и попал ли наш текст в цель.

Что такое Google BigQuery?

На наш взгляд, на этот вопрос может быть несколько ответов:

BigQuery – это база данных

В самом широком смысле BigQuery – это база данных. Как вы знаете, базы данных – это наборы связанных данных, а BigQuery позволяет хранить терабайты записей.

BigQuery – облачное хранилище данных

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

BigQuery – это столбчатая база данных

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

BigQuery – это база данных электронных таблиц

Это – простое определение BigQuery, если перечисленных выше недостаточно. BigQuery сочетает в себе функции ПО для работы с электронными таблицами, такими как Google Sheets, и системами управления базами данных, например, MySQL.

Почему вам следует использовать BigQuery

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

Скажем, в Google Таблицах вы также можете запрашивать наборы данных с помощью функции QUERY. Это может работать для различных типов отчетов и диаграмм, основанных на малых и средних наборах данных. Однако ни одно приложение для работы с электронными таблицами (даже Excel) не сможет обрабатывать сложные запросы больших наборов данных, которые включают миллионы строк в таблице.

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

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

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

Руководство по настройке BigQuery

Первые шаги

Ваше путешествие начнется с Google Cloud Platform. Если это ваш первый визит, вам нужно будет выбрать свою страну и принять Условия использования.

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

Создание проекта

Вот как выглядит BigQuery при первом посещении.

Нажмите кнопку «CREATE PROJECT», чтобы запустить движок создания проекта. Назовите свой проект, при необходимости выберите организацию и нажмите «CREATE».

Теперь вас официально приветствуют в BigQuery.

SANDBOX BigQuery

Скорее всего, ваше внимание привлекли два сообщения в верхней части консоли BigQuery.

SANDBOX (прим.: англ. – песочница) означает, что вы используете учетную запись среды тестирования, для которой не требуется вводить платежную информацию. Этот вариант уровня бесплатного использования предоставляет вам 10 ГБ активного хранилища и 1 ТБ обработанных данных запросов в месяц. При использовании этой учетной записи срок действия ваших таблиц истечет через 60 дней.

Второй баннер предлагает вам активировать бесплатную пробную версию. Отличие от аккаунта «песочницы» в том, что при активации пробной версии, вам нужно будет ввести свои платежные данные. Если вы это сделаете, вы получите 300 долларов в качестве облачных кредитов.

В этом учебном пособии по Google BigQuery мы будем использовать вариант «песочницы». Не стесняйтесь отклонить «DISMISS» оба сообщениях в верхней части консоли.

Создать набор данных в BigQuery

Давайте добавим данные в BigQuery, чтобы проверить, как это работает. Щелкните нужный проект, а затем «Create dataset».

Назначьте идентификатор набора данных – вы можете вводить буквы и цифры. При необходимости вы можете выбрать расположение данных, а также срок действия таблицы (до 60 дней) и шифрование. После этого нажмите «Create dataset»

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

Следующим шагом будет создание таблицы в наборе данных. Вот кнопка «Create table», которую нужно нажать:

Здесь у вас есть несколько вариантов:

Создайте пустую таблицу (Empty table) и заполните ее вручную.
Загрузите (Upload) таблицу со своего устройства в одном из поддерживаемых форматов.
Импортируйте таблицу из Google Cloud Storage или Google Диска (Drive).
Импортируйте таблицу из Google Cloud Bigtable через интерфейс командной строки.

Форматы файлов, которые можно импортировать в BigQuery

Табличные данные можно легко загрузить в BigQuery в следующих форматах:

CSV
JSONL (линии JSON)
Avro
Parquet
ORC
Google Sheets (только для Google Диска)
Резервное копирование облачного хранилища данных (только для облачного хранилища Google)

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

Загрузить данные CSV в BigQuery

После того, как вы нажмете кнопку «Create table», вам необходимо выполнить следующие шаги:

Выберите источник – «Upload»
Выберите файл – нажмите «Обзор» и выберите файл CSV на своем устройстве.
Формат файла – выберите «CSV», но обычно система определяет формат файла автоматически.
Имя таблицы – введите имя таблицы.
Установите флажок автоопределения схемы «Autodetect».
Нажмите «Createtable».

Так выглядит основной поток. Кроме того, вы можете определить параметры раздела (для разделения таблицы на более мелкие сегменты), параметры кластера (для организации данных на основе содержимого указанных столбцов), а также настроить дополнительные параметры «Advanced options».

Вот как выглядит ваша таблица, загруженная в BigQuery:

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

Импортировать данные из Google Таблиц в BigQuery

Большинство из вас, вероятно, хотели бы узнать больше об импорте таблиц из Google Sheets в BigQuery. Рабочий процесс очень похож, но с некоторыми изменениями. Нажмите кнопку «Create table» и:

Выберите источник – «Drive»
Выберите Drive URI – вставьте URL-адрес вашей электронной таблицы Google Таблиц.
Формат файла – выберите «Google Sheets».
Диапазон листов – укажите лист и диапазон данных для импорта. Если вы оставите это поле пустым, BigQuery будет извлекать данные с первого листа вашей электронной таблицы.
Имя таблицы – введите имя таблицы.
Установите флажок автоопределения схемы «Autodetect».
Нажмите «Createtable».

Возможно, вам будет интересно как настроить дополнительные параметры «Advanced options», поскольку они позволяют:

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

После того, как вы нажмете «Create table», указанный лист из вашей электронной таблицы будет импортирован в BigQuery. Вот подробности (предварительный просмотр таблицы недоступен для импорта Google Таблиц):

Импортируйте данные из источника в BigQuery

Допустим, у вас есть набор данных в Airtable, QuickBooks или другом источнике, который вы хотите импортировать в BigQuery. Вы можете сделать это вручную с помощью параметров CSV, как описано выше, или автоматизировать импорт данных в BigQuery с помощью различных ETL инструментов.

Таблицы запросов в BigQuery

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

Ознакомьтесь с нашим Руководством по Google BigQuery SQL, чтобы вникнуть в этот вопрос.

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

=query(Deals!A:EU,»select E, N, T order by T Desc»)

«select E, N, T order by T Desc» – это запрос для получения трех столбцов всего набора данных и упорядочения результатов в порядке убывания.

В BigQuery тот же запрос к набору данных будет выглядеть так:

SELECT string_field_4, string_field_13, string_field_19 FROM `test-project-310714.test.pipedrive-deals` ORDER BY string_field_19 DESC

Теперь мы объясним, как это работает.

Как запрашивать данные в примере синтаксиса BigQuery +

Нажмите кнопку «Таблица запросов», чтобы начать запрос.

Вы увидите шаблон запроса, например:

SELECT FROM `test-project-310714.test.pipedrive-deals` LIMIT 1000

Это основной пример, который вы можете использовать для начала знакомства с запросами. Добавьте * после метода SELECT, чтобы запрос выглядел так:

SELECT * FROM `test-project-310714.test.pipedrive-deals` LIMIT 1000

Этот запрос вернет все доступные столбцы из указанной таблицы, но не более 1000 строк. Нажмите «RUN», и все, готово!

Теперь давайте запросим определенные поля (столбцы) и отсортируем их. Итак, вместо использования * нам нужно указать нужные нам имена полей. Вы можете найти имена полей на вкладке «Results» или в своем последнем запросе.

Давайте заменим метод LIMIT из запроса по умолчанию на ORDER BY – это позволит вам сортировать данные по указанному столбцу. Чтобы отсортировать данные в порядке убывания, добавьте DESC в конец запроса. Вот как это выглядит:

SELECT string_field_4, string_field_13, string_field_19 FROM `test-project-310714.test.pipedrive-deals` ORDER BY string_field_19 DESC

Настройки запроса

Если вы нажмете кнопку «MORE» и выберете настройки запроса «Query settings», то сможете настроить место назначения для результатов запроса, а также другие параметры.

Здесь вы также можете настроить запуск запросов в пакетном режиме. Пакетные запросы ставятся в очередь и запускаются, как только в общем пуле ресурсов BigQuery становятся доступны свободные ресурсы.

Как сохранять запросы в BigQuery

Вы можете сохранить свои запросы для дальнейшего использования. Для этого нажмите «Save» => «Save Query».

В следующем окне назовите свой запрос и укажите его видимость:

личные – только вы сможете редактировать запрос;
проект – только участники проекта смогут редактировать запрос;
публичный – запрос будет общедоступным для редактирования.

Сохраните «SAVE».

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

Как планировать запросы в BigQuery

Рядом с кнопкой сохранения «SAVE» есть кнопка «SCHEDULE», которая позволяет включать запросы по расписанию. Вы уже подумали: «Зачем мне выполнять запросы по расписанию?» Что ж, для этого есть как минимум две причины:

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

Примечание. Планирование запросов доступно только для проектов с включенным биллингом. Это не будет работать для проектов учетных записей SANDBOX (ПЕСОЧНИЦА).

После того, как вы нажмете кнопку «SCHEDULE», вы получите уведомление о том, что вам необходимо сначала включить BigQuery Data Transfer API.

Нажмите «ENABLE API» и подождите. После этого вы сможете создавать запланированные запросы, нажав кнопку «SCHEDULE».

Нажмите «Create new scheduled query» для создания нового запланированного запроса и определите следующие параметры:

Имя запланированного запроса
Параметры расписания

Повторения
Дата начала и время работы
Дата окончания
- Место назначения
- Имя таблицы
- Запись предпочтений (перезапись или добавление)
- Перезаписать – результаты запроса перезапишут данные в таблице.
- Добавить – результаты запроса будут добавлены к данным в таблице
При желании вы можете настроить дополнительные параметры и параметры уведомлений. По завершении настройки нажмите «Schedule».

Затем вам нужно будет выбрать свою учетную запись Google, чтобы продолжить работу со службой передачи данных BigQuery.

История запросов

Допустим, вы забыли сохранить расширенный запрос, но хотите восстановить его сейчас. Не беспокойтесь, BigQuery предоставит вам журналы выполненных запросов и заданий. Вы найдете их во всплывающих вкладках истории заданий или запросов :«JOB HISTORY» и «QUERY HISTORY».

Примечание. BigQuery отображает все задания загрузки, экспорта, копирования и запроса за последние 6 месяцев. Он ограничивает историю заданий и запросов до 1000 записей.

Экспорт запросов из BigQuery… и перенос данных в BigQuery

В большинстве случаев пользователям необходимо экспортировать результаты своих запросов за пределы BigQuery. Обычно используются приложения для работы с электронными таблицами, такие как Google Таблицы и Excel, инструменты визуализации и информационной панели, такие как Google Data Studio и Tableau, а также другое ПО. Вы также можете подключить Power BI к BigQuery.

Чтобы экспортировать результаты запроса, вам нужно нажать кнопку «SAVE QUERY RESULTS» и выбрать один из доступных вариантов:
- CSV файл
  - Скачать на свое устройство (до 16К строк)
  - Скачать на Google Диск (до 1 ГБ)
  - Скачать на свое устройство (до 16К строк)
  - Скачать на Google Диск (до 1 ГБ)
  В качестве примера выберем параметр таблицы BigQuery. Вам нужно будет выбрать проект и набор данных, а также назвать свою таблицу.
  
  Нажмите «SAVE», и готово!
  
  Бонус: как BigQuery хранит данные
  
  В отличие от традиционных реляционных баз данных, в которых данные хранятся построчно, BigQuery хранит столбец за столбцом. Это означает, что для хранения каждого столбца используется отдельный файловый блок. Этот столбчатый формат, называемый Capacitor, позволяет BigQuery достичь очень высокой пропускной способности, что имеет решающее значение для онлайн-аналитической обработки.
  
  Архитектура BigQuery
  
  В безсерверной архитектуре BigQuery ресурсы для хранения и вычислений разделены. Это позволяет загружать данные любого размера в хранилище и сразу же приступать к их анализу. Вот инфраструктурные технологии, благодаря которым это происходит:
  - Colossus – отвечает за хранение. Это глобальная система хранения, оптимизированная для чтения больших объемов структурированных данных, а также для обработки репликации, восстановления и распределенного управления.
  - Dremel – отвечает за вычисления. Это – мультитенантный кластер, который превращает SQL-запросы в деревья выполнения. У этих деревьев есть листья, которые называются слотами, и один пользователь может получить тысячи слотов для выполнения своих запросов.
  - Jupiter – отвечает за перемещение данных между хранилищем (Colossus) и вычислениями (Dremel). Это – петабитная сеть, которая перемещает данные из одного места в другое, и делает это очень быстро.
  - Borg – отвечает за распределение аппаратных ресурсов. Это система управления кластером для выполнения сотен тысяч заданий в BigQuery.
  Узнать больше о BigQuery
  
  Давайте будем честными – наша цель заключалась не в том, чтобы написать идеальное руководство по BigQuery, а в том, чтобы дать вам ответы на вопросы, которые есть у каждого новичка, когда он открывает для себя новый инструмент или технологию. Мы уверены, что после прочтения у вас, вероятно, возникнут дополнительные вопросы, но вам придется искать на них ответы самостоятельно.
  
  Основным источником информации является официальная документация BigQuery, в которой вы найдете обширную базу знаний об использовании BigQuery. Недостатком этого источника является то, что он абсолютно огромен и иногда чрезмерно структурирован (как и большинство документации Google).
  
  Со своей стороны, мы постараемся охватить другие темы, связанные с BigQuery, чтобы прояснить некоторые сложные моменты. Удачи вам!
  
  Работа с Google BigQuery. Считаем деньги
  
  В данной статье мы хотели бы рассказать о том, как мы в команде Wargaming Platform знакомились с BigQuery, о задаче, которую необходимо было решать, и проблемах, с которыми мы столкнулись. Кроме того, расскажем немного о ценообразовании и об инструментах, имеющихся в BigQuery, с которыми нам удалось поработать, а также предоставим наши рекомендации, как можно сэкономить бюджет во время работы с BigQuery.
  
  Знакомство с BigQuery
  
  BigQuery — это бессерверное, масштабируемое облачное хранилище данных с мощной инфраструктурой от Google, которое имеет на борту RESTful веб-сервис. Имеет тесное взаимодействие с другими сервисами от Google. Создатели обещают молниеносное выполнение запросов с максимальной задержкой в RESTful до 1 секунды. BigQuery поддерживает диалект Standard SQL. Имеется возможность контроля доступа к данным и разграничение прав пользователей. Также есть возможность задавать квоты и лимиты для операций с БД. Доступ к BigQuery возможен через Google Cloud Console, с помощью внутренней консоли BigQuery, а также через вызовы BigQuery REST API как напрямую, так и через различные клиентские библиотеки java, python, .net и многие другие.
  
  Есть возможность подключения через ODBC/JDBC-драйвер с помощью Magnitude Simba ODBC. В состав BigQuery входит довольно мощный визуальный SQL-редактор, в котором можно увидеть историю выполнения запросов, проанализировать потребляемый объём данных, не выполняя запрос, что позволяет существенно сэкономить финансы.
  
  Ценообразование
  
  BigQuery предлагает несколько вариантов ценообразования в соответствии с техническими потребностями. Все расходы, связанные с выполнением заданий BigQuery в проекте, оплачиваются через привязанный платёжный аккаунт.
  Затраты формируются из двух составляющих:
  - хранение данных
  - обработка данных во время выполнения запросов.
  Стоимость хранения зависит от объёма данных, хранящихся в BigQuery.
  - Active. Ежемесячная плата за данные, хранящиеся в таблицах или разделах, которые были изменены за последние 90 дней. Плата за активное хранение данных составляет 0,020 $ за 1 ГБ, первые 10 ГБ — бесплатно каждый месяц. Стоимость хранилища рассчитывается пропорционально за МБ в секунду.
  - Long-term. Плата за данные, хранящиеся в таблицах или разделах, которые не были изменены в течение последних 90 дней. Если таблица не редактируется в течение 90 дней подряд, стоимость хранения этой таблицы автоматически снижается примерно на 50%.
  Что касается затрат на запрос, вы можете выбрать одну из двух моделей ценообразования:
  - On-demand. Цена зависит от объёма данных, обрабатываемых каждым запросом. Стоимость каждого терабайта обработанных данных составляет 5,00 $. Первый обработанный 1 ТБ в месяц бесплатно, минимум 10 МБ обрабатываемых данных на таблицу, на которую ссылается запрос, и минимум 10 МБ обрабатываемых данных на запрос. Важный момент: оплата происходит за обработанные данные, а не за данные, полученные после выполнения запроса.
  - Flat-rate. Фиксированная цена. В данной модели выделяется фиксированная мощность на выполнение запросов. Запросы используют эту мощность, и вам не выставляется счёт за обработанные байты. Мощность измеряется в слотах. Минимальное количество слотов — 100. Стоимость за 100 слотов — 2000 $ в месяц.
  Стоит заметить, что хранение данных часто обходится значительно дешевле, чем обработка данных в запросах.
  Бесплатные операции:
  - Загрузка данных. Не нужно платить за загрузку данных из облачного хранилища или из локальных файлов в BigQuery.
  - Копирование данных. Не нужно платить за копирование данных из одной таблицы BigQuery в другую.
  - Экспорт данных. Не нужно платить за экспорт данных из других сервисов, например из Google Analytics (GA).
  - Удаление наборов данных (датасетов), таблиц, представлений, партиций и функций.
  - Операции с метаданными таблиц. Не нужно платить за редактирование метаданных.
  - Чтение данных из метатаблиц __PARTITIONS_SUMMARY__ и __TABLES_SUMMARY__.
  - Все операции UDF. Не нужно платить за операции создания, замены или вызова функций.
  Wildcard-синтаксис
  
  Wildcard-синтаксис позволяет выполнять запросы к нескольким таблицам, используя краткие операторы SQL. Wildcard-синтаксис доступен только в Standard SQL. Таблица с подстановочными знаками (wildcard) представляет собой объединение всех таблиц, соответствующих выражению с подстановочными знаками. Например, следующее предложение FROM использует выражение с подстановочными знаками table* для сопоставления всех таблиц в наборе данных test_dataset, которые начинаются со строки table:
```
FROM `bq.test_dataset.table*`
```
  Запросы к таблице имеют следующие ограничения:
  - Не поддерживаются представления. Если таблица подстановочных знаков соответствует любому представлению в наборе данных, запрос возвращает ошибку. Это верно независимо от того, содержит ли ваш запрос WHERE в псевдостолбце _TABLE_SUFFIX для фильтрации представления.
  - В настоящее время кешированные результаты не поддерживаются для запросов к нескольким таблицам через wildcard-синтаксис, даже если установлен флажок «Использовать кешированные результаты». Если вы запускаете один и тот же запрос с подстановочными знаками несколько раз, вам будет выставлен счёт за каждый запрос.
  - Запросы, содержащие операторы DML, не могут использовать wildcard-синтаксис в таблицах в качестве цели запроса. Например, wildcard-синтаксис для таблиц может использоваться в предложении FROM запроса UPDATE, но не может использоваться в качестве цели операции UPDATE.
  Wildcard-синтаксис для таблиц полезен, когда набор данных содержит несколько таблиц с одинаковыми именами, которые имеют совместимые схемы. Обычно такие наборы данных содержат таблицы, каждая из которых представляет данные за один день, месяц или год. Такой синтаксис полезен для сегментированных таблиц (sharded tables) — не путать с партиционированными таблицами.
  
  Примеры использования:
  Допустим, в BQ существуют набор данных test_dataset c таблицами, которые сегментированы по датам: test_table_20200101 test_table_20200102 ……. test_table_20201231
  Выборка всех записей за дату 2020-01-01:
```
select * from test_dataset.test_table_20200101
```
  Выборка всех записей за месяц:
```
select * from test_dataset.test_table_202001*
```
  Выборка всех записей за год:
```
select * from test_dataset.test_table_2020*
```
  Выборка всех записей за весь период:
```
select * from test_dataset.test_table_*
```
  Выборка всех записей из всего набора данных:
```
select * from test_dataset.*
```
  Для ограничения запроса таким образом, чтобы он просматривал произвольный набор таблиц, можно использовать псевдостолбец _TABLE_SUFFIX в предложении WHERE. Псевдостолбец _TABLE_SUFFIX содержит значения, соответствующие подстановочному знаку *. Например, чтобы получить все данные за 1 и 5 января, можно выполнить следующий запрос:
```
select * from test_dataset.test_table_202001* where _TABLE_SUFFIX="01" or _TABLE_SUFFIX="05"
```
  Партиционирование и шардирование таблиц
  
  Партиционная таблица в BQ (partitioned table) — таблица, которая разделяется на сегменты (секции) по определённому признаку. Таблицы BigQuery можно разбивать на разделы по следующим признакам:
  - Ingestion Time. Таблицы разбиваются на разделы в зависимости от времени загрузки или времени поступления данных, которые содержат дополнительные зарезервированные поля _PARTITIONTIME, _PARTITIONDATE, хранящие дату создания записи.
  - Date/timestamp/datetime. Таблицы разбиты на разделы на основе столбца с типом timestamp, date или datetime. Если таблица партиционирована по столбцу с типом DATE, вы можете создавать партиции с ежедневной, ежемесячной или ежегодной гранулярностью. Каждый раздел содержит диапазон значений, где начало диапазона — это начало дня, месяца или года, а интервал диапазона составляет один день, месяц или год в зависимости от степени детализации разделения. Если таблица партиционирована по столбцам с типом TIMESTAMP или DATETIME, вы можете создавать разделы с любым типом гранулярности в единицах времени, включая HOUR.
  - Integer range. Таблицы разделены по целочисленному столбцу. BigQuery позволяет разбивать таблицы на разделы на основе определённого столбца INTEGER с указанием значений начала, конца и интервала. Запросы могут указывать фильтры предикатов на основе столбца секционирования, чтобы уменьшить объём сканируемых данных.
  Шард-таблица в BQ (sharded table) — совокупность таблиц, имеющих одну схему и сегментированных по датам. Другими словами, под шард-таблицами понимается разделение больших наборов данных на отдельные таблицы и добавление суффикса к имени каждой таблицы. Имена таблиц имеют шаблон tablename_YYMMDD, где YYMMDD — шаблон даты. В отличие от партиционированных таблиц, шард-таблица не имеет колонки, по которой будет происходить сегментация данных. Обращение к шард-таблицам возможно с помощью синтаксиса wildcard-table. Шард-таблицы и запрос к ним с помощью оператора UNION могут имитировать партиционирование.
  
  Партиционированные таблицы работают лучше, чем шард-таблицы. Когда вы создаёте шард-таблицы, BigQuery должен поддерживать копию схемы и метаданных для каждой таблицы с указанием даты. Кроме того, когда используются шард-таблицы с указанием даты, BigQuery может потребоваться для проверки разрешений для каждой запрашиваемой таблицы. Это влечёт за собой увеличение накладных расходов на запросы и влияет на производительность.
  
  Кластеризация таблиц
  
  Когда вы создаёте кластеризованную таблицу в BigQuery, данные таблицы автоматически сортируются на основе содержимого одного или нескольких столбцов в схеме таблицы. Указанные столбцы используются для размещения связанных данных. При кластеризации таблицы с использованием нескольких столбцов важен порядок указанных столбцов. Порядок указанных столбцов определяет порядок сортировки данных. Кластеризация может улучшить производительность определённых типов запросов, таких как запросы, использующие предложения фильтра, и запросы, которые объединяют данные. Когда данные записываются в кластеризованную таблицу, BigQuery сортирует данные, используя значения в столбцах кластеризации. Когда вы выполняете запрос, содержащий в фильтре данные на основе столбцов кластеризации, BigQuery использует отсортированные блоки, чтобы исключить сканирование ненужных данных. Вы можете не увидеть значительной разницы в производительности запросов между кластеризованной и некластеризованной таблицей, если размер таблицы или раздела меньше 1 ГБ.
  
  Пример из жизни. Работа с GDPR
  
  Нам пришлось столкнуться с задачей анонимизации данных по регламенту GDPR для Google Analytics (GA) в BigQuery. Задача заключалась в том, что каждый день в BigQuery импортировались данные из Google Analytics (GA). Поскольку в GA хранились персональные данные пользователей, для удалённых пользователей необходимо было очищать персональную информацию по регламенту GDPR. Данные, которые необходимо было анонимизировать, находились в поле с типом array. Аккаунт BigQuery, который нам предоставили, работал с ценовой моделью On-demand, в которой стоимость рассчитывалась из обработанных данных каждого запроса. Данные хранились в шард-таблицах. Google не рекомендует использовать шардированные таблицы и предлагает взамен партиционирование + кластеризацию, но, к сожалению, в Google Analytics (GA) это стандартная структура хранения данных. При экспорте данных из Google Analytics в BQ создаётся шард-таблица ga_sessions_, сегментированная по датам. В таблице находится порядка 16 полей, для нашей задачи необходимы были поля:
  - fullVisitorId (string) — уникальный идентификатор посетителя GA (также известный как идентификатор клиента)
  - customDimensions (array) — поле с типом array, содержит пользовательские данные, которые устанавливаются для каждого сеанса пользователя.
  В поле customDimensions хранятся значения (идентификаторы), которые нам необходимо анонимизировать. Значения хранятся под определёнными индексами в массиве customDimensions.
  
  Решение задачи
  
  Мы создали в BQ таблицу opted_out_visitors для хранения пользователей, информацию о которых необходимо анонимизировать.
  
  Схема таблицы:
  
  В данной таблице мы храним копию данных из ga_sessions_, которые подпадают под регламент GDPR.
  
  При получении запроса на удаление пользователя в нашей системе мы находили в таблицах GA данные пользователя для анонимизации и добавляли значения в таблицу opted_out_visitors:
```
INSERT INTO `dataset`.opted_out_visitors ( SELECT fullVisitorId, date, customDimensions FROM `dataset`.`ga_sessions_*`, UNNEST(customDimensions) AS cd WHERE cd.index=11 and cd.value="1111111")
```
  где cd.index=11 — индекс массива customDimensions, в котором хранятся идентификаторы пользователя, а cd.value=»1111111″ — идентификатор пользователя, данные которого необходимо почистить в таблицах GA. Собственно, в данной таблице мы имеем идентификатор пользователя в сеансе GA (fullVisitorId), данные пользователя (customDimensions) для анонимизации и дату, когда пользователь оставил за собой следы. После добавления данных в таблицу opted_out_visitors чистим значение, которое необходимо заменить.
```
UPDATE `dataset`.`opted_out_visitors` SET customDimensions = ARRAY( SELECT (index, IF(index = 3, "GDPR", value)) FROM UNNEST(customDimensions) ) where 1=1
```
  Осталось почистить значения в оригинальных таблицах GA. Для этого мы раз в 7 дней запускаем скрипт, который обновляет данные в ga_sessions для каждой даты в opted_out_visitors:
```
MERGE `dataset`.`ga_sessions_20200112` S USING `dataset`.`opted_out_visitors` O ON S.fullVisitorId = O.fullVisitorId WHEN MATCHED and O.date='20200112' THEN UPDATE SET S.customDimensions = O.customDimensions
```
  После этого очищаем таблицу opted_out_visitors.
  
  Многие читатели могут подумать, зачем так запариваться, создавать отдельную таблицу opted_out_visitors, хранить там идентификаторы из ga_sessions с анонимизированными данными, а потом всё это мержить раз в N дней. Дело в том, что каждая таблица ga_sessions занимает около 10 ГБ, и с каждым днём количество таблиц увеличивалось. Если бы мы выполняли анонимизацию данных каждый раз при поступлении запроса на удаление, мы получили бы огромные затраты в BigQuery. Но и с данным подходом нам не удалось добиться минимальных затрат. После анализа всех запросов мы выявили, что проблемным местом является запрос поиска анонимных данных и добавления в таблицу opted_out_visitors.
```
INSERT INTO `dataset`.opted_out_visitors ( SELECT fullVisitorId, date, customDimensions FROM `dataset`.`ga_sessions_*`, UNNEST(customDimensions) AS cd WHERE cd.index=11 and cd.value="1111111" )
```
  В данном запросе мы обращаемся к таблице ga_sessions_ за весь период, откуда получаем данные из столбца customDimensions (напомним, что этот столбец имеет тип array, который может хранить в себе любой объём данных). В итоге один запрос весил около 40 ГБ, так как BigQuery сканировал все таблицы ga_sessions_ и искал информацию в столбце customDimensions. Таких запросов в день было 50–80. Таким образом, меньше чем за день мы тратили весь бесплатный месячный трафик (1 ТБ).
  
  Оптимизация
  
  Поскольку проблема заключалась в чтении данных из customDimensions каждый раз, когда пользователь удалялся, было решено отказаться от постоянного обращения к данному столбцу при удалении пользователя и хранить только минимальную информацию, которая понадобится в дальнейшем для поиска и удаления анонимных данных. В итоге таблица opted_out_visitors была упразднена, и была создана новая таблица opted_out_users_id, в которую мы добавляем только идентификатор пользователя user_id и идентификатор, которым будем заменять анонимные данные.
  
  Каждый раз, когда к нам приходит запрос на удаления пользователя, в opted_out_users_id мы добавляем user_id и anonymized_id
```
INSERT INTO `dataset`.opted_out_users_id (user_id, anonymized_id) SELECT user_id, anonymized_id FROM (select '' as user_id, '' as anonymized_id) WHERE NOT (EXISTS ( SELECT 1 FROM `dataset`.opted_out_users_id WHERE `dataset`.opted_out_users_id.user_id = '' ) )
```
  Теперь при добавлении новой записи в opted_out_users_id мы тратим около 15 КБ, что значительно меньше, чем когда мы работали напрямую со столбцом customDimensions. Далее, раз в 7 дней нам необходимо запускать скрипт, который будет анонимизировать необходимые значения в ga_sessions_. Так как ga_sessions_ — это шард-таблицы, сегментированные по датам, мы не можем обратиться к таблицам через wildcard-синтаксис ga_sessions_* в операторах UPDATE, INSERT, MERGE. Придётся найти даты, где удалённые пользователи были замечены, и далее обращаться напрямую к каждой таблице ga_sessions_. Для этого перед анонимизацией данных мы находим все даты, где фигурируют удалённые пользователи.
```
SELECT ga.date, ARRAY_AGG(DISTINCT oous.user_id) AS accounts FROM `dataset.opted_out_users_id` AS oous LEFT JOIN ( SELECT `dataset.ga_sessions_*`.date, cd.index AS index, cd.value AS value FROM `dataset.ga_sessions_*`, unnest(`dataset.ga_sessions_*`.customDimensions) AS cd ) AS ga ON oous.user_id = ga.value AND ga.index = 3 GROUP BY ga.date
```
  Запрос возвращает всех пользователей, сгруппированных по датам. В данном запросе мы потребляем порядка 30 ГБ трафика, что вполне допустимо для нас, т. к. запрос выполняется раз в 7 дней. Ну что же, теперь можно выполнить анонимизацию.
```
UPDATE `dataset`.`ga_sessions_` as ga SET customDimensions=ARRAY( SELECT ( index, CASE WHEN index = THEN ac.anonymized_id WHEN index in cleanup_fields THEN null else value end ) FROM unnest(ga.customDimensions) ) FROM `dataset`.`opted_out_users_id` as ac WHERE ac.user_id=( SELECT value FROM unnest(ga.customDimensions) WHERE index=3 )
```
  В данном запросе для удалённых пользователей в столбце customDimensions производим замену user_id на anonymize_id, которые расположены в индексе с номером lookup_field, а для остальных индексов, которые расположены в cleanup_fields, чистим значения, устанавливая для них null. Запрос потребляет около 7 ГБ трафика, что также приемлемо для нас. В итоге в сумме все наши запросы не выходят за рамки бесплатного лимита 1 ТБ в месяц.
  
  Итоги
  
  Хочется сказать в конце, что BigQuery вполне достойное облачное хранилище данных. Для хранения небольшого количества данных можно уложиться в бесплатный лимит, но если ваши данные будут насчитывать терабайты и работать с данными вы будете часто, то и затраты будут высокими. С первого взгляда кажется, что 1 ТБ в месяц для запросов — это очень много, но подводный камень кроется в том, что BigQuery считает все данные, которые были обработаны во время выполнения запроса. И если вы работаете с обычными таблицами и попытаетесь выполнить какое-либо усечение данных в виде добавления WHERE либо LIMIT, то с грустью говорим вам, что BigQuery израсходует такой же объём трафика, как и при обычном запросе SELECT FROM. Однако если грамотно построить структуру вашей БД, вы сможете колоссально сэкономить свой бюджет в BigQuery.
  
  Наши рекомендации:
  - Избегайте SELECT *.
    Делайте запросы всегда только к тем полям, которые вам необходимы.
  - Избегайте в таблицах полей с типами данных record, array (repeated record).
    Запросы, в которых присутствуют данные столбцы, будут потреблять больше трафика, т. к. BigQuery придётся обработать все данные этого столбца.
    Спасибо @ekoblov за дельное замечание.
  - Старайтесь создавать секционированные таблицы (partitioned tables).
    Если грамотно разбить таблицу по партициям, в запросах, в которых будет происходить фильтрация по партиционированному полю, можно значительно снизить потребление трафика, т. к. BigQuery обработает только партицию таблицы, указанную в фильтре запроса.
  - Старайтесь добавлять кластеризацию в ваших секционированных таблицах.
    Кластеризация позволяет отсортировать данные в ваших таблицах по заданным столбцам, что также сократит потребление трафика. При использовании фильтрации по кластеризованным столбцам в запросах BigQuery обработает только тот диапазон данных, который включает значения из вашего фильтра.
  - Для подсчёта обработанных данных всегда используйте Cloud Console BigQuery.
    Когда вы вводите запрос в Cloud Console, валидатор запроса проверяет синтаксис запроса и предоставляет оценку количества прочитанных байтов. Эту оценку можно использовать для расчёта стоимости запроса в калькуляторе цен.
  - Используйте калькулятор для оценки стоимости хранения данных и выполнения запросов: https://cloud.google.com/products/calculator/.
    Для оценки стоимости запросов в калькуляторе необходимо ввести количество байтов, обрабатываемых запросом, в виде Б, КБ, МБ, ГБ, ТБ. Если запрос обрабатывает менее 1 ТБ, оценка составит 0 долларов, поскольку BigQuery предоставляет 1 ТБ в месяц бесплатно для обработки запросов по требованию. Аналогичные действия можно выполнить и для оценки хранения данных.
  - Блог компании Lesta Studio
  - SQL
  - Big Data
  - Google Cloud Platform
  Публичный набор данных BigQuery для Google Analytics 4
  
  Хотите начать изучение облачного хранилища Google BigQuery и написание SQL-команд для Google Analytics 4, но не имеете под рукой статистики, для которой можно было бы выполнять запросы? Не беда! Разработчики Google для всех желающих открыли публичный набор данных BigQuery для GA4.
  
  Теперь вы можете просматривать тестовые данные, экспериментировать с функциями и изучать возможности работы Google Analytics 4 в BigQuery. Все, что для этого нужно — стремление к изучению нового и время на тренировки!
  
  Данные, с которыми вы будете работать, принадлежат демонстрационному интернет-магазину Google Merchandise Store с настроенной электронной торговлей и активированной улучшенной статистикой. Однако этот публичный датасет следует отличать от тестового аккаунта GA4 для Google Merchandise Store, поскольку это другие данные. Публичный набор данных в BigQuery содержит обфусцированные данные за три месяца (с 1 ноября 2020 года по 31 января 2021 года), то есть немного искаженные и запутанные для анализа. Некоторые поля содержат плейсхолдеры, в том числе , NULL и » «. Из-за обфускации внутренняя согласованность набора данных может быть несколько ограничена.
  
  Сам набор данных в BigQuery называется ga4_obfuscated_sample_ecommerce. Для его использования вам понадобится доступ к проекту Google Cloud c включенным BigQuery API.
  
  Создание проекта в Google Cloud
  
  Чтобы создать новый проект, используйте любую учетную запись Gmail и перейдите в Google Gloud по ссылке. Если вы работаете с облаком Google впервые, то вас попросят указать страну и согласиться с условиями использованиями Google Cloud Platform:
  
  Начало работы с Google Cloud
  
  В связи с текущими событиями в мире Google приостановил работу для пользователей из России. На момент публикации данной статьи вы не сможете привязать свою банковскую карту, выпущенную на территории РФ, чтобы использовать все возможности Google Cloud Platform, а также выбрать страну Россия из списка. Поэтому выберете из списка наиболее подходящую и нажмите Agree and continue.
  
  На открывшейся странице Google Cloud в левом верхнем углу нажмите на Select a project:
  
  Select a project
  
  И создайте проект — NEW PROJECT:
  
  В поле Project name вы можете оставить автоматически добавленное название проекта, а можете указать свое собственное название, например BigQuery Public Dataset:
  
  В завершение нажмите кнопку Create (Создать). В вашем Google Cloud будет создан новый проект. Об этом будет свидетельствовать уведомление в правом верхнем углу (Notifications):
  
  Уведомление о создании проекта
  
  Включение BigQuery API
  
  Когда вы создали новый проект, API BigQuery включается автоматически. Но я рекомендую перепроверить это. Для этого в Google Cloud выберите свой проект из выпадающего списка:
  
  Выбор проекта в Google Cloud
  
  Затем перейдите по ссылке. Проверьте, что вы вносите изменения в выбранный проект и нажмите Next:
  
  Подтверждение изменений в проекте
  
  Включите BigQuery API:
  
  Включение BigQuery API
  
  На этом предварительная настройка вашего проекта в Google Cloud завершена.
  
  Платное и бесплатное использование
  
  Google Cloud и BigQuery являются платными инструментами инфраструктуры Google Cloud Platform. Но вы можете бесплатно использовать режим тестовой среды BigQuery (она же Песочница или Sandbox) с ограниченным набором функций BigQuery.
  
  Тестовая среда BigQuery
  
  Бесплатного тарифа должно быть достаточно для того, чтобы ознакомиться с набором данных и запустить примеры запросов. Для использования платного функционала вам необходимо активировать Billing, привязав активную банковскую карту к своей учетной записи. С нее в дальнейшем буду списываться деньги.
  
  Использование публичного набора данных
  
  Общедоступные наборы данных BigQuery по умолчанию доступны в консоли Google Cloud. Добавить публичный датасет с данными интернет-магазина Google к себе можно просто перейдя по ссылке.
  
  Вам откроется страница BigQuery, а в ней публичный проект со множеством наборов данных:
  
  Публичные датасеты BigQuery
  
  Один из них — это датасет с данными Google Merchandise Store ga4_obfuscated_sample_ecommerce для Google Analytics 4:
  
  Публичный набор данных BigQuery для Google Analytics 4
  
  Внутри датасета отображается 92 таблицы с названием events_ Это и есть статистика за три месяца (с 1 ноября 2020 года по 31 января 2021 года), где каждая таблица с events_ — конкретный день:
  
  Статистика в публичном датасете BigQuery для GA4 за три месяца
  
  Теперь вы можете выполнять запросы к этому набору данных.
  
  Примеры запросов
  
  Перед тем, как вы начнете писать SQL-команды для публичного набора данных BigQuery для Google Analytics 4, я рекомендую ознакомиться с другими материалами моего блога:
  
  Схема данных Google Analytics 4 в Google BigQuery
  
  Выполнение запросов нескольких таблиц GA4 events_ с _TABLE_SUFFIX
  
  Если вы не знаете, как правильно составить запрос или не уверены в корректности его написания, попробуйте инструмент ga4sql.com.
  
  Чтобы создать свой первый запрос для публичных данных BigQuery, нажмите на Query и выберите один из доступных способов:
  
  In new tab (В новой вкладке);
  
  In split tab (На вкладке рядом).
  
  Вам откроется окно запроса, в котором вы будете писать SQL-команды.
  
  Рассмотрим несколько примеров запросов.
  
  Количество уникальных событий, пользователей и дней
  
  Скопируйте нижеприведенный код и вставьте его на вкладку запроса:
  
  ClickHouse как альтернатива Google BigQuery
  
  Рассказываем, почему Spotify и Uber выбрали российскую разработку для своих проектов
  
  Что общего у Spotify, eBay, Uber, VK, Яндекс и Deutsche Bank?
  
  Все эти компании используют в своей работе ClickHouse — колоночную систему управления базами данных от Яндекса с открытым исходным кодом. В этой статье мы подробно рассмотрим плюсы и минусы ClickHouse, сравним его с Google BigQuery, а также расскажем о миграции на российскую СУБД.
  
  Что такое ClickHouse и чем он хорош?
  
  ClickHouse — это высокопроизводительная колоночная аналитическая СУБД с открытым исходным кодом, разработанная Яндексом.
  
  Предположим, что у вас несколько больших интернет-магазинов. Перед вами стоит задача управлять огромным объемом данных — параметрами визитов всех людей, которые заходили на ваш сайт и делали заказы. Как следствие, Яндекс Метрика и Google Analytics пробрасывают примерно 140 000 000 событий в вашу базу данных в месяц. И за весь период работы у вас накопились терабайты данных.
  
  Вообще, вы можете строить отчеты на любой системе (как платной, так и бесплатной) — вот только когда объем достигнет миллиарда строк, картина в любом случае перестанет быть радужной. Аналитические запросы в БД будут исполняться десятками минут.
  
  Для того чтобы ускорить такие процессы, были созданы особые СУБД — колоночные.
  Разумеется, в мире существуют и другие разновидности систем управления базами данных: реляционные, объектные, документные, сетевые, функциональные, их различные сочетания. Но именно колоночные обладают самой высокой скоростью работы с большими объемами информации (если сравнивать со строковыми).
  
  ClickHouse — колоночная СУБД, которая также является реляционной. Она была создана Яндексом для того, чтобы анализировать и читать данные по множеству значений максимально быстро: во время разработки код проекта постоянно оптимизировался с приоритетом на производительность.
  
  После релиза многие действительно были впечатлены возможностями масштабирования и скоростью работы ClickHouse. При использовании других популярных СУБД, запрос легко мог выполняться по несколько минут и пользователи уходили пить кофе. В ClickHouse же все происходило за секунды — решение от Яндекса «проглатывало» тысячи строк в секунду и петабайты данных с дисков.
  
  Читать:
  Как отключить подчеркивание ошибок в word