Skip to main content

What makes a connector?

In Data Sync a connector enables you to connect to a datasource to read the data or to write back to the data set.

When using the JSON connector currently this is read only which means you can only connect on the source side. However you can use project automation to extend the functionality and handle the write requests. The support of writing back to the API endpoint will be coming in the future.

The JSON API connector enables you to connect to any API that returns a JSON response. You need to create an XML connection file to describe the connection, which is where the JSON API designer tool comes in.

In simple terms the connection needs the schema and data set describing, which can be either static or discovered if the API supports it, and around that is the configuration. This includes the connection parameters and any other parameters the API requires.

What is GetDataSchema & GetDataTable?​

You can describe the schema and dataset by using the two main functions within the Datasource node: GetDataSchema and GetDataTable.

  • GetDataSchema returns the schema or column information. Supports static schema definition or fetch functions to discover the schema.
  • GetDataTable loads the data set into those columns.

If an API supports it you can use fetch functions to return the column names and properties, however not all properties may be returned and you may need to handle looping through the nodes to discover all of the columns. By using a Static Schema map you can define the data types and column names, and this is our recommended method for schema definition.

OAuth2 Ouvvi Apps Example​

The example below puts everything together to create a OAuth2 authenticated JSON API connection to return a Products dataset by Looking up the Products for Each Supplier and paging the returned results. This example calls the Demo Ouvvi Apps URL.

<!--
An Example REST API connector which will use 2 requests
 * First to return a List of Suppliers
 * Second to loop around the Suppliers and return a List of Products for each Supplier.
-->
<Connector name="Simego Web API Connector" description="Connect to Simego Web API" version="1.0">
  <Parameters>
    <Parameter name="URL" value="https://demo.online.simego.com" />
    <Parameter name="PageSize" value="10" />
    <Parameter name="ClientID" value="" />
    <Parameter name="ClientSecret" value="" />
  </Parameters>
  <Authentication>
    <OAuth2 token-url="{URL}/oauth/token" client-id="{ClientID}" client-secret="{ClientSecret}" grant-type="client_credentials" />
  </Authentication>
  <Datasources>
    <Datasource name="Products">
      <Action name="GetDataSchema">
        <!-- Define Static Schema for this Datasource -->
        <StaticSchemaMap>
          <Column name="ProductID" data-type="System.Int32" />
          <Column name="SupplierID" data-type="System.Int32" />
          <Column name="ProductName" data-type="System.String" />
        </StaticSchemaMap>
      </Action>
      <Action name="GetDataTable">
        <!-- First get a List of Supplier ID values -->
        <Fetch url="{URL}/app/list/suppliers?columns=SupplierID&amp;limit=1000">
          <!-- For Each SupplierID get the related Products -->
          <ForEach path="data">
            <!-- Handle Paging for the Product List -->
            <PagingRequest path="data" start="1" next-path="nextpage">
              <Fetch url="{URL}/app/list/products?filter=SupplierID%20eq%20{$SupplierID}&amp;limit={PageSize}&amp;page={PagingRequest.Page}">
                <DataTableTransform path="data" />
              </Fetch>
            </PagingRequest>
          </ForEach>
        </Fetch>
      </Action>
    </Datasource>
  </Datasources>
</Connector>

OAuth Graph API Example​

In the example below we can call the Microsoft Graph API whilst using OAuth2 authorization_code flow.

<Connector name="MS Graph API" description="Test querying Graph API via JSON API" version="1.0">
  <Parameters>
    <Parameter name="url" value="https://graph.microsoft.com/beta" />
    <Parameter name="authorize-url" value="https://login.microsoftonline.com/cfb071d-x7yu-4792-4414-9f3ca405620b/oauth2/v2.0/authorize" />
    <Parameter name="token-url" value="https://login.microsoftonline.com/cfb071d-x7yu-4792-4414-9f3ca405620b/oauth2/v2.0/token" />
    <Parameter name="client-id" value="" />
    <Parameter name="client-secret" value="" />
    <Parameter name="scope" value="https://graph.microsoft.com/.default offline_access" />
  </Parameters>
  <Authentication>
    <OAuth2 authorise-url="{authorize-url}" token-url="{token-url}" client-id="{client-id}" client-secret="{client-secret}" grant-type="authorization_code" scope="{scope}" prompt="select_account" />
    <HttpHeader name="OData-Version" value="4.0" />
    <HttpHeader name="OData-MaxVersion" value="4.0" />
  </Authentication>
  <Datasources>
    <Datasource name="users">
      <Actions>
        <Action name="GetDataSchema">
          <StaticSchemaMap>
            <Column name="id" type="System.String" />
            <Column name="givenName" type="System.String" />
            <Column name="mail" type="System.String" />
          </StaticSchemaMap>
        </Action>
        <Action name="GetDataTable">
          <Fetch url="{url}/users">
            <DataTableTransform path="value" />
          </Fetch>
        </Action>
      </Actions>
    </Datasource>
  </Datasources>
</Connector>

Return from a File​

In the example below we return data from a JSON file that contains exchange rate data by using the ReadFile action.

<Connector name="My_API" description="" version="1.0">
    <Parameters></Parameters>
    <Authentication></Authentication>
    <Datasources>
        <Datasource name="products">
            <Action name="GetDataSchema">
                <StaticSchemaMap>
                    <Column name="ProductID" data-type="System.Int32" />
                    <Column name="ProductName" data-type="System.String" />
                    <Column name="SupplierID" data-type="System.Int32" />
                    <Column name="CategoryID" data-type="System.Int32" />
                    <Column name="QuantityPerUnit" data-type="System.String" />
                    <Column name="UnitPrice" data-type="System.Decimal" />
                    <Column name="UnitsInStock" data-type="System.Int32" />
                    <Column name="UnitsOnOrder" data-type="System.Int32" />
                    <Column name="ReorderLevel" data-type="System.Int32" />
                    <Column name="Discontinued" data-type="System.Boolean" />
                </StaticSchemaMap>
            </Action>
            <Action name="GetDataTable">
                <ReadFile path="C:\Users\Sean\Downloads\products.json">
                    <DataTableTransform path="" />
                </ReadFile>
            </Action>
        </Datasource>
        <Datasource name="fxrates">
            <Action name="GetDataSchema">
                <StaticSchemaMap>
                    <Column name="Currency" data-type="System.String" />
                    <Column name="Rate" data-type="System.Decimal" />
                </StaticSchemaMap>
            </Action>
            <Action name="GetDataTable">
                <ReadFile path="C:\Users\MyUser\Downloads\fxrates.json">
                    <DataTableTransform path="" />
                </ReadFile>
            </Action>
        </Datasource>       
    </Datasources>
</Connector>

Simple API​

The following example connects to a simle API that returns sunrise and sunset data

<Connector name="Sunrise and Sunset" description="Returns the sunrise and sunset for a longitude and latitude." version="1.0">
    <Parameters>
        <Parameter name="url" value="https://api.sunrise-sunset.org/json?lat=36.7201600&amp;lng=-4.4203400" />      
    </Parameters>
    <Datasources>
        <Datasource name="Sunset-Sunrise">
            <Actions>
                <Action name="GetDataSchema">
                    <StaticSchemaMap>
                        <Column name="sunrise" type="System.String" />
                        <Column name="sunset" type="System.String" />
                        <Column name="solar_noon" type="System.String" />
                        <Column name="day_length" type="System.String" />
                        <Column name="civil_twilight_begin" type="System.String" />
                        <Column name="civil_twilight_end" type="System.String" />
                        <Column name="nautical_twilight_begin" type="System.String" />
                        <Column name="nautical_twilight_end" type="System.String" />
                        <Column name="astronomical_twilight_begin" type="System.String" />
                        <Column name="astronomical_twilight_end" type="System.String" />
                    </StaticSchemaMap>
                </Action>
                <Action name="GetDataTable">
                    <Fetch method="GET" url="{url}">
                        <DataTableTransform path="results" />
                    </Fetch>
                </Action>
            </Actions>
        </Datasource>
    </Datasources>
</Connector>

Separate Data and Write Value to Column​

The example below returns national holiday dates for the different regions throughout the UK.

This connection file handles looping through the nodes to return the data for England, Scotland and Wales and ensure that the region is written back to the region column inside the GetDataTable function.

<Connector name="My_API" description="" version="1.0">  
    <Datasources>       
        <Datasource name="holidays">
            <Action name="GetDataSchema">
                <StaticSchemaMap>
                    <Column name="region" data-type="System.String" />
                    <Column name="title" data-type="System.String" />
                    <Column name="date" data-type="System.DateTime" />
                </StaticSchemaMap>
            </Action>
            <Action name="GetDataTable">
                <Fetch url="https://www.gov.uk/bank-holidays.json">
                    <DataTableTransform path="england-and-wales.events">
                        <ColumnMap value="england-and-wales" to="region" />
                    </DataTableTransform>
                    <DataTableTransform path="scotland.events">
                        <ColumnMap value="scotland" to="region" />
                    </DataTableTransform>
                    <DataTableTransform path="northern-ireland.events">
                        <ColumnMap value="northern-ireland" to="region" />
                    </DataTableTransform>
                </Fetch>
            </Action>
        </Datasource>
    </Datasources>
</Connector>