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

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

  1. Через интерфейс в BigQuery
  2. Запросом в редакторе BigQuery
  3. Через API BigQuery
  4. Через специальную командную строку 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';
Made on
Tilda