Importing On-Premises Oracle Data in to Google Spreadsheets

Combining on-premises data with cloud technologies almost always raises immediate concerns about security, but the DataDirect Hybrid Data Pipeline lets you securely access data behind any firewall without the requirement to make complex network configurations such as SSH tunnels, reverse proxies or VPNs. 

In this tutorial, you'll learn how to easily extract your on-premises Oracle data to Google Sheets using Progress Hybrid Data Pipeline and Google App Scripts. We will be using OData(REST) generated by Progress Hybrid Data Pipeline service to load the data to Google Sheets.

Combining on-premises data with cloud technologies almost always raises immediate concerns about security, but the DataDirect Hybrid Data Pipeline lets you securely access data behind any firewall without the requirement to make complex network configurations such as SSH tunnels, reverse proxies or VPNs. 

In this tutorial, you'll learn how to easily extract your on-premises Oracle data to Google Sheets using Progress Hybrid Data Pipeline and Google App Scripts. We will be using OData(REST) generated by Progress Hybrid Data Pipeline service to load the data to Google Sheets.

1. Install Hybrid Data Pipeline in your DMZ  or in the cloud by following the below tutorials.
   • For Google Cloud
   • For AWS
   • For Azure
   • For Digital Ocean
2. To connect to On-Premises databases, you need to install an On-Premises agent on one of your servers behind the firewall, that lets the Hybrid Data Pipeline Server communicate with the database.
3. To Install Hybrid Data Pipeline’s On-Premise Agent and configure it the cloud service where you installed Hybrid Data Pipeline Server, please follow the below tutorials.
   • If your Hybrid Data Pipeline Server is in Google Cloud
   • If your Hybrid Data Pipeline Server is in AWS
   • If your Hybrid Data Pipeline Server is in Azure
   • If your Hybrid Data Pipeline Server is in Digital Ocean
4. Also install the Hybrid Data Pipeline’s JDBC driver which can be found on the same download page of Hybrid Data Pipeline Server and On-Premise Connector. Follow this documentation to install the driver.
5. Once you have everything setup, navigate to http://<server-address>:8080/d2c-ui or https://<server-address>:8443/d2c-ui to view the Hybrid Data Pipeline’s UI.
6. Log in with the default credentials d2cadmin/d2cadmin
7. Once you have logged in, create a New DataSource, by clicking on New Data Source button as shown below.




8. You should now see list of all Data stores as shown below. Choose Oracle as your data store.




9. On the Configuration, fill out all the connection parameters that you would generally use to connect to your Oracle database and set the Connector ID. The Connector ID is the ID of the On-premises connector that you have installed for this server and account in Step – 3. If you have installed and configured the On-Premise connector, you should automatically see the Connector ID In drop down.




10. Now Click on Test Connection and you should now be able to connect to your Oracle database On-Premise.
11. Go to OData tab and click on “Configure Schema” button. You should now see list of schema’s in your database, select the schema that you want to access via OData.
12. Once you select the schema, you should now see all the tables in your schema as shown below. Select the tables that you would want to expose through OData (REST) API and click on Save button.




13. Now you should be back on OData config tab. Copy the OData Access URI and click on Update button to save all the configurations.
14. Paste the OData Access URI into a new tab to test it. Give your Hybrid Data Pipeline credentials when prompted for authentication and you should now see the response from the server listing your tables as shown below.

1. Install Hybrid Data Pipeline in your DMZ  or in the cloud by following the below tutorials.
   • For Google Cloud
   • For AWS
   • For Azure
   • For Digital Ocean
2. To connect to On-Premises databases, you need to install an On-Premises agent on one of your servers behind the firewall, that lets the Hybrid Data Pipeline Server communicate with the database.
3. To Install Hybrid Data Pipeline’s On-Premise Agent and configure it the cloud service where you installed Hybrid Data Pipeline Server, please follow the below tutorials.
   • If your Hybrid Data Pipeline Server is in Google Cloud
   • If your Hybrid Data Pipeline Server is in AWS
   • If your Hybrid Data Pipeline Server is in Azure
   • If your Hybrid Data Pipeline Server is in Digital Ocean
4. Also install the Hybrid Data Pipeline’s JDBC driver which can be found on the same download page of Hybrid Data Pipeline Server and On-Premise Connector. Follow this documentation to install the driver.
5. Once you have everything setup, navigate to http://<server-address>:8080/d2c-ui or https://<server-address>:8443/d2c-ui to view the Hybrid Data Pipeline’s UI.
6. Log in with the default credentials d2cadmin/d2cadmin
7. Once you have logged in, create a New DataSource, by clicking on New Data Source button as shown below.




8. You should now see list of all Data stores as shown below. Choose Oracle as your data store.




9. On the Configuration, fill out all the connection parameters that you would generally use to connect to your Oracle database and set the Connector ID. The Connector ID is the ID of the On-premises connector that you have installed for this server and account in Step – 3. If you have installed and configured the On-Premise connector, you should automatically see the Connector ID In drop down.




10. Now Click on Test Connection and you should now be able to connect to your Oracle database On-Premise.
11. Go to OData tab and click on “Configure Schema” button. You should now see list of schema’s in your database, select the schema that you want to access via OData.
12. Once you select the schema, you should now see all the tables in your schema as shown below. Select the tables that you would want to expose through OData (REST) API and click on Save button.




13. Now you should be back on OData config tab. Copy the OData Access URI and click on Update button to save all the configurations.
14. Paste the OData Access URI into a new tab to test it. Give your Hybrid Data Pipeline credentials when prompted for authentication and you should now see the response from the server listing your tables as shown below.

1. Download the Google Sheets OData reader  from GitHub.
2. Go to Google Sheets -> Create New Sheet.
3. From Menu bar -> Tools -> Script Editor and you should see a script editor where you can code.
4. Create the code files with same name in the Script Editor and copy the code to respective file.
5. Go back to the Google Sheet, and refresh the page. You should now see a new Menu Item named “Import On-Premises Data”




6. Click on Configure OData and you should see a window as shown below.




7. Fill in the OData URI, your Hybrid Data Pipeline’s Username and password from Step 14. Click on Submit.

    

8. You should now see another window listing all your tables as shown below.




9. Choose a table and click on Select. You should now see all of your on-premises data in the oracle table loaded up.

You can import any of your On-Premises data in any of your databases systems or Big Data systems using Progress DataDirect Hybrid Data Pipeline. Feel free to change the Google App Script Code per your needs and let us know if you have any questions or difficulties in getting this running.

1. Download the Google Sheets OData reader  from GitHub.
2. Go to Google Sheets -> Create New Sheet.
3. From Menu bar -> Tools -> Script Editor and you should see a script editor where you can code.
4. Create the code files with same name in the Script Editor and copy the code to respective file.
5. Go back to the Google Sheet, and refresh the page. You should now see a new Menu Item named “Import On-Premises Data”




6. Click on Configure OData and you should see a window as shown below.




7. Fill in the OData URI, your Hybrid Data Pipeline’s Username and password from Step 14. Click on Submit.

    

8. You should now see another window listing all your tables as shown below.




9. Choose a table and click on Select. You should now see all of your on-premises data in the oracle table loaded up.

You can import any of your On-Premises data in any of your databases systems or Big Data systems using Progress DataDirect Hybrid Data Pipeline. Feel free to change the Google App Script Code per your needs and let us know if you have any questions or difficulties in getting this running.