Oracle NoSQL Database Python SDK - 5.4.1

This is the Python SDK for the Oracle NoSQL Database. Python 3.5+ is supported.

For information about the Oracle NoSQL Database see https://www.oracle.com/database/technologies/related/nosql.html

Installation

This topic describes how to install, configure, and use the Oracle NoSQL Database Python SDK. There are several supported environments:

  1. Oracle NoSQL Database Cloud Service
  2. Oracle NoSQL Database Cloud Simulator
  3. Oracle NoSQL Database on-premise

Prerequisites

The Python SDK requires:

  • Python version 3.5 or later, running on Mac, Windows, or Linux.
  • For the Oracle NoSQL Cloud Service:
    • An Oracle Cloud Infrastructure account
    • A user created in that account, in a group with a policy that grants the desired permissions.
  • For the Oracle NoSQL Database Cloud Simulator:
  • For the on-premise Oracle NoSQL Database:

Downloading and Installing the SDK

You can install the Python SDK through the Python Package Index (PyPI), or alternatively through GitHub.

PyPi

To install from PyPI use the following command:

pip install borneo

GitHub

To install from GitHub:

  1. Download the SDK from GitHub. The download is a zip containing a whl file and documentation.

  2. Extract the files from the zip.

  3. Use the following command to install the SDK:

    pip install borneo-*-py2.py3-none-any.whl

Note

If you’re unable to install the whl file, make sure pip is up to date. Use pip install -U pip and then try to install the whl file again.

Configuring the SDK

This section describes configuring the SDK for the 3 environments supported. Skip to the section or sections of interest. The areas where the environments and use differ are

  1. Authentication and authorization. This is encapsulated in the AuthorizationProvider interface. The Cloud Service is secure and requires a Cloud Service identity as well as authorization for desired operations. The Cloud Simulator is not secure at all and requires no identity. The on-premise configuration can be either secure or not and it also requires an instance of the proxy service to access the database.
  2. API differences. Some classes and methods are specific to an environment. For example, the on-premise configuration includes methods to create namespaces and users and these concepts don’t exist in the cloud service. Similarly, the cloud service includes interfaces to specify and acquire throughput information on tables that is not relevant on-premise.

Configure for the Cloud Service

The SDK requires an Oracle Cloud account and a subscription to the Oracle NoSQL Database Cloud Service. If you do not already have an Oracle Cloud account you can start here. Credentials used for connecting an application are associated with a specific user. If needed, create a user for the person or system using the api. See Adding Users.

Using the SDK with the Oracle NoSQL Database Cloud Service also requires installation of the Oracle Cloud Infrastructure (OCI) Python SDK:

pip install oci
Acquire Credentials for the Oracle NoSQL Database Cloud Service

These steps only need to be performed one time for a user. If they have already been done they can be skipped. You need to obtain the following credentials:

  • Tenancy ID
  • User ID
  • API signing key (private key in PEM format)
  • Private key pass phrase, only needed if the private key is encrypted
  • Fingerprint for the public key uploaded to the user’s account

See Required Keys and OCIDs for detailed descriptions of the above credentials and the steps you need to perform to obtain them. Specifically:

Supplying Credentials to an Application

Credentials are used to establish the initial connection from your application to the service. There are 2 ways to supply credentials to the application:

  1. Directly, via API
  2. Using a configuration file

Both mechanisms use borneo.iam.SignatureProvider to handle credentials. If using a configuration file it’s default location is $HOME/.oci/config, but the location can be changed using the api.

The format of the configuration file is that of a properties file with the format of key=value, with one property per line. The contents and format are:

[DEFAULT]
tenancy=<your-tenancy-id>
user=<your-user-id>
fingerprint=<fingerprint-of-your-public-key>
key_file=<path-to-your-private-key-file>
pass_phrase=<optional-pass-phrase-for-key-file>

The Tenancy ID, User ID and fingerprint should be acquired using the instructions above. The path to your private key file is the absolute path of the RSA private key. The order of the properties does not matter. The [DEFAULT] portion is the profile. A configuration file may contain multiple profiles with the target profile specified in the borneo.iam.SignatureProvider parameters.

Provide credentials without a configuration file:

from borneo.iam import SignatureProvider

#
# Use SignatureProvider directly via API. Note that the
# private_key argument can either point to a key file or be the
# string content of the private key itself.
#
at_provider = SignatureProvider(
    tenant_id='ocid1.tenancy.oc1..tenancy',
    user_id='ocid1.user.oc1..user',
    private_key=key_file_or_key,
    fingerprint='fingerprint',
    pass_phrase='mypassphrase')

Provide credentials using a configuration file in the default location, using the default profile:

from borneo.iam import SignatureProvider

#
# Use SignatureProvider with a default credentials file and
# profile $HOME/.oci/config
#
at_provider = SignatureProvider()

Provide credentials using a configuration file in a non-default location and non-default profile:

from borneo.iam import SignatureProvider

#
# Use SignatureProvider with a non-default credentials file and
# profile
#
at_provider = SignatureProvider(config_file='myconfigfile',
    profile_name='myprofile')
Connecting an Application

The first step in any Oracle NoSQL Database Cloud Service application is to create a handle used to send requests to the service. The handle is configured using your credentials and other authentication information as well as the endpoint to which the application will connect. An example endpoint is to use the region Regions.US_ASHBURN_1. Information on regions can be found in borneo.Regions.

from borneo import NoSQLHandle, NoSQLHandleConfig, Regions
from borneo.iam import SignatureProvider

#
# Required information:
#

# the region to which the application will connect
region = Regions.US_ASHBURN_1

# if using a specified credentials file
credentials_file = <path-to-your-credentials-file>

#
# Create an AuthorizationProvider
#
at_provider = SignatureProvider(config_file=credentials_file)

#
# create a configuration object
#
config = NoSQLHandleConfig(region, at_provider)

#
# create a handle from the configuration object
#
handle = NoSQLHandle(config)

See examples and test code for specific details. Both of these use config*.py files for configuration of required information.

Configure for the Cloud Simulator

The Oracle NoSQL Cloud Simulator is a useful way to use this SDK to connect to a local server that supports the same protocol. The Cloud Simulator requires Java 8 or higher.

See Download the Oracle NoSQL Cloud Simulator to download and start the Cloud Simulator.

  1. Download and start the Cloud Simulator
  2. Follow instructions in the examples/config.py file for connecting examples to the Cloud Simulator. By default that file is configured to communicate with the Cloud Simulator, using default configuration.

The Cloud Simulator does not require the credentials and authentication information required by the Oracle NoSQL Database Cloud Service. The Cloud Simulator should not be used for deploying applications or important data.

Before using the Cloud Service it is recommended that users start with the Cloud Simulator to become familiar with the interfaces supported by the SDK.

Configure for the On-Premise Oracle NoSQL Database

The on-premise configuration requires a running instance of the Oracle NoSQL database. In addition a running proxy service is required. See Oracle NoSQL Database Downloads for downloads, and see Information about the proxy for proxy configuration information.

If running a secure store, a certificate path should be specified through the REQUESTS_CA_BUNDLE environment variable:

$ export REQUESTS_CA_BUNDLE=<path-to-certificate>/certificate.pem:$REQUESTS_CA_BUNDLE

Or borneo.NoSQLHandleConfig.set_ssl_ca_certs().

In addition, a user identity must be created in the store (separately) that has permission to perform the required operations of the application, such as manipulated tables and data. The identity is used in the borneo.kv.StoreAccessTokenProvider.

If the store is not secure, an empty instance of borneo.kv.StoreAccessTokenProvider is used. For example:

from borneo import NoSQLHandle, NoSQLHandleConfig
from borneo.kv import StoreAccessTokenProvider

#
# Assume the proxy is running on localhost:8080
#
endpoint = 'http://localhost:8080'

#
# Assume the proxy is secure and running on localhost:443
#
endpoint = 'https://localhost:443'

#
# Create the AuthorizationProvider for a secure store:
#
ap = StoreAccessTokenProvider(user_name, password)

#
# Create the AuthorizationProvider for a not secure store:
#
ap = StoreAccessTokenProvider()

#
# create a configuration object
#
config = NoSQLHandleConfig(endpoint).set_authorization_provider(ap)

#
# set the certificate path if running a secure store
#
config.set_ssl_ca_certs(<ca_certs>)

#
# create a handle from the configuration object
#
handle = NoSQLHandle(config)

Working With Tables

Applications using the Oracle NoSQL Database work with tables. Tables are created and data is added, modified and removed. Indexes can be added on tables. These topics are covered. Not all options and functions are described here. Detailed descriptions of interfaces can be found in API Reference.

Obtain a NoSQL Handle

borneo.NoSQLHandle represents a connection to the service. Once created it must be closed using the method borneo.NoSQLHandle.close() in order to clean up resources. Handles are thread-safe and intended to be shared. A handle is created by first creating a borneo.NoSQLHandleConfig instance to configure the communication endpoint, authorization information, as well as default values for handle configuration.

Configuration requires an borneo.AuthorizationProvider to provide identity and authorization information to the handle. There are different instances of this class for the different environments:

  1. Oracle NoSQL Cloud Service
  2. Oracle NoSQL Cloud Simulator
  3. Oracle NoSQL Database on-premise

About Compartments

In the Oracle NoSQL Cloud Service environment tables are always created in an Oracle Cloud Infrastructure compartment (see Managing Compartments). It is recommended that compartments be created for tables to better organize them and control security, which is a feature of compartments. When authorized as a specific user the default compartment for tables is the root compartment of the user’s tenancy. A method exists to allow specification of a default compartment for requests in borneo.NoSQLHandleConfig.set_compartment(). This overrides the user’s default. In addition it is possible to specify a compartment is each Request instance.

The set_compartment methods take either an id (OCID) or a compartment name or path. If a compartment name is used it may be the name of a top-level compartment. If a compartment path is used to reference a nested compartment, the path is a dot-separate path that excludes the top-level compartment of the path, for example compartmentA.compartmentB.

Instead of setting a compartment in the request it is possible to use a compartment name to prefix a table name in a request, query, or DDL statement. This usage overrides any other setting of the compartment. E.g.

...
request = PutRequest().set_table_name('mycompartment:mytable')
...
create_statement = 'create table mycompartment:mytable(...)'
...
request = GetRequest().set_table_name('compartmentA.compartmentB')

If the application is authorized using an instance principal (see borneo.iam.SignatureProvider.create_with_instance_principal()) a compartment must be specified either using a default or in each request, and it must be specified as an id, as there is no default root compartment in this path.

An example of acquiring a NoSQL Handle for the Oracle NoSQL Cloud Service:

from borneo import NoSQLHandle, NoSQLHandleConfig, Regions
from borneo.iam import SignatureProvider

# create AuthorizationProvider
provider = SignatureProvider()

# create handle config using the correct desired region as endpoint, add a
# default compartment.
config = NoSQLHandleConfig(Regions.US_ASHBURN_1).set_authorization_provider(
    provider).set_default_compartment('mycompartment')

# create the handle
handle = NoSQLHandle(config)

An example using the on-premise Oracle NoSQL Database in a secure configuration, a certificate path should be specified through the REQUESTS_CA_BUNDLE environment variable:

$ export REQUESTS_CA_BUNDLE=<path-to-certificate>/certificate.pem:$REQUESTS_CA_BUNDLE

Or borneo.NoSQLHandleConfig.set_ssl_ca_certs(), for example:

from borneo import NoSQLHandle, NoSQLHandleConfig
from borneo.kv import StoreAccessTokenProvider

# create AuthorizationProvider
provider = StoreAccessTokenProvider(<user_name>, <password>)

# create handle config using the correct endpoint for the running proxy
config = NoSQLHandleConfig(
    'https://localhost:443').set_authorization_provider(
    provider).set_ssl_ca_certs(<ca_certs>)

# create the handle
handle = NoSQLHandle(config)

To reduce resource usage and overhead of handle creation it is best to avoid excessive creation and closing of borneo.NoSQLHandle instances.

Create Tables and Indexes

Learn how to create tables and indexes in Oracle NoSQL Database.

Creating a table is the first step of developing your application. You use the borneo.TableRequest class and its methods to execute Data Definition Language (DDL) statements, such as, creating, modifying, and dropping tables. If using the Oracle NoSQL Cloud Service or Cloud Simulator you must also set table limits using borneo.TableRequest.set_table_limits() method. Limits are ignored on-premise, if provided.

Before creating a table, learn about:

The supported data types for Oracle NoSQL Database. See Supported Data Types. Also see Data Types for a description of how database types map to Python.

For the Oracle NoSQL Database Cloud Service limits. See Oracle NoSQL Database Cloud Limits. These limits are not relevant on-premise.

Examples of DDL statements are:

# Create a new table called users
CREATE IF NOT EXISTS users (id INTEGER, name STRING, PRIMARY KEY (id))

# Create a new table called users and set the TTl value to 4 days
CREATE IF NOT EXISTS users (id INTEGER, name STRING, PRIMARY KEY (id))
USING TTL 4 days

# Create a new index called nameIdx on the name field in the users table
CREATE INDEX IF NOT EXISTS nameIdx ON users(name)

DDL statements are executing using the borneo.TableRequest class. All calls to borneo.NoSQLHandle.table_request() are asynchronous so it is necessary to check the result and call borneo.TableResult.wait_for_completion() to wait for operation to complete. The convenience method, borneo.NoSQLHandle.do_table_request(), exists to combine execution of the operation with waiting for completion.

from borneo import TableLimits, TableRequest

statement = 'create table if not exists users(id integer, name string, ' +
            'primary key(id)'

# In the Cloud Service TableLimits is a required object for table creation.
# It specifies the throughput and capacity for the table in ReadUnits,
# WriteUnits, GB
request = TableRequest().set_statement(statement).set_table_limits(
    TableLimits(20, 10, 5))

# assume that a handle has been created, as handle, make the request wait
# for 40 seconds, polling every 3 seconds
result = handle.do_table_request(request, 40000, 3000)

# the above call to do_table_request is equivalent to
result = handle.table_request(request)
result.wait_for_completion(handle, 40000, 3000)

Add Data

Add rows to your table.

When you store data in table rows, your application can easily retrieve, add to, or delete information from the table.

The borneo.PutRequest class represents input to the borneo.NoSQLHandle.put() method used to insert single rows. This method can be used for unconditional and conditional puts to:

Options can be set using borneo.PutRequest.set_option().

To add rows to your table:

from borneo import PutRequest

# PutRequest requires a table name
 request = PutRequest().set_table_name('users')

# set the value
request.set_value({'id': i, 'name': 'myname'})
result = handle.put(request)

# a successful put returns a non-empty version
if result.get_version() is not None:
   # success

When adding data the values supplied must accurately correspond to the schema for the table. If they do not, IllegalArgumentException is raised. Columns with default or nullable values can be left out without error, but it is recommended that values be provided for all columns to avoid unexpected defaults. By default, unexpected columns are ignored silently, and the value is put using the expected columns.

If you have multiple rows that share the same shard key they can be put in a single request using borneo.WriteMultipleRequest which can be created using a number of PutRequest or DeleteRequest objects.

You can also add JSON data to your table. In the case of a fixed-schema table the JSON is converted to the target schema. JSON data can be directly inserted into a column of type JSON. The use of the JSON data type allows you to create table data without a fixed schema, allowing more flexible use of the data.

Add JSON Data

The data value provided for a row or key is a Python dict. It can be supplied to the relevant requests (GetRequest, PutRequest, DeleteRequest) in multiple ways:

  • as a Python dict directly:

    request.set_value({'id': 1})
request.set_key({'id': 1 })

  • as a JSON string:

    request.set_value_from_json('{"id": 1, "name": "myname"}')
request.set_key_from_json('{"id": 1}')

In both cases the keys and values provided must accurately correspond to the schema of the table. If not an borneo.IllegalArgumentException exception is raised. If the data is provided as JSON and the JSON cannot be parsed a ValueError is raised.

Read Data

Learn how to read data from your table.

You can read single rows using the borneo.NoSQLHandle.get() method. This method allows you to retrieve a record based on its primary key value. In order to read multiple rows in a single request see Use Queries, below.

The borneo.GetRequest class is used for simple get operations. It contains the primary key value for the target row and returns an instance of borneo.GetResult.

from borneo import GetRequest

# GetRequest requires a table name
request = GetRequest().set_table_name('users')

# set the primary key to use
request.set_key({'id': 1})
result = handle.get(request)

# on success the value is not empty
if result.get_value() is not None:
   # success

By default all read operations are eventually consistent, using borneo.Consistency.EVENTUAL. This type of read is less costly than those using absolute consistency, borneo.Consistency.ABSOLUTE. This default can be changed in borneo.NoSQLHandle using borneo.NoSQLHandleConfig.set_consistency() before creating the handle. It can be changed for a single request using borneo.GetRequest.set_consistency().

Use Queries

Learn about using queries in your application.

Oracle NoSQL Database provides a rich query language to read and update data. See the SQL For NoSQL Specification for a full description of the query language.

There are two ways to get the results of a query: using an iterator or loop through partial results.

Iterator

Use borneo.NoSQLHandle.query_iterable() to get an iterable that contains all the results of a query. Usage example:

from borneo import QueryRequest

handle = ...
statement = 'select * from users where name = "Taylor"'
request = QueryRequest().set_statement(statement)
qiresult = handle.query_iterable(request)
for row in qiresult:
    # do something with the result row
    print(row)

Partial results

Another way is to loop through partial results by using the borneo.NoSQLHandle.query() method. For example, to execute a SELECT query to read data from your table, a borneo.QueryResult contains a list of results. And if the borneo.QueryRequest.is_done() returns False, there may be more results, so queries should generally be run in a loop. It is possible for single request to return no results but the query still not done, indicating that the query loop should continue. For example:

from borneo import QueryRequest

# Query at table named 'users" using the field 'name' where name may match 0
# or more rows in the table. The table name is inferred from the query
# statement
statement = 'select * from users where name = "Taylor"'
request = QueryRequest().set_statement(statement)
# loop until request is done, handling results as they arrive
while True:
    result = handle.query(request)
    # handle results
    handle_results(result) # do something with results
    if request.is_done():
        break

When using queries it is important to be aware of the following considerations:

  • Oracle NoSQL Database provides the ability to prepare queries for execution and reuse. It is recommended that you use prepared queries when you run the same query for multiple times. When you use prepared queries, the execution is much more efficient than starting with a query string every time. The query language and API support query variables to assist with query reuse. See borneo.NoSQLHandle.prepare() and borneo.PrepareRequest for more information.
  • The borneo.QueryRequest allows you to set the read consistency for a query as well as modifying the maximum amount of resource (read and write) to be used by a single request. This can be important to prevent a query from getting throttled because it uses too much resource too quickly.

Here is an example of using a prepared query with a single variable:

from borneo import PrepareRequest, QueryRequest

# Use a similar query to above but make the name a variable
statement = 'declare $name string; select * from users where name = $name'
prequest = PrepareRequest().set_statement(statement)
presult = handle.prepare(prequest)

# use the prepared statement, set the variable
pstatement = presult.get_prepared_statement()
pstatement.set_variable('$name', 'Taylor')
qrequest = QueryRequest().set_prepared_statement(pstatement)
qiresult = handle.query_iterable(qrequest)
# loop on all the results
for row in qiresult:
    # do something with the result row
    print(row)


# use a different variable value with the same prepared query
pstatement.set_variable('$name', 'another_name')
qrequest = QueryRequest().set_prepared_statement(pstatement)
# loop until qrequest is done, handling results as they arrive
while True:
    # use the prepared query in the query request
    qresult = handle.query(qrequest)
    # handle results
    handle_results(qresult) # do something with results
    if qrequest.is_done():
        break

Delete Data

Learn how to delete rows from your table.

Single rows are deleted using borneo.DeleteRequest using a primary key value:

from borneo import DeleteRequest

# DeleteRequest requires table name and primary key
request = DeleteRequest().set_table_name('users')
request.set_key({'id': 1})

# perform the operation
result = handle.delete(request)
if result.get_success():
   # success -- the row was deleted

# if the row didn't exist or was not deleted for any other reason, False is
# returned

Delete operations can be conditional based on a borneo.Version returned from a get operation. See borneo.DeleteRequest.

You can perform multiple deletes in a single operation using a value range using borneo.MultiDeleteRequest and borneo.NoSQLHandle.multi_delete().

Modify Tables

Learn how to modify tables. You modify a table to:

  • Add or remove fields to an existing table
  • Change the default TimeToLive (TTL) value for the table
  • Modify table limits

Examples of DDL statements to modify a table are:

# Add a new field to the table
ALTER TABLE users (ADD age INTEGER)

# Drop an existing field from the table
ALTER TABLE users (DROP age)

# Modify the default TTl value
ALTER TABLE users USING TTL 4 days

If using the Oracle NoSQL Database Cloud Service table limits can be modified using borneo.TableRequest.set_table_limits(), for example:

from borneo import TableLimits, TableRequest

# in this path the table name is required, as there is no DDL statement
request = TableRequest().set_table_name('users')
request.set_table_limits(TableLimits(40, 10, 5))
result = handle.table_request(request)

# table_request is asynchronous, so wait for the operation to complete, wait
# for 40 seconds, polling every 3 seconds
result.wait_for_completion(handle, 40000, 3000)

Delete Tables and Indexes

Learn how to delete a table or index.

To drop a table or index, use the drop table or drop index DDL statement, for example:

# drop the table named users (implicitly drops any indexes on that table)
DROP TABLE users

# drop the index called nameIndex on the table users. Don't fail if the index
# doesn't exist
DROP INDEX IF EXISTS nameIndex ON users

from borneo import TableRequest

# the drop statement
statement = 'drop table users'
request = TableRequest().set_statement(statement)

# perform the operation, wait for 40 seconds, polling every 3 seconds
result = handle.do_table_request(request, 40000, 3000)

Handle Errors

Python errors are raised as exceptions defined as part of the API. They are all instances of Python’s RuntimeError. Most exceptions are instances of borneo.NoSQLException which is a base class for exceptions raised by the Python driver.

Exceptions are split into 2 broad categories:

borneo.ThrottlingException instances will never be thrown in an on-premise configuration as there are no relevant limits.

Handle Resource Limits

This section is relevant only to the Cloud Service and Simulator.

Programming in a resource-limited environment can be unfamiliar and can lead to unexpected errors. Tables have user-specified throughput limits and if an application exceeds those limits it may be throttled, which means requests will raise instances of borneo.ThrottlingException.

There is some support for built-in retries and users can create their own borneo.RetryHandler instances to be set using borneo.NoSQLHandleConfig.set_retry_handler() allowing more direct control over retries as well as tracing of throttling events. An application should not rely on retries to handle throttling exceptions as that will result in poor performance and an inability to use all of the throughput available for the table. This happens because the default retry handler will do exponential backoff, starting with a one-second delay.

While handling borneo.ThrottlingException is necessary it is best to avoid throttling entirely by rate-limiting your application. In this context rate-limiting means keeping request rates under the limits for the table. This is most common using queries, which can read a lot of data, using up capacity very quickly. It can also happen for get and put operations that run in a tight loop. Some tools to control your request rate include:

  • use the methods available in all Result objects that indicate how much read and write throughput was used by that request. For example, see borneo.GetResult.get_read_units() or borneo.PutResult.get_write_units().
  • reduce the default amount of data read for a single query request by using borneo.QueryRequest.set_max_read_kb(). Remember to perform query operations in a loop, looking at the continuation key. Be aware that a single query request can return 0 results but still have a continuation key that means you need to keep looping.
  • add rate-limiting code in your request loop. This may be as simple as a delay between requests or intelligent code that considers how much data has been read (see borneo.QueryResult.get_read_units()) as well as the capacity of the table to either delay a request or reduce the amount of data to be read.

Data Types

This topic describes the mapping between types in the Oracle NoSQL Database and Python data types. The database types are referred to as database types while the Python equivalents are Python types.

Oracle NoSQL Database Types

See Supported Data Types for a description of the data types supported by the service. An application uses these types to create tables and indexes. For example, a table may be created using this Data Definition Language (DDL) statement, which defines types in terms of the database types:

create table mytable(id integer, name string, created timestamp,
    address record(street string, city string, zip integer), primary key(id))

In order to insert rows into such a table your application must create a Python dict that corresponds to that schema, for example:

{'id': 1, 'name': 'myname', 'created': datetime.now(),
 'address' : {'street' : '14 Elm Street', 'city' : 'hometown',
 'zip' : 00000}}

Similarly, when operating on rows retrieved from the database it is important to understand the mappings to Python types.

Mapping Between Database and Python types

These mappings apply on both input (get/query) and output (put). In general the system is permissive in terms of valid conversions among types and that any lossless conversion is allowed. For example an integer will be accepted for a float or double database type. The Timestamp type is also flexible and will accept any valid IS0 8601 formatted string. Timestamps are always stored and managed in UTC.

Database Type Python Type
Integer int
Long int (Python 3), long (Python2)
Float float
Double float
Number decimal.Decimal
Boolean bool
String str
Timestamp datetime.datetime
Enum str
Binary bytearray
FixedBinary bytearray
Array list
Map dict
Record dict
JSON any Python datatype that can be represented as JSON without data loss

Timestamp in Borneo

As mentioned above Timestamp fields are managed internally as UTC time. If a timezone is supplied when setting a Timestamp, either as a string or as a Python datetime object, it will be honored. The value will be converted to UTC internally and will be in UTC when returned in a row. Although they are represented in UTC returned datetime objects will be “naive” as described by Python documentation. On input, if no timezone is supplied, python datetime instances and time strings are treated as UTC.

API Reference

borneo Package

Classes

AuthorizationProvider AuthorizationProvider is a callback interface used by the driver to obtain an authorization string for a request.
BatchOperationNumberLimitException(message) Cloud service only.
Consistency Set the consistency for read requests.
Durability(master_sync, replica_sync, …) Durability defines the durability characteristics associated with a standalone write (put or update) operation.
DefaultRetryHandler([retries, delay_s]) Default retry handler.
DeleteRequest() Represents the input to a NoSQLHandle.delete() operation.
DeleteResult() Represents the result of a NoSQLHandle.delete() operation.
FieldRange(field_path) FieldRange defines a range of values to be used in a NoSQLHandle.multi_delete() operation, as specified in MultiDeleteRequest.set_range().
GetIndexesRequest() Represents the argument of a NoSQLHandle.get_indexes() operation which returns the information of a specific index or all indexes of the specified table, as returned in GetIndexesResult.
GetIndexesResult() Represents the result of a NoSQLHandle.get_indexes() operation.
GetRequest() Represents the input to a NoSQLHandle.get() operation which returns a single row based on the specified key.
GetResult() Represents the result of a NoSQLHandle.get() operation.
GetTableRequest() Represents the argument of a NoSQLHandle.get_table() operation which returns static information associated with a table, as returned in TableResult.
IllegalArgumentException([message, cause]) Exception class that is used when an invalid argument was passed, this could mean that the type is not the expected or the value is not valid for the specific case.
IllegalStateException([message, cause]) Exception that is thrown when a method has been invoked at an illegal or inappropriate time.
IndexExistsException(message) The operation attempted to create an index for a table but the named index already exists.
IndexInfo(index_name, field_names[, field_types]) IndexInfo represents the information about a single index including its name, field names and field types.
IndexNotFoundException(message) The operation attempted to access a index that does not exist or is not in a visible state.
InvalidAuthorizationException(message) The exception is thrown if the application presents an invalid authorization string in a request.
ListTablesRequest() Represents the argument of a NoSQLHandle.list_tables() operation which lists all available tables associated with the identity associated with the handle used for the operation.
ListTablesResult() Represents the result of a NoSQLHandle.list_tables() operation.
MultiDeleteRequest() Represents the input to a NoSQLHandle.multi_delete() operation which can be used to delete a range of values that match the primary key and range provided.
MultiDeleteResult() Represents the result of a NoSQLHandle.multi_delete() operation.
NoSQLException(message[, cause]) A base class for most exceptions thrown by the NoSQL driver.
NoSQLHandle(config) NoSQLHandle is a handle that can be used to access Oracle NoSQL tables.
NoSQLHandleConfig([endpoint, provider]) An instance of this class is required by NoSQLHandle.
OperationNotSupportedException(message) The operation attempted is not supported.
OperationResult() A single Result associated with the execution of an individual operation in a NoSQLHandle.write_multiple() request.
OperationThrottlingException(message) Cloud service only.
PreparedStatement(sql_text, query_plan, …) A class encapsulating a prepared query statement.
PrepareRequest() A request that encapsulates a query prepare call.
PrepareResult() The result of a prepare operation.
PutOption Set the put option for put requests.
PutRequest() Represents the input to a NoSQLHandle.put() operation.
PutResult() Represents the result of a NoSQLHandle.put() operation.
QueryRequest() A request that represents a query.
QueryResult(request[, computed]) QueryResult comprises a list of dict instances representing the query results.
QueryIterableResult(request, handle) QueryIterableResult comprises an iterable list of dict instances representing all the query results.
ReadThrottlingException(message) Cloud service only.
Region(region_id) Cloud service only.
Regions Cloud service only.
Request() A request is a class used as a base for all requests types.
RequestSizeLimitException(message) Cloud service only.
RequestTimeoutException(message[, …]) Thrown when a request cannot be processed because the configured timeout interval is exceeded.
ResourceExistsException(message) The operation attempted to create a resource but it already exists.
ResourcePrincipalClaimKeys Claim keys in the resource principal session token(RPST).
ResourceNotFoundException(message) The operation attempted to access a resource that does not exist or is not in a visible state.
Result() Result is a base class for result classes for all supported operations.
RetryHandler RetryHandler is called by the request handling system when a RetryableException is thrown.
RetryableException(message) A base class for all exceptions that may be retried with a reasonable expectation that they may succeed on retry.
SecurityInfoNotReadyException(message) Cloud service only.
State Represents the table state.
StatsControl(config, logger, …) StatsControl allows user to control the collection of driver statistics at
StatsProfile The following semantics are attached to the StatsProfile values:
SystemException(message) An exception that is thrown when there is an internal system problem.
SystemRequest() On-premise only.
SystemResult() On-premise only.
SystemState On-premise only.
SystemStatusRequest() On-premise only.
TableExistsException(message) The operation attempted to create a table but the named table already exists.
TableLimits(read_units, write_units, storage_gb) Cloud service only.
TableNotFoundException(message) The operation attempted to access a table that does not exist or is not in a visible state.
TableRequest() TableRequest is used to create, modify, and drop tables.
TableResult() TableResult is returned from NoSQLHandle.get_table() and NoSQLHandle.table_request() operations.
TableUsageRequest() Cloud service only.
TableUsageResult() Cloud service only.
ThrottlingException(message) Cloud service only.
TimeToLive(value, timeunit) TimeToLive is a utility class that represents a period of time, similar to java.time.Duration in Java, but specialized to the needs of this driver.
TimeUnit The time unit to use.
UserInfo(user_id, user_name) On-premise only.
Version(version) Version is an opaque class that represents the version of a row in the database.
WriteMultipleRequest() Represents the input to a NoSQLHandle.write_multiple() operation.
WriteMultipleResult() Represents the result of a NoSQLHandle.write_multiple() operation.
WriteThrottlingException(message) Cloud service only.

borneo.iam Package

Classes

SignatureProvider([provider, config_file, …]) Cloud service only.

borneo.kv Package

Classes

AuthenticationException(message[, cause]) On-premise only.
StoreAccessTokenProvider([user_name, password]) On-premise only.

How to find client statistics

StatsControl allows user to control the collection of driver statistics at runtime.

The statistics data is collected for an interval of time. At the end of the interval, the stats data is logged in a specified JSON format that can be filtered and parsed. After the logging, the counters are cleared and collection of data resumes.

Collection intervals are aligned to the top of the hour. This means first interval logs may contain stats for a shorter interval.

How to enable and configure from command line

Collection of stats are controlled by the following environment variables:

  • NOSQL_STATS_PROFILE=[none|regular|more|all]
    Specifies the stats profile:
    • none - disabled.
    • regular - per request: counters, errors, latencies, delays, retries.
      This incurs minimum overhead.
    • more - stats above with 95th and 99th percentile latencies.
      This may add 0.5% overhead compared to none stats profile.
    • all - stats above with per query information.
      This may add 1% overhead compared to none stats profile.
  • NOSQL_STATS_INTERVAL=600
    Interval in seconds to log the stats, by default is 10 minutes.
  • NOSQL_STATS_PRETTY_PRINT=true
    Option to enable pretty printing of the JSON data, default value is false.

How to enable and configure using the API

Collection of stats can also be used by using the API: NoSQLHandleConfig.set_stats_profile() or StatsControl.set_profile(). At runtime stats collection can be enabled selectively by using StatsControl.start() ond StatsControl.stop(). The following example shows how to use a stats handler and how to control the stats at runtime:

def stats_handler(stats):
    # type: (Dict) -> None
    print("Stats : " + str(stats))
...
config = NoSQLHandleConfig( endpoint )
config.set_stats_profile(StatsProfile.REGULAR)
config.set_stats_interval(600)
config.set_stats_pretty_print(False)
config.set_stats_handler(stats_handler)

handle = NoSQLHandle(config)

handle = get_handle(tenant_id)

stats_control = handle.get_stats_control()

#... application code without stats

# enable observations
stats_control.start();

#... application code with REGULAR stats

# For particular parts of code profile can be changed to collect more stats.
stats_control.set_stats_profile(StatsProfile.ALL)
#... more sensitive code with ALL stats

stats_control.set_stats_profile(StatsProfile.REGULAR)
#... application code with REGULAR stats

# disable observations
stats_control.stop()

#... application code without stats
handle.close()

Example log entry

The following is an example of stats log entry using the ALL profile:

  • A one time entry containing stats id and options:
INFO: Client stats|{    // INFO log entry
"sdkName" : "Oracle NoSQL SDK for Python",  // SDK name
"sdkVersion" : "5.2.4",                 // SDK version
"clientId" : "f595b333",                  // NoSQLHandle id
"profile" : "ALL",                        // stats profile
"intervalSec" : 600,                      // interval length in seconds
"prettyPrint" : true,                     // JSON pretty print
"rateLimitingEnabled" : false}            // if rate limiting is enabled
  • An entry at the end of each interval containing the stats values:
INFO: Client stats|{
"clientId" : "b7bc7734",              // id of NoSQLHandle object
"startTime" : "2021-09-20T20:11:42Z", // UTC start interval time
"endTime" : "2021-09-20T20:11:47Z",   // UTC end interval time
"requests" : [{                       // array of types of requests
  "name" : "Get",                       // stats for GET request type
  "httpRequestCount" : 2,               // count of http requests
  "errors" : 0,                         // number of errors in interval
  "httpRequestLatencyMs" : {            // response time of http requests
    "min" : 4,                            // minimum value in interval
    "avg" : 4.5,                          // average value in interval
    "max" : 5,                            // maximum value in interval
    "95th" : 5,                           // 95th percentile value
    "99th" : 5                            // 99th percentile value
  },
  "requestSize" : {                     // http request size in bytes
    "min" : 42,                           // minimum value in interval
    "avg" : 42.5,                         // average value in interval
    "max" : 43                            // maximum value in interval
  },
  "resultSize" : {                      // http result size in bytes
    "min" : 193,                          // minimum value in interval
    "avg" : 206.5,                        // average value in interval
    "max" : 220                           // maximum value in interval
  },
  "rateLimitDelayMs" : 0,               // delay in milliseconds introduced by the rate limiter
  "retry" : {                           // retries
    "delayMs" : 0,                        // delay in milliseconds introduced by retries
    "authCount" : 0,                      // no of auth retries
    "throttleCount" : 0,                  // no of throttle retries
    "count" : 0                           // total number of retries
  }
}, {
  "name" : "Query",                   // stats for all QUERY type requests
  "httpRequestCount" : 14,
  "errors" : 0,
  "httpRequestLatencyMs" : {
    "min" : 3,
    "avg" : 13.0,
    "max" : 32,
    "95th" : 32,
    "99th" : 32
  },
  "resultSize" : {
    "min" : 146,
    "avg" : 7379.71,
    "max" : 10989
  },
  "requestSize" : {
    "min" : 65,
    "avg" : 709.85,
    "max" : 799
  },
  "rateLimitDelayMs" : 0,
  "retry" : {
    "delayMs" : 0,
    "authCount" : 0,
    "throttleCount" : 0,
    "count" : 0
  }
}, {
  "name" : "Put",                    // stats for PUT type requests
  "httpRequestCount" : 1002,
  "errors" : 0,
  "httpRequestLatencyMs" : {
    "min" : 1,
    "avg" : 4.41,
    "max" : 80,
    "95th" : 8,
    "99th" : 20
  },
  "requestSize" : {
    "min" : 90,
    "avg" : 90.16,
    "max" : 187
  },
  "resultSize" : {
    "min" : 58,
    "avg" : 58.0,
    "max" : 58
  },
  "rateLimitDelayMs" : 0,
  "retry" : {
    "delayMs" : 0,
    "authCount" : 0,
    "throttleCount" : 0,
    "count" : 0
  }
}],
"queries" : [{            // query stats aggregated by query statement
                            // query statement
  "query" : "SELECT * FROM audienceData ORDER BY cookie_id",
                            // query plan description

  "plan" : "SFW([6])
     [
       FROM:
         RECV([3])
           [
             DistributionKind : ALL_PARTITIONS,
             Sort Fields : sort_gen,
           ] as $from-0
       SELECT:
        FIELD_STEP([6])
          [
           VAR_REF($from-0)([3]),
           audienceData
          ]
     ]",
  "doesWrites" : false,
  "httpRequestCount" : 12,  // number of http calls to the server
  "unprepared" : 1,         // number of query requests without prepare
  "simple" : false,         // type of query
  "count" : 20,             // number of handle.query() API calls
  "errors" : 0,             // number of calls trowing exception
  "httpRequestLatencyMs" : {// response time of http requests in milliseconds
    "min" : 8,                // minimum value in interval
    "avg" : 14.58,            // average value in interval
    "max" : 32,               // maximum value in interval
    "95th" : 32,              // 95th percentile value in interval
    "99th" : 32               // 99th percentile value in interval
  },
  "requestSize" : {         // http request size in bytes
    "min" : 65,               // minimum value in interval
    "avg" : 732.5,            // average value in interval
    "max" : 799               // maximum value in interval
  },
  "resultSize" : {          // http result size in bytes
    "min" : 914,              // minimum value in interval
    "avg" : 8585.33,          // average value in interval
    "max" : 10989             // maximum value in interval
  },
  "rateLimitDelayMs" : 0,   // total delay introduced by rate limiter in milliseconds
  "retry" : {               // automatic retries
    "delayMs" : 0,            // delay introduced by retries
    "authCount" : 0,          // count of auth related retries
    "throttleCount" : 0,      // count of throttle related retries
    "count" : 0               // total count of retries
  }
}]

The log entries go to the logger configured in NoSQLHandlerConfig. By default, if no logger is configured the statistics entries, if enabled, will be logged to file logs/driver.log in the local directory.

Stats collection is not dependent of logging configuration, even if logging is disabled, collection of stats will still happen if stats profile other than none is used. In this case, the stats are available by using the stats handler.

Depending on the type of query, if client processing is required, for example in the case of ordered or aggregate queries, indicated by the false simple field of the query entry, the count and httpRequestsCount numbers will differ. count represents the number of handle.query() API calls and httpRequestCount represents the number of internal http requests from server. For these type of queries, the driver executes several simpler queries, per shard or partition, and than combines the results locally.

Note: connection statistics are not available for NoSQL Python driver.