GraphQL Data Connector

Query any GraphQL endpoint with SQL, page through results automatically, and unnest JSON responses into rows and columns.

The GraphQL Data Connector enables federated SQL queries on any GraphQL endpoint by specifying graphql as the selector in the from value for the dataset.

datasets:
  - from: graphql:your-graphql-endpoint
    name: my_dataset
    params:
      json_pointer: /data/some/nodes
      graphql_query: |
        {
          some {
            nodes {
              field1
              field2
            }
          }
        }

Configuration

`from`

The from field takes the form of graphql:your-graphql-endpoint.

`name`

The dataset name. This will be used as the table name within Spice. The dataset name cannot be a reserved keyword.

`params`

The GraphQL data connector can be configured by providing the following params. Use the secret replacement syntax to load the password from a secret store, e.g. ${secrets:my_graphql_auth_token}.

Parameter Name	Description	Required	Default
`graphql_query`	The GraphQL query to execute. See examples for a sample GraphQL query.	Yes	-
`json_pointer`	The JSON pointer into the response body. When `graphql_query` is paginated, the `json_pointer` can be inferred.	No	-
`unnest_depth`	Depth level to automatically unnest objects to. Disabled if unspecified or `0`. Maximum value is `50`.	No	`0`
`graphql_auth_header`	A custom header name to use for authentication instead of the default `Authorization: Bearer` header. When set, the value of `graphql_auth_token` is sent as the value of this header. Useful for APIs that require authentication via a custom header (e.g. `X-Shopify-Access-Token`).	No	-
`graphql_auth_token`	The authentication token to use to connect to the GraphQL server. Uses bearer authentication by default, or sent via the custom header specified by `graphql_auth_header`.	No	-
`graphql_auth_user`	The username to use for basic auth. E.g. `graphql_auth_user: my_user`	No	-
`graphql_auth_pass`	The password to use for basic auth. E.g. `graphql_auth_pass: ${secrets:my_graphql_auth_pass}`	No	-

GraphQL Query Example

graphql_query: |
  {
    some {
      nodes {
        field1
        field2
      }
    }
  }

Examples

Example using the GitHub GraphQL API and Bearer Auth. The following will use json_pointer to retrieve all of the nodes in starredRepositories:

from: graphql:https://api.github.com/graphql
name: stars
params:
  graphql_auth_token: ${env:GITHUB_TOKEN}
  graphql_auth_user: ${env:GRAPHQL_USER}                                                                                            ...
  graphql_auth_pass: ${env:GRAPHQL_PASS}
  json_pointer: /data/viewer/starredRepositories/nodes
  graphql_query: |
    {
      viewer {
        starredRepositories {
          nodes {
            name
            stargazerCount
            languages (first: 10) {
              nodes {
                name
              }
            }
          }
        }
      }
    }

Custom Auth Header Example

Some APIs require authentication via a custom header instead of the standard Authorization: Bearer header. Use the graphql_auth_header parameter to specify a custom header name:

datasets:
  - from: graphql:https://mystore.myshopify.com/admin/api/2024-01/graphql.json
    name: shopify_products
    params:
      graphql_auth_header: "X-Shopify-Access-Token"
      graphql_auth_token: ${secrets:SHOPIFY_TOKEN}
      graphql_query: |
        {
          products(first: 10) {
            edges {
              node {
                id
                title
              }
            }
          }
        }
      json_pointer: /data/products/edges

`graphql_auth_header`	`graphql_auth_token`	`graphql_auth_user`/`graphql_auth_pass`	Result
set	set	-	Custom header: `X-Custom: <token>`
not set	set	-	Default: `Authorization: Bearer <token>`
not set	not set	set	HTTP Basic Auth
not set	not set	not set	No auth

Rate Control Parameters

The GraphQL connector supports shared HTTP rate control to limit concurrency and request rate per upstream origin. These parameters can be set per-dataset (in params) or globally (in runtime.params). Dataset-level settings override the global defaults.

Parameter Name	Description
`max_concurrent_requests`	Maximum number of concurrent HTTP requests to the same upstream origin. Overrides `runtime.params.http_max_concurrent_requests`. If both are unset, concurrency limiting is disabled.
`requests_per_second_limit`	Maximum number of HTTP requests per second to the same upstream origin. Overrides `runtime.params.http_requests_per_second_limit`. If both are unset, no per-second rate limit is applied.
`requests_per_minute_limit`	Maximum number of HTTP requests per minute to the same upstream origin. Overrides `runtime.params.http_requests_per_minute_limit`. If both are unset, no per-minute rate limit is applied.
`rate_control_jitter_min`	Minimum random delay added before HTTP requests when rate control is active. Accepts durations such as `5ms` or `0ms`. Defaults to `5ms` when a request-rate limit is configured.
`rate_control_jitter_max`	Maximum random delay added before HTTP requests when rate control is active. Accepts durations such as `10ms` or `0ms`. Defaults to `10ms` when a request-rate limit is configured.
`rate_control_acquire_timeout`	Maximum time a request waits for rate-control capacity before it fails, as a duration such as `30s`. Overrides `runtime.params.http_rate_control_acquire_timeout`. Defaults to `30s`, the connector's request timeout. `0` waits without a bound.
`rate_control_failure_threshold`	Upstream error rate above which adaptive rate control admits fewer requests than the configured limits, as a percentage (`25%`) or a fraction (`0.25`). Overrides `runtime.params.http_rate_control_failure_threshold`. Default: `10%`.
`rate_control_window`	Reaction and recovery window of adaptive rate control, as a duration. A shorter window reacts and recovers faster. Overrides `runtime.params.http_rate_control_window`. Default: `10s`. With cluster rate control, the default is `runtime.source_rate_control.refresh_interval`, and a shorter window is raised to it.

Multiple datasets targeting the same origin (scheme://host:port) share the same rate controller, even when their endpoint paths differ, and must resolve to the same values for these parameters; a dataset whose values differ fails to load. See HTTP Rate Control for adaptive throttling, bounded waits, and limits across instances.

Pagination

The GraphQL Data Connector supports automatic pagination of the response for queries using cursor pagination.

The graphql_query must include the pageInfo field as per spec. The connector will parse the graphql_query, and when pageInfo is present, will retrieve data until pagination completes.

The query must have the correct pagination arguments in the associated paginated field.

Example

Forward Pagination:

{
  something_paginated(first: 100) {
    nodes {
      foo
      bar
    }
    pageInfo {
      endCursor
      hasNextPage
    }
  }
}

Backward Pagination:

{
  something_paginated(last: 100) {
    nodes {
      foo
      bar
    }
    pageInfo {
      startCursor
      hasPreviousPage
    }
  }
}

Working with JSON Data

Tips for working with JSON data. For more information see Datafusion Docs.

Accessing objects fields

You can access the fields of the object using the square bracket notation. Arrays are indexed from 1.

Example for the stargazers query from pagination section:

sql> select node['login'] as login, node['name'] as name from stargazers limit 5;
+--------------+----------------------+
| login        | name                 |
+--------------+----------------------+
| simsieg      | Simon Siegert        |
| davidmathers | David Mathers        |
| ahmedtadde   | Ahmed Tadde          |
| lordhamlet   | Shih-Fen Cheng       |
| thinmy       | Thinmy Patrick Alves |
+--------------+----------------------+

Piping array into rows

You can use Datafusion unnest function to pipe values from array into rows. We'll be using countries GraphQL api as an example.

from: graphql:https://countries.trevorblades.com
name: countries
params:
  json_pointer: /data/continents
  graphql_query: |
    {
      continents {
        name
        countries {
          name
          capital
        }
      }
    }

description: countries
acceleration:
  enabled: true
  refresh_mode: full
  refresh_check_interval: 30m

Example query:

sql> select continent, country['name'] as country, country['capital'] as capital
from (select name as continent, unnest(countries) as country from countries)
where continent = 'North America' limit 5;
+---------------+---------------------+--------------+
| continent     | country             | capital      |
+---------------+---------------------+--------------+
| North America | Antigua and Barbuda | Saint John's |
| North America | Anguilla            | The Valley   |
| North America | Aruba               | Oranjestad   |
| North America | Barbados            | Bridgetown   |
| North America | Saint Barthélemy    | Gustavia     |
+---------------+---------------------+--------------+

Unnesting object properties

You can also use the unnest_depth parameter to control automatic unnesting of objects from GraphQL responses.

This examples uses the GitHub stargazers endpoint:

from: graphql:https://api.github.com/graphql
name: stargazers
params:
  graphql_auth_token: ${env:GITHUB_TOKEN}
  unnest_depth: 2
  json_pointer: /data/repository/stargazers/edges
  graphql_query: |
    {
      repository(name: "spiceai", owner: "spiceai") {
        id
        name
        stargazers(first: 100) {
          edges {
            node {
              id
              name
              login
            }
          }
          pageInfo {
            hasNextPage
            endCursor
          }
        }

      }
    }

If unnest_depth is set to 0, or unspecified, object unnesting is disabled. When enabled, unnesting automatically moves nested fields to the parent level.

Without unnesting, stargazers data looks like this in a query:

sql> select node from stargazers limit 1;
+------------------------------------------------------------+
| node                                                       |
+------------------------------------------------------------+
| {id: MDQ6VXNlcjcwNzIw, login: ashtom, name: Thomas Dohmke} |
+------------------------------------------------------------+

With unnesting, these properties are automatically placed into their own columns:

sql> select node from stargazers limit 1;
+------------------+--------+---------------+
| id               | login  | name          |
+------------------+--------+---------------+
| MDQ6VXNlcjcwNzIw | ashtom | Thomas Dohmke |
+------------------+--------+---------------+

Unnesting Duplicate Columns

By default, the Spice Runtime will error when a duplicate column is detected during unnesting.

For example, this example spicepod.yml query would fail due to name fields:

from: graphql:https://localhost
name: stargazers
params:
  unnest_depth: 2
  json_pointer: /data/users
  graphql_query: |
    query {
      users {
        name
        emergency_contact {
          name
        }
      }
    }

This example would fail with a runtime error:

WARN runtime: Invalid GraphQL object access: Column 'name' already exists in the object.

Avoid this error by using aliases in the query where possible. In the example above, a duplicate error was introduced from emergency_contact { name }.

The example below uses a GraphQL alias to rename emergency_contact.name as emergencyContactName.

from: graphql:https://localhost
name: stargazers
params:
  unnest_depth: 2
  json_pointer: /data/people
  graphql_query: |
    query {
      users {
        name
        emergency_contact {
          emergencyContactName: name
        }
      }
    }

Cookbook

A cookbook recipe to configure GraphQL as a data connector in Spice. GraphQL Data Connector