Allow direct usage of Date objects for inserts
aimfeld opened this issue · 3 comments
I'm using clickhouse-js to insert rows into a table. For timestamps, I'm using DateTime64(3, 'UTC')
. However, the client seems to have a problem with Date objects:
Cannot parse input: expected '"' before: 'Z","message":"{\\"created_at\\":\\"2023-12-06T10:54:48.000Z\\",\\"modified_at\\":\\"2023-12-': (while reading the value of key created_at): While executing ParallelParsingBlockInputFormat: (at row 1) ... ,"error":{"code":"27","type":"CANNOT_PARSE_INPUT_ASSERTION_FAILED"}
When I convert the Date objects to strings and remove the timezone part, it works:
import { ClickHouseClient } from '@clickhouse/client';
import { InsertClient } from './InsertClient';
import moment from 'moment';
export class ClickHouseInsertClient implements InsertClient {
public constructor(private clickHouseClient: ClickHouseClient) {}
public async insertObjects(tableName: string, objects: object[]): Promise<void> {
for (const object of objects) {
for (const key of Object.keys(object)) {
object[key] = this.sanitizeValue(object[key]);
}
}
await this.clickHouseClient.insert({
table: tableName,
values: objects,
format: 'JSONEachRow'
});
}
private sanitizeValue(value: unknown): unknown {
// ClickHouse can't handle the timezone part (Z or offset +00:00) in the date string
if (value instanceof Date) {
return moment(value).format('YYYY-MM-DD HH:mm:ss.SSS');
}
return value;
}
}
Environment
- Client version: 0.2.6
- Language version:
- OS: Linux
ClickHouse server
- ClickHouse Server version: 23.9.1.1854
consider using date_time_input_format= best_effort
@mshustov, thanks! It works very well with DateTime fields (see the test). Date/Date32 are not parsed properly with this setting, though; I think we can revisit it in the scope of #216
@aimfeld, I added an example that should work for your use-case. Feel free to re-open or DM in the community Slack if you encounter any issues.