You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by "karlovnv (via GitHub)" <gi...@apache.org> on 2023/11/08 11:14:33 UTC
[I] INSERT INTO does not allow to insert only specified columns [arrow-datafusion]
karlovnv opened a new issue, #8091:
URL: https://github.com/apache/arrow-datafusion/issues/8091
### Describe the bug
Most SQL databases (PG, MSSQL, ClickHouse, ...) allow to do INSERTS without resort to providing all the columns.
This is allowed by `insert into TABLE(columns) VALUES (values)` syntax when nullable columns could be omited.
It's important for upgrading schema of tables systems with zero downtime: on the first step we add new column and on the second we deploy new app code that deals with new schema.
Now it's impossible add column to schema without downtime due to logical planner error:
`Error during planning: Inserting query must have the same schema with the table.`
### To Reproduce
```
create table foo(x int, y int);
insert into foo(x) values (10);
```
Expected:
(10, NULL) is inserted
Actual:
```
DataFusion CLI v32.0.0
❯ create table foo(x int, y int);
0 rows in set. Query took 0.002 seconds.
❯ insert into foo(x) values (10);
Error during planning: Inserting query must have the same schema with the table.
❯ insert into foo(x, y) values (10, 11);
+-------+
| count |
+-------+
| 1 |
+-------+
1 row in set. Query took 0.002 seconds.
```
### Expected behavior
row (10, NULL) is inserted
### Additional context
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] INSERT INTO does not allow to insert only specified columns [arrow-datafusion]
Posted by "karlovnv (via GitHub)" <gi...@apache.org>.
karlovnv commented on issue #8091:
URL: https://github.com/apache/arrow-datafusion/issues/8091#issuecomment-1803650333
> I plan to support this.
>
> I think we can begin by supporting NULL as the default value, and later on, allow specifying default values for columns.
Now I consider several workarounds:
1. Write custom user defined LogicalPlan rewriter in order to add skipped columns
The proiblem is we have wedi tables (1000+ columns) and I think that it might be be slow.
2. Implement something like UDF placeholder replaced by custom logic
3. Use recordbatch (consists of rows to insert) as a parameter and provide it via user defined VarProvider
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] INSERT INTO does not allow to insert only specified columns [arrow-datafusion]
Posted by "jonahgao (via GitHub)" <gi...@apache.org>.
jonahgao commented on issue #8091:
URL: https://github.com/apache/arrow-datafusion/issues/8091#issuecomment-1803091208
I plan to support this.
I think we can begin by supporting NULL as the default value, and later on, allow specifying default values for columns.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] INSERT INTO does not allow to insert only specified columns [arrow-datafusion]
Posted by "oleggator (via GitHub)" <gi...@apache.org>.
oleggator commented on issue #8091:
URL: https://github.com/apache/arrow-datafusion/issues/8091#issuecomment-1803927624
The actual check performs [in FileSinkExec](https://github.com/apache/arrow-datafusion/blob/main/datafusion/physical-plan/src/insert.rs#L121). So fixing the check in TableProvider is not enough.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] INSERT INTO does not allow to insert only specified columns [arrow-datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #8091: INSERT INTO does not allow to insert only specified columns
URL: https://github.com/apache/arrow-datafusion/issues/8091
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] INSERT INTO does not allow to insert only specified columns [arrow-datafusion]
Posted by "jonahgao (via GitHub)" <gi...@apache.org>.
jonahgao commented on issue #8091:
URL: https://github.com/apache/arrow-datafusion/issues/8091#issuecomment-1803978780
> Now I consider several workarounds:
> 1. Write custom user defined LogicalPlan rewriter in order to add skipped columns
The proiblem is we have wedi tables (1000+ columns) and I think that it might be be slow.
I think the implementation of the first version could adopt a similar approach to the first one.
Since it is intuitive, and should be applicable to different sinks.
We can fill missing values with NULLs inside `insert_to_plan`.
https://github.com/apache/arrow-datafusion/blob/4512805c2087d1a5538afdaba9d2e2ca5347c90c/datafusion/sql/src/statement.rs#L1067
For scenarios with a large number of columns, maybe we can do some profiling later.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] INSERT INTO does not allow to insert only specified columns [arrow-datafusion]
Posted by "devinjdangelo (via GitHub)" <gi...@apache.org>.
devinjdangelo commented on issue #8091:
URL: https://github.com/apache/arrow-datafusion/issues/8091#issuecomment-1803750113
One simple approach would be to update the check here: https://github.com/apache/arrow-datafusion/blob/4512805c2087d1a5538afdaba9d2e2ca5347c90c/datafusion/core/src/datasource/listing/table.rs#L827-L835 to be more sophisticated. You can see it fails immediately if the schema's have different lengths: https://github.com/apache/arrow-datafusion/blob/4512805c2087d1a5538afdaba9d2e2ca5347c90c/datafusion/common/src/dfschema.rs#L398-L403
That check could be updated to only fail if the input schema is missing a field from the table which is not nullable. That way, the insert plan will happily continue, writing out files which are missing those nullable columns.
A longer term solution would have a more explicit way to fill in missing nullable columns or columns with a default values. This could be a dedicated execution plan that comes right before an insertion (fill nulls and defaults), or this behavior could be added directly to the existing FileSinkExec plan.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org