Examples of creating secure external tables

You learn by example how to set up policies to secure HBase-, Kafka- and JDBC-based tables using the Ranger policy properties

Kafka-based table

CREATE EXTERNAL TABLE kafka_table_test (`timestamp` timestamp , `page` string,  `newPage` boolean, added int, deleted bigint, delta double) 
STORED BY 'org.apache.hadoop.hive.kafka.KafkaStorageHandler' 
TBLPROPERTIES ("kafka.topic" = "test-topic","kafka.bootstrap.servers" = "hostclustername:2181");

In this case, the URI sent for ranger Authentication is kafka://hostclustername:2181/test-topic/column_names.

The policy definition sets the kafka storage-type and storage Url bootstrap-server:port/kafka-topic.

JDBC-based table

CREATE EXTERNAL TABLE mytable_jdbc(col1 string, col2 int, col3 double) 
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' 
TBLPROPERTIES ("hive.sql.database.type" = "MYSQL", "hive.sql.jdbc.driver" = "com.mysql.jdbc.Driver", "hive.sql.jdbc.url" = "jdbc:mysql://localhost/sample", "hive.sql.dbcp.username" = "hive", "hive.sql.dbcp.password" = "hive", "hive.sql.table" = "MYTABLE");

In this case, the URI used for Ranger Authentication is jdbc:mysql://localhost/sample/MYTABLE.

The policy definition sets the com.mysql.jdbc.Driver storage-type and the storage Url jdbc:muysql://mysql-host:port/table-name: