Connect with the JDBC driver

You can use the Bigtable Java Database Connectivity (JDBC) driver to connect the following applications to Bigtable:

The Bigtable JDBC driver is a wrapper around the Bigtable client for Java that lets you run queries on your Bigtable data using GoogleSQL.

Limitations

The following limitations apply when you use the Bigtable JDBC driver:

  • GoogleSQL for Bigtable limitations apply. For more information, see Use cases.
  • In queries with parameters, changing a parameter type results in an error.
  • The driver doesn't support connection pooling. We recommend that you reuse a single connection for all queries.

Required roles

To get the permissions that you need to run queries using the Bigtable JDBC driver, ask your administrator to grant you the Bigtable Reader (roles/bigtable.reader) Identity and Access Management (IAM) role on your project. This read-only role is sufficient for applications that only query data.

For more information about granting roles, see Manage access to projects, folders, and organizations.

Before you begin

To complete the following example, install Java JDK version 8 or later and Apache Maven.

Connect from a Java application

To use the Bigtable JDBC driver to connect a Java application to Bigtable, complete the following steps:

  1. Declare the preview artifact repository by adding the following code to your Maven pom.xml file:

    <!-- pom.xml -->
<distributionManagement>
    <snapshotRepository>
        <id>artifact-registry</id>
        <url>artifactregistry://us-central1-maven.pkg.dev/cloud-bigtable-ecosystem/java-bigtable-jdbc</url>
    </snapshotRepository>
</distributionManagement>

<repositories>
    <repository>
        <id>artifact-registry</id>
        <url>artifactregistry://us-central1-maven.pkg.dev/cloud-bigtable-ecosystem/java-bigtable-jdbc</url>
        <releases>
            <enabled>false</enabled>
        </releases>
        <snapshots>
            <enabled>true</enabled>
        </snapshots>
    </repository>
</repositories>

<build>
    <extensions>
        <extension>
            <groupId>com.google.cloud.artifactregistry</groupId>
            <artifactId>artifactregistry-maven-wagon</artifactId>
            <version>2.2.0</version>
        </extension>
    </extensions>
</build>

  2. Add the Bigtable JDBC driver to your application. Declare the driver as a dependency by adding code that is similar to the following to the Maven pom.xml file of your project:

    <!-- pom.xml in the `dependencies` element -->
<dependency>
    <groupId>com.google.cloud</groupId>
    <artifactId>bigtable-jdbc</artifactId>
    <version>1.0-preview-SNAPSHOT</version>
</dependency>

  3. Load the driver class by including the following command in the startup sequence of your application:

    public static void main(String[] args) {
     // Step 1: Load the Bigtable JDBC driver class at runtime
     try {
         Class.forName("com.google.cloud.bigtable.jdbc.BigtableDriver");
         System.out.println("Bigtable JDBC Driver loaded successfully.");
     } catch (ClassNotFoundException e) {
         System.err.println("Could not load Bigtable JDBC Driver. Make sure the JAR is on the classpath.");
         throw new RuntimeException(e);
     }

  4. Build a Java project in Apache Maven.

  5. Create the connection URL:

    // Step 2: Set up the connection string
String projectId = "PROJECT_NAME";
String instanceId = "INSTANCE_NAME";
String appProfileId = "APP_PROFILE"; // Optional but recommended, can also be set using a java.util.Properties

String connStr = String.format("jdbc:bigtable:/projects/%s/instances/%s?app_profile_id=%s", projectId, instanceId, appProfileId);

// Step 3: Now you can establish a connection
     try (Connection connection = DriverManager.getConnection(connStr)) {
         System.out.println("Connection to Bigtable established: " + !connection.isClosed());
         // You can now execute queries using this connection object
     } catch (SQLException e) {
         System.err.println("Failed to connect to Bigtable.");
         throw new RuntimeException(e);
     }

    Replace the following:

    • PROJECT_NAME: the name of the project where your Bigtable instance is located
    • INSTANCE_NAME: the name of your Bigtable instance
    • Optional: APP_PROFILE: the name of the specific application profile that you want to use for the connection

  6. Run queries. For query examples, see the Sample queries section of this document.

  7. Process a result. For a sample result, see the Process a result section of this document.

Sample queries

You can query your Bigtable data using standard JDBC interfaces defined in the JDBC API. For more information about how to write Bigtable queries, see GoogleSQL for Bigtable overview.

One-time queries

Use statement objects to run one-time queries, as shown in the following example:

   try (Connection connection = DriverManager.getConnection(connStr)) {
     try (Statement statement = connection.createStatement()) {
       try (ResultSet rs = statement.executeQuery("SELECT _key, columnFamily['qualifier'] FROM myTable WHERE _key = 'first-row'")) {
         while (rs.next()) {
           // Process query results
         }
       }
     }
   } catch (SQLException e) {
     throw new RuntimeException(e);
   }

Queries with parameters

For queries that you run multiple times with different parameters, use a PreparedStatement for better efficiency, as shown in the following example:

try (Connection connection = DriverManager.getConnection(connStr)) {
  // Note that JDBC uses positional parameters, as opposed to Google SQL for Bigtable which uses named parameters.
  try (PreparedStatement statement = connection.prepareStatement("SELECT _key, columnFamily['qualifier'] FROM myTable WHERE _key = ?")) {
    // Reuse the prepared statement for multiple queries
    for (String param : new String[]{"row1", "row2"}) {
      statement.setString(1, param);
      try (ResultSet rs = statement.executeQuery()) {
        while (rs.next()) {
          // Process query results
        }
      }
    }
  }
} catch (SQLException e) {
  throw new RuntimeException(e);
}

Process a result

Bigtable returns query results as a ResultSet object, which is a standard JDBC interface that represents table data returned from a query. By default, queries return fields as bytes or map types unless you cast them to the appropriate type in your SQL statement or in a logical view.

The following example shows how to iterate through each row of data in the result set to access the data returned by the SQL query:

try (ResultSet rs = statement.executeQuery()) {
  while (rs.next()) {
    System.out.printf(rs.getString(0));
  }
}

Connect from a tool or a framework

To use the Bigtable JDBC driver to connect a tool, such as a business intelligence application, or an ETL framework to Bigtable, follow these steps:

  1. Download the Bigtable JDBC driver. For more information about how to install and configure the driver, see the applicable vendor documentation.
  2. Provide the JDBC connection string to configure a new connection within the tool's web interface.

Support

Cloud Customer Care provides support for JDBC drivers.

Pricing

You can download the Bigtable JDBC driver at no cost. However, you're charged for the Bigtable resources that your queries use.

Costs for using the Bigtable JDBC driver vary depending on your configuration:

  • The nodes in your Bigtable cluster process queries that are sent through the JDBC driver. You're billed for the number of nodes in your cluster. Running queries consumes CPU, and we recommend that you monitor your cluster's CPU utilization to ensure that it's provisioned appropriately.
  • You're billed for the amount of data stored in your Bigtable tables.
  • You're billed for any data that the JDBC driver transfers from Bigtable to your client application over the network.

What's next