andygrove opened a new issue, #294:
URL: https://github.com/apache/datafusion-spark/issues/294

   Part of #272 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `schema_of_csv(csv[, options])` parses one CSV record and returns the 
inferred schema as a DDL string such as
   `STRUCT<_c0: INT, _c1: STRING>`. Fields are named `_c0`, `_c1`, ... by 
position. `csv` must be foldable
   (`DATATYPE_MISMATCH.NON_FOLDABLE_INPUT`) and not NULL 
(`DATATYPE_MISMATCH.UNEXPECTED_NULL`). From 4.1, a non-string
   argument fails with `DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE`. `options` is 
a `map(...)` of string literals, read
   with the same `CSVOptions` as `from_csv` (`sep`/`delimiter`, `quote`, 
`escape`, `nullValue`, `nanValue`,
   `positiveInf`, `negativeInf`, `dateFormat`, `timestampFormat`, 
`timestampNTZFormat`, `preferDate`, `locale`,
   `ignoreLeadingWhiteSpace`, `ignoreTrailingWhiteSpace`, ...). The time zone 
is always `UTC`. `lineSep` is forced to
   `\uFFFF`, so a newline is part of the field.
   
   Returns a non-nullable `STRING`. Since 3.0.0. Because the input is foldable, 
Spark always constant-folds it
   (in 4.x it is `RuntimeReplaceable` over an `Invoke` on a literal). Its main 
use is as the schema argument of
   `from_csv`, so DataFusion can evaluate it at planning time (for example in 
`simplify`).
   
   ### Examples
   
   ```sql
   SELECT schema_of_csv('1,abc');
   -- STRUCT<_c0: INT, _c1: STRING>
   SELECT schema_of_csv('1|abc', map('delimiter', '|'));
   -- STRUCT<_c0: INT, _c1: STRING>
   SELECT schema_of_csv('0.1,1');
   -- STRUCT<_c0: DOUBLE, _c1: INT>
   SELECT schema_of_csv('0.1 1', map('sep', ' '));
   -- STRUCT<_c0: DOUBLE, _c1: INT>
   SELECT schema_of_csv('14:30:45');
   -- STRUCT<_c0: TIMESTAMP>
   SELECT schema_of_csv('14:30:45.123456');
   -- STRUCT<_c0: TIMESTAMP>
   SELECT schema_of_csv(null);
   -- error: DATATYPE_MISMATCH.UNEXPECTED_NULL
   SELECT schema_of_csv(42);
   -- error: DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE (4.1+)
   SELECT schema_of_csv(csvField) FROM csvTable;
   -- error: DATATYPE_MISMATCH.NON_FOLDABLE_INPUT
   ```
   
   These come from the `ExpressionDescription`, `csv-functions.sql.out` and 
`CsvFunctionsSuite`. Others, by reading
   `CSVInferSchema`: `'1,,abc'` gives `_c1: STRING`; `'12345678901234567890'` 
gives `DECIMAL(20,0)`;
   `'2015-08-26'` gives `DATE`; `'TRUE'` gives `BOOLEAN`; `'NaN'` and `'Inf'` 
give `DOUBLE`.
   
   ### Edge cases to test
   
   Inference (`CSVInferSchema.inferField`, starting from `NullType` for each 
field, single row so no merging):
   
   - Empty field or a field equal to `nullValue` (default `""`) stays 
`NullType`, which becomes `STRING`.
   - The type ladder is tried in this order, each falling through to the next 
on failure:
     `INT` (`Integer.parseInt`), `BIGINT` (`Long.parseLong`), `DECIMAL(p, s)` 
only when the `BigDecimal` has
     scale <= 0 (so integers too big for BIGINT; precision > 38 or a negative 
scale such as `1e5` throws and falls
     through), `DOUBLE` (`Double.parseDouble`, or the `nanValue` / 
`positiveInf` / `negativeInf` strings), `DATE`
     (only when `preferDate`, default true; strict parse with `dateFormat`, 
default `yyyy-MM-dd`), `TIMESTAMP_NTZ` (only
     when `spark.sql.timestampType=TIMESTAMP_NTZ`), `TIMESTAMP` (with 
`timestampFormat`, or the lenient default parser
     that also accepts time-only strings, see above), `BOOLEAN` 
(case-insensitive `true`/`false`), else `STRING`.
   - `1.5` has scale 1, so it skips DECIMAL and becomes `DOUBLE`. `TIME` is 
never inferred, even in 4.2.
   - Whitespace: `' 0.8'` fails INT, BIGINT and BigDecimal but 
`Double.parseDouble` trims, so it is `DOUBLE`.
     `' 1'` is `DOUBLE` too, not `INT` (by code reading). With 
`ignoreLeadingWhiteSpace` it becomes `INT`.
   - Java `Double.parseDouble` also accepts `1d`, `1f`, `Infinity` and hex 
floats, all giving `DOUBLE`.
   - With a non-US `locale` the decimal step uses `DecimalFormat` for that 
locale (grouping and decimal separators).
     The US default uses plain `new BigDecimal(s)` (unlike `from_csv`, commas 
are not stripped).
   - `spark.sql.legacy.timeParserPolicy=LEGACY` turns `preferDate` off and sets 
the default timestamp pattern to
     `yyyy-MM-dd'T'HH:mm:ss.SSSXXX`, which changes date and timestamp inference.
   - A quoted field with the delimiter inside (`'"a,b",1'`) is one field. The 
number of fields equals the number of
     tokens univocity returns, including a trailing empty one (`'1,'` gives two 
fields).
   - Output formatting is `StructType.sql`: `STRUCT<name: TYPE, ...>` with `: ` 
and `, ` separators, upper-case types,
     and field names quoted with backticks only when needed.
   - Bad option values give the same errors as `from_csv` 
(`OPTION_VALUE_EXCEEDS_ONE_CHARACTER`,
     `INVALID_DELIMITER_VALUE.*`, `INVALID_OPTIONS.NON_MAP_FUNCTION`, 
`INVALID_OPTIONS.NON_STRING_TYPE`).
   
   ### Differences across Spark versions
   
   - 3.4 / 3.5: `CodegenFallback` expression. No explicit string type check on 
the argument.
   - 4.0: becomes `RuntimeReplaceable` (`SchemaOfCsvEvaluator`). 
`spark.sql.legacy.timeParserPolicy` default moves
     from `EXCEPTION` to `CORRECTED`.
   - 4.1: adds the `UNEXPECTED_INPUT_TYPE` check (`child.dataType != 
StringType`).
   - 4.2: `csv-functions.sql.out` adds the time-only cases, which infer 
`TIMESTAMP`.
   
   ### Implementation notes
   
   - Spark: 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/csvExpressions.scala`
     (`SchemaOfCsv`), `expressions/csv/CsvExpressionEvalUtils.scala` 
(`SchemaOfCsvEvaluator`),
     `catalyst/csv/CSVInferSchema.scala` (`inferRowType`, `inferField`, 
`toStructFields`), `catalyst/csv/CSVOptions.scala`.
   - DataFusion core has no equivalent. Arrow's CSV schema inference 
(`arrow-csv` `infer_schema`) uses different
     rules (regex based, Int64/Float64/Boolean/Date/Timestamp) and can't be 
reused.
   - Share the CSV tokenizer and `CsvOptions` with `from_csv` and `to_csv` 
(apache/datafusion-spark#138; Comet's
     native `to_csv` in `native/spark-expr/src/csv_funcs/` already has write 
options) in
     `crates/spark/src/function/csv/`. `schema_of_json` should share the DDL 
type-string formatting if it exists.
   - Tests: add `crates/sqllogictest/test_files/csv/schema_of_csv.slt`; 
document with `#[user_doc]`.
   
   ---
   _This issue was generated by an LLM (Claude) from the Apache Spark source 
code and tests. Examples come from Spark's `ExpressionDescription` examples, 
test suites and `.sql.out` golden files, but please verify them against Spark 
before relying on them._


-- 
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: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to