Saturday, March 10, 2018

Substitute variables in Hive

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> set hivevar:zipcode=42031;
No rows affected (0.007 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select fname,lname from employees where zipcode='${hivevar:zipcode}';
+--------+-----------+--+
| fname  |   lname   |
+--------+-----------+--+
| Eliza  | Weller    |
| Russ   | Jennings  |
+--------+-----------+--+
2 rows selected (22.079 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> set hivevar:zipcode=95066;
No rows affected (0.011 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select fname,lname from employees where zipcode='${hivevar:zipcode}';
+--------------+-------------+--+
|    fname     |    lname    |
+--------------+-------------+--+
| Anna         | Atkins      |
| Austin       | Dickson     |
| Alex         | Magee       |
| Alan         | Millner     |
| Ashley       | Quarles     |


[donghua@cdh-vm ~]$ beeline -u jdbc:hive2://cdh-vm.dbaglobe.com:10000 -n donghua --hivevar zipcode="42031"

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> set hivevar:zipcode;
+------------------------+--+
|          set           |
+------------------------+--+
| hivevar:zipcode=42031  |
+------------------------+--+
1 row selected (0.022 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select fname,lname from employees where zipcode='${hivevar:zipcode}';
+--------+-----------+--+
| fname  |   lname   |
+--------+-----------+--+
| Eliza  | Weller    |
| Russ   | Jennings  |
+--------+-----------+--+
2 rows selected (20.247 seconds)