Saturday, March 10, 2018

Substitute variables in Impala

set var:xx and set hivevar:xx are equivalent in impala-shell

[cdh-vm.dbaglobe.com:21000] > set var:zipcode=42031;
Variable ZIPCODE set to 42031
[cdh-vm.dbaglobe.com:21000] > select fname,lname from employees where zipcode='${var:zipcode}';
Query: select fname,lname from employees where zipcode='42031'
Query submitted at: 2018-03-10 08:27:09 (Coordinator: http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=7540c4bdaae3d532:c213eb6900000000
+-------+----------+
| fname | lname    |
+-------+----------+
| Russ  | Jennings |
| Eliza | Weller   |
+-------+----------+
Fetched 2 row(s) in 6.60s
[cdh-vm.dbaglobe.com:21000] > set hivevar:zipcode=42031;
Variable ZIPCODE set to 42031
[cdh-vm.dbaglobe.com:21000] > select fname,lname from employees where zipcode='${hivevar:zipcode}';
Query: select fname,lname from employees where zipcode='42031'
Query submitted at: 2018-03-10 08:27:31 (Coordinator: http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=444489b56fdff58e:d52cc25800000000
+-------+----------+
| fname | lname    |
+-------+----------+
| Eliza | Weller   |
| Russ  | Jennings |
+-------+----------+
Fetched 2 row(s) in 0.17s


[donghua@cdh-vm ~]$ impala-shell --var=zipcode="42031" 
Starting Impala Shell without Kerberos authentication
Connected to cdh-vm.dbaglobe.com:21000
Server version: impalad version 2.11.0-cdh5.14.0 RELEASE (build d68206561bce6b26762d62c01a78e6cd27aa7690)
***********************************************************************************
Welcome to the Impala shell.
(Impala Shell v2.11.0-cdh5.14.0 (d682065) built on Sat Jan  6 13:27:16 PST 2018)

Run the PROFILE command after a query has finished to see a comprehensive summary
of all the performance and diagnostic information that Impala gathered for that
query. Be warned, it can be very long!
***********************************************************************************

[cdh-vm.dbaglobe.com:21000] > select ${var:zipcode};
Query: select 42031
Query submitted at: 2018-03-10 08:48:06 (Coordinator: http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=94434f0933a88c9c:15cfcf3d00000000
+-------+
| 42031 |
+-------+
| 42031 |
+-------+
Fetched 1 row(s) in 0.04s
[cdh-vm.dbaglobe.com:21000] > select ${hivevar:zipcode};
Query: select 42031
Query submitted at: 2018-03-10 08:48:17 (Coordinator: http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=9b4849b05dddb406:9da4309e00000000
+-------+
| 42031 |
+-------+
| 42031 |
+-------+
Fetched 1 row(s) in 0.04s