You are viewing a plain text version of this content. The canonical link for it is here.

Posted to github@arrow.apache.org by "devinjdangelo (via GitHub)" <gi...@apache.org> on 2024/02/15 15:02:45 UTC

[PR] Support Copy To Partitioned Files [arrow-datafusion]

devinjdangelo opened a new pull request, #9240:
URL: https://github.com/apache/arrow-datafusion/pull/9240

   ## Which issue does this PR close?
   
   Closes #9237
   Closes #8493
   
   ## Rationale for this change
   
   We currently support writing to partitioned listing tables. It would be nice to leverage the same physical plan implementation within a CopyTo statement, so users can skip registering a table if they don't need one.
   
   ## What changes are included in this PR?
   
   - Extends CopyTo LogicalPlan to support partition_by option
   - Extends CopyTo physical planning to propagate partition_by columns to the FileSinkExec plan
   - Extends DataFrameWriteOptions to support the same partition_by option
   - Adds partition column DataType inference to demux code so users do not have to explicitly specify data type in the COPY statement.
   
   With these changes we can support queries like the following:
   
   ```sql
   COPY source_table TO 'test_files/scratch/copy/partitioned_table1/' 
   (format parquet, compression 'zstd(10)', partition_by 'col2');
   
   COPY (values (1, 'a', 'x'), (2, 'b', 'y'), (3, 'c', 'z')) TO 'test_files/scratch/copy/partitioned_table2/' 
   (format parquet, compression 'zstd(10)', partition_by 'column2, column3');
   ```
   
   ## Are these changes tested?
   
   Yes, the examples above are validated in copy.slt tests.
   
   ## Are there any user-facing changes?
   
   Copying to partition files is easier now


-- 
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: [PR] Support Copy To Partitioned Files [arrow-datafusion]

Posted by "devinjdangelo (via GitHub)" <gi...@apache.org>.

devinjdangelo commented on code in PR #9240:
URL: https://github.com/apache/arrow-datafusion/pull/9240#discussion_r1493058196


##########
datafusion/sqllogictest/test_files/copy.slt:
##########
@@ -25,6 +25,90 @@ COPY source_table TO 'test_files/scratch/copy/table/' (format parquet, compressi
 ----
 2
 
+# Copy to directory as partitioned files
+query IT
+COPY source_table TO 'test_files/scratch/copy/partitioned_table1/' (format parquet, compression 'zstd(10)', partition_by 'col2');
+----
+2
+
+# validate multiple partitioned parquet file output
+statement ok
+CREATE EXTERNAL TABLE validate_partitioned_parquet STORED AS PARQUET 
+LOCATION 'test_files/scratch/copy/partitioned_table1/' PARTITIONED BY (col2);
+
+query I?
+select * from validate_partitioned_parquet order by col1, col2;
+----
+1 Foo
+2 Bar
+
+# validate partition paths were actually generated
+statement ok
+CREATE EXTERNAL TABLE validate_partitioned_parquet_bar STORED AS PARQUET 
+LOCATION 'test_files/scratch/copy/partitioned_table1/col2=Bar';
+
+query I
+select * from validate_partitioned_parquet_bar order by col1;
+----
+2
+
+# Copy to directory as partitioned files
+query ITT
+COPY (values (1, 'a', 'x'), (2, 'b', 'y'), (3, 'c', 'z')) TO 'test_files/scratch/copy/partitioned_table2/' 
+(format parquet, compression 'zstd(10)', partition_by 'column2, column3');
+----
+3
+
+# validate multiple partitioned parquet file output
+statement ok
+CREATE EXTERNAL TABLE validate_partitioned_parquet2 STORED AS PARQUET 
+LOCATION 'test_files/scratch/copy/partitioned_table2/' PARTITIONED BY (column2, column3);
+
+query I??
+select * from validate_partitioned_parquet2 order by column1,column2,column3;
+----
+1 a x
+2 b y
+3 c z
+
+statement ok
+CREATE EXTERNAL TABLE validate_partitioned_parquet_a_x STORED AS PARQUET 
+LOCATION 'test_files/scratch/copy/partitioned_table2/column2=a/column3=x';
+
+query I
+select * from validate_partitioned_parquet_a_x order by column1;
+----
+1
+
+statement ok
+create table test ("'test'" varchar, "'test2'" varchar, "'test3'" varchar); 
+
+query TTT
+insert into test VALUES ('a', 'x', 'aa'), ('b','y', 'bb'), ('c', 'z', 'cc')
+----
+3
+
+query T
+select "'test'" from test
+----
+a
+b
+c
+
+# Note to place a single ' inside of a literal string escape by putting two ''
+query TTT
+copy test to 'test_files/scratch/copy/escape_quote' (format csv, partition_by '''test2'',''test3''')
+----
+3
+
+statement ok
+CREATE EXTERNAL TABLE validate_partitioned_escape_quote STORED AS CSV 
+LOCATION 'test_files/scratch/copy/escape_quote/' PARTITIONED BY ("'test2'", "'test3'");
+
+# This triggers a panic (index out of bounds)

Review Comment:
   Trying to query on this table partitioned by a single quote containing column name panics with index out of bounds error.
   
   Manually inspecting the CSV suggests the previous COPY statement worked.
   
   As mentioned in other thread, I'm not sure if it makes sense to support ' in a partition path name. It will certainly get ugly if we try.



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