DarkWanderer / ClickHouse.Client

.NET client for ClickHouse
MIT License
302 stars 58 forks source link

Bulk Insertion not working with Geo Data Types #481

Closed EvertonAiko closed 1 month ago

EvertonAiko commented 1 month ago

I'm trying to bulk insert into a table with a geospatial data type column.

table DDL:

CREATE TABLE local.metric_position
(
    equipment_id UInt64,
    timestamp DateTime,
    value Point
)
ENGINE = MergeTree
PARTITION BY equipment_id
PRIMARY KEY (equipment_id, timestamp)
ORDER BY (equipment_id, timestamp)

Code:

using Bogus;
using ClickHouse.Client.ADO;
using ClickHouse.Client.Copy;
using NetTopologySuite.Geometries;

var clickHouseConnection = new ClickHouseConnection("Host=localhost;Port=8123;Username=default;Password=12345678;Database=default");

var faker = new Faker();
var fakeData = new Faker<DataPoint>()
    .RuleFor(x => x.Timestamp, f => f.Date.Between(new DateTime(2000, 1, 1), DateTime.Now))
    .RuleFor(x => x.EquipmentId, f => f.PickRandom(new[] { 2392575, 2392578, 2392656 }))
    .RuleFor(x => x.Value, f => new Point(f.Address.Latitude(), f.Address.Longitude()))
    .Generate(100);

var rows = fakeData.Select(r => new object[] { r.EquipmentId, r.Timestamp, r.Value });

using var bulkCopyInterface = new ClickHouseBulkCopy(clickHouseConnection)
{
    DestinationTableName = "local.metric_position",
    ColumnNames = new [] { "equipment_id", "timestamp", "value" }
};
await bulkCopyInterface.InitAsync(); 
await bulkCopyInterface.WriteToServerAsync(rows);

record DataPoint
{
    public long EquipmentId { get; init; }
    public DateTime Timestamp { get; init; }
    public Point Value { get; init; }
}

Error:

Unhandled exception. ClickHouse.Client.ClickHouseServerException (0x00000021): Code: 33. DB::Exception: Cannot read all data. Bytes read: 4. Bytes expected: 8.: (at row 43)
: While executing BinaryRowInputFormat. (CANNOT_READ_ALL_DATA) (version 24.4.1.2088 (official build))

   at ClickHouse.Client.ADO.ClickHouseConnection.HandleError(HttpResponseMessage response, String query, Activity activity)
   at ClickHouse.Client.ADO.ClickHouseConnection.PostContentAsync(String sql, HttpContent httpData, CancellationToken token)
   at ClickHouse.Client.Copy.ClickHouseBulkCopy.SendBatchAsync(BulkCopyHttpContent batchContent, CancellationToken token)
   at ClickHouse.Client.Copy.ClickHouseBulkCopy.WriteToServerAsync(IEnumerable`1 rows, CancellationToken token)
   at Program.<Main>$(String[] args) in /home/everton/Desktop/temp/ClickHousePlayground/Program.cs:line 23
   at Program.<Main>(String[] args)

Using dapper and creating a type handler for NetTopologySuite Point types, it works. But through the bulk copy interface the error occurs. Is there anything I can do to use this bulk insertion method with this geospatial data?