JDBC

Available in VPC

It describes how to use the JDBC provided by Data Query.

Preparations

Java 8 or later must be installed in the environment where you will be using JDBC.

How to use

It describes how to use Data Query JDBC.

1. Download

Download the JDBC executable file for Data Query from the following link:

dataquery-jdbc-101-437.zip

2.1. Integrations using DB Tool

You can register the Data Query JDBC Driver with DB Tool to issue queries to Data Query. This guide will walk you through connecting to Data Query via JetBrains DataGrip.

2.1.1. Register Data Query JDBC Driver

To register the Data Query JDBC driver, follow these steps:

1. In DataGrip, click File > New > Driver in order.
2. Click the Drivers tab, then click the [+] button.
3. In the Name field, enter NCP Data Query.
4. Click the [+] button in the Driver Files area.
5. Select Custom Jars, and then click to select the downloaded driver file in the search window.
6. Select io.trino.jdbc.TrinoDriver from the list of Class entries.
7. To complete the registration, click the [Apply] button or the [OK] button.

2.1.2. Register Data Query Data Source

To register Data Query Data Source, follow these steps:

1. In DataGrip, click File > New > Data Source > NCP Data Query in order.
2. In the User field, enter the user AccessKey information.
3. In the Password field, enter the user SecretKey information.
4. In the URL field, enter the Data Query JDBC URL information.
   • For more information, see Data Query Service JDBC URLs by Region.
5. Click the [Test Connection] button to verify that you have successfully connected.
6. To complete the registration, click the [Apply] button or the [OK] button.

2.1.3. Available options

Detailed connection settings can be set in the [Advanced] tab of the registered Data Source.

Example: connect JDBC to reuse the same request query within the last 1 hour as the query result to submit.

Data Query Service JDBC URLs by Region
Currently, only the KR Region is available, with more Regions to be provided later.

2.1.4. Submit queries and view results

Depending on how you use the tool, connect to the added Data Source to submit a query and view the results.

Example:

2.2. Integrate with JDBC in Java projects

You can add the Data Query JDBC Driver as a Library in your Java Project to issue queries to Data Query. This guide describes how to connect to Data Query through JetBrains IntelliJ IDEA.

2.2.1. Register Data Query JDBC Driver library

Add the downloaded JDBC Driver to the project settings to register the Data Query JDBC driver library. To register the Data Query JDBC Driver library, follow these steps:

1. Click IntelliJ > Project Settings > Modules in order.
2. Click the [Dependencies] tab, then click the [+] button.
3. Select JARs or Directories, and click to select the downloaded driver file in the search window.
4. To complete the registration, click the [Apply] button or the [OK] button.

2.2.2. Submit queries and view results

Write the code to submit the query and view the results.

Example:

package org.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class DataQueryJdbcDemo {
	public static void main(String[] args) {
		final String accesskey = "{my-access-key}";
		final String secretKey = "{my-secret-key}";
		final String jdbcUrl = "{dataquery-jdbc-url}";

		String query = """
			SELECT movieId, COUNT(*) AS ratingCount
			FROM public_data.the_movies_dataset.ratings_small
			GROUP BY movieId
			ORDER BY ratingCount DESC
			LIMIT 10
			""";

		try {
			// Create a connection
			/* You can select between using properties and using url parameters. */
			// 1. properties
			Properties properties = new Properties();
			properties.setProperty("user", accesskey);
			properties.setProperty("password", secretKey);
			properties.setProperty("SSL", "true");
			Connection connection = DriverManager.getConnection(jdbcUrl, properties);
			// 2. URL parameters
			// String url = String.format("%s?user=%s&password=%s&SSL=true", jdbcUrl, accesskey, secretKey);
			// Connection connection = DriverManager.getConnection(url);

			// Create a statement
			Statement stmt = connection.createStatement();
			// Execute the SELECT query
			ResultSet resultSet = stmt.executeQuery(query);
			// Process the query results
			long index = 0L;
			while (resultSet.next()) {
				System.out.println(
					String.format("[%d] %s %d", index, resultSet.getString(1), resultSet.getInt(2))
				);
				index++;
			}
			// Close the result set
			resultSet.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

// Result examples
[0] 356 341
[1] 296 324
[2] 318 311
[3] 593 304
[4] 260 291
[5] 480 274
[6] 2571 259
[7] 1 247
[8] 527 244
[9] 589 237

2.2.3. Available options

You can use the same as described in 2.1.3. Available options. Depending on your connection method, use it in the following form:

• Properties method: add to Properties in the form of properties.setProperty("옵션명", 옵션값).
• Url method: add to Url strings in the form of "{dataquery-jdbc-url}?옵션명=옵션값".