1. Using Oracle Machine Learning User Interface on Autonomous Database
  2. About Oracle Machine Learning Notebooks

5 About Oracle Machine Learning Notebooks

A notebook can contain many paragraphs. A paragraph is a notebook component where you can write and run SQL statements, PL/SQL scripts, R and Python code, and conda instructions. You can run paragraphs individually or, run all the paragraphs in a notebook using a single button.

A paragraph has an input section and an output section. In the input section, specify the interpreter to run along with the code or text. This information is sent to the interpreter to be run. In the output section, the results of the interpreter are provided.

The Notebooks page lists all the notebooks associated with the selected project. You can create, edit, and run your notebooks here.

You can perform the following tasks in the Notebooks page:

Notebooks Page with OML Notebooks EA option
  • Go to OML Notebook EA: Click Go to OML Notebook EA to go to the Oracle Machine Learning Notebooks Early Adopter page.

    Note:

    EA stands for Early Adopter.

  • Create: Click Create to create a new notebook.

  • Edit: Click on a notebook row to select it and click Edit. You can edit the notebook name, and add comments in the Edit Notebook dialog box.

  • Delete: Click on a notebook row to select it and click Delete.

  • Duplicate: Click on a notebook row to select it , and click Duplicate. This creates a copy of a notebook, and the duplicate copy of the is listed on the Notebooks page with the suffix _1 in the notebook name.

  • Move: Select a notebook and click Move to move the notebook to a different project, either in the same or in a different workspace. You must have either the Administrator or Developer privilege to move notebooks across projects and workspaces. You can move notebooks that are running from jobs because when you run notebooks from jobs, it runs a copy of the notebook.

    Note:

    A notebook cannot be moved if it is in RUNNING state, or if a notebook already exists in the target project by the same name.
  • Copy: Select a notebook and click Copy to copy the notebook to another project, either in the same or different workspace. You must have either the Administrator or the Developer privilege to copy notebooks in different projects and workspaces.

  • Save as Template: To save a notebook as a template, select the notebook and click Save as Template. In the Save as Template dialog, you can define the location of the template to save it in Personal or Shared under Templates.

  • Import: To import a notebook as .json files, click Import. Select the project and workspace in which to import the notebook.

  • Export: To export a notebook, click Export. You can export notebooks in Zeppelin format ( .json ) file and in Jupyter format ( .ipynb ), and later import them in to the same or a different environment.

  • Version: To create versions of a notebook, select it and click Version. You can experiment with your notebook by creating versions of it, and revert to an older version by clicking Revert Version.

    Note:

    You can also version a notebook by opening it, and then clicking on the Versioning option Versioning. Using this option, you can create new versions, view version history, restore older versions, and delete any older versions of the notebook that you have opened.
  • Copy to OML Notebook EA: To copy the selected notebook(s) to the early adopter interface using the original name with the suffix _ea._ea. You can now access this notebook by clicking on Notebooks EA in the left navigation menu, or by clicking on Go to OML Notebooks EA on the top right corner of this page.

  • To open a notebook and run it, click the notebook. Notebook opens in the edit mode.

  • Create a Notebook
    A notebook is a web-based interface for data analysis, data discovery, data visualization and collaboration.
  • Edit Your Notebook
    Upon creating a notebook, it opens automatically, presenting you with a single paragraph using the default %sql interpreter. You can change the interpreter by explicitly specifying one of %script, %python, %sql , %r , %md or %conda.
  • Version a Notebook
    You can version or create a backup a notebook, experiment on it, and revert to the original notebook, if required.
  • Save Notebooks as Templates
    You can save an existing notebook as a template in Personal or in Shared.
  • Use the SQL Interpreter in a Notebook Paragraph
    An Oracle Machine Learning notebook supports multiple languages. Each paragraph is associated with a specific interpreter. For example, to run SQL statements use the SQL interpreter. To run PL/SQL statements, use the script interpreter.
  • Use the Python Interpreter in a Notebook Paragraph
    An Oracle Machine Learning notebook supports multiple languages. Each paragraph is associated with a specific interpreter. To run Python commands in a notebook, you must first connect to the Python interpreter. To use OML4Py, you must import the oml module.
  • Use the R Interpreter in a Notebook Paragraph
    An Oracle Machine Learning notebook supports multiple languages. Each paragraph is associated with a specific interpreter. To run R functions in an Oracle Machine Learning notebook, you must first connect to the R interpreter.
  • Use the Conda Interpreter in a Notebook Paragraph
    Oracle Machine Learning Notebooks provides a Conda interpreter to enable administrators to create conda environments with custom third-party Python and R libraries. Once created, you can download and activate Conda environments inside a notebook session also using the Conda interpreter.
  • Call the Markdown Interpreter and Generate Static html from Markdown Plain Text
    Use the Markdown interpreter and generate static html from Markdown plain text.
  • About Interpreter Bindings and Notebooks
    An interpreter is a plug-in that allows you to use a specific data processing language backend.
  • Use the Scratchpad
    The Scratchpad provides you convenient one-click access to a notebook for running SQL statements, PL/SQL, R, and Python scripts that can be renamed. The Scratchpad is available on the Oracle Machine Learning User Interface (UI) home page.
  • Collaborate in Oracle Machine Learning
    Two or more users can collaborate and share Oracle Machine Learning User Interface (UI) notebooks with other users.

Related Topics

5.1 Create a Notebook

A notebook is a web-based interface for data analysis, data discovery, data visualization and collaboration.

Whenever you create a notebook, it has an interpreter settings specification. The notebook contains an internal list of bindings that determines the order of the interpreter bindings. A notebook comprises paragraphs which is a notebook component where you can write SQL statements, run PL/SQL scripts, and run Python commands. A paragraph has an input section and an output section. In the input section, specify the interpreter to run along with the text. This information is sent to the interpreter to be executed. In the output section, the results of the interpreter are provided.
To create a notebook:
  1. On the Oracle Machine Learning UI home page, click Notebooks. The Notebooks page opens.
  2. On the Notebooks page, click Create.
    The Create Notebook window appears.
  3. In the Name field, provide a name for the notebook.
  4. In the Comments field, enter comments, if any.
  5. Click OK.
Your notebook is created and it opens in the notebook editor. You can now use it to run SQL statements, run PL/SQL scripts, run Python, R and Conda commands. To do so, specify any one of the following directives in the input section of the paragraph:
  • %sql — To call the SQL interpreter and run SQL statements
  • %script — To call PL/SQL interpreter and run PL/SQL scripts
  • %md — To call the Markdown interpreter and generate static html from Markdown plain text
  • %python — To call the Python interpreter and run Python scripts
  • %r — To call the R interpreter and run R scripts.
  • %conda — To connect to the Conda interpreter, and install third-party Python and R libraries inside a notebook session.

Parent topic: About Oracle Machine Learning Notebooks

5.2 Edit Your Notebook

Upon creating a notebook, it opens automatically, presenting you with a single paragraph using the default %sql interpreter. You can change the interpreter by explicitly specifying one of %script, %python, %sql , %r , %md or %conda.

Set the context with a project with which your notebook is associated.
You can edit an existing notebook in your project. To edit an existing notebook:
  1. On Oracle Machine Learning UI home page, select the project in which your notebook is available.
  2. Go to the Oracle Machine Learning UI navigator, and select Notebooks. Alternatively, you can click the Notebooks quick link on the home page.
    On the right pane, all notebooks that are available in the project are listed.
  3. Click the notebook that you want to open and edit.
    The selected notebook opens in edit mode.
  4. In the edit mode, you can use the Oracle Machine Learning notebooks toolbar options to run code in paragraphs, for configuration settings, and display options.

    Figure 5-1 Notebook toolbar

    Notebook toolbar
    You can perform the following tasks:
    • Write code to fetch data
    • Click run to run one or all paragraphs in the notebook.
    • Click show hide code to hide all codes from all the paragraphs in the notebook. Click it again to display the codes.
    • Click show hide output to hide all outputs from all the paragraphs in the notebook. Click it again to view the outputs.
    • Click clear output to remove all outputs from all the paragraphs in the notebook. To view the output, click the run icon again.
    • Click clear notebook to delete all the paragraphs in the notebook.
    • Click export notebookto export the notebook.
    • Click search code to search any information in the codes present in the notebook.
    • Click keyboard shortcuts to view the list of keyboard shortcuts.
    • Click interpreter bindings to set the order for interpreter bindings for the notebook.
    • Click notebook display options to select one of the three notebook display options.
      • Click default to view the codes, output, and metadata in all paragraphs in the notebook.
      • Click Simple to view only the code and output in all paragraphs in the notebook. In this view, the notebook toolbar and all edit options are hidden. You must hover your mouse to view the edit options.
      • Click Report to view only the output in all paragraphs in the notebook.
    • Click edit options to access paragraph specific edit options such as clear output, remove paragraph, adjust width, font size, run all paragraphs above or below the selected paragraph and so on.
    • Add dynamic forms such as the Text Input form, Select form, Check box form for easy selection of inputs and easy filtering of data in your notebook. Oracle Machine Learning supports the following Apache Zeppelin dynamic forms:

      • Text Input form — Allows you to create a simple form for text input.

      • Select form — Allows you to create a form containing a range of values that the user can select.

      • Check Box form — Allows you to insert check boxes for multiple selection of inputs.

    Note:

    The Apache Zeppelin dynamic forms are supported only on SQL interpreter notebooks.
  5. Once you have finished editing the notebook, click Back.
    This takes you back to the Notebook page.
  • Export a Notebook
    You can export a notebook in Zeppelin format ( .json ) file and in Jupyter format ( .ipynb ), and later import them in to the same or a different environment.
  • Import a Notebook
    You can import notebooks across Pluggable Databases (PDBs) into your workspace. You can also import Jupyter notebooks into Oracle Machine Learning UI.
  • Create Check Box Forms in Notebooks
    The Check Box Form supports multiple selection of inputs in a paragraph. The inputs are available as check box options in the notebook.
  • Create Select Forms in Notebooks
    The Select Form allows you to select input values from a list of values, and dynamically retrieve the selected values as defined in the paragraph.
  • Create Text Input Forms in Notebooks
    The Text Input form allows you to dynamically retrieve values as defined in the notebook.

Parent topic: About Oracle Machine Learning Notebooks

5.2.1 Export a Notebook

You can export a notebook in Zeppelin format ( .json ) file and in Jupyter format ( .ipynb ), and later import them in to the same or a different environment.

To export a notebook:
  1. On the Notebooks page, select the notebooks that you want to export. You have the option to export one or more or all the notebooks.
  2. On the top panel of the notebook editor, click Export and then click any one of the following options:
    Supported Notebook Formats for Export
    • Notebooks to Export — To export notebooks, click:
      • All — To export all the notebooks.
      • Selected — To export the selected notebooks.
    • Format — Select the format in which you want to export your notebook:
      • Zeppelin — Exports the notebook as a .json (JavaScript Object Notation) file.
      • Jupyter — Exports the notebook as a .ipynb file.
    The exported notebooks are saved either as .json files or .ipynb files in a zipped folder.

Related Topics

Parent topic: Edit Your Notebook

5.2.2 Import a Notebook

You can import notebooks across Pluggable Databases (PDBs) into your workspace. You can also import Jupyter notebooks into Oracle Machine Learning UI.

Oracle Machine Learning UI supports the import of both Zeppelin (.json) and Jupyter (.ipynb) notebooks.

Note:

Starting in Oracle Database 20c,"database" refers specifically to the data files of a multitenant container database (CDB), pluggable database (PDB), or application container.
To import a notebook:
  1. On Oracle Machine Learning UI home page, click Notebooks.
  2. On the Notebooks page, click Import.
    This opens the File Upload dialog. Browse and select the notebook that you want to import.

    Note:

    You must have the notebook saved as a .json file to import it. You can import notebooks exported from non-Oracle Apache Zeppelin environments, but only paragraphs types that are supported may be run.
  3. In the File Upload dialog, browse and select the .json file and click Open.
    This imports the notebook file into your workspace.
  4. Click the imported notebook to open it. On the Notebooks page, click the gear icon to view the interpreter bindings.

Related Topics

Parent topic: Edit Your Notebook

5.2.3 Create Check Box Forms in Notebooks

The Check Box Form supports multiple selection of inputs in a paragraph. The inputs are available as check box options in the notebook.

To create a Check Box Form:
  1. Open the notebook in which you want to add the Check Box Form.
  2. In a SQL statement, define the Check Box form by using the syntax:
    ${checkbox:formName=defaultValue1|defaultValue2...,option1|option2...}
    For example, run the SQL statement:
    SELECT ${checkbox:whichcolumn=OWNER|OBJECT_TYPE, OWNER|OBJECT_NAME|OBJECT_TYPE|CREATED|STATUS} FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('VIEW', 'TABLE', 'INDEX', 'SYNONYM');
    In this example,
    • The Check Box form is WhichColumn
    • The multiple selection options available in the check boxes are OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, and STATUS
    • The fields OWNER and OBJECT_TYPE are defined as default
    • The table name is ALL_OBJECTS
    • The columns that are configured for display are OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, and STATUS
  3. Run the notebook. The Check Box form called WhichForm is available in the notebook, as shown in the screenshot.checkbox_form

Parent topic: Edit Your Notebook

5.2.4 Create Select Forms in Notebooks

The Select Form allows you to select input values from a list of values, and dynamically retrieve the selected values as defined in the paragraph.

To create a Select form:
  1. Open the notebook in which you want to add the text input form.
  2. In a SQL statement, define the Select form by using the syntax:
    ${formName=defaultValue,option1|option2...}.
    For example, run the SQL statement:
    SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE = '${OBJ=INDEX,INDEX|TABLE|VIEW|SYNONYM}';
    select_form
    In this example,
    • The form name is obj
    • The list of available values are INDEX, TABLE, VIEW, SYNONYM.
    • The table name is ALL_OBJECTS
    • The column name is OBJECT_TYPE
    Select any values from the drop-down list in the obj form. The selected value will be retrieved in the OBJECT_TYPE column in the ALL_OBJECTS table.

Parent topic: Edit Your Notebook

5.2.5 Create Text Input Forms in Notebooks

The Text Input form allows you to dynamically retrieve values as defined in the notebook.

To create a Text Input form:
  1. Open the notebook in which you want to add the Text Input form.
  2. In a SQL statement, define the Text Input form by using the syntax:
    ${formName}
    For example, run the SQL statement:
    SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE = '${OBJ}';
    In this example,
    • The form name is obj
    • The table name is ALL_OBJECTS
    • The column name is OBJECT_TYPE
    Here, the text form obj is created for the column OBJECT_TYPE in the table ALL_OBJECTS. You can enter different values in the form field obj and run the notebook to retrieve the corresponding values in the column OBJECT_TYPE.
  3. Run the paragraph. The notebook now displays the text input form field obj, as shown in the screenshot. You can enter values in the obj field, and run the notebook to retrieve the corresponding values for the column OBJECT_TYPE in the table ALL_OBJECTS.
    • If you enter TABLE in the obj field, and run the notebook, then the notebook retrieves TABLE in the column OBJECT_TYPE, as shown in the screenshot.text input form with object type table
    • If you enter VIEW in the obj form field and run the notebook, then the notebook retrieves the value VIEW in the column OBJECT_TYPE, as shown in the screenshot. text input form with object type view
  4. You can also assign default values in the form by using the syntax:
    ${formName=defaultValue}
    To assign a default value to the Text Input form, modify the SQL statement to:
    SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE = '${obj=TABLE}'
    Here, the default value assigned to the form is TABLE. Once you run the paragraph, the default value TABLE will be retrieved in the column OBJECT_TYPE, as shown in the screenshot.
    text input form with default value

Parent topic: Edit Your Notebook

5.3 Version a Notebook

You can version or create a backup a notebook, experiment on it, and revert to the original notebook, if required.

To version a notebook:
  1. On the Notebooks page, select the notebook that you want to version and click Version.
    The Versions page opens.
  2. On the Versions page for the selected notebook, click + Version.
    The Create Versions dialog box opens.
  3. In the Create Versions dialog box, enter comments for the specific version of your notebook, and click OK.
  4. The versioned notebook is now listed on the Versions page. You can perform the following tasks:
    • Click Revert Version to restore the older version of your notebook.
    • Click Delete to delete the selected version of your notebook.
    • Click New Notebook to create a new notebook from the selected notebook version.

Parent topic: About Oracle Machine Learning Notebooks

5.4 Save Notebooks as Templates

You can save an existing notebook as a template in Personal or in Shared.

To save a notebook as a template:
  1. On the Notebooks page, select the notebook that you want to save as template and click Save as Template.
    The Save as Template dialog box opens.
  2. In the Name field, enter a name for the notebook template.
  3. In the Comments field, enter comments, if any.
  4. In the Tags field, enter tags for the template.
  5. In Save To, select:
    • Personal: If you want to save this notebook template to Personal such that only your account can view or use this notebook.
    • Shared: If you want to save and share this notebook template such that other users can view and create notebooks from this template that they can run and edit.

Parent topic: About Oracle Machine Learning Notebooks

5.5 Use the SQL Interpreter in a Notebook Paragraph

An Oracle Machine Learning notebook supports multiple languages. Each paragraph is associated with a specific interpreter. For example, to run SQL statements use the SQL interpreter. To run PL/SQL statements, use the script interpreter.

In an Oracle Machine Learning UI notebook, you can add multiple paragraphs, and each paragraph can be connected to different interpreters such as SQL or Python. You identify which interpreter to use by specifying % followed by the interpreter to use: sql, script, r, python, conda, markdown.

A paragraph has an input section and an output section. In the input section, specify the interpreter to run along with the text. This information is sent to the interpreter to be run. In the output section, the results of the interpreter are provided.
You can use the following directives in a notebook paragraph:
  • %sql — Supports standard SQL statements. In %sql the results of a SELECT statement are directly displayed in a table viewer, with access to other visualization options. Use the options in the chart settings to perform groupings, summation, and other operations.
  • %script — Supports both SQL statements and PL/SQL. In %script, the results of a SELECT statement are provided as text string output.
  • %conda — Supports the Conda environment. Type %conda at the beginning of the paragraph to connect to the Conda environment and work with third-party libraries for Python.
  • %r — Supports R scripts. Type %r at the beginning of the paragraph to connect to the R interpreter.
  • %python — Supports Python scripts. Type %python at the beginning of the paragraph to connect to the Python interpreter.
  • %md — Supports Markdown markup language.

Note:

To run a Group By on all your data, then it is recommended to use SQL scripts to do the grouping in the database, and return the summary information for charting in the notebook. Grouping at the notebook level works well for small sets of data. If you pull too much data to the notebook, you may encounter issues due to insufficient memory. You can set the row limit for your notebook by using the option Render Row Limit on the Connections Group page.
To fetch and visualize data in a notebook:
  1. On the Notebook page, click the notebook that you want to run.
    The notebook opens in edit mode.
  2. Type %SQL to call the SQL interpreter, and press Enter. Your notebook is now ready to run SQL statements.
  3. Type the SQL statement to fetch data from an Oracle Database. For example, type SELECT * FROM TABLENAME and click run icon. Alternatively, press Shift+Enter keys to run the notebook.

    Note:

    Notebooks must be opened as a regular user, that is, a non-administrator user. The Run notebook option is not available to the Administrator.
    This fetches the data in the notebook.
  4. The data is displayed in the output of the paragraph.
    The results of the interpreter appear in the output section. The output section of the paragraph comprises a charting component that displays the results in graphical output. The chart interface allows you to interact with the output in the notebook paragraph. You have the option to run and edit single a paragraph or all paragraphs in a notebook.
    For Table Options, click settings and select:

    • useFilter: To enable filter for columns.

    • showPagination: To enable pagination for enhanced navigation.

    • showAggregationFooter: To enable a footer to display aggregated values.

    You can also sort the columns by clicking the down arrow next to the column name.

    To visualize the tabular data, click the respective icons for each of the each graphical representation, as shown here:
    • Click Bar Chart to represent the data in a Bar Chart.
    • Click Pie Chart to represent the data in a Pie Chart.
    • Click Area Chart to represent the data in an Area Chart.
    • Click Line Chart to represent the data in a Line Chart.
    • Click Scatter Chart to represent the data in a Scatter Chart.
  • About Oracle Machine Learning for SQL
    Oracle Machine Learning for SQL (OML4SQL) provides a powerful, state-of-the-art machine learning capability within Oracle Database. You can use Oracle Machine Learning for SQL to build and deploy predictive and descriptive machine learning models, add intelligent capabilities to existing and new applications.
  • Set Output Format in Notebooks
    Oracle Machine Learning Notebooks allow you to preformat query output in notebooks.
  • Output Formats Supported by SET SQLFORMAT Command
    By using the SET SQLFORMAT command, you can generate the query output in a variety for formats.

Related Topics

Parent topic: About Oracle Machine Learning Notebooks

5.5.1 About Oracle Machine Learning for SQL

Oracle Machine Learning for SQL (OML4SQL) provides a powerful, state-of-the-art machine learning capability within Oracle Database. You can use Oracle Machine Learning for SQL to build and deploy predictive and descriptive machine learning models, add intelligent capabilities to existing and new applications.

Oracle Machine Learning for SQL offers an extensive set of in-database algorithms for performing a variety of machine learning tasks, such as classification, regression, anomaly detection, feature extraction, clustering, and market basket analysis, among others. The programmatic interfaces to OML4SQL are PL/SQL for building and maintaining models and a family of SQL functions for scoring.

Use Oracle Machine Learning Notebook with the SQL and PL/SQL interpreters to run SQL statements (%sql) and PL/SQL scripts (%script) respectively. Use Oracle Machine Learning for SQL to:
  • Perform data exploration and data analysis
  • Build, evaluate and deploy machine learning models, and
  • Score data using those models

Parent topic: Use the SQL Interpreter in a Notebook Paragraph

5.5.2 Set Output Format in Notebooks

Oracle Machine Learning Notebooks allow you to preformat query output in notebooks.

To preformat query output, you must use the command SET SQLFORMAT as follows:
  1. Open a notebook in Oracle Machine Learning.
  2. Type the command:
    %script
    SET SQLFORMAT format_option
    For example, if you want the output in ansiconsole format, then type the command followed by the SQL query as:
    SET SQLFORMAT ansiconsole;
    SELECT * FROM HR.EMPLOYEES;
    Here, the output format is ansiconsole, and the table name is HR.EMPLOYEES.

    Note:

    This formatting is available for the Script interpreter. Therefore, you must add the prefix %script as shown in this example.

Parent topic: Use the SQL Interpreter in a Notebook Paragraph

5.5.3 Output Formats Supported by SET SQLFORMAT Command

By using the SET SQLFORMAT command, you can generate the query output in a variety for formats.

Note:

These output formats are available for the Script interpreter. Therefore, you must include the prefix %script.
The available output formats are:

  • CSV — The CSV format produces standard comma-separated variable output, with string values enclosed in double quotes. The syntax is:

    %script

    SET SQLFORMAT CSV

  • HTML — The HTML format produces the HTML for a responsive table. The content of the table changes dynamically to match the search string entered in the text field. The syntax is:

    %script

    SET SQLFORMAT HTML

  • XML — The XML format produces a tag based XML document. All data is presented as CDATA tags. The syntax is:

    %script

    SET SQLFORMAT XML

  • JSON — The JSON format produces a JSON document containing the definitions of the columns along with the data that it contains. The syntax is:

    %script

    SET SQLFORMAT JSON

  • ANSICONSOLE — The ANSICONSOLE format resizes the columns to the width of the data to save space. It also underlines the columns, instead of separate line of output. The syntax is:

    %script

    SET SQLFORMAT ANSICONSOLE

  • INSERT — The INSERT format produces the INSERT statements that could be used to recreate the rows in a table. The syntax is:

    %script

    SET SQLFORMAT INSERT

  • LOADER — The LOADER format produces pipe delimited output with string values enclosed in double quotes. The column names are not included in the output. The syntax is:

    %script

    SET SQLFORMAT LOADER

  • FIXED — The FIXED format produces fixed width columns with all data enclosed in double-quotes. The syntax is:

    %script

    SET SQLFORMAT FIXED

  • DEFAULT — The DEFAULT option clears all previous SQLFORMAT settings, and returns to the default output. The syntax is:

    %script

    SET SQLFORMAT DEFAULT

    Note:

    You can also run this command without the format name DEFAULT by simply typing SET SQLFORMAT.

  • DELIMITED — The DELIMITED format allows you to manually define the delimiter string, and the characters that are enclosed in the string values. The syntax is:

    %script

    SQLFORMAT DELIMITED delimiter left_enclosure right_enclosure

    For example,

    %script

    SET SQLFORMAT DELIMITED ~del~ " "

    SELECT * FROM emp WHERE deptno = 20;

    Output:

    "EMPNO"~del~"ENAME"~del~"JOB"~del~"MGR"~del~"HIREDATE"~del~"SAL"~del~"COMM"~del~"DEPTNO"

    In this example, the delimiter string is ~del~ and string values such as EMPNO, ENAME, JOB and so on, are enclosed in double quotes.

Parent topic: Use the SQL Interpreter in a Notebook Paragraph

5.6 Use the Python Interpreter in a Notebook Paragraph

An Oracle Machine Learning notebook supports multiple languages. Each paragraph is associated with a specific interpreter. To run Python commands in a notebook, you must first connect to the Python interpreter. To use OML4Py, you must import the oml module.

In an Oracle Machine Learning UI notebook, you can add multiple paragraphs, and each paragraph can be connected to different interpreters such as SQL or Python. You identify which interpreter to use by specifying % followed by the interpreter to use: sql, script, r, python, conda, markdown.

This example shows you how to:
  • Connect to a Python interpreter to run Python commands in a notebook
  • Import the Python modules - oml, matplotlib, and numpy
  • Check if the oml module is connected to the Oracle Database

Note:

z is a reserved keyword and must not be used as a variable in %python paragraphs in Oracle Machine Learning UI notebooks.
Assumption: The example assumes that you have created a new notebook named Py Note.
  1. Open the Py Note notebook and click the interpreter bindings icon. View the available interpreter bindings.
  2. To connect to the Python interpreter, type %python
    You are now ready to run Python scripts in your notebook.
  3. To use OML4Py module, you must import the oml module. Type the following Python command to import the oml module, and the click run icon. Alternatively, you can press Shift+Enter keys to run the notebook.
    import oml
  4. To verify if the oml module is connected to the Database, type:
    oml.isconnected()
    Once your notebook is connected, the command returns TRUE. The notebook is now connected to the Python interpreter, and you are ready to run python commands in your notebook.

Example to demonstrate the use of the Python modules - matplotlib and numpy , and use random data to plot two histograms.

  1. Type the following commands to import the modules: 
    %python
import matplotlib.pyplot as plt
import numpy as np
    • Matplotlib - Python module to render graphs
    • Numpy - Python module for computations
  2. Type the following commands to compute and render the data in two histograms.
    
list1 = np.random.rand(10)*2.1
list2 = np.random.rand(10)*3.0

plt.subplot(1,2,1) # 1 line, 2 rows, index nr 1 (first position in the subplot)
plt.hist(list1)
plt.subplot(1, 2, 2) # 1 line, 2 rows, index nr 2 (second position in the subplot)
plt.hist(list2)
plt.show()
    In this example, the commands import two Python module to compute and render the data in two histograms list1 and list2.
  3. Click Run.
    The output section of the paragraph which contains a charting component displays the results in two histograms - list1 and list2, as shown in the screenshot.
  • About Oracle Machine Learning for Python
    Oracle Machine Learning for Python (OML4Py) is a component of Oracle Autonomous Database, which includes Oracle Autonomous Data Warehouse (ADW), Oracle Autonomous Transaction Processing (ATP), and Oracle Autonomous JSON Database (AJD). By using Oracle Machine Learning UI notebooks, you can run Python functions on data for data exploration and preparation while leveraging Oracle Database as a high-performance computing environment. Oracle Machine Learning User Interface (UI) is available through Autonomous Data Warehouse (ADW) , Autonomous Transaction Processing (ATP) and Autonomous JSON Database (AJD) services.

Parent topic: About Oracle Machine Learning Notebooks

5.6.1 About Oracle Machine Learning for Python

Oracle Machine Learning for Python (OML4Py) is a component of Oracle Autonomous Database, which includes Oracle Autonomous Data Warehouse (ADW), Oracle Autonomous Transaction Processing (ATP), and Oracle Autonomous JSON Database (AJD). By using Oracle Machine Learning UI notebooks, you can run Python functions on data for data exploration and preparation while leveraging Oracle Database as a high-performance computing environment. Oracle Machine Learning User Interface (UI) is available through Autonomous Data Warehouse (ADW) , Autonomous Transaction Processing (ATP) and Autonomous JSON Database (AJD) services.

Oracle Machine Learning for Python (OML4Py) makes the open source Python scripting language and environment ready for the enterprise and big data. Designed for problems involving both large and small volumes of data, Oracle Machine Learning for Python integrates Python with Oracle Autonomous Database, including its powerful in-database machine learning algorithms, and enables deployment of Python code.

Use Oracle Machine Learning for Python to:
  • Perform data exploration, data analysis, and machine learning using Python leveraging Oracle Database as a high performance compute engine
  • Build and evaluate machine learning models and score data using those models from an integrated Python API using in-database algorithms
  • Deploy user-defined Python functions through a REST interface with data-parallel and task-parallel processing

The Python interpreter uses Python 3.8.5 to process Python scripts in Oracle Machine Learning UI notebooks. To use the interpreter, specify the %python directive at the beginning of the paragraph. The Python interpreter supports the following Python modules:

  • cx_Oracle 7.3.0
  • cycler 0.10.0
  • joblib 0.14.0
  • kiwisolver 1.1.0
  • matplotlib 3.1.2
  • numpy 1.18.1
  • pandas 0.25.3
  • pyparsing 2.4.0
  • python-dateutil 2.8.1
  • pytz 2019.3
  • scikit_learn 0.22.1
  • scipy 1.4.1
  • six 1.13.0

Related Topics

Parent topic: Use the Python Interpreter in a Notebook Paragraph

5.7 Use the R Interpreter in a Notebook Paragraph

An Oracle Machine Learning notebook supports multiple languages. Each paragraph is associated with a specific interpreter. To run R functions in an Oracle Machine Learning notebook, you must first connect to the R interpreter.

In an Oracle Machine Learning UI notebook, you can add multiple paragraphs, and each paragraph can be connected to different interpreters such as R or SQL or Python. You identify which interpreter to use by specifying % followed by the interpreter to use: sql, script, r, python, conda, markdown.

This example shows how to:
  • Connect to the R interpreter to run R commands in a notebook.
  • Verify the connection to Oracle Autonomous Database, and
  • Load the ORE libraries
  1. To connect to the R interpreter, type the following directive at the beginning of the notebook paragraph, and press Enter:
    %r
  2. To verify the database connection, type the following command and press Enter:
    ore.is.connected()
    Once your notebook is connected, the command returns TRUE, as shown in the screenshot here. The notebook is now connected to the R interpreter, and you are ready to run R commands in your notebook.

    Figure 5-2 Test Database Connection

    Test connection to the Oracle Database
  3. To import R Libraries, run the following commands: 
    library(ORE)
library(OREdplyr)
    Once the packages are loaded successfully, the list of ORE packages are displayed as shown in the screenshot here. Scroll down to view the entire list.

    Figure 5-3 Commands to Load R Packages

    Load R Packages
  • About Oracle Machine Learning for R
    Oracle Machine Learning for R (OML4R) is a component of the Oracle Machine Learning family of products, which integrates R with Oracle Autonomous Database.

Parent topic: About Oracle Machine Learning Notebooks

5.7.1 About Oracle Machine Learning for R

Oracle Machine Learning for R (OML4R) is a component of the Oracle Machine Learning family of products, which integrates R with Oracle Autonomous Database.

Oracle Machine Learning for R makes the open source R scripting language and environment ready for enterprise and big data. It is designed for problems involving for both large and small data volumes. OML4R allows users to run R commands and scripts for statistical, machine learning, and perform visualization analyses on database tables and views using R syntax.

Oracle Machine Learning for R is available in Oracle Machine Learning UI, currently available through Oracle Autonomous Database, including Autonomous Data Warehouse, Autonomous Transaction Processing, and Autonomous JSON Database. Oracle Machine Learning for R Embedded R Execution functionality can be deployed through SQL and REST APIs on Autonomous Database.

Use Oracle Machine Learning for R to:

  • Perform data exploration and data preparation while seamlessly leveraging Oracle Database as a high-performance computing environment.
  • Run user-defined R functions on database spawned and controlled R engines, with system-supported data-parallel and task-parallel capabilities.
  • Access and use powerful in-database machine learning algorithms from R language.

To use the R interpreter, specify the %r directive at the beginning of the paragraph. The following R packages are installed to support Oracle Machine Learning for R.

Supported Oracle Machine Learning for R Proprietary R Packages

The supported Oracle Machine Learning for R proprietary R packages are:

  • ORE_1.5.1
  • OREbase_1.5.1
  • OREcommon_1.5.1
  • OREdm_1.5.1
  • OREdplyr_1.5.1
  • OREeda_1.5.1
  • OREembed_1.5.1
  • OREgraphics_1.5.1
  • OREmodels_1.5.1
  • OREpredict_1.5.1
  • OREstats_1.5.1
  • ORExml_1.5.1

Supported Open Source R Modules

The following open source R packages are supported by Oracle Machine Learning for R:

  • R-4.0.5
  • Cairo_1.5-15
  • ROracle_1.4-1: DBI_1.1-2
  • arules_1.7-3
  • png_0.1-7
  • randomForest_4.6-14
  • statmod_1.4-36
  • dplyr_1.0-9:
  • R6_2.5.1
  • assertthat_0.2.1
  • cli_3.3.0
  • crayon_1.5.1
  • ellipsis_0.3.2
  • fansi_1.0.3
  • generics_0.1.2
  • glue_1.6.2
  • lazyeval_0.2.2
  • lifecycle_1.0.1
  • magrittr_2.0.3
  • pillar_1.7.0
  • pkgconfig_2.0.3
  • purrr_0.3.4
  • rlang_1.0.2
  • tibble_3.1.7
  • tidyselect_1.1.2
  • utf8_1.2.2
  • vctrs_0.4.1

Oracle Machine Learning for R Interpreter Requirements

The R interpreter requires the following open source R packages:
  • Rkernel 1.3:
    • base64enc 0.1-3
    • cli 3.3.0
    • crayon 1.5.1
    • digest 0.6.29
    • ellipsis 0.3.2
    • evaluate 0.15
    • fansi 1.0.3
    • fastmap 1.1.0
    • glue 1.6.2
    • htmltools 0.5.2
    • IRdisplay 1.1
    • jsonlite 1.8.0
    • lifecycle 1.0.1
    • pbdZMQ 0.3-7
    • pillar 1.7.0
    • repr 1.1.4
    • rlang 1.0.2
    • utf8 1.2.2
    • uuid 1.1-0
    • vctrs 0.4.1
  • knitr 1.39:
    • evaluate_0.15
    • glue_1.6.2
    • highr_0.9
    • magrittr_2.0.3
    • stringi_1.7.6
    • stringr_1.4.0
    • xfun_0.31
    • yaml_2.3.5

Parent topic: Use the R Interpreter in a Notebook Paragraph

5.8 Use the Conda Interpreter in a Notebook Paragraph

Oracle Machine Learning Notebooks provides a Conda interpreter to enable administrators to create conda environments with custom third-party Python and R libraries. Once created, you can download and activate Conda environments inside a notebook session also using the Conda interpreter.

An Oracle Machine Learning notebook supports multiple languages. For this, you must create a notebook with some paragraphs to run SQL queries, and other paragraphs to run PL/SQL scripts. To run a notebook in different scripting languages, you must first connect the notebook paragraphs with the respective interpreters such as SQL, PL/SQL, R, Python, or Conda.

This topic shows how to start working in the Conda environment:
  • Connect to the Conda interpreter
  • Download and activate the Conda environment
  • View the list of packages in the Conda environment
  • Run a Python function to import the Iris dataset, and use the seaborn package for visualization
  1. Type %conda at the beginning of the paragraph to connect to the Conda interpreter, and press Enter.
    %conda
  2. Next, download and activate the Conda environment. Type:
    download sbenv
activate sbenv
    In this example, the Conda environment is downloaded and activated. The name of the Conda environment in this example is sbenv.Download and Activate the Conda Environment
  3. You can view all the packages that are present in the Conda environment. To view the list of packages, type list.
    List of packages in the seaborn library
  4. Here's an example that demonstrates how to use the seaborn library package for visualization. Seaborn is a Python visualization library based on matplotlib. It provides a high-level interface for drawing attractive statistical graphics. This example
    • Imports Pandas and seaborn
    • Loads the Iris dataset
    • Plots the datapoints, that is, the three different species of the Iris flower - Setosa, Virginica, and Versicolor based on its dimensions. It creates a scatter plot
    Type:
    %python

def sb_plot():
    import pandas as pd
    import seaborn as sb
    from matplotlib import pyplot as plt
    df = sb.load_dataset('iris')
    sb.set_style("ticks")
    sb.pairplot(df,hue = 'species',diag_kind = "kde",kind = "scatter",palette = "husl")
    plt.show()
    Commands
  5. Run the function in a Python paragraph.
    Type:
    %python 
sb_plot()
    Visualization using Seaborn Python library
  • About the Conda Environment and Conda Interpreter
    Conda is an open-source package and environment management system that allows the use of environments containing third-party Python and R libraries. Oracle Machine Learning User Interface (UI) provide the conda interpreter to install third-party Python and R libraries inside a notebook session.
  • Conda Interpreter Commands
    This table lists the commands for the Conda interpreter.

Parent topic: About Oracle Machine Learning Notebooks

5.8.1 About the Conda Environment and Conda Interpreter

Conda is an open-source package and environment management system that allows the use of environments containing third-party Python and R libraries. Oracle Machine Learning User Interface (UI) provide the conda interpreter to install third-party Python and R libraries inside a notebook session.

Third-party libraries installed in Oracle Machine Learning Notebooks can be used in:
  • Standard Python
  • Standard R
  • Oracle Machine Learning for Python embedded Python execution from the Python, SQL and REST APIs
  • Oracle Machine Learning for R embedded R execution from the R, SQL, and REST APIs

To start working in the Conda environment:

  1. Ensure that a Conda environment is saved to Object Storage, or update an existing package by installing a new version.

    Note:

    The Conda environment is created by the ADMIN user with OML_SYS_ADMIN role. The administrator manages the lifecycle of an environment, including adding or deleting packages from it, and removing environments. The Conda environments are stored in an Object Storage bucket associated with the Autonomous Database.
  2. Sign into Oracle Machine Learning UI and download the Conda environment. To download the Conda environment, type: 
    %conda 
    download myenv
  3. Activate the Conda environment. To activate the Conda environment, type: 
    activate myenv

    Note:

    There is only one active Conda environment at a given point in time.
  4. Create a notebook, use the Conda interpreter to use third-party libraries in the Object Storage. To use the Conda interpreter, type %conda at the beginning of the paragraph to connect to the Conda environment, and work with third-party libraries for Python. You can switch between the preinstalled Conda environments. For example, you can have an environment for working with Graph analysis, and another environment for Oracle Machine Learning analysis.
  5. Deactivate the Conda environment. As a best practice, deactivate the Conda environment after you have finished working on your machine learning analysis. To deactivate the environment, type: 
    deactivate

Parent topic: Use the Conda Interpreter in a Notebook Paragraph

5.8.2 Conda Interpreter Commands

This table lists the commands for the Conda interpreter.

Conda Interpreter Commands

Table 5-1 Conda Interpreter Commands

Tasks Commands Role
Create a Conda environment. create -n <env_name> <python_version>
  • ADMIN
Remove a list of packages from a specified conda environment. It is also an alias for conda uninstall. remove -n <env_name> --all

Note:

The Conda environment is deleted from the user session.
  • ADMIN
  • OML user
List user-created local environment. env list
  • ADMIN
  • OML user
Remove user-created local environment. env remove -n <env_name>
  • ADMIN
  • OML user
List all packages and versions installed in active environment. list
  • ADMIN
  • OML user
Activate a user-created local environment. activate -n <env_name>
  • ADMIN
  • OML user
Deactivate the current environment. deactivate
  • ADMIN
  • OML user
Install an external package from a public Conda channel. install -n <env_name> <package_name>
  • ADMIN
Uninstall a specific package from a Conda environment. It is also an alias for remove. uninstall -n <env_name> <package_name>
  • ADMIN
Display information about current conda install. info
  • ADMIN
  • OML user
View the command line help. COMMANDNAME --help
  • ADMIN
  • OML user
Upload a Conda environment to object storage.

Note:

This is an Autonomous Database specific command.
upload --overwrite <env_name> --description 'some description' -t <name> <value>

Note:

You can provide many tags. For example: -t <name1> <value1> -t <name2> <value2> ..
  • ADMIN
Download and unpack a specific Conda environment from the object storage.

Note:

This is an Autonomous Database specific command.
download --overwrite <env_name>
  • ADMIN
  • OML user
List local environments available to the user. list-local-envs
  • ADMIN
  • OML user
List all Conda environments in the object storage.

Note:

This is an Autonomous Database specific command.
list-saved-envs --installed-packages -e <env_name>
  • ADMIN
  • OML user
Delete a Conda environment.

Note:

This is an Autonomous Database specific command.
delete <env_name>

Note:

The Conda environment is deleted from the Object Storage.
  • ADMIN
Update conda packages to the latest compatible version. update
  • ADMIN
Upgrade the current conda package. It is also an alias for conda update. upgrade
  • ADMIN
Search for packages and display associated information. The input is a MatchSpec, a query language for conda packages. search
  • ADMIN
  • OML user

Parent topic: Use the Conda Interpreter in a Notebook Paragraph

5.9 Call the Markdown Interpreter and Generate Static html from Markdown Plain Text

Use the Markdown interpreter and generate static html from Markdown plain text.

To call the Markdown interpreter and generate static html from Markdown plain text:
  1. In your notebook, type %md and press Enter.
  2. Type "Hello World!" and click Run. The static html text is generated, as seen in the screenshot below.
    Static html text
  3. You can format the text in bold. To display the text in bold, write the same text inside two asterisks pair and click Run.
    Text in bold
  4. To display the text in italics, write the same text inside an asterisk pair or underscore pair as shown in the screenshot, and click Run.
    Text in italics
  5. To display the text in a bulleted list, prefix *(asterisk) to the text, as shown in the screenshot below:
    Text in bulleted points
  6. To display the text in heading1, heading 2 and heading 2, prefix # (hash) to the text and click Run. For H1, H2, and H3, you must prefix one, two, and three hashes respectively.
    Headings

Parent topic: About Oracle Machine Learning Notebooks

5.10 Use the Scratchpad

The Scratchpad provides you convenient one-click access to a notebook for running SQL statements, PL/SQL, R, and Python scripts that can be renamed. The Scratchpad is available on the Oracle Machine Learning User Interface (UI) home page.

Note:

The Scratchpad is a regular notebook that is prepopulated with four paragraphs - %sql, %script, , %python and %r.

After you run your scripts, the Scratchpad is automatically saved as a notebook by the default name Scratchpad on the Notebooks page. You can access it later on the Notebooks page. You can run all the paragraphs together or one paragraph at a time.
  1. To open and use the scratchpad, click Scratchpad on the Oracle Machine Learning UI home page under Quick Actions. The Scratchpad opens. The Scratchpad has three paragraphs each with the following directives:
    • %sql — Allows you to run SQL statements.
    • %script — Allows you to run PL/SQL scripts.
    • %python — Allows you to run Python scripts.
    • %r — Allows you to run R scripts.

    Figure 5-4 Scratchpad

    Scratchpad
  2. To run SQL script:
    1. Go to the paragraph with the %sql directive.
    2. Type the following command and click the Run icon. Alternatively, you can press Shift+Enter keys to run the paragraph.
      SELECT * FROM SH.SALES;
    In this example, the SQL statement fetches all of the data about product sales from the table SALES. Here, SH is the schema name, and SALES is the table name. Oracle Machine Learning UI fetches the relevant data from the database and displays it in a tabular format.

    Figure 5-5 SQL Statement in Scratchpad

    SQL Statement in Scratchpad
  3. To run PL/SQL script:
    1. Go to the paragraph with the %script directive.
    2. Enter the following PL/SQL script and click the Run icon. Alternatively, you can press Shift+Enter keys to run the paragraph.
      CREATE TABLE small_table
	(
	 NAME VARCHAR(200),
	 ID1 INTEGER,
	 ID2 VARCHAR(200),
	 ID3 VARCHAR(200),
	 ID4 VARCHAR(200),
	 TEXT VARCHAR(200)
	);

	BEGIN 
		FOR i IN 1..100 LOOP
				INSERT INTO small_table VALUES ('Name_'||i, i,'ID2_'||i,'ID3_'||i,'ID4_'||i,'TEXT_'||i);
		END LOOP;
		COMMIT;
	END;
      The PL/SQL script successfully creates the table SMALL_TABLE. The PL/SQL script in this example contains two parts:
      • The first part of the script contains the SQL statement CREATE TABLE to create a table named small_table. It defines the table name, table column, data types, and size. In this example, the column names are NAME, ID1, ID2, ID3, ID4, and TEXT.
      • The second part of the script begins with the keyword BEGIN. It inserts 100 rows in to the table small_table.

      Note:

      When using the CREATE statement with a primary key, it fails and displays the error message Insufficient privileges. This error occurs due to lockdown profiles in the database. If you encounter this error, contact your database administrator or the designated security administrator to grant the required privileges.

      Figure 5-6 PL/SQL Script in Scratchpad

      PL/SQL Procedure in Scratchpad
  4. To run python script:
    1. To use OML4Py, you must first import the oml module. oml is the OML4Py module that allows you to manipulate Oracle Database objects such as tables and views, call user-defined Python functions using embedded execution, and use the database machine learning algorithms. Go to the paragraph with %python directive. To import the oml module, type the following command and click the Run icon. Alternatively, you can press Shift+Enter keys to run the paragraph.
      import oml
    2. To check if the oml module is connected to Oracle Database, type oml.isconnected() and click the Run icon. Alternatively, you can press Shift+Enter keys to run the paragraph. 
      oml.isconnected()
    3. You are now ready to run your Python script. Type the following Python code and click the run icon. Alternatively, you can press Shift+Enter keys to run the paragraph.
      import matplotlib.pyplot as plt
import numpy as np

list1 = np.random.rand(10)*2.1
list2 = np.random.rand(10)*3.0

plt.subplot(1,2,1) # 1 line, 2 rows, index nr 1 (first position in subplot)
plt.hist(list1)
plt.subplot(1, 2, 2) # 1 line, 2 rows, index nr 2 (second position in subplot)
plt.hist(list2)
plt.show()
      In this example, the commands import two python packages to compute and render the data in two histograms for list1 and list2. The Python packages are:
      • Matplotlib — Python package to render graphs.
      • Numpy — Python package for computations.

      Figure 5-7 Python Script in Scratchpad

      Python Script in Scratchpad

      The two graphs for list1 and list 2 are generated by the python engine, as shown in the screenshot here.

  5. After you have created and run your scripts in the Scratchpad, the Scratchpad is automatically saved as a notebook by the name default name Scratchpad on the Notebooks page. You can edit the name of the notebook and save it with the new name by clicking Edit.

Parent topic: About Oracle Machine Learning Notebooks

5.11 Collaborate in Oracle Machine Learning

Two or more users can collaborate and share Oracle Machine Learning User Interface (UI) notebooks with other users.

You can collaborate by:

  • Granting Access to Workspace of Another User
  • Using the Export Option
  • Using Oracle Machine Learning UI notebooks templates

Tutorial icon Collaborating in Oracle Machine Learning

Parent topic: About Oracle Machine Learning Notebooks