Учебное пособие по 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», но обычно система определяет формат файла автоматически.
- Имя таблицы – введите имя таблицы.
- Установите флажок автоопределения схемы «Auto detect».
- Нажмите «Create table».
Так выглядит основной поток. Кроме того, вы можете определить параметры раздела (для разделения таблицы на более мелкие сегменты), параметры кластера (для организации данных на основе содержимого указанных столбцов), а также настроить дополнительные параметры «Advanced options».
Вот как выглядит ваша таблица, загруженная в BigQuery:
Примечание. Функция предварительного просмотра таблиц позволяет предварительно просматривать таблицы, хранящиеся в BigQuery. Например, когда вы загружаете CSV, он сохраняется в BigQuery – вы увидите лист предварительного просмотра. Однако, когда вы извлекаете данные из Google Таблиц, это соединение в режиме реального времени, поскольку BigQuery сканирует Google Таблицы каждый раз, когда вы запрашиваете их. В этом случае предварительный просмотр будет недоступен.
Импортировать данные из Google Таблиц в BigQuery
Большинство из вас, вероятно, хотели бы узнать больше об импорте таблиц из Google Sheets в BigQuery. Рабочий процесс очень похож, но с некоторыми изменениями. Нажмите кнопку «Create table» и:
- Выберите источник – «Drive»
- Выберите Drive URI – вставьте URL-адрес вашей электронной таблицы Google Таблиц.
- Формат файла – выберите «Google Sheets».
- Диапазон листов – укажите лист и диапазон данных для импорта. Если вы оставите это поле пустым, BigQuery будет извлекать данные с первого листа вашей электронной таблицы.
- Имя таблицы – введите имя таблицы.
- Установите флажок автоопределения схемы «Auto detect».
- Нажмите «Create table».
Возможно, вам будет интересно как настроить дополнительные параметры «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 ГБ)
-
JSON файл
- Скачать на свое устройство (до 16К строк)
- Скачать на Google Диск (до 1 ГБ)
- BigQuery таблица
- Google таблицы (до 16K строк)
- Копировать в буфер обмена (до 16K строк)
В качестве примера выберем параметр таблицы BigQuery. Вам нужно будет выбрать проект и набор данных, а также назвать свою таблицу.
Нажмите «SAVE», и готово!
Бонус: как BigQuery хранит данные
В отличие от традиционных реляционных баз данных, в которых данные хранятся построчно, BigQuery хранит столбец за столбцом. Это означает, что для хранения каждого столбца используется отдельный файловый блок. Этот столбчатый формат, называемый Capacitor, позволяет BigQuery достичь очень высокой пропускной способности, что имеет решающее значение для онлайн-аналитической обработки.
Архитектура BigQuery
В безсерверной архитектуре BigQuery ресурсы для хранения и вычислений разделены. Это позволяет загружать данные любого размера в хранилище и сразу же приступать к их анализу. Вот инфраструктурные технологии, благодаря которым это происходит:
- Colossus – отвечает за хранение. Это глобальная система хранения, оптимизированная для чтения больших объемов структурированных данных, а также для обработки репликации, восстановления и распределенного управления.
- Dremel – отвечает за вычисления. Это – мультитенантный кластер, который превращает SQL-запросы в деревья выполнения. У этих деревьев есть листья, которые называются слотами, и один пользователь может получить тысячи слотов для выполнения своих запросов.
- Jupiter – отвечает за перемещение данных между хранилищем (Colossus) и вычислениями (Dremel). Это – петабитная сеть, которая перемещает данные из одного места в другое, и делает это очень быстро.
- Borg – отвечает за распределение аппаратных ресурсов. Это система управления кластером для выполнения сотен тысяч заданий в BigQuery.
Узнать больше о BigQuery
Давайте будем честными – наша цель заключалась не в том, чтобы написать идеальное руководство по BigQuery, а в том, чтобы дать вам ответы на вопросы, которые есть у каждого новичка, когда он открывает для себя новый инструмент или технологию. Мы уверены, что после прочтения у вас, вероятно, возникнут дополнительные вопросы, но вам придется искать на них ответы самостоятельно.
Основным источником информации является официальная документация BigQuery, в которой вы найдете обширную базу знаний об использовании BigQuery. Недостатком этого источника является то, что он абсолютно огромен и иногда чрезмерно структурирован (как и большинство документации Google).
Со своей стороны, мы постараемся охватить другие темы, связанные с BigQuery, чтобы прояснить некоторые сложные моменты. Удачи вам!
