RESTful API is a popular protocol for exchanging data between different applications and systems especially in Web applications. Most programming languages support calling RESTful API(s) to query data quickly. Oracle Data Integrator supports RESTful services for reading and writing data.
In this tutorial, we are using Oracle Data Integrator to query data from Google Financial Service for stock prices of various companies, returning results to JSON files. Then we’ll import the JSON file into Autonomous Database. Let’s have a closer look at this process.
Table of contents
- What Needs to Be Done
- What is Oracle Data Integrator?
- Register an Account at RapidAPI
- Verify Yahoo Finance API
- How to Set up the Oracle Data Integrator RESTful Topology
- Create a Package
- Execute Package
- Conclusion
Suppose you want to get quotes of Oracle Corporation Company from Google:
What Needs to Be Done
- Provision an ADW instance
- Download the ADW Wallet file
- Create a database user in ADW
We explored this in our previous articles. Refer to the post about Provisioning an Oracle Autonomous Database for detailed information or if you want to refresh your knowledge.
Finally, you need to install an Oracle Data Integrator (version 12.2.1 or higher)
What is Oracle Data Integrator?
Oracle Data Integrator (ODI) is a comprehensive data integration platform that streamlines data movement and transformation between various systems. It is designed to work with heterogeneous data sources and targets, offering high-performance data integration and ETL (Extract, Transform, Load) capabilities. ODI simplifies complex integration tasks by using a declarative, metadata-driven approach, which allows developers to focus on business rules rather than low-level implementation details.
Register an Account at RapidAPI
RapidAPI is one of the popular API platforms that enables you to find and connect APIs.
You can use your Google Account to register a RapidAPI account. As Google’s Finance API is deprecated, it’s impossible to query Google’s Finance directly. Instead, you can use the Yahoo Finance API through the RapidAPI platform – it provides a lot of endpoints. Each of them covers a specific feature you can use. See the examples below:
- market/get-quotes: retrieve the quotes by symbols such as ORCL stands for Oracle Corporation (Stock Code)
- market/get-charts: get data to draw charts for a specific symbol and its comparisons
- stock/get-detail: get detailed information of each stock
- stock/get-histories: get stock histories to draw charts
Before using any API in Yahoo Finance, you need to subscribe to it by switching to the Pricing tab and choosing Personal plan.
Verify Yahoo Finance API
After you register and subscribe to Yahoo Finance API through the RapidAPI platform, verify one of the APIs using the Postman tool. Download and install the Postman app on your local machine.
On the Yahoo Finance API page, expand the market group and choose the API name market/v2/get-quotes
Click Code Snippets and choose Shell > cURL as shown below:
Click Copy Code to copy the cURL code, then paste it in the Postman app to verify this APID. After that, open the Postman app installed > click Import > Raw Text, and paste the code copied to the Textbox.
You can see the process below:
Replace the value of the symbols parameter with ORCL because you need the query data of Oracle Corporation only.
Click Continue, and Postman will support the parse. Then enter all the required info, such as Parameters and Headers of the request.
Click the Send button to submit your request. If its status is 200, it means the request returns the data to you, and you can save the file in JSON.
How to Set up the Oracle Data Integrator RESTful Topology
Open ODI Studio and log in to the ODI Work Repository.
In the Topology tab, expand Physical Architecture and Technologies, then right-click Restful Services and select New Data Server.
- Enter the name: GoogleFinanceService
- REST service endpoint URL: https://apidojo-yahoo-finance-v1.p.rapidapi.com
Right-click the GoogleFinanceService data server and select New Physical Schema.
In the Definition tab, enter /market/v2 in Resource URI. As soon as you did it, the requested string will be combined between the REST service endpoint URL and the Resource URI when submitted:
Switch to the Operations tab. Click Add Operation and set the values of the following parameters:
- Name: GetQuotes
- Method: GET
- Operation URI: get-quotes is a function provided by the API endpoint. You can check all functions in Yahoo Finance:
Query Parameter: Click the ellipsis icon, then click Add Parameter in the Edit Query Parameter popup window. Notice that you verified the API with the Postman application, the API endpoint string was https://apidojo-yahoo-finance-v1.p.rapidapi.com/market/v2/get-quotes.
You passed two parameters and the request string was
https://apidojo-yahoo-finance-v1.p.rapidapi.com/market/v2/get-quotes?region=US&symbols=ORCL
| region | US |
| symbols | ORCL |
You need to add the following parameters:
Header Parameter: whenever you request an API endpoint, you must be authorized and granted access to the API. There are various authorization methods, such as API Key, Bearer Token, Basic Auth, Digest Auth, OAuth 1.0 and OAuth 2.0, etc. With Yahoo Finance, APIs support the API Key method, and you can also get the API Key from RapidAPI.
Click the ellipsis icon, then click Add Parameter in the Edit Header Parameters popup window, and add the following parameters:
| x-rapidapi-host | apidojo-yahoo-finance-v1.p.rapidapi.com |
| x-rapidapi-key | c3c6046b36msh4a59522037c8567p179f4ajsna3382cf1d476 |
Let’s verify the service by clicking Test Resful Service. In the OdiInvokeRestfulService popup window, click the Send Request button. If you see the result in Response Content, your request is working correctly.
Click OK and Save.
Switch to the Context tab and add a context:
- Context: Global
- Logical Schema: google-finance-get-quote
You are querying the quotes data of Oracle Corporation with fixed values of parameters. In a real project, you can query the quotes data of any company on the Google Finance platform. To do this, you need to pass the dynamic values to the parameters of API so that you query data dynamically.
In the Topology tab, expand Technologies, RESTful Service, and GoogleFinanceService, then double-click GoogleFinanceService./market/v2
Switch to the Operations tab, then click Add Operation and add the following:
- Name: getQuoteDynamicTemplateValues
- Method: GET
- Query Parameter: Click the ellipsis icon, then click Add Parameter in the Edit Query Parameters dialog window, and add the following parameters:
| region | {region} |
| symbols | {symbols} |
Continue editing the Header Parameter as you did in the previous step:
| x-rapidapi-host | apidojo-yahoo-finance-v1.p.rapidapi.com |
| x-rapidapi-key | c3c6046b36msh4a59522037c8567p179f4ajsna3382cf1d476 |
Create a Package
Switch to the Designer tab and expand Projects, right-click Variables and select New Variable
- Enter name: PV_REGION
- Datatype: Text
Continue creating a new variable PV_SYMBOL.
These variables are essential for passing values to the parameters of API to query the data. The stock code you need when executing the package.
Create a new variable PV_QUOTE_RESPONSE_JSON that is used to save the response context to the JSON format. The file name is generated based on the PV_SYMBOL variable, so if you pass the ORCL value to PV_SYMBOL, the file is QUOTE_ORCL.json
Continue creating a new variable for tracing the file with the name PV_QUOTE_TRACE_FILE
In the Designer tab, under your Projects, right-click Packages and then select New Packages.
Enter the name: PK_GOOGLE_FINANCE_QUOTES
Drag and drop the PV_REGION variable into the package, then set the value of this variable to US.
Drag and drop the PV_REGION variable into the package. Then set the value of this variable to ORCL or any stock code.
Drag and drop the PV_QUOTE_RESPONSE_JSON variable into the package again, then set the type of this variable to Refresh and rename to Get Response Json File Name
Drag and drop the PV_QUOTE_TRACE_FILE variable into the package. Then set the type of this variable to Refresh and rename to Get Trace File Name
In Toolbox, select OdiInvokeRESTfulService, then click in the package map area to add it. Set the following parameter values:
- Step name: Get Google Finance Quote
- Context: Global
- Logical Schema: google-finance-get-quote
- Main Operation: GetQuoteDynamicTemplateValues
- Response File: /home/opc/#<your project>. PV_QUOTE_RESPONSE_JSON
- Append to Response File: No
- Trace File: /home/opc/#<your project>. PV_QUOTE_TRACE_FILE
- Append to Trace File: No
Depending on your environment, you need to replace the values of the Response File and Trace File accordingly.
Click the RequestTemplate tool parameter and set the following variables:
- region: #PV_REGION
- symbol: #PV_SYMBOL
Execute Package
Use-case 1: Query Quotes of Oracle
Make sure that you set the value of PV_SYMBOL to ORCL before executing the package. Let’s check the files generated:
Use-case 2: Query Quotes of Microsoft
Set the value of PV_SIMBOL to MSFT (Microsoft Corporation Stock Code)
Let’s continue checking the files generated:
Conclusion
RESTful Service is a useful component in Oracle Data Integrator. You can extract data from APIs in JSON format. In a real project, you will combine this service and another service of ODI that support loading JSON data into a database for the end-to-end data flow solution in ODI.
Tags: json, oracle data integrator, rest api Last modified: April 04, 2023
