Создание таблиц в Google BigQuery через UI, также через оператор CREATE table

Есть несколько способов создать таблицу в BigQuery:

Через интерфейс в BigQuery
Запросом в редакторе BigQuery
Через API BigQuery
Через специальную командную строку BigQuery Cloud CLI

Мы рассмотрим два наиболее простых способа создания таблиц

Через интерфейс в BigQuery

Для начала нужно создать датасет (dateset). Кликаем по трем точкам справа проекта и клик по "Create dataset"

Вводим название датасета (Dataset ID), выбираем в каком регионе будут хранится наши данные ➜ Create Dataset

Созданные датасеты появятся в списке слева

Опять кликаем по трем точкам и нажимаем на "Create table"

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

пустая таблица (empty table) - создается таблица без данных, данные будут добавлены позже
загрузить таблицу (upload) - выбираете на компьютере файл, обычно используется csv, json и т.д.
загрузка из google sheets (drive) - загрузка по ссылке с доступом на редактирование из google таблиц

1. Пустая таблица

Вводим название таблицы

Чтобы добавить название полей таблицы кликаем на "ADD FIELD"

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

Задаем нужно количество полей нашей таблицы, например:

Нажимаем по кнопке создать таблицу (Create Table)

Слева в списке появилась наша созданная таблица

Пустая таблица готова, как добавить в нее данные разберемся в разделе о добавлении данных в таблицу

2. Загрузить таблицу

Загрузить таблицу можно весом не более 100мб, если больше то это можно сделать через API
Данные вводятся через запятую, например:

Загружаем файл локально, вводим название таблицы

Галочку, чтобы BigQuery автоматически распознал тип данных

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

Нажимаем по кнопке Create Table

Слева в панели BigQuery мы увидим нашу созданную таблицу и при клике на нее увидим структуру таблицы

При клике на вкладку "PREVIEW" мы увидим заполненную таблицу нашими данными

3. Загрузка таблицы из Google Sheets в BigQuery

В таблицах у нас есть та же таблица что и из примера выше

Настроим доступ к нашей таблице

Скопируем ссылку и вставим ее в BigQuery

Вводим название таблицы

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

Вводим в поля названия столбцов и тип

Открываем расширенные настройки и в поле "Header rows to skip" вводим "1" - это сколько строк сверху нужно пропустить, потому что вы вручную ввели название полей и их тип. Нажимаем кнопку Create table.

Таблица появилась слева в списке. Но это еще не все. Мы создали оболочку таблицы, без данных, как бы источник

Чтобы посмотреть данные из таблицы, напишем простой запрос


select * from `united-strategy-279207.new_dataset.table_test_3`

В результате запроса мы увидим нашу таблицу из google sheets

Чтобы создать таблицу, которую можно "потрогать" перейдем в настройки запроса BigQuery

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

После сохранения, еще раз запускаем наш запрос.

В результате у нас слева появится наша таблица. Нажимаем на "PREVIEW"

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

Запросом в редакторе BigQuery

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


CREATE TABLE IF NOT EXISTS `project.dataset.table_name`
(
  column_name STRING, -- для типа поля текст
  date DATE, -- для типа поля даты
  numeric_column_name INT64, -- или INTEGER для типа поля число
  numeric_column_name FLOAT64 -- или FLOAT для типа поля число с плавающей точкой (0.002)
);

Например, создадим таблицу с такими столбцами


CREATE TABLE IF NOT EXISTS `united-strategy-279207.new_dataset.table_test_4`
(
  date DATE, 
  source STRING, 
  medium STRING,
  conversions INT64, 
  revenue FLOAT64 
);

В результате слева появится пустая таблица BigQuery с заголовками столбцов и типом который мы задали в запросе.

При создании таблицы можно указать разделение на сегменты (партиции) с помощью оператора PARTITION BY по ссылке можно изучить подробнее на нашем сайте


CREATE TABLE IF NOT EXISTS `united-strategy-279207.new_dataset.table_test_4`
(
  date DATE, 
  source STRING, 
  medium STRING,
  conversions INT64, 
  revenue FLOAT64 
)
partition by (date)

И в итоге наша таблица будет отсортирована и как бы сгруппирована по дате

Добавление данных в таблицу

Команда INSERT INTO в SQL отвечает за добавление данных в таблицу
Покажем сразу на примере:
! При добавлении данных в каждый столбец таблицы не нужно указывать названия столбцов.


INSERT INTO `united-strategy-279207.new_dataset.table_test_4` 
   (date,source,medium,conversions,revenue) 
   VALUES 
      (DATE('05-07-2022'),'google','cpc',5,55.5),
      (DATE('06-07-2022'),'google','organic',4,123.3),
      (DATE('07-07-2022'),'google','cpm',8,10.3),
      (DATE('08-07-2022'),'yandex','cpc',1,3.4),
      (DATE('09-07-2022'),'google','cpc',3.333)

В результате в таблицу 4 добавились эти данные

Чтобы добавить еще данные в таблицу пишем такой же запрос


INSERT INTO `united-strategy-279207.new_dataset.table_test_4` 
   VALUES 
      (DATE('10-07-2022'),'facebook','cpc',1,3.33),
      (DATE('11-07-2022'),'facebook','cpm',0,0)

Для того чтобы обновить данные в таблице используем UPDATE SET. То есть мы заменяем строку с каналом = "organic" на строку в "SET"


 UPDATE `united-strategy-279207.new_dataset.table_test_4` 
  SET date = (DATE('30-07-2022'), source = 'direct', medium = 'none', conversions = 100, revenue = 1500.0
  WHERE medium = 'organic';