Query Google’s Financial Service Using Oracle Data Integrator

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.

CodingSight - Oracle Data Integrator to query data from Google Financial Service for stock prices

Suppose you want to get quotes of Oracle Corporation Company from Google:

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)

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
Register an Account at RapidAPI

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:

Verify Yahoo Finance API

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:

Verification Process of Yahoo Finance API

Replace the value of the symbols parameter with ORCL because you need the query data of Oracle Corporation only.

Replacing the value of the symbols parameter with ORCL

Click Continue, and Postman will support the parse. Then enter all the required info, such as Parameters and Headers of the request.

Entering the required info

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.

request stubmission

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.

Set up the ODI RESTful Topology

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:

New Physical Schema

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

regionUS
symbolsORCL

 You need to add the following parameters:

adding 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.

Header parameter

Click the ellipsis icon, then click Add Parameter in the Edit Header Parameters popup window, and add the following parameters:

x-rapidapi-hostapidojo-yahoo-finance-v1.p.rapidapi.com
x-rapidapi-keyc3c6046b36msh4a59522037c8567p179f4ajsna3382cf1d476
Edit Header Parameters

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.

result in Response Content

Click OK and Save.

Switch to the Context tab and add a context:

  • Context: Global
  • Logical Schema: google-finance-get-quote
Switch to the Context tab and add a context

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}
Editing the Header Parameter

Continue editing the Header Parameter as you did in the previous step:

x-rapidapi-hostapidojo-yahoo-finance-v1.p.rapidapi.com
x-rapidapi-keyc3c6046b36msh4a59522037c8567p179f4ajsna3382cf1d476
Continue editing the Header Parameter

Create a Package

Switch to the Designer tab and expand Projects, right-click Variables and select New Variable

  • Enter name: PV_REGION
  • Datatype: Text
Create a Package

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

Create a new variable PV_QUOTE_RESPONSE_JSON

Continue creating a new variable for tracing the file with the name PV_QUOTE_TRACE_FILE

Continue creating a new variable for tracing the 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

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_REGION variable into the package

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_RESPONSE_JSON variable into the package again

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

 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
select OdiInvokeRESTfulService in Toolbox
  • 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.

replace the values of the Response File and Trace File

Click the RequestTemplate tool parameter and set the following variables:

  • region: #PV_REGION
  • symbol: #PV_SYMBOL
Click the RequestTemplate tool parameter

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:

Query Quotes of Oracle

Use-case 2: Query Quotes of Microsoft

Set the value of PV_SIMBOL to MSFT (Microsoft Corporation Stock Code)

Query quotes of Microsoft

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.

Dung Dinh

Dung Dinh

BI Specialist, Data Modelling, working as Oracle Consultant in Oracle Cloud.