Appender API with nested types

[loicalleyne]

Today I saw a post on LinkedIn by an engineer at ShareChat link where they managed to get 20K inserts/sec using a query batcher but estimate that appender could be even faster. The table schema they had was flat, and usually I have to deal with nested data; I've seen it said that there is an 'append data chunk' way of using appender but in go-duckdb that function is not public. Is there a way to use appender with nested data using the existing AppendRow function?
Or is it easier/faster to use the Arrow RegisterView and insert a select from the view?

[taniabogatsch]

Hi @loicalleyne!

I've seen it said that there is an 'append data chunk' way of using appender but in go-duckdb that function is not public

Indeed, when using the C API, we recommend using the duckdb_append_data_chunk functions. That way, the client/user can pass data chunks to the appender, which stores them in a local ColumnDataCollection and eventually batch inserts them.

Internally, go-duckdb also uses duckdb_append_data_chunk. The Appender also uses these data chunks. When you call AppendRow, it fills the current data chunk (row-wise). Eventually, go-duckdb flushes all data chunks via duckdb_append_data_chunk.

Another way to fill these data chunks internally would be to expose an AppendColumn function. go-duckdb does not implement that. We've only discussed it in a few PRs.

So, to summarize, the Appender and AppendRow internally already use the recommended C API functions to append chunks. However, go-duckdb still has room for improvement in how it creates/fills these data chunks.

Is there a way to use appender with nested data using the existing AppendRow function?

Yes, this is possible. Look at this test case, which appends many different nested types.

Or is it easier/faster to use the Arrow RegisterView and insert a select from the view?

I cannot say, I haven't benchmarked this.

Hope this helps - :)

[loicalleyne]

@taniabogatsch I've been busy working on bodkin a helper library for Arrow so I haven't tried the Appender yet; that being said I just tested inserting 836855 rows of data converted into Arrow Records into duckdb with
RegisterView and a QueryContext INSERT into T1 BY NAME (SELECT * FROM 'arrowview') and it completed in 7.8 seconds or ~107K/rows per second. The data's schema is 46 fields with nested structs and lists.