Clickhouse Json parsing exception using a property input_format_skip_unknown

I am trying to insert Json data to clickhouseDB table from its Http port but an error is coming up. The same code worked for a different payload but failing for this payload. This insert to db code is in hosted aws lambda. Details are mentioned below.

Http send code:-

r = http.request(
        'POST',
        f'http://{clickhouse_host}/?input_format_skip_unknown_fields=1&query=INSERT INTO events.card_events FORMAT JSONEachRow',
        body=payload,
        headers={'Content-Type': 'text/plain'}
        )

Payload is:-

{"eventType": "CARD", "eventSubType": "CARD_ISSUED", "description": "kuch bhee", "version": "chal gaya", "bankCode": "", "data_cardToken": "sadads", "data_realId": "999999999", "data_bin": "1000000000", "data_bank": "", "data_lastFour": "3535", "data_cardStatus": "VIRTUAL_ACTIVATED", "data_createdAt": "2021-11-29 13:24:55.390859", "data_virtualActivatedAt": "", "data_embossDate": "", "data_productCode": "", "data_creditLimit": "", "data_id": 1234, "data_dateTime": "2021-11-29 13:24:55.390859", "data_cardHolderDetails_firstName": "Jason Phillips", "data_cardHolderDetails_lastName": "Jessica Hurst", "data_cardHolderDetails_phone": 9430344902, "data_cardHolderDetails_email": "[email protected]", "data_cardHolderDetails_pincode": "449668", "data_cardHolderDetails_city": null, "data_cardHolderDetails_status": "TRANSACTOR-3", "data_cardHolderDetails_realId": "999999999", "data_cardHolderDetails_active": false, "data_cardHolderDetails_productCode": "OCL", "data_cardHolderDetails_startDate": "2021-08-09", "data_logo_id": "", "data_logo_description": "", "data_logo_code": "", "data_logo_embossingFilePrefix": "", "data_logo_byDefault": "", "data_shipmentDetails_awb": "", "data_shipmentDetails_deliveryPartner": "", "data_shipmentDetails_status": "", "data_blockCode_blockCode": "", "data_blockCode_updatedAt": "", "data_blockCode_description": "", "data_blockCode_notes": ""}

Table DDL:-

(
    `eventType` LowCardinality(String) DEFAULT '',
    `eventSubType` LowCardinality(String) DEFAULT '',
    `description` String DEFAULT '',
    `version` LowCardinality(String) DEFAULT '',
    `bankCode` LowCardinality(String) DEFAULT '',
    `data_bin` String DEFAULT '',
    `data_cardHolderDetails_city` String DEFAULT '',
    `data_logo_code` LowCardinality(String) DEFAULT '',
    `data_creditLimit` Decimal(10, 2) DEFAULT 99999999.99 CODEC(T64, LZ4),
    `data_cardHolderDetails_pincode` LowCardinality(String) DEFAULT '',
    `data_productCode` LowCardinality(String) DEFAULT '',
    `data_realId` String DEFAULT '',
    `data_createdAt` DateTime64(3) DEFAULT '000000' CODEC(DoubleDelta, LZ4),
    `data_date` DateTime64(3) MATERIALIZED toDateTime64(now(), 3) CODEC(DoubleDelta, LZ4)
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(data_createdAt)
ORDER BY (data_createdAt, bankCode, eventSubType)
SETTINGS index_granularity = 8192

Error:-

Arguments: (b'Code: 27. DB::ParsingException: Cannot parse input: expected \',\' before: \'"", "data_id": 1234, "data_dateTime": "2021-11-29 13:24:55.390859", "data_cardHolderDetails_firstName": "Jason Phillips", "data_cardHolderDetails_lastName": "Je\': While executing JSONEachRowRowInputFormat: (at row 1)\n. (CANNOT_PARSE_INPUT_ASSERTION_FAILED) (version 21.11.4.14 (official build))\n',)

According to error code, there should be a comma before data_id and it is already there. What else could be the problem ? pls help

CodePudding user response：

Looks like the error is misleading.

In your case you have data_creditLimit as DecimalType:

`data_creditLimit` Decimal(10, 2) DEFAULT 99999999.99 CODEC(T64, LZ4)

But at your json it's a string:

"data_creditLimit": ""

You can remove this or replace with

"data_creditLimit": null