You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by zml张明磊 <mi...@Ctrip.com> on 2015/12/16 07:15:01 UTC

Loading data from HDFS to hive and leading to many NULL value in hive table

I am confusing about the following result. Why the hive table has so many NULL value ?

hive> select * from managers;
OK
fergubo01m,BS1,31,20,10,10NULLNULLNULLNULLNULL
woodji01m,CH1,28,19,9,9NULLNULLNULLNULLNULL
paborch01m,CL1,29,10,19,19NULLNULLNULLNULLNULL
lennobi01m,FW1,14,5,9,9NULLNULLNULLNULLNULL
deaneha01m,FW1,5,2,3,3NULLNULLNULLNULLNULL
fergubo01m,NY2,33,16,17,17NULLNULLNULLNULLNULL
Time taken: 0.107 seconds, Fetched: 6 row(s)

This is my create table code : create table managers  (managerID string, teamID string, g int, w int, l int, x int);

managers.txt in HDFS structure
fergubo01m,BS1,31,20,10,10
woodji01m,CH1,28,19,9,9
paborch01m,CL1,29,10,19,19
lennobi01m,FW1,14,5,9,9
deaneha01m,FW1,5,2,3,3
fergubo01m,NY2,33,16,17,17

Loading data from HDFS to hive code
 LOAD DATA INPATH '/managers.txt' OVERWRITE TABLE managers;

What’s wrong with these NULL value?

答复: Loading data from HDFS to hive and leading to many NULL value in hive table

Posted by zml张明磊 <mi...@Ctrip.com>.
OK, I know.

Thanks
Mingle.


发件人: Jörn Franke [mailto:jornfranke@gmail.com]
发送时间: 2015年12月16日 15:15
收件人: user@hive.apache.org
主题: Re: Loading data from HDFS to hive and leading to many NULL value in hive table

You forgot to tell Hive that the file is comma-separated. You may want to use the CSV serde.

On 16 Dec 2015, at 07:15, zml张明磊 <mi...@ctrip.com>> wrote:
I am confusing about the following result. Why the hive table has so many NULL value ?

hive> select * from managers;
OK
fergubo01m,BS1,31,20,10,10NULLNULLNULLNULLNULL
woodji01m,CH1,28,19,9,9NULLNULLNULLNULLNULL
paborch01m,CL1,29,10,19,19NULLNULLNULLNULLNULL
lennobi01m,FW1,14,5,9,9NULLNULLNULLNULLNULL
deaneha01m,FW1,5,2,3,3NULLNULLNULLNULLNULL
fergubo01m,NY2,33,16,17,17NULLNULLNULLNULLNULL
Time taken: 0.107 seconds, Fetched: 6 row(s)

This is my create table code : create table managers  (managerID string, teamID string, g int, w int, l int, x int);

managers.txt in HDFS structure
fergubo01m,BS1,31,20,10,10
woodji01m,CH1,28,19,9,9
paborch01m,CL1,29,10,19,19
lennobi01m,FW1,14,5,9,9
deaneha01m,FW1,5,2,3,3
fergubo01m,NY2,33,16,17,17

Loading data from HDFS to hive code
 LOAD DATA INPATH '/managers.txt' OVERWRITE TABLE managers;

What’s wrong with these NULL value?

Re: Loading data from HDFS to hive and leading to many NULL value in hive table

Posted by Jörn Franke <jo...@gmail.com>.
You forgot to tell Hive that the file is comma-separated. You may want to use the CSV serde.

> On 16 Dec 2015, at 07:15, zml张明磊 <mi...@Ctrip.com> wrote:
> 
> I am confusing about the following result. Why the hive table has so many NULL value ?
>  
> hive> select * from managers;
> OK
> fergubo01m,BS1,31,20,10,10NULLNULLNULLNULLNULL
> woodji01m,CH1,28,19,9,9NULLNULLNULLNULLNULL
> paborch01m,CL1,29,10,19,19NULLNULLNULLNULLNULL
> lennobi01m,FW1,14,5,9,9NULLNULLNULLNULLNULL
> deaneha01m,FW1,5,2,3,3NULLNULLNULLNULLNULL
> fergubo01m,NY2,33,16,17,17NULLNULLNULLNULLNULL
> Time taken: 0.107 seconds, Fetched: 6 row(s)
>  
> This is my create table code : create table managers  (managerID string, teamID string, g int, w int, l int, x int);
>  
> managers.txt in HDFS structure
> fergubo01m,BS1,31,20,10,10
> woodji01m,CH1,28,19,9,9
> paborch01m,CL1,29,10,19,19
> lennobi01m,FW1,14,5,9,9
> deaneha01m,FW1,5,2,3,3
> fergubo01m,NY2,33,16,17,17
>  
> Loading data from HDFS to hive code
>  LOAD DATA INPATH '/managers.txt' OVERWRITE TABLE managers;
>  
> What’s wrong with these NULL value?

答复: Loading data from HDFS to hive and leading to many NULL value in hive table

Posted by zml张明磊 <mi...@Ctrip.com>.
OK, I solve this by add row format delimited fields terminated by ”symbol” ………….. ffffffffff…fff

发件人: zml张明磊 [mailto:mingleizhang@Ctrip.com]
发送时间: 2015年12月16日 14:15
收件人: user@hive.apache.org
主题: Loading data from HDFS to hive and leading to many NULL value in hive table

I am confusing about the following result. Why the hive table has so many NULL value ?

hive> select * from managers;
OK
fergubo01m,BS1,31,20,10,10NULLNULLNULLNULLNULL
woodji01m,CH1,28,19,9,9NULLNULLNULLNULLNULL
paborch01m,CL1,29,10,19,19NULLNULLNULLNULLNULL
lennobi01m,FW1,14,5,9,9NULLNULLNULLNULLNULL
deaneha01m,FW1,5,2,3,3NULLNULLNULLNULLNULL
fergubo01m,NY2,33,16,17,17NULLNULLNULLNULLNULL
Time taken: 0.107 seconds, Fetched: 6 row(s)

This is my create table code : create table managers  (managerID string, teamID string, g int, w int, l int, x int);

managers.txt in HDFS structure
fergubo01m,BS1,31,20,10,10
woodji01m,CH1,28,19,9,9
paborch01m,CL1,29,10,19,19
lennobi01m,FW1,14,5,9,9
deaneha01m,FW1,5,2,3,3
fergubo01m,NY2,33,16,17,17

Loading data from HDFS to hive code
 LOAD DATA INPATH '/managers.txt' OVERWRITE TABLE managers;

What’s wrong with these NULL value?