Repository navigation
Description
Is your feature request related to a problem or challenge?
Thanks to the great work from @metesynnada @devinjdangelo and others, it is now possible to have DataFusion insert data into CSV and JSON tables.
However, the User Experience / UX is tough as it is not easy to write datasets because: External tables require an existing location to exist, even if empty
So for example, if I wanted to write to json files in /tmp/my_table I currently need to create a the target file / directory externally
mkdir /tmp/my_table
echo "" > /tmp/my_table/1.json
datafusion-cli❯ create external table my_table(x int, y int) stored as JSON location '/tmp/my_table';
0 rows in set. Query took 0.002 seconds.
❯ insert into my_table values (1,2), (3, 4);
+-------+
| count |
+-------+
| 2 |
+-------+
1 row in set. Query took 0.006 seconds.
❯
\q$ cat /tmp/my_table/1.json
{"x":1,"y":2}
{"x":3,"y":4}Describe the solution you'd like
I would like to be able to have datafusion create the targets directly
Something like this, without any setup:
❯ create external table my_table(x int, y int) stored as JSON location '/tmp/my_table';
0 rows in set. Query took 0.002 seconds.
❯ insert into my_table values (1,2), (3, 4);
+-------+
| count |
+-------+
| 2 |
+-------+
1 row in set. Query took 0.006 seconds.$ cat /tmp/my_table/1.json
{"x":1,"y":2}
{"x":3,"y":4}Describe alternatives you've considered
Option 1: Automatically create the target
One option is simply to remove the existence check on CREATE EXTERNAL TABLE
❯ create external table my_table(x int, y int) stored as JSON location '/tmp/my_table';
0 rows in set. Query took 0.002 seconds.
❯ insert into my_table values (1,2), (3, 4);
+-------+
| count |
+-------+
| 2 |
+-------+
1 row in set. Query took 0.006 seconds.The downside is that the reason CREATE EXTERNAL TABLE errors if the target doesn't exist is to help people debug errors when reading
Option 2: Add new DDL to CREATE EXTERNAL TABLE
Perhaps we could add a phrase to CREATE EXTERNAL TABLE like FOR WRITE
❯ create external table my_table(x int, y int) stored as JSON location '/tmp/my_table' FOR WRITE;
0 rows in set. Query took 0.002 seconds.The semantics would be if the target file/directory doesn't already exist, then create it rather than error
Option 3: Add a config parameter to control the default behavior
We could also add a config parameter like
❯ set catalog.auto_create_tables = true;
SET
❯ create external table my_table(x int, y int) stored as JSON location '/tmp/my_table' FOR WRITE;
0 rows in set. Query took 0.002 seconds.with the same semantics as option 2 (create it target doesn't exist)
Additional context
No response
Activity
alamb commentedon Aug 8, 2023
I think OPTON 2 would be ideal and the easiest to use. Maybe we could even add a HINT in the message if
CREATE TABLEfailed with "not found" thatFOR WRITEcould be added to make a new filesdevinjdangelo commentedon Aug 8, 2023
I agree UX needs work. An additional problem is
ListingTableInsertModecannot be controlled from SQL or sesision config parameters. So while you can append new files to a table via rust like so:Registering a table via SQL doesn't expose this setting, so it always defaults to ListingTableInsertModeAppendToFile.
I am thinking Option3 makes the most sense to solve these issues. My concern with option2 is we will have to add a lot of new SQL grammar to support these and future write options (e.g. compression, parquet row group size ect...). For parquet read configs, we have a lot of options controlled via session config, and we could follow a similar pattern for default write behaviors (both parquet and non parquet specific write behaviors).
For option 2, maybe some syntax like
create external table WITH (...)where (...) is arbitrary config tuples could work. I believe trino has some syntax like this for creating tables.flashmouse commentedon Aug 10, 2023
I would like to implement this feature, I'll try work on it this weekend. for now I prefer to implement option3, any advice are welcome at any time. thx!
2010YOUY01 commentedon Aug 10, 2023
The recent work on data export looks awesome 👀
I'm wondering is the current work (
ctx.register_*(); ctx.sql("insert into...")')just building reusable infra forcreate external table...and the TODOCOPY ... TO ...syntax? Is that the case they mostly have the same functionality but with different syntax2010YOUY01 commentedon Aug 10, 2023
This syntax looks really nice 👍🏼
Looks like Option 3 can be used for default settings, and this syntax can do local config override for individual queries
DuckDB's
COPY ... TO ...syntax has very similar syntax for those arbitrary config tupleshttps://duckdb.org/docs/sql/statements/copy
alamb commentedon Aug 10, 2023
I think option 3 should be a relatively straightforward exercise of:
devinjdangelo commentedon Aug 14, 2023
I did not intend to close this issue with my recent PR. Can we reopen it?
#7276 provides a possible workaround to the issue here, but not a complete solution.