Getting started with NoSQL Database Service on Oracle Cloud Infrastructure

0

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

 

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 }"

image

Check if the table has been created:

oci nosql table list --compartment-id $compartmentId

image

In the console, this same information is presented like this:

image

The details for the table can be inspected:

image

 

    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\")"
    

    image

    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"
    

    image

    We can execute queries in the console to inspect the data in the table(s):

    image

     

    Let’s create a few more records:

    image

    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:

    image

     

    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 }"
    

    image

    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.

    image

    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"
    

    SNAGHTML28e13189

    In the console this looks like this:

    image

     

    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"
    

    SNAGHTML28e349da

    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"
    

    image

    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"
    

    image

     

     

    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"
    

    SNAGHTML28e625e9

    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"
    

    image

    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 *"
    

    image

     

    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 –forceoci 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 
    

    SNAGHTML28ca3fc2

       

      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.

      image

      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

       

       

      About Author

      Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

      Leave a Reply

      This site uses Akismet to reduce spam. Learn how your comment data is processed.