JDBC development

JDBC endpoint

Connect to JDBC with Beeline

1. Download the Spark release package.

2. Decompress the Spark release package.

3. Set the SPARK_HOME environment variable to the directory of the decompressed package.

   export SPARK_HOME=/path/to/spark/;

4. Configure the $SPARK_HOME/conf/beeline.conf file.

   • endpoint: The JDBC endpoint for LDPS.

   • user: The username for accessing LindormTable.

   • password: The password for the specified user.

   • shareResource: Specifies whether to share Spark resources across multiple interactive sessions. Defaults to true.

   Note

   To configure more job parameters, add them to the beeline.conf file. Each line can contain only one job parameter in the key=value format. For example: spark.dynamicAllocation.minExecutors=3.

5. Run the $SPARK_HOME/bin/beeline command, and then enter SQL statements in the interactive session.

   LDPS can access various data sources. For more information, see Usage notes.

   For example, after you activate the Hive Metastore for Lindorm, you can use the following statements to create a table and perform read and write operations. For more information about how to activate the service, see Activate Hive Metastore.

   CREATE TABLE test (id INT, name STRING);
   INSERT INTO test VALUES (0, 'Jay'), (1, 'Edison');
   SELECT id, name FROM test;

Connect to JDBC with Java

1. Add the JDBC dependency to your project. The following example uses Maven.

   <dependency>
       <groupId>org.apache.hive</groupId>
       <artifactId>hive-jdbc</artifactId>
       <version>2.3.8</version>
   </dependency>

2. Use the following Java code to connect to the JDBC service:

   import java.sql.*;
   public class App {
       public static void main(String[] args) throws Exception {
           Class.forName("org.apache.hive.jdbc.HiveDriver");
           String endpoint = "jdbc:hive2://123.234.XX.XX:10009/;?token=bisdfjis-f7dc-fdsa-9qwe-dasdfhhv8****";
           String user = "";
           String password = "";
           Connection con = DriverManager.getConnection(endpoint, user, password);
           Statement stmt = con.createStatement();
           String sql = "SELECT * FROM test";
           ResultSet res = stmt.executeQuery(sql);
           while (res.next()) {
               System.out.println(res.getString(1));
           }
       }
   }

3. Optional: To configure additional job parameters, add them to the JDBC endpoint string, as shown in the following example:

   String endpoint = "jdbc:hive2://123.234.XX.XX:10009/;?token=bisdfjis-f7dc-fdsa-9qwe-dasdfhhv8****;spark.dynamicAllocation.minExecutors=3;spark.sql.adaptive.enabled=false";

Connect to JDBC with Python

1. Download the Spark release package .

2. Decompress the Spark release package.

3. Configure path variables.

   1. Set the SPARK_HOME environment variable.

      export SPARK_HOME=/path/to/dir/;

   2. Set the CLASSPATH environment variable.

      export CLASSPATH=$CLASSPATH:$SPARK_HOME/jars/*;

   3. Install JayDeBeApi.

      pip install JayDeBeApi

4. Use the following Python code to connect to the JDBC service:

   import jaydebeapi
   driver = 'org.apache.hive.jdbc.HiveDriver'
   endpoint = 'jdbc:hive2://123.234.XX.XX:10009/;?token=bisdfjis-f7dc-fdsa-9qwe-dasdfhhv8****'
   jarPath = '/path/to/sparkhome/jars/hive-jdbc-****.jar'
   user = '****'
   password = '****'
   conn=jaydebeapi.connect(driver, endpoint, [user, password], [jarPath])
   cursor = conn.cursor()
   cursor.execute("select 1")
   results = cursor.fetchall()
   cursor.close()
   conn.close()

5. Optional: To configure additional job parameters, add them to the JDBC endpoint string, as shown in the following example:

   endpoint = "jdbc:hive2://123.234.XX.XX:10009/;?token=bisdfjis-f7dc-fdsa-9qwe-dasdfhhv8****;spark.dynamicAllocation.minExecutors=3;spark.sql.adaptive.enabled=false"