Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: neutral (athena.apache.org: local policy)
MIME-Version: 1.0
Date: Mon, 18 Feb 2013 18:13:30 +0530
Message-ID: 
 <CAN9TFXSWtMrsHCZsL_teHdGZiBj9xTX2RqeO-yg0Qum_ZLYNkA@mail.gmail.com>
Subject: Loading json files into hive table is giving NULL as output(data is
 in s3 bucket)
From: Chunky Gupta <chunky.gupta@vizury.com>
To: user@hive.apache.org
Content-Type: multipart/alternative; boundary=047d7bdc0e7e87708904d5ff1214

--047d7bdc0e7e87708904d5ff1214
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I have data in s3 bucket, which is in json format and is a zip file. I have
added this jar file in hive console :-
http://code.google.com/p/hive-json-serde/downloads/detail?name=hive-json-serde-0.2.jar&can=2&q=

I tried the following steps to create table and load data :-

1. CREATE EXTERNAL TABLE table_test ( uname STRING ) PARTITIONED BY (dt
STRING ) ROW FORMAT SERDE "org.apache.hadoop.hive.contrib.serde2.JsonSerde"
WITH SERDEPROPERTIES ( "uname"="$._u" ) LOCATION
's3://BUCKET_NAME/test_data/'

           I tried this also :-

CREATE EXTERNAL TABLE table_test ( uname STRING ) PARTITIONED BY (dt STRING
) ROW FORMAT SERDE "org.apache.hadoop.hive.contrib.serde2.JsonSerde" WITH
SERDEPROPERTIES ( "uname"="_u" ) LOCATION 's3://BUCKET_NAME/test_data/'


2. alter table table_test add partition (dt='13Feb2012') location
's3n://BUCKET_NAME/test_data/13Feb2012';

and json file is like this :-
-------------------------------------
{"_u":"test_name1","_ts":"2012-01-13","_ip":"IP1"}
{"_u":"test_name2","_ts":"2012-01-13","_ip":"IP2"}
{"_u":"test_name3","_ts":"2012-01-13","_ip":"IP3"}


When I query :-
select uname from table_test;

Output :-
NULL 13Feb2012
NULL 13Feb2012
NULL 13Feb2012


Please help me and let me know how to add json data in a table.

Thanks,
Chunky.

--047d7bdc0e7e87708904d5ff1214
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi,<br><br>I have data in s3 bucket, which is in json format and is a zip f=
ile. I have added this jar file in hive console :-<br><a href=3D"http://cod=
e.google.com/p/hive-json-serde/downloads/detail?name=3Dhive-json-serde-0.2.=
jar&amp;can=3D2&amp;q=3D">http://code.google.com/p/hive-json-serde/download=
s/detail?name=3Dhive-json-serde-0.2.jar&amp;can=3D2&amp;q=3D</a><br>
<br>I tried the following steps to create table and load data :-<br><br>1. =
CREATE EXTERNAL TABLE table_test ( uname STRING ) PARTITIONED BY (dt STRING=
 ) ROW FORMAT SERDE &quot;org.apache.hadoop.hive.contrib.serde2.JsonSerde&q=
uot; WITH SERDEPROPERTIES ( &quot;uname&quot;=3D&quot;$._u&quot; ) LOCATION=
 &#39;s3://BUCKET_NAME/test_data/&#39;<br>
<br>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 I tried this also :-<br><br>CREATE EXTER=
NAL TABLE table_test ( uname STRING ) PARTITIONED BY (dt=20
STRING ) ROW FORMAT SERDE=20
&quot;org.apache.hadoop.hive.contrib.serde2.JsonSerde&quot; WITH SERDEPROPE=
RTIES (
<span style=3D"color:rgb(51,204,0)"> &quot;uname&quot;=3D&quot;_u&quot;</sp=
an> ) LOCATION &#39;s3://BUCKET_NAME/test_data/&#39;<br><br><br><br>2. alte=
r table table_test add partition (dt=3D&#39;13Feb2012&#39;) location &#39;s=
3n://BUCKET_NAME/test_data/13Feb2012&#39;;<br>
<br>and json file is like this :- <br>-------------------------------------=
<br>{&quot;_u&quot;:&quot;test_name1&quot;,&quot;_ts&quot;:&quot;2012-01-13=
&quot;,&quot;_ip&quot;:&quot;IP1&quot;}<br>{&quot;_u&quot;:&quot;test_name2=
&quot;,&quot;_ts&quot;:&quot;2012-01-13&quot;,&quot;_ip&quot;:&quot;IP2&quo=
t;}<br>
{&quot;_u&quot;:&quot;test_name3&quot;,&quot;_ts&quot;:&quot;2012-01-13&quo=
t;,&quot;_ip&quot;:&quot;IP3&quot;}<br><br><br>When I query :-<br>select un=
ame from table_test;<br><br>Output :-<br><span style=3D"color:rgb(255,0,0)"=
>NULL</span> 13Feb2012<br>
<span style=3D"color:rgb(255,0,0)">NULL</span> 13Feb2012<br><span style=3D"=
color:rgb(255,0,0)">NULL</span> 13Feb2012<br><br><br>Please help me and let=
 me know how to add json data in a table.<br><br>Thanks,<br>Chunky.<br>

--047d7bdc0e7e87708904d5ff1214--