Sidebar

HPL/SQL is included to Apache Hive since version 2.0

connections

Connections

HPL/SQL allows you to work with multiple databases in a single HPL/SQL script.

Configuring Connections

Connection information is stored in hplsql-site.xml configuration file. The default connection profile is defined by hplsql.conn.default option:

<property>
  <name>hplsql.conn.default</name>
  <value>hiveconn</value>
</property>

Predefined Connections

hplsql-site.xml contains predefined connections for some databases:

  • hiveconn - Embedded Hive JDBC Connection (not requiring a HiveServer)
<property>
  <name>hplsql.conn.hiveconn</name>
  <value>org.apache.hadoop.hive.jdbc.HiveDriver;jdbc:hive://</value>
</property>
  • hive2conn - HiveServer2 JDBC Connection
<property>
  <name>hplsql.conn.hive2conn</name>
  <value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://localhost:10000</value>
</property>
  • db2conn - IBM DB2 JDBC Connection
<property>
  <name>hplsql.conn.db2conn</name>
  <value>com.ibm.db2.jcc.DB2Driver;jdbc:db2://localhost:50001/dbname;user;pwd</value>
  <description>IBM DB2 connection</description>
</property>

When using DB2 JDBC driver make sure db2jcc4.jar is specified in CLASSPATH.

  • tdconn - Teradata JDBC Connection
<property>
 <name>hplsql.conn.tdconn</name>
 <value>
 com.teradata.jdbc.TeraDriver;jdbc:teradata://host/database=name,logmech=ldap;usr;pwd
 </value>
</property>

When using Teradata JDBC driver make sure teradata-jdbc4-xx.xx.xx.jarand tdgssconfig.jar (both .jars are required) are specified in CLASSPATH.

  • mysqlconn - MySQL JDBC Connection
<property>
  <name>hplsql.conn.mysqlconn</name>
  <value>com.mysql.jdbc.Driver;jdbc:mysql://localhost/test;user;password</value>
</property>

When using MySQL JDBC driver make sure mysql-connector-java-x.x.xx-bin.jar is specified in CLASSPATH.

You can modify these connection profiles and specify appropriate connection details. Set the default connection profile using the hplsql.conn.default option.

Defining a New Connection

HPL/SQL allows you to define any connection profile. Just create a new parameter named hplsql.conn.<your_connection_name> and specify the connection details:

<value>JDBC Driver;JDBC Connection String;User;Password</value>

For example, to create a new connection sales that points to the sales_db database in MySQL add the following section to your hplsql-site.xml file:

<property>
  <name>hplsql.conn.sales</name>
  <value>com.mysql.jdbc.Driver;jdbc:mysql://localhost/sales_db;paul;pwd</value>
</property>

Read your JDBC driver documentation how to properly specify the connection string and available options.

Once you configured the connections, read how to use them in HPL/SQL scripts.