SYMPTOM
After enabling the following two properties, "View Raw Table Data" or other querying activities from Design Center failed.
connection.overrideUdfSchema.enabled = true
connection.overrideUdfSchema.schema = atscale_udf
# some non default database for UDF
ERROR MESSAGE
The error message in the Design Center UI and Engine log is:
com.atscale.engine.connection.pool.ConnectionUnavailableException: ConnectionGroup [default.ed56145f-fa9a-4582-8fd0-c9225b91dc38.con1] SubGroup [subgroup:a9ed3d79-e297-4e01-9ecf-bca40c8485e7] could not connect to host [node1.ubuntu.localdomain] on port [10000] as Subject [None]..
(The last recorded connect error message was: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: HiveAccessControlException Permission denied: user [atscale] does not have [DROP] privilege on [hll_aggregate])
The Hadoop distribution is HDP (2.6.x), and Ranger is enabled.
Ranger Hive Policy was already created for the AtScale database and atscale_udf database.
ROOT CAUSE
Missing a Hive Ranger policy for temporary functions.
When the SQL Engine is Hive, AtScale uses temporary functions (CREATE TEMPORARY FUNCTION ...), and Ranger requires another Hive policy for temporary functions (UDF).
WORKAROUND
An example of Hive Ranger policy is below:
NOTE: With the currently released Ranger, "database" needs to be "*".
In "udf", specify below UDF names, or use "*":
hll_aggregate, hll_aggregate_estimate, hll_aggregate_merge, hll_estimate, quantile_estimate quantile_sketch, quantile_sketch_merge, quantilefromsketch
RESOLUTION
No solution is available from AtScale to avoid creating another Hive Policy, as this is a Ranger limitation.