Skip to main content
Version: 1.0.x

Ad-hoc Import

Column ingestion

Introduction

Column ingestion query updates values of a column in a partition of a table by taking the value of another column from another table

Example: Table db.target

DateIDNameIncome
2021-01-011A4
2021-01-012B5
2021-01-013C6

Table db.source

DateIDName
2021-01-011aa
2021-01-012bb
2021-01-014cc
ALTER TABLE db.target INGEST PARTITION '2021-01-01' COLUMNS Name KEY ID FROM db.source

Table db.target after execute the query

DateIDNameIncome
2021-01-011aa4
2021-01-012bb5
2021-01-0136
2021-01-014dd0

The Name value of row with ID = 3 can have value remain to C if the setting for target table ingest_default_column_value_if_not_provided is 0 by creating the table with this syntax

CREATE TABLE db.target
(
`date` Date,
`id` Int32,
`name` String
)
ENGINE = CnchMergeTree
PARTITION BY date
ORDER BY id
SETTINGS ingest_default_column_value_if_not_provided = 0

In that case, the result will be

DateIDNameIncome
2021-01-011aa4
2021-01-012bb5
2021-01-013C6
2021-01-014dd0

Syntax

ALTER TABLE db.target INGEST PARTITION xxx COLUMNS col1, col2 [KEY k1, k2] FROM db.temp_table

Implementation note

The main execution of ingest column will be done in worker