Как эффективно создать новую таблицу из существующей в BigQuery: пошаговая инструкция и лучшие практики?

04.11.2025
62020

3 мин
29

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

Способы создания таблицы из другой таблицы в BigQuery

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

Использование SQL запроса CREATE TABLE AS SELECT (CTAS)

Самый распространенный и гибкий способ – использование SQL-запроса CREATE TABLE AS SELECT (CTAS). Этот запрос позволяет создать новую таблицу и заполнить её данными, полученными из SELECT-запроса. Синтаксис выглядит следующим образом:

CREATE OR REPLACE TABLE `project_id.dataset_name.new_table_name` AS
SELECT
    column1, column2, ...
FROM
    `project_id.dataset_name.existing_table_name`
WHERE
    condition;

CREATE OR REPLACE TABLE: Создает новую таблицу или заменяет существующую.
project_id.dataset_name.new_table_name: Полное имя новой таблицы, включая project ID, dataset ID и имя таблицы.
SELECT ... FROM ... WHERE: SQL-запрос, определяющий данные, которые будут скопированы в новую таблицу. WHERE позволяет применять фильтры.

Обзор инструментов: BigQuery UI и bq CLI

Кроме SQL-запросов, можно использовать BigQuery UI и bq CLI (Command Line Interface) для копирования таблиц. BigQuery UI предоставляет графический интерфейс, удобный для простых операций, в то время как bq CLI позволяет автоматизировать процесс копирования с помощью скриптов.

BigQuery UI: В BigQuery UI можно скопировать таблицу, выбрав опцию «Copy table» в меню таблицы. Можно указать целевой проект, набор данных и имя новой таблицы. UI не поддерживает копирование с фильтрацией.
bq CLI: bq CLI предоставляет команду bq cp для копирования таблиц. Пример:

bq cp ‘project_id:dataset_name.existing_table’ ‘project_id:dataset_name.new_table’ «`

bq CLI также поддерживает копирование данных между регионами. Этот вариант эффективен, когда необходимо перенести большие объемы данных.

Создание таблицы с идентичной структурой

Иногда требуется создать новую таблицу с той же структурой (схемой), что и у существующей, но без данных. Это полезно, например, для создания тестовой таблицы или для подготовки таблицы к загрузке данных.

Копирование структуры таблицы без данных

Для копирования только структуры можно использовать CTAS с предложением WHERE FALSE, которое исключает все строки из исходной таблицы:

CREATE OR REPLACE TABLE `project_id.dataset_name.new_table_name` AS
SELECT

    * EXCEPT (column_to_exclude) -- optional: excluding columns
FROM
    `project_id.dataset_name.existing_table_name`
WHERE FALSE;

Этот запрос создаст новую таблицу с той же схемой, что и у existing_table_name, но без каких-либо данных.

Копирование структуры таблицы с помощью BigQuery UI

BigQuery UI не имеет встроенной функции для копирования только структуры таблицы. Однако, можно сначала скопировать таблицу целиком, а затем очистить данные в новой таблице с помощью запроса DELETE FROM. Этот способ не оптимален, но подходит для небольших таблиц.

Копирование данных и фильтрация

Часто возникает необходимость скопировать только часть данных из существующей таблицы, например, данные за определенный период или данные, соответствующие определенным критериям.

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

Для копирования всех данных из одной таблицы в другую можно использовать CTAS без предложения WHERE:

CREATE OR REPLACE TABLE `project_id.dataset_name.new_table_name` AS
SELECT

    * -- specify columns for better performance
FROM
    `project_id.dataset_name.existing_table_name`;

Рекомендуется явно указывать столбцы в SELECT запросе вместо использования *. Это улучшает производительность и делает запрос более понятным.

Копирование данных с применением фильтров WHERE

Для копирования данных с фильтрацией используется предложение WHERE в SELECT запросе:

CREATE OR REPLACE TABLE `project_id.dataset_name.new_table_name` AS
SELECT
    column1, column2, ...
FROM
    `project_id.dataset_name.existing_table_name`
WHERE
    date_column >= '2023-01-01' AND category = 'example';

Этот запрос скопирует только те строки, в которых значение столбца date_column больше или равно ‘2023-01-01’, а значение столбца category равно ‘example’. Важно убедиться, что столбцы, используемые в WHERE условии, индексированы для ускорения фильтрации.

Практические примеры и лучшие практики

Сценарии использования: резервное копирование и создание тестовых сред

Резервное копирование: Создание резервной копии таблицы перед выполнением операций, которые могут привести к потере данных. Можно создать ежедневные резервные копии, используя CTAS и планировщик запросов BigQuery.
Создание тестовых сред: Копирование части данных в тестовую таблицу для разработки и тестирования новых запросов и процедур. Фильтрация данных позволяет создать репрезентативную, но меньшую по объему тестовую выборку.
Трансформация данных: Создание новой таблицы с измененной структурой или преобразованными данными. Например, можно создать таблицу с агрегированными данными на основе исходной таблицы.

Оптимизация производительности при копировании больших таблиц

При копировании больших таблиц важно учитывать следующие факторы для оптимизации производительности:

Указывать столбцы явно: Вместо использования SELECT * явно указывать необходимые столбцы. Это уменьшает объем данных, которые нужно обработать.
Использовать партиционирование и кластеризацию: Если исходная таблица партиционирована или кластеризована, следует создать новую таблицу с аналогичным партиционированием и кластеризацией. Это позволит оптимизировать запросы к новой таблице.
Выбирать подходящий тип хранения: Рассмотреть возможность использования различных классов хранения (например, Coldline или Archive) для старых данных, которые не требуют частого доступа.
Мониторинг стоимости запросов: BigQuery взимает плату за обработку данных. Важно отслеживать стоимость запросов и оптимизировать их для снижения затрат. Используйте режим Dry Run для оценки стоимости запроса перед его выполнением.
Использовать CREATE OR REPLACE TABLE с осторожностью: При использовании CREATE OR REPLACE TABLE существующая таблица будет полностью заменена, что может привести к потере данных, если операция прервется. Рассмотрите возможность использования временных таблиц или резервных копий перед заменой таблицы.

Заключение

В этой статье мы рассмотрели различные способы создания новой таблицы из существующей в BigQuery. Выбор оптимального способа зависит от конкретных требований и размера таблицы. Использование CTAS с фильтрацией, BigQuery UI и bq CLI позволяет гибко решать задачи копирования и трансформации данных. Важно учитывать лучшие практики и оптимизировать производительность при работе с большими таблицами для снижения затрат и повышения эффективности.