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