How To
Summary
Fetching data from JSON-based Restful service is supported in Federation Server via Autonomous REST Connector.
When user wants to query the data from RESTful service, follow the below steps:
1. Understand the RESTful request method parameters and the format of the response;
2. Customize the .rest file to configure the RESTful service connection;
3. Create the Federated JDBC Wrapper, Server, User Mapping, and Nickname, and then query the data.
Objective
Query the data from JSON-based RESTful service via JDBC connection in Federation Server
Environment
Linux; AIX
Steps
In Federation, fetching data from JSON-based RESTful service is supported via Autonomous REST Connector.
The connector supports several authentication methods which described in the link above.
It takes the non-authentication as an example in this article which queries earthquake data from the public Restful service earthquake.usgs.gov.
1. Understand the RESTful request method parameters and the format of the response.
When querying earthquake data from earthquake RESTful service, it needs to call "query" method according to descriptions here.
a> Investigate the parameters.
According to description, it supports many parameters for "query", such as "format", "starttime", "endtime", "limit", "reviewstatus", and so on. The "geojson" is the right "format" Federation supports.
b> Investigate the format of response.
The details of earthquake data to query is described here, such as "place", "mag", "sig", and so on are in "FEATURES" JSON-array.
When querying with statement in Federation, it queries data from normalized table of response.
The mapping between normalized table and JSON objects is here.
2. Configure the .rest file based on investigation in step 1>
Sample and REST file are two ways to access remote Restful service in connector. It takes REST file as an example in this article.
According to investigation, the REST file "autorest.rest" is configured to fetch earthquake data in 2018:
{
// root table name defined for Restful request(s)
"tab1":{
// 1. Define Restful service URL(s)
"#path":[
"https://earthquake.usgs.gov/fdsnws/event/1/query"
],
// 2. define the virtual columns of Restful request parameters
// If your Restful API has no parameter, please ignore the definition below
"format": {
"#type": "VarChar(10),#key",
"#virtual": true,
"#eq": "format",
"#default": "geojson"
},
"limit":{
"#type": "integer,#key",
"#virtual": true,
"#eq": "limit",
"#default": "20000"
},
"starttime": {
"#type": "VarChar(12),#key",
"#virtual": true,
"#eq": "starttime",
"#default": "2018-06-01"
},
"endtime": {
"#type": "VarChar(12),#key",
"#virtual": true,
"#eq": "endtime",
"#default":"2018-06-30"
},
"reviewstatus": {
"#type": "VarChar(10),#key",
"#virtual": true,
"#eq": "reviewstatus",
"#default": "automatic"
},
//3. define columns in "metadata" object for table "tab1"(root table)
// Define 3 "metadata" columns of "tab1" which user wants to query only
"metadata":{
"status":"integer",
"title":"varchar(25)",
"api":"varchar(10)"
},
//4. define columns for child-table features of response object
// Define 6 columns of features which user wants to query only.
"features[]": {
"properties":{
"mag": "Double",
"place": "VarChar(108)",
"time": "bigint",
"updated": "bigint",
"status": "VarChar(12)",
"sig": "integer"
}
}
}
}
3. Create the Federated JDBC Wrapper, Server, User Mapping, and Nickname, and then query the data.
create wrapper jdbc
DB20000I The SQL command completed successfully.
create server earthquake wrapper JDBC options(DRIVER_PACKAGE '/home/db2inst1/sqllib/federation/jdbc/lib/FOautorest.jar', driver_class 'com.ibm.fluidquery.jdbc.autorest.AutoRESTDriver', url 'jdbc:ibm:autorest:Config=/home/db2inst1/workspace/autorest/autorest_pushdown.rest;createMap=forceNew;InsensitiveResultSetBufferSize=4096;')
DB20000I The SQL command completed successfully.
-- If no credential is needed for Resftull call, you can set any values for remote_authid and remote_password
create user mapping for db2inst1 server earthquake options(remote_authid 'userXXX', remote_password 'passwordXXX')
DB20000I The SQL command completed successfully.
-- Query data from features(the child-table of normalized response JSON object)
create nickname features for earthquake.autorest.FEATURES
DB20000I The SQL command completed successfully.
describe table features
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
TAB1_FORMAT SYSIBM VARCHAR 10 0 No
TAB1_LIMIT SYSIBM INTEGER 4 0 No
TAB1_STARTTIME SYSIBM VARCHAR 12 0 No
TAB1_ENDTIME SYSIBM VARCHAR 12 0 No
TAB1_REVIEWSTATUS SYSIBM VARCHAR 10 0 No
POSITION SYSIBM INTEGER 4 0 No
PROPERTIES_MAG SYSIBM DOUBLE 8 0 Yes
PROPERTIES_PLACE SYSIBM VARCHAR 108 0 Yes
PROPERTIES_TIME SYSIBM BIGINT 8 0 Yes
PROPERTIES_UPDATED SYSIBM BIGINT 8 0 Yes
PROPERTIES_STATUS SYSIBM VARCHAR 12 0 Yes
PROPERTIES_SIG SYSIBM INTEGER 4 0 Yes
12 record(s) selected.
select PROPERTIES_STATUS,PROPERTIES_MAG, PROPERTIES_SIG, PROPERTIES_PLACE from features where Tab1_REVIEWSTATUS= 'reviewed' limit 5
PROPERTIES_STATUS PROPERTIES_MAG PROPERTIES_SIG PROPERTIES_PLACE
----------------- ------------------------ -------------- ------------------------------------------------------------------------------------------------------------
reviewed +7.00000000000000E-001 8 23km SSE of Gardnerville Ranchos, Nevada
reviewed +8.00000000000000E-001 10 69km SSW of Kobuk, Alaska
reviewed +8.40000000000000E-001 11 9km SW of Borrego Springs, CA
reviewed +7.80000000000000E-001 9 9km NE of Aguanga, CA
reviewed +8.40000000000000E-001 11 41km W of Tanaga Volcano, Alaska
5 record(s) selected.
-- query values from root table "tab1"
create nickname tab1 for earthquake.autorest.tab1
DB20000I The SQL command completed successfully.
describe table tab1
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
FORMAT SYSIBM VARCHAR 10 0 No
LIMIT SYSIBM INTEGER 4 0 No
STARTTIME SYSIBM VARCHAR 12 0 No
ENDTIME SYSIBM VARCHAR 12 0 No
REVIEWSTATUS SYSIBM VARCHAR 10 0 No
METADATA_STATUS SYSIBM INTEGER 4 0 Yes
METADATA_TITLE SYSIBM VARCHAR 25 0 Yes
METADATA_API SYSIBM VARCHAR 10 0 Yes
8 record(s) selected.
select * from tab1
FORMAT LIMIT STARTTIME ENDTIME REVIEWSTATUS METADATA_STATUS METADATA_TITLE METADATA_API
---------- ----------- ------------ ------------ ------------ --------------- ------------------------- ------------
geojson 20000 2018-06-01 2018-06-30 automatic 200 USGS Earthquakes 1.10.3
1 record(s) selected.
Generally, the full data is fetched from remote Restful service, and filtering is done by Federation local filter.
In current example, the Restful method "query" has parameter "reviewstatus" to filter events with a specific review status; So, the performance of querying data from features table above is optimized by configuring "reviewstatus" virtual column in rest file which can pushdown the predicate "reviewstatus" to remote Restful service.
The details about how to configure virtual columns to improve query performance is here.
Document Location
Worldwide
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PkvAAE","label":"Federation"}],"ARM Case Number":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"}],"Version":"11.5.0"}]
Was this topic helpful?
Document Information
Modified date:
26 February 2021
UID
ibm16407806