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();
CodePudding user response:
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);
}
}