[
https://issues.apache.org/jira/browse/DRILL-6035?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16305689#comment-16305689
]
Paul Rogers edited comment on DRILL-6035 at 12/28/17 7:42 PM:
--------------------------------------------------------------
h4. JSON Translation in Drill
The goal of the JSON reader is to load JSON data into Drill vectors. The
challenge, as shown above, is that the two data models are different. The
problems are two-fold:
* There exist many perfectly valid ways to translate relational data into JSON.
* There exist no universal ways to translate arbitrary JSON into a relational
model.
That is, JSON can represent any relational table, and do so in a variety of
ways. There is a one (table) to many (JSON formats) relationship. Further,
there are many JSON structures that do not correspond to tables.
h4. Lack of JSON Support in JDBC and ODBC
The issue is further complicated by the fact that ODBC (Tableau) and JDBC are
Drill’s primary client interfaces. These formats do not readily support
non-tabular data. Thus, not only just Drill successfully consume JSON DAGs,
Drill must then convert these structures into simple tables to be consumed by
BI tools. (That is, Drill is not a document-oriented query engine, it is a
classic tabular query engine.)
h4. Many-to-Many Mappings from JSON to Tables
The challenge of the JSON reader is to convert arbitrary JSON into a relational
model, and to do so with no (or very little) information beyond a list of
projected columns and the JSON file itself.
As we will see, the problem is fundamentally not solvable: Drill has too little
information to correctly map from the many possible (and often conflicting)
JSON formats into the proper relational format.
Consider a trivial example. The following is a perfectly legal representation
of a Customer in JSON:
{code}
[101, “Fred”, “Bedrock”, 123.45, “10-11-12”]
{code}
Applications sometimes use the above format to conserve space. It works because
the writer and reader agree on the meaning of each array entry.
How is Drill to interpret the above. More to the point, how is Drill to
interpret the above *without a schema*? Said another way, the above format
works because the writer and reader agree on a format, but Drill is designed to
work without that information. Clearly, without a schema, it is impossible to
understand that the above is a terse representation of a row.
Without a schema, all Drill knows is that the above is a heterogeneous array.
How is Drill to know that this array has a one-to-one correspondence to columns
in a customer record vs. say, an arbitrary array? It can’t. All Drill can do is
ask the user to enable “all-text mode”, read the array as text, then use SQL to
project the array entries correctly:
{code}
SELECT CAST(cust[0] AS INTEGER) AS cust_id,
cust[1] AS cust_name, cust[2] AS city,
CAST(cust[3] AS FLOAT8) AS balance,
TO_DATE(cust[4], ‘yy-mm-dd’) as start_date FROM …
{code}
Although this example is contrived, “JSON in the wild” has a wide variety of
formats since JSON is a universal format and places no constraints on an
application’s data model.
h4. Schema Inference
Translation of JSON to the relational model is simple when the JSON is designed
to exactly fit what Drill expects.
Since JSON is Drill’s reference model, Drill attempts to translate all valid
JSON into the relational model. This is impossible in the general case. Here
are just a few of the complexities:
* A run of null values without a non-null value.
* Different data types for an attribute name across objects. (That is, JSON
does not enforce a schema.)
* Different data types (as parsed by Drill) for array elements. (Such as the
customer row example above.)
* Nulls inside arrays.
* “Sophisticated” data models such as those described earlier
Drill implements a variety of special rules to handle some of the above in some
special cases. These rules were presented in earlier sections. To summarize a
few:
* All-text mode can overcome different primitive types in an object attribute
or list (at the cost of extensive casts in the SQL expression.) But, all-text
mode cannot overcome a change from primitive type to object or list. All-text
mode applies to all queries within a session, not just to the one column with a
conflict.
* Using a projection list to avoid projecting a column with conflicting types
(but, then the value is unavailable to Drill queries.) A projection list,
however, changes the data structure, moving columns nested inside maps to the
top level, requiring changes elsewhere in the query to adjust.
* “Null-deferral” delays picking a type for a null column until a value is
seen, but can’t see across a batch boundary.
* “All numbers as Float” handles the case of integer numbers followed by
numbers with a decimal point, but is a session option so applies to all queries
within a session, not just the one column with the conflict.
h4. Distribution Considerations
The issues are further compounded because Drill is a distributed query engine.
Rules and inferences applied by one JSON reader area unknown to other readers
(in other fragments) working on different JSON files for the same query. In the
simplest case, file A has column `x` which is clearly a FLOAT8. File B, created
earlier, has no column `x`, and so the reader guesses nullable INT (Drill 1.12
or before) or nullable VARCHAR (Drill 1.13.) The result is conflict elsewhere
in the query DAG.
h4. Open Schema Inference Issues
The general conclusion is that Drill suffers from three intrinsic limitations:
* Drill cannot predict the future (can’t see a billion rows ahead or predict
the contents of files not yet read).
* Drill cannot reverse engineer intent from JSON structure.
* Drill cannot share data across readers (reader of file A cannot coordinate
with the reader of file B to agree on a schema.)
The above are not bugs; they are intrinsic characteristic of a distributed
schema-free query system.
Drill must infer schema on the first record (or, for nulls, in the first
batch.) Information that arrives later (or in another file) is of no help in
inferring schema. (That is, Drill cannot see into the future.)
There are many ways to encode the same data into JSON. Each JSON encoding could
represent many data formats. Drill has a preference, but that preference cannot
magically calls all JSON file creators to adopt Drill’s preferred format.
was (Author: paul.rogers):
h4. JSON Translation in Drill
The goal of the JSON reader is to load JSON data into Drill vectors. The
challenge, as shown above, is that the two data models are different. The
problems are two-fold:
* There exist many perfectly valid ways to translate relational data into JSON.
* There exist no universal ways to translate arbitrary JSON into a relational
model.
That is, JSON can represent any relational table, and do so in a variety of
ways. There is a one (table) to many (JSON formats) relationship. Further,
there are many JSON structures that do not correspond to tables.
h4. Lack of JSON Support in JDBC and ODBC
The issue is further complicated by the fact that ODBC (Tableau) and JDBC are
Drill’s primary client interfaces. These formats do not readily support
non-tabular data. Thus, not only just Drill successfully consume JSON DAGs,
Drill must then convert these structures into simple tables to be consumed by
BI tools. (That is, Drill is not a document-oriented query engine, it is a
classic tabular query engine.)
h4. Many-to-Many Mappings from JSON to Tables
The challenge of the JSON reader is to convert arbitrary JSON into a relational
model, and to do so with no (or very little) information beyond a list of
projected columns and the JSON file itself.
As we will see, the problem is fundamentally not solvable: Drill has too little
information to correctly map from the many possible (and often conflicting)
JSON formats into the proper relational format.
Consider a trivial example. The following is a perfectly legal representation
of a Customer in JSON:
{code}
[101, “Fred”, “Bedrock”, 123.45, “10-11-12”]
{code}
Applications sometimes use the above format to conserve space. It works because
the writer and reader agree on the meaning of each array entry.
How is Drill to interpret the above. More to the point, how is Drill to
interpret the above *without a schema*? Said another way, the above format
works because the writer and reader agree on a format, but Drill is designed to
work without that information. Clearly, without a schema, it is impossible to
understand that the above is a terse representation of a row.
Without a schema, all Drill knows is that the above is a heterogeneous array.
How is Drill to know that this array has a one-to-one correspondence to columns
in a customer record vs. say, an arbitrary array? It can’t. All Drill can do is
ask the user to enable “all-text mode”, read the array as text, then use SQL to
project the array entries correctly:
{code}
SELECT CAST(cust[0] AS INTEGER) AS cust_id,
cust[1] AS cust_name, cust[2] AS city,
CAST(cust[3] AS FLOAT8) AS balance,
TO_DATE(cust[4], ‘yy-mm-dd’) as start_date FROM …
{code}
Although this example is contrived, “JSON in the wild” has a wide variety of
formats since JSON is a universal format and places no constraints on an
application’s data model.
> Specify Drill's JSON behavior
> -----------------------------
>
> Key: DRILL-6035
> URL: https://issues.apache.org/jira/browse/DRILL-6035
> Project: Apache Drill
> Issue Type: Improvement
> Affects Versions: 1.13.0
> Reporter: Paul Rogers
> Assignee: Pritesh Maker
>
> Drill supports JSON as its native data format. However, experience suggests
> that Drill may have limitations in the JSON that Drill supports. This ticket
> asks to clarify Drill's expected behavior on various kinds of JSON.
> Topics to be addressed:
> * Relational vs. non-relational structures
> * JSON structures used in practice and how they map to Drill
> * Support for varying data types
> * Support for missing values, especially across files
> These topics are complex, hence the request to provide a detailed
> specifications that clarifies what Drill does and does not support (or what
> is should and should not support.)
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
