0

I have following table in CH:

(
    msisdn     UInt64,
    time_stamp DateTime64(3, 'Europe/Moscow'),
    user_id    UInt64,
    imsi       String,
    iccid      Nullable(UInt64)
)
    engine = MergeTree()
        PARTITION BY toYYYYMM(time_stamp)
        ORDER BY (msisdn, time_stamp)
        SETTINGS index_granularity = 8192;

if I try to insert new row manually using intellij idea console with query:

insert into raw_mobile_data.identifier (msisdn, time_stamp, user_id, imsi, iccid)
VALUES (1, parseDateTime64BestEffort('2021-04-02T17:22:32.999+03:00'), 1, 1, null);

it's inserting successfully!

In my java code I have:

private static final String INSERT_IDENTIFIERS = "INSERT INTO identifier (msisdn,time_stamp,user_id,imsi,iccid) VALUES (:msisdn,parseDateTime64BestEffort(:time_stamp),:user_id,:imsi,:iccid)";

@Override
    public Integer save(List<IdentifierDto> identifiers) {
        if (CollectionUtils.isEmpty(identifiers)) {
            log.info("identifiers list for insert is empty");
            return 0;
        }
        int[] batchInsertResult = namedParameterJdbcTemplate.batchUpdate(INSERT_IDENTIFIERS, getParametersSource(identifiers));
        return Arrays.stream(batchInsertResult).boxed().mapToInt(it -> it).sum();
    }

private SqlParameterSource[] getParametersSource(List<IdentifierDto> identifiers) {
        return identifiers.stream()
                .map(i -> {
                    return new MapSqlParameterSource()
                            .addValue("msisdn", i.getMsisdn())
                            .addValue("time_stamp", i.getTimestamp())
                            .addValue("user_id", i.getUserId())
                            .addValue("imsi", i.getImsi())
                            .addValue("iccid", i.getIccid());
                })
                .toArray(SqlParameterSource[]::new);
    }

timestamp field in java object IdentifierDto is String type. While save(...) executed, I get following error:

Caused by: ru.yandex.clickhouse.except.ClickHouseException: ClickHouse exception, code: 27, host: ХХ.ХХ.ХХ.ХХХ, port: 8123; Code: 27, e.displayText() = DB::Exception: Cannot parse input: expected '\t' before: '+03:00\t123\t19750666\t\\N\n': (at row 1)

Row 1:
Column 0,   name: msisdn,     type: UInt64,                         parsed text: "79160300666"
Column 1,   name: time_stamp, type: DateTime64(3, 'Europe/Moscow'), parsed text: "2021-04-02T17:22:32.999"
ERROR: garbage after DateTime64(3, 'Europe/Moscow'): "+03:00<TAB>123"

 (version 20.11.4.13 (official build))

what could be the reason?

Roman Patrushev
  • 81
  • 7
  • Clickhouse is not usual RDBMS https://github.com/ClickHouse/clickhouse-jdbc/blob/master/clickhouse-jdbc/src/test/java/ru/yandex/clickhouse/integration/BatchInsertsTest.java#L46 https://github.com/ClickHouse/clickhouse-jdbc/blob/master/clickhouse-jdbc/src/test/java/ru/yandex/clickhouse/integration/RowBinaryStreamTest.java – Denny Crane Apr 08 '21 at 23:39
  • @DennyCrane I tried your example, but still getting the same error – Roman Patrushev Apr 09 '21 at 07:35
  • Do not use ('2021-04-02T17:22:32.999+03:00') , convert it into UTC timestamp in JAVA – Denny Crane Apr 09 '21 at 12:17

0 Answers0