You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "zhuqi-lucas (via GitHub)" <gi...@apache.org> on 2024/12/21 15:12:03 UTC
[PR] Support (order by / sort) for DataFrameWriteOptions [datafusion]
zhuqi-lucas opened a new pull request, #13874:
URL: https://github.com/apache/datafusion/pull/13874
## Which issue does this PR close?
Closes [#13873](https://github.com/apache/datafusion/issues/13873)
## Rationale for this change
```rust
DataFrameWriteOptions is missing an order by / sort by like available in SQL.
For sql we have the option to sort, for example:
You can use the WITH ORDER clause of the CREATE EXTERNAL TABLE if your data is already ordered
https://datafusion.apache.org/user-guide/sql/ddl.html#create-external-table
CREATE EXTERNAL TABLE test (
c1 VARCHAR NOT NULL,
c2 INT NOT NULL,
c3 SMALLINT NOT NULL,
c4 SMALLINT NOT NULL,
c5 INT NOT NULL,
c6 BIGINT NOT NULL,
c7 SMALLINT NOT NULL,
c8 INT NOT NULL,
c9 BIGINT NOT NULL,
c10 VARCHAR NOT NULL,
c11 FLOAT NOT NULL,
c12 DOUBLE NOT NULL,
c13 VARCHAR NOT NULL
)
STORED AS CSV
-- this line tells DataFusion the data in the file is already ordered by (c2 ASC)
WITH ORDER (c2 ASC)
LOCATION '/path/to/aggregate_test_100.csv'
OPTIONS ('has_header' 'true');
But for writing my parquet or other format files, we don't support it.
```
## What changes are included in this PR?
Add the sort support.
## Are these changes tested?
yes
Added unit testing.
## Are there any user-facing changes?
Yes, we support new order option for DataFrameWriteOptions
--
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: [PR] Support (order by / sort) for DataFrameWriteOptions [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #13874:
URL: https://github.com/apache/datafusion/pull/13874#discussion_r1894948500
##########
datafusion/core/src/dataframe/mod.rs:
##########
@@ -4057,4 +4094,178 @@ mod tests {
Ok(())
}
+
+ // Test issue: https://github.com/apache/datafusion/issues/13873
+ #[tokio::test]
+ async fn write_parquet_with_order() -> Result<()> {
+ let tmp_dir = TempDir::new()?;
+ let schema = Arc::new(Schema::new(vec![
+ Field::new("a", DataType::Int32, true),
+ Field::new("b", DataType::Int32, true),
+ ]));
+
+ let ctx = SessionContext::new();
+ let write_df = ctx.read_batch(RecordBatch::try_new(
+ schema.clone(),
+ vec![
+ Arc::new(Int32Array::from(vec![1, 5, 7, 3, 2])),
+ Arc::new(Int32Array::from(vec![2, 3, 4, 5, 6])),
+ ],
+ )?)?;
+
+ let test_path = tmp_dir.path().join("test.parquet");
+
+ write_df
+ .clone()
+ .write_parquet(
+ test_path.to_str().unwrap(),
+ DataFrameWriteOptions::new()
+ .with_sort_by(vec![col("a").sort(true, true)]),
+ None,
+ )
+ .await?;
+
+ let ctx = SessionContext::new();
+ ctx.register_parquet(
+ "data",
+ test_path.to_str().unwrap(),
+ ParquetReadOptions::default(),
+ )
+ .await?;
+
+ let df = ctx.sql("SELECT * FROM data").await?;
+ let results = df.collect().await?;
+
+ assert_batches_eq!(
+ &[
+ "+---+---+",
Review Comment:
this is a good test 👍
--
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: [PR] Support (order by / sort) for DataFrameWriteOptions [datafusion]
Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on code in PR #13874:
URL: https://github.com/apache/datafusion/pull/13874#discussion_r1894960842
##########
datafusion/core/src/dataframe/parquet.rs:
##########
@@ -74,8 +74,16 @@ impl DataFrame {
let file_type = format_as_file_type(format);
+ let plan = if options.sort_by.is_empty() {
+ self.plan
+ } else {
+ LogicalPlanBuilder::from(self.plan)
+ .sort(options.sort_by)?
+ .build()?
+ };
+
Review Comment:
Shouldn't `LogicalPlanBuilder::copy_to` have the ordering info as well?
If we write the info to a new table, do we know avoid sorting the table again? We should expose the ordering info to the newly created table for that.
--
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: [PR] Support (order by / sort) for DataFrameWriteOptions [datafusion]
Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on code in PR #13874:
URL: https://github.com/apache/datafusion/pull/13874#discussion_r1895051122
##########
datafusion/core/src/dataframe/parquet.rs:
##########
@@ -74,8 +74,16 @@ impl DataFrame {
let file_type = format_as_file_type(format);
+ let plan = if options.sort_by.is_empty() {
+ self.plan
+ } else {
+ LogicalPlanBuilder::from(self.plan)
+ .sort(options.sort_by)?
+ .build()?
+ };
+
Review Comment:
Yes I mean that after this PR we seem we don't communicate "we are sorted" yet, which is I think would support usecases such as in the issue from @TheBuilderJR (write data sorted on a column, making reading with `order by` on the same column fast).
--
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: [PR] Support (order by / sort) for DataFrameWriteOptions [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #13874:
URL: https://github.com/apache/datafusion/pull/13874#discussion_r1894978630
##########
datafusion/core/src/dataframe/parquet.rs:
##########
@@ -74,8 +74,16 @@ impl DataFrame {
let file_type = format_as_file_type(format);
+ let plan = if options.sort_by.is_empty() {
+ self.plan
+ } else {
+ LogicalPlanBuilder::from(self.plan)
+ .sort(options.sort_by)?
+ .build()?
+ };
+
Review Comment:
I think copy_to inserts the data from whatever the input plan is (if there is a redundant sort the optimizer will remove it). Is that what you are asking?
It is an interesting question of how to communicate "this table is sorted" information for newly written files. Is this what you are talking about?
--
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: [PR] Support (order by / sort) for DataFrameWriteOptions [datafusion]
Posted by "zhuqi-lucas (via GitHub)" <gi...@apache.org>.
zhuqi-lucas commented on PR #13874:
URL: https://github.com/apache/datafusion/pull/13874#issuecomment-2558886583
Updated the code to also include the write_table testing case which is the same with insert into 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: [PR] Support (order by / sort) for DataFrameWriteOptions [datafusion]
Posted by "zhuqi-lucas (via GitHub)" <gi...@apache.org>.
zhuqi-lucas commented on code in PR #13874:
URL: https://github.com/apache/datafusion/pull/13874#discussion_r1895145562
##########
datafusion/core/src/dataframe/parquet.rs:
##########
@@ -74,8 +74,16 @@ impl DataFrame {
let file_type = format_as_file_type(format);
+ let plan = if options.sort_by.is_empty() {
+ self.plan
+ } else {
+ LogicalPlanBuilder::from(self.plan)
+ .sort(options.sort_by)?
+ .build()?
+ };
+
Review Comment:
Thank you @alamb @Dandandan for review, i am also interested how we can communicate "this table is sorted" information for newly written files. I will also investigate 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
Re: [PR] Support (order by / sort) for DataFrameWriteOptions [datafusion]
Posted by "zhuqi-lucas (via GitHub)" <gi...@apache.org>.
zhuqi-lucas commented on PR #13874:
URL: https://github.com/apache/datafusion/pull/13874#issuecomment-2560602968
Thank you @alamb @Dandandan for review, it makes sense we continue investigating the solution for communicating the ordering of a file back with the existing listing table implementation.
I created a follow-up issue for this:
https://github.com/apache/datafusion/issues/13891
--
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: [PR] Support (order by / sort) for DataFrameWriteOptions [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #13874:
URL: https://github.com/apache/datafusion/pull/13874#discussion_r1896169061
##########
datafusion/core/src/dataframe/parquet.rs:
##########
@@ -74,8 +74,16 @@ impl DataFrame {
let file_type = format_as_file_type(format);
+ let plan = if options.sort_by.is_empty() {
+ self.plan
+ } else {
+ LogicalPlanBuilder::from(self.plan)
+ .sort(options.sort_by)?
+ .build()?
+ };
+
Review Comment:
I think the ordering information is normally handled by a higher level "catalog" rather than the parquet format itself.
This is the only thing I know of in Parquet, but I don't think it can describe the ordering
https://github.com/apache/parquet-format/blob/d784f11f4485e64fdeaa614e0bde125f5132093d/src/main/thrift/parquet.thrift#L587-L591
Maybe this is something that https://iceberg.apache.org/ could represent 🤔
It is also conceivable that DataFusion itself could write custom metadata in paquet and other formats that support that custom metadata with the ordering, but that seems like we would just be reinventing Iceberg and similar table formats
--
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: [PR] Support (order by / sort) for DataFrameWriteOptions [datafusion]
Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on code in PR #13874:
URL: https://github.com/apache/datafusion/pull/13874#discussion_r1894960842
##########
datafusion/core/src/dataframe/parquet.rs:
##########
@@ -74,8 +74,16 @@ impl DataFrame {
let file_type = format_as_file_type(format);
+ let plan = if options.sort_by.is_empty() {
+ self.plan
+ } else {
+ LogicalPlanBuilder::from(self.plan)
+ .sort(options.sort_by)?
+ .build()?
+ };
+
Review Comment:
Shouldn't `LogicalPlabbuolder::copy_to` have the ordering info as well?
If we write the info to a new table, do we know avoid sorting the table again? We should expose the ordering info to the newly created table for that.
--
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: [PR] Support (order by / sort) for DataFrameWriteOptions [datafusion]
Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on code in PR #13874:
URL: https://github.com/apache/datafusion/pull/13874#discussion_r1895051122
##########
datafusion/core/src/dataframe/parquet.rs:
##########
@@ -74,8 +74,16 @@ impl DataFrame {
let file_type = format_as_file_type(format);
+ let plan = if options.sort_by.is_empty() {
+ self.plan
+ } else {
+ LogicalPlanBuilder::from(self.plan)
+ .sort(options.sort_by)?
+ .build()?
+ };
+
Review Comment:
Yes I mean that after this PR we seem we don't communicate "we are sorted" yet, which is I think would support usecases such as in the issue from @TheBuilderJR (write data sorted on a column, making following read query with `order by` on the same column fast).
--
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: [PR] Support (order by / sort) for DataFrameWriteOptions [datafusion]
Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on PR #13874:
URL: https://github.com/apache/datafusion/pull/13874#issuecomment-2560941432
Thank you @zhuqi-lucas
--
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: [PR] Support (order by / sort) for DataFrameWriteOptions [datafusion]
Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan merged PR #13874:
URL: https://github.com/apache/datafusion/pull/13874
--
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