Getting Started with the OML4Py "Universal" Client

The OML4Py "universal" client connects a Python session to Oracle Database running the OML4Py server using a single client package. This includes on-premises Oracle Database, Oracle Database Cloud Service, and Oracle Autonomous Database, versions 19c and 21c. With the universal client, users log into the database using the oml.connect command. The connection makes database data available to the OML4Py client and enables the processing power, memory, and storage capabilities of the database server from the client. Users can manipulate tables as views through pandas DataFrame proxy objects, build models and score data using scalable in-database algorithms, and leverage embedded Python execution to deploy user-defined Python functions, automated machine learning (AutoML), and model explainability (MLX) – all from a common Python interface. The OML4Py universal client facilitates the use of external Jupyter, JupyterLab, and Zeppelin notebook environments and IDEs like PyCharm, Spyder, among others.

In this blog, we highlight:

• Prerequisites for the OML4Py universal client
• Establishing a database connection
• Verifying the installation and configuration
• Getting started with the OML4Py universal client

OML4Py client installation prerequisites

The OML4Py client is supported on Linux 7 and 8 systems. It requires an installation of Python 3.9.5, and supporting libraries cx_Oracle 8.1.0, scikit-learn 1.0.1, scipy 1.7.3, matplotlib 3.3.3, numpy 1.21.5, and pandas 1.3.4. OML4Py is certified with these versions of the supporting libraries. The Oracle Client or Instant Client, versions 19c or 21c, are also needed to establish a database connection. Once the prerequisites are satisfied, run the OML4Py client installer script client.pl, to install the oml library. This enables the OML4Py functionality. For on-premises Oracle Database, first install the OML4Py server. On Autonomous Database, the OML4Py server components are installed by default.

To install the OML4Py universal client, you will need the following:

• Download the OML4Py universal client installer from the OML4Py Downloads on the Oracle network
• Download the Oracle Client or Instant Client for Linux on the Oracle network
• For installation and configuration instructions, refer to the OML4Py User's Guide.

Oracle Wallet

The OML4Py client creates a database connection using an Oracle Wallet with Autonomous Database, and optionally, on-premises Oracle Database, Database Cloud Service (DBCS), ExaCC, and ExaCS. A wallet is a secure software container that stores authentication and signing credentials for an Oracle Database instance. For instructions on configuring a wallet for Autonomous Database, see Install OML4Py Client for Linux for Use With Autonomous Database in the Oracle Machine Learning for Python User's Guide. For instructions on creating a wallet for on-premises Oracle Database, see Managing the Secure External Password Store for Password Credentials in the Oracle Database Security Guide. 

User credentials are added to the wallet using the mkstore command-line utility. The mkstore utility is shipped with the Oracle client. To configure a client wallet using mkstore, see Configuring a Client to Use the External Password Store in the Oracle Database Security Guide.

The wallet architecture is shown below. It resides on the client system and contains network configuration settings in tnsnames.ora, including the TNS connection alias and database-resident connection pool. The wallet also contains the SQL*Net client configuration in sqlnet.ora. The TNS_ADMIN environment variable is set to the location of the wallet directory.

Enabling AutoML, model explainability, and parallel embedded Python execution

To use Automated Machine Learning (AutoML), you must specify a running database-resident connection pool (DRCP) on the server in the automl parameter in an oml.connect invocation. Using parallel-enabled embedded Python execution and model explainability (MLX) also require a running connection pool. The connection pool is automatically available on Autonomous Database. While DRCP is included in on-premises Oracle Database and other cloud database instances, the database administrator (DBA) must start it by issuing the following SQL statement:

EXECUTE DBMS_CONNECTION_POOL.START_POOL();    `

Verify the DRCP connection pool has been started by running:

select connection_pool, status from dba_cpool_info;

Once started, the connection pool remains in this state until a DBA explicitly stops it by issuing the following command:

EXECUTE DBMS_CONNECTION_POOL.STOP_POOL();

Establishing a database connection: Oracle Autonomous Database

Connect to Oracle Autonomous Database using the wallet credential

Import the oml module and connect to the database using the oml.connect command. Provide empty strings for the user and password parameters in the oml.connect invocation to bypass exposing your Oracle Machine Learning user credentials in open text. Set the dsn parameter to the name of the TNS connection alias in the wallet.

For Autonomous Database, the connection maps to either the low, medium, or high service levels. To enable AutoML, we use the automl parameter to specify the running connection pool on the server. Here, we use the connection alias myadb_medium for the medium service level, and myadb_medium_pool for the connection pool.

import oml
oml.connect(user="", password="", dsn="myadb_medium"")
oml.connect(user="", password="", dsn="myadb_medium", automl="myadb_medium_pool")

You can also use the SQL interface for embedded Python execution. With the SQL interface, you can run user-defined Python functions in one or more separate Python engines in an Oracle database environment, manage user-defined Python functions in the OML4Py script repository, and control access to and get information about datastores and about user-defined Python functions in the script repository. To log in using SQL Plus, provide the dsn, and specify the connection pool if AutoML, parallel embedded Python execution, or MLX will be used.

sqlplus /@myadb_medium
sqlplus /@myadb_medium_pool

Connect to Oracle Autonomous Database using username and password

If user credentials have not been added to the wallet, or if you want to override the wallet credentials, you can provide the username and password in the oml.connect invocation. In this case, the username is OMLUSER and the password is mypassword. Specify the connection pool if AutoML, parallel embedded Python execution, or MLX or will be used.

import oml
oml.connect(user="OMLUSER", password="mypassword", dsn="myadb_medium")
oml.connect(user="OMLUSER", password="mypassword", dsn="myadb_medium", automl="myadb_medium_pool")

Here are the equivalent SQL Plus commands:

sqlplus OMLUSER/mypassword@myadb_medium
sqlplus OMLUSER/mypassword@myadb_medium_pool

Establishing a database connection: on-premises Oracle Database

The next section describes the variations for establishing a secure connection to your database instance, both with and without a wallet. When using a wallet to establish a connection to Oracle Database, provide empty strings for the user and password parameters in the oml.connect invocation. Set the dsn parameter to the name of the TNS connection alias to saved in the wallet. Specify the connection pool if AutoML, parallel embedded Python execution, or MLX will be used.

Connect to on-premises Oracle Database with wallet credential

import oml
oml.connect(user="", password="", dsn="myodb")
oml.connect(user="", password="", dsn="myodb", automl="myodb_pool")

Here are the equivalent SQL Plus commands:

sqlplus /@myodb
sqlplus /@myodb_pool

Connect to an on-premises Oracle Database without wallet

To create a connection an on-premises Oracle Database without using a wallet, specify arguments to the oml.connect function using any of the following combinations:

• user, password, dsn
• user, password, host, port, sid
• user, password, host, port, service_name

The dsn corresponds to a TNS connection entry for the database. The dsn information can reside in tnsnames.ora or defined as a string. To enable AutoML, include the TNS entry for the pooled connection.

An example of each follows. In these examples, the user is pyquser with password pyquser, the sid is orcl, or, if a service name is used, the service name is myodb. The TNS connection alias is myodb, and the pooled connection name is myodb_pool.

Connect with user, password, and dsn

import oml
oml.connect(user="pyquser", password="pyquser", dsn="myodb")
oml.connect(user="pyquser", password="pyquser", dsn="myodb", automl="myodb_pool")

Alternatively, pass the value of dsn as a string to the oml.connect invocation.

dsn = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST="host")(PORT="1521")) 
       (CONNECT_DATA=(SERVICE_NAME="myodb")))"​

dsn = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST="host")(PORT="1521")) 
       (CONNECT_DATA(SERVICE_NAME="myodb_pool")))"

oml.connect(user="pyquser", password="pyquser", dsn=dsn)

Here are the equivalent SQL Plus commands:

sqlplus pyquser/pyquser@myodb
sqlplus pyquser/pyquser@myodb_pool

Another option is to provide the user credentials along with the hostname, port number, and database SID or service name.

Connect using username, password, host, port, and sid

oml.connect(user="pyquser", password="pyquser", host="myhost", port=port, sid="orcl")
oml.connect(user="pyquser", password="pyquser", host="myhost", port=port, sid="orcl", automl=True)

Connect using username , password, host, port, and service name

oml.connect(user="pyquser", password="pyquser", host="myhost", port=1521, service_name="my_odb")
oml.connect(user="pyquser", password="pyquser", host="myhost", port=1521, service_name="my_odb", automl=True)

Here are the equivalent SQL Plus commands:

sqlplus pyquser/pyquser@myhost:1521/orcl
sqlplus pyquser/pyquser@myhost:1521/myodb
sqlplus pyquser/pyquser@myhost:1521/myodb__pool

Verifying the OML4Py client installation

You can validate the installation of the OML4Py universal client by doing the following:

• Step 1: Establish a database connection from Python and SQL
• Step 2: Confirm the OML4Py supporting libraries are installed with the correct versions
• Step 3: Verify embedded Python execution

Note, to use embedded Python execution, you must install the OML4Py server and client components with embedded Python execution enabled. The OML4Py server components are installed and embedded Python execution is available on Oracle Autonomous Database by default.

Verify the database connection is established and AutoML-enabled

Verify that the connection is established using the oml.connect command, and setcheck_automl to True to confirm the connection is AutoML-enabled.

oml.isconnected()
True

oml.isconnected(check_automl=True)
True

Verify embedded Python execution is enabled

Confirm the embedded Python execution is enabled in the database instance using the oml.check_embed function.

oml.check_embed()
True

Verify the client supporting packages

Both the OML4Py server and client installations require a set of supporting Python packages. OML4Py 1.0 requires the following supporting packages and versions:

• numpy 1.21.5
• pandas 1.3.4
• scipy 1.7.3
• matplotlib 3.3.3
• cx_Oracle 8.1.0
• scikit-learn 1.0.1

Load the packages to ensure they have been installed successfully, and check the version for each package. Start Python and run the following commands. If all the packages are installed successfully, then no errors are returned.

import numpy
print('numpy version:'+ ' ' + numpy.__version__)

import pandas
print('pandas version:'+ ' '+ pandas.__version__)

​import scipy
print('scipy version:'+ ' '+ scipy.__version__)

import matplotlib
print('matplotlib version:'+ ' '+ matplotlib.__version__)

import cx_Oracle
print('cx_Oracle version:'+ ' '+ cx_Oracle.__version__)

import sklearn
print('sklearn version:'+ ' '+ sklearn.__version__)

numpy version: 1.21.5
pandas version: 1.3.4
scipy version: 1.7.3
matplotlib version: 3.3.3
cx_Oracle version: 8.1.0
sklearn version: 1.0.1

Embedded Python execution validation

Embedded Python execution is an OML4Py feature that allows users to invoke user-defined Python functions directly in the database environment. To verify that embedded Python execution is configured correctly, define a simple function using Python, and then create the equivalent function using SQL.

Invoke the function by name using the embedded Python execution command oml.do_eval from the OML4Py Python interface, and using a select statement with the corresponding embedded Python function pyqEval from the OML4Py SQL interface. The test function performs a simple addition of two integers, 1+1. If embedded Python execution is configure correctly, the value 2 will be returned.

When using on-premises Oracle Database, to save a script to the script repository, you must be granted PYQADMIN role. To use an embedded Python execution function, you must have installed the OML4Py client with embedded Python execution enabled on both the server and client systems.

oml.script.create("TEST",     
            func='def func():return 1 + 1',   
            overwrite=True)

oml.do_eval(func='TEST')
  2

SQL> BEGIN 
     sys.pyqScriptCreate('TEST', 
       'def func():return 1 + 1');  
    END;  
     /  
PL/SQL procedure successfully completed.

SQL> SELECT *
     FROM table(pyqEval(NULL,'XML','TEST'));
   2

When finished testing, drop the test function from either OML4Py interface - SQL or Python

SQL> BEGIN 
   sys.pyqScriptDrop('TEST');
   END;
   /
PL/SQL procedure successfully completed.

oml.script.drop('TEST')

Getting Started with the OML4Py universal client

Once you have the OML4Py universal client installed, watch the OML4Py universal client office hours session, download and explore the OML4Py Tour Jupyter notebooks for Autonomous Database and on-premises Oracle Database, or start creating your own notebooks. To dive deeper into the OML4Py functionality, create a reservation for the Introduction to OML4Py on Autonomous Database Live Lab workshop.