Prerequisites Limitations Parameters Storing credentials Set up the connector Running queries Data type support Performance Additional resources

Amazon Athena Workday Live Data Query connector

With the Amazon Athena connector for Workday Live Data Query, you can run SQL queries on the current data in your Workday tenant, without copying or moving that data. This connector can be registered with AWS Glue Data Catalog as a federated catalog. It supports data access controls defined in Lake Formation at the catalog, database, table, column, and row levels. This connector uses Glue connections to centralize configuration properties in AWS Glue. AWS manages the connector for you, so you don't need to deploy an AWS Lambda function in your account.

For more information about Workday Live Data Query, see Connecting to Workday Live Data Query in the AWS Glue User Guide. For information about federating Workday data to the AWS Glue Data Catalog through Workday Data Connect, see Federate to Workday Data Connect in the AWS Lake Formation Developer Guide.

Prerequisites

Before you set up the connector, make sure that you have the following.

A Workday tenant with Workday Data Cloud, and Live Data Query available for your tenant. Your subscription might require extra enablement.
An Integration System User (ISU), and an API client registered for the ISU with the JWT Bearer Grant client grant type. The domain permissions of the ISU determine which Workday tables and columns you can query. For more information, see Set Up Live Data Query ISU Authentication in Workday in the Workday documentation.
The following information from your Workday administrator.
- Workday Data Service host – The host name of the Workday service that runs Live Data Query queries.
- Token endpoint – The Workday OAuth 2.0 token endpoint, in the format https://host/ccx/oauth2/tenant/token.
- Integration System User – The ISU user name.
- Client ID – The client ID of the API client registered for the ISU.
- Private key – The RSA private key, in PEM format, that matches the x509 certificate registered for the API client.
An Amazon S3 bucket in the same AWS Region as the connector, to use as a spill location for data that exceeds the memory limits of the connector.
The AWS CLI, installed and configured. For more information, see Installing or updating to the latest version of the AWS CLI in the AWS Command Line Interface User Guide.
An IAM principal that is a Lake Formation data lake administrator or that has the CREATE_CATALOG permission on the AWS Glue Data Catalog, and that has permissions to create AWS Glue connections and catalogs, AWS Secrets Manager secrets, and IAM roles.

Limitations

Write DDL and DML operations are not supported. The connector is read-only.
DDL statements such as SHOW TABLES are not supported on the federated catalog. To list tables and columns, query information_schema or browse the catalog in the Athena query editor.
Passthrough queries are not supported.
The Athena console doesn't offer Workday Live Data Query as a data source. Create the connection and the federated catalog with the AWS CLI, or with the AWS Glue and Lake Formation APIs, as described in Set up the connector.
Workday Live Data Query connections that you create in AWS Glue Studio are for AWS Glue ETL jobs. If you want to use the connection with Athena, you can update the connection with the AWS CLI to add support for Athena.
The connector supports only ISU authentication. Workday User As Self (UAS) authentication isn't supported.
Queries run against live data in your Workday tenant, so they typically take longer than queries on data stored in Amazon S3.
Workday applies its own guardrails, such as query run time, concurrent queries per tenant, and memory per query. These guardrails apply to queries that come from Athena. For current limits, see Get Started with Workday Live Data Query in the Workday documentation.

Parameters

You configure the connector by using a Glue connection of type WORKDAYLDQ.

Glue connection properties

Use the following command to get the schema for the connection type. This schema contains the properties that you can use to control your connection.


aws glue describe-connection-type --connection-type WORKDAYLDQ

The following table describes the connection properties.

Property	Required	Description
`HOST`	Yes	Your Workday Data Service host name.
`PORT`	Yes	The port number. Use `443`.
`ROLE_ARN`	Yes	The ARN of the IAM role that AWS Glue and Lake Formation use to retrieve the secret and access the spill location.
`ENFORCE_SSL`	No	Whether to enforce SSL. The default is `false`. Connections to Workday on port `443` use TLS whether or not you set this property.
`JDBC_PARAMS`	No	Additional Workday driver parameters, in the format `key1=value1;key2=value2`.

Athena properties

Specify the following properties in the AthenaProperties object of the Glue connection.

Property	Required	Description
`MANAGED_CONNECTION`	Yes	Set to `true` so that AWS manages the connector for Athena. You can't create a federated catalog from the connection without this property.
`spill_bucket`	Yes	The Amazon S3 bucket for data that exceeds the memory limits of the connector. The bucket must be in the same AWS Region as the connection.
`spill_prefix`	No	The prefix in `spill_bucket` for spilled data. We recommend that you configure an Amazon S3 storage lifecycle to delete old spills.
`spill_kms_key_id`	No	The AWS KMS key ID to use to encrypt spilled data. By default, spilled data is encrypted with AES-GCM and a randomly generated key.
`disable_spill_encryption`	No	When set to `true`, disables spill encryption. The default is `false`.
`default_scale`	No	The default scale for numeric values, which is the number of digits to the right of the decimal point.

Storing credentials

The connector uses custom authentication and reads Workday OAuth 2.0 credentials from an AWS Secrets Manager secret. The connector signs a JSON Web Token (JWT) for the ISU with your private key and exchanges it for an access token at the Workday token endpoint.

The secret must contain the following keys.

wd.authn.isu – The ISU user name. The connector uses this value as the JWT subject.
wd.authn.clientId – The client ID of the API client registered for the ISU.
wd.authn.accessTokenEndpoint – The Workday OAuth 2.0 token endpoint URL.
wd.authn.privateKey – The PEM private key that the connector uses to sign the JWT.

Note

The key names are reserved. Enter them exactly. The private key must keep its line breaks. If you create the secret as JSON, encode each line break as \n.

Set up the connector

To create a connection for Athena with Workday Live Data Query as a data source, you can use the AWS CLI. The following steps create an AWS Secrets Manager secret, an IAM role, and a Glue connection, register the connection with Lake Formation, and create a federated catalog that you query in Athena. Create all resources in the same AWS Region.

Create an AWS Secrets Manager secret

Create a file named secret.json that contains your Workday OAuth 2.0 credentials.


{
  "wd.authn.isu": "isu",
  "wd.authn.clientId": "clientId",
  "wd.authn.accessTokenEndpoint": "https://host/ccx/oauth2/tenant/token",
  "wd.authn.privateKey": "-----BEGIN PRIVATE KEY-----\nkey\n-----END PRIVATE KEY-----"
}

Create the secret with the following command.


aws secretsmanager create-secret --region region --name name --secret-string file://secret.json

Create an IAM role for the connector

Create an IAM role for the connector. AWS Glue uses the role to retrieve the secret and validate the connection. Lake Formation uses the role when you register the connection. The connector uses the role at query time to read the secret and write to the spill location.

Create a file named trust-policy.json that allows AWS Glue and Lake Formation to assume the role.


{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "glue.amazonaws.com",
          "lakeformation.amazonaws.com"
        ]
      },
      "Action": "sts:AssumeRole",
      "Condition": {
        "StringEquals": {
          "aws:SourceAccount": "account"
        }
      }
    }
  ]
}

Create a file named permissions-policy.json that grants the role access to the secret, the Glue connection, Lake Formation, the managed connector, and the spill bucket.


{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Secret",
      "Effect": "Allow",
      "Action": "secretsmanager:GetSecretValue",
      "Resource": "arn:aws:secretsmanager:us-east-1:123456789012:secret:secretName-AbCdEf"
    },
    {
      "Sid": "GlueConnection",
      "Effect": "Allow",
      "Action": [
        "glue:GetConnection",
        "glue:GetConnections"
      ],
      "Resource": "*"
    },
    {
      "Sid": "LakeFormationDataAccess",
      "Effect": "Allow",
      "Action": "lakeformation:GetDataAccess",
      "Resource": "*"
    },
    {
      "Sid": "ManagedConnector",
      "Effect": "Allow",
      "Action": "glue:ManagedConnector",
      "Resource": "*"
    },
    {
      "Sid": "SpillBucket",
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:PutObject",
        "s3:DeleteObject",
        "s3:ListBucket",
        "s3:GetBucketLocation"
      ],
      "Resource": [
        "arn:aws:s3:::amzn-s3-demo-bucket",
        "arn:aws:s3:::amzn-s3-demo-bucket/*"
      ]
    }
  ]
}

Create the role and attach the permissions policy with the following commands.


aws iam create-role --role-name role --assume-role-policy-document file://trust-policy.json

aws iam put-role-policy --role-name role --policy-name WorkdayLDQConnectorAccess --policy-document file://permissions-policy.json

Note

The glue:ManagedConnector permission and the spill bucket permissions are required. Without them, queries fail at run time.

Create the Glue connection

Create the Glue connection of type WORKDAYLDQ. In AthenaProperties, set MANAGED_CONNECTION to true and specify a spill bucket. When ValidateCredentials is true, AWS Glue gets an access token from Workday and runs a test query before it creates the connection.


aws glue create-connection --region region --cli-input-json '{"ConnectionInput":{"Name":"name","ConnectionType":"WORKDAYLDQ","ConnectionProperties":{"HOST":"host","PORT":"443","ROLE_ARN":"arn:aws:iam::account:role/role"},"AthenaProperties":{"MANAGED_CONNECTION":"true","spill_bucket":"bucket","spill_prefix":"workday-ldq-spill"},"AuthenticationConfiguration":{"AuthenticationType":"CUSTOM","SecretArn":"secret-arn"},"ValidateCredentials":true,"ValidateForComputeEnvironments":["SPARK","ATHENA"]}}'

On success, the command returns "CreateConnectionStatus":"READY".

Register the connection with Lake Formation

Register the connection with Lake Formation. The connection ARN has the format arn:aws:glue:region:account:connection/name.


aws lakeformation register-resource --region region --resource-arn arn:aws:glue:region:account:connection/name --role-arn arn:aws:iam::account:role/role --with-federation --with-privileged-access

Create the federated catalog

Create the federated catalog that points to the connection with the identifier workday. The catalog name that you choose is the name that you use in Athena.


aws glue create-catalog --region region --cli-input-json '{"Name":"catalog","CatalogInput":{"FederatedCatalog":{"Identifier":"workday","ConnectionName":"name"},"CreateDatabaseDefaultPermissions":[],"CreateTableDefaultPermissions":[]}}'

Verify the catalog with the following command. The public database contains your Workday tables.


aws glue get-databases --region region --catalog-id account:catalog

Grant Lake Formation permissions

Grant Lake Formation permissions to each IAM principal that queries Workday from Athena. Grant DESCRIBE on the public database and SELECT and DESCRIBE on all tables.


aws lakeformation grant-permissions --region region --principal DataLakePrincipalIdentifier=principal --permissions DESCRIBE --resource '{"Database":{"CatalogId":"account:catalog","Name":"public"}}'

aws lakeformation grant-permissions --region region --principal DataLakePrincipalIdentifier=principal --permissions SELECT DESCRIBE --resource '{"Table":{"CatalogId":"account:catalog","DatabaseName":"public","TableWildcard":{}}}'

To limit access to specific columns, grant SELECT on a TableWithColumns resource. To limit access to specific rows, create a data filter with a row filter expression and grant SELECT on the data filter. For more information, see Data filtering and cell-level security in Lake Formation in the AWS Lake Formation Developer Guide.

Each principal that queries Workday from Athena also needs IAM permissions to run Athena queries and to write to the Athena query result location. For example, attach the AmazonAthenaFullAccess managed policy to the principal. For more information, see Identity and access management in Athena and Specify a query result location.

Note

Lake Formation data lake administrators can query the federated catalog without these grants. To confirm that your grants work as you intend, run a test query as a principal that isn't a data lake administrator.

Running queries

In the Athena query editor, for Data source, choose your federated catalog, and then choose the public database. You can reference tables with three-part names in the format "catalog"."database"."table". The Athena query result location must be in the same AWS Region as the federated catalog.

The following example query returns the columns and data types of a table.


SELECT column_name, data_type FROM "catalog".information_schema.columns WHERE table_schema = 'public' AND table_name = 'procurement_supplier';

The following example query returns rows from a table.


SELECT id, workday_id, display_id FROM "catalog"."public"."procurement_supplier" LIMIT 10;

Data type support

The following table shows the corresponding data types for Workday Live Data Query and Athena.

Workday Live Data Query	Athena
Long (64-bit integer)	`bigint`
Boolean	`boolean`
String	`varchar`
Date	`date`
Timestamp (millisecond precision)	`timestamp(3)`
Decimal	`decimal(p,s)`. The precision and scale depend on the Workday field. For example, currency amounts use `decimal(26,6)`.
Multi-instance field	`array(varchar)`

To see the data type of each column, query information_schema.columns.

Performance

For optimal performance, use filters in queries whenever possible and select only the columns that you need. The connector performs predicate pushdown to decrease the data scanned. LIMIT clauses and simple predicates are pushed down to Workday.

LIMIT clauses

A LIMIT N statement reduces the data scanned by the query. With LIMIT N pushdown, the connector returns only N rows to Athena.

Predicates

A predicate is an expression in the WHERE clause of a SQL query that evaluates to a Boolean value and filters rows. The following operators support predicate pushdown.

Boolean: AND, OR
Equality: EQUAL, LESS_THAN, LESS_THAN_OR_EQUAL, GREATER_THAN, GREATER_THAN_OR_EQUAL, IS_NULL
Other: IN, BETWEEN

Athena evaluates other expressions, such as LIKE patterns, after it reads data from the connector.

Combined pushdown example

For enhanced querying capabilities, combine the pushdown types, as in the following example.


SELECT id, display_id FROM "catalog"."public"."procurement_supplier" WHERE (id BETWEEN 1000 AND 2000 OR id IN (3000, 3001)) AND basic_worktag_only = false LIMIT 10;

Additional resources

Get Started with Workday Live Data Query in the Workday documentation
Data Cloud Catalog in the Workday documentation
Connecting to Workday Live Data Query in the AWS Glue User Guide
Use Amazon Athena Federated Query
Lake Formation permissions reference in the AWS Lake Formation Developer Guide
Federate to Workday Data Connect in the AWS Lake Formation Developer Guide

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Vertica

Create a data source connection