Call Web Service
The "Call Web Service" module reads data from a web service and writes it to a table in the target database. The SQL web query and conversion of the result are defined by the datasqill developer via the GUI.
| Name | Meaning |
|---|---|
| Module | Call Web Service |
| Module Class | DsModWebServiceClient |
| Type | Java |
| Purpose | Transfer data between a web service and a database |
| Transformation Code | SQL query on the web service result |
| Sources | Web request (POST) or URL query (GET) |
| Targets | Tables in the database |
Description
The "Call Web Service" module sends a request (or a query in the URL) to a web service and makes the result returned by the web server available for an SQL query that processes the response. The result of the SQL query is written to the target table.
Unlike modules that operate within a database, this module reads data from the web service and writes it to the target database.
The following simple example shows how to load exchange rates between the Euro and US$, British Pound, and Japanese Yen from the European Central Bank as of 15 May 2009. The query is specified in the URL.
The URL in the object looks like this:
M.USD+GBP+JPY.EUR.SP00.A?updatedAfter=2009-05-15T14:15:00
Now the SQL query used (which does not further interpret the data):
SELECT r.ws_response
FROM ${ws_response} r
The variable ${ws_response} is replaced by the module with a subquery that returns 3 columns. See below.
If the target schema is "datasqill" and the target table is "response", the module generates the following SQL for inserting into the target database:
INSERT INTO datasqill.response (ws_response) VALUES(?)
As can be seen in the example, the column names of the target table must be specified as aliases when the source columns have different names.
Data Sources
The source of the module is a web service object. For GET queries, the query parameters must be specified in the URL. Variables from the Loop Query (if specified) can be used in the URL.
For SOAP queries, the SOAPAction is stored in the source object.
The web service used must be connected to the module input in the datasqill graphical data model via the datasqill GUI.
Data Target
The target of the module is a single table. The datasqill runtime user must have write rights for this table.
The target table must be connected to the module output in the datasqill GUI in the graphical data model.
SQL Query (Transformation Content)
In the specified query, the variable ${ws_response} is replaced by a (database-dependent) subquery. Example for Postgres:
(SELECT a ws_request, b ws_response, c http_code)
Where a is replaced with the sent request, b with the received result, and c with the status code from the web call.
The effective SQL looks like this for the example above:
SELECT r.ws_response
FROM (SELECT a ws_request, b ws_response, c http_code) r
Attributes
Query for Loop
A select statement can be specified here. The query is executed in the target database. The columns returned by the Loop Query can be used as variables in the main query and in the URL. The web service is called once per row in the Loop Query. Loop queries with large result sets can significantly increase runtime.
WS Request
For POST web service calls, the request to be transmitted is specified here. The format corresponds to what the web service expects (URL-encoded, JSON, XML, ...). Variables from the Loop Query (if specified) can be used in the request.
Request header
"Request header" allows the definition of header fields to be sent with the web service call. The definition is done with a simple JSON:
{
"header1": "value1",
"header2": "value2
}
Variables from the Loop query can be used to replace header keys and values.
Truncate Before
When the boolean attribute Truncate Before is enabled, the table is completely emptied beforehand. If the target database offers a datasqill trigger for TRUNCATE TABLE, it will be used. Otherwise, all rows are deleted with the DELETE command.
Fail on Error
By default, the run fails when the web service returns an error (status >= 300). This can be disabled. In that case, the status code should be stored for later consideration.
Configuring a web service in the keyfile
The base URL is stored in the keyfile, as well as optional additional parameters for authentication (if necessary).
A connection must be set up to access the web service. Assuming this connection has ID 11, the entry in the keyfile looks like this:
SQTS_DB_11 https://sdw-wsrest.ecb.europa.eu/service/data/EXR||method=GET:encoding=UTF-8|
A keyfile consists of 4 sections separated by the pipe "|" symbol. To use a pipe symbol within a field, it must be escaped with a backslash.
| Position | Meaning | Note |
|---|---|---|
| 1 | URL | Base URL to the web service |
| 2 | User | Currently not used. In implementations before version 4.01, the method was stored here |
| 3 | Parameters | See list below. In implementations before version 4.01, the character set used was stored here |
| 4 | Password | The BASIC encrypted user and password pair is stored here |
In future versions, the user and password will be used for Basic authentication.
Parameters in the keyfile
Parameters are always name/value pairs (Name=Value). Multiple parameters are separated by a colon.
To use a colon within a parameter name or value, it must be escaped with a backslash. This should be considered especially when specifying URLs.
The following parameters are available from version 4.01:
| Name | Meaning | Default | Note |
|---|---|---|---|
| method | Method | POST | Default method for calling the web service |
| encoding | Charset | UTF-8 | Charset of posted data or expected result when using GET |
| media_type | Format | text/xml | Format of the request to be sent, or expected response when using GET |
| https.proxyHost | Proxy Host | none | HTTPS proxy address |
| https.proxyPort | Proxy Port | none | HTTPS proxy port |
| AuthenticationUrl | Authentication Server URL | none | Server address to obtain a token |
| AuthenticationMethod | Authentication Method | none | POST or GET |
| AuthenticationRequest | Authentication Request Content | none | For POST, this text is sent to obtain a token |
| AuthenticationRequestContentType | Media Type | none | Media type to obtain a token |
| AuthenticationTokenPattern | Regular Expression | none | Regular expression to extract the token from the response |
| loopDelay | Delay | none | Minimum interval in milliseconds between two calls |
| headerList | Header fields | none | List of header parameters as key-value pairs, e.g. headerList={clientid=xyz,apikey=very-secret} |
When an AuthenticationUrl is specified, AuthenticationMethod, AuthenticationRequest, and AuthenticationTokenPattern must also be specified.
In this case, a token is requested from the specified server and the token is sent as BEARER with all subsequent web requests. If Basic authentication is also specified, it is used for authentication when requesting the token.
If no AuthenticationUrl was specified but Basic authentication was, then Basic authentication is sent with each service call.
If the loopDelay parameter is present and a valid number, it ensures the minimum interval between two successive calls. If a subsequent call would come too close to the previous one, the parameter is used to calculate a wait time after which another call is allowed.
If
waitTime = lastInvocationTime + loopDelay - currentInvocationTime
is greater than 0, the module waits for that time.
Examples
Loading exchange rates into a structured table
In the example above, the exchange rates are loaded such that the response is stored as text in the database.
In this example, the data is to be loaded in structured form into an Oracle database.
Since the ECB allows only limited sizes in the response, the exchange rates for Dollar, Japanese Yen, and British Pound are loaded with separate service calls.
The target table is created as follows:
CREATE TABLE datasqill.wechselkurse (
waehrung VARCHAR2(10) NOT NULL
, datum DATE NOT NULL
, wechselkurs NUMBER
, status CHAR
);
A Loop Query selects the desired currencies:
SELECT 'GBP' waehrung FROM DUAL UNION ALL
SELECT 'JPY' waehrung FROM DUAL UNION ALL
SELECT 'USD' waehrung FROM DUAL
The specified URL of the web service query contains the variable waehrung.
M.${waehrung}.EUR.SP00.A
The result of the web request looks like this (first two entries):
<generic:Obs
xmlns:generic="http://www.sdmx.org/resources/sdmxml/schemas/v2_1/data/generic">
<generic:ObsDimension value="1999-01"/>
<generic:ObsValue value="0.7029125"/>
<generic:Attributes>
<generic:Value id="OBS_STATUS" value="A"/>
<generic:Value id="OBS_CONF" value="F"/>
</generic:Attributes>
</generic:Obs>
<generic:Obs
xmlns:generic="http://www.sdmx.org/resources/sdmxml/schemas/v2_1/data/generic">
<generic:ObsDimension value="1999-02"/>
<generic:ObsValue value="0.688505"/>
<generic:Attributes>
<generic:Value id="OBS_STATUS" value="A"/>
<generic:Value id="OBS_CONF" value="F"/>
</generic:Attributes>
</generic:Obs>
The following query takes the result of the query and extracts the desired information from the XML using Oracle syntax:
SELECT ab.currency waehrung
, TO_DATE(obs.obs_date, 'YYYY-MM') datum
, CASE WHEN SUBSTR(obs.obs_status,1 ,1) = 'A'
THEN TO_NUMBER(obs.obs_value, 'FM9999.99999999999999999')
END wechselkurs
, SUBSTR(obs.obs_status,1 ,1) status
FROM ${ws_response} r
, XMLTABLE(XMLNamespaces ( 'http://www.sdmx.org/resources/sdmxml/schemas/v2_1/message' AS "message"
, 'http://www.sdmx.org/resources/sdmxml/schemas/v2_1/common' AS "common"
, 'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"
, 'http://www.sdmx.org/resources/sdmxml/schemas/v2_1/data/generic' AS "generic"
),
'$d/message:GenericData/message:DataSet/generic:Series'
PASSING XMLTYPE(r.ws_response) AS "d"
COLUMNS dataset VARCHAR2(255 CHAR) PATH '@action'
, series XMLTYPE PATH '.'
) ar
, XMLTABLE(XMLNamespaces ('http://www.sdmx.org/resources/sdmxml/schemas/v2_1/data/generic' AS "generic")
,'$e/generic:Series' PASSING ar.series AS "e"
COLUMNS obs XMLTYPE PATH 'generic:Obs'
, attributes XMLTYPE PATH 'generic:Attributes'
, currency VARCHAR2(255 CHAR) PATH 'generic:Attributes/generic:Value[@id="UNIT"]/@value'
) ab
, XMLTABLE(XMLNamespaces ('http://www.sdmx.org/resources/sdmxml/schemas/v2_1/data/generic' AS "generic")
,'$f/generic:Obs' PASSING ab.obs AS "f"
COLUMNS obs_date VARCHAR2(255 CHAR) PATH 'generic:ObsDimension/@value'
, obs_value VARCHAR2(255 CHAR) PATH 'generic:ObsValue/@value'
, obs_status VARCHAR2(255 CHAR) PATH 'generic:Attributes/generic:Value[@id="OBS_STATUS"]/@value'
) obs