Running SQL Statements in
Oracle Machine Learning
[Edition 2]
[Last Update 190713]
1 [email protected]
Contents
1 Introduction................................................................................................................................................... 3
2 Documentation ............................................................................................................................................. 4
2.1 Oracle Documentation ........................................................................................................................................ 4
3 Creating OML Users ..................................................................................................................................... 5
4 Exploring the OML Home Page ................................................................................................................. 9
5 Running a SQL Statement ........................................................................................................................ 11
6 Saving the Scratchpad as a New Notebook ......................................................................................... 16
7 Sharing notebooks .................................................................................................................................... 18
8 Accessing shared notebooks .................................................................................................................. 21
9 Creating and running SQL scripts ......................................................................................................... 23
10 SUMMARY ................................................................................................................................................ 29
2 [email protected]
1 INTRODUCTION
This activity guide covers Running SQL Statements in Oracle Machine Learning.
Pre-Requisite: AG2_Provisioning_Autonomous_Data_Warehouse
This guide covers,
• how to create OML Users
• how to run a SQL Statement
• how to share notebooks
• how to create and run SQL scripts
Oracle Machine Learning (OML) SQL notebook application provided with your Autonomous Data
Warehouse. This browser-based application provides a web interface to run SQL queries and
scripts, which can be grouped together within a notebook. Notebooks can be used to build single
reports, collections of reports, and even dashboards. OML provides a simple way to share
workbooks, and collections of workbooks, with other OML users.
3 [email protected]
2 DOCUMENTATION
2.1 Oracle Documentation
1. Autonomous Data Warehouse
https://docs.cloud.oracle.com/iaas/Content/Database/Concepts/adwoverview.htm
2. Creating an Autonomous Data Warehouse
https://docs.cloud.oracle.com/iaas/Content/Database/Tasks/adwcreating.htm
3. Connecting with Oracle SQL Developer (earlier than Version 18.2)
https://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/adwc/obe_connecting_s
ql_developer_to_autonomous_data_warehouse_and_creating_tables/connecting_sql_dev_
to_adw_and_creating_tables.html
4. SQL Developer 18.3
https://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
5. Loading Your Data Into Autonomous Data Warehouse
https://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/adwc/OBE_Loading%20
Your%20Data/loading_your_data.html
6. Managing and Monitoring Performance of Autonomous Data Warehouse
https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/manage-
service.html#GUID-759EFFFA-9FAC-4439-B47F-281E470E01DE
7. Oracle Machine Learning
https://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/oml/OMLPW-create-project-
workspace/html/index.html
4 [email protected]
3 CREATING OML USERS
1. Go to the Cloud Console and open the Instances screen. Find your database, click & Select
Service Console.
2. Go to the Administration tab and click Manage Oracle ML Users to go to the OML user
management page - this page will allow you to manage OML users.
3. Click Create button to create a new OML user.
Note: This will also create a new database user with the same name. This newly created user
will be able to use the OML notebook application.
5 [email protected]
Note: You can also enter an email address to send an email confirmation to your user (for this
lab you can use your own personal email address) when creating the user.
4. Enter the required information for this user, name the user as omluser1. If you supplied a valid
email address, a welcome email should arrive within a few minutes to your Inbox. Click the
Create button, in the top-right corner of the page, to create the user.
5. Below is the email which each user receives welcoming them to the OML application. It
includes a direct link to the OML application for that user which they can bookmark. Click on
Access Oracle ML SQL notebook to access.
6 [email protected]
6. You will be redirected to OCI to reset the Password.
7. Once the user is created , you will be to see the user under users tab.
7 [email protected]
Note: Make sure you verified the users from mail, then only you will see the status of that user
to open else it will be expired.
8. Using the same steps, create another user named omluser2.
In this section we have successfully created two users .
8 [email protected]
4 EXPLORING THE OML HOME PAGE
1. Signing into OML using the link from your welcome email, from Oracle Global Accounts, you
can now sign-in to OML. Copy and paste the application link from the email into your browser
and sign-in to OML.
Note: If you have not specified an email address you can click the Home icon on the top right of
Oracle Machine Learning User administration page to go to OML home page.
2. Use your new user account omluser1, which we have created In section 3
Note: that you do not have to go to this page using the same steps every time, you can
bookmark this Oracle ML Notebook Admin URL and access it directly later.
3. Once you have successfully signed in to OML the application home page will be displayed.
9 [email protected]
4. Overview of OML Home Page
• The grey menu bar at the top of the screen provides links to the main OML menus for the
application (left corner) and the workspace/project and user maintenance on the right-hand
side.
Note: All your work is automatically saved – i.e. there is no “Save” button when you are writing
scripts and/or queries.
5. Understanding the Key concepts
• Workspace: A workspace is an area where you can store your projects. Each workspace
can be shared with other users so they can collaborate with you. For collaborating with other
users, you can provide different levels of permission such as Viewer, Developer and
Manager – these will be covered in more detail later in this lab. You can create multiple
workspaces.
• Project: A project is a container for organizing your notebooks. You can create multiple
projects.
• Notebook: A notebook is a web-based interface for building reports and dashboards using a
series of pre-built data visualizations which can then be shared with other OML users. Each
notebook can contain one or SQL queries and/or SQL scripts. Additional non-query
information can be displayed using special markdown tags (an example of these tags will be
shown later).
In this section we have successfully overview the OML Page and its various Components
10 [email protected]
5 RUNNING A SQL STATEMENT
1. From the home page click on the “Run SQL Statement” link in the Quick Actions panel to
open a new SQL query scratchpad.
2. The following screen should appear:
The white panel below the main title (SQL Query Scratchpad – this name is automatically
generated) is an area known as “paragraph”. Within a scratchpad you can have multiple
paragraphs. Each paragraph can contain one SQL statement or a SQL script.
3. In the SQL paragraph area copy and paste this code snippet. Your screen should now look like
this:
SELECT
p.prod_category_desc,
t.calendar_year as year,
t.calendar_month_desc as Month,
TRUNC(SUM(amount_sold)) as revenue,
TRUNC(AVG(SUM(amount_sold)) over (PARTITION BY t.calendar_year ORDER BY
p.prod_category_desc, t.calendar_month_desc ROWS 2 PRECEDING)) as avg_3M_revenue,
TRUNC(AVG(SUM(amount_sold)) over (ORDER BY p.prod_category_desc,
t.calendar_month_desc ROWS 5 PRECEDING)) as avg_6M_revenue,
TRUNC(AVG(SUM(amount_sold)) over (ORDER BY p.prod_category_desc,
t.calendar_month_desc ROWS 11 PRECEDING)) as avg_12M_revenue
FROM sh.sales s, sh.times t, sh.products p
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND prod_category_desc = 'Electronics'
11 [email protected]
GROUP BY p.prod_category_desc, t.calendar_year, calendar_month_desc
ORDER BY p.prod_category_desc, t.calendar_year, calendar_month_desc;
4. Press the icon shown in the red box to execute the SQL statement.
5. It will display the results in a tabular format:
6. Changing the report type
• Using the report menu bar you can change the table to a graph and/or export the result set to
a CSV or TSV file.
12 [email protected]
• When you change the report type to one of the graphs, then a Settings link will appear to the
right of the menu which allows you to control the layout of columns within the graph.
• Click on the bar graph icon to change the output to a bar graph (see below).
• Click on the Settings link to unfold the settings panel for the graph.
• To add a column to one of the Keys, Groups of Values panels just drag and drop the column
name into the required panel.
• To remove a column from the Keys, Groups of Values panel just click on the x next to the
column name displayed in the relevant panel.
7. Changing the layout of the graph
With the graph settings panel visible:
13 [email protected]
• Remove all columns from the both the Keys and Values panels.
• Drag and drop MONTH into the Keys panel
• Drag and drop REVENUE into the Values panel
• Drag and drop AVG_12M_REVENUE into the Values panel
The report should now look like the one shown below.
8. Tidying up the report
• Click on the Settings link to hide the layout controls.
• Click on the Hide editor button which is to the right of the "Run this paragraph" button.
14 [email protected]
9. Now only the output is visible.
In this Section we have successfully run a SQKL Statement
15 [email protected]
6 SAVING THE SCRATCHPAD AS A NEW NOTEBOOK
The SQL Scratchpad in the previous section is simply a default type notebook with a system
generated name. But we can change the name of the scratchpad we have just created SQL
Query Scratchpad.
1. Click on the Back link in the top left corner of the Scratchpad window to return to the OML
home page.
2. Click on Notebooks in the Quick Actions panel
3. The Notebooks page will be displayed:
4. Let’s rename our SQL Scratchpad notebook to something more informative. Click on text in the
comments column to select the scratchpad so we can rename it. After you click, the “SQL
Query Scratchpad” will become selected and the menu buttons above will activate.
16 [email protected]
5. Click on the Edit button to pop-up the settings dialog for this notebook and enter the information
as shown in the image below (note that the connection information is read-only because this is
managed by Autonomous Data Warehouse):
6. Click OK to save your notebook. You will see that your SQL Query Scratchpad notebook is now
renamed to the new name you specified.
In this section we have successfully saved the Scratchpad as a New Notebook
17 [email protected]
7 SHARING NOTEBOOKS
1. Logging into OML as the second OML (OMLUSER2) user which we have created in Section 3
• By default, when you create a notebook it’s only visible to you.
• To make it available to other users you need to share the workspace containing the
notebook. You can create new workspaces and projects to organize your notebooks for ease
of use and to share with other users.
• To demonstrate the sharing process let’s begin by logging in to OML as our second OML
(OMLUSER2) user and checking if any notebooks are available.
• Click on your user name in the top right corner (OMLUSER1) and select “Sign Out”.
2. Now sign-in as OML user OMLUSER2 using the password that you have created in Section 3
3. Notice that you have no activity listed in the Recent Activities panel on your OML home page
and you don’t have any notebooks.
18 [email protected]
Hint – click on the Notebooks link in the Quick Actions panel:
Repeat the previous steps to logout of OML and sign into OML as OMLUSER1.
4. Changing Workspace Permissions.
• From the OML home page, click on link OML Project (OML Workspace) link in the top right
corner on the OML home page to display the workspace-project menu. Then select
Workspace Permissions.
• The permissions dialog box will appear (see below).
o In the dialog box next to the Add Permissions text type OMLUSER2 (use uppercase).
o Set the permission type to Viewer (this means read-only access to the workspace, project
and notebook).
19 [email protected]
• Note:
o A “Developer” would have read-only access to the workspace, project but could add new
notebooks, update and delete existing notebooks and schedule jobs to refresh a
notebook.
o A “Manager” would have read-only access to the workspace, can create update and
delete projects, add new notebooks, update and delete existing notebooks and schedule
jobs to refresh a notebook.
Click the Add button to add the user OMLUSER2 as a read-only viewer of the workspace. Your
form should look like this:
Finally, click the OK button.
In this section we have successfully Shared the Notebooks to other OML users
20 [email protected]
8 ACCESSING SHARED NOTEBOOKS
1. Now repeat the process you followed at the start of this section and sign-out of OML and sign-in
to OML again as user OMLUSER2.
2. First thing to note is that the Recent Activities panel below the Quick Links panel now shows all
the changes user OMLUSER1 made within the workspace OML-Workspace.
3. As user OMLUSER2 you can now run the Sales Analysis Over Time notebook by clicking on
the blue-linked text in the Recent Activities panel (note that your recent activity will be logged
under the banner labelled “Today”).
4. The notebook will now open:
21 [email protected]
In this Section we have successfully accessed Shared notebooks
22 [email protected]
9 CREATING AND RUNNING SQL SCRIPTS
1. Log out from user OMLUSER2 and log in as OMLUSER1. The “Run SQL Statement” link on
the home page allows you to run a single query in a paragraph. To be able to run scripts you
can use the “Create a SQL Script” link on the home page.
2. On the OML home page click Run SQL Script link within the Quick Actions panel.
3. A new SQL scratchpad will be created with the %script identifier already selected, this
identifier allows you to run multiple SQL statements.
Note: that the script paragraph does not have any menus to control the display and formatting
of the output. You can, however, use SQL SET commands to control how data is formatted for
display.
4. In this section we are going to use a script from a SQL pattern matching
tutorial, Sessionization with MATCH_RECOGNIZE and JSON
(https://livesql.oracle.com/apex/livesql/file/tutorial_EWB8G5JBSHAGM9FB2GL4V5CAQ.html),
on the free Oracle livesql.oracle.com site. This script shows how to use the SQL pattern
matching MATCH_RECOGNIZEfeature for sessionization analysis based on JSON web log
files.
Copy and paste this code snippet into the %script paragraph. After pasting the above code
into the script paragraph it should look something like this:
%script
/*
23 [email protected]
First step is to create the JSON table that will hold our data from our session log file. The log
will provide the details of the time and account/user id.
Oracle Autonomous Data Warehouse supports storing JSON documents inside the
database. Use following code to create a table to store the transaction log which is in JSON
format
*/
BEGIN
EXECUTE IMMEDIATE 'drop table json_sessionization';
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE json_sessionization(session_doc CLOB,
CONSTRAINT "VALID_JSON" CHECK (session_doc IS JSON) ENABLE)';
END;
/
/*
Next step is to add some data to our JSON table using the normal JSON notation of key-
value pairs.
*/
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"1","user_id":"Mary"}'')';
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"2","user_id":"Sam"}'')';
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"11","user_id":"Mary"}'')';
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"12","user_id":"Sam"}'')';
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"22","user_id":"Sam"}'')';
24 [email protected]
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"23","user_id":"Mary"}'')';
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"32","user_id":"Sam"}'')';
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"34","user_id":"Mary"}'')';
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"43","user_id":"Sam"}'')';
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"44","user_id":"Mary"}'')';
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"47","user_id":"Sam"}'')';
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"48","user_id":"Sam"}'')';
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"53","user_id":"Mary"}'')';
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"59","user_id":"Sam"}'')';
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"60","user_id":"Sam"}'')';
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"63","user_id":"Mary"}'')';
EXECUTE IMMEDIATE 'INSERT INTO json_sessionization VALUES
(''{"time_id":"68","user_id":"Sam"}'')';
EXECUTE IMMEDIATE 'COMMIT';
END;
/
/*
Using the new JSON SQL notation we can query our data using a simple SELECT statement
*/
SELECT
TO_NUMBER(j.session_doc.time_id) as time_id,
j.session_doc.user_id as user_id
FROM json_sessionization j;
25 [email protected]
/*
Using MATCH_RECOGNIZE clause to create a sessionization report.The aim is to count the
number of events within each session and calculate the duration of each session.
How do we do that? We can use some of the other built-in measures such as FIRST() and
LAST() to extract values from our resultset and we can calculate new values such as the
duration of a session.
In our code we will compute some new measures:
count(*) returns the number of events within a session
first(tstamp) returns the start time of each session
last(tstamp) returns the end time of each session
last(tstamp) - first(tstamp) calculates the duration of each session
*/
SELECT
userid,
session_id,
no_of_events,
start_time,
end_time,
session_duration
FROM (SELECT
TO_NUMBER(j.session_doc.time_id) as time_id,
j.session_doc.user_id as userid
FROM json_sessionization j)
MATCH_RECOGNIZE(
PARTITION BY userid ORDER BY time_id
MEASURES match_number() as session_id,
COUNT(*) as no_of_events,
FIRST(b.time_id) start_time,
LAST(s.time_id) end_time,
LAST(s.time_id) - FIRST(b.time_id) session_duration
26 [email protected]
ONE ROW PER MATCH
PATTERN (b s+)
DEFINE
s as (time_id - PREV(time_id) <= 10)
);
5. You can then run the script/paragraph and the output will appear below the code that makes
up the script.
The output should look something like this:
27 [email protected]
In this section we have successfully created and run SQL Scripts
28 [email protected]
10 SUMMARY
In this activity Guide we learned how to create OML Users, how to run a SQL Statement how to
share notebooks & how to create and run SQL scripts
29 [email protected]
