You could do something with the coalesce UDF?

Phil.
On Sep 5, 2012 12:24 AM, "MiaoMiao" <[EMAIL PROTECTED]> 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
>

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:[EMAIL PROTECTED]]
Sent: Wednesday, September 05, 2012 07:52
To: [EMAIL PROTECTED]
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" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> 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

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,  <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]]
> Sent: Wednesday, September 05, 2012 07:52
> To: [EMAIL PROTECTED]
> 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" <[EMAIL PROTECTED]> 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

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
<[EMAIL PROTECTED]> wrote:
> You could do something with the coalesce UDF?
>
> Phil.
>
> On Sep 5, 2012 12:24 AM, "MiaoMiao" <[EMAIL PROTECTED]> 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

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
<[EMAIL PROTECTED]> wrote:
> You could do something with the coalesce UDF?
>
> Phil.
>
> On Sep 5, 2012 12:24 AM, "MiaoMiao" <[EMAIL PROTECTED]> 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

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" <[EMAIL PROTECTED]> 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
> <[EMAIL PROTECTED]> wrote:
> > You could do something with the coalesce UDF?
> >
> > Phil.
> >
> > On Sep 5, 2012 12:24 AM, "MiaoMiao" <[EMAIL PROTECTED]> 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
>

Thank you all, guess I just have to do it this way.

On Thu, Sep 6, 2012 at 11:50 AM, Philip Tromans
<[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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
>> <[EMAIL PROTECTED]> wrote:
>> > You could do something with the coalesce UDF?
>> >
>> > Phil.
>> >
>> > On Sep 5, 2012 12:24 AM, "MiaoMiao" <[EMAIL PROTECTED]> 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