1. Using Oracle Machine Learning User Interface on Autonomous Database
  2. OML Notebooks
  3. Use the SQL Interpreter in a Notebook Paragraph

4.8 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: OML Notebooks