This morning I discovered a new entry in the menu on my Oracle Cloud Infrastructure Tenancy (on Ashburn, USA): the NoSQL Database service had been added overnight.
The service offers a key-(schema-less) document store. It also supports ACID transactions, SQL on JSON documents, joins across tables, structured tables and if needed also relaxed (eventual) consistency. This is NoSQL in the “Not Only” SQL sense of the term, because there is quite a bit of “SQL” (ACID, structured, relational and SQL) available. If you are looking for a cheap, simple, scalable, fully managed, H/A relational database (on Oracle Cloud), you might be well served by this NoSQL Database service as alternative to the Autonomous Database which is based on the Oracle Database flagship product.
I had been waiting for this service for some time – as it is crucial element of a cloud native application solution. The service was previously available on Oracle Cloud Classic – for a while as the Oracle Data Hub Cloud Service. Now it has been fully integrated with Oracle Cloud Infrastructure (Gen 2 Cloud). This makes it ready to be used for real.
Data in the NoSQL Database is held in tables that can contain a mix of structured columns – in a relational style – and columns of JSON document type. Data – both structured and unstructured – can be queried schema less JSON data by using the familiar SQL syntax. It is possible to update (change, add, and remove) parts of a JSON document. Because these updates occur on the server, the need for a read-modify-write cycle is eliminated, which would consume throughput capacity. ACID transactions are supported for full consistency. If required, consistency can be relaxed in favor of lower latency.
The managed aspect of this services results among other things in data safety: The Oracle NoSQL Database Cloud Service stores data across multiple Availability Domains (ADs) or Fault Domains (FDs) in single AD regions. If an AD or FD becomes unavailable, user data is still accessible from another AD or FD. Also encryption: Data is encrypted at rest (on disk) with Advanced Encryption Standard (AES). Data is encrypted in motion (transferring data between the application and Oracle NoSQL Database Cloud Service) with HTTPS.
In this article, I will show what my first impressions are with the NoSQL Database on OCI.
The steps I will demonstrate – using both the command line interface (OCI CLI) and the Console:
- create a table with structured column definition – in the relational tradition
- create data records in this table
- query the structured data through SQL queries
- create a table with a JSON column – for key/document storage
- create multiple document-records in this table from JSON documents
- query the JSON documents using SQL
- update the JSON documents using SQL
- delete the tables
In addition to OCI CLI and the Console, two NoSQL SDKs (Java and Python at the time of writing) are available for accessing Oracle NoSQL Database Cloud Service. All operations on tables, indexes and data in NoSQL Database service can be performed through the REST APIs. Oracle NoSQL Database Cloud Service provides easy-to-use CRUD (Create Read Update Delete) APIs.
Step 1: Create Table – Structured as a Relational Table
Creating a table in the NoSQL Database can be done in the console, through the REST API and the SDKs as well as on the command line using the OCI CLI.
In this case I will create a table with the NoSQL Database service. The table has three columns:
- id – numeric, meaningless primary key
- name – string
- country – string
I make use of two environment variables, for the OCID of the compartment and the name of the table:
export compartmentId=<OCID of the target compartment> export tableName=labTable1
Using these two variables, the following statement will create a table called labTable1 with the three columns I mentioned. Note that nothing is stopping me from defining many more columns using one of many data types on offer (see Documentation on Data Types)
oci nosql table create --compartment-id $compartmentId --name $tableName --ddl-statement "CREATE TABLE IF NOT EXISTS $tableName (id INTEGER, name STRING, country STRING, PRIMARY KEY(SHARD(id)))" --table-limits="{\"maxReadUnits\": 15, \"maxStorageInGBs\": 1, \"maxWriteUnits\": 15 }"
Check if the table has been created:
oci nosql table list --compartment-id $compartmentId
In the console, this same information is presented like this:
The details for the table can be inspected:
Step 2: create data records in this table
Data records can be created in the console – and through the REST API, the SDK and of course the OCI CLI. Let us create a record:
oci nosql query execute --compartment-id $compartmentId --statement="INSERT INTO $tableName (id, name, country) VALUES (1,\"John\",\"Scotland\")"
If the INSERT keyword is used, the row will be inserted only if it does not exist already. If the UPSERT keyword is used, the row will be inserted if it does not exist already, otherwise the new row will replace the existing one.
Step 3: query the structured data through SQL queries
Query the table for “all” its data:
oci nosql query execute --compartment-id $compartmentId --statement="SELECT * FROM $tableName"
We can execute queries in the console to inspect the data in the table(s):
Let’s create a few more records:
oci nosql query execute --compartment-id $compartmentId --statement="INSERT INTO $tableName (id, name, country) VALUES (2,\"Rolando\",\"Mexico\")" oci nosql query execute --compartment-id $compartmentId --statement="INSERT INTO $tableName (id, name, country) VALUES (3,\"Sven\",\"Germany\")" oci nosql query execute --compartment-id $compartmentId --statement="INSERT INTO $tableName (id, name, country) VALUES (4,\"José\",\"Portugal\")"
And query the table again in the console:
Step 4: create a table with a JSON column – for key/document storage
The next step is the creation of the table for schema-less JSON documents with the NoSQL Database service. Execute this next command to have a table created with three columns:
- id – numeric, meaningless primary key
- document – JSON
Set the name of the key/document table in an environment variable
export docTableName=labDocumentsTable1
Then create the table
oci nosql table create --compartment-id $compartmentId --name $docTableName --ddl-statement "CREATE TABLE IF NOT EXISTS $docTableName (id INTEGER, document JSON, PRIMARY KEY(SHARD(id)))" --table-limits="{\"maxReadUnits\": 25, \"maxStorageInGBs\": 1, \"maxWriteUnits\": 25 }"
Note: the creation of the table happens asynchronously from the oci nosql table create command. If we check for the existence of the table through the CLI (oci nosql table list –compartment-id $compartmentId),it might be required to execute the list command a few times before the table is present.
Step 5: create multiple document-records in this table from JSON documents
Insert a JSON document into the new table:
document='{ "staff":[{"name":"Sven","country":"Germany"},{"name":"José","country":"Portugal"},{"name":"Rolando","country":"Mexico"},{"name":"Arturo","country":"Norway"}]}'
oci nosql query execute --compartment-id $compartmentId --statement="INSERT INTO $docTableName (id, document) VALUES (2,$document)"
And show to how utterly schemaless our database is, let’s create an entirely different JSON document (the contents of a local document special-doc.json), and insert that into the table too.
document=$(cat special-doc.json) oci nosql query execute --compartment-id $compartmentId --statement="INSERT INTO $docTableName (id, document) VALUES (5,$document)"
Step 6: query and update the JSON documents using SQL
Querying the table is done in the same way as before the pure relational table:
oci nosql query execute --compartment-id $compartmentId --statement="SELECT * FROM $docTableName"
In the console this looks like this:
Let’s see how we can query the data in this table. The queries that can be executed against the NoSQL Database tables can refer to JSON document properties in both the WHERE and SELECT clauses.
Query the *staff* object from all documents that contain such an object:
oci nosql query execute --compartment-id $compartmentId --statement="SELECT d.document.staff FROM $docTableName d WHERE EXISTS d.document.staff"
To only select the country values from the staff members (from the records that even have a staff object in the root of their document) :
oci nosql query execute --compartment-id $compartmentId --statement="SELECT d.document.staff.country FROM $docTableName d WHERE EXISTS d.document.staff"
From the second JSON document, select all countries that alphabetically rank behind Switzerland:
oci nosql query execute --compartment-id $compartmentId --statement="SELECT d.document.values(\$value > \"Switzerland\") FROM $docTableName d WHERE id=5"
Step 7: Updating JSON documents
The JSON documents in the NoSQL Database table can be updated in a partial fashion. Here are two examples.
Partially update the second JSON document – change the country name for just the NL entry, from Netherlands to Holland:
oci nosql query execute --compartment-id $compartmentId --statement="UPDATE $docTableName d set d.document.NL=\"Holland\" where id=5 returning id, document"
Query the document for key NL to see the change applied:
oci nosql query execute --compartment-id $compartmentId --statement="SELECT d.document.NL FROM $docTableName d WHERE id=5"
In a similar way, the next command will add a staff member in the former JSON document – by adding an element to the array object:
oci nosql query execute --compartment-id $compartmentId --statement="UPDATE $docTableName d ADD d.document.staff {\"name\":\"Dan\",\"country\":\"USA\"} where id=2 returning *"
Step 8: delete the tables
Getting rid of the tables I have created in this article can easily be done in the console. Of course the OCI CLI also has support for this operation, which is even quicker:
oci nosql table delete –compartment-id $compartmentId –table-name-or-id $tableName –force oci nosql table delete –compartment-id $compartmentId –table-name-or-id $docTableName –force [/code]To verify if the tables have been dropped:
oci nosql table list --compartment-id $compartmentId
Pricing
The price you pay for the NoSQL Database Service depends on read and write operations (the number and the size of data involved) and the storage of the data. The storage is fairly cheap – at $2 for 10GB per month monthly flex. Read units (one unit corresponds to one 1 KB size read operation per second) are fairly cheap as well – 500 RU for $8. The most expensive aspect of this service is the Write Unit : a WU is throughput for up to 1 kilobyte (KB) of data per second; a write operation is any Oracle NoSQL Database Cloud Service API call that results in insertion, update, or deletion of a record. Index updates also consume write units. Write units are charged at 100 Write Units for $31 per month. Note: the number of tables is not really relevant – it is the number of RUs and WUs required to manipulate and read the data that determine the cost.
See Cloud Cost Estimator and Estimating Capacity for more details.
Summary
The NoSQL Database Service is much more than you might assume based on the name. This service does schema-less JSON documents and SQL (with ACID, consistent, relational, structured, joins). The service is fully managed, highly performant and offers data safety through a redundant architecture. I think this service has great potential: it is very easy to get started with, it has rich functionality and promises great scalability. It integrates consistently in OCI in a similar way as all other services.
I have not yet made use of NoSQL Database Service in a serious scenario, but I am looking forward to give it a try.
Resources
One topic you may want to take a look at is the Oracle NoSQL Database Cloud Simulator, a standalone tool that simulates the cloud service and lets you write and test applications locally without accessing Oracle NoSQL Database Cloud Service. See documentation on this simulator.
OCI Documentation on NoSQL Database Service
SQL Language Reference for Oracle NoSQL Database
New Features in Oracle NoSQL Database Service
