drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Paul Rogers (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DRILL-6035) Specify Drill's JSON behavior
Date Thu, 28 Dec 2017 19:43:00 GMT

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

Mime
View raw message