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

Switch to Threaded View
Hive, mail # user - Load Timestamp data type fom local file


Copy link to this message
-
Re: Load Timestamp data type fom local file
Nitin Pawar 2013-09-30, 11:09
Hi Claudio,

When you do a select * from table there is no mapreduce in place.
What hive does is it uses the hdfs api and reads your files and displays
the data by a tab separated columns list.
If the data is wrongly populated, hive will show the entire set into first
column and rest of the columns are shown as NULL
When you are seeing no data, i suspect the data file is deleted somehow
from your table.

I would recommend you to do following things
1) Create table with a location flag
2) Load data in the table and check the directory for the file
3) If the file is present then you run select * query

alternatively, what you can do is check your current table directory
:"home/nophiq/Programmi/hive-0.10.0/warehouse/momis_test_a_3"
If there are any files, you can do "hadoop dfs -cat " on that file and see
if that shows your content.
If that shows some content then we will need to see why hive is not able to
read the file

On Mon, Sep 30, 2013 at 4:28 PM, Claudio Reggiani <[EMAIL PROTECTED]> wrote:

> Thanks Nitin for the reply,
>
> if I run the query "SELECT * FROM momis_test_a_3" I get an empty result
> set with no errors. Instead I would expect all the results.
>
> My best guess is that because of timestamp data the whole dataset is not
> able to be loaded. But since I don't have any errors (of any kind) I don't
> know where to puts my hands on.
>
> Claudio
>
>
> 2013/9/30 Nitin Pawar <[EMAIL PROTECTED]>
>
>> Sorry but I could not understand the issues you are facing.
>>
>> When you loaded data, did select col from table for the timestamp column,
>> what error did you get? what data did you get?
>> this is the default datetime format "yyyy-MM-dd hh:mm:ss.ffff"
>>
>> Looking at your sample data seems to match the format. Can you show us
>> some error or what you expect to see as the query output?
>>
>>
>> On Mon, Sep 30, 2013 at 3:36 PM, Claudio Reggiani <[EMAIL PROTECTED]>wrote:
>>
>>> Hello,
>>>
>>> For unit testing, I would like to load from a local file data that has
>>> several columns, one is also Timestamp. The command I use is "LOAD DATA
>>> LOCAL INPATH... ".
>>>
>>> Unfortunately that column does not allow me to load all the dataset. I
>>> have no error in the log of my local apache hive server, everything looks
>>> ok. By the way, officially the data type Timestamp is available.
>>>
>>> For completeness, I'm using hive version: 0.10.0 and I report both the
>>> script which format the database and the dataset:
>>>
>>> ---------------------
>>> hive> DROP TABLE momis_test_a_3
>>> hive> CREATE TABLE momis_test_a_3 (col1 STRING, col2 DOUBLE, col3 FLOAT,
>>> col4 TIMESTAMP, col5 BOOLEAN) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
>>> STORED AS TEXTFILE
>>> hive> LOAD DATA LOCAL INPATH
>>> '/home/nophiq/Programmi/Eclipse-Indigo-Momis/workspace/datariver/datariver-querymanager/test/sources/hive/dataset3'
>>> OVERWRITE INTO TABLE momis_test_a_3
>>>
>>> ---------------------
>>> testo1,100.00,201.00,2013-01-­01 04:00:00.123,true
>>> testo2,300.00,401.00,2013-01-02 04:00:00.123,false
>>> testo3,500.00,601.00,2013-01-03 04:00:00.123,false
>>>
>>>
>>> Finally, here it is the log from the local server:
>>>
>>> Copying data from
>>> file:/home/nophiq/Programmi/Eclipse-Indigo-Momis/workspace/datariver/datariver-querymanager/test/sources/hive/dataset3
>>> Copying file:
>>> file:/home/nophiq/Programmi/Eclipse-Indigo-Momis/workspace/datariver/datariver-querymanager/test/sources/hive/dataset3
>>> Loading data to table default.momis_test_a_3
>>> Deleted file:/home/nophiq/Programmi/hive-0.10.0/warehouse/momis_test_a_3
>>> Table default.momis_test_a_3 stats: [num_partitions: 0, num_files: 1,
>>> num_rows: 0, total_size: 182, raw_data_size: 0]
>>> OK
>>> OK
>>>
>>>
>>> How can I load timestamp data type from a local file? I don't want to
>>> create an external table.
>>> Any suggestion?
>>>
>>> Thanks
>>> Claudio Reggiani
>>>
>>
>>
>>
>> --
>> Nitin Pawar
>>
>
>
--
Nitin Pawar