Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Hive select shows null after successful data load


Copy link to this message
-
Re: Hive select shows null after successful data load
Can you run some other queries from job1 table and see if any query returns
some data?

I am guessing your query "select jobs.values.position.title from jobs1;"
may have some issue. May be it should be as

select jobs.values[0].position.title from jobs1;
Regards,
Ramki.
On Wed, Jun 19, 2013 at 8:24 AM, Sunita Arvind <[EMAIL PROTECTED]>wrote:

> Thanks Stephen,
>
> That's just what I tried with the try_parsed table. It is exactly same
> data with lesser nesting in the structure and lesser number of entries.
> Do you mean to say that highly nested jsons can lead to issues? What are
> typical solution to such issues? Write UDFs in hive or parse the JSON into
> a delimited file?
> I have heard of custom serdes also. Not sure if UDFs and custom serdes are
> one and the same.
>
> regards
> Sunita
>
>
> On Wed, Jun 19, 2013 at 10:38 AM, Stephen Sprague <[EMAIL PROTECTED]>wrote:
>
>> I think you might have to start small here instead of going for the home
>> run on the first swing.  when all else fails start with a trivial json
>> object and then build up from there and see what additional step breaks
>> it.   that way you know if the trivial example fails is something
>> fundamental and not the complexity of your json object that's throwing
>> things off.
>>
>>
>> On Wed, Jun 19, 2013 at 4:34 AM, Sunita Arvind <[EMAIL PROTECTED]>wrote:
>>
>>> Thanks for sharing your experience Richa.
>>> I do have timestamps but in the format of year : INT, day : INT, month :
>>> INT.
>>> As per your suggestion, I changed them all to string, but still get null
>>> as the output.
>>>
>>> regards
>>> Sunita
>>>
>>>
>>> On Wed, Jun 19, 2013 at 2:17 AM, Richa Sharma <
>>> [EMAIL PROTECTED]> wrote:
>>>
>>>> Do you have any timestamp fields in the table that might contain null
>>>> value ?
>>>>
>>>> I faced a similar situation sometime back - changing the data type to
>>>> string made it work.
>>>>
>>>> But I was working on delimited text files.
>>>> Not sure if it applies to JSON .. but its still worth giving a try !!
>>>>
>>>> Richa
>>>>
>>>>
>>>>
>>>> On Wed, Jun 19, 2013 at 7:28 AM, Sunita Arvind <[EMAIL PROTECTED]>wrote:
>>>>
>>>>> Having the a column name same as the table name, is a problem due to
>>>>> which I was not able to reference jobs.values.id from jobs. Changing
>>>>> the table name to jobs1 resolved the semantic error.
>>>>> However, the query still returns null
>>>>>
>>>>> hive> select jobs.values.position.title from jobs1;
>>>>> Total MapReduce jobs = 1
>>>>> Launching Job 1 out of 1
>>>>> Number of reduce tasks is set to 0 since there's no reduce operator
>>>>> Starting Job = job_201306080116_0036, Tracking URL >>>>> http://node01.expressanalytics.net:50030/jobdetails.jsp?jobid=job_201306080116_0036
>>>>> Kill Command = /usr/lib/hadoop/bin/hadoop job  -kill
>>>>> job_201306080116_0036
>>>>> Hadoop job information for Stage-1: number of mappers: 1; number of
>>>>> reducers: 0
>>>>> 2013-06-18 18:55:52,381 Stage-1 map = 0%,  reduce = 0%
>>>>> 2013-06-18 18:55:56,394 Stage-1 map = 100%,  reduce = 0%, Cumulative
>>>>> CPU 0.88 sec
>>>>> 2013-06-18 18:55:57,400 Stage-1 map = 100%,  reduce = 0%, Cumulative
>>>>> CPU 0.88 sec
>>>>> 2013-06-18 18:55:58,407 Stage-1 map = 100%,  reduce = 100%, Cumulative
>>>>> CPU 0.88 sec
>>>>> MapReduce Total cumulative CPU time: 880 msec
>>>>> Ended Job = job_201306080116_0036
>>>>> MapReduce Jobs Launched:
>>>>> Job 0: Map: 1   Cumulative CPU: 0.88 sec   HDFS Read: 35374 HDFS
>>>>> Write: 3 SUCCESS
>>>>> Total MapReduce CPU Time Spent: 880 msec
>>>>> OK
>>>>> null
>>>>> Time taken: 9.591 seconds
>>>>>
>>>>>
>>>>> regards
>>>>> Sunita
>>>>>
>>>>>
>>>>> On Tue, Jun 18, 2013 at 9:35 PM, Sunita Arvind <[EMAIL PROTECTED]>wrote:
>>>>>
>>>>>> Ok.
>>>>>> The data files are quite small. Around 35 KB and 1 KB each.
>>>>>>
>>>>>> [sunita@node01 tables]$  hadoop fs -ls /user/sunita/tables/jobs
>>>>>> Found 1 items
>>>>>> -rw-r--r--   3 sunita hdfs      35172 2013-06-18 18:31
>>>>>> /user/sunita/tables/jobs/jobs_noSite_parsed.json