Connect to Imply Polaris over JDBC

Java Database Connectivity (JDBC) is a Java application programming interface (API) for connecting and executing SQL queries on database management systems. You can use a JDBC connection to integrate Imply Polaris with your Java applications and business intelligence tools.

Prerequisites

To establish a connection to Polaris using JDBC, you need the following:

• The Avatica JDBC driver. We recommend using Avatica JDBC driver version 1.23.0 or later. Note that starting with Avatica 1.21.0, you may need to set the transparent_reconnection property to true if you notice intermittent query failures.
• An API key with the AccessQueries permission. See Authenticate with API keys to obtain an API key. Visit Permissions reference for more information on permissions.

Connect from Java applications

You can connect to Polaris from custom Java code.

The following steps are required to establish a JDBC connection with Polaris:

1. Define the JDBC connection string. The JDBC connection string uses the following syntax:

   jdbc:avatica:remote:url=https://PROJECT_ID.jdbc.REGION.CLOUD_PROVIDER.api.imply.io;transparent_reconnection=true

   In the connection string, replace the following:

   • REGION: The cloud region of your Polaris project.
   • CLOUD_PROVIDER: The cloud service provider for your Polaris infrastructure.
   • PROJECT_ID: The unique identifier for your Polaris project.

2. To authenticate your connection, define a password connection property and pass your API key as the property value.

The following example shows a Java program that connects to Polaris and issues a SQL query to the "Koalas to the Max" table. In the example, the environment variable POLARIS_API_KEY stores the API key value.

import java.sql.*;
import java.util.Properties;
public class JdbcPolaris {

    public static void main(String args[]) {

        // define the JDBC URL
        String url = "jdbc:avatica:remote:url=https://PROJECT_ID.jdbc.REGION.CLOUD_PROVIDER.api.imply.io";

        // get the API key from the environment variable
        String apikey = System.getenv("POLARIS_API_KEY");

        // define query directed to "Koalas to the Max" table
        String query = "SELECT continent, COUNT(*) AS counts FROM \"Koalas to the Max\" GROUP BY 1 ORDER BY counts DESC";

        // instantiate a Properties object and store the API key value in the "password" key
        Properties connectionProperties = new Properties();
        connectionProperties.setProperty("password", apikey);

        // establish a connection to the database
        try (Connection connection = DriverManager.getConnection(url, connectionProperties)) {
            try (

                // create a Statement object for sending SQL statements and execute query 
                final Statement statement = connection.createStatement();
                final ResultSet rs = statement.executeQuery(query);
            ) {
                // iterate over each row of results and print to stdout
                while (rs.next()) {
                    String location = rs.getString("continent");
                    int amount = rs.getInt("counts");
                    System.out.println(location + ":\t\t" + amount);
                }
            }
        } catch (Exception e) {
            System.out.println(e.toString());
        }
    }
}

Learn more

See the following topics for more information:

• Query API to query tables in Polaris.
• Connect from business intelligence applications to connect Polaris to a business intelligence tool.