0

I have a shell script that I use to parse a string variable into Hive, in order to filter my observations. I provide both the script and the hive code below.

In the following script I have a variable which has a string value and I try to parse it into hive, the example below:

Shell Script:

name1='"Maria Nash"' *(I use a single quote first and then a double)*

hive --hiveconf name=${name1} -f t2.hql

Hive code (t2.hql)

create table db.mytable as

SELECT *

FROM db.employees

WHERE emp_name='${hivevar:name}';

Conclusion

To be accurate, the final table is created but it does not contain any observation. The employees table contains observations which has emp_name "Maria Nash" though.

I think that I might not parse the string correctly from shell or I do not follow the correct syntax on how I should handle the parsed variable in the hive query.

I would appreciate your help!

Improve this question
5
  • 1
    Why are you using single+ double quotes. Double quotes is enough. they will be removed when passed to Hive and in the Hive you added single quotes, this is correct. Or your emp_name contains double quotes? Commented Oct 31, 2018 at 11:21
  • @leftjoin Please check again my example. I changed it. I replaced the "Maria" with a string like "Maria Nash". It might be different. Commented Oct 31, 2018 at 11:31
  • 2
    quotes are processed by shell but when you are using the quotes inside quoted expression they are part of string value, you can use for example name1='Maria Nash' then hive --hiveconf name="${name1}" -f t2.hql, double quotes are important around parameter expansion, the issue is using name=${name1} without quotes Commented Oct 31, 2018 at 11:45
  • @NahuelFouilleul That's the correct answer. Thank you! Commented Oct 31, 2018 at 11:52
  • 1
    Add this as the first line in the Hive script: ` ! echo emp_name='${hivevar:name}'` - space+! is a shell command executed from Hive. This will prints your parameter Commented Oct 31, 2018 at 11:54

2 Answers 2

Reset to default
0

you are passing variable in hiveconf namespace but in the sql script are using hivevar, you should also use hiveconf:

WHERE emp_name=${hiveconf:name} --hiveconf, not hivevar
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

-1

Use of the CLI is deprecated

you can use beeline from a shell script

it should look something like

beeline << EOF

!connect jdbc:hive2://host:port/db username password

select * 
from db.employees
where emp_name = "${1}"

EOF

assuming that $1 is the input from the script.

This is an example of how to do it rather than a production implementation. Generally,

  1. Kerberos would be enabled so username and password wouldn't be there and a valid token would be available
  2. Validate the input parameters.

Given that you can do it in a single line

beeline -u jdbc:hive2://hostname:10000  -f {full Path to Script} --hivevar {variable}={value}
Improve this answer

5 Comments

This is a SQL injection attack waiting to happen. You are not passing a value to a query; you are dynamically constructing a query from a variable, which could contain an arbitrary fragment of SQL.
changed it but still this generally depends on how you pass the parameters into the script
The problem is where emp_name = "${1}". Suppose $1 is 1; drop table db.employees. The query seen by beeline is now select * from db.employees where emp_name = 1; drop table db.employees, not select * from db.employees where emp_name = '1; drop table db.employees'.
but that is true of any parameterised script without any field validation, i do understand what you mean but it does seem a little obtuse. you could have improved my answer by adding in field validation.
The idea is that you don't construct the query in bash. I don't know what the capabilities of beeline are, but pretend you could call it like this: beeline 'select * from db.employees where emp_name = %s' "$1". Now you are not constructing the query; you tell beeline to take the value of $1 and insert it into the query where %s is, but safely so that you aren't mixing code and data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.