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

12
169
Материалы для скачивания
978.32 Kb

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

Содержание

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

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

Регистрация в  в Google Cloud Platform

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

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

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

Сознание набора данных в Google BigQuery

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

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

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

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

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

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

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

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

Создание таблицы в Google BigQuery

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

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

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

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

Схема таблицы в Google BigQuery

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

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

Тип данных в Google BigQuery

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

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

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

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

  1. Нажмите кнопку «Добавить поле» и заполните название, тип и режим для каждого столбца:
  1. Введите схему таблицы в виде массива JSON с помощью переключателя «Редактировать как текст»:

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

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

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

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

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

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

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

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

    
#legacySQL
Select
date,
order_id,
order___________ as order_type, -- новое название поля
product_id
from [project_name:dataset_name.owoxbi_sessions_20190314]
    
    
#standardSQL
Select
* EXCEPT (orotp, ddat),
orotp as order_id,
ddat as date
from `project_name.dataset_name.owoxbi_sessions_20190314`
    

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Импорт данных в Google BigQuery

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

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

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

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

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

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

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

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

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

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

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

  1. Откройте Google Sheets и на вкладке «Дополнения» выберите «OWOX BI BigQuery Reports → Add a new report»:
  1. Затем укажите свой проект в Google BigQuery и выберите «Add new query».
  2. В новом окне вставьте свой SQL-запрос. Это может быть запрос, который выгружает данные из таблицы в BigQuery, или запрос, который вытягивает и рассчитывает необходимые данные.
  3. Переименуйте запрос, чтобы его было легко найти, и запустите, нажав кнопку «Save & Run».

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

  1. На вкладке «Дополнения» выберите «OWOX BI BigQuery Reports → Schedule report»:
  1. В открывшемся окне задайте время и частоту обновления отчета и нажмите «Save»:

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

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

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

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

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

Вас также могут заинтересовать