You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by MiaoMiao <li...@gmail.com> on 2012/09/05 06:23:10 UTC
How to set default value for a certain field?
I have a file whose content is:
1,1
2,1
3,2
4,
5,
Then I import in into a hive table.
create external table testtest (id int,value int) row format delimited
fields terminated by ',' stored as textfile location '/wtt/test/def';
select * from testtest;
1 1
2 1
3 2
4 NULL
5 NULL
I want to set default value for value, but seems hive simply doesn't
have this feature, any idea?
1 1
2 1
3 2
4 3
5 3
Re: How to set default value for a certain field?
Posted by MiaoMiao <li...@gmail.com>.
Thank you all, guess I just have to do it this way.
On Thu, Sep 6, 2012 at 11:50 AM, Philip Tromans
<ph...@gmail.com> wrote:
> Yep. It's not an ideal solution, but it gets you part of the way there. Hive
> doesn't have a way of specifying default values at schema level.
>
> Phil.
>
> On Sep 5, 2012 11:38 PM, "MiaoMiao" <li...@gmail.com> wrote:
>>
>> You mean COALESCE(value,3)? This does work on int field, and easier than
>> IF
>>
>> select value,COALESCE(value,3) from testtest;
>> 1 1
>> 1 1
>> 2 2
>> NULL 3
>> NULL 3
>>
>> On Wed, Sep 5, 2012 at 7:52 PM, Philip Tromans
>> <ph...@gmail.com> wrote:
>> > You could do something with the coalesce UDF?
>> >
>> > Phil.
>> >
>> > On Sep 5, 2012 12:24 AM, "MiaoMiao" <li...@gmail.com> wrote:
>> >>
>> >> I have a file whose content is:
>> >> 1,1
>> >> 2,1
>> >> 3,2
>> >> 4,
>> >> 5,
>> >> Then I import in into a hive table.
>> >> create external table testtest (id int,value int) row format delimited
>> >> fields terminated by ',' stored as textfile location '/wtt/test/def';
>> >> select * from testtest;
>> >> 1 1
>> >> 2 1
>> >> 3 2
>> >> 4 NULL
>> >> 5 NULL
>> >>
>> >> I want to set default value for value, but seems hive simply doesn't
>> >> have this feature, any idea?
>> >> 1 1
>> >> 2 1
>> >> 3 2
>> >> 4 3
>> >> 5 3
Re: How to set default value for a certain field?
Posted by Philip Tromans <ph...@gmail.com>.
Yep. It's not an ideal solution, but it gets you part of the way there.
Hive doesn't have a way of specifying default values at schema level.
Phil.
On Sep 5, 2012 11:38 PM, "MiaoMiao" <li...@gmail.com> wrote:
> You mean COALESCE(value,3)? This does work on int field, and easier than IF
>
> select value,COALESCE(value,3) from testtest;
> 1 1
> 1 1
> 2 2
> NULL 3
> NULL 3
>
> On Wed, Sep 5, 2012 at 7:52 PM, Philip Tromans
> <ph...@gmail.com> wrote:
> > You could do something with the coalesce UDF?
> >
> > Phil.
> >
> > On Sep 5, 2012 12:24 AM, "MiaoMiao" <li...@gmail.com> wrote:
> >>
> >> I have a file whose content is:
> >> 1,1
> >> 2,1
> >> 3,2
> >> 4,
> >> 5,
> >> Then I import in into a hive table.
> >> create external table testtest (id int,value int) row format delimited
> >> fields terminated by ',' stored as textfile location '/wtt/test/def';
> >> select * from testtest;
> >> 1 1
> >> 2 1
> >> 3 2
> >> 4 NULL
> >> 5 NULL
> >>
> >> I want to set default value for value, but seems hive simply doesn't
> >> have this feature, any idea?
> >> 1 1
> >> 2 1
> >> 3 2
> >> 4 3
> >> 5 3
>
Re: How to set default value for a certain field?
Posted by MiaoMiao <li...@gmail.com>.
You mean COALESCE(value,3)? This does work on int field, and easier than IF
select value,COALESCE(value,3) from testtest;
1 1
1 1
2 2
NULL 3
NULL 3
On Wed, Sep 5, 2012 at 7:52 PM, Philip Tromans
<ph...@gmail.com> wrote:
> You could do something with the coalesce UDF?
>
> Phil.
>
> On Sep 5, 2012 12:24 AM, "MiaoMiao" <li...@gmail.com> wrote:
>>
>> I have a file whose content is:
>> 1,1
>> 2,1
>> 3,2
>> 4,
>> 5,
>> Then I import in into a hive table.
>> create external table testtest (id int,value int) row format delimited
>> fields terminated by ',' stored as textfile location '/wtt/test/def';
>> select * from testtest;
>> 1 1
>> 2 1
>> 3 2
>> 4 NULL
>> 5 NULL
>>
>> I want to set default value for value, but seems hive simply doesn't
>> have this feature, any idea?
>> 1 1
>> 2 1
>> 3 2
>> 4 3
>> 5 3
Re: How to set default value for a certain field?
Posted by MiaoMiao <li...@gmail.com>.
COALESCE how?
COALESCE(VALUE) will return VALUE or null.
I know this query works
select IF(VALUE is NULL,3,VALUE) from testtest;
But I have to do this conditional check on every possible field, every
time I perform queries. I mean, does Hive have something like
CREATE TABLE table (field INT NOT NULL) ?
On Wed, Sep 5, 2012 at 7:52 PM, Philip Tromans
<ph...@gmail.com> wrote:
> You could do something with the coalesce UDF?
>
> Phil.
>
> On Sep 5, 2012 12:24 AM, "MiaoMiao" <li...@gmail.com> wrote:
>>
>> I have a file whose content is:
>> 1,1
>> 2,1
>> 3,2
>> 4,
>> 5,
>> Then I import in into a hive table.
>> create external table testtest (id int,value int) row format delimited
>> fields terminated by ',' stored as textfile location '/wtt/test/def';
>> select * from testtest;
>> 1 1
>> 2 1
>> 3 2
>> 4 NULL
>> 5 NULL
>>
>> I want to set default value for value, but seems hive simply doesn't
>> have this feature, any idea?
>> 1 1
>> 2 1
>> 3 2
>> 4 3
>> 5 3
Re: How to set default value for a certain field?
Posted by MiaoMiao <li...@gmail.com>.
This will certainly work, but with 20 tables, each of 20GB size, using
insert overwrite could take up both time and space a lot.
On Wed, Sep 5, 2012 at 7:57 PM, <ca...@nokia.com> wrote:
> Well, you could create the table and then insert overwrite from the file:
>
>
>
> Insert overwrite
>
> Select id, case when value = ‘’ then 3 else value end as value
>
> From testtest;
>
>
>
> Carla
>
>
>
> From: ext Philip Tromans [mailto:philip.j.tromans@gmail.com]
> Sent: Wednesday, September 05, 2012 07:52
> To: user@hive.apache.org
> Subject: Re: How to set default value for a certain field?
>
>
>
> You could do something with the coalesce UDF?
>
> Phil.
>
> On Sep 5, 2012 12:24 AM, "MiaoMiao" <li...@gmail.com> wrote:
>
> I have a file whose content is:
> 1,1
> 2,1
> 3,2
> 4,
> 5,
> Then I import in into a hive table.
> create external table testtest (id int,value int) row format delimited
> fields terminated by ',' stored as textfile location '/wtt/test/def';
> select * from testtest;
> 1 1
> 2 1
> 3 2
> 4 NULL
> 5 NULL
>
> I want to set default value for value, but seems hive simply doesn't
> have this feature, any idea?
> 1 1
> 2 1
> 3 2
> 4 3
> 5 3
RE: How to set default value for a certain field?
Posted by ca...@nokia.com.
Well, you could create the table and then insert overwrite from the file:
Insert overwrite
Select id, case when value = '' then 3 else value end as value
>From testtest;
Carla
From: ext Philip Tromans [mailto:philip.j.tromans@gmail.com]
Sent: Wednesday, September 05, 2012 07:52
To: user@hive.apache.org
Subject: Re: How to set default value for a certain field?
You could do something with the coalesce UDF?
Phil.
On Sep 5, 2012 12:24 AM, "MiaoMiao" <li...@gmail.com>> wrote:
I have a file whose content is:
1,1
2,1
3,2
4,
5,
Then I import in into a hive table.
create external table testtest (id int,value int) row format delimited
fields terminated by ',' stored as textfile location '/wtt/test/def';
select * from testtest;
1 1
2 1
3 2
4 NULL
5 NULL
I want to set default value for value, but seems hive simply doesn't
have this feature, any idea?
1 1
2 1
3 2
4 3
5 3
Re: How to set default value for a certain field?
Posted by Philip Tromans <ph...@gmail.com>.
You could do something with the coalesce UDF?
Phil.
On Sep 5, 2012 12:24 AM, "MiaoMiao" <li...@gmail.com> wrote:
> I have a file whose content is:
> 1,1
> 2,1
> 3,2
> 4,
> 5,
> Then I import in into a hive table.
> create external table testtest (id int,value int) row format delimited
> fields terminated by ',' stored as textfile location '/wtt/test/def';
> select * from testtest;
> 1 1
> 2 1
> 3 2
> 4 NULL
> 5 NULL
>
> I want to set default value for value, but seems hive simply doesn't
> have this feature, any idea?
> 1 1
> 2 1
> 3 2
> 4 3
> 5 3
>