Setting up the Looker host for connections

All Oracle ADWC connections require SSL and certificate authentication. In order for Looker to connect to your Oracle ADWC instance, it is necessary to download your Oracle wallet files and install them on the Looker server. If you are a customer-hosted Looker user, you will need a system administrator with access to the Looker server to do this. If you are a Looker-hosted user, reach out to Looker Support.

To install your Oracle wallet on the Looker server:

Download your Oracle wallet to your local computer. You will have a zip file named something like Wallet_databasename.zip.
On the Looker server, make a directory to hold the wallet zip file:
mkdir /home/looker/looker/credentials
Copy the wallet zip file from your local computer to the Looker server. This example uses scp and places the file in /home/looker/looker/credentials:
scp Wallet_databasename.zip username@remotehost:/home/looker/looker/credentials
Unzip the wallet zip file. This example uses the command unzip:
cd /home/looker/looker/credentials unzip Wallet_databasename.zip
Verify the contents of the wallet with the ls command. These are the files you should have:
```
ls

cwallet.sso  keystore.jks      sqlnet.ora    truststore.jks
ewallet.p12  ojdbc.properties  tnsnames.ora
```
Looker connects to Oracle ADWC using Oracle Wallets with the JDBC Thin Driver 18.3. For this, you will need the Transparent Network Substrate (TNS) Alias of the Oracle ADWC Service level for your database and the PATH to your Oracle wallet files.
To get the TNS Alias of your database, run this command:
cat tnsnames.ora

There will be three TNS aliases to choose from: dbname_high, dbname_medium, and dbname_low. These aliases correspond to different levels of service. The protocol, host, port, service name, and SSL information is included in this file. For this example, we will use dbname_medium.

Creating a Looker user

First, create a designated Looker user:

-- connect / as sysdba; CREATE USER LOOKER IDENTIFIED BY <password> DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

Next, give the new Looker user the ability to create sessions:

GRANT CREATE SESSION TO LOOKER;

Finally, give the Looker user the appropriate SELECT permissions for the data tables you plan to access from Looker. If you want to access additional tables in the future, you will need to grant SELECT on those new tables as well.

GRANT SELECT ON -- <all tables that will be used by looker>;

Ensuring Looker can see all tables

Looker may not be able to identify tables (especially empty tables) without first collecting statistics in Oracle. If tables you need do not appear in generated LookML or SQL Runner, execute this command:

EXEC DBMS_STATS.GATHER_DATABASE_STATS;

For alternative methods, consult your Oracle documentation.

Setting up main database objects

Your Oracle DBA must set up the following objects and permissions on Oracle. The following commands create LOOKER_SESSION and LOOKER_SQL as synonyms for V$SESSION and V$SQL.

Run the following commands as the root user to complete this setup. These examples assume that the Looker user’s name is LOOKER.

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; -- Pay special attention to the comments below: -- the following view will be different for clustered Oracle deployments CREATE OR REPLACE VIEW LOOKER_SESSION AS SELECT SID, USERNAME, TYPE, STATUS, SQL_ID, -- If using a single node Oracle ADWC deployment "SERIAL#", -- If using a clustered Oracle ADWC deployment (SERIAL# || ',' || INST_ID) AS "SERIAL#", AUDSID -- If using a single node Oracle ADWC deployment FROM V$SESSION -- If using a clustered Oracle ADWC deployment FROM GV$SESSION WHERE USERNAME='LOOKER'; CREATE OR REPLACE SYNONYM LOOKER.LOOKER_SESSION FOR LOOKER_SESSION; GRANT SELECT ON LOOKER.LOOKER_SESSION TO LOOKER;

Setting up symmetric aggregates

Your Oracle DBA must set up the LOOKER_HASH function to enable symmetric aggregates. The LOOKER_HASH function is a synonym for the Oracle dbms_crypto.hash function. The DBA must also create the associated synonym and privileges. The following commands assume that the Looker user’s name is LOOKER:

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 SYS.LOOKER_HASH TO LOOKER;

Depending on your Oracle database configuration, the SYS prefix may be SYSDBA, ADMIN, or unnecessary.

Setting up persistent derived tables

In order to enable Persistent Derived Tables, give the Looker user the UNLIMITED TABLESPACE and CREATE TABLE permissions. The following commands assume that the Looker user’s name is LOOKER:

GRANT UNLIMITED TABLESPACE TO LOOKER; GRANT CREATE TABLE TO LOOKER;

Setting up query killing

To set up query killing, the Oracle DBA must create the LOOKER_KILL_QUERY procedure as a synonym of ALTER SYSTEM KILL SESSION. To do this, execute the following command:

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 -- If using a single node Oracle ADWC deployment FROM V$SESSION -- If using a clustered Oracle ADWC deployment FROM GV$SESSION WHERE username = USER AND sid = p_sid -- If using a single node Oracle ADWC deployment AND serial# = p_serial#; -- If using a clustered Oracle ADWC deployment AND (SERIAL# || ',' || INST_ID) = 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;

The DBA will also need to run these related commands:

CREATE OR REPLACE SYNONYM LOOKER.LOOKER_KILL_QUERY FOR SYS.LOOKER_KILL_QUERY; GRANT EXECUTE ON SYS.LOOKER_KILL_QUERY TO LOOKER;

Depending on your Oracle database configuration, the SYS prefix may be SYSDBA, ADMIN, or unnecessary.

Adding the connection

In the Admin section of Looker, select Connections, and then select New Connection.

Fill out the connection details. The majority of these settings are common to most database dialects, and are described on the Connecting Looker to your database documentation page. The following settings are specific to Oracle ADWC or to the example Connections Settings page shown above:

Dialect: Oracle ADWC.
Use TNS: Enable Transparent Network Substrate (TNS) connections.
Host: Hostname or TNS alias. For this example, dbname_medium.
Port: Leave as default; Looker will find the port from the tnsnames.ora file.
Service Name: Leave blank; Looker will find the service name from the tnsnames.ora file.
Username: Database username or Temp Database if PDTs are enabled.
Password: Database user password.
Persistent Derived Tables: Check this box to enable persistent derived tables. This reveals additional PDT fields and the PDT Overrides column.
Temp Database: In Oracle a user is a schema, so this should be specified as the name of the database user. For this example, we would use the temp schema value LOOKER.
Additional Params: The PATH to your Oracle wallet on the Looker server. For this example it is /home/looker/looker/credentials.
- On a Looker-hosted legacy deployment, this value will be /home/lookerops/looker/credentials.
- On a Looker-hosted deployment in next-generation hosting, this value will be /app/credentials.
SSL and Verify SSL Cert: You can ignore these fields; Looker will always use SSL with Oracle ADWC.

When you click Test These Settings, Looker will build a JDBC string like this:

jdbc:oracle:thin:@dbname_medium?TNS_ADMIN=/home/looker/looker/credentials

Feature support

For Looker to support some features, your database dialect must also support them.

In Looker 22.6, Oracle Autonomous Data Warehouse on Cloud supports the following Looker features:

Next steps

After you have completed the database configuration, connect to the database from Looker.

Articles in this section