SAP CPI How to query Databricks and map the result

Overview

One of my requirement working with SAP CPI was to interface Databricks with another system. As JDBC connectivity to Databricks was not supported by CPI, I used the API but I had to solve several issues to transform the payload :

  • Transform the payload from Json to Xml
  • Tweak the query
  • Transform the xml Payload to a more readable structure.

Authenticating to Databricks

Pretty straight forward authentication is done through Token stored in a Credentials stored in Security Material section. It is exposed in the Authorization header.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

   def Message processData(Message message) {
   def service = ITApiFactory.getService(SecureStoreService.class, null);
   def propertiesMap = message.getProperties()
   
   // Credentials Alias refers to where the Credentials are stored in Monitoring/Security Material section.
   def alias = propertiesMap.get('CREDENTIALS_ALIAS');
   
   def credential 		  = service.getUserCredential(alias);
   def password 		    = credential.getPassword(); // client_secret
   def password_value   = password.toString()
   def username 		    = credential.getUsername(); // client_id
   
   String pat = "Bearer ${password_value}";
          
   message.setHeader("Authorization", pat);
   
   return message;
}

Transforming Json to XML

In CPI using the out of the box component was leading to some issues with the payload. To solve the issue I used an xslt script instead. Details are available here .

Tweaking Query

My query was polling data from a single table but some data where generating errors when transforming the json payload to xml. The issue was coming from characters ‘&’ that were not escaped. This solution will not be valid for everyone, but this is how I solved this : sql SELECT field1, field2, base64(field3) as field3, field4 FROM table1 WHERE ... In the xsl mapping, with Saxon xslt version 3 processor, the value can be converted back using this :

1
2
3
4
5
6

	<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:p="http://www.w3.org/2005/xpath-functions" version="3.0" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:saxon="http://saxon.sf.net/" exclude-result-prefixes="#all" expand-text="yes">
	...
		<Field3>
			<xsl:value-of select="saxon:base64Binary-to-string(*[$ixField3], 'UTF8')"/>
		</Field3>
	...

Possible alternative :

  • use the to_xml function. This will return a mix of json and xml.
  • use the url_encode function

Transform the payload

The result sent back is composed of 3 parts :

  • a Status
  • a Manifest
  • the data

The Status shows if the query was successfull or not. The Manifest describes the structure of the data sendback. We can found the following information :

  • Field Index
  • Field Name
  • Field Type

The data is a collection of nodes the structure follows the Manifest description. One note, each field data is contained in a node with the type name (ex. string -> value). Whenever a value is null or empty afaik, the node name becomes “null” ()

For this reason, I decided to get the position of the fields in the Manifest then map the values by position.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:p="http://www.w3.org/2005/xpath-functions" version="3.0" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:saxon="http://saxon.sf.net/" exclude-result-prefixes="#all" expand-text="yes">
	<xsl:output method="xml" indent="yes"/>
	<xsl:template match="/">
 		<Result xmlns="http://mynamespace/something">
			<Status>
				<xsl:value-of select="//p:map[@key='status']/p:string[@key='state']/text()"/>
			</Status>
			<xsl:choose>
				<xsl:when test="//p:map[@key='status']/p:string[@key='state']/text()='SUCCEEDED'">
					<!-- 1. Get all indexes from the manifest -->
					<xsl:variable name="ixField1" select="//p:array[@key='columns']/p:map[p:string[@key='name']/text()='field1']/p:number[@key='position']/text() + 1"/>
					<xsl:variable name="ixField2" select="//p:array[@key='columns']/p:map[p:string[@key='name']/text()='field2']/p:number[@key='position']/text() + 1"/>
					<xsl:variable name="ixField3" select="//p:array[@key='columns']/p:map[p:string[@key='name']/text()='field3']/p:number[@key='position']/text() + 1"/>
					<xsl:variable name="ixField4" select="//p:array[@key='columns']/p:map[p:string[@key='name']/text()='field4']/p:number[@key='position']/text() + 1"/>
          <!-- 2. For Support / Debug reasons, showing the indexes. This can be removed-->
					<Indexes>
						<ixField1>
							<xsl:value-of select="$ixField1"/>
						</ixField1>
						<ixField2>
							<xsl:value-of select="$ixField2"/>
						</ixField2>
						<ixField3>
							<xsl:value-of select="$ixField3"/>
						</ixField3>
						<ixField4>
							<xsl:value-of select="$ixField4"/>
						</ixField4>
					</Indexes>
          <!-- 3. Building the PAyload from the data.-->
					<Records>
						<xsl:for-each select="//p:array[@key='data_array']/p:array">
							<Record>
								<Field1>
									<xsl:value-of select="*[$ixField1]"/>
								</Field1>
								<Field2>
									<xsl:value-of select="*[$ixField2]"/>
								</Field2>
								<Field3>
									<xsl:value-of select="saxon:base64Binary-to-string(*[$ixField3], 'UTF8')"/>
								</Field3>
								<Field4>
									<xsl:value-of select="*[$ixField4]"/>
								</Field4>
							</Record>
						</xsl:for-each>
					</Records>
				</xsl:when>
			</xsl:choose>
		</Common>
	</xsl:template>
</xsl:stylesheet>

References