Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Allow direct usage of Date objects for inserts #215

Closed
aimfeld opened this issue Dec 6, 2023 · 3 comments
Closed

Allow direct usage of Date objects for inserts #215

aimfeld opened this issue Dec 6, 2023 · 3 comments
Labels
question Further information is requested

Comments

@aimfeld
Copy link

aimfeld commented Dec 6, 2023

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
@aimfeld aimfeld added the bug Something isn't working label Dec 6, 2023
@aimfeld aimfeld changed the title Client insert() cannot handle Date objects with milliseconds and timezone Client insert() cannot handle Date objects with timezone Dec 6, 2023
@slvrtrn slvrtrn added enhancement New feature or request and removed bug Something isn't working labels Dec 6, 2023
@slvrtrn slvrtrn changed the title Client insert() cannot handle Date objects with timezone Allow direct usage of Date objects for inserts Dec 6, 2023
@mshustov
Copy link
Member

consider using date_time_input_format= best_effort

@slvrtrn
Copy link
Contributor

slvrtrn commented Dec 15, 2023

@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.

@slvrtrn slvrtrn closed this as completed Dec 15, 2023
@slvrtrn slvrtrn added question Further information is requested and removed enhancement New feature or request labels Dec 15, 2023
@aimfeld
Copy link
Author

aimfeld commented Dec 18, 2023

@slvrtrn It works, thanks for the quick fix!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
question Further information is requested
Projects
None yet
Development

No branches or pull requests

3 participants