Create parameterized view in Impala

user_8867951 0
Viewed 1325

My goal is to create a parameterized view in Impala so users can easily change values in a query. If I run below query, for example, in HUE, is possible to introduce a value.

SELECT * FROM customers WHERE customer_id = ${id}

But I would like to create a view as follows, that when you run it, it asks you for the value you want to search. But this way is not working:

CREATE VIEW test AS SELECT * FROM customers WHERE customer_id = ${id}

Someone know if it is possible?

Many thanks

cloudera impala hue hive view
2 Answers

When you creating a view, it takes the actual variable's value.

Two workarounds exist:

  1. Create a real table where you will store/update the parameter.

CREATE VIEW test AS SELECT * FROM customers JOIN id_table ON customer_id = id_tableid

  1. Pass a parameter into the view with the help of the user-defined function(UDF). Probably you will need two UDFs set and get. Set UDF will write UDF on HDFS and Get UDF will read the variable from HDFS.

Two above mentioned workarounds work but not ideal. My suggestion is to use Hive for parametrized view creation. You can create a GenericUDF via which you can access hive configuration and read the variable and perform filtration. You can't use it for Impala. 

SELECT Generic_UDF(array(customer_id)) FROM customers

GenericUDFs has method configure you can use it to read the hive variable:

public void configure(MapredContext mapredContext) {
  String name = mapredContext.getJobConf().get("name");
}
user_86448470

You could do the opposite, e.g. parameterize the query on the view instead

user_731170
Related