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

Switch to Threaded View
Hive, mail # user - Loading data into data_dim table


Copy link to this message
-
Re: Loading data into data_dim table
Bejoy KS 2012-07-25, 11:50
Hi Prabhu

Your data is tab delimited use /t as the delimiter while creating table.

fields terminated by '/t'

Not sure this is the right / or not. If this doesn't work try the other one.

Regards
Bejoy KS

Sent from handheld, please excuse typos.

-----Original Message-----
From: prabhu k <[EMAIL PROTECTED]>
Date: Wed, 25 Jul 2012 17:10:09
To: <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
Subject: Re: Loading data into data_dim table

Thanks for the reply.

I have tried the with delimited fields terminated by '|'  and delimited
fields terminated by ','  while selecting the table both Im getting null .

when i see the HDFS file looks like below.
bin/hadoop fs -cat /user/hive/warehoure/time.txt

7666 2020-12-26 00:00:00.000    2020    4   12  3       52 13   4
361      87 26   7       December        Dec     Saturday        Sat
20201226        2020/12/26  Dec 26 2020 2020-12-26
7667 2020-12-27 00:00:00.000    2020    4   12  3       53 14   5
362      88 27   1       December        Dec     Sunday  Sun
20201227        2020/12/27     Dec 27 2020  2020-12-27
7668 2020-12-28 00:00:00.000    2020    4   12  3       53 14   5
363      89 28   2       December        Dec     Monday  Mon
20201228        2020/12/28     Dec 28 2020  2020-12-28
7669 2020-12-29 00:00:00.000    2020    4   12  3       53 14   5
364      90 29   3       December        Dec     Tuesday Tue
20201229        2020/12/29     Dec 29 2020  2020-12-29
7670 2020-12-30 00:00:00.000    2020    4   12  3       53 14   5
365      91 30   4       December        Dec     Wednesday       Wed
20201230        2020/12/30  Dec 30 2020 2020-12-30
7671 2020-12-31 00:00:00.000    2020    4   12  3       53 14   5
366      92 31   5       December        Dec     Thursday        Thu
20201231        2020/12/31  Dec 31 2020 2020-12-31

Please suggest and help me.
Thanks,
Prabhu.
On Wed, Jul 25, 2012 at 4:58 PM, Bertrand Dechoux <[EMAIL PROTECTED]>wrote:

> What Bejoy is saying implicitly, is that the format is not verified by
> the load command. If it does not match, you will get NULL.
> And it would be curious that your comma separated value (csv) file is
> using pipe (|) but why not.
>
> Bertrand
>
> On Wed, Jul 25, 2012 at 12:45 PM, Bejoy KS <[EMAIL PROTECTED]> wrote:
>
>> **
>> Hi Prabhu
>>
>> Can you cat the file in hdfs and ensure that the fields are delimited by
>> '|' character.
>>
>> hadoop fs -text user/hive/warehouse/dim_date/time.csv
>> Regards
>> Bejoy KS
>>
>> Sent from handheld, please excuse typos.
>> ------------------------------
>> *From: *prabhu k <[EMAIL PROTECTED]>
>> *Date: *Wed, 25 Jul 2012 16:05:42 +0530
>> *To: *<[EMAIL PROTECTED]>
>> *ReplyTo: *[EMAIL PROTECTED]
>> *Subject: *Loading data into data_dim table
>>
>> Hi Users,
>>
>>
>> I have created dim_date table like below. table created successfully and
>> i then load the data into the dim_date table
>>
>> while i am selecting the table, getting null values.my input file is
>> time.csv file
>>
>> hive> create table dim_date(DateId int,ddate string,Year int,Quarter
>> int,Month_Number_Year int,Month_Number_Of_Quarter int,Week_Number_Of_Year
>> int,Week_Number_Of_Quarter int,Week_Number_Of_Month int,Day_Number_Of_Year
>> int,Day_Number_Of_Quarter int,Day_Number_Of_Month int,Day_Number_Of_Week
>> int,Month_Name string,Month_Name_Abbreviation string,Day_Name
>> string,Day_Name_Abbreviation string)row format delimited fields terminated
>> by '|' stored as textfile;
>> OK
>> Time taken: 0.123 seconds
>>
>> hive> load data local inpath '/usr/local/time.csv' overwrite into table
>> dim_date;
>> Copying data from file:/usr/local/time.csv
>> Copying file: file:/usr/local/time.csv
>> Loading data to table default.dim_date
>> Deleted hdfs://md-aaa1:54310/user/hive/warehouse/dim_date
>> OK
>> Time taken: 0.234 seconds
>>
>> hive> select * from dim_date;
>> NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
>> NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
>> NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL