Query AlloyDB Data in DataGrip

DataGrip is a database IDE that allows SQL developers to query, create, and manage databases. When paired with the CData JDBC Driver for AlloyDB, DataGrip can work with live AlloyDB data. This article shows how to establish a connection to AlloyDB data in DataGrip and use the table editor to load AlloyDB data.

Create a New Driver Definition for AlloyDB

The steps below describe how to create a new Data Source in DataGrip for AlloyDB.

1. In DataGrip, click File -> New > Project and name the project
2. In the Database Explorer, click the plus icon () and select Driver.
3. In the Driver tab:
   • Set Name to a user-friendly name (e.g. "CData AlloyDB Driver")
   • Set Driver Files to the appropriate JAR file. To add the file, click the plus (), select "Add Files," navigate to the "lib" folder in the driver's installation directory and select the JAR file (e.g. cdata.jdbc.alloydb.jar).
   • Set Class to cdata.jdbc.alloydb.AlloyDB.jar
   Additionally, in the advanced tab you can change driver properties and some other settings like VM Options, VM environment, VM home path, DBMS, etc
   • For most cases, change the DBMS type to "Unknown" in Expert options to avoid native SQL Server queries (Transact-SQL), which might result in an invalid function error
4. Click "Apply" then "OK" to save the Connection

Configure a Connection to AlloyDB

1. Once the connection is saved, click the plus (), then "Data Source" then "CData AlloyDB Driver" to create a new AlloyDB Data Source.
2. In the new window, configure the connection to AlloyDB with a JDBC URL.

   Built-in Connection String Designer

   For assistance in constructing the JDBC URL, use the connection string designer built into the AlloyDB JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

   java -jar cdata.jdbc.alloydb.jar

   Fill in the connection properties and copy the connection string to the clipboard.

   The following connection properties are usually required in order to connect to AlloyDB.

   • Server: The host name or IP of the server hosting the AlloyDB database.
   • User: The user which will be used to authenticate with the AlloyDB server.
   • Password: The password which will be used to authenticate with the AlloyDB server.

   You can also optionally set the following:

   • Database: The database to connect to when connecting to the AlloyDB Server. If this is not set, the user's default database will be used.
   • Port: The port of the server hosting the AlloyDB database. This property is set to 5432 by default.

   Authenticating with Standard Authentication

   Standard authentication (using the user/password combination supplied earlier) is the default form of authentication.

   No further action is required to leverage Standard Authentication to connect.

   Authenticating with pg_hba.conf Auth Schemes

   There are additional methods of authentication available which must be enabled in the pg_hba.conf file on the AlloyDB server.

   Find instructions about authentication setup on the AlloyDB Server here.

   Authenticating with MD5 Authentication

   This authentication method must be enabled by setting the auth-method in the pg_hba.conf file to md5.

   Authenticating with SASL Authentication

   This authentication method must be enabled by setting the auth-method in the pg_hba.conf file to scram-sha-256.

   Authenticating with Kerberos

   The authentication with Kerberos is initiated by AlloyDB Server when the ∏ is trying to connect to it. You should set up Kerberos on the AlloyDB Server to activate this authentication method. Once you have Kerberos authentication set up on the AlloyDB Server, see the Kerberos section of the help documentation for details on how to authenticate with Kerberos.

3. Set URL to the connection string, e.g., jdbc:alloydb:User=alloydb;Password=admin;Database=alloydb;Server=127.0.0.1;Port=5432
4. Click "Apply" and "OK" to save the connection string

At this point, you will see the data source in the Data Explorer.

Execute SQL Queries Against AlloyDB

To browse through the AlloyDB entities (available as tables) accessible through the JDBC Driver, expand the Data Source.

To execute queries, right click on any table and select "New" -> "Query Console."

In the Console, write the SQL query you wish to execute. For example: SELECT ShipName, ShipCity FROM Orders WHERE ShipCountry = 'USA'

Download a free, 30-day trial of the CData JDBC Driver for AlloyDB and start working with your live AlloyDB data in DataGrip. Reach out to our Support Team if you have any questions.