Looker into Oracle Autonomous Database
In an earlier blogpost I showed how to use Looker’s Data Platform to query Snowflake. Because I have been an Oracle fan for years, it makes perfect sense to do something similar with Looker and the Oracle Autonomous Datawarehouse. In this post I outline the various steps to Looker into Oracle ADWC.
Oracle’s yearly Open World conference is always packed with announcements. This was the case this year as well. If you are interested in a summary of Analytics related events of this year’s OOW, you should check out these articles by; Francesco Tisiot and Bruno Aziza. One of the interesting announcements is; New Always Free Services. Free and Oracle. This is something you should check out. Now you are able to spin up your own Oracle Autonomous Data Warehouse Cloud instance, for free. Go and setup up your environment here and get yourself started here.
Setup the Oracle Autonomous Data Warehouse Environment
Following the tutorial via the linke above, you should be able to setup a demo environment in no time. To make things even easier I added some of the scripts on Github.
Oracle provides a tutorial to easily load some data into Oracle ADWC. For this blogpost, it’s enough to follow the first three steps.
- Provisioning Autonomous Data Warehouse Cloud
- Connecting SQL Developer and Creating Tables
- Loading Your Data
Find below references to files and scripts I used to setup Oracle ADWC.
- Create a user (ADWC_DEMO) in Oracle ADWC using the following script
- The Sales History (SH) tables can be created in the ADWC_DEMO schema via this script
- The above created SH-tables can be loaded via the Oracle Object Store. Find the data files in this archive
- To make sure the ADWC_DEMO user is able to connect to the Object Store. Use this script to view, create (or possibly delete) the Object Store Credential.
- Finally copy the SH-data from the object store to the ADWC_DEMO user in Oracle ADWC running the following script. Don’t forget to change the table names (if changed), region name, tenant name, bucket name, and file names (if changed) according to your needs.
Prepare Oracle Autonomous Data Warehouse (ADWC) for Looker
Now the Oracle ADWC environment is ready and loaded with data. Now it’s time prepare the Oracle ADWC to work with the Looker Data Platform. On the Looker website is a step-by-step. First you have to set up the Looker host for connections.
All the other steps in the document, can be performed following this script.
Connect to Oracle Autonomous Data Warehouse (ADWC) from Looker
Finally, after setting up de Looker host and preparing Oracle ADWC for Looker, it’s time to create the connection from Looker to Oracle ADWC. The result should be something like the below.
Now we are almost ready to query the Sales History (SH) tables from Looker. The only thing we have to do is configure the LookML. If the connection is ok and the LookML is valid, you can see the different SH tables. We can verify we have the same tables, with data available in Looker.
Querying Oracle Autonomous Data Warehouse (ADWC) for Looker
Now the setup is complete. We can setup a query and validate the results.
Basically this whole blogpost comes down to following the step-by-steps on both Oracle as well as Looker. To make things a little bit easier I stored a few of the scripts on Github. Give it a go yourself. If I can help, do not hesitate to get into contact.
Thanks for reading.
Originally published at https://daanalytics.nl on September 27, 2019.