0

I'm rather new to Hive so be gentle... I have a large table in Hive, and one of the columns has dollar signs in it's values, for example "123$AB". I want to filter the table using this column:

select * from foo where bar = "123$AB"

Seemed simple but when I executed the query, I got a prompt asking for input: "Please specify parameters for this query: AB ________"

A bit of research turned up this: How to set variables in HIVE scripts

I guess Hive is seeing the dollar sign and thinking I want to input a variable or something. OK, so we need to escape the dollar sign.

I tried the following:

select * from foo where bar = "123\$AB"
select * from foo where bar = "123\\$AB"
select * from foo where bar = '123$AB'
select * from foo where bar = '123\$AB'
select * from foo where bar = '123\\$AB'

but I keep getting the same prompt. It just won't recognize '$' as a character. How do I escape the '$'?

For now I am doing the following:

select * from foo where substr(bar,1,3) = '123'

which works fine for now but I still want to know how to escape special characters. Thanks!

EDIT: Workaround available - see comments!

EDIT 2: To the left of the query editor is a "Settings" tab, and at the bottom of the settings tab is "OPTIONS: Enable parameterization". I didn't realize what this was until now. I should have unchecked this, which would have solved my problem. However, the problem still exists; if there was a situation where I wanted to use a parameter variable, as well as a character string containing a dollar sign.

Community
  • 1
  • 1
winampman
  • 484
  • 5
  • 15
  • I wonder if two backslashes are needed. Sometimes that helps. – Gordon Linoff Mar 16 '16 at 02:26
  • @GordonLinoff thanks for the suggestion but I tried one, two, three, and four back and forward slashes and it still didn't work :( – winampman Mar 16 '16 at 02:34
  • It may not be satisfying but you can use regular expressions: `foo rlike '^123[$]AB$'`. – Gordon Linoff Mar 16 '16 at 02:41
  • Could not reproduce your issue with Hive CLI nor with Beeline (V1.1.0-cdh5.5.2) - which version are you using? – Samson Scharfrichter Mar 16 '16 at 17:52
  • 1
    Another workaround could be `=concat('123$','AB')` so that the dollar sign is not followed by any alphanum character, and should not reasonably be mistaken for a parameter to be replaced... – Samson Scharfrichter Mar 16 '16 at 17:55
  • @SamsonScharfrichter I am on Hive 1.1.0-cdh5.5.0. Perhaps updating to 5.5.2 will fix the issue... And thanks for the concat suggestion, it worked! – winampman Mar 16 '16 at 20:27
  • Don't think such a minor upgrade would make a difference, might be an obscure config flag that is not set the same way in your cluster and on ours. Anyway, could you edit your question so that it shows "*[Workaround available - see comments]"* or sthg similar? – Samson Scharfrichter Mar 17 '16 at 16:07
  • @SamsonScharfrichter original post has been edited! – winampman Mar 17 '16 at 20:29

2 Answers2

0

you can try using back tilde (`) instead of double quotes, the one below Esc key.

select * from foo where bar = `123$AB`;
Vrushank Doshi
  • 2,428
  • 5
  • 20
  • 34
0

I guess \$\ instead of $ does the job

Ankit Gorakhpuriya
  • 11
  • 2