Campaign: Databricks queries intermittently very slow
This article describes how to mitigate the timeout on long workflow queries using Databricks FDA.
Description description
A customer has been using a Databricks FDA for workflow queries and their queries are intermittently very slow and failing due to 1 hour timeout limit (a result of OAuth token expiry after 1 hour). Workflow logs showed messages:
ODB-240000 ODBC error: [ Simba] [ Hardy] (115) Connection failed with error: Unauthorized/Forbidden error response returned, but no token expired message received SQLState: 08S01
and
ODB-240000 ODBC error: Authentication token has expired. The user must authenticate again. SQLState: 08001
Resolution resolution
The expiry itself is not controlled by Campaign workflow settings or serverConf.xml configuration file. In this setup ACC connects to Databricks through the Simba ODBC driver using OAuth on nmsaccountName. The long-lived credential/app secret may be valid until 2031, but the access token issued for an ODBC connection can still have a much shorter lifetime, commonly 60 minutes.
Mitigation options:
- Databricks admin can review the OAuth app/integration used by Adobe Campaign and increase the access-token TTL, for example by updating the Databricks OAuth token access policy such as access_token_ttl_in_minutes. This should be done by the customer/Databricks admin and should follow their security policy, since longer-lived access tokens increase exposure if leaked.
- After changing the token policy, allow propagation time on the Databricks side and re-test with a fresh ACC/ODBC connection. Existing open connections may continue using the previous token issued before the policy change.
- Operationally, split or optimize the ACC workflow queries so each individual DBX SQL statement completes well below the token lifetime. This is the safer and preferred mitigation, because it also addresses the intermittent slow/queued DBX execution instead of only extending the failure window.
- On the Databricks side, check SQL warehouse query history for the slow runs and tune the warehouse/query path: queueing time, concurrency, scaling/max clusters, cold starts, query plan, table statistics/layout, and data volume for the predicates/joins.
- If the customer requires DBX SQL statements that can legitimately run longer than the OAuth access-token lifetime, they should confirm with Databricks/Simba whether the OAuth mode used by the ODBC driver supports token refresh for long-running/open statements in their configuration. If not, increasing token TTL or using another supported authentication mode may be required.
So the practical short-term mitigation is to increase the Databricks OAuth access-token TTL for the ACC integration, while the long-term fix is to reduce the DBX query runtime/queueing so workflows do not depend on tokens surviving very long-running SQL calls.