You are viewing a plain text version of this content. The canonical link for it is here.
Posted to hdfs-user@hadoop.apache.org by Giri P <gp...@gmail.com> on 2015/05/15 20:31:39 UTC
Hive question
Hi All,
Since hive is schema on read when we try to write data that is different
data type into a column it doesn't throw any error. When we try to read it
, it actually show NULL if its a different data type.
Are there any options to throw error if data is of different data type when
we try to insert or read
Thanks
Giri
Re: Hive question
Posted by Giri P <gp...@gmail.com>.
But coalesce returns first not null value.
what I'm trying here is to throw error when I try to read the data using
hive table of different datatype
create external table test123(id int, name string)
row format delimited fields terminated by '\t'
location /user/sandbox/test.tsv
test.csv
1 king
2 queen
3a john
When we try to read that data the last row "3a" is show as null since it
cannot cast that to INT. But here in my case I want to throw error
something like invalid data.
I did post in hive user group also but didn't get any response
On Fri, May 15, 2015 at 4:02 PM, Rajesh Kartha <ka...@gmail.com> wrote:
> Hi Giri,
>
> Have you tried the COALESCE function:
>
> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-ConditionalFunctions
>
> example for a date column type:
> hive> select COALESCE(dt, cast('2014-02-23' as date)) from simpletest;
>
> In any case, maybe good to post the question in the Hive user/dev group as
> well.
>
> -Rajesh
>
>
> On Fri, May 15, 2015 at 11:31 AM, Giri P <gp...@gmail.com> wrote:
>
>> Hi All,
>>
>> Since hive is schema on read when we try to write data that is different
>> data type into a column it doesn't throw any error. When we try to read it
>> , it actually show NULL if its a different data type.
>>
>> Are there any options to throw error if data is of different data type
>> when we try to insert or read
>>
>>
>> Thanks
>> Giri
>>
>
>
Re: Hive question
Posted by Giri P <gp...@gmail.com>.
But coalesce returns first not null value.
what I'm trying here is to throw error when I try to read the data using
hive table of different datatype
create external table test123(id int, name string)
row format delimited fields terminated by '\t'
location /user/sandbox/test.tsv
test.csv
1 king
2 queen
3a john
When we try to read that data the last row "3a" is show as null since it
cannot cast that to INT. But here in my case I want to throw error
something like invalid data.
I did post in hive user group also but didn't get any response
On Fri, May 15, 2015 at 4:02 PM, Rajesh Kartha <ka...@gmail.com> wrote:
> Hi Giri,
>
> Have you tried the COALESCE function:
>
> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-ConditionalFunctions
>
> example for a date column type:
> hive> select COALESCE(dt, cast('2014-02-23' as date)) from simpletest;
>
> In any case, maybe good to post the question in the Hive user/dev group as
> well.
>
> -Rajesh
>
>
> On Fri, May 15, 2015 at 11:31 AM, Giri P <gp...@gmail.com> wrote:
>
>> Hi All,
>>
>> Since hive is schema on read when we try to write data that is different
>> data type into a column it doesn't throw any error. When we try to read it
>> , it actually show NULL if its a different data type.
>>
>> Are there any options to throw error if data is of different data type
>> when we try to insert or read
>>
>>
>> Thanks
>> Giri
>>
>
>
Re: Hive question
Posted by Giri P <gp...@gmail.com>.
But coalesce returns first not null value.
what I'm trying here is to throw error when I try to read the data using
hive table of different datatype
create external table test123(id int, name string)
row format delimited fields terminated by '\t'
location /user/sandbox/test.tsv
test.csv
1 king
2 queen
3a john
When we try to read that data the last row "3a" is show as null since it
cannot cast that to INT. But here in my case I want to throw error
something like invalid data.
I did post in hive user group also but didn't get any response
On Fri, May 15, 2015 at 4:02 PM, Rajesh Kartha <ka...@gmail.com> wrote:
> Hi Giri,
>
> Have you tried the COALESCE function:
>
> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-ConditionalFunctions
>
> example for a date column type:
> hive> select COALESCE(dt, cast('2014-02-23' as date)) from simpletest;
>
> In any case, maybe good to post the question in the Hive user/dev group as
> well.
>
> -Rajesh
>
>
> On Fri, May 15, 2015 at 11:31 AM, Giri P <gp...@gmail.com> wrote:
>
>> Hi All,
>>
>> Since hive is schema on read when we try to write data that is different
>> data type into a column it doesn't throw any error. When we try to read it
>> , it actually show NULL if its a different data type.
>>
>> Are there any options to throw error if data is of different data type
>> when we try to insert or read
>>
>>
>> Thanks
>> Giri
>>
>
>
Re: Hive question
Posted by Giri P <gp...@gmail.com>.
But coalesce returns first not null value.
what I'm trying here is to throw error when I try to read the data using
hive table of different datatype
create external table test123(id int, name string)
row format delimited fields terminated by '\t'
location /user/sandbox/test.tsv
test.csv
1 king
2 queen
3a john
When we try to read that data the last row "3a" is show as null since it
cannot cast that to INT. But here in my case I want to throw error
something like invalid data.
I did post in hive user group also but didn't get any response
On Fri, May 15, 2015 at 4:02 PM, Rajesh Kartha <ka...@gmail.com> wrote:
> Hi Giri,
>
> Have you tried the COALESCE function:
>
> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-ConditionalFunctions
>
> example for a date column type:
> hive> select COALESCE(dt, cast('2014-02-23' as date)) from simpletest;
>
> In any case, maybe good to post the question in the Hive user/dev group as
> well.
>
> -Rajesh
>
>
> On Fri, May 15, 2015 at 11:31 AM, Giri P <gp...@gmail.com> wrote:
>
>> Hi All,
>>
>> Since hive is schema on read when we try to write data that is different
>> data type into a column it doesn't throw any error. When we try to read it
>> , it actually show NULL if its a different data type.
>>
>> Are there any options to throw error if data is of different data type
>> when we try to insert or read
>>
>>
>> Thanks
>> Giri
>>
>
>
Re: Hive question
Posted by Rajesh Kartha <ka...@gmail.com>.
Hi Giri,
Have you tried the COALESCE function:
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-ConditionalFunctions
example for a date column type:
hive> select COALESCE(dt, cast('2014-02-23' as date)) from simpletest;
In any case, maybe good to post the question in the Hive user/dev group as
well.
-Rajesh
On Fri, May 15, 2015 at 11:31 AM, Giri P <gp...@gmail.com> wrote:
> Hi All,
>
> Since hive is schema on read when we try to write data that is different
> data type into a column it doesn't throw any error. When we try to read it
> , it actually show NULL if its a different data type.
>
> Are there any options to throw error if data is of different data type
> when we try to insert or read
>
>
> Thanks
> Giri
>
Re: Hive question
Posted by Rajesh Kartha <ka...@gmail.com>.
Hi Giri,
Have you tried the COALESCE function:
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-ConditionalFunctions
example for a date column type:
hive> select COALESCE(dt, cast('2014-02-23' as date)) from simpletest;
In any case, maybe good to post the question in the Hive user/dev group as
well.
-Rajesh
On Fri, May 15, 2015 at 11:31 AM, Giri P <gp...@gmail.com> wrote:
> Hi All,
>
> Since hive is schema on read when we try to write data that is different
> data type into a column it doesn't throw any error. When we try to read it
> , it actually show NULL if its a different data type.
>
> Are there any options to throw error if data is of different data type
> when we try to insert or read
>
>
> Thanks
> Giri
>
Re: Hive question
Posted by Rajesh Kartha <ka...@gmail.com>.
Hi Giri,
Have you tried the COALESCE function:
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-ConditionalFunctions
example for a date column type:
hive> select COALESCE(dt, cast('2014-02-23' as date)) from simpletest;
In any case, maybe good to post the question in the Hive user/dev group as
well.
-Rajesh
On Fri, May 15, 2015 at 11:31 AM, Giri P <gp...@gmail.com> wrote:
> Hi All,
>
> Since hive is schema on read when we try to write data that is different
> data type into a column it doesn't throw any error. When we try to read it
> , it actually show NULL if its a different data type.
>
> Are there any options to throw error if data is of different data type
> when we try to insert or read
>
>
> Thanks
> Giri
>
Re: Hive question
Posted by Rajesh Kartha <ka...@gmail.com>.
Hi Giri,
Have you tried the COALESCE function:
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-ConditionalFunctions
example for a date column type:
hive> select COALESCE(dt, cast('2014-02-23' as date)) from simpletest;
In any case, maybe good to post the question in the Hive user/dev group as
well.
-Rajesh
On Fri, May 15, 2015 at 11:31 AM, Giri P <gp...@gmail.com> wrote:
> Hi All,
>
> Since hive is schema on read when we try to write data that is different
> data type into a column it doesn't throw any error. When we try to read it
> , it actually show NULL if its a different data type.
>
> Are there any options to throw error if data is of different data type
> when we try to insert or read
>
>
> Thanks
> Giri
>