SSIS Components for GraphQL

Build 21.0.7940

SELECT Execution

When a SELECT query is issued, the component executes the GET method of the schema, which invokes the component's built-in operations to process GraphQL. In the GET method you have control over the request for data. The following procedures show several ways to use this: search the remote data, server-side, with SELECT WHERE, or implement paging.

Collaborative Query Processing

By default, the component will process the query client-side, in memory, so the Url connection property is all you need to set to execute any SELECT statement.

The component can also offload supported queries to the server while processing the rest of the query client side. For example, a server-side search filters the data so that a smaller dataset can be processed client-side. See the documentation for the SupportEnhancedSQL property for more information on the collaborative query processing feature. You can extend the resulting script to add support for processing requests server side.

Mapping SELECT criteria to GraphQL arguments

The following sections show how to translate a SELECT WHERE statement into a GraphQL query to GraphQL APIs. The procedure uses the following statement:

SELECT * 
FROM <table> 
WHERE ModifiedAt<'2019-10-30 05:05:36.001'

If this filter is supported on the server via query parameters, you can use the other:filter property of the api:info column definition to specify the desired mapping. For the above query, we will use this property to map the modifiedAt < '<date>' filter to the query parameter that returns results that were modifed before a given date, and the modifedAt > '<date>' filter to the query parameter that filters results that were modifed after. other:filter is specified as follows:

  • other:filter is a semicolon separated list of <parameter name>:<operator list>. <parameter name> is the name of the query parameter and <operator list> is a comma-separated list of operators used for the mapping. Valid operators are <, <=, =, > and >=.

To perform this mapping, we would use the following markup for the modifedAt column definition:

    <attr   name="ModifiedAt"                    xs:type="datetime"                other:relativePath="modifiedAt"                              description="When the vendor was last modified."                                        other:filter="modifiedAtAfter:>;modifiedAtBefore:<"                                                                                />

This query results in the following GraphQL query:

businesses {
    edges {
        node {
            customers(modifiedAtBefore: "2019-10-30T09:05:36.001Z") {
                edges {
                    node {
                        ...
                    }
                }
            }
        }
    }
}

Path filters

There exist GraphQL services where the GraphQL argument is not enough to process the filter serverside. The path should be changed. In order to correctly use a path filter you need to complete the following steps:
  1. Add the path to the other:possiblePaths extra info and map it with the column name you want to filter.

    Ex: other:possiblepaths="{'path':'/businesses/edges/node','id':{'path':'/business'}}"

  2. Set the other:isPathFilter to TRUE in the column defintion.
    <attr  name="Id"  xs:type="string" key="true" other:relativePath="id" other:isPathFilter="true" other:filter="id:="  />
After completing these steps the following SQL query
SELECT Id,Name,CreatedAt FROM Businesses WHERE Id='QnVzaW5M6ZTY4ZDA2MmQtYzkzZS00MGZkLTk4YWUtNDg2YzcxMmExNzFl'
will be converted to the GraphQL query
business(id: "QnVzaW5M6ZTY4ZDA2MmQtYzkzZS00MGZkLTk4YWUtNDg2YzcxMmExNzFl") {
    id
    name
    createdAt
}

Pagination

The driver supports two types of paging implementations automatically. You should use the other:paginationType property to select the desired pagination type. The possible values for this property are:

  • Cursor
    repository(owner: "testaccount71", name: "updated-name") {
        labels(first: <Pagesize>) {
            edges {
                node {
                    id
                }
            }
            pageInfo {
                endCursor
                hasNextPage
            }
        }
    }
  • Offset
    businesses(pageSize: <Pagesize>) { 
        edges {
            node {
                id
            }
        }
        pageInfo {
            totalPages
            currentPage
        }
    }

Process Other SELECT Statements Server Side

ORDER BY

You can sort the results serverside if and only if the GraphQL service supports it by specifying the following properties:
  • The other:orderByFormat can be specified in the table definition or in the column definition.
    <rsb:info title="Releases" desc="Lists information about the different releases of a repository." other:orderByFormat="{field: {orderByArgumentValue}, direction: {sortOrder}}">
    <attr name="CreatedAt" xs:type="datetime" other:relativePath="createdAt"  other:orderByFormat="{field: {orderByArgumentValue}, direction: {sortOrder}}" other:orderBy="sort:CREATED_AT" />
  • The other:orderBy should be specified only in the column definition. The format of this property is <orderByArgumentName>:<orderByArgumentValue>
    <attr name="CreatedAt" xs:type="datetime" other:relativePath="createdAt"  other:orderBy="sort:CREATED_AT" />
After completing these steps the following SQL query
SELECT Id FROM Releases ORDER BY CreatedAt ASC
will be converted to the GraphQL query.
repository {
    releases(sort: {field: CREATED_AT, direction: ASC}) {
        edges {
            node {
                id
            }
        }
    }
}

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7940