Skip to content

Instantly share code, notes, and snippets.

@lucasjellema
Last active December 5, 2023 09:22
Show Gist options
  • Save lucasjellema/fcd7e382c1bdbfe37b7c0abbfddc8ebc to your computer and use it in GitHub Desktop.
Save lucasjellema/fcd7e382c1bdbfe37b7c0abbfddc8ebc to your computer and use it in GitHub Desktop.
OCI NoSQL Database SQL

To create the table, we need a DDL statement - it still feels weird to say stuff like that regarding a NoSQL database

CREATE TABLE TWEETS_TABLE(id LONG, text STRING, author STRING, tweet_timestamp TIMESTAMP(0), language STRING, hashtags STRING, PRIMARY KEY(SHARD(id))) USING TTL 1 DAYS

This can be done in OCI CLI. It is convenient to first set the compartment OCID

export COMPARTMENT_OCID=ocid................................

Then:

oci nosql table create -c $COMPARTMENT_OCID --name TWEETS_TABLE --ddl-statement "CREATE TABLE TWEETS_TABLE(id LONG, text STRING, author STRING, tweet_timestamp TIMESTAMP(0), language STRING, hashtags STRING, PRIMARY KEY(SHARD(id))) USING TTL 1 DAYS"  --table-limits "{  \"maxReadUnits\": 50,  \"maxStorageInGBs\": 1,\"maxWriteUnits\": 1}"

To create an index on the table - in this example on one column: tweet_timestamp:

oci nosql index create --index-name tweet_time_idx1 --table-name-or-id TWEETS_TABLE --compartment-id $COMPARTMENT_OCID --keys   "[  {  \"columnName\": \"tweet_timestamp\"}]"

Querying data with various where clauses:

oci nosql query execute  --compartment-id $COMPARTMENT_OCID --statement "SELECT *  FROM TWEETS_TABLE WHERE contains(text,\"virus\")"  --limit 3

oci nosql query execute  --compartment-id $COMPARTMENT_OCID --statement "SELECT *  FROM TWEETS_TABLE WHERE contains(upper(text),upper(\"virus\")) and contains(upper(hashtags),upper(\"#omt\"))"  --limit 20


oci nosql query execute  --compartment-id $COMPARTMENT_OCID --statement "SELECT *  FROM TWEETS_TABLE WHERE tweet_timestamp > CAST(\"2021-11-25T14:18:05\" AS TIMESTAMP)"  --limit 20

I was not able to perform queries with an Order By cause, despite creating the index on tweet_timestamp

oci nosql query execute  --compartment-id $COMPARTMENT_OCID --statement "SELECT *  FROM TWEETS_TABLE order by tweet_timestamp desc"  --limit 20

The error:

"code": "InvalidParameter",
    "message": "QUERY: Illegal Argument: The driver or SDK being used does not support queries with order by expressions that do not include a \"where\" clause specifying a complete shard key",
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment