[
https://issues.apache.org/jira/browse/SPARK-60002?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ivelin Tchangalov updated SPARK-60002:
--------------------------------------
Description:
Spark's SQL parser accepts the
{code:java}
NULL DEFINED AS {code}
clause of
{code:java}
CREATE TABLE ... ROW FORMAT DELIMITED{code}
but it is never written to the table's SerDe properties.
Therefore, the same DDL over the same files returns different results in
Hive and in Spark.
This translates to data issues because data isn't read correctly.
h3. Root cause
{{AstBuilder.visitRowFormatDelimited}} builds the SerDe property map from the
delimited
clauses but never reads {{{}ctx.nullDefinedAs{}}}. The method carries the
acknowledgement in a
comment: {{// TODO we need proper support for the NULL format.}}
{{{}field.delim{}}}, {{{}escape.delim{}}}, {{{}colelction.delim{}}},
{{mapkey.delim}} and
{{line.delim}} are all stored correctly. The gap is exactly one property,
{{{}serialization.null.format{}}}, which appears nowhere in Spark's main source:
{code:java}
grep -rn "serialization\.null\.format" --include=*.scala --include=*.java . |
grep -v /test/
# (no output)
{code}
The grammar already captures the value: {{SqlBaseParser.g4}} binds it to the
labelled token {{{}nullDefinedAs{}}}. Its only consumer in the codebase is
{{{}getRowFormatDelimited{}}}, the SCRIPT TRANSFORM path, which maps it to
{{{}TOK_TABLEROWFORMATNULL{}}}. The table-DDL visitor ignores it.
h3. Reproduction
PySpark with Hive support enabled; {{DESCRIBE FORMATTED}} reports {{{}Provider:
hive{}}}, so
this is a genuine Hive SerDe table and not a converted datasource table.
{code:sql}
CREATE EXTERNAL TABLE t (id INT, region STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
NULL DEFINED AS 'NA'
STORED AS TEXTFILE LOCATION '...';
{code}
Data:
{noformat}
1|east
2|NA
3|west
{noformat}
{{DESCRIBE FORMATTED }}reports
{noformat}
Storage Properties [serialization.format=|, field.delim=|]
{noformat}
There is no {{{}serialization.null.format{}}}, and the row with id 2 reads
{{region}} as the
string {{'NA'}} rather than NULL. Hive returns NULL for the same table and data.
Every sentinel value is affected: {{{}''{}}}, {{{}\N{}}}, {{{}NA{}}},
{{{}X{}}}, {{\t}} and {{\001}} all behave identically. {{\N}} appears to work
only because it is the engine default.
h3. How the bug was introduced
It was never implemented.
h3. Related issues
https://issues.apache.org/jira/browse/SPARK-14583 reported the read-side
variant of this parity gap: a sentinel supplied through {{TBLPROPERTIES}} on a
partitioned table that Spark fails to apply on read. It was bulk-closed as
Incomplete in 2019. That is a different vector into the same Hive parity gap
and is not addressed by this ticket.
was:
Spark's SQL parser accepts the
{code:java}
NULL DEFINED AS {code}
clause of
{code:java}
CREATE TABLE ... ROW FORMAT DELIMITED{code}
but it is never never written to the table's SerDe properties
Hive honours the clause, so the same DDL over the same files returns different
results in
Hive and in Spark.
Every delimited-text table whose sentinel is not Hive's default {{\N}}
reads that sentinel as literal data instead of NULL. Because the clause is
accepted
silently, there is no signal at table-creation time. That translates to a data
issues because data isn't read correctly.
h3. Root cause
{{AstBuilder.visitRowFormatDelimited}} builds the SerDe property map from the
delimited
clauses but never reads {{{}ctx.nullDefinedAs{}}}. The method carries the
acknowledgement in a
comment: {{// TODO we need proper support for the NULL format.}}
{{{}field.delim{}}}, {{{}escape.delim{}}}, {{{}colelction.delim{}}},
{{mapkey.delim}} and
{{line.delim}} are all stored correctly. The gap is exactly one property,
{{{}serialization.null.format{}}}, which appears nowhere in Spark's main source:
{code:java}
grep -rn "serialization\.null\.format" --include=*.scala --include=*.java . |
grep -v /test/
# (no output)
{code}
The grammar already captures the value: {{SqlBaseParser.g4}} binds it to the
labelled token {{{}nullDefinedAs{}}}. Its only consumer in the codebase is
{{{}getRowFormatDelimited{}}}, the SCRIPT TRANSFORM path, which maps it to
{{{}TOK_TABLEROWFORMATNULL{}}}. The table-DDL visitor ignores it.
h3. Reproduction
PySpark with Hive support enabled; {{DESCRIBE FORMATTED}} reports {{{}Provider:
hive{}}}, so
this is a genuine Hive SerDe table and not a converted datasource table.
{code:sql}
CREATE EXTERNAL TABLE t (id INT, region STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
NULL DEFINED AS 'NA'
STORED AS TEXTFILE LOCATION '...';
{code}
Data:
{noformat}
1|east
2|NA
3|west
{noformat}
{{DESCRIBE FORMATTED t}} reports
{noformat}
Storage Properties [serialization.format=|, field.delim=|]
{noformat}
There is no {{{}serialization.null.format{}}}, and the row with id 2 reads
{{region}} as the
string {{'NA'}} rather than NULL. Hive returns NULL for the same table and data.
Every sentinel value is affected: {{{}''{}}}, {{{}\N{}}}, {{{}NA{}}},
{{{}X{}}}, {{\t}} and {{\001}} all
behave identically. {{\N}} appears to work only because it is the engine
default.
h3. How the bug was introduced
It was never implemented
h3. Related issues
SPARK-14583 reported the read-side variant of this parity gap: a sentinel
supplied through {{TBLPROPERTIES}} on a partitioned table that Spark fails to
apply on read. It was
bulk-closed as Incomplete in 2019. That is a different vector into the same
Hive parity gap and is not addressed by this ticket.
> CREATE TABLE ... ROW FORMAT DELIMITED silently discards NULL DEFINED AS
> -----------------------------------------------------------------------
>
> Key: SPARK-60002
> URL: https://issues.apache.org/jira/browse/SPARK-60002
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 3.5.6, 4.0.0
> Reporter: Ivelin Tchangalov
> Priority: Major
>
> Spark's SQL parser accepts the
> {code:java}
> NULL DEFINED AS {code}
> clause of
> {code:java}
> CREATE TABLE ... ROW FORMAT DELIMITED{code}
> but it is never written to the table's SerDe properties.
> Therefore, the same DDL over the same files returns different results in
> Hive and in Spark.
> This translates to data issues because data isn't read correctly.
> h3. Root cause
> {{AstBuilder.visitRowFormatDelimited}} builds the SerDe property map from the
> delimited
> clauses but never reads {{{}ctx.nullDefinedAs{}}}. The method carries the
> acknowledgement in a
> comment: {{// TODO we need proper support for the NULL format.}}
> {{{}field.delim{}}}, {{{}escape.delim{}}}, {{{}colelction.delim{}}},
> {{mapkey.delim}} and
> {{line.delim}} are all stored correctly. The gap is exactly one property,
> {{{}serialization.null.format{}}}, which appears nowhere in Spark's main
> source:
> {code:java}
> grep -rn "serialization\.null\.format" --include=*.scala --include=*.java .
> | grep -v /test/
> # (no output)
> {code}
> The grammar already captures the value: {{SqlBaseParser.g4}} binds it to the
> labelled token {{{}nullDefinedAs{}}}. Its only consumer in the codebase is
> {{{}getRowFormatDelimited{}}}, the SCRIPT TRANSFORM path, which maps it to
> {{{}TOK_TABLEROWFORMATNULL{}}}. The table-DDL visitor ignores it.
> h3. Reproduction
> PySpark with Hive support enabled; {{DESCRIBE FORMATTED}} reports
> {{{}Provider: hive{}}}, so
> this is a genuine Hive SerDe table and not a converted datasource table.
> {code:sql}
> CREATE EXTERNAL TABLE t (id INT, region STRING)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
> NULL DEFINED AS 'NA'
> STORED AS TEXTFILE LOCATION '...';
> {code}
> Data:
> {noformat}
> 1|east
> 2|NA
> 3|west
> {noformat}
> {{DESCRIBE FORMATTED }}reports
> {noformat}
> Storage Properties [serialization.format=|, field.delim=|]
> {noformat}
> There is no {{{}serialization.null.format{}}}, and the row with id 2 reads
> {{region}} as the
> string {{'NA'}} rather than NULL. Hive returns NULL for the same table and
> data.
> Every sentinel value is affected: {{{}''{}}}, {{{}\N{}}}, {{{}NA{}}},
> {{{}X{}}}, {{\t}} and {{\001}} all behave identically. {{\N}} appears to work
> only because it is the engine default.
> h3. How the bug was introduced
> It was never implemented.
> h3. Related issues
> https://issues.apache.org/jira/browse/SPARK-14583 reported the read-side
> variant of this parity gap: a sentinel supplied through {{TBLPROPERTIES}} on
> a partitioned table that Spark fails to apply on read. It was bulk-closed as
> Incomplete in 2019. That is a different vector into the same Hive parity gap
> and is not addressed by this ticket.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
