Skip to main content

 

ThoughtSpot

Use the JDBC Driver

Overview

Explains what the feature is or what its benefits are to the user or customer.

Feature

To use the JDBC driver, include the JDBC library in your path, and provide the connection information.

You need this information to configure the JDBC driver:
  • Driver name: com.simba.client.core.jdbc4.JDBC4Driver
  • Server IP address: The ThoughtSpot appliance URL or IP address.
  • Simba port: The simba port, which is 12345 by default. The simba port can be found by going to http://<server-ip>:2101/service?name=simba_server
  • Database name: The ThoughtSpot cluster name to connect to.
  • Database username: The name of a ThoughtSpot user with administrator permissions.
  • Database password: The user password.
The installation packages for clients are hosted both in the Help Center and on the ThoughtSpot Analytic Search Appliance. If you download them directly from the appliance, you'll need the IP address and login credentials for the admin user on the appliance.
  1. Log in to the local machine where you want to install the JDBC driver.
  2. To obtain the JDBC driver, do one of:
    • In a browser, log in to ThoughtSpot and click on the Help Center icon Help Icon, scroll to the bottom and click the Get Clients button. Then click the link to download the JDBC driver.
    • Open a command prompt on your local machine and copy the file from the directory on the ThoughtSpot Analytic Search Appliance which contains the JDBC client:
      scp admin@<appliance_IP>:/usr/local/scaligent/toolchain/local/simba/jdbc/SimbaJDBCClient4.jar
  3. Move the driver to the desired directory on your local machine.
  4. Add the JDBC driver to your Java class path on the local machine.
  5. Now write your Java application code.
    Using JDBC with ThoughtSpot is the same as using any other JDBC driver with any other database. You need to provide the connection information, create a connection, execute statements, and close the connection.

    Specify each of the nodes in the cluster in the connection string, as shown. This enables high availability for JDBC connections. To find out the nodes in the cluster, you can run the command tscli node ls from the Linux shell on the ThoughtSpot Analytic Search Appliance.

    The format for the connection is:

    jdbc:simba://<node1>:12345;SERVERS=<node2>:12345,<node3>:12345
    [,<node4>:12345...];LoginTimeout=5;Database=<database>[;Schema=<schema>]

    For example:

    jdbc:simba://192.168.2.248:12345;SERVERS=192.168.2.249:12345,
    192.168.2.247:12345;Database=test;Schema=falcon_default_schema
This InsertData.java example shows how to use ThoughtSpot with JDBC. This is an example of a reference JDBC application:
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertData {

  // JDBC class to use.
  private static final String DB_DRIVER = "com.simba.client.core.JDBCDriver";
  // jdbc_example should be an existing database.

  private static final String DB_CONNECTION = "jdbc:simba://192.168.2.129:12345;
     SERVERS=192.168.2.249:12345,192.168.2.247:12345;
     Database=jdbc_example";

  private static final String TABLE_NAME = "jdbc_example";
  private static final String DB_USER = "<username>";
  private static final String DB_PASSWORD = "<password>";

  // Assuming everything in local directory use:
  //   java -cp .:SimbaJDBCClient4.jar InsertData
  public static void main(String[] argv) {

    try {
      insertRecordsIntoTable();
    } 
    catch (SQLException e) {
      System.out.println(e.getMessage());
    }
  }

  /**
   * Insert some records using batch updates.
   *  Assumes a table exists:  CREATE TABLE "jdbc_example" ( "text" varchar(10) );
   */
  private static void insertRecordsIntoTable() throws SQLException {

    System.out.println("Inserting records.");
    Connection dbConnection = getDBConnection();
    PreparedStatement preparedStatement = null;
    String insertTableSQL = "INSERT INTO falcon_default_schema.jdbc_example (text) VALUES (?)";
 
    try {
      preparedStatement = dbConnection.prepareStatement(insertTableSQL);

      // Create multiple statements and add to a batch update.
      for (int cnt = 1; cnt <= 10; cnt++) {
        preparedStatement.setString(1, "some string " + cnt);
        preparedStatement.addBatch();
        System.out.println("Record " + cnt + " was added to the batch!");
      }
      preparedStatement.executeBatch();  // For large numbers of records, recommend doing sets of executeBatch commands.
      System.out.println("Records committed");
 
    }
    catch (SQLException sqle) {
      sqle.printStackTrace();
    }
    finally {
 
      if (preparedStatement != null) {
        preparedStatement.close();
      }
      if (dbConnection != null) {
        dbConnection.close();
      }
    }
  }

  /** Create a connection to the database. */
  private static Connection getDBConnection() {
    Connection dbConnection = null;
    try {
      Class.forName(DB_DRIVER);
    }
    catch (ClassNotFoundException e) {
      System.out.println(e.getMessage());
    }
    try {
      dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,DB_PASSWORD);
      return dbConnection;
    }
    catch (SQLException sqle) {
      System.out.println(sqle.getMessage());
    }
 
    return dbConnection;
  }
 
}
  • Was this article helpful?