JDBC (HiveServer2)

Owned by Juliane Wetzel
Last updated: Sept 01, 2022
4 min read

INFO

Datameer X contains the connector 'HiveServer2' which supports ZooKeeper. Therefore the Hive JDBC client has to be enabled instead of the Thrift client.

INFO

Note when using Hive 3: We are now converting all Datameer DATE columns to Hive TIMESTAMP and Hive DATE type data when exporting to HIVE 3 from the Datameer server time zone to UTC. And on import from Hive TIMESTAMP and Hive DATE back from UTC to Datameer server time zone. Never set the property "hive.parquet.timestamp.skip.conversion" to "TRUE". This would result in inconsistent data being stored in HIVE.


Configuring HiveServer2 as a JDBC Connection

To configure HiveServer2 as a JDBC connection:

  1. Click the "+" button and select "Connection" or right-click in the browser and select "Create New" → "Connection". The "New Connection" tab appears in the menu bar.
     
  2. Select "Hive Server2 1.2.1.x" from the drop-down and confirm with "Next". The type is displayed in the drop-down.
     
  3. Click on "Next". The 'Connection Details' tab opens.
     
  4. Enter the JDBC URL in 'Hive Server2 Connection'.
    INFO: To enable the Hive JDBC client, the JDBC URL must start with 'jdbc:hive2://'. 
     
  5. View the pre-selected transport mode.
    INFO: This property is not respected when connecting via the JDBC client. To configure the transport mode for Hive JDBC you have to adjust the connection URL.
     
  6. If needed, define a database name or pattern to limit the amount of listed databases during the import. 
     
  7. If needed, enter a path on the same filesystem as the Hive warehouse where exports will be written.
     
  8. If needed, enter custom properties.
    INFO: One key or value pair is allowed per line.
     
  9. Select "Import/Export" from the drop-down 'Connection usage'.
     
  10. Select "LDAP/ AD Authentication" as the authentication mode from the drop-down, activate "Provide credentials" and enter your username and password.
    INFO: It is possible but not recommended to put the username/ password into the 'hive_conf_List' of the URL.
    INFO: Kerberos Authentication is not recommended but still supported. Use the UI only to configure and do not use URL parameters for that.   
     
  11. Confirm with "Next". The 'Save' tab opens.
     
  12. If needed, enter a description, and confirm with "Next". The 'Save Connection' dialog opens.
     
  13. Select the folder to save the connection in and enter a name in 'Save as'. Confirm with "Save". The connection is saved. Configuring Hive Server2 as a JDBC connection is finished.
     

JDBC Connection URL Syntax

INFO

The HiveServer2 URL is a string with the following syntax:

jdbc:hive2://<host1>:<port1>,<host2>:<port2>/dbName;sess_var_list?hive_conf_list#hive_var_list

where

  • <host1>:<port1>,<host2>:<port2> is a server instance or a comma separated list of server instances to connect to (if dynamic service discovery is enabled); If empty, the embedded server will be used
  • dbName is the name of the initial database.
  • sess_var_list is a semicolon separated list of key=value pairs of session variables (e.g., user=foo;password=bar)
  • hive_conf_list is a semicolon separated list of key=value pairs of Hive configuration variables for this session
  • hive_var_list is a semicolon separated list of key=value pairs of Hive variables for this session

Special characters in sess_var_list, hive_conf_list, hive_var_list parameter values should be encoded with URL encoding, if needed.

For more information see: https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-ConnectionURLFormat.

Proxy User (Impersonation) Limitations for the JDBC Connection URL 

INFO

It is not allowed to use the URL parameter 'hive.server2.proxy.user' in a JDBC connection URL. Datameer X will take on this parameter depending on the configured secure mode.

Connection URL Parameters

Example ZooKeeper

  • serviceDiscoveryMode
  • zooKeeperNamespace
jdbc:hive2://pmaster.datameer.lan:2181,pnode-02.datameer.lan:2181,pnode-04.datameer.lan:2181/default;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2

Example SSL

jdbc:hive2://localhost:10000/mydb;ssl=true;sslTrustStore=/opt/cloudera/security/jks/jssecacerts;trustStorePassword=changeit

Example Transport Protocol

jdbc:hive2://localhost:10000/default;transportMode=http;httpPath=cliservice