Using JdbcStorageHandler to query RDBMS

Using the JdbcStorageHandler, you can connect Apache Hive to a MySQL, PostgreSQL, Oracle, DB2, Derby or another Hive data source. You can then create an external table to represent the data, and query the table.

This task assumes you are a CDP Private Cloud Base user. You can create an external table that uses the JdbcStorageHandler to connect to and read a local JDBC data source.

Load data into a supported SQL database on a cluster node or review the existing data in your database.

Create an external table using the JdbcStorageHandler and table properties that specify the minimum information which includes database type, driver, database connection string, user name and password for querying Hive, table name, and number of active connections to Hive.

Creating an external JDBC table to map a table in a remote MySQL database.

CREATE EXTERNAL TABLE mytable_example1(
  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_EXAMPLE1",
  "hive.sql.dbcp.maxActive" = "1"
);

Creating an external JDBC table to map a table in another Hive cluster.

create external table mytable2(
colm1 string,
colm2 int,
colm3 double 
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
"hive.sql.database.type" = "HIVE",
"hive.sql.jdbc.driver" = "org.apache.hive.jdbc.HiveDriver",
"hive.sal.jdbc.url" = "jdbc:hive2://localhost/sample",
"hive.sql.dbcp.username" = "hive"
"hive.sql.dbcp.password" = "hive",
"hive.sql.table" = "MYTABLE_EXAMPLE2",
"hive.sql.dbcp.maxActive" = "1"
);

Query the external table.

SELECT * FROM mytable_jdbc WHERE col2 = 19;