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:
The console has an easy way to inspect the data:
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:
We can use filter conditions, concatenation and functions on strings and timestamps:
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.
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
