insert_data_formats_overview.ts

// An overview of available formats for inserting your data, mainly in different JSON formats.
// For "raw" formats, such as:
//  - CSV
//  - CSVWithNames
//  - CSVWithNamesAndTypes
//  - TabSeparated
//  - TabSeparatedRaw
//  - TabSeparatedWithNames
//  - TabSeparatedWithNamesAndTypes
//  - CustomSeparated
//  - CustomSeparatedWithNames
//  - CustomSeparatedWithNamesAndTypes
//  - Parquet
//  insert method requires a Stream as its input; see the streaming examples:
//  - streaming from a CSV file - node/insert_file_stream_csv.ts
//  - streaming from a Parquet file - node/insert_file_stream_parquet.ts
//
// If some format is missing from the overview, you could help us by updating this example or submitting an issue.
//
// See also:
// - ClickHouse formats documentation - https://clickhouse.com/docs/en/interfaces/formats
// - SELECT formats overview - select_data_formats_overview.ts
import {
  createClient,
  type DataFormat,
  type InputJSON,
  type InputJSONObjectEachRow,
} from "@clickhouse/client-web";

const tableName = "insert_data_formats_overview_web";
const client = createClient();
await prepareTestTable();

// These JSON formats can be streamed as well instead of sending the entire data set at once;
// See this example that streams a file: node/insert_file_stream_ndjson.ts
console.log("#### Streamable JSON formats:\n");
// All of these formats accept various arrays of objects, depending on the format.
await insertJSON("JSONEachRow", [
  { id: 1, name: "foo", sku: [1, 2, 3] },
  { id: 2, name: "bar", sku: [4, 5, 6] },
]);
await insertJSON("JSONStringsEachRow", [
  { id: "3", name: "foo", sku: "[1,2,3]" },
  { id: "4", name: "bar", sku: "[4,5,6]" },
]);
await insertJSON("JSONCompactEachRow", [
  [5, "foo", [1, 2, 3]],
  [6, "bar", [4, 5, 6]],
]);
await insertJSON("JSONCompactStringsEachRow", [
  ["7", "foo", "[1,2,3]"],
  ["8", "bar", "[4,5,6]"],
]);
await insertJSON("JSONCompactEachRowWithNames", [
  ["id", "name", "sku"],
  [9, "foo", [1, 2, 3]],
  [10, "bar", [4, 5, 6]],
]);
await insertJSON("JSONCompactEachRowWithNamesAndTypes", [
  ["id", "name", "sku"],
  ["UInt32", "String", "Array(UInt32)"],
  [11, "foo", [1, 2, 3]],
  [12, "bar", [4, 5, 6]],
]);
await insertJSON("JSONCompactStringsEachRowWithNames", [
  ["id", "name", "sku"],
  ["13", "foo", "[1,2,3]"],
  ["14", "bar", "[4,5,6]"],
]);
await insertJSON("JSONCompactStringsEachRowWithNamesAndTypes", [
  ["id", "name", "sku"],
  ["UInt32", "String", "Array(UInt32)"],
  ["15", "foo", "[1,2,3]"],
  ["16", "bar", "[4,5,6]"],
]);

// These are single document JSON formats, which are not streamable
console.log("\n#### Single document JSON formats:\n");
// JSON, JSONCompact, JSONColumnsWithMetadata accept the InputJSON<T> shape.
// For example: https://clickhouse.com/docs/en/interfaces/formats#json
const meta: InputJSON["meta"] = [
  { name: "id", type: "UInt32" },
  { name: "name", type: "String" },
  { name: "sku", type: "Array(UInt32)" },
];
await insertJSON("JSON", {
  meta: [], // not required for JSON format input
  data: [
    { id: 17, name: "foo", sku: [1, 2, 3] },
    { id: 18, name: "bar", sku: [4, 5, 6] },
  ],
});
await insertJSON("JSONCompact", {
  meta,
  data: [
    [19, "foo", [1, 2, 3]],
    [20, "bar", [4, 5, 6]],
  ],
});
await insertJSON("JSONColumnsWithMetadata", {
  meta,
  data: {
    id: [21, 22],
    name: ["foo", "bar"],
    sku: [
      [1, 2, 3],
      [4, 5, 6],
    ],
  },
});

// JSONObjectEachRow accepts Record<string, T> (alias: InputJSONObjectEachRow<T>).
// See https://clickhouse.com/docs/en/interfaces/formats#jsonobjecteachrow
await insertJSON("JSONObjectEachRow", {
  row_1: { id: 23, name: "foo", sku: [1, 2, 3] },
  row_2: { id: 24, name: "bar", sku: [4, 5, 6] },
});

// Print the inserted data - see that the IDs are matching.
await printInsertedData();
await client.close();

// Inserting data in different JSON formats
async function insertJSON<T = unknown>(
  format: DataFormat,
  values: ReadonlyArray<T> | InputJSON<T> | InputJSONObjectEachRow<T>,
) {
  try {
    await client.insert({
      table: tableName,
      format: format,
      values,
    });
    console.log(`Successfully inserted data with format ${format}`);
  } catch (err) {
    console.error(`Failed to insert data with format ${format}, cause:`, err);
    throw err;
  }
}

async function prepareTestTable() {
  await client.command({
    query: `
      CREATE OR REPLACE TABLE ${tableName}
      (id UInt32, name String, sku Array(UInt32))
      ENGINE MergeTree()
      ORDER BY (id)
    `,
  });
}

async function printInsertedData() {
  const resultSet = await client.query({
    query: `SELECT * FROM ${tableName} ORDER BY id ASC`,
    format: "JSONEachRow",
  });
  const data = await resultSet.json();
  console.log("Inserted data:");
  console.log(data);
}