4.7 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 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 AI 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 AI 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

4.7.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 AI 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 Notebooks 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

4.7.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

4.7.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