> For the complete documentation index, see [llms.txt](https://docs.coherent.global/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.coherent.global/xconnector/how-to-use-spark-xconnector-service-to-connect-a-remote-service.md). # How to: Use Spark XConnector service to connect a remote service {% hint style="info" %} This feature is only enabled on certain environments and tenants. [Contact support](/support/contact-support.md) for more information about this feature. {% endhint %} The process to set up a Spark `XConnector` service is similar to [Call Spark services as functions (Xcall)](/build-spark-services/additional-mappings/call-spark-services-as-functions-xcall.md). ## Configure the remote service {% hint style="warning" %} The sample remote service is publicly available without authentication. Please see [Secure XConnector access with OAuth 2.0 tokens](/xconnector/secure-xconnector-access-with-oauth-2.0-tokens.md) for information about secure remote services. {% endhint %} For this guide, a remote service has been configured with an [Azure Function](https://learn.microsoft.com/en-us/azure/azure-functions/) called `species`. The `species` remote service receives a table of animal `types` and returns a table of `results` for the species `name`, `type`, and `countries`. {% tabs %} {% tab title="Sample request" %} 1. Run the `curl` statement in your application of choice. The request body follows the format in [Remote service request and response format](/xconnector/remote-service-request-and-response-format.md#remote-service-request-format). 2. Remove entries under `["Type"]` and run the command again. 3. The list of species returned should change.
{% code overflow="wrap" %} ```json curl --location 'https://func-xconnector-sample.azurewebsites.net/api/species' \ --header 'Content-Type: application/json' \ --data '{ "inputs": [ ["species"], [ [ ["Type"], ["Bird"], ["Mammal"], ["Reptile"] ] ] ] }' ``` {% endcode %}
{% endtab %} {% tab title="Sample response" %} The response follows the format [Remote service request and response format](/xconnector/remote-service-request-and-response-format.md#remote-service-response-format).
{% code overflow="wrap" %} ```json { "outputs": [ [ "results" ], [ [ ["name", "type", "countries"], ["Spotted Owl", "Bird", "USA,Canada"], ..., ["Golden Tiger","Mammal","India,Thailand"], ..., ["Slow Lizard","Reptile","Mexico,Argentina"], ... ] ] ], "warnings": [ null ], "errors": [ null ], "process_time": [ 1 ] } ``` {% endcode %}
{% endtab %} {% endtabs %} The `species` Azure Function can be built with this C# project. {% file src="/files/Ob5pGYtpncheQcIC0Tdd" %} ## Create a downstream Spark service with the remote service call {% file src="/files/3abQzfzaSPIo7MCTemV2" %} In [Call Spark services as functions (Xcall)](/build-spark-services/additional-mappings/call-spark-services-as-functions-xcall.md), downstream services contain encapsulated calculations in Data. For `XConnector`, it only needs to contain the references to the remote service and the input / output specifications. 1. Open the Excel file. * The workbook has two tabs. * `XConnector`: Defines the location of the remote service. * `POST`: Define the inputs and outputs to send to the remote service. 2. Review all of the mappings in the **Name Manager**, either: * Select the **Formulas** tab and select **Name Manager** OR, * Use the keyboard shortcut `Ctrl+F3`. | Name | Value | Refers to | Comment | | --------------------------------- | ------------------------------------------------------- | ------------------- | ------------------------------------------------------------------------------------------------------------------------- | | `Xinput_type` | `...` | `=POST!$B$2:$B$7` | List of the species types. This range needs to be long enough to accept the maximum input length to the remote service. | | `Xoutput_results` | `...` | `=POST!$E$2:$G$102` | List of species received. This range needs to be long enough to accept the maximum output length from the remote service. | | `XPROPERTY_DefaultEngineType` | `XConnector` | `=XConnector!$B$4` | Sets the service type as `XConnector` in Spark. | | `XPROPERTY_XConnectorServiceName` | `species` | `=XConnector!$B$3` | Name of the remote service within the base URL. | | `XPROPERTY_XConnectorTargetUrl` | `https://func-xconnector-sample.azurewebsites.net/api/` | `=XConnector!$B$2` | Base URL for the remote resource. Must end with `/`. | 3. Note that this Excel workbook contains no formulas. Its purpose is only to define the location and layout of the remote service request and response. 4. Create a folder called `xconnectors` if it does not exist. 5. Upload the Excel file for the downstream service into the folder. 6. Note that the [API Tester](/navigation/api-tester.md) and [Execute API](/spark-apis/execute-api.md) will not function directly on this service. ## Create an upstream Spark service to invoke the downstream service With [Call Spark services as functions (Xcall)](/build-spark-services/additional-mappings/call-spark-services-as-functions-xcall.md), any Excel file can have access to the remote service. The upstream service makes the API request and receives the response from the downstream service. {% file src="/files/QImuJPJ2PnyRNlZSQawg" %} ### Interact with `XConnector` in Excel 1. Open the upstream file `species-xcall.xlsx` in Excel. The file includes a `CS.SPARK_XCALL` function to `xconnectors/species`. 2. Load [Coherent Assistant](https://docs.coherent.global/assistant/) and sign in to the same environment and tenant. 3. Review all of the mappings in the **Name Manager**, either: * Select the **Formulas** tab and select **Name Manager** OR, * Use the keyboard shortcut `Ctrl+F3`. | Name | Value | Refers to | Comment | | ----------------- | ----- | --------------------- | ------------------------------------------------------------------------------------------------------------------------- | | `Xinput_type` | `...` | `=Sheet1!$B$2:$B$7` | List of the species types. This range needs to be long enough to accept the maximum input length to the remote service. | | `Xoutput_count` | `71` | `=Sheet1!$T$2` | Number of species returned. | | `Xoutput_results` | `...` | `=Sheet1!$O$2:$Q$102` | List of species received. This range needs to be long enough to accept the maximum output length from the remote service. | 4. Remove entries under the input `type` and the species returned will change. ### Use the `XConnector` with an integration When uploaded to Spark, the Excel file becomes an upstream service with access to the remote service. 1. Open the previously created `xconnectors` folder. 2. Upload the Excel file for the upstream service into the folder. 3. Use the [API Tester](/navigation/api-tester.md) to interact with the upstream service `species-xcall`. For this to work properly, you must add the [Execute API (v3)](/spark-apis/execute-api/execute-api-v3.md#request_meta) `xconnector_parse_mode = 1`. 1. In **API request (inputs)**, select **Raw view**. 2. In the `request_meta`, add a comma `,` to the end of the last entry and then add `"xconnector_parse_mode" = 1`. 4. Remove entries under the input `type` and the species returned will change. 5. If you would like to make any changes to the upstream service, edit the Excel file and re-test the `CS.SPARK_XCALL` function with Coherent Assistant. Upload the Excel file again to update the service. Two levels of calculations take place: 1. The downstream service calls the remote service to query the `species` data in the Azure Function. 2. The upstream file receives the response and processes the response into the `CS.SPARK_XCALL` output template. Any calculations within the upstream file are also calculated, such as the `Xoutput_count`. {% tabs %} {% tab title="Sample request body" %}
```json { "request_data": { "inputs": { "type": [ { "type": "bird" }, { "type": "reptile" } ] } }, "request_meta": { "xconnector_parse_mode": 1 } } ```
{% endtab %} {% tab title="Sample response body" %}
`HTTP 200 OK` `Content-Type: application/json` ```json { "response_data": { "outputs": { "count": 24, "results": [ { "name": "Spotted Owl", "type": "Bird", "countries": "USA,Canada" }, ..., { "name": "Slow Lizard", "type": "Reptile", "countries": "Mexico,Argentina" }, ... ] }, "warnings": null, "errors": null }, "response_meta": { ... } } ```
{% endtab %} {% endtabs %} ## Considerations * If the `request_meta` `"xconnector_parse_mode" = 1` is omitted, you will encounter issues handling any `XConnector` services that return tables. * Although [Remote service request and response format](/xconnector/remote-service-request-and-response-format.md) specification supports multiple request records and response records at a time, the upstream Spark integration can only process one request at a time. In other words, the length of the `outputs`, `warnings`, `errors`, `process_time` array should be `2` elements only. * [Tutorial: Define CS.SPARK\_XCALL UDF](/build-spark-services/additional-mappings/call-spark-services-as-functions-xcall/tutorial-define-cs.spark_xcall-udf.md#array-xcall-for-scaling) is not supported with `XConnector`.