Insert JSON string via BulkCopyInterface

[plewam]

Hello @DarkWanderer,

Fist of all thank you very much for this beautiful clickhouse client. I got a question around the JSON column type. I would like to insert a json string into clickhouse. However when I do this, I get below exception:

ClickHouse.Client.Copy.ClickHouseBulkCopySerializationException: Error when serializing data
 ---> System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Runtime.CompilerServices.ITuple'.
   at ClickHouse.Client.Types.TupleType.Write(ExtendedBinaryWriter writer, Object value)
   at ClickHouse.Client.Copy.ClickHouseBulkCopy.WriteToServerAsync(IEnumerable`1 rows, IReadOnlyCollection`1 columns, CancellationToken token)
   --- End of inner exception stack trace ---
   at ClickHouse.Client.Copy.ClickHouseBulkCopy.WriteToServerAsync(IEnumerable`1 rows, IReadOnlyCollection`1 columns, CancellationToken token)

The exception itself is quite clear, I assume you library expects a tuple as data type. However I got the data present as a json string. Have you have any experience with this or any trick I can use to make it work?

Kind regards,

Mathis

[DarkWanderer]

Hi @plewam,

This error means that when ClickHouse.Client tried to SELECT * LIMIT 0 data from target table to determine column types, CH returned Tuple(_) instead of Json type for this column. Json support is experimental in ClickHouse itself, so the API has not yet stabilized - not much I can do about it client-side.

As a workaround, you can create a Materialized View which writes into target table and maps String to Json

See also #243

[plewam]

Hi @DarkWanderer, thanks for your reply. I have found myself a workaround where I write simple insert statements in order to insert json data. The JSON string itself is encoded by using HttpUtility.JavaScriptStringEncode((string)value) where value is my json string. However this only works for regular inserts and not your bulk copy interface. This one expects a tuple.
I can live with it at the moment as my json data volume is quite low. Maybe the API stabilizes in the future so we can have full json support via the bulk copy interface.

Kind regards

[plewam]

@DarkWanderer As JSON now is supported since the latest version do you have a rough documentation available what I need to do in order to insert json data?

[DarkWanderer]

Here is a quick example:

        var targetTable = "test." + SanitizeTableName($"bulk_json");
        await connection.ExecuteStatementAsync($"DROP TABLE IF EXISTS {targetTable}");
        await connection.ExecuteStatementAsync($"CREATE TABLE IF NOT EXISTS {targetTable} (value JSON) ENGINE Memory");

        using var bulkCopy = new ClickHouseBulkCopy(connection)
        {
            DestinationTableName = targetTable,
        };

        var jsonString = "{\"string\": \"value\"}";
        var jsonObject = (JsonObject)JsonNode.Parse(jsonString);

        await bulkCopy.InitAsync();
        await bulkCopy.WriteToServerAsync([[jsonString], [jsonObject]]);

        using var reader = await connection.ExecuteReaderAsync($"SELECT * from {targetTable}");
        while(reader.Read())
        {
            Assert.That(reader.GetValue(0), Is.EqualTo(jsonObject).UsingPropertiesComparer());
        }

You can insert either a string or a System.Text.Json.Nodes.JsonObject. You will always get a JsonObject back (which then you can call ToString() on)

[plewam]

Works, thank you very much :)