Skip to main content
Last updated on

Workday Live Data Query

Workday Live Data Query (LDQ) provides real-time SQL access to Workday's core business objects without ETL or data replication. Workday distributes an LDQ JDBC driver that you install on a Unity Catalog JDBC connection to query Workday from Databricks compute. For the concepts, requirements, authentication methods, and limitations that apply to every JDBC connection, see JDBC connection.

Authentication uses the JDBC connection's Static Credential method. The LDQ driver performs Workday's JWT bearer grant itself, using driver options that you set on the connection: wd.authn.accessTokenEndpoint, wd.authn.clientId, wd.authn.isu, and wd.authn.privateKey. Databricks passes these options through to the driver as-is, so Unity Catalog does not perform an OAuth token exchange. Don't use the OAuth Machine-to-Machine method for this connection.

note

Confirm your compute meets the requirements in JDBC connection before you begin. For serverless SQL warehouses, you must also enable the Enable networking for isolated workloads in Serverless SQL Warehouses preview.

note

Access is read-only. Depending on your organization's Workday subscription, you might need to take additional steps to enable Live Data Query for your tenant. See Get Started with Workday Live Data Query.

Before you begin​

In addition to the requirements on JDBC connection:

Workday requirements:

  • Generate the Private Key for Workday Live Data Query as part of the Live Data Query prerequisites.
  • Enable Live Data Query for your Workday tenant, and set up an Integration System User (ISU) authorized to read the objects you want to query. See Set Up Live Data Query ISU Authentication in Workday in Workday for the full procedure. Register the ISU's API client with these settings:
    • Jwt Bearer Grant grant type
    • An x509 public key
    • Include Workday Owned Scope selected After registering, note the client's Client ID and token endpoint.

Databricks requirements:

  • A Unity Catalog volume where you can upload the driver JAR, and a secret scope to hold the private key. See Secret management.

Step 1: Install the Workday LDQ JDBC driver​

  1. Download the current Workday Live Data Query JDBC driver from Workday's Live Data Query Downloads.
  2. Upload the driver JAR to a Unity Catalog volume, following Step 1 of JDBC connection.

The driver auto-registers through its META-INF/services/java.sql.Driver entry, so you don't set a driver class. The class name differs by driver version, so don't hardcode it.

Step 2: Create the connection​

Store the ISU's private key as a secret first so it isn't exposed on the connection. See Secret management. Then create a JDBC connection that points at your Workday host and carries the LDQ driver's authentication options.

Create the connection with SQL. The Catalog Explorer wizard isn't used for this connection: its Additional Options store values as literal strings, so it can't express the secret(...) reference that wd.authn.privateKey requires.

Run the following command in a notebook or the Databricks SQL query editor:

SQL
CREATE CONNECTION workday_ldq TYPE JDBC
ENVIRONMENT (
  java_dependencies '["/Volumes/<catalog>/<schema>/<volume>/<workday-ldq-driver>.jar"]'
)
OPTIONS (
  url 'jdbc:workday://<workday-data-service-host>:443',
  `wd.authn.accessTokenEndpoint` 'https://<workday-host>/ccx/oauth2/<tenant>/token',
  `wd.authn.clientId` '<client-id>',
  `wd.authn.isu` '<isu-name>',
  `wd.authn.privateKey` secret('<secret-scope>','<secret-key>'),
  externalOptionsAllowList 'dbtable,query,partitionColumn,lowerBound,upperBound,numPartitions,fetchSize'
);
  • url: your Workday Data Service host on port 443.
  • wd.authn.*: the LDQ driver's JWT-bearer-grant inputs, and the only authentication this connection uses. Keep them as static connection options so they're hidden from querying users. Don't add them to externalOptionsAllowList.
  • wd.authn.accessTokenEndpoint: the token endpoint shown for your registered API client, in the form https://<workday-host>/ccx/oauth2/<tenant>/token.
  • wd.authn.privateKey: reference the ISU's RSA private key as a secret rather than pasting it inline.
  • externalOptionsAllowList: the Spark data source options querying users can set. Only options in this list can be passed at query time, so it includes fetchSize — users must be able to lower it if they hit the driver's 400 MiB memory limit. See JDBC connection limitations.

The generic user and password OPTIONS aren't needed for Workday LDQ — authentication is entirely through wd.authn.*. You can include them for readability, but they're ignored.

Step 3: Grant access and query​

Grant the USE CONNECTION privilege, then query Workday with the remote_query SQL function or the Spark JDBC data source. The query runs in Workday's SQL dialect, and objects use three-level names (for example, workday_core.public.<table>).

SQL
GRANT USE CONNECTION ON CONNECTION workday_ldq TO `<user-or-group>`;

Discover which objects the ISU can read, and an object's columns, from the driver's JDBC metadata (system.jdbc.tables, system.jdbc.columns):

SQL
-- Objects the ISU is authorized to read
SELECT * FROM remote_query('workday_ldq', query => 'SELECT * FROM system.jdbc.tables');

-- Columns of one object (filter the returned results in Databricks)
SELECT * FROM remote_query('workday_ldq', query => 'SELECT * FROM system.jdbc.columns')
WHERE table_schem = 'public' AND table_name = 'supervisory_organization';
important

Don't SELECT * from a Workday object. Many objects include multi-valued (ARRAY/STRUCT) columns that the JDBC type mapping can't return, which fails with UNRECOGNIZED_SQL_TYPE. Project the scalar columns you need instead.

Then query the scalar columns you need:

SQL
SELECT * FROM remote_query('workday_ldq',
  query => 'SELECT id, workday_id, display_id FROM workday_core.public.supervisory_organization LIMIT 100');

Limitations​

The limitations on JDBC connection apply. In addition, Workday LDQ access is read-only, and Workday enforces its own guardrails. For the current Workday limitations, see Get Started with Workday Live Data Query.

Additional resources​

On this page