Table functions are functions that produce rows in the same way as a table, and with Crate’s latest testing release (0.55.0) we have added support for unnest.
Unnest uses arrays to generate a transient table with each array representing a column:
cr> select * from unnest([1, 2, 3], ['Arthur', 'Trillian', 'Marvin']); +------+----------+ | col1 | col2 | +------+----------+ | 1 | Arthur | | 2 | Trillian | | 3 | Marvin | +------+----------+ SELECT 3 rows in set (... sec)
To insert large amounts of data efficiently, you are recommended to use bulk inserts. With a client that does not support these or if you have no direct access to the rest endpoint you can be out of luck! The same is also unfortunately true for our ODBC driver since the ODBC specification does not have a way to perform bulk requests.
unnest there is a way around this by using insert-by-query you can select from an ‘unnested’ table which results in a bulk insert internally:
cr> insert into authors (id, name) (select * from unnest([1, 2, 3], ['Arthur', 'Trillian', 'Marvin'])); INSERT OK, 3 rows affected (... sec)
As the response suggested, the three rows have now been inserted:
cr> select * from authors order by id; +----+----------+ | id | name | +----+----------+ | 1 | Arthur | | 2 | Trillian | | 3 | Marvin | +----+----------+ SELECT 3 rows in set (... sec)
The resulting performance is also comparable to bulk requests
Comparison of a single bulk request and insert via ‘unnest’ of 1000 rows, repeated 10 times on a single machine.
We have extended the crate-version-comparison Github repository to include a way to insert with unnest and compare single-threaded performance.
As an Open Source company we rely on your feedback and would be happy if you try it out and tell us how you liked it. Or even better, submit a pull request ?!
If your download did not start, click here to retry. CrateDB is also available for other platforms.
Crate.io is constantly adding fantastic new features and making CrateDB an even better solution for you. Sign up for our newsletter to stay up to date.