You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2023/08/17 12:25:14 UTC

[GitHub] [arrow-datafusion] alamb opened a new issue, #7317: Allowing setting sort order of parquet files without specifying the schema

alamb opened a new issue, #7317:
URL: https://github.com/apache/arrow-datafusion/issues/7317

   ### Is your feature request related to a problem or challenge?
   
   This is a follow on to https://github.com/apache/arrow-datafusion/issues/7036
   
   As @bmmeijers says in https://github.com/apache/arrow-datafusion/issues/7036, datafusion can make much better plans if you tell it about the sort order of files. 
   
   It is possible now to specify the order of a parquet file
   
   ```sql
   $ datafusion-cli
   DataFusion CLI v29.0.0
   ❯ create external table cpu(time timestamp) stored as parquet location 'cpu.parquet' with order (time desc);
   0 rows in set. Query took 0.001 seconds.
   
   ❯ select * from cpu;
   +---------------------+
   | time                |
   +---------------------+
   | 2022-09-30T12:55:00 |
   +---------------------+
   1 row in set. Query took 0.003 seconds.
   
   ❯ explain select * from cpu order by time desc;
   +---------------+-----------------------------------------------------------------------------------------------------------------------------+
   | plan_type     | plan                                                                                                                        |
   +---------------+-----------------------------------------------------------------------------------------------------------------------------+
   | logical_plan  | Sort: cpu.time DESC NULLS FIRST                                                                                             |
   |               |   TableScan: cpu projection=[time]                                                                                          |
   | physical_plan | ParquetExec: file_groups={1 group: [[Users/alamb/Downloads/cpu.parquet]]}, projection=[time], output_ordering=[time@0 DESC] |
   |               |                                                                                                                             |
   +---------------+-----------------------------------------------------------------------------------------------------------------------------+
   2 rows in set. Query took 0.001 seconds.
   ```
   
   However, it is not possible to specify the time without also specifying all of the schema, which is redundant given the schema is stored in the parquet files:
   
   ```sql
   ❯ create external table cpu stored as parquet location 'cpu.parquet' with order (time desc);
   Error during planning: Provide a schema before specifying the order while creating a table.
   ```
   
   Even though DataFusion can infer the schema automatically
   
   ```sql
   ❯ create external table cpu stored as parquet location 'cpu.parquet';
   0 rows in set. Query took 0.002 seconds.
   
   ❯ select * from cpu;
   +-----+---------------------+
   | v   | time                |
   +-----+---------------------+
   | 1.0 | 2023-03-01T00:00:00 |
   | 2.0 | 2023-03-02T00:00:00 |
   +-----+---------------------+
   2 rows in set. Query took 0.002 seconds.
   ```
   
   ### Describe the solution you'd like
   
   I would like to be able to specify the sort order for parquet files without also specifying the schema
   
   Given this parquet file: [cpu.zip](https://github.com/apache/arrow-datafusion/files/12369253/cpu.zip)
   
   I would like this to work and produce a table both columns `v` and `time` ordered by `time`:
   
   ```sql
   ❯ create external table cpu stored as parquet location 'cpu.parquet' with order (time);
   Error during planning: Provide a schema before specifying the order while creating a table.
   ```
   
   
   ### Describe alternatives you've considered
   
   _No response_
   
   ### 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] Allowing setting sort order of parquet files without specifying the schema [arrow-datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #7317:
URL: https://github.com/apache/arrow-datafusion/issues/7317#issuecomment-1898936140

   FYY @helenosheaa I think implementing this DataFusion feature would help us potentially reproduce issues more easily downstream in IOx (as we could use datafusion-cli to scan files more closely to the way the IOx querier and compactor do)


-- 
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] Allowing setting sort order of parquet files without specifying the schema [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #7317: Allowing setting sort order of parquet files without specifying the schema
URL: https://github.com/apache/datafusion/issues/7317


-- 
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@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


[GitHub] [arrow-datafusion] judahrand commented on issue #7317: Allowing setting sort order of parquet files without specifying the schema

Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on issue #7317:
URL: https://github.com/apache/arrow-datafusion/issues/7317#issuecomment-1713552909

   @alamb What do you think of splitting `file_format` out into its own crate similar to what you're doing with `physical_plan`? The `infer_schema` method is really needed in `datafusion-sql` to make this issue solvable (I think).


-- 
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


[GitHub] [arrow-datafusion] alamb commented on issue #7317: Allowing setting sort order of parquet files without specifying the schema

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #7317:
URL: https://github.com/apache/arrow-datafusion/issues/7317#issuecomment-1682195092

   I think this is a good first issue as all the code exists, it is just a matter of hooking it up and writing some tests (I think)


-- 
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


[GitHub] [arrow-datafusion] akoshchiy commented on issue #7317: Allowing setting sort order of parquet files without specifying the schema

Posted by "akoshchiy (via GitHub)" <gi...@apache.org>.
akoshchiy commented on issue #7317:
URL: https://github.com/apache/arrow-datafusion/issues/7317#issuecomment-1693110381

   yeah, it was too naive :) Now I see that `build_order_by` requires a lot of stuff from the `DFSchema`.


-- 
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] Allowing setting sort order of parquet files without specifying the schema [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #7317:
URL: https://github.com/apache/datafusion/issues/7317#issuecomment-2351551869

   Thank you @devanbenz  -- this will actually be super valuable to InfluxData as well (as we heavily rely on sorted parquet, but creating reproducers can't be done with pure-sql)


-- 
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@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Allowing setting sort order of parquet files without specifying the schema [datafusion]

Posted by "devanbenz (via GitHub)" <gi...@apache.org>.
devanbenz commented on issue #7317:
URL: https://github.com/apache/datafusion/issues/7317#issuecomment-2351007739

   take


-- 
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@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Allowing setting sort order of parquet files without specifying the schema [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #7317: Allowing setting sort order of parquet files without specifying the schema
URL: https://github.com/apache/datafusion/issues/7317


-- 
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@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Allowing setting sort order of parquet files without specifying the schema [datafusion]

Posted by "devanbenz (via GitHub)" <gi...@apache.org>.
devanbenz commented on issue #7317:
URL: https://github.com/apache/datafusion/issues/7317#issuecomment-2351007725

   Appears no one is working on this currently and the error is still occurring as of today:
   ```
   DataFusion CLI v41.0.0
   > create external table cpu stored as parquet location 'cpu.parquet' with order (time);
   Error during planning: Provide a schema before specifying the order while creating a table.
   ```
   
   I'll go ahead and take this


-- 
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@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


[GitHub] [arrow-datafusion] alamb commented on issue #7317: Allowing setting sort order of parquet files without specifying the schema

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #7317:
URL: https://github.com/apache/arrow-datafusion/issues/7317#issuecomment-1714295732

   >  It almost seems like the datasource module should live in datafusion-common?
   
   I think this would be very challenging as the datasource module has physical plans in it as well
   
   > @alamb What do you think of splitting file_format out into its own crate similar to what you're doing with
   
   @judahrand  -- If you are referring to https://github.com/apache/arrow-datafusion/tree/main/datafusion/core/src/datasource/file_format it may be challenging given that it depends on ExecutionPlan (which is in datafusion-core at the moment)
   
   I think the key dependency is that FileScanConfig has embedded `PhysicalExpr` which are not yet created during planning
   
   https://github.com/apache/arrow-datafusion/blob/4abae3b4fadeadc8a368155e14186016117529c8/datafusion/core/src/datasource/physical_plan/file_scan_config.rs#L73C1-L108
   
   What I would recommend is updating the FileScanConfig if possible (or making an equivalent in LogicalPlan) so that it represents sort order in terms of `Expr` (the logical expressions) and then converting `Expr` --> `PhysicalExpr` during physical planning


-- 
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


[GitHub] [arrow-datafusion] judahrand commented on issue #7317: Allowing setting sort order of parquet files without specifying the schema

Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on issue #7317:
URL: https://github.com/apache/arrow-datafusion/issues/7317#issuecomment-1713499796

   It almost seems like the `datasource` module should live in `datafusion-common`?


-- 
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


[GitHub] [arrow-datafusion] parkma99 commented on issue #7317: Allowing setting sort order of parquet files without specifying the schema

Posted by "parkma99 (via GitHub)" <gi...@apache.org>.
parkma99 commented on issue #7317:
URL: https://github.com/apache/arrow-datafusion/issues/7317#issuecomment-1692985230

   Have a try 😄, I just confusing how to decouple this code.


-- 
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


[GitHub] [arrow-datafusion] akoshchiy commented on issue #7317: Allowing setting sort order of parquet files without specifying the schema

Posted by "akoshchiy (via GitHub)" <gi...@apache.org>.
akoshchiy commented on issue #7317:
URL: https://github.com/apache/arrow-datafusion/issues/7317#issuecomment-1692960008

   Hi! I also had the plans to try it, but you were faster :) My idea was to completely remove the schema fields check in `build_order_by` and move it after schema infer. 


-- 
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


[GitHub] [arrow-datafusion] parkma99 commented on issue #7317: Allowing setting sort order of parquet files without specifying the schema

Posted by "parkma99 (via GitHub)" <gi...@apache.org>.
parkma99 commented on issue #7317:
URL: https://github.com/apache/arrow-datafusion/issues/7317#issuecomment-1692771675

   Hello , I would like work this ticket to prepare #7354 . But I find it's a little hard to hook all coe up.
   
   https://github.com/apache/arrow-datafusion/blob/ea9144e6597593c09a4ef0b71a4da1cdfaca8249/datafusion/sql/src/statement.rs#L660-L665
   
   in here raise the plan error `Provide a schema before specifying the order while creating a table.`
   
   https://github.com/apache/arrow-datafusion/blob/ea9144e6597593c09a4ef0b71a4da1cdfaca8249/datafusion/core/src/datasource/listing_table_factory.rs#L187-L190
   
   in here infer the schema.
   
   I would like to move `build_order_by` after `infer the schema`, but `build_order_by` is member function of `SqlToRel`
   
   I would like to move `infer the schema` before `build_order_by`, but `infer_schema` need `SessionState` .


-- 
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


[GitHub] [arrow-datafusion] parkma99 commented on issue #7317: Allowing setting sort order of parquet files without specifying the schema

Posted by "parkma99 (via GitHub)" <gi...@apache.org>.
parkma99 commented on issue #7317:
URL: https://github.com/apache/arrow-datafusion/issues/7317#issuecomment-1707598549

   @akoshchiy Would you have some ideas ? 😊😊


-- 
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