What is the best way to insert using ClickHouse functions?

[francescorivola]

Hi Clickhouse js team,

I am writing you to ask you what is the recommended approach to perform inserts using clickhouse functions.

An example is the following where we use unhex to improve storage for BSON ObjectId values:

INSERT INTO test (id,timestamp,email,birthdate) VALUES
(unhex('57ee136f67d7c9384ec71f75'),1623677409145,'john.doe@gmail.com',NULL)
(unhex('57ee136f67d7c9384ec71f76'),1623677409145,'john.doe2@gmail.com',NULL)

We are planning to migrate to this driver and our current ETL process is adding data with this approach with inserts of 20K rows in each insert.

According to the current doc (https://clickhouse.com/docs/en/integrations/language-clients/javascript#insert-method) our understanding is that the right approach would be to use the command function, as we haven't found any way to archive this with the insert function.

Could you please confirm we are on the right path?

Is there any plan in the future to support this kind of inserts with the insert function?

Thank you in advance.

Best Regards.

[slvrtrn]

Hi @francescorivola, welcome!

Yes, for "complicated" inserts with VALUES clause + additional functions invocations/INSERT FROM SELECT/etc. I'd recommend looking into the command method, specifically designed for these corner cases. An example for INSERT FROM SELECT is here; adjusted for your use case:

await client.command({
  query: `
    INSERT INTO test 
      (id, timestamp, email, birthdate) 
    VALUES 
      (unhex('57ee136f67d7c9384ec71f75'), 1623677409145, 'john.doe@gmail.com', NULL)
  `,
})

[slvrtrn]

@francescorivola, I added a full example for future users based on this issue: https://github.com/ClickHouse/clickhouse-js/blob/main/examples/insert_values_and_functions.ts

[francescorivola]

Hi @slvrtrn ,

Thank you so much for the speedy reply. The new example will help for sure other people.

I am closing the issue.

Thank you again.

[slvrtrn]

No worries! Feel free to open a new one if you have any more questions or reach out in the community Slack directly.