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:

Today I Learned - more about Git config

Whilst trying to create a container image from a project on GitHub, I hit an issue with the cloning process of the GH repository ... Specifi...