Simple queries against Oracle Cloud NoSQL Database

Lucas Jellema

I have been struggling in my first simple steps with querying data in a NoSQL Database table on Oracle Cloud. Creating the table is easy enough – either in the console or through Terraform or the CLI. However, the data that my Node application creates in the table seemed very difficult to query. Either in the console or in the CLI, I could not easily get a hold on my data – beyond a simple select * from table that returns all rows.

I am a newbie with Oracle NoSQL database – but not a stranger to SQL. So how hard can it be? This article demonstrates that in the end it is not hard at all – and of course now I do not understand why it took me so much time to get here. I will share some simple query examples that helped me to get going a little beyond select *.

I would like to take other steps, such as installing the NoSQL SQLShell tool in OCI Cloud Shell and running SQL Developer as a query front end against a NoSQL Table on OCI. For both scenarios it seems that the documentation and community resources do not yet provide the instructions.

Here is my table TWEETS_TABLE:

image

The console has an easy way to inspect the data:

image

The query statement in this page can be modified and executed – to filter the rows (but not to aggregate or sort) and to query expressions instead of only straight column values. Note however that the UI can only handle query results which have the same names as the original columns:

image

We can use filter conditions, concatenation and functions on strings and timestamps:

image

We cannot execute DML statements in the console. We can remove individual records, but not a larger set of records at once. However, we can define the Time To Live property for the table – with a granularity of days – to purge records after their TTL has expired.

Note: To sort the results from a SELECT statement using a field that is not the table’s primary key, you must first create an index for the column of choice.

Querying NoSQL Database Cloud Tables from the CLI

We can interact with the NoSQL Database Cloud Service through the OCI CLI. And we can do so most easily through the OCI Cloud Shell interface right in the browser.

image

In the CLI, we can create a table and indexes on that table, query records and also create records. Deleting rows is done using the primary keys of the rows to be deleted – nothing so easy as ‘delete from tweets_table where year(tweet_timestamp) < 2021’  I am afraid.

Resources

Gist with the OCI CLI code discussed in this article: https://gist.github.com/lucasjellema/fcd7e382c1bdbfe37b7c0abbfddc8ebc

OCI CLI – query against NoSQL Table – https://docs.oracle.com/en-us/iaas/tools/oci-cli/2.9.4/oci_cli_docs/cmdref/nosql/query/execute.html

OCI NoSQL Docs – query – https://docs.oracle.com/en/database/other-databases/nosql-database/20.3/sqlfornosql/simple-select-where-queries.html; Timestamps examples: https://docs.oracle.com/en/database/other-databases/nosql-database/20.3/sqlfornosql/working-timestamps.html

OCI NoSQL Database Cloud Service Docs – https://docs.oracle.com/en/cloud/paas/nosql-cloud/index.html

Leave a Reply

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

Next Post

How to open IDCS Console from OCI– Finding the entry to Identity Cloud Service

I have just spent way too much time on finding the way to the Oracle IDCS console from within my OCI (Oracle Cloud Infrastructure) console. I was reading a bunch of interesting articles on using IDCS to protect access to OCI Functions and I was trying to follow along. However, […]
%d bloggers like this: