[jira] [Commented] (DRILL-6035) Specify Drill's JSON behavior

["Paul Rogers (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-6035?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16293265#comment-16293265 ] 

Paul Rogers commented on DRILL-6035:
------------------------------------

Drill uses simple rules to infer scalar data types from JSON input:

* If the value is quoted, it is a Nullable {{VARCHAR}}. Example: {{a: "varchar"}}
* If the value is numeric, and has no decimal point, it is a Nullable {{BIGINT}}. Example: {{a: 10}}
* If the value is numeric, and has a decimal point, it is a Nullable {{FLOAT8}}. Eample: {{a: 10.5}}
* If the value is {{true}} or {{false}}, it is a Nullable {{BIT}}, stored as Nullable {{UINT1}}, with {{true}} stored as 1, {{false}} as 0. Example: {{a: true}}

A special case occurs if the user enables {{store.json.all_text_mode}}. In this case, all cases above give rise to a nullable {{VARCHAR}} column type. 

The rules for {{null}} are a bit more complex. The rules below apply to the Drill 1.13 JSON reader, earlier versions require investigation.

* If the {{null}} occurs after seeing one of the above types, then the value is a {{NULL}} of that type (such as a {{NULL}} {{VARCHAR}}.)
* If the {{null}} occurs at the start of the file, Drill defers identifying the data type until it sees the first non-null value.
* If the entire file (or first batch of a file: 4K records before Drill 1.13, variable number in Drill 1.13 or later) contains only {{null}} values, the column is defined as nullable {{VARCHAR}}. (Drill 1.12 and earlier likely has different behavior.)

For example, in the following example, Drill 1.13 identifies the type as nullable {{BIGINT}}:

{code}
{a: null} {a: null} {a: null} {a: 10}
{code}

In the following example, Drill must guess that the data type is nullable {{VARCHAR}}:

{code}
{a: null} {a: null} {a: null}
{code}

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