Структура данных BigQuery в Google: как начать работу с облачным хранилищем

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

Чтобы использовать Google BigQuery, вам необходимо создать проект в Google Cloud Platform (GCP). После регистрации вы получите доступ ко всем продуктам Cloud Platform в течение бесплатного пробного периода и 300 долларов США, которые можно потратить на эти продукты в течение следующих 12 месяцев.

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

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

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

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

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

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

• Вручную создайте пустую таблицу и настройте для нее схему данных
• Создайте таблицу, используя результат ранее рассчитанного SQL-запроса.
• Загрузите файл со своего компьютера (в формате CSV, AVRO, JSON, Parquet, ORC или Google Sheets)
• Вместо загрузки или потоковой передачи данных вы можете создать таблицу со ссылкой на внешний источник: Cloud Bigtable, Cloud Storage или Google Drive.

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

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

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

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

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

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

Имена столбцов
В названии столбца нужно указать параметр, за который отвечает каждый столбец: дата, user_id, товары и т. д. Заголовки могут содержать только латинские буквы, цифры и символы подчеркивания (максимум 128 символов). Идентичные имена полей не допускаются, даже если они различаются по регистру.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Установите надстройку BigQuery Reports в свой браузер.
2. Откройте файл данных в Google Sheets и во вкладке « Дополнения » выберите OWOX BI BigQuery Reports → Загрузить данные в BigQuery.
3. В открывшемся окне выберите свой проект и набор данных в BigQuery и введите желаемое имя для таблицы. Также выберите поля, значения которых вы хотите загрузить. По умолчанию тип всех полей — STRING, но мы рекомендуем выбирать тип данных в зависимости от контекста (например, для полей с числовыми идентификаторами выберите INTEGER, для цен — FLOAT):
   

4. Нажмите кнопку «Начать загрузку», и ваши данные будут загружены в Google BigQuery.

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

1. Откройте Google Таблицы. Во вкладке « Дополнения » выберите OWOX BI BigQuery Reports → Добавить новый отчет:
   

2. Затем введите свой проект в Google BigQuery и выберите Добавить новый запрос.
3. В новом окне вставьте свой SQL-запрос. Это может быть запрос, загружающий данные из таблицы в BigQuery, или запрос, который извлекает и вычисляет необходимые данные.
4. Переименуйте запрос, чтобы его было легко найти и запустить, нажав кнопку «Сохранить и выполнить».
   

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

1. Во вкладке Add-ons выберите OWOX BI BigQuery Reports → Schedule report :
   

2. В открывшемся окне задайте время и периодичность обновления отчета и нажмите Сохранить :
   

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

• Настройте сквозную аналитику и узнайте реальную отдачу от вашего маркетинга с учетом офлайн-заказов, возвратов и всех шагов пользователя на пути к покупке;
• Создавать отчеты на полных несемплированных данных с любыми параметрами и показателями;
• Оценить каналы привлечения клиентов с помощью когортного анализа;
• Узнайте, как ваша интернет-реклама влияет на офлайн-продажи;
• Сократите долю расходов на рекламу, продлите жизненный цикл клиентов и увеличьте LTV вашей клиентской базы в целом;
• Сегментируйте клиентов в зависимости от их активности и персонализируйте общение с ними.

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