How should I Bulk Insert data with Dictionary(array) model props?

[karpov239-alt]

So I can`t understand how to properly bulk insert data which has Dictionary in its model, for example:

public class Price
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public Dictionary<string, Decimal> Prices { get; set; } = [];
}

It is said in the docs that ClickHouse supports Map and Array. I created the table like this:

CREATE TABLE default.table_nameON CLUSTER 'cluster-name'
(
    Id UUID DEFAULT generateUUIDv4(),
    Prices Map(String, Decimal(15, 2)) DEFAULT map(),
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/table_name', '{replica}')
ORDER BY (Id)
SETTINGS index_granularity = 8192;

I created wrapper around the click house functionlity so I don`t have to copy paste connection string code:

this method comes from the wrapper class:

public async Task BulkInsertAsync<T>(string tableName, IEnumerable<T> data, int batchSize)
{
    var properties = typeof(T).GetProperties();

    var items = data.Select(item =>
        properties.Select(prop =>
        {
            var value = prop.GetValue(item);
            return value ?? DBNull.Value;
        }).ToArray()
    );

    using var bulkInsertConfig = new ClickHouseBulkCopy(_clickHouseConnection)
    {
        DestinationTableName = tableName,
        BatchSize = batchSize,
    };

    await bulkInsertConfig.InitAsync();
    await bulkInsertConfig.WriteToServerAsync(items);
}

But when I try to bulk insert:

var items = new List<Price>
{
    new() {
        Id = Guid.NewGuid(),
        Prices = new Dictionary<string, Decimal> { ["RUB"] = 0m },
    },
};
await _clickHouseProvider.BulkInsertAsync("qp_answers_new", items, 1000);

I get the following exception:

Message = "Error when serializing data"
InnerException = {"Unable to cast object of type 'System.DateTime' to type 'System.Collections.IDictionary'."}
stacktrace =
   "в ClickHouse.Driver.Copy.Serializer.BatchSerializer.Serialize(Batch batch, Stream stream)
   в ClickHouse.Driver.Copy.ClickHouseBulkCopy.<>c__DisplayClass44_0.<SendBatchAsync>b__0()
   в System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
--- Конец трассировки стека из предыдущего расположения ---
   в System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
   в System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- Конец трассировки стека из предыдущего расположения ---
   в ClickHouse.Driver.Copy.ClickHouseBulkCopy.<SendBatchAsync>d__44.MoveNext()
   в ClickHouse.Driver.Copy.ClickHouseBulkCopy.<WriteToServerAsync>d__42.MoveNext()
   в {not important}
   в {not important}

I need this Map in the model, so I don`t have to create extra fields for prices in different currencies like Price_EUR or Price_RUB.

I haven`t found any info on the net and I hope you can tell me how I can do that?

Thanks!

[karpov239-alt]

I am sorry I cut out all sensitive information from the example. So in the stack trace System.DateTime is mentioned.

The issue is not with the DateTime cause I had no problems with it in my previous Bulk Insertions which had no Dictionary or Array in my model.

[alex-clickhouse]

Hi, based on this message: InnerException = {"Unable to cast object of type 'System.DateTime' to type 'System.Collections.IDictionary'."}

My first suspicion is that there's a mismatch in terms of the ordering of the columns in the table vs the data provided to BulkCopy. So it's expecting a Dictionary but getting a DateTime.

Perhaps it would a good idea in the future to be able to simply pass an object to the BulkCopy and have it intelligently match columns to properties, that seems like it would be useful in your case?

[alex-clickhouse]

Oh, also: you can provide the columns in the order you want when creating the BulkCopy object, like this:

        using var bulkCopy = new ClickHouseBulkCopy(connection)
        {
            DestinationTableName = targetTable,
            ColumnNames = new[] { "value2" }
        };