You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "goldmedal (via GitHub)" <gi...@apache.org> on 2024/10/08 15:51:35 UTC

[PR] Introdcue `binary_as_string` parquet option [datafusion]

goldmedal opened a new pull request, #12816:
URL: https://github.com/apache/datafusion/pull/12816

   ## Which issue does this PR close?
   
   <!--
   We generally require a GitHub issue to be filed for all bug fixes and enhancements and this helps us generate change logs for our releases. You can link an issue to this PR using the GitHub syntax. For example `Closes #123` indicates that this PR will close issue #123.
   -->
   
   Closes #12788 .
   
   ## Rationale for this change
   
   <!--
    Why are you proposing this change? If this is already explained clearly in the issue then this section is not needed.
    Explaining clearly why changes are proposed helps reviewers understand your changes and offer better suggestions for fixes.  
   -->
   
   ## What changes are included in this PR?
   
   <!--
   There is no need to duplicate the description in the issue here but it is sometimes worth providing a summary of the individual changes in this PR.
   -->
   
   ## Are these changes tested?
   
   <!--
   We typically require tests for all PRs in order to:
   1. Prevent the code from being accidentally broken by subsequent changes
   2. Serve as another way to document the expected behavior of the code
   
   If tests are not included in your PR, please explain why (for example, are they covered by existing tests)?
   -->
   
   ## Are there any user-facing changes?
   
   <!--
   If there are user-facing changes then we may require documentation to be updated before approving the PR.
   -->
   
   <!--
   If there are any breaking changes to public APIs, please add the `api change` label.
   -->
   


-- 
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] Introduce `binary_as_string` parquet option, upgrade to arrow/parquet `53.2.0` [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb merged PR #12816:
URL: https://github.com/apache/datafusion/pull/12816


-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "goldmedal (via GitHub)" <gi...@apache.org>.
goldmedal commented on PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#issuecomment-2405668858

   @alamb
   I have confirmed this feature works well and added some tests for it. Only some concerns about https://github.com/apache/datafusion/pull/12816#discussion_r1795799303.
   You can feel free to push any changes if needed.


-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "goldmedal (via GitHub)" <gi...@apache.org>.
goldmedal commented on code in PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#discussion_r1803996980


##########
datafusion/core/src/datasource/file_format/mod.rs:
##########
@@ -235,20 +235,26 @@ pub fn file_type_to_format(
     }
 }
 
+/// Create a new field with the specified data type, copying the other
+/// properties from the input field
+fn field_with_new_type(field: &FieldRef, new_type: DataType) -> FieldRef {
+    Arc::new(field.as_ref().clone().with_data_type(new_type))
+}
+
 /// Transform a schema to use view types for Utf8 and Binary
+///
+/// See [parquet::ParquetFormat::force_view_types] for details
 pub fn transform_schema_to_view(schema: &Schema) -> Schema {
     let transformed_fields: Vec<Arc<Field>> = schema
         .fields
         .iter()
         .map(|field| match field.data_type() {
-            DataType::Utf8 | DataType::LargeUtf8 => Arc::new(
-                Field::new(field.name(), DataType::Utf8View, field.is_nullable())
-                    .with_metadata(field.metadata().to_owned()),
-            ),
-            DataType::Binary | DataType::LargeBinary => Arc::new(
-                Field::new(field.name(), DataType::BinaryView, field.is_nullable())
-                    .with_metadata(field.metadata().to_owned()),
-            ),
+            DataType::Utf8 | DataType::LargeUtf8 => {

Review Comment:
   Thanks! It makes sense to me. ๐Ÿ‘ 



-- 
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] Introduce `binary_as_string` parquet option, upgrade to arrow/parquet `53.2.0` [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#issuecomment-2437722148

   > Thanks, @alamb. I have one question about the test. Others look good to me.
   
   Thanks @goldmedal  -- that was an excellent catch. I have fixed the issue


-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#issuecomment-2407558141

   I am starting to play around with this PR / write some tests. Will post my updates shortly


-- 
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] Introduce `binary_as_string` parquet option, upgrade to arrow/parquet `53.2.0` [datafusion]

Posted by "goldmedal (via GitHub)" <gi...@apache.org>.
goldmedal commented on code in PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#discussion_r1816119853


##########
datafusion/sqllogictest/test_files/parquet.slt:
##########
@@ -348,3 +348,204 @@ DROP TABLE list_columns;
 # Clean up
 statement ok
 DROP TABLE listing_table;
+
+### Tests for binary_ar_string
+
+# This scenario models the case where a column has been stored in parquet
+# "binary" column (without a String logical type annotation)
+# this is the case with the `hits_partitioned` ClickBench datasets
+# see https://github.com/apache/datafusion/issues/12788
+
+## Create a table with a binary column
+
+query I
+COPY (
+  SELECT
+    arrow_cast(string_col, 'Binary')      as binary_col,
+    arrow_cast(string_col, 'LargeBinary') as largebinary_col,
+    arrow_cast(string_col, 'BinaryView')  as binaryview_col
+  FROM src_table
+  )
+TO 'test_files/scratch/parquet/binary_as_string.parquet'
+STORED AS PARQUET;
+----
+9
+
+# Test 1: Read table with default options
+statement ok
+CREATE EXTERNAL TABLE binary_as_string_default
+STORED AS PARQUET LOCATION 'test_files/scratch/parquet/binary_as_string.parquet'
+
+# NB the data is read and displayed as binary
+query T?T?T?
+select
+  arrow_typeof(binary_col),      binary_col,
+  arrow_typeof(largebinary_col), largebinary_col,
+  arrow_typeof(binaryview_col),  binaryview_col
+ FROM binary_as_string_default;
+----
+Binary 616161 Binary 616161 Binary 616161
+Binary 626262 Binary 626262 Binary 626262
+Binary 636363 Binary 636363 Binary 636363
+Binary 646464 Binary 646464 Binary 646464
+Binary 656565 Binary 656565 Binary 656565
+Binary 666666 Binary 666666 Binary 666666
+Binary 676767 Binary 676767 Binary 676767
+Binary 686868 Binary 686868 Binary 686868
+Binary 696969 Binary 696969 Binary 696969
+
+# Run an explain plan to show the cast happens in the plan (a CAST is needed for the predicates)
+query TT
+EXPLAIN
+  SELECT binary_col, largebinary_col, binaryview_col
+  FROM binary_as_string_default
+ WHERE
+   binary_col LIKE '%a%' AND
+   largebinary_col LIKE '%a%' AND
+   binaryview_col LIKE '%a%';
+----
+logical_plan
+01)Filter: CAST(binary_as_string_default.binary_col AS Utf8) LIKE Utf8("%a%") AND CAST(binary_as_string_default.largebinary_col AS Utf8) LIKE Utf8("%a%") AND CAST(binary_as_string_default.binaryview_col AS Utf8) LIKE Utf8("%a%")
+02)--TableScan: binary_as_string_default projection=[binary_col, largebinary_col, binaryview_col], partial_filters=[CAST(binary_as_string_default.binary_col AS Utf8) LIKE Utf8("%a%"), CAST(binary_as_string_default.largebinary_col AS Utf8) LIKE Utf8("%a%"), CAST(binary_as_string_default.binaryview_col AS Utf8) LIKE Utf8("%a%")]
+physical_plan
+01)CoalesceBatchesExec: target_batch_size=8192
+02)--FilterExec: CAST(binary_col@0 AS Utf8) LIKE %a% AND CAST(largebinary_col@1 AS Utf8) LIKE %a% AND CAST(binaryview_col@2 AS Utf8) LIKE %a%
+03)----RepartitionExec: partitioning=RoundRobinBatch(2), input_partitions=1
+04)------ParquetExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet/binary_as_string.parquet]]}, projection=[binary_col, largebinary_col, binaryview_col], predicate=CAST(binary_col@0 AS Utf8) LIKE %a% AND CAST(largebinary_col@1 AS Utf8) LIKE %a% AND CAST(binaryview_col@2 AS Utf8) LIKE %a%
+
+
+statement ok
+DROP TABLE binary_as_string_default;
+
+## Test 2: Read table using the binary_as_string option
+
+statement ok
+CREATE EXTERNAL TABLE binary_as_string_option
+STORED AS PARQUET LOCATION 'test_files/scratch/parquet/binary_as_string.parquet'
+OPTIONS ('binary_as_string' 'true');
+
+# NB the data is read and displayed as string
+query TTTTTT
+select
+  arrow_typeof(binary_col),      binary_col,
+  arrow_typeof(largebinary_col), largebinary_col,
+  arrow_typeof(binaryview_col),  binaryview_col
+ FROM binary_as_string_option;
+----
+Utf8 aaa Utf8 aaa Utf8 aaa
+Utf8 bbb Utf8 bbb Utf8 bbb
+Utf8 ccc Utf8 ccc Utf8 ccc
+Utf8 ddd Utf8 ddd Utf8 ddd
+Utf8 eee Utf8 eee Utf8 eee
+Utf8 fff Utf8 fff Utf8 fff
+Utf8 ggg Utf8 ggg Utf8 ggg
+Utf8 hhh Utf8 hhh Utf8 hhh
+Utf8 iii Utf8 iii Utf8 iii
+
+# Run an explain plan to show the cast happens in the plan (there should be no casts)
+query TT
+EXPLAIN
+  SELECT binary_col, largebinary_col, binaryview_col
+  FROM binary_as_string_option
+ WHERE
+   binary_col LIKE '%a%' AND
+   largebinary_col LIKE '%a%' AND
+   binaryview_col LIKE '%a%';
+----
+logical_plan
+01)Filter: binary_as_string_option.binary_col LIKE Utf8("%a%") AND binary_as_string_option.largebinary_col LIKE Utf8("%a%") AND binary_as_string_option.binaryview_col LIKE Utf8("%a%")
+02)--TableScan: binary_as_string_option projection=[binary_col, largebinary_col, binaryview_col], partial_filters=[binary_as_string_option.binary_col LIKE Utf8("%a%"), binary_as_string_option.largebinary_col LIKE Utf8("%a%"), binary_as_string_option.binaryview_col LIKE Utf8("%a%")]
+physical_plan
+01)CoalesceBatchesExec: target_batch_size=8192
+02)--FilterExec: binary_col@0 LIKE %a% AND largebinary_col@1 LIKE %a% AND binaryview_col@2 LIKE %a%
+03)----RepartitionExec: partitioning=RoundRobinBatch(2), input_partitions=1
+04)------ParquetExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet/binary_as_string.parquet]]}, projection=[binary_col, largebinary_col, binaryview_col], predicate=binary_col@0 LIKE %a% AND largebinary_col@1 LIKE %a% AND binaryview_col@2 LIKE %a%
+
+
+statement ok
+DROP TABLE binary_as_string_option;
+
+## Test 3: Read table with binary_as_string option AND schema_force_view_types
+
+statement ok
+CREATE EXTERNAL TABLE binary_as_string_both
+STORED AS PARQUET LOCATION 'test_files/scratch/parquet/binary_as_string.parquet'
+OPTIONS (
+  'binary_as_string' 'true',
+  'schema_force_view_types' 'true'
+);
+
+# NB the data is read and displayed a StringView
+query error DataFusion error: SQL error: ParserError\("Expected: an SQL statement, found: Utf8View"\)
+select
+  arrow_typeof(binary_col),      binary_col,
+  arrow_typeof(largebinary_col), largebinary_col,
+  arrow_typeof(binaryview_col),  binaryview_col
+ FROM binary_as_string_both;
+ ----
+Utf8View aaa
+Utf8View bbb
+Utf8View ccc
+Utf8View ddd

Review Comment:
   Do you know why this query fails ๐Ÿค” ? It got a `ParserError` but I think it's a valid SQL.
   The test expects this query to fail but it still returns some results with the wrong schema.
   The query should have 6 columns per row but It only shows 2.
   It's weird. ๐Ÿค” 



-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#issuecomment-2408940698

   Here is the performance of this PR. Some queries are slower, some are faster. 
   
   I believe once we turn on string view everything will be faster. 
   
   ```
   --------------------
   Benchmark clickbench_partitioned.json
   --------------------
   โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”ณโ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”ณโ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”ณโ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”“
   โ”ƒ Query        โ”ƒ  main_base โ”ƒ feature_12788-binary-as-string-โ€ฆ โ”ƒ        Change โ”ƒ
   โ”กโ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ•‡โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ•‡โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ•‡โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”ฉ
   โ”‚ QQuery 0     โ”‚     2.26ms โ”‚                           2.22ms โ”‚     no change โ”‚
   โ”‚ QQuery 1     โ”‚    38.78ms โ”‚                          38.16ms โ”‚     no change โ”‚
   โ”‚ QQuery 2     โ”‚    92.96ms โ”‚                          94.66ms โ”‚     no change โ”‚
   โ”‚ QQuery 3     โ”‚    98.39ms โ”‚                         100.72ms โ”‚     no change โ”‚
   โ”‚ QQuery 4     โ”‚   928.83ms โ”‚                         929.22ms โ”‚     no change โ”‚
   โ”‚ QQuery 5     โ”‚   973.20ms โ”‚                         988.39ms โ”‚     no change โ”‚
   โ”‚ QQuery 6     โ”‚    33.75ms โ”‚                          36.60ms โ”‚  1.08x slower โ”‚
   โ”‚ QQuery 7     โ”‚    42.31ms โ”‚                          42.33ms โ”‚     no change โ”‚
   โ”‚ QQuery 8     โ”‚  1383.69ms โ”‚                        1364.35ms โ”‚     no change โ”‚
   โ”‚ QQuery 9     โ”‚  1323.14ms โ”‚                        1361.55ms โ”‚     no change โ”‚
   โ”‚ QQuery 10    โ”‚   351.92ms โ”‚                         413.62ms โ”‚  1.18x slower โ”‚
   โ”‚ QQuery 11    โ”‚   400.63ms โ”‚                         462.88ms โ”‚  1.16x slower โ”‚
   โ”‚ QQuery 12    โ”‚  1095.05ms โ”‚                        1101.34ms โ”‚     no change โ”‚
   โ”‚ QQuery 13    โ”‚  1753.33ms โ”‚                        1676.37ms โ”‚     no change โ”‚
   โ”‚ QQuery 14    โ”‚  1220.66ms โ”‚                        1253.31ms โ”‚     no change โ”‚
   โ”‚ QQuery 15    โ”‚  1099.67ms โ”‚                        1091.86ms โ”‚     no change โ”‚
   โ”‚ QQuery 16    โ”‚  2530.60ms โ”‚                        2536.06ms โ”‚     no change โ”‚
   โ”‚ QQuery 17    โ”‚  2299.37ms โ”‚                        2317.53ms โ”‚     no change โ”‚
   โ”‚ QQuery 18    โ”‚  5042.06ms โ”‚                        4983.43ms โ”‚     no change โ”‚
   โ”‚ QQuery 19    โ”‚    94.19ms โ”‚                          95.74ms โ”‚     no change โ”‚
   โ”‚ QQuery 20    โ”‚  1720.68ms โ”‚                        1493.36ms โ”‚ +1.15x faster โ”‚
   โ”‚ QQuery 21    โ”‚  2074.60ms โ”‚                        1824.64ms โ”‚ +1.14x faster โ”‚
   โ”‚ QQuery 22    โ”‚  5257.20ms โ”‚                        3143.86ms โ”‚ +1.67x faster โ”‚
   โ”‚ QQuery 23    โ”‚ 10530.69ms โ”‚                       10229.16ms โ”‚     no change โ”‚
   โ”‚ QQuery 24    โ”‚   590.29ms โ”‚                         654.78ms โ”‚  1.11x slower โ”‚
   โ”‚ QQuery 25    โ”‚   489.54ms โ”‚                         523.81ms โ”‚  1.07x slower โ”‚
   โ”‚ QQuery 26    โ”‚   653.48ms โ”‚                         714.03ms โ”‚  1.09x slower โ”‚
   โ”‚ QQuery 27    โ”‚  2585.81ms โ”‚                        2285.59ms โ”‚ +1.13x faster โ”‚
   โ”‚ QQuery 28    โ”‚ 15372.17ms โ”‚                       15562.00ms โ”‚     no change โ”‚
   โ”‚ QQuery 29    โ”‚   530.70ms โ”‚                         529.82ms โ”‚     no change โ”‚
   โ”‚ QQuery 30    โ”‚  1031.82ms โ”‚                        1094.30ms โ”‚  1.06x slower โ”‚
   โ”‚ QQuery 31    โ”‚  1121.86ms โ”‚                        1139.77ms โ”‚     no change โ”‚
   โ”‚ QQuery 32    โ”‚  4358.25ms โ”‚                        4290.06ms โ”‚     no change โ”‚
   โ”‚ QQuery 33    โ”‚  5154.15ms โ”‚                        5209.55ms โ”‚     no change โ”‚
   โ”‚ QQuery 34    โ”‚  5133.94ms โ”‚                        5172.32ms โ”‚     no change โ”‚
   โ”‚ QQuery 35    โ”‚  1947.97ms โ”‚                        1895.07ms โ”‚     no change โ”‚
   โ”‚ QQuery 36    โ”‚   270.40ms โ”‚                         262.39ms โ”‚     no change โ”‚
   โ”‚ QQuery 37    โ”‚   121.52ms โ”‚                         126.73ms โ”‚     no change โ”‚
   โ”‚ QQuery 38    โ”‚   143.79ms โ”‚                         141.60ms โ”‚     no change โ”‚
   โ”‚ QQuery 39    โ”‚   758.06ms โ”‚                         767.78ms โ”‚     no change โ”‚
   โ”‚ QQuery 40    โ”‚    52.74ms โ”‚                          56.09ms โ”‚  1.06x slower โ”‚
   โ”‚ QQuery 41    โ”‚    48.60ms โ”‚                          50.59ms โ”‚     no change โ”‚
   โ”‚ QQuery 42    โ”‚    64.42ms โ”‚                          63.09ms โ”‚     no change โ”‚
   โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜
   ```


-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "goldmedal (via GitHub)" <gi...@apache.org>.
goldmedal commented on PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#issuecomment-2405298447

   > I am about out of time to work on this today, but if no one else gets a chance to do this I'll try and polish this PR up tomorrow
   
   I would be able to help finish the remaining work before I sleep today. (Ensure this PR works well)
   


-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#issuecomment-2404976028

   > Submitted https://github.com/apache/arrow-rs/pull/6539 for it.
   
    @goldmedal   would you be ok if I pushed a change to this PR to temporarily patch arrow-rs to include the fix for https://github.com/apache/arrow-rs/pull/6539 ?
   
   Then we could get this PR ready to go (and I could use it to test with string view on by default)


-- 
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] Introduce `binary_as_string` parquet option, upgrade to arrow/parquet `53.2.0` [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#discussion_r1815631208


##########
Cargo.toml:
##########
@@ -70,22 +70,22 @@ version = "42.1.0"
 ahash = { version = "0.8", default-features = false, features = [
     "runtime-rng",
 ] }
-arrow = { version = "53.1.0", features = [
+arrow = { version = "53.2.0", features = [

Review Comment:
   this update is required to have access to https://github.com/apache/arrow-rs/pull/6539



-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#issuecomment-2405157842

   > > @goldmedal would you be ok if I pushed a change to this PR to temporarily patch arrow-rs to include the fix for [apache/arrow-rs#6539](https://github.com/apache/arrow-rs/pull/6539) ?
   > > Then we could get this PR ready to go (and I could use it to test with string view on by default)
   > 
   > Sure. feel free to do it. Thanks!
   
   I pushed the change in https://github.com/apache/datafusion/pull/12816/commits/d7c35658d3f5cb9606e4952b79f13c12c766a41b
   
   I am about out of time to work on this today, but if no one else gets a chance to do this I'll try and polish this PR up tomorrow


-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "goldmedal (via GitHub)" <gi...@apache.org>.
goldmedal commented on code in PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#discussion_r1795799303


##########
datafusion/core/src/datasource/file_format/mod.rs:
##########
@@ -302,6 +302,87 @@ pub(crate) fn coerce_file_schema_to_view_type(
     ))
 }
 
+/// Transform a schema to force binary types to be strings
+pub fn transform_binary_to_string(schema: &Schema) -> Schema {
+    let transformed_fields: Vec<Arc<Field>> = schema
+        .fields
+        .iter()
+        .map(|field| match field.data_type() {
+            DataType::Binary => Arc::new(
+                Field::new(field.name(), DataType::Utf8, field.is_nullable())
+                    .with_metadata(field.metadata().to_owned()),
+            ),
+            DataType::LargeBinary => Arc::new(
+                Field::new(field.name(), DataType::LargeUtf8, field.is_nullable())
+                    .with_metadata(field.metadata().to_owned()),
+            ),
+            _ => field.clone(),
+        })
+        .collect();
+    Schema::new_with_metadata(transformed_fields, schema.metadata.clone())
+}
+
+/// If the table schema uses a string type, coerce the file schema to use a string type.
+pub(crate) fn coerce_file_schema_to_string_type(
+    table_schema: &Schema,
+    file_schema: &Schema,
+) -> Option<Schema> {
+    let mut transform = false;
+    let table_fields: HashMap<_, _> = table_schema
+        .fields
+        .iter()
+        .map(|f| (f.name(), f.data_type()))
+        .collect();
+    let transformed_fields: Vec<Arc<Field>> = file_schema
+        .fields
+        .iter()
+        .map(
+            |field| match (table_fields.get(field.name()), field.data_type()) {
+                (Some(DataType::Utf8), DataType::Binary) => {
+                    transform = true;
+                    Arc::new(Field::new(
+                        field.name(),
+                        DataType::Utf8,
+                        field.is_nullable(),
+                    ))
+                }
+                (Some(DataType::LargeUtf8), DataType::LargeBinary) => {

Review Comment:
   Actually, this case isn't covered by testing because the Arrow reader always marks `BYTE_ARRAY` as `Utf8` or `Binary`. I'm not pretty sure if we need it. ๐Ÿค” 



-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#issuecomment-2435009622

   Update here is we are on track to release a version of arrow with the required fixes today and then I will merge this PR up and get it ready for review โฒ๏ธ 


-- 
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] Introduce `binary_as_string` parquet option, upgrade to arrow/parquet `53.2.0` [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#discussion_r1815642965


##########
datafusion/core/src/datasource/file_format/mod.rs:
##########
@@ -235,20 +235,26 @@ pub fn file_type_to_format(
     }
 }
 
+/// Create a new field with the specified data type, copying the other
+/// properties from the input field
+fn field_with_new_type(field: &FieldRef, new_type: DataType) -> FieldRef {
+    Arc::new(field.as_ref().clone().with_data_type(new_type))
+}
+
 /// Transform a schema to use view types for Utf8 and Binary
+///
+/// See [parquet::ParquetFormat::force_view_types] for details
 pub fn transform_schema_to_view(schema: &Schema) -> Schema {
     let transformed_fields: Vec<Arc<Field>> = schema
         .fields
         .iter()
         .map(|field| match field.data_type() {
-            DataType::Utf8 | DataType::LargeUtf8 => Arc::new(
-                Field::new(field.name(), DataType::Utf8View, field.is_nullable())
-                    .with_metadata(field.metadata().to_owned()),
-            ),
-            DataType::Binary | DataType::LargeBinary => Arc::new(
-                Field::new(field.name(), DataType::BinaryView, field.is_nullable())
-                    .with_metadata(field.metadata().to_owned()),
-            ),
+            DataType::Utf8 | DataType::LargeUtf8 => {

Review Comment:
   arrow is released. I think this PR is ready for review



-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#issuecomment-2411139325

   Since this PR requires a change to arrow-rs, I think there is no particular rush to merge it in -- I have a few thoughts about how to make the code a bit simpler and hope to propose some changes over the next few days


-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#issuecomment-2408955172

   I reabased / squashed all the code in this branch so it would be easier to pull in to test in https://github.com/apache/datafusion/pull/12092


-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#issuecomment-2407784491

   I am running some benchmarks on this PR 


-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "goldmedal (via GitHub)" <gi...@apache.org>.
goldmedal commented on PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#issuecomment-2405020464

   > @goldmedal would you be ok if I pushed a change to this PR to temporarily patch arrow-rs to include the fix for [apache/arrow-rs#6539](https://github.com/apache/arrow-rs/pull/6539) ?
   > 
   > Then we could get this PR ready to go (and I could use it to test with string view on by default)
   
   Sure. feel free to do it. Thanks!


-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#discussion_r1803746194


##########
benchmarks/src/clickbench.rs:
##########
@@ -115,12 +115,15 @@ impl RunOpt {
             None => queries.min_query_id()..=queries.max_query_id(),
         };
 
+        // configure parquet options
         let mut config = self.common.config();
-        config
-            .options_mut()
-            .execution
-            .parquet
-            .schema_force_view_types = self.common.force_view_types;
+        {
+            let parquet_options = &mut config.options_mut().execution.parquet;
+            parquet_options.schema_force_view_types = self.common.force_view_types;
+            // The hits_partitioned dataset specifies string columns
+            // as binary due to how it was written. Force it to strings
+            parquet_options.binary_as_string = true;

Review Comment:
   we will have to mirror these options in the actual clickbench run



##########
datafusion/core/src/datasource/file_format/mod.rs:
##########
@@ -235,20 +235,26 @@ pub fn file_type_to_format(
     }
 }
 
+/// Create a new field with the specified data type, copying the other
+/// properties from the input field
+fn field_with_new_type(field: &FieldRef, new_type: DataType) -> FieldRef {
+    Arc::new(field.as_ref().clone().with_data_type(new_type))
+}
+
 /// Transform a schema to use view types for Utf8 and Binary
+///
+/// See [parquet::ParquetFormat::force_view_types] for details
 pub fn transform_schema_to_view(schema: &Schema) -> Schema {
     let transformed_fields: Vec<Arc<Field>> = schema
         .fields
         .iter()
         .map(|field| match field.data_type() {
-            DataType::Utf8 | DataType::LargeUtf8 => Arc::new(
-                Field::new(field.name(), DataType::Utf8View, field.is_nullable())
-                    .with_metadata(field.metadata().to_owned()),
-            ),
-            DataType::Binary | DataType::LargeBinary => Arc::new(
-                Field::new(field.name(), DataType::BinaryView, field.is_nullable())
-                    .with_metadata(field.metadata().to_owned()),
-            ),
+            DataType::Utf8 | DataType::LargeUtf8 => {

Review Comment:
   I reduced repetition of this code by encapsulating creating a new `FieldRef` from an existing `Field` in a function. I also think this will avoid the potential loss of metadata



-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#issuecomment-2417799655

   I am starting to get this PR ready


-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "goldmedal (via GitHub)" <gi...@apache.org>.
goldmedal commented on code in PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#discussion_r1795800690


##########
datafusion/core/src/datasource/file_format/mod.rs:
##########
@@ -302,6 +302,87 @@ pub(crate) fn coerce_file_schema_to_view_type(
     ))
 }
 
+/// Transform a schema to force binary types to be strings
+pub fn transform_binary_to_string(schema: &Schema) -> Schema {
+    let transformed_fields: Vec<Arc<Field>> = schema
+        .fields
+        .iter()
+        .map(|field| match field.data_type() {
+            DataType::Binary => Arc::new(
+                Field::new(field.name(), DataType::Utf8, field.is_nullable())
+                    .with_metadata(field.metadata().to_owned()),
+            ),
+            DataType::LargeBinary => Arc::new(
+                Field::new(field.name(), DataType::LargeUtf8, field.is_nullable())
+                    .with_metadata(field.metadata().to_owned()),
+            ),
+            _ => field.clone(),
+        })
+        .collect();
+    Schema::new_with_metadata(transformed_fields, schema.metadata.clone())
+}
+
+/// If the table schema uses a string type, coerce the file schema to use a string type.
+pub(crate) fn coerce_file_schema_to_string_type(
+    table_schema: &Schema,
+    file_schema: &Schema,
+) -> Option<Schema> {
+    let mut transform = false;
+    let table_fields: HashMap<_, _> = table_schema
+        .fields
+        .iter()
+        .map(|f| (f.name(), f.data_type()))
+        .collect();
+    let transformed_fields: Vec<Arc<Field>> = file_schema
+        .fields
+        .iter()
+        .map(
+            |field| match (table_fields.get(field.name()), field.data_type()) {
+                (Some(DataType::Utf8), DataType::Binary) => {
+                    transform = true;
+                    Arc::new(Field::new(
+                        field.name(),
+                        DataType::Utf8,
+                        field.is_nullable(),
+                    ))
+                }
+                (Some(DataType::LargeUtf8), DataType::LargeBinary) => {
+                    transform = true;
+                    Arc::new(Field::new(
+                        field.name(),
+                        DataType::LargeUtf8,
+                        field.is_nullable(),
+                    ))
+                }
+                // If `schema_force_view_types` is enabled, the actual data could be `Binary` or `LargeBinary`
+                // because we will first change the table schema for binary-to-string coercion, then apply the
+                // string-to-view transformation. So we need all binary types to be coerced to `Utf8View` here.
+                (
+                    Some(DataType::Utf8View),
+                    DataType::Binary | DataType::LargeBinary | DataType::BinaryView,

Review Comment:
   Same as above, testing doesn't cover the case for `DataType::LargeBinary` and `DataType::BinaryView`.



-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#issuecomment-2411137742

   > > Here is the performance of this PR. Some queries are slower, some are faster.
   > > I believe once we turn on string view everything will be faster.
   > 
   > Thanks @alamb It's interesting ๐Ÿค” Does this benchmark only include the change made by this PR, or does it include others? It seems there are many queries slowed down by this PR.
   
   It only includes changes made by this PR 
   
   The results with several other changes are here: https://github.com/apache/datafusion/pull/12092#issuecomment-2410952786 (and they are all faster ๐ŸŽ‰ )
   
   > 
   > Before this PR, the casting flow is
   > 
   > ```
   > Binary(parquet) -> Binary(arrow) -> BinaryView(arrow) -> StringView(arrow)
   > ```
   > 
   > Now, it's
   > 
   > ```
   > Binary(paruqet) -> StringView(arrow)
   > ```
   > 
   > Theoretically, we save the two steps (including the most expensive ones) for it. I have no idea why they would be slower. I might try to do some profiling for the slower cases ๐Ÿค”
   
   I think the reason it is slower is that there are some operations in the hash grouping code that have specializations for `StringArray`/`BinaryArray` but do not (yet) have specializations for StringView.  Specifically
   
   - https://github.com/apache/datafusion/pull/12792
   - https://github.com/apache/datafusion/pull/12809 from @Rachelint
   
   So while this PR makes the scan faster, the total time is slower as those paths dominated the query path. 
   
   When they are all put together we get the speedup we have been looking for


-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "goldmedal (via GitHub)" <gi...@apache.org>.
goldmedal commented on PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#issuecomment-2409843953

   > Here is the performance of this PR. Some queries are slower, some are faster.
   > 
   > I believe once we turn on string view everything will be faster.
   > 
   
   Thanks @alamb It's interesting ๐Ÿค” 
   Does this benchmark only include the change made by this PR, or does it include others?
   It seems there are many queries slowed down by this PR.
   
   Before this PR, the casting flow is 
   ```
   Binary(parquet) -> Binary(arrow) -> BinaryView(arrow) -> StringView(arrow)
   ```
   Now, it's
   ```
   Binary(paruqet) -> StringView(arrow)
   ```
   
   Theoretically, we save the two steps (including the most expensive ones) for it. I have no idea why they would be slower.
   I might try to do some profiling for the slower cases ๐Ÿค” 
   
   


-- 
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] Introduce `binary_as_string` parquet option [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#discussion_r1807808549


##########
datafusion/core/src/datasource/file_format/mod.rs:
##########
@@ -235,20 +235,26 @@ pub fn file_type_to_format(
     }
 }
 
+/// Create a new field with the specified data type, copying the other
+/// properties from the input field
+fn field_with_new_type(field: &FieldRef, new_type: DataType) -> FieldRef {
+    Arc::new(field.as_ref().clone().with_data_type(new_type))
+}
+
 /// Transform a schema to use view types for Utf8 and Binary
+///
+/// See [parquet::ParquetFormat::force_view_types] for details
 pub fn transform_schema_to_view(schema: &Schema) -> Schema {
     let transformed_fields: Vec<Arc<Field>> = schema
         .fields
         .iter()
         .map(|field| match field.data_type() {
-            DataType::Utf8 | DataType::LargeUtf8 => Arc::new(
-                Field::new(field.name(), DataType::Utf8View, field.is_nullable())
-                    .with_metadata(field.metadata().to_owned()),
-            ),
-            DataType::Binary | DataType::LargeBinary => Arc::new(
-                Field::new(field.name(), DataType::BinaryView, field.is_nullable())
-                    .with_metadata(field.metadata().to_owned()),
-            ),
+            DataType::Utf8 | DataType::LargeUtf8 => {

Review Comment:
   I plan to try and accelerate this PR along with a new arrow-rs release: https://github.com/apache/arrow-rs/issues/6341



-- 
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] Introduce `binary_as_string` parquet option, upgrade to arrow/parquet `53.2.0` [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#discussion_r1815642566


##########
benchmarks/src/clickbench.rs:
##########
@@ -115,12 +115,15 @@ impl RunOpt {
             None => queries.min_query_id()..=queries.max_query_id(),
         };
 
+        // configure parquet options
         let mut config = self.common.config();
-        config
-            .options_mut()
-            .execution
-            .parquet
-            .schema_force_view_types = self.common.force_view_types;
+        {
+            let parquet_options = &mut config.options_mut().execution.parquet;
+            parquet_options.schema_force_view_types = self.common.force_view_types;
+            // The hits_partitioned dataset specifies string columns
+            // as binary due to how it was written. Force it to strings
+            parquet_options.binary_as_string = true;

Review Comment:
   Added a note in https://github.com/apache/datafusion/issues/13099



-- 
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] Introduce `binary_as_string` parquet option, upgrade to arrow/parquet `53.2.0` [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #12816:
URL: https://github.com/apache/datafusion/pull/12816#discussion_r1816609950


##########
datafusion/sqllogictest/test_files/parquet.slt:
##########
@@ -348,3 +348,204 @@ DROP TABLE list_columns;
 # Clean up
 statement ok
 DROP TABLE listing_table;
+
+### Tests for binary_ar_string
+
+# This scenario models the case where a column has been stored in parquet
+# "binary" column (without a String logical type annotation)
+# this is the case with the `hits_partitioned` ClickBench datasets
+# see https://github.com/apache/datafusion/issues/12788
+
+## Create a table with a binary column
+
+query I
+COPY (
+  SELECT
+    arrow_cast(string_col, 'Binary')      as binary_col,
+    arrow_cast(string_col, 'LargeBinary') as largebinary_col,
+    arrow_cast(string_col, 'BinaryView')  as binaryview_col
+  FROM src_table
+  )
+TO 'test_files/scratch/parquet/binary_as_string.parquet'
+STORED AS PARQUET;
+----
+9
+
+# Test 1: Read table with default options
+statement ok
+CREATE EXTERNAL TABLE binary_as_string_default
+STORED AS PARQUET LOCATION 'test_files/scratch/parquet/binary_as_string.parquet'
+
+# NB the data is read and displayed as binary
+query T?T?T?
+select
+  arrow_typeof(binary_col),      binary_col,
+  arrow_typeof(largebinary_col), largebinary_col,
+  arrow_typeof(binaryview_col),  binaryview_col
+ FROM binary_as_string_default;
+----
+Binary 616161 Binary 616161 Binary 616161
+Binary 626262 Binary 626262 Binary 626262
+Binary 636363 Binary 636363 Binary 636363
+Binary 646464 Binary 646464 Binary 646464
+Binary 656565 Binary 656565 Binary 656565
+Binary 666666 Binary 666666 Binary 666666
+Binary 676767 Binary 676767 Binary 676767
+Binary 686868 Binary 686868 Binary 686868
+Binary 696969 Binary 696969 Binary 696969
+
+# Run an explain plan to show the cast happens in the plan (a CAST is needed for the predicates)
+query TT
+EXPLAIN
+  SELECT binary_col, largebinary_col, binaryview_col
+  FROM binary_as_string_default
+ WHERE
+   binary_col LIKE '%a%' AND
+   largebinary_col LIKE '%a%' AND
+   binaryview_col LIKE '%a%';
+----
+logical_plan
+01)Filter: CAST(binary_as_string_default.binary_col AS Utf8) LIKE Utf8("%a%") AND CAST(binary_as_string_default.largebinary_col AS Utf8) LIKE Utf8("%a%") AND CAST(binary_as_string_default.binaryview_col AS Utf8) LIKE Utf8("%a%")
+02)--TableScan: binary_as_string_default projection=[binary_col, largebinary_col, binaryview_col], partial_filters=[CAST(binary_as_string_default.binary_col AS Utf8) LIKE Utf8("%a%"), CAST(binary_as_string_default.largebinary_col AS Utf8) LIKE Utf8("%a%"), CAST(binary_as_string_default.binaryview_col AS Utf8) LIKE Utf8("%a%")]
+physical_plan
+01)CoalesceBatchesExec: target_batch_size=8192
+02)--FilterExec: CAST(binary_col@0 AS Utf8) LIKE %a% AND CAST(largebinary_col@1 AS Utf8) LIKE %a% AND CAST(binaryview_col@2 AS Utf8) LIKE %a%
+03)----RepartitionExec: partitioning=RoundRobinBatch(2), input_partitions=1
+04)------ParquetExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet/binary_as_string.parquet]]}, projection=[binary_col, largebinary_col, binaryview_col], predicate=CAST(binary_col@0 AS Utf8) LIKE %a% AND CAST(largebinary_col@1 AS Utf8) LIKE %a% AND CAST(binaryview_col@2 AS Utf8) LIKE %a%
+
+
+statement ok
+DROP TABLE binary_as_string_default;
+
+## Test 2: Read table using the binary_as_string option
+
+statement ok
+CREATE EXTERNAL TABLE binary_as_string_option
+STORED AS PARQUET LOCATION 'test_files/scratch/parquet/binary_as_string.parquet'
+OPTIONS ('binary_as_string' 'true');
+
+# NB the data is read and displayed as string
+query TTTTTT
+select
+  arrow_typeof(binary_col),      binary_col,
+  arrow_typeof(largebinary_col), largebinary_col,
+  arrow_typeof(binaryview_col),  binaryview_col
+ FROM binary_as_string_option;
+----
+Utf8 aaa Utf8 aaa Utf8 aaa
+Utf8 bbb Utf8 bbb Utf8 bbb
+Utf8 ccc Utf8 ccc Utf8 ccc
+Utf8 ddd Utf8 ddd Utf8 ddd
+Utf8 eee Utf8 eee Utf8 eee
+Utf8 fff Utf8 fff Utf8 fff
+Utf8 ggg Utf8 ggg Utf8 ggg
+Utf8 hhh Utf8 hhh Utf8 hhh
+Utf8 iii Utf8 iii Utf8 iii
+
+# Run an explain plan to show the cast happens in the plan (there should be no casts)
+query TT
+EXPLAIN
+  SELECT binary_col, largebinary_col, binaryview_col
+  FROM binary_as_string_option
+ WHERE
+   binary_col LIKE '%a%' AND
+   largebinary_col LIKE '%a%' AND
+   binaryview_col LIKE '%a%';
+----
+logical_plan
+01)Filter: binary_as_string_option.binary_col LIKE Utf8("%a%") AND binary_as_string_option.largebinary_col LIKE Utf8("%a%") AND binary_as_string_option.binaryview_col LIKE Utf8("%a%")
+02)--TableScan: binary_as_string_option projection=[binary_col, largebinary_col, binaryview_col], partial_filters=[binary_as_string_option.binary_col LIKE Utf8("%a%"), binary_as_string_option.largebinary_col LIKE Utf8("%a%"), binary_as_string_option.binaryview_col LIKE Utf8("%a%")]
+physical_plan
+01)CoalesceBatchesExec: target_batch_size=8192
+02)--FilterExec: binary_col@0 LIKE %a% AND largebinary_col@1 LIKE %a% AND binaryview_col@2 LIKE %a%
+03)----RepartitionExec: partitioning=RoundRobinBatch(2), input_partitions=1
+04)------ParquetExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet/binary_as_string.parquet]]}, projection=[binary_col, largebinary_col, binaryview_col], predicate=binary_col@0 LIKE %a% AND largebinary_col@1 LIKE %a% AND binaryview_col@2 LIKE %a%
+
+
+statement ok
+DROP TABLE binary_as_string_option;
+
+## Test 3: Read table with binary_as_string option AND schema_force_view_types
+
+statement ok
+CREATE EXTERNAL TABLE binary_as_string_both
+STORED AS PARQUET LOCATION 'test_files/scratch/parquet/binary_as_string.parquet'
+OPTIONS (
+  'binary_as_string' 'true',
+  'schema_force_view_types' 'true'
+);
+
+# NB the data is read and displayed a StringView
+query error DataFusion error: SQL error: ParserError\("Expected: an SQL statement, found: Utf8View"\)
+select
+  arrow_typeof(binary_col),      binary_col,
+  arrow_typeof(largebinary_col), largebinary_col,
+  arrow_typeof(binaryview_col),  binaryview_col
+ FROM binary_as_string_both;
+ ----
+Utf8View aaa
+Utf8View bbb
+Utf8View ccc
+Utf8View ddd

Review Comment:
   This is an excellent find @goldmedal  -- thank you
   
   I debugged it and the issue was there was an extra space before `----` ๐Ÿคฆ 
   
   ```sql
    ----
   ``` 
   
   vs
   
   ```sql
   ----
   ```
   
   Fixed in a48dce18000900bb080ba1fec3e8e26bc73d38a7



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