You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Shankar Mane <ss...@gmail.com> on 2019/07/17 11:14:09 UTC
Apache Hive 2.3.4 - Issue with combination of Like operator & newline
(\n) character in data
Hi All,
I am facing some issues while using Like operator & newline (\n) character.
Below is the in details description :
*-------------------------------------------------- Hive Queries
------------------------------------------------------------*
create table default.withdraw(
id string
) stored as parquet;
insert into default.withdraw select 'withdraw\ncash';
*--1) result = success*
hive> select * from default.withdraw where id like '%withdraw%';
OK
withdraw
cash
Time taken: 0.078 seconds, Fetched: 1 row(s)
*--2) **result = wrong*
hive> select * from default.withdraw where id like '%withdraw%cash';
OK
Time taken: 0.066 seconds
*--3) **result = success*
hive> select * from default.withdraw where id like '%cash%';
OK
withdraw
cash
Time taken: 0.086 seconds, Fetched: 1 row(s)
*-------------------------------------------------- Presto Queries
-----------------------------------------------------*
FYI - Presto (v0.221) is using above table meta store. We tested above
queries on presto too.
*--1) **result = **success*
presto> select * from default.withdraw where id like '%withdraw%';
id
----------
withdraw
cash
(1 row)
*--2) **result = **success*
presto> select * from default.withdraw where id like '%withdraw%cash';
id
----------
withdraw
cash
(1 row)
*--3) **result = **success*
presto> select * from default.withdraw where id like '%cash%';
id
----------
withdraw
cash
(1 row)
*-------------------------------------------------- *
*-------------------------------------------------- *
Please help here in case i am missing anything.
regards,
shankar
Re: Apache Hive 2.3.4 - Issue with combination of Like operator &
newline (\n) character in data
Posted by Shankar Mane <ss...@gmail.com>.
It will worked when your aware of data. This solution is not suitable when
column data is unknown and dynamic in nature.
On Mon, 29 Jul 2019, 21:50 Sungwoo Park, <gl...@gmail.com> wrote:
> Not a solution, but one can use \n in the search string, e.g.:
>
> select * from default.withdraw where id like '%withdraw\ncash';
> select * from default.withdraw where id like '%withdraw%\ncash';
> select * from default.withdraw where id like '%withdraw%\n%cash';
>
> --- Sungwoo
>
>
> On Tue, Jul 30, 2019 at 12:58 AM Shankar Mane <ss...@gmail.com> wrote:
>
>> Can anyone looking at this issue ?
>>
>> On Sat, Jul 20, 2019 at 9:08 AM Shankar Mane <ss...@gmail.com> wrote:
>>
>>> Have created jira at https://issues.apache.org/jira/browse/HIVE-22008
>>> <https://www.google.com/url?q=https://issues.apache.org/jira/browse/HIVE-22008&sa=D&source=hangouts&ust=1563538731290000&usg=AFQjCNHrfFVVopKL-7k-5dGBYICOVAHMDg>
>>>
>>> On Wed, 17 Jul 2019, 16:44 Shankar Mane, <ss...@gmail.com> wrote:
>>>
>>>> Hi All,
>>>>
>>>> I am facing some issues while using Like operator & newline (\n)
>>>> character. Below is the in details description :
>>>>
>>>> *-------------------------------------------------- Hive Queries
>>>> ------------------------------------------------------------*
>>>>
>>>> create table default.withdraw(
>>>> id string
>>>> ) stored as parquet;
>>>>
>>>>
>>>> insert into default.withdraw select 'withdraw\ncash';
>>>>
>>>>
>>>> *--1) result = success*
>>>>
>>>> hive> select * from default.withdraw where id like '%withdraw%';
>>>> OK
>>>> withdraw
>>>> cash
>>>> Time taken: 0.078 seconds, Fetched: 1 row(s)
>>>>
>>>>
>>>> *--2) **result = wrong*
>>>>
>>>> hive> select * from default.withdraw where id like '%withdraw%cash';
>>>> OK
>>>> Time taken: 0.066 seconds
>>>>
>>>>
>>>> *--3) **result = success*
>>>>
>>>> hive> select * from default.withdraw where id like '%cash%';
>>>> OK
>>>> withdraw
>>>> cash
>>>> Time taken: 0.086 seconds, Fetched: 1 row(s)
>>>>
>>>>
>>>>
>>>> *-------------------------------------------------- Presto Queries
>>>> -----------------------------------------------------*
>>>> FYI - Presto (v0.221) is using above table meta store. We tested above
>>>> queries on presto too.
>>>>
>>>> *--1) **result = **success*
>>>>
>>>> presto> select * from default.withdraw where id like '%withdraw%';
>>>> id
>>>> ----------
>>>> withdraw
>>>> cash
>>>> (1 row)
>>>>
>>>>
>>>> *--2) **result = **success*
>>>>
>>>> presto> select * from default.withdraw where id like '%withdraw%cash';
>>>> id
>>>> ----------
>>>> withdraw
>>>> cash
>>>> (1 row)
>>>>
>>>>
>>>> *--3) **result = **success*
>>>>
>>>> presto> select * from default.withdraw where id like '%cash%';
>>>> id
>>>> ----------
>>>> withdraw
>>>> cash
>>>> (1 row)
>>>>
>>>> *-------------------------------------------------- *
>>>> *-------------------------------------------------- *
>>>>
>>>> Please help here in case i am missing anything.
>>>>
>>>> regards,
>>>> shankar
>>>>
>>>>
>>>>
Re: Apache Hive 2.3.4 - Issue with combination of Like operator &
newline (\n) character in data
Posted by Sungwoo Park <gl...@gmail.com>.
Not a solution, but one can use \n in the search string, e.g.:
select * from default.withdraw where id like '%withdraw\ncash';
select * from default.withdraw where id like '%withdraw%\ncash';
select * from default.withdraw where id like '%withdraw%\n%cash';
--- Sungwoo
On Tue, Jul 30, 2019 at 12:58 AM Shankar Mane <ss...@gmail.com> wrote:
> Can anyone looking at this issue ?
>
> On Sat, Jul 20, 2019 at 9:08 AM Shankar Mane <ss...@gmail.com> wrote:
>
>> Have created jira at https://issues.apache.org/jira/browse/HIVE-22008
>> <https://www.google.com/url?q=https://issues.apache.org/jira/browse/HIVE-22008&sa=D&source=hangouts&ust=1563538731290000&usg=AFQjCNHrfFVVopKL-7k-5dGBYICOVAHMDg>
>>
>> On Wed, 17 Jul 2019, 16:44 Shankar Mane, <ss...@gmail.com> wrote:
>>
>>> Hi All,
>>>
>>> I am facing some issues while using Like operator & newline (\n)
>>> character. Below is the in details description :
>>>
>>> *-------------------------------------------------- Hive Queries
>>> ------------------------------------------------------------*
>>>
>>> create table default.withdraw(
>>> id string
>>> ) stored as parquet;
>>>
>>>
>>> insert into default.withdraw select 'withdraw\ncash';
>>>
>>>
>>> *--1) result = success*
>>>
>>> hive> select * from default.withdraw where id like '%withdraw%';
>>> OK
>>> withdraw
>>> cash
>>> Time taken: 0.078 seconds, Fetched: 1 row(s)
>>>
>>>
>>> *--2) **result = wrong*
>>>
>>> hive> select * from default.withdraw where id like '%withdraw%cash';
>>> OK
>>> Time taken: 0.066 seconds
>>>
>>>
>>> *--3) **result = success*
>>>
>>> hive> select * from default.withdraw where id like '%cash%';
>>> OK
>>> withdraw
>>> cash
>>> Time taken: 0.086 seconds, Fetched: 1 row(s)
>>>
>>>
>>>
>>> *-------------------------------------------------- Presto Queries
>>> -----------------------------------------------------*
>>> FYI - Presto (v0.221) is using above table meta store. We tested above
>>> queries on presto too.
>>>
>>> *--1) **result = **success*
>>>
>>> presto> select * from default.withdraw where id like '%withdraw%';
>>> id
>>> ----------
>>> withdraw
>>> cash
>>> (1 row)
>>>
>>>
>>> *--2) **result = **success*
>>>
>>> presto> select * from default.withdraw where id like '%withdraw%cash';
>>> id
>>> ----------
>>> withdraw
>>> cash
>>> (1 row)
>>>
>>>
>>> *--3) **result = **success*
>>>
>>> presto> select * from default.withdraw where id like '%cash%';
>>> id
>>> ----------
>>> withdraw
>>> cash
>>> (1 row)
>>>
>>> *-------------------------------------------------- *
>>> *-------------------------------------------------- *
>>>
>>> Please help here in case i am missing anything.
>>>
>>> regards,
>>> shankar
>>>
>>>
>>>
Re: Apache Hive 2.3.4 - Issue with combination of Like operator &
newline (\n) character in data
Posted by Shankar Mane <ss...@gmail.com>.
Can anyone looking at this issue ?
On Sat, Jul 20, 2019 at 9:08 AM Shankar Mane <ss...@gmail.com> wrote:
> Have created jira at https://issues.apache.org/jira/browse/HIVE-22008
> <https://www.google.com/url?q=https://issues.apache.org/jira/browse/HIVE-22008&sa=D&source=hangouts&ust=1563538731290000&usg=AFQjCNHrfFVVopKL-7k-5dGBYICOVAHMDg>
>
> On Wed, 17 Jul 2019, 16:44 Shankar Mane, <ss...@gmail.com> wrote:
>
>> Hi All,
>>
>> I am facing some issues while using Like operator & newline (\n)
>> character. Below is the in details description :
>>
>> *-------------------------------------------------- Hive Queries
>> ------------------------------------------------------------*
>>
>> create table default.withdraw(
>> id string
>> ) stored as parquet;
>>
>>
>> insert into default.withdraw select 'withdraw\ncash';
>>
>>
>> *--1) result = success*
>>
>> hive> select * from default.withdraw where id like '%withdraw%';
>> OK
>> withdraw
>> cash
>> Time taken: 0.078 seconds, Fetched: 1 row(s)
>>
>>
>> *--2) **result = wrong*
>>
>> hive> select * from default.withdraw where id like '%withdraw%cash';
>> OK
>> Time taken: 0.066 seconds
>>
>>
>> *--3) **result = success*
>>
>> hive> select * from default.withdraw where id like '%cash%';
>> OK
>> withdraw
>> cash
>> Time taken: 0.086 seconds, Fetched: 1 row(s)
>>
>>
>>
>> *-------------------------------------------------- Presto Queries
>> -----------------------------------------------------*
>> FYI - Presto (v0.221) is using above table meta store. We tested above
>> queries on presto too.
>>
>> *--1) **result = **success*
>>
>> presto> select * from default.withdraw where id like '%withdraw%';
>> id
>> ----------
>> withdraw
>> cash
>> (1 row)
>>
>>
>> *--2) **result = **success*
>>
>> presto> select * from default.withdraw where id like '%withdraw%cash';
>> id
>> ----------
>> withdraw
>> cash
>> (1 row)
>>
>>
>> *--3) **result = **success*
>>
>> presto> select * from default.withdraw where id like '%cash%';
>> id
>> ----------
>> withdraw
>> cash
>> (1 row)
>>
>> *-------------------------------------------------- *
>> *-------------------------------------------------- *
>>
>> Please help here in case i am missing anything.
>>
>> regards,
>> shankar
>>
>>
>>
Re: Apache Hive 2.3.4 - Issue with combination of Like operator &
newline (\n) character in data
Posted by Shankar Mane <ss...@gmail.com>.
Have created jira at https://issues.apache.org/jira/browse/HIVE-22008
<https://www.google.com/url?q=https://issues.apache.org/jira/browse/HIVE-22008&sa=D&source=hangouts&ust=1563538731290000&usg=AFQjCNHrfFVVopKL-7k-5dGBYICOVAHMDg>
On Wed, 17 Jul 2019, 16:44 Shankar Mane, <ss...@gmail.com> wrote:
> Hi All,
>
> I am facing some issues while using Like operator & newline (\n)
> character. Below is the in details description :
>
> *-------------------------------------------------- Hive Queries
> ------------------------------------------------------------*
>
> create table default.withdraw(
> id string
> ) stored as parquet;
>
>
> insert into default.withdraw select 'withdraw\ncash';
>
>
> *--1) result = success*
>
> hive> select * from default.withdraw where id like '%withdraw%';
> OK
> withdraw
> cash
> Time taken: 0.078 seconds, Fetched: 1 row(s)
>
>
> *--2) **result = wrong*
>
> hive> select * from default.withdraw where id like '%withdraw%cash';
> OK
> Time taken: 0.066 seconds
>
>
> *--3) **result = success*
>
> hive> select * from default.withdraw where id like '%cash%';
> OK
> withdraw
> cash
> Time taken: 0.086 seconds, Fetched: 1 row(s)
>
>
>
> *-------------------------------------------------- Presto Queries
> -----------------------------------------------------*
> FYI - Presto (v0.221) is using above table meta store. We tested above
> queries on presto too.
>
> *--1) **result = **success*
>
> presto> select * from default.withdraw where id like '%withdraw%';
> id
> ----------
> withdraw
> cash
> (1 row)
>
>
> *--2) **result = **success*
>
> presto> select * from default.withdraw where id like '%withdraw%cash';
> id
> ----------
> withdraw
> cash
> (1 row)
>
>
> *--3) **result = **success*
>
> presto> select * from default.withdraw where id like '%cash%';
> id
> ----------
> withdraw
> cash
> (1 row)
>
> *-------------------------------------------------- *
> *-------------------------------------------------- *
>
> Please help here in case i am missing anything.
>
> regards,
> shankar
>
>
>