JDBC Client Connections

Java Database Connectivity (JDBC) is an API standard that allows Java client applications to connect to a relational database and issue SQL queries using a standard interface. This section explains how to connect to AtScale from JDBC-compliant client applications.

About JCBD client connections

To applications that send SQL queries, AtScale uses the same protocols and drivers as a remote PostgreSQL instance.

Connecting to AtScale from a Java client is the same as connecting to PostgreSQL, except you connect to the AtScale engine. The JDBC connection URL points to the AtScale catalog database in the data warehouse: jdbc:postgresql://<host>:<port>/atscale_catalogs.

When issuing a SQL query to AtScale, the SQL table name is the name of the model, and the column names are the model attribute's unique names. Below is an example. Note the backticks that are used to escape table and column names containing spaces.

SELECT \`Internet Sales\`.Order Month,  
  SUM(Internet Sales.orderquantity) AS
\`Order Quantity\`  
FROM \`Sales Insights\`.\`Internet Sales\`  
GROUP BY \`Internet Sales\`.\`Order Month\`

PGWire impersonation

You can optionally enable impersonation for PGWire JDBC queries. When enabled, the JDBC connection string includes the impersonated user with the atscale.impersonate_user flag:

jdbc:postgresql://<engine-host>:15432/<catalog>?options=-c%20atscale.impersonate_user=<impersonated_user>

In the string above, the port is the PGWire default port (15432), and the database is the catalog. The %20 represents the space after -c, though AtScale also accepts the string without the space. If you'd rather skip the encoding, it would look like this: options=-catscale.impersonate_user=<impersonated_user>

Prerequisites

Before enabling impersonation for PGWire JDBC queries, ensure the following requirements are met:

• The impersonated user must exist in Keycloak. If they do not, queries will error out with a 28000 code.
• The connecting user account (usually the service account) requires the impersonation_user role in Keycloak. Without this, queries will error out with a 42501 code. This error is expected on the first query, rather than at connect time.

Enable impersonation

When using impersonation, AtScale recommends omitting the credentials from the URL. You can do this in two ways:

• Configure impersonation for the life of the connection. For this method, when you open the connection, you can pass all required attributes to the Postgres JDBC driver as a Java Properties object:

  Properties props = new Properties();
  props.setProperty("user", "<service_account>");
  props.setProperty("password", "***");
  props.setProperty("options", "-c atscale.impersonate_user=<impersonated_user>");
  Connection conn = DriverManager.getConnection("jdbc:postgresql://<engine-host>:15432/<catalog>", props);

  With this method, be aware that the flag is fixed for the life of the connection, so it only supports opening a connection per end user.

• Configure impersonation using a connection pool. For this method, AtScale recommends using the SET PostgreSQL command:

  SET SESSION AUTHORIZATION '<impersonated_user>'    -- at checkout
  RESET SESSION AUTHORIZATION                        -- when it goes back to the pool

  With the SET command, the same connection gets reused. The subject changes per borrower, and no connection ever runs as whoever had it last.