How to query Apache Hive data source using Federation Server through JDBC driver

To configure a federation to access Apache Hive data source through JDBC wrapper, you need to provide information about the data sources and objects that you want to access. Then create wrapper, server, user mapping and nickname about the tables on remote Apache Hive data source.

Before you begin

Check the driver FOhive.jar in the path $/INSTANCE_HOME/sqllib/federation/jdbc/lib.

1. Enable Federation server and restart Db2.

# db2 update dbm cfg using federated YES

# db2stop force

# db2start

2. Test the connection to the Apache hive data source and verify the service is started correctly.

telnet <hive_ip> <port>
If the connection is successful, you receive the following similar output from the command.

# telnet 9.30.231.148 10000
Trying 9.30.231.148...
Connected to 9.30.231.148.
If the connection fails, you will receive an error, please check the hive service status.

3. Create wrapper, server, user mapping, nickname, and query the nickname. 

# connect to testdb

# create wrapper "wrapper1" library 'libdb2rcjdbc.so' options(db2_fenced 'y')

# CREATE SERVER jdbc1 TYPE HIVE WRAPPER "wrapper1" OPTIONS(DRIVER_CLASS 'com.ibm.fluidquery.jdbc.hive.HiveDriver', URL 'jdbc:ibm:hive://fiches1.fyre.ibm.com:10000;DatabaseName=default', DRIVER_PACKAGE '/home/db2inst1/sqllib/federation/jdbc/lib/FOhive.jar')

# create user mapping for user server SERVER1 options (REMOTE_AUTHID 'hive', REMOTE_PASSWORD 'hadoop')
# create nickname nk1 for server1."test1"

# select * from nk1

col_1

--------------------------

1970-11-23-12.12.12.000000

1999-12-31-12.12.12.000000

  2 record(s) selected.