API_CALL | Agile Data Engine

API_CALL is a load language used to define load steps that make API calls to external systems. It enables triggering external processes or polling external system states as part of a load process.

API_CALL load steps are defined using YAML with a structured schema that supports various features.

Authentication for the API calls is done either with the same Google Cloud or Databricks authentication that is configured for the target database instance or via the Secrets & Integrations.

See also:

Usage

Load step created to a load by using API_CALL as the load step language. It is possible to use OVERRIDE, POST or PRE step to define load steps with API_CALL.

Supported Functionalities

API_CALL supports technically following functionalities:

HTTP Requests
- URL: Only HTTPS is allowed.
- Methods: GET, POST, PUT, DELETE.
- Headers
- Query Parameters: Automatically URL-encoded.
- Timeout Definition: Configurable timeout settings.
- Content: Ability to send JSON payloads.
Retry Logic
- Retry count
- Backoff factor, min and max delays etc
- Rules based on
  - HTTP response codes
  - Content extraction
Response transformations by defining
- Conditions used to select wanted transformation based on
  - HTTP response codes
  - Content extraction
- Result (SUCCESS, FAILED)
- Affected rows
- Variables (output which can be used in later steps etc)
- Transformations results and variables can be based on
  - HTTP response codes
  - Content extraction
  - Constants

Use of variables

API_CALL supports:

Environment Variables: Variables defined in CONFIG_ENVIRONMENT_VARIABLES.
Workflow Variables: Variables defined in CONFIG_LOAD_SCHEDULES.
Variables documented in Variables.
Special Variables:
- <bearer_id_token_from_bq_service_account>: Uses Service Account credentials with a bearer ID token, primarily used when invoking Google Cloud Functions.
- <bearer_token_from_bq_service_account>: Alternative to bearer_id_token_from_bq_service_account.
- <bearer_access_token_from_bq_service_account>: An alternative to the ID token, it can be used with other Google Cloud APIs besides Cloud Functions.
- <bearer_token_from_databricks_service_account>: Uses Service Account credentials with a bearer ID token, primarily used when invoking Databricks APIs.

Examples

Example 1: Simple API_CALL Load Step to Google Cloud functions

POST request to a specified URL.
Default timeout: 300 seconds.
Authorization: Bearer token authentication
- <bearer_token_from_bq_service_account> is special variable only supported when target database is BigQuery and we can use same credentials for Google API-calls what we use for the target database.
Request Content: Simple JSON payload.
Retry Mechanism: Up to 4 retries with default logic.
Response Handling: Default response transformation where 200/201 are SUCCESS and other cases are FAILED

YAML

type: HTTP
request:
  url: https://europe-north1-test-ade-gcp-ci.cloudfunctions.net/ade-ci-test-function
  method: POST 
  headers:
    Authorization: <bearer_token_from_bq_service_account>
  content: |
    {"name": "test1"}
retries:
  total: 4

Example 2: API_CALL with Polling

Load step 1: Start External Process and Capture Process Name

POST request with JSON payload
Authorization: Bearer token authentication
Retries: Retry 401, 404, 429, 500, 502, 503, 504 error codes or timeouts of 10 seconds
Response Handling:
- Parse process_name variable from 200/201 response by using response transformation definition
- Failure if other responses are received.

YAML

type: HTTP
request:
  url: https://europe-north1-ade-gcp-ci.cloudfunctions.net/ade-ci-data-processor
  timeout_seconds: 10
  method: POST
  headers:
    Content-Type: application/json
    Authorization: <bearer_token_from_bq_service_account>
  content: |
    {"process_time":"30","future_result":"SUCCESS","info":"Test call 1"}
retries:
    total: 3
    rules:
      - conditions:
          - type: VALUE_MATCHER
            source: <http_status_code>
            values: [401, 404, 429, 500, 502, 503, 504]
response:
  transformations:
    - description: In case of success we extract returned process_name value with is needed in next step polling
      conditions:
        - type: VALUE_MATCHER
          source: <http_status_code>
          values:
            - 200
            - 201
      variables:
        process_name:
          type: REGEXP_VALUE
          source: <http_response_content>
          regexp: '"process_name"\s*:\s*"([^"]+)"'

Load step 2: Poll Until External Process Completes in external system

GET endpoint with url containing <process_name> variable extracted in previous load step
Authorization: Bearer token authentication
Retries: Retry until we get 200 response where status is not RUNNING
- backoff min and max are configured to same to get same interval with polling (every 5 seconds)
- total is configured to 12 → 12 * 5s = 60s (we expect external process finish in 60 seconds)
Response Handling:
- Check that we get 200 response which return status SUCCESS
- If anything else comes this step fails which indicates that actual external process executed and finished but with fail status

YAML

type: HTTP
request:
  # in url we use variable containing process name from previous step
  url: https://europe-north1-ade-gcp-ci.cloudfunctions.net/ade-ci-data-processor/<process_name>/status
  timeout_seconds: 10
  method: GET
  headers:
    # We need to change audience of token because it is otherwise taken from url and for sub resources it won't work for this cloud function
    Authorization: <bearer_token_from_bq_service_account:audience=https://europe-north1-ade-gcp-ci.cloudfunctions.net/ade-ci-data-processor>
retries:
    # we do status polling every 5 seconds and assumne process should be ready in 60 secs -> 60/5=12
    total: 12
    backoff_min_seconds: 5
    backoff_max_seconds: 5    
    # we retry polling as long as we get non 200 response or status endpoint return RUNNING state (or we hit retry total count)
    rules:
      - conditions:
          - type: VALUE_MATCHER
            source: <http_status_code>
            negate: true
            values: [200]
      - conditions:
          - type: VALUE_MATCHER
            source: <http_status_code>
            values: [200]
          - type: REGEXP_VALUE_MATCHER
            source: <http_response_content>
            regexp: 'RUNNING'
            values:
              - RUNNING
response:
  transformations:
    - description: Step is successful if 200 is returned and state is SUCCESS anymore (process has finished successfully in expected time span)
      conditions:
        - type: VALUE_MATCHER
          source: <http_status_code>
          values:
            - 200
        - type: VALUE_MATCHER
          source: <http_response_content>
          values:
           - SUCCESS

Load step 3: Retrieve Results from Completed Process

Retrieving data from external process result endpoint after process has finished.

GET endpoint with url containing <process_name> variable extracted in previous load step
Authorization: Bearer token authentication
Retries: Default retries
Response Handling:
- Check that we get 200 response and parse result_info with reg exp
- If anything else comes this step fails

YAML

type: HTTP
request:
  url: https://europe-north1-ade-gcp-ci.cloudfunctions.net/ade-ci-data-processor/<process_name>
  timeout_seconds: 10
  method: GET
  headers:
    Content-Type: application/json
    Authorization: <bearer_token_from_bq_service_account:audience=https://europe-north1--ade-gcp-ci.cloudfunctions.net/ade-ci-data-processor>
retries:
    total: 3
response:
  transformations:
    - description: In case of success extract affected rows
      conditions:
        - type: VALUE_MATCHER
          source: <http_status_code>
          values:
            - 200
      variables:
        result_info:
          type: REGEXP_VALUE
          source: <http_response_content>
          regexp: '"generation_time"\s*:\s*"([^"]+)"'

Example 3: Databricks endpoint

You can call a Databricks app that exposes an HTTP API and API_CALL load step authenticates to it by using OAuth 2.0 Bearer token authentication.

GET endpoint
Authorization: Bearer token authentication
Retries: 1 time for these HTTP status codes: 401, 404, 429, 500, 502, 503, 504 or when it timeouts after 10 seconds
Response Handling:
- Check that we get 200 or 201 status code
- If anything else comes this step fails

type: HTTP
request:
  url: https://dbc-012345cd-1b2a.cloud.databricks.com/api/2.1/unity-catalog/catalogs
  timeout_seconds: 10
  method: GET
  headers:
    Content-Type: application/json
    Authorization: <bearer_token_from_databricks_service_account>
retries:
    total: 1
    rules:
      - conditions:
          - type: VALUE_MATCHER
            source: <http_status_code>
            values: [401, 404, 429, 500, 502, 503, 504]
response:
  transformations:
    - description: In case of success extract affected rows
      conditions:
        - type: VALUE_MATCHER
          source: <http_status_code>
          values:
            - 200
            - 201

Example 4: Microsoft Power BI endpoint

This needs the integration configuration be done in the Admin UI. An secret and the integration config is added. An type of “Entra ID Service principal” -secret is added.

In the Admin UI the secret for the PowerBI is added

Then an configuration is added using the PowerBI secret. Integration type is “Power BI API” and scope is for ADE runtime environment, which here is for the a666666 tenant DEV runtime.

The Power BI API integration configuration.

After these are added, a load step using the integration named as “ade_analytics_dev_powerbi” can be used by the API_CALL.

type: HTTP
request:
  integration_name: ade_analytics_dev_powerbi
  url: https://api.powerbi.com/v1.0/myorg/groups/d111d2222-de33-4aa4-5555-d666e777fb88/datasets
  timeout_seconds: 0
  method: GET
  headers:
    Content-Type: application/json
retries:
    total: 1
    rules:
      - conditions:
          - type: VALUE_MATCHER
            source: <http_status_code>
            values: [401, 404, 429, 500, 502, 503, 504]

That integration_name -value can be given from environment variable also as <variable-name> -notation.