Mailing-List: contact issues-help@drill.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@drill.apache.org
Date: Sat, 4 Jun 2016 00:52:59 +0000 (UTC)
From: "ASF GitHub Bot (JIRA)" <jira@apache.org>
To: issues@drill.apache.org
Message-ID: <JIRA.12972817.1464202611000.31368.1465001579241@Atlassian.JIRA>
In-Reply-To: <JIRA.12972817.1464202611000@Atlassian.JIRA>
References: <JIRA.12972817.1464202611000@Atlassian.JIRA> <JIRA.12972817.1464202611263@arcas>
Subject: [jira] [Commented] (DRILL-4694) CTAS in JSON format produces
 extraneous NULL fields
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Sat, 04 Jun 2016 00:53:03 -0000


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

ASF GitHub Bot commented on DRILL-4694:
---------------------------------------

GitHub user parthchandra opened a pull request:

    https://github.com/apache/drill/pull/514

    DRILL-4694: CTAS in JSON format produces extraneous NULL fields

       Changed behavior of JSON CTAS to skip fields if the value is null. Added an option "store.json.writer.skip_null_fields" to enable old behavior.

You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/parthchandra/drill DRILL-4694

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/drill/pull/514.patch

To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:

    This closes #514
    
----
commit 68ff4950f8302725aadf72a50074f6eef735738b
Author: Parth Chandra <parthc@apache.org>
Date:   2016-06-02T00:19:03Z

    DRILL-4694: CTAS in JSON format produces extraneous NULL fields
       Changed behavior of JSON CTAS to skip fields if the value is null. Added an option "store.json.writer.skip_null_fields" to enable old behavior.

----


> CTAS in JSON format produces extraneous NULL fields
> ---------------------------------------------------
>
>                 Key: DRILL-4694
>                 URL: https://issues.apache.org/jira/browse/DRILL-4694
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Storage - JSON
>    Affects Versions: 1.6.0
>            Reporter: Aman Sinha
>
> Consider the following JSON file: 
> {noformat}
> // file t2.json
> {
> "X" : {
>   "key1" : "value1",
>   "key2" : "value2"
>   } 
> }
> {
> "X" : {
>   "key3" : "value3",
>   "key4" : "value4"
>   }
> }
> {
> "X" : {
>   "key5" : "value5",
>   "key6" : "value6"
>   }
> }
> {noformat}
> Now create a table in Json format using CTAS: 
> {noformat}
> 0: jdbc:drill:zk=local> alter session set `store.format` = 'json';
> 0: jdbc:drill:zk=local> create table dfs.tmp.jt12 as select t.`X` from `t2.json` t;
> +-----------+----------------------------+
> | Fragment  | Number of records written  |
> +-----------+----------------------------+
> | 0_0       | 3                          |
> +-----------+----------------------------+
> {noformat}
> The output file has rows with union schema of all the fields in all the records.  This creates extraneous Null fields in the output: 
> {noformat}
> $ cat jt12/0_0_0.json 
> {
>   "X" : {
>     "key1" : "value1",
>     "key2" : "value2",
>     "key3" : null,
>     "key4" : null,
>     "key5" : null,
>     "key6" : null
>   }
> } {
>   "X" : {
>     "key1" : null,
>     "key2" : null,
>     "key3" : "value3",
>     "key4" : "value4",
>     "key5" : null,
>     "key6" : null
>   }
> } {
>   "X" : {
>     "key1" : null,
>     "key2" : null,
>     "key3" : null,
>     "key4" : null,
>     "key5" : "value5",
>     "key6" : "value6"
>   }
> }
> {noformat}
> Note that if I change the output format to CSV or Parquet, there are no Null fields created in the output file.   The expectation for a CTAS in json format is that the output should match that of the input json data.  


--
This message was sent by Atlassian JIRA
(v6.3.4#6332)