I am getting this error on inserting data in ClickHouse

Question

Unknown error field: Code: 27. DB::ParsingException: Cannot parse input: expected '"' before:
'40","gmtoffset":0,"open":109.6,"high":109.6,"low":109.6,"close":109.6,"volume":0,"previousClose":108.4,"change":1.2,"change_p":1.107}\n': (while reading the value of key timestamp): While executing JSONEachRowRowInputFormat: (at row 1). (CANNOT_PARSE_INPUT_ASSERTION_FAILED) (version 21.11.5.33 (official build))]

Create table query is simple

 CREATE TABLE IF NOT EXISTS RealTime ( code String,  timestamp DateTime,  gmtoffset Int32,  open Float32,  high Float32,  low Float32,  close Float32,  volume Float32,  previousClose Int32,  change Float32,  change_p Float32) ENGINE = MergeTree 
    PARTITION BY code 
    ORDER BY (timestamp);

insert query:

realTime = {
  "code": "2010.SR",
  "timestamp": 1639311540,
  "gmtoffset": 0,
  "open": 108.4,
  "high": 109.8,
  "low": 107.8,
  "close": 109.6,
  "volume": 1326663,
  "previousClose": 108.4,
  "change": 1.2,
  "change_p": 1.107
}



const writeableStream = clickhouse.query(
      `INSERT into RealTime`,
      { format: "JSONEachRow" },
      (err) => {
        if (err) {
          console.error(err);
        }
        console.log("Insert complete!");
      }
    );

    writeableStream.write([JSON.stringify(realtime)]);

    writeableStream.end();

Answer 1

The type of column 'previousClose' is 'Int32', but the value in realTime is the float type.

I tried your problem with clickhouse java jdbc driver. Change the value of 'previousClose' to 108, then it work fine.

And this is my java code:

public class ClickHouseDemo {

    private static final String CLICKHOUSE_DRIVER_NAME = "ru.yandex.clickhouse.ClickHouseDriver";

    /**
     * jdbc url
     */
    private static final String CK_URL = "your-ck-url";

    /**
     * jdbc database
     */
    private static final String CK_DB = "your-ck-db";

    /**
     * database user
     */
    private static final String CK_USER = "your-ck-user";

    /**
     * data password
     */
    private static final String CK_PASS = "your-ck-pass";

    public static void main(String[] args) throws SQLException {
        //correct string
        String validStr = "{\"code\": \"2010.SR\",\"timestamp\": 1639311540,\"gmtoffset\": 0,\"open\": 108.4,\"high\": 109.8,\"low\": 107.8,\"close\": 109.6,\"volume\": 1326663,\"previousClose\": 108,\"change\": 1.2,\"change_p\": 1.107}";

        //incorrect string
        String inValidStr = "{\"code\": \"2010.SR\",\"timestamp\": 1639311540,\"gmtoffset\": 0,\"open\": 108.4,\"high\": 109.8,\"low\": 107.8,\"close\": 109.6,\"volume\": 1326663,\"previousClose\": 108.4,\"change\": 1.2,\"change_p\": 1.107}";

        ClickHouseDemo clickHouseDemo = new ClickHouseDemo();
        ClickHouseConnection connection = clickHouseDemo.getConnection(CK_URL, CK_DB, CK_USER, CK_PASS);
        ClickHouseStatement chst = connection.createStatement();
        chst.write().addDbParam(ClickHouseQueryParam.QUERY_ID, UUID.randomUUID().toString()).sql("INSERT INTO RealTime_d").data(new ByteArrayInputStream(validStr.getBytes(StandardCharsets.UTF_8)), ClickHouseFormat.JSONEachRow).send();
    }

    private ClickHouseConnection getConnection(String url, String dbName, String user, String password) throws SQLException {
        try {
            Class.forName(CLICKHOUSE_DRIVER_NAME);
        } catch (ClassNotFoundException e) {
            throw new SQLException(e);
        }

        String conStr = String.format("jdbc:clickhouse://%s/%s", url, dbName);

        Properties properties = new Properties();
        properties.setProperty("user", user);
        properties.setProperty("password", password);
        return (ClickHouseConnection) DriverManager.getConnection(conStr, properties);
    }
}