Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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
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
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB