Thursday, 5 November 2020

Cloudy databases - Back with Cloudant on IBM Cloud

 A few years back, I was using Cloudant to store JSON data for a POC upon which my team and I were working, and wrote a few posts: -

IBM Integration Bus and Cloudant - Baby steps ...

Cloudant - Continuing to tinker

Doofus Alert - Using Cloudant queries via cURL

Fast forward to now ... I'm back in the game with Cloudant, and had to remind myself of some of the core concepts ...

So I created a Cloudant instance in my IBM Cloud account: -



Once my instance was created, I navigated to the Manage tab: -


( note that I've deliberately obscured the CRN and Endpoint details, for security )

However, I grabbed the External Endpoint (preferred) because that URL will be of use in a tick ...

Also note that Authentication methods defaults to IBM Cloud IAM which is important, in that it allows one to authenticate to the database using an API key / Access Token ...

Using the External Endpoint as an example, I've created a randomised sample here: -

https://d66573ef-c567-4efc-6e59-c8ab7eb33de6-bluemix.cloudantnosqldb.appdomain.cloud/

because ... SECURITY 😂

With that, and an Access Token, which I generated via my IBM Cloud API key, I'm able to access my Cloudant instance from the command-line: -

Generate Access Token

- This is a script that I use, via an alias, to parse the API key and generate an $ACCESS_TOKEN variable

source ~/genAccessToken.sh

Set the $URL variable

- Note, when copying/pasting the URL from the Manage -> Overview page, note that a trailing backslash ( / ) character will be copied. This must NOT be appended to the URL, or things go BOOM! ( I'll post about this shortly )

export URL="https://d66573ef-c567-4efc-6e59-c8ab7eb33de6-bluemix.cloudantnosqldb.appdomain.cloud"

Query the Cloudant instance

- Note that we don't need to explicitly authenticate to do this i.e. we don't present $ACCESS_TOKEN

curl -s -k -X GET $URL | json_pp

{
   "features" : [
      "geo",
      "access-ready",
      "iam",
      "partitioned",
      "pluggable-storage-engines",
      "scheduler"
   ],
   "version" : "2.1.1",
   "vendor" : {
      "version" : "8162",
      "name" : "IBM Cloudant",
      "variant" : "paas"
   },
   "features_flags" : [
      "partitioned"
   ],
   "couchdb" : "Welcome"
}

Create a database

- called movies

curl -s -k -X PUT $URL/movies | json_pp

{
   "error" : "unauthorized",
   "reason" : "one of _admin, server_admin is required for this request"
}

Goops, forgot to add the $ACCESS_TOKEN environment variable to the HTTP header

curl -s -k -X PUT -H 'Authorization: Bearer '"$ACCESS_TOKEN" $URL/movies | json_pp

{
   "ok" : true
}

Query available databases

curl -s -k -X GET -H 'Authorization: Bearer '"$ACCESS_TOKEN" $URL/_all_dbs | json_pp

[
   "movies"
]

Get the details on the movies database

curl -s -k -X GET -H 'Authorization: Bearer '"$ACCESS_TOKEN" $URL/movies | json_pp

{
   "instance_start_time" : "0",
   "doc_count" : 0,
   "cluster" : {
      "q" : 16,
      "r" : 2,
      "w" : 2,
      "n" : 3
   },
   "compact_running" : false,
   "props" : {},
   "db_name" : "movies",
   "sizes" : {
      "external" : 0,
      "file" : 133940,
      "active" : 0
   },
   "purge_seq" : 0,
   "update_seq" : "0-g1AAAAP3eJzLYWBgEMhgTmFQT0lKzi9KdUhJMjTQS8rVTU7WTS3VTUnVNTTWS87JL01JzCvRy0styQFqYMpjAZIMH4DUfyDISmQAmaAGN8GISAMeQAx4j2GAKZEGXIAYcB_DAAsiDTgAMeA81AAyAmEDxIT9ZAfCAogB68kOhAkQA-aTHQgNEAP6yQmEpAIgmVRPXipISgBpzsemmbDnkwJAmuOxaSbs8SQHkGZ_hGbSPG0A0m1PpqcVQJr1yfS0AEizPHmeTmRI4ofozAIACC1D2Q",
   "disk_format_version" : 8,
   "doc_del_count" : 0
}

Create a JSON payload to populate the movies database

vi movies.json

{
  "docs":[
    {
      "_id":"1",
      "name":"War Games",
      "format":"DVD"
    },
    {
      "_id":"2",
      "name":"Top Gun",
      "format":"BluRay"
    },
    {
      "_id":"3",
      "name":"Rogue One",
      "format":"MP4"
    },
    {
      "_id":"4",
      "name":"Airplane",
      "format":"MP4"
    },
    {
      "_id":"5",
      "name":"Avengers",
      "format":"BluRay"
    },
    {
      "_id":"6",
      "name":"Mission Impossible",
      "format":"MP4"
    }
  ]
}

Populate the movies database

curl -s -k -X POST -H 'Authorization: Bearer '"$ACCESS_TOKEN" -H "Content-type: application/json" -d @movies.json $URL/movies/_bulk_docs | json_pp

[
   {
      "rev" : "1-48aa26577c7e2a4380df147095a1f592",
      "ok" : true,
      "id" : "1"
   },
   {
      "rev" : "1-cd93fc31e45e9379eb2137601ffaef38",
      "ok" : true,
      "id" : "2"
   },
   {
      "id" : "3",
      "rev" : "1-7bd2eb120d332170527a584897702e60",
      "ok" : true
   },
   {
      "ok" : true,
      "rev" : "1-5e930ff076df50b8cb9b09d354dc184b",
      "id" : "4"
   },
   {
      "rev" : "1-007906c513fc0c6d8c13c99e3ca4265f",
      "ok" : true,
      "id" : "5"
   },
   {
      "ok" : true,
      "rev" : "1-e3af283ebc1e44960cbc90690dde197c",
      "id" : "6"
   }
]

Query the movies database

curl -s -k -X GET -H 'Authorization: Bearer '"$ACCESS_TOKEN" $URL/movies/_all_docs?include_docs=true | json_pp

{
   "rows" : [
      {
         "key" : "1",
         "value" : {
            "rev" : "1-48aa26577c7e2a4380df147095a1f592"
         },
         "id" : "1",
         "doc" : {
            "format" : "DVD",
            "_rev" : "1-48aa26577c7e2a4380df147095a1f592",
            "_id" : "1",
            "name" : "War Games"
         }
      },
      {
         "doc" : {
            "name" : "Top Gun",
            "format" : "BluRay",
            "_id" : "2",
            "_rev" : "1-cd93fc31e45e9379eb2137601ffaef38"
         },
         "id" : "2",
         "value" : {
            "rev" : "1-cd93fc31e45e9379eb2137601ffaef38"
         },
         "key" : "2"
      },
      {
         "doc" : {
            "_rev" : "1-7bd2eb120d332170527a584897702e60",
            "_id" : "3",
            "format" : "MP4",
            "name" : "Rogue One"
         },
         "key" : "3",
         "value" : {
            "rev" : "1-7bd2eb120d332170527a584897702e60"
         },
         "id" : "3"
      },
      {
         "id" : "4",
         "value" : {
            "rev" : "1-5e930ff076df50b8cb9b09d354dc184b"
         },
         "key" : "4",
         "doc" : {
            "name" : "Airplane",
            "_id" : "4",
            "_rev" : "1-5e930ff076df50b8cb9b09d354dc184b",
            "format" : "MP4"
         }
      },
      {
         "key" : "5",
         "value" : {
            "rev" : "1-007906c513fc0c6d8c13c99e3ca4265f"
         },
         "id" : "5",
         "doc" : {
            "_id" : "5",
            "_rev" : "1-007906c513fc0c6d8c13c99e3ca4265f",
            "format" : "BluRay",
            "name" : "Avengers"
         }
      },
      {
         "doc" : {
            "_rev" : "1-e3af283ebc1e44960cbc90690dde197c",
            "_id" : "6",
            "format" : "MP4",
            "name" : "Mission Impossible"
         },
         "key" : "6",
         "value" : {
            "rev" : "1-e3af283ebc1e44960cbc90690dde197c"
         },
         "id" : "6"
      }
   ],
   "offset" : 0,
   "total_rows" : 6
}

Query individual records

curl -s -k -X GET -H 'Authorization: Bearer '"$ACCESS_TOKEN" $URL/movies/1 | json_pp

{
   "name" : "War Games",
   "_rev" : "1-48aa26577c7e2a4380df147095a1f592",
   "_id" : "1",
   "format" : "DVD"
}

curl -s -k -X GET -H 'Authorization: Bearer '"$ACCESS_TOKEN" $URL/movies/2 | json_pp

{
   "name" : "Top Gun",
   "format" : "BluRay",
   "_id" : "2",
   "_rev" : "1-cd93fc31e45e9379eb2137601ffaef38"
}

Query the database using search criteria

curl -s -k -X POST -H 'Authorization: Bearer '"$ACCESS_TOKEN" -H "Content-type: application/json" $URL/movies/_find -d '{
   "selector": {
     "name":"Airplane"
   }
 }' | json_pp

{
   "docs" : [
      {
         "name" : "Airplane",
         "format" : "MP4",
         "_id" : "4",
         "_rev" : "1-5e930ff076df50b8cb9b09d354dc184b"
      }
   ],
   "warning" : "No matching index found, create an index to optimize query time.",
   "bookmark" : "g1AAAAAyeJzLYWBgYMpgSmHgKy5JLCrJTq2MT8lPzkzJBYozmoAkOGASEKEsAEr3DR8"
}

Create a text index on the name field

- This will allow us to mitigate the warnings shown previously

curl -s -k -X POST -H 'Authorization: Bearer '"$ACCESS_TOKEN" -H "Content-type: application/json" $URL/movies/_index -d '{
   "index": {
      "fields": [
         "name"
      ]
   },
   "name": "name-json-index",
   "type": "json"
}
' | json_pp

{
   "id" : "_design/700ae9eb4c3ddffe7f46e8b3140ee324aed53c0c",
   "result" : "created",
   "name" : "name-json-index"
}

Create a text index on the format field

curl -s -k -X POST -H 'Authorization: Bearer '"$ACCESS_TOKEN" -H "Content-type: application/json" $URL/movies/_index -d '{
   "index": {
      "fields": [
         "format"
      ]
   },
   "name": "format-json-index",
   "type": "json"
}
' | json_pp

{
   "name" : "format-json-index",
   "result" : "created",
   "id" : "_design/22dce09672c8b999fabd11c2cb7caffc9ee3ee37"
}

Query the database using the name field

curl -s -k -X POST -H 'Authorization: Bearer '"$ACCESS_TOKEN" -H "Content-type: application/json" $URL/movies/_find -d '{
   "selector": {
     "name":"Airplane"
   }
 }' | json_pp

{
   "bookmark" : "g1AAAAA_eJzLYWBgYMpgSmHgKy5JLCrJTq2MT8lPzkzJBYozmoAkOGASOSAhkDiHY2ZRQU5iXmpWFgAI1RD1",
   "docs" : [
      {
         "name" : "Airplane",
         "_id" : "4",
         "_rev" : "1-5e930ff076df50b8cb9b09d354dc184b",
         "format" : "MP4"
      }
   ]
}

Query the database using the format field

curl -s -k -X POST -H 'Authorization: Bearer '"$ACCESS_TOKEN" -H "Content-type: application/json" $URL/movies/_find -d '{
   "selector": {
     "format":"MP4"
   }
 }' | json_pp

{
   "bookmark" : "g1AAAAA6eJzLYWBgYMpgSmHgKy5JLCrJTq2MT8lPzkzJBYozmoEkOGASOSAhkDizb4BJVhYAt0cOlw",
   "docs" : [
      {
         "_rev" : "1-7bd2eb120d332170527a584897702e60",
         "format" : "MP4",
         "name" : "Rogue One",
         "_id" : "3"
      },
      {
         "_rev" : "1-5e930ff076df50b8cb9b09d354dc184b",
         "format" : "MP4",
         "name" : "Airplane",
         "_id" : "4"
      },
      {
         "name" : "Mission Impossible",
         "format" : "MP4",
         "_rev" : "1-e3af283ebc1e44960cbc90690dde197c",
         "_id" : "6"
      }
   ]
}

Just to close the loop, this is what I now see in the Cloudant Dashboard: -


and: -


It's been great to get back into Cloudant ... so much more to learn 🤣🤣🤣🤣🤣

No comments:

Yay, VMware Fusion and macOS Big Sur - no longer "NAT good friends" - forgive the double negative and the terrible pun ...

After macOS 11 Big Sur was released in 2020, VMware updated their Fusion product to v12 and, sadly, managed to break Network Address Trans...