How to Import CSV Into ClickHouse

ClickHouse documents several ways to move a CSV into a table. The command line client takes a file on its standard input with FORMAT CSVWithNames. A SQL statement reads one from disk with INSERT INTO sometable FROM INFILE 'data.csv' FORMAT CSV. The file() table function queries a CSV where it sits and infers its types. The Cloud console takes a JSON, CSV or TSV file through the browser, and ClickPipes pulls from S3, GCS, Postgres and Kafka. Every one of those starts from a seat that already holds the credentials. Your customer sits outside that seat, holding a spend export a media agency sent them.

What ClickHouse checks when a row lands

ClickHouse checks almost nothing on the way in, and it says so. The deduplication guide states that ClickHouse does not check for an existing primary key before inserting a row. Its own comparison with BigQuery states that ClickHouse does not enforce uniqueness for a table's primary key column values, and that ClickHouse currently does not support foreign key constraints. So a repeated line lands twice, and a campaign id nobody recognises lands once.

One thing is checked. A CONSTRAINT ... CHECK declared on the table is checked for every row of an INSERT, and a violated one raises an exception naming the constraint and the expression. That exception fails the statement, so it guards the table without telling anybody which line of the file caused it.

Everything else has to happen before the insert. In how to import CSV into BigQuery a single MERGE settles which row is new and which row is a correction. Here the two places left are the browser and the table engine.

The file that arrives

The agency exports the month from its own planning tool, and it looks like this.

1Date,Channel,Campaign ID,Campaign,Spend,Impr.,Clicks,Ccy201/09/2026,Meta,CMP-4471,Back to school,"1.240,50",84 213,1 902,EUR301/09/2026,Meta,CMP-4471,Back to school,"1.240,50",84 213,1 902,EUR402/09/2026,meta ads,CMP-4489,Autumn drop,"980,00",51 004,743,EUR515/09/2026,OOH,CMP-5102,Station takeover,"12.000,00",0,0,EUR⋮407 rows not shown41330/09/2026,Google Ads,CMP-5230,Brand always-on,"2.415,80",118 902,3 044,EUR

Two headers are shortened. Impr. means impressions and Ccy means the currency code. The first two lines are the same line twice. The money carries a dot for thousands and a comma for decimals. The counts carry spaces for thousands. The dates run day first, and the first two of them could be read either way. The channel column says Meta on one line and meta ads on the next, and OOH further down.

The person drops that file into the importer inside your app. Updog Importer reads it in the browser, matches the headers to your schema, checks the campaign ids against the warehouse, and puts every value in front of them beside the spend you already hold. Your onComplete handler receives each row with a verdict on it. The handler posts the rows to a route you own, and that route inserts them.

No Updog server stands between the browser and ClickHouse.

The table the rows land in

One table holds the spend, and the engine decides what a second import means.

create table analytics.ad_spend
(
  account_id    String,
  spend_date    Date,
  channel       LowCardinality(String),
  campaign_id   String,
  campaign_name String,
  spend         Decimal(12, 2),
  impressions   UInt32,
  clicks        UInt32,
  currency      LowCardinality(String),
  source_file   String,
  version       DateTime64(3) default now64(3),
  is_deleted    UInt8 default 0,
  constraint spend_not_negative check spend >= 0
)
engine = ReplacingMergeTree(version, is_deleted)
partition by toYYYYMM(spend_date)
order by (account_id, spend_date, channel, campaign_id);

ReplacingMergeTree removes duplicate entries with the same sorting key value, and uniqueness of rows comes from the ORDER BY section. The PRIMARY KEY clause has no say in it. With a version column named, the row carrying the maximum version survives. So an update is an insert of the same four key columns with a fresh version, and now64(3) fills that column at insert time because the browser has no business stamping it.

is_deleted is the other half. ClickHouse describes it as the column that says whether a row is the state or is to be deleted, 1 for deleted and 0 for state, typed UInt8. It can only be enabled when a version column is used. So a delete arrives as a row like any other.

The rest of the table is ordinary warehouse work. channel and currency take LowCardinality, which ClickHouse suggests for a cardinality under ten thousand. spend_date is a Date, two bytes, covering 1970-01-01 to 2149-06-06. The CHECK constraint refuses a negative spend, and the schema in the browser refuses it first.

The schema in Updog Importer

The columns array is the same table written for the person looking at the file.

import type { DataEditorColumn } from "@updog/data-editor";

const CHANNELS = ["Meta", "Google Ads", "TikTok", "Out of home"];
const CURRENCIES = ["EUR", "GBP", "USD"];

export const columns: DataEditorColumn[] = [
  {
    id: "spendDate",
    title: "Spend date",
    size: 140,
    editor: { type: "date" },
    validators: [
      { type: "required" },
      { type: "date" },
    ],
  },
  {
    id: "channel",
    title: "Channel",
    size: 150,
    editor: { type: "select", options: CHANNELS, enableCustomValue: false },
    validators: [{ type: "oneOf", values: CHANNELS }],
  },
  {
    id: "campaignName",
    title: "Campaign name",
    size: 200,
    transformer: (value) => String(value).trim(),
  },
  {
    id: "spend",
    title: "Spend",
    size: 120,
    editor: { type: "number" },
    validators: [
      { type: "number", min: 0, decimalPlaces: 2 },
    ],
  },
  {
    id: "impressions",
    title: "Impressions",
    size: 130,
    editor: { type: "number" },
    validators: [{ type: "number", decimalPlaces: 0 }],
  },
  {
    id: "clicks",
    title: "Clicks",
    size: 110,
    editor: { type: "number" },
    validators: [{ type: "number", decimalPlaces: 0 }],
  },
  {
    id: "currency",
    title: "Currency",
    size: 120,
    editor: { type: "select", options: CURRENCIES, enableCustomValue: false },
    validators: [{ type: "oneOf", values: CURRENCIES }],
  },
];

Each editor earns its place against what arrives. The number editor reads 1.240,50 as 1240.50, because a value carrying both separators is proof on its own, the last one being the decimal. That single value settles the punctuation for the whole file. Space grouping needs no verdict at all, so 84 213 loses its space and lands as 84213. The date editor turns 15/09/2026 into 2026-09-15, and since 15 is above 12 the file settles day first, so the two rows that could have gone either way land as 2026-09-01 and 2026-09-02. A file where every date part stays at or below 12 settles on the runtime locale instead, which is the case worth sending your customer a template for.

The select editors hold the channel and the currency to a fixed list, and a value nobody maps is dropped from the row.

The check against the campaigns you already hold

A campaign id is a reference to another table, and ClickHouse holds no foreign keys. So the check runs while the person is still looking at the file.

{
  id: "campaignId",
  title: "Campaign id",
  size: 150,
  validators: [
    { type: "required" },
    { type: "regex", pattern: "^CMP-\\d{4}$" },
    {
      type: "asyncFunction",
      fn: async (cells, onChunk, signal) => {
        for (let offset = 0; offset < cells.length; offset += 500) {
          if (signal.aborted) return;
          const batch = cells.slice(offset, offset + 500);

          const response = await fetch("/api/spend/campaigns/known", {
            method: "POST",
            headers: { "Content-Type": "application/json" },
            body: JSON.stringify({
              ids: batch.map((cell) => String(cell.value)),
            }),
          });
          const known = new Set<string>(await response.json());

          onChunk(
            batch.flatMap((cell, index) =>
              known.has(String(cell.value))
                ? []
                : [{
                    index: offset + index,
                    error: {
                      level: "error",
                      message: "No campaign with this id",
                    },
                  }],
            ),
          );
        }
      },
    },
  ],
}

asyncFunction receives every affected cell for the column in one call, each with its own row for context, and reports failures by index. Batching is yours, which is why the loop splits the work into five hundred ids a request and streams verdicts back through onChunk as they arrive. Opening a file triggers a full check of the column, so the first sweep carries one cell per row and later sweeps carry only what changed. Async validators run after every sync validator, whatever their position in the array, so the regex rejects a malformed id before the warehouse is asked about it.

The endpoint answers out of ClickHouse itself.

import { createClient } from "@clickhouse/client";

export const clickhouse = createClient({
  url: process.env.CLICKHOUSE_URL,
  username: process.env.CLICKHOUSE_USER,
  password: process.env.CLICKHOUSE_PASSWORD,
});

const KNOWN_CAMPAIGNS =
  "select campaign_id from analytics.dim_campaign " +
  "where account_id = {account: String} " +
  "and campaign_id in {ids: Array(String)}";

app.post("/api/spend/campaigns/known", async (request, response) => {
  const session = await getVerifiedSession(request);
  if (!session) return response.status(401).json({ message: "Not signed in" });

  const ids = request.body.ids.slice(0, 500).map(String);
  const result = await clickhouse.query({
    query: KNOWN_CAMPAIGNS,
    query_params: { account: session.accountId, ids },
    format: "JSONEachRow",
  });

  const rows = await result.json<{ campaign_id: string }>();
  response.json(rows.map((row) => row.campaign_id));
});

const CURRENT_SPEND =
  "select spend_date as spendDate, channel, " +
  "campaign_id as campaignId, campaign_name as campaignName, " +
  "spend, impressions, clicks, currency " +
  "from analytics.ad_spend final " +
  "where account_id = {account: String} " +
  "and is_deleted = 0 " +
  "and spend_date >= {from: Date} " +
  "order by spend_date, channel, campaign_id";

app.get("/api/spend/current", async (request, response) => {
  const session = await getVerifiedSession(request);
  if (!session) return response.status(401).json({ message: "Not signed in" });

  const result = await clickhouse.query({
    query: CURRENT_SPEND,
    query_params: { account: session.accountId, from: "2026-09-01" },
    format: "JSONEachRow",
  });

  response.json({ rows: await result.json() });
});

query_params keeps the ids out of the SQL text, and the account comes off the session on your server. The second route is what fills the editor when it opens. It reads with FINAL, which is how ClickHouse asks for the deduplicated view at query time. It also filters is_deleted = 0, since a delete row survives the merge until a cleanup pass removes it. The select aliases the warehouse's snake_case to the column ids, because loadData matches a row to a column by id.

CMP-5102 comes back unknown, so the person sees the error on the row and either fixes the id or removes the line. That is the check a database with foreign keys would have run for you, moved to the one moment somebody is still there to answer it.

The mount

The props tie the file, the schema and the warehouse together.

<DataEditor<Spend>
  apiKey="your-license-key"
  open={open}
  onClose={closeEditor}
  columns={columns}
  primaryKey={["spendDate", "channel", "campaignId"]}
  enableDeleteRow="all"
  blockSubmitOnError
  synonyms={{
    columns: { currency: ["ccy", "curr", "currency code"] },
    values: { "Out of home": ["ooh", "outdoor", "billboard"] },
  }}
  loadData={async (onChunk) => {
    const response = await fetch("/api/spend/current");
    const body = await response.json();
    if (!response.ok) throw new Error(body.message);
    onChunk(body.rows, { source: "ClickHouse", done: true });
  }}
  onComplete={onComplete}
/>

Ccy reaches currency through the synonyms table and through nothing else. Fuzzy matching scores it at zero. Three characters fall under the four-character floor the contains tier needs, ccy shares no whole word with currency, and the length gap of five exceeds the two edits allowed at that length. A match needs sixty. The same table carries ooh on the value side, where Out of home stands too far away to be reached alone. meta ads needs no help, since meta sits inside it and the contains tier scores eighty. Whatever the person fixes by hand comes back on the result as learnedSynonyms, ready to store and feed back next time.

primaryKey takes three columns, because one campaign spends on one channel on one day. The warehouse key has a fourth part, and account_id never reaches the browser at all. enableDeleteRow="all" lets the person drop a line the agency invoiced by mistake, and blockSubmitOnError keeps submit disabled while any row carries an error, the unknown campaign id included.

Every snippet here is React. Those props reach Vue, Angular and Svelte through the web component build. The install and the modal wiring beneath this mount live in how to import a CSV file into a React app.

The result on submit

On submit, Updog Importer hands your handler every row grouped by source, each one carrying isNew, isChanged, isDeleted and isValid. The flags are independent, so one row can be new, changed and deleted at once. Rows that merged onto warehouse rows arrive under the ClickHouse source as changed. Rows the file added arrive under the file's own name as new. A warehouse row nobody touched never appears.

import type { DataEditorResult, ResultRow } from "@updog/data-editor";

const CHUNK_SIZE = 50_000;

const toRow = (entry: ResultRow<Spend>, sourceFile: string) => {
  if (entry.isDeleted && entry.isNew) return [];
  return [{
    spend_date: entry.row.spendDate,
    channel: entry.row.channel,
    campaign_id: entry.row.campaignId,
    campaign_name: entry.row.campaignName,
    spend: entry.row.spend,
    impressions: entry.row.impressions,
    clicks: entry.row.clicks,
    currency: entry.row.currency,
    source_file: sourceFile,
    is_deleted: entry.isDeleted ? 1 : 0,
  }];
};

const onComplete = useCallback(async (result: DataEditorResult<Spend>) => {
  const rows = result.sources.flatMap((source) => {
    return source.rows.flatMap((entry) => toRow(entry, source.sourceName));
  });

  const batchId = crypto.randomUUID();

  for (let start = 0; start < rows.length; start += CHUNK_SIZE) {
    const written = await fetch("/api/spend/rows", {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({
        batchId,
        offset: start,
        rows: rows.slice(start, start + CHUNK_SIZE),
      }),
    });
    if (!written.ok) throw new Error((await written.json()).message);
  }
}, []);

Three flags become one column. An insert and an update are the same payload, since the engine settles which version wins, and a delete is the same payload with is_deleted set to 1. A row the person added and then deleted goes nowhere.

The chunk size comes from ClickHouse's own guidance, which asks for batches of at least a thousand rows and ideally between ten thousand and a hundred thousand. Fewer and larger inserts write fewer parts, and many small ones end in the TOO_MANY_PARTS error. Fifty thousand sits inside that band and stays well under max_insert_block_size, which is around a million rows and is the point where a single insert stops being one block. An insert of one block into one partition of a MergeTree table is transactional, and this table partitions by month, so a chunk covering two months commits per partition.

Throw when a route answers with a failure. Updog holds submit until your promise resolves, then empties the editor. A handler that traps the error and returns counts as a finished import, and the month clears the grid unwritten. A thrown error keeps the grid as it stands, with every match and hand correction on it. The person submits again on rows that never left the screen.

The whole loop runs behind a spinner in the confirm dialog. Anything on the result worth keeping gets copied inside the handler, since the editor drops its rows, its sources, its history and its learned synonyms once the promise resolves.

The route that inserts

The route holds the credentials, and the browser stops there.

import { ClickHouseError } from "@clickhouse/client";

const UUID = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/;

app.post("/api/spend/rows", async (request, response) => {
  const session = await getVerifiedSession(request);
  if (!session) return response.status(401).json({ message: "Not signed in" });
  if (!UUID.test(request.body.batchId)) {
    return response.status(400).json({ message: "Bad batch id" });
  }

  const values = request.body.rows.map((row) => ({
    ...row,
    account_id: session.accountId,
  }));

  try {
    await clickhouse.insert({
      table: "analytics.ad_spend",
      values,
      format: "JSONEachRow",
      clickhouse_settings: {
        insert_deduplication_token:
          request.body.batchId + ":" + request.body.offset,
      },
    });
  } catch (error) {
    if (error instanceof ClickHouseError) {
      request.log.error({ code: error.code, type: error.type });
      return response.status(502).json({ message: error.type ?? "Insert failed" });
    }
    throw error;
  }

  response.json({ written: values.length });
});

getVerifiedSession() stands in for your own server-side authentication check. account_id comes off that session and never off the request body, so a guessed batch id reaches no other tenant's rows. The client posts JSONEachRow over HTTP, and the columns nobody sent take their declared defaults, which is how version fills itself.

insert_deduplication_token is what makes a repeated POST safe. ClickHouse identifies a repeated insert by a hash of the block, and a supplied token takes priority over that hash, so one chunk arriving twice under the same batch id lands once. The token is tracked per partition, so a chunk spanning two months carries it into both. A fresh submit mints a fresh batch id, and there the version column settles the repeat. Deduplication is on by default for the ReplicatedMergeTree family, inside a window of ten thousand blocks or one hour. A single-node MergeTree table starts with non_replicated_deduplication_window at 0, so a self-managed reader sets it before leaning on any of this.

What the merge promises

Deduplication happens during a merge, and merges run in the background at an unknown time. Until one runs, the table holds every row that was inserted. The agency's file carried the same line twice, and Updog leaves both on screen because two rows from one file never merge into each other, so both land in ClickHouse.

A plain SELECT over that table counts the spend twice. FINAL is the answer ClickHouse documents for it, since background merges alone cannot promise a deduplicated read, and the read route above carries it. FINAL merges at query time and reads the primary key columns on top of the ones you asked for, so it runs slower than the same query without it, which is the moment to reach for an aggregating view or an argMax over the version column.

When the agency sends a corrected September, the same three key columns arrive with a fresh now64(3), and the newer version wins from the moment it lands.

When an insert fails

The client throws a ClickHouseError carrying a code and a type parsed out of the server's message. TOO_MANY_PARTS is 252 and means the inserts are too small or too frequent. VIOLATED_CONSTRAINT is 469 and names the CHECK that refused the row. TYPE_MISMATCH is 53 and means a value reached a column that cannot hold it.

There is no per-row failure list. The insert fails as a block, and the message names the constraint or the parse position. A file-shaped load can be told to tolerate some of that through input_format_allow_errors_num and input_format_allow_errors_ratio, both of which sit at 0 by default and skip a failing row while the counter stays under the limit. This import takes the other route, where the person sees the bad row on screen while the file is still theirs to fix.

The routes nobody ships for you

Updog Importer integrates with nobody. There is no ClickHouse connector, no destination list, no webhook and no server of ours. onComplete hands your code an object, and the routes in the middle are work you do.

ClickHouse already ships its own ways in for the other case. The command line client streams a file straight into a table. FROM INFILE reads one from disk. The file() table function queries a CSV where it sits and guesses its types, and DESCRIBE shows the guess before anything is loaded. The Cloud console takes a JSON, CSV or TSV file, and ClickPipes keeps object storage and Postgres flowing in. For an export your own team downloaded, those tools are the shorter way in. Everything above exists for the export an agency sent, opened in a browser inside a session your app issued. Client-side and server-side CSV import names the jobs each of the two models fits.

The pieces you wrote and the next export

You wrote one table, a schema with eight columns, an async check against the campaigns you already hold, a read route, a handler that turns three flags into one column, and one insert route. The file stays on the machine that opened it. The rows travel from your own front end to your own route and into ClickHouse, and the only party you added to the chain is yourself. Point the same setup at a React CSV importer modal or at the web component and the middle stays the same.

The agency will send October, and half of it will be September again with two lines corrected. That time the mappings are already stored, the editor opens on the spend that landed last time, and the version column decides the rest.