hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Markovitz, Dudu" <dmarkov...@paypal.com>
Subject RE: Any way in hive to have functionality like SQL Server collation on Case sensitivity
Date Wed, 13 Jul 2016 17:50:14 GMT
Hi

I’m personally not aware of other methods to achieve case insensitivity comparison but to
use lower() / upper()

Dudu

From: Mahender Sarangam [mailto:Mahender.BigData@outlook.com]
Sent: Wednesday, July 13, 2016 12:56 AM
To: user@hive.apache.org
Subject: Re: Any way in hive to have functionality like SQL Server collation on Case sensitivity


Thanks Dudu,

I would like to know dealing with case in-sensitivity in other project. is every one converting
to toLower() or toUpper() in the Joins ? . Is there any setting applied at Hive Server level
which gets reflected in all the queries ?



/MS

On 5/25/2016 9:05 AM, Markovitz, Dudu wrote:
It will not be suitable for JOIN operation since it will cause a Cartesian product.
Any chosen solution should determine a single representation for any given string.

Dudu

From: Mich Talebzadeh [mailto:mich.talebzadeh@gmail.com]
Sent: Wednesday, May 25, 2016 1:31 AM
To: user <user@hive.apache.org><mailto:user@hive.apache.org>
Subject: Re: Any way in hive to have functionality like SQL Server collation on Case sensitivity

I would rather go for something like compare() <http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36271.1572/html/blocks/X14054.htm>
that allows one to directly compare two character strings based on alternate collation rules.

Hive does not have it. This is from SAP ASE

1> select compare ("aaa","bbb")
2> go
 -----------
          -1
(1 row affected)
1> select compare ("aaa","Aaa")
2> go
 -----------
           1
(1 row affected)

1> select compare ("aaa","AAA")
2> go
 -----------
           1

•  The compare function returns the following values, based on the collation rules that
you chose:

·         1 – indicates that char_expression1 or uchar_expression1 is greater than char_expression2
or uchar_expression2.

·         0 – indicates that char_expression1 or uchar_expression1 is equal to char_expression2
or uchar_expression2.

·         -1 – indicates that char_expression1 or uchar_expression1 is less than char_expression2
or uchar expression2.

hive> select compare("aaa", "bbb");
FAILED: SemanticException [Error 10011]: Line 1:7 Invalid function 'compare'


HTH




Dr Mich Talebzadeh



LinkedIn  https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw



http://talebzadehmich.wordpress.com<http://talebzadehmich.wordpress.com/>



On 24 May 2016 at 21:15, mahender bigdata <Mahender.BigData@outlook.com<mailto:Mahender.BigData@outlook.com>>
wrote:
Hi,

We would like to have feature in Hive where string comparison should ignore case sensitivity
while joining on String Columns in hive. This feature helps us in reducing code of calling
Upper or Lower function on Join columns. If it is already there, please let me know settings
to enable this feature.

/MS


Mime
View raw message