CODEWITHSUNDEEP
syntaxhive
user2205916
user2205916

Reputation: 3456

Hive syntax: purpose of curly braces and dollar sign

I'm reading over some Hive scripts from another team in my company and having trouble understanding a specific part of it. The part in question is:where dt='${product_dt}', which can be found on on the third line from the bottom of the code chunk below.

I've never seen this syntax before nor am I able to find anything via Google search (probably because I don't know the correct search terms to use). Any insight into what that where row filter step is doing would be appreciated.

set hive.security.authorization.enabled=false;
add jar /opt/mobiletl/prod_workflow_dir/lib/hiveudf_hash.jar;
create temporary function hash_string as 'HashString';

drop table 00_truthset_product_email_uid_pid;
create table 00_truthset_product_email_uid_pid as
select distinct email,        
       concat_ws('|', hash_string(lower(email), "SHA-1"),
                      hash_string(lower(email), "MD5"),
                      hash_string(upper(email), "SHA-1"),
                      hash_string(upper(email), "MD5")) as hashed_email,
       uid, address_id, confidencescore
from product.prod_vintages
where dt='${product_dt}'
      and email is not null and email != ''
      and address_id is not null and address_id != '';

I tried set product_dt = 2014-12;, but it doesn't seem to work:

hive> SELECT dt FROM enabilink.prod_vintages GROUP BY dt LIMIT 10;
. . .
dt
2014-12
2015-01
2015-02
2015-03
2015-05
2015-07
2015-10
2016-01
2016-02
2016-03

hive> set product_dt = 2014-12;

hive> SELECT email FROM product.prod_vintages WHERE dt='${product_dt}';
. . .
Total MapReduce CPU Time Spent: 2 seconds 570 msec
OK
email
Time taken: 25.801 seconds

Upvotes: 0

Views: 1929

Answers (1)

hlagos
hlagos

Reputation: 7947

those are variables set in Hive. if you have set the variables before the query (in the same session), Hive will replace it with the specified value

for example

set product_dt=03-11-2012

Edit Make sure that you are removing the spaces in your dt field (use trim UDF). Also, set the variable without spaces.

Upvotes: 1

Related Questions