Earlier in the week I wrote-up my first impressions of Oracle Autonomous Data Warehouse Cloud (ADWC) on this blog, and said at the end I’d follow with another post on how to connect ADWC to Looker, the cloud-based BI tool I usually query Google BigQuery with in my role as Analytics Product Manager at Qubit.

For this initial example I uploaded a few tables of workout and other data into ADWC using SQL Developer, exporting the relevant tables out of BigQuery in CSV format and then uploading them into a user account I created for the purpose in AWDC. The table I’ll concentrate on for this example is the STRAVA_RIDES table, each row detailing an individual cycle workout as recorded through the Strava smartphone app.

The first step in connecting-up Looker to ADWC, or indeed any Oracle Database, is to run some scripts that set up Oracle as Looker expects to find it. Unlike most other BI tools I’ve used with Oracle, Looker expects to connect through a specific user login (“LOOKER”) that is then granted SELECT access to any tables and views you want to report on in other schemas. This user login also needs to have some views and synonyms created to give it access to the V$ system views within Oracle that report on active sessions, and an ability to kill long-running sessions through a PL/SQL package that calls ALTER SYSTEM … KILL SESSION.

The commands to run for regular Oracle databases are detailed on the Looker website but you need to alter them slightly to use ADWC’s superuser account name (“ADMIN”) instead of SYS when initially connecting and when creating the LOOKER_HASH function for symmetric aggregate handling, along with various other changes due to differences in how various objects are named in AWDS vs. regular Oracle Database.

I’ve listed the commands I ran on my ADWC instance below, they should work for you but if not then check out the “Autonomous Data Warehouse Cloud for Experienced Oracle Database Users” section in Using Autonomous Data Warehouse Cloud that explains the differences between the new autonomous and regular Oracle Database server versions.

First create the Looker account and grant the relevant roles and priviledges:

connect ADMIN/<<your_admin_password>>

create user LOOKER identified by <<new_looker_account_password>>;

alter user LOOKER

default tablespace DATA

temporary tablespace TEMP

account unlock;

alter user LOOKER quota unlimited on DATA;

alter user LOOKER default role RESOURCE;

grant CREATE SESSION to LOOKER;

GRANT UNLIMITED TABLESPACE TO LOOKER;

GRANT CREATE TABLE TO LOOKER;

grant select on -- <all tables that will be used by looker>;

Now create the views that Looker uses to understand what sessions are active and the SQL that’s currently being executed to provide data for looks and dashboard tiles:

create or replace view LOOKER_SQL

as

select SQL.SQL_ID, SQL.SQL_TEXT

from V$SQL sql ,v$session sess

where SESS.SQL_ADDRESS = SQL.ADDRESS

and SESS.USERNAME='LOOKER';

create or replace synonym LOOKER.LOOKER_SQL for LOOKER_SQL;

grant select ON LOOKER.LOOKER_SQL to LOOKER;

create or replace view LOOKER_SESSION as

SELECT SID, USERNAME, TYPE, STATUS, SQL_ID, "SERIAL#", AUDSID

FROM V$SESSION

WHERE USERNAME='LOOKER';

create or replace synonym LOOKER.LOOKER_SESSION FOR LOOKER_SESSION;

GRANT SELECT ON LOOKER.LOOKER_SESSION TO LOOKER;

Next, create the Oracle PL/SQL function that Looker uses as part of symmetric aggregate handling, and a function that Looker can use to “kill” runaway database queries that are taking too long to return results back to you.

create or replace function LOOKER_HASH(bytes raw, prec number) return raw as

begin

return(DBMS_CRYPTO.HASH(bytes, prec));

end;

create or replace synonym LOOKER.LOOKER_HASH for LOOKER_HASH;

grant execute on LOOKER.LOOKER_HASH to LOOKER;

grant execute on ADMIN.LOOKER_HASH to LOOKER;

create or replace procedure LOOKER_KILL_QUERY(P_SID in VARCHAR2,

P_SERIAL# in VARCHAR2)

is

CURSOR_NAME pls_integer default dbms_sql.open_cursor;

IGNORE pls_integer;

begin

select COUNT(*) into IGNORE

from V$SESSION

where USERNAME = USER

and SID = P_SID

and SERIAL# = P_SERIAL#;

if (IGNORE = 1)

then

dbms_sql.parse(CURSOR_NAME,

'alter system kill session '''

|| P_SID || ',' || P_SERIAL# || '''',

dbms_sql.native);

IGNORE := dbms_sql.execute(CURSOR_NAME);

else

raise_application_error(-20001,

'You do not own session ''' ||

P_SID || ',' || P_SERIAL# ||

'''');

end if;

end;

create or replace synonym LOOKER.LOOKER_KILL_QUERY

for ADMIN.LOOKER_KILL_QUERY;

grant execute on ADMIN.LOOKER_KILL_QUERY to LOOKER;

Next over to the Looker configuration. You’ll need to be on the Looker 5.12.12 or higher release with an instance hosted in the US to get the integration working as of the time of writing so that “ADWC” is listed as a connection type and the ADWC wallet integration works; if you’re running Looker as a hosted instance you’ll also need to speak with support to have them copy across the wallet files to the correct location on the Looker server.

To create the connection, enter the following details:

Name : Name of your connection, e.g. “rittman_adwc”
Dialect : Oracle ADWC (only appears with Looker 5.12.12+)
Host:Port : from the TNSNAMES.ORA file in your ADWC wallet zip file
Username : LOOKER (as per the account setup in previous steps)
Password : password of LOOKER account
Temp Database : LOOKER (as per previous steps)
Persistent Derived Tables : checked
Service Name : From TNSNAMES.ORA in your ADWC wallet zip file
Additional Database Params : TNSNAMES.ORA SSL Server Cert DN

To show the Service Name and Additional Database Params fields you first have to save the connection, then tick the “Use TNS” checkbox to reveal the fields. To find your host:port, service name and SSL Server Cert DN values first download the wallet zip file for your ADWC instance from the ADWC Service Console, unzip the archive and then locate the details you need in the TNSNAMES.ORA file as shown below. In my case I chose to use the “medium” ADWC instance type for my connection settings.

Then, save and test your connection. The step that checks that persistent derived tables will probably fail if you try this around the time of my writing as there’s a known bug in the step that checks this feature, it’ll no doubt be fixed soon but if the rest of the checks pass you should be good.

Finally, it’s just then a case of importing your table metadata into Looker and creating explores and a model as you’d do with any other data source, like this:

In this instance I’ve updated the Strava Rides LookML view to turn the relevant metric fields into measures, define a primary key for the view and remove or hide fields that aren’t relevant to my analysis, like this:

Now I can start to analyze my Strava workout data I previously uploaded to Oracle Autonomous Data Warehouse, starting with average cadence and speed along with total distance and Strava’s “suffer score” for each of my workouts:

and then looking to see how much correlation there is between distance and personal strava records being broken on my five longest rides.

In the background, Looker is sending Oracle and ADWC-specific SQL to Oracle Autonomous Data Warehouse Cloud, with the SQL tab in the Explore interface showing me the actual SQL for each query as its sent.

Should I wish to check how much of the storage and CPU capacity available for my ADWC instance is being used, I can do this from ADWC’s Service Console.

So there you have it — Looker powered by an Oracle Autonomous Data Warehouse Cloud, and no need for an Oracle DBA to get it all running for you.

Connecting Looker to Oracle Autonomous Data Warehouse Cloud

Updates to Oracle Analytics Cloud, Oracle BIEE 12c and Oracle DV Desktop

First Impressions of Oracle Autonomous Data Warehouse Cloud