ODBC & JDBC Drivers for Foundry Datasets

Overview

The ODBC and JDBC drivers for Foundry Datasets present a read-only SQL-based interface for accessing datasets from client applications (such as BI tools and ETL tools). Users can explore projects and datasets in Foundry and execute SQL queries to access tabular data. The drivers leverage Foundry SQL Server on the server side to process and execute SQL queries.

JDBC or ODBC?

We recommend the use of JDBC over ODBC in cases where the client application supports both protocols. The JDBC driver is easier to install and configure, and data loading is more performant.

Foundry SQL Server

The drivers rely on Foundry SQL Server to process and execute SQL queries against Foundry datasets. Review the Foundry SQL Server architecture documentation to learn more about the architecture and how to improve query performance.

System requirements

ODBC

JDBC

Setup guide

ODBC

Part 1. Install the ODBC driver

Run the ODBC driver installer following the instructions found in Downloads: ODBC Driver.

Part 2. Configure a Data Source Name (DSN) configuration

To use the driver within client applications, first configure a Data Source Name (DSN) configuration for your Foundry environment:

1. From the Start menu, search for ODBC and open the ODBC Data Sources tool. Choose the 64-bit version.
2. On either the User DSN or System DSN tab, click Add... .
3. Select FoundrySqlDriver from the list of drivers.
4. Enter the following required parameters:
   1. Data Source Name: A name for the data source on your machine.
   2. Server: The URL of your Foundry environment; for example https://<SUBDOMAIN>.palantirfoundry.com .
   3. Token: A security token generated from the Settings page inside Foundry. See the User-generated tokens documentation for instructions on how to obtain a token. For additional authentication options, review the Use OAuth to authenticate guide.
5. Click Test... to verify a successful connection, then click OK to save the DSN configuration.

See Configuration parameters for additional configuration parameters and instructions on building a connection URL.

Part 3. Configure the DSN within client applications

Now that you created a DSN, you can reference it from client applications that support ODBC sources. Refer to the client application documentation on ODBC sources. Below are some set-up guides for applications commonly used with Foundry:

• Excel
• Power BI
• Microsoft Report Builder

(Optional) Part 4: Execute a SQL query

If supported by the client application, test a SQL query that returns rows from a Foundry dataset:

SELECT * FROM "/Path/To/Dataset" LIMIT 10

The client application may instead allow you to browse projects and select datasets to access data.

JDBC

Part 1: Install the JDBC driver

Download the JDBC driver (.jar file) which can be found on the Downloads: JDBC Driver page. Once downloaded, place the file into the appropriate location as specified in the client application's documentation for configuring JDBC connections.

Part 2: Construct the JDBC connection string

The JDBC connection string format is:

jdbc:foundrysql://<FOUNDRY_HOSTNAME>?Password=<TOKEN>

• FOUNDRY_HOSTNAME is the hostname of your Foundry environment (such as subdomain.palantirfoundry.com).
• TOKEN is a security token generated from the Settings page inside Foundry. See the User-generated tokens documentation for instructions on how to obtain a token. For additional authentication options, review the Use OAuth to authenticate guide.

Optional parameters may be specified by appending &OptionalParam=<VALUE> to the connection string. See Configuration parameters for a full list of available parameters.

If the JDBC client requires the driver class to be specified explicitly, specify com.palantir.foundry.sql.jdbc.FoundryJdbcDriver.

(Optional) Part 3: Execute a SQL query

If supported by the client application, test a SQL query that returns rows from a Foundry dataset:

SELECT * FROM "/Path/To/Dataset" LIMIT 10

The client application may instead allow you to browse projects and select datasets to access data.

Reference

Configuration parameters

The available configuration parameters are the same across ODBC and JDBC. Each driver can be configured in one of two ways: using a connection string within the client application, or configuring outside the client application:

Parameter reference

Type handling

The following table shows how Foundry types are mapped to ODBC and JDBC types.

SQL dialects

The following table outlines some of the SQL syntax and features of available SQL dialects (see Dialect parameter in Configuration parameters).

Usage guides

Use SQL to query Foundry datasets

Datasets can be referenced in SQL queries by path or by RID. The SQL syntax depends on the dialect set for the connection (see Configuration parameters).

SPARK dialect

-- Basic SELECT
SELECT * FROM `/Path/To/Dataset`

-- Filtering with a WHERE clause
SELECT * FROM `/Path/To/Dataset`
WHERE years < 13 AND category = 'Z';

-- Using JOIN
SELECT *
FROM `/Path/To/Dataset_A` a
JOIN `/Path/To/Dataset_B` b
    ON a.id = b.fk_id;

ODBC & ANSI dialect

-- Basic SELECT
SELECT * FROM "/Path/To/Dataset";

-- Filtering with a WHERE clause
SELECT * FROM "/Path/To/Dataset"
WHERE years < 13 AND category = 'Z';

-- Using JOIN
SELECT *
FROM "/Path/To/Dataset_A" a
JOIN "/Path/To/Dataset_B" b
    ON a.id = b.fk_id;

See Guides: Identifying a dataset's RID or filepath for further instructions on using dataset identifiers.

Use OAuth to authenticate

Instead of manually generating an authentication token tied to a single Foundry account, the ODBC & JDBC drivers support OAuth 2.0 flows for additional authentication options:

• Individual users: The driver opens a login prompt in your browser for you to authenticate to Foundry.
• Service users: The driver connects to Foundry as the service user attached to a third-party application registered in Foundry.
• External applications (for developers): A third-party application integrates with Foundry’s OAuth system to fetch tokens on behalf of its users.

When possible, we recommend using these OAuth-based options over token generation as they are more secure and allow shared use of embedded connection strings without sharing an individual user’s token.

Individual users (ODBC on Windows only)

The ODBC driver supports an automatic OAuth login and authorization flow when running on Windows. Follow the steps below to set up this flow:

1. (Completed by a Foundry administrator) Register a third-party application in Foundry, specifying the following configuration options:
   1. Client type: Public client is recommended in most cases; Confidential client is supported if your use case requires it.
   2. Authorization grant types: Enable the Authorization code grant and set the redirect URL to http://127.0.0.1/foundrydriver/oauthredirect.
   3. Ensure the application is Registered and Enabled.
   4. Copy the application’s Client ID from the application details screen and share with any users who are setting up an ODBC connection on their machines.
2. After receiving the Client ID from your Foundry administrator, set the following ODBC connection parameters:
   1. AuthMethod = OauthFlow
   2. OauthClientId = <YOUR_CLIENT_ID>
      Alternatively, the Client ID can be set in an application's username field instead of OauthClientId, to allow configuring credentials in-app. AuthMethod must still be set.
3. (Optional) If you are configuring these settings in the Windows ODBC Administrator app, you can choose the Test option to trigger the login prompt and verify that login is working.

The next time you use the driver in a client application, you will be prompted to log in to Foundry in your browser. After that, you will not need to log in every time you use the driver, though you may occasionally be prompted again.

Service users

For workflows not associated with individual users (such as refreshing a dashboard's data on a schedule), we recommend OAuth-based service users, leveraging third-party applications with the OAuth Client Credentials grant type. The driver authenticates to Foundry with a long-lived client ID/secret pair that is easier to manage than manually created service accounts with generated tokens.

Follow the steps below to set up OAuth for a service user:

1. (Completed by a Foundry administrator.) Register a third-party application in Foundry:
   1. Client type: Choose Confidential client.
   2. Authorization grant types: Enable the Client credentials grant, and be sure to store the Client ID and Client Secret pair in a secure location.
   3. Ensure the application is Registered and Enabled.
   4. Ensure that the generated service user has been granted access to any datasets that will be accessed via the ODBC or JDBC driver. The service user generated for the application is listed in the Client credentials grant details panel.
2. Set the following configuration parameters when configuring the driver:
   1. AuthMethod = ClientCredentials
   2. OauthClientId = <YOUR_CLIENT_ID>
   3. OauthClientSecret = <YOUR_CLIENT_SECRET>
      Alternatively, the Client ID and Client Secret can be set in an application's username and password fields instead of OauthClientId and OauthClientSecret, to allow configuring credentials in-app. AuthMethod must still be set.

The driver will now connect to Foundry as the OAuth app's service user.

External applications (for developers)

Application developers can integrate ODBC and JDBC drivers into an application that runs its own OAuth client to manage OAuth login flows with third-party applications. This option allows full control of the login flow, including redirecting the user to authentication with Foundry and handling the authorization response after the user has completed authentication and authorization. See the writing OAuth2 clients for Foundry documentation to fetch access tokens on behalf of a Foundry user.

Once your application has obtained an access token on behalf of a user, it can be passed to the driver via the standard password property:

• ODBC Pwd / JDBC Password = <ACCESS_TOKEN_OBTAINED_FROM_TOKEN_ENDPOINT>

Enable logging in the ODBC driver

If you need to enable driver logging to troubleshoot an issue, follow the steps below. Note that these steps may require administrator permissions.

1. Create a folder in which to save the logs, such as My Documents\Foundry Driver logs.
2. Open the Windows ODBC Data Sources tool. You can find this by searching "ODBC" in the Windows search bar. Choose the 64-bit version.
3. Open the System DSN tab. Select the FoundrySql source, and then select Configure. Note that it does not matter which Foundry data source you select, as the logging settings are applied to all data sources that use the Foundry driver.
4. Select Logging Options on the configuration window.
5. Set the log level to DEBUG. Set the log path to the folder you created earlier.
6. Select OK to save the settings.

Restart the client application and perform the action want to troubleshoot. The logs should appear in the folder you selected. If you require support from Palantir or another support team, you can compress this folder into a zip file to share.

After troubleshooting is complete, return to the ODBC Data Sources tool and disable logging by setting the log level to OFF. This step is recommended to improve performance.

Enable logging in the JDBC driver

The JDBC driver will discover any SLF4J logger that your Java application provides on the classpath. Specifically, your application should provide implementations of the classes org.slf4j.impl.StaticLoggerBinder and org.slf4j.impl.StaticMDCBinder. You can use a default implementation by adding slf4j-simple (version 1.X) as a project dependency.

If you have not configured an SLF4J logger, you will see the following messages printed when the driver is first loaded:

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
SLF4J: Failed to load class "org.slf4j.impl.StaticMDCBinder".
SLF4J: Defaulting to no-operation MDCAdapter implementation.
SLF4J: See http://www.slf4j.org/codes.html#no_static_mdc_binder for further details.

Configure the JDBC driver using a foundry.ini config file

The JDBC driver can be configured via a config file in addition to using the connection string. To do this, create a file called foundry.ini in the same directory as where the JDBC .jar file is located.

The .ini file is divided into two sections: low-priority and high-priority. Properties specified in the low-priority section have a lower priority than connection string properties. This means that if the same property is specified in the low-priority section and in the connection string, the connection value will be used. In contrast, properties specified in the high-priority section will take precedence over connection properties. This can be useful in situations where reports are published to a server from a development machine, and the server properties need to take precedence over the development properties.

Example foundry.ini file:

[high-priority]
proxyHost=myproxy.abc
proxyPort=1234

[low-priority]
branch=production-branch

Use the JDBC driver with Java 16 and above

The JDBC driver supports a maximum Java version of Java 15 by default. To use the driver with Java versions 16 and above, set the --add-opens Java runtime option within your application to java.base/java.nio=org.apache.arrow.memory.core,ALL-UNNAMED.

Example: Java command

Copied!
1
java --add-opens=java.base/java.nio=org.apache.arrow.memory.core,ALL-UNNAMED -jar your_application.jar

Example: Environment variable

In some cases, it is more convenient to specify the option in the _JAVA_OPTIONS environment variable, which is detected and applied automatically by some Java environments. On Unix-based systems this can be configured using the export command:

export _JAVA_OPTIONS="--add-opens=java.base/java.nio=org.apache.arrow.memory.core,ALL-UNNAMED"