Команда INSERT INTO
Вставляет данные в таблицу.
Синтаксис
Вы можете указать список колонок для вставки с помощью (c1, c2, c3). Вы также можете использовать выражение с колонкой matcher такой как * и/или модификаторы такие как APPLY, EXCEPT, REPLACE.
Например, рассмотрим таблицу:
Если вы хотите вставить данные во все колонки, кроме колонки b, вы можете сделать это с помощью ключевого слова EXCEPT. Учитывая синтаксис выше, вы должны вставить столько же значений (VALUES (v11, v13)), сколько указываете колонок ((c1, c3)):
В этом примере мы видим, что вторая вставленная строка имеет заполненные колонки a и c переданными значениями, а b заполнена значением по умолчанию. Также возможно использовать ключевое слово DEFAULT для вставки значений по умолчанию:
Если список колонок не включает все существующие колонки, остальные колонки заполняются:
- Значениями, рассчитанными из выражений
DEFAULT, указанных в определении таблицы. - Нулями и пустыми строками, если выражения
DEFAULTне заданы.
Данные могут передаваться в INSERT в любом формате, поддерживаемом ClickHouse. Формат должен быть явно указан в запросе:
Например, следующий запрос по формату идентичен базовой версии INSERT ... VALUES:
ClickHouse удаляет все пробелы и переносы строки (если они есть) перед данными. При формировании запроса рекомендуется помещать данные на новую строку после операторов запроса, что важно, если данные начинаются с пробелов.
Пример:
Вы можете вставлять данные отдельно от запроса, используя клиент командной строки или HTTP интерфейс.
Если вы хотите указать SETTINGS для запроса INSERT, то вы должны сделать это перед оператором FORMAT, поскольку всё после FORMAT format_name считается данными. Например:
Ограничения
Если у таблицы есть ограничения, их выражения будут проверяться для каждой строки вставляемых данных. Если какое-либо из этих ограничений не будет удовлетворено — сервер выдаст исключение, содержащее название и выражение ограничения, и запрос будет остановлен.
Вставка результатов SELECT
Синтаксис
Колонки сопоставляются в соответствии с их положением в операторе SELECT. Однако их названия в выражении SELECT и таблице для INSERT могут различаться. При необходимости происходит приведение типов.
Никакие форматы данных, кроме формата Values, не позволяют устанавливать значения в выражения, такие как now(), 1 + 2 и так далее. Формат Values позволяет ограниченное использование выражений, но это не рекомендуется, так как в этом случае используется неэффективный код для их выполнения.
Другие запросы для изменения частей данных не поддерживаются: UPDATE, DELETE, REPLACE, MERGE, UPSERT, INSERT UPDATE.
Однако вы можете удалить старые данные, используя ALTER TABLE ... DROP PARTITION.
Клаузу FORMAT необходимо указывать в конце запроса, если оператор SELECT содержит таблицу-функцию input().
Чтобы вставить значение по умолчанию вместо NULL в колонку с неизменяемым типом данных, включите настройку insert_null_as_default.
INSERT также поддерживает CTE (общие табличные выражения). Например, следующие два оператора эквивалентны:
Вставка данных из файла
Синтаксис
Используйте синтаксис выше, чтобы вставить данные из файла или файлов, хранящихся на стороне клиента. file_name и type — это строковые литералы. Формат входного файла формат должен быть установлен в клаузе FORMAT.
Поддерживаются сжатые файлы. Тип сжатия определяется по расширению имени файла. Или его можно явно указать в клаузе COMPRESSION. Поддерживаемые типы: 'none', 'gzip', 'deflate', 'br', 'xz', 'zstd', 'lz4', 'bz2'.
Эта функциональность доступна в клиенте командной строки и clickhouse-local.
Примеры
Один файл с FROM INFILE
Выполните следующие запросы, используя клиент командной строки:
Результат:
Несколько файлов с FROM INFILE используя глобусы
Этот пример очень похож на предыдущий, но вставки выполняются из нескольких файлов с использованием FROM INFILE 'input_*.csv.
В дополнение к выбору нескольких файлов с *, вы можете использовать диапазоны ({1,2} или {1..9}) и другие глобусные замены. Все три варианта будут работать с приведённым выше примером:
Вставка с использованием таблицы-функции
Данные могут быть вставлены в таблицы, на которые ссылаются таблица-функции.
Синтаксис
Пример
Таблица-функция remote используется в следующих запросах:
Результат:
Вставка в ClickHouse Cloud
По умолчанию, сервисы ClickHouse Cloud предоставляют несколько реплик для высокой доступности. Когда вы подключаетесь к сервису, устанавливается соединение с одной из этих реплик.
После успешного выполнения INSERT данные записываются в основное хранилище. Однако может потребоваться некоторое время, чтобы реплики получили эти обновления. Поэтому, если вы используете другое соединение, которое выполняет запрос SELECT на одной из этих других реплик, обновленные данные могут еще не отображаться.
Можно использовать select_sequential_consistency, чтобы заставить реплику получить последние обновления. Вот пример запроса SELECT, использующего эту настройку:
Обратите внимание, что использование select_sequential_consistency увеличит нагрузку на ClickHouse Keeper (используемый ClickHouse Cloud внутренне) и может привести к снижению производительности в зависимости от нагрузки на сервис. Мы не рекомендуем включать эту настройку, если это не обязательно. Рекомендуемый подход — выполнять чтения/записи в одной сессии или использовать клиентский драйвер, который использует родной протокол (и, таким образом, поддерживает «липкие» соединения).
Вставка в реплицированную настройку
В реплицированной настройке данные будут видны на других репликах после того, как они были реплицированы. Данные начинают реплицироваться (скачиваться на другие реплики) сразу после INSERT. Это отличается от ClickHouse Cloud, где данные сразу записываются в общее хранилище, а реплики подписываются на изменения метаданных.
Обратите внимание, что в реплицированных настройках INSERT иногда может занять значительное время (в порядке одной секунды), так как это требует фиксации в ClickHouse Keeper для распределенного консенсуса. Использование S3 для хранения также добавляет дополнительную задержку.
Учет производительности
INSERT сортирует входные данные по первичному ключу и разбивает их на партиции по ключу партиции. Если вы вставляете данные в несколько партиций одновременно, это может значительно снизить производительность запроса INSERT. Чтобы избежать этого:
- Добавляйте данные в довольно больших партиях, например, по 100,000 строк за раз.
- Группируйте данные по ключу партиции перед загрузкой их в ClickHouse.
Производительность не снизится, если:
- Данные добавляются в реальном времени.
- Вы загружаете данные, которые обычно отсортированы по времени.
Асинхронные вставки
Возможно асинхронно вставлять данные небольшими, но частыми вставками. Данные из таких вставок объединяются в партии, а затем безопасно вставляются в таблицу. Чтобы использовать асинхронные вставки, включите настройку async_insert.
Использование async_insert или Buffer таблицы-система приводит к дополнительному буферизированию.
Большие или длительные вставки
Когда вы вставляете большие объемы данных, ClickHouse оптимизирует производительность записи через процесс, называемый "сжатие". Небольшие блоки вставленных данных в памяти объединяются и сжимаются в более крупные блоки перед записью на диск. Сжатие уменьшает накладные расходы, связанные с каждой операцией записи. В этом процессе вставленные данные будут доступны для запроса после того, как ClickHouse завершит запись каждой max_insert_block_size строк.
Смотрите также