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.