Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id E44F0200B14 for ; Sat, 4 Jun 2016 02:53:02 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id E3173160A50; Sat, 4 Jun 2016 00:53:02 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 12CAE160A49 for ; Sat, 4 Jun 2016 02:53:01 +0200 (CEST) Received: (qmail 25367 invoked by uid 500); 4 Jun 2016 00:52:59 -0000 Mailing-List: contact issues-help@drill.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@drill.apache.org Delivered-To: mailing list issues@drill.apache.org Received: (qmail 25354 invoked by uid 99); 4 Jun 2016 00:52:59 -0000 Received: from arcas.apache.org (HELO arcas) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 04 Jun 2016 00:52:59 +0000 Received: from arcas.apache.org (localhost [127.0.0.1]) by arcas (Postfix) with ESMTP id 40A4E2C1F69 for ; Sat, 4 Jun 2016 00:52:59 +0000 (UTC) Date: Sat, 4 Jun 2016 00:52:59 +0000 (UTC) From: "Parth Chandra (JIRA)" To: issues@drill.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Updated] (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 X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 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:all-tabpanel ] Parth Chandra updated DRILL-4694: --------------------------------- Assignee: Aman Sinha > 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 > Assignee: 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)