The newbie cloud architect diary

$ kubectl create namespace rocketchat
$ kubens rocketchat
$ helm install \
    --set mongodb.mongodbUsername=rocketchat, \
    --set mongodb.mongodbPassword=changeme, \
    --set mongodb.mongodbDatabase=rocketchat, \
    --set mongodb.mongodbRootPassword=root-changeme, \
    --set mongodb.image.tag=3.6.19-debian-9-r20 \
    --name my-rocketchat stable/rocketchat

$ sensible-browser http://$(kubectl describe svc \
    my-rocketchat-rocketchat | grep Endpoints: | awk '{print $2}')

$ mongo -u rocketchat -p changeme mongo.local:27017/rocketchat \
    --eval "db.getCollectionInfos({name:'rocketchat_message'})" \
    --quiet
[
	{
		"name" : "rocketchat_message",
		"type" : "collection",
		"options" : {
			
		},
		"info" : {
			"readOnly" : false,
			"uuid" : UUID("310cba0f-5fe3-412d-9951-056cec5ad397")
		},
		"idIndex" : {
			"v" : 2,
			"key" : {
				"_id" : 1
			},
			"name" : "_id_",
			"ns" : "rocketchat.rocketchat_message"
		}
	}
]

$ mongo -u rocketchat -p changeme mongo.local:27017/rocketchat \
    --eval "var allKeys = {}; db.rocketchat_message.find().forEach(function(doc){Object.keys(doc).forEach(function(key){allKeys[key]=1})}); allKeys;" | sed 's/ : 1,/,/g;s/\s//g' | tr -d '\n' \
    --quiet
{"_id","t","rid","ts","msg","u","groupable","_updatedAt","mentions","channels","drid","attachments","dcount","dlm","starred","reactions","replies","tcount","tlm","tmid":1}

$ cat rocketchat_message.metadata.json | jq -c '.indexes[].key| keys' | tr -d '\n' | sed 's/\]\[/,/g'
["_id","_updatedAt","rid","ts","ts","u._id","editedAt","editedBy._id","rid","t","u._id","expireAt","_fts","_ftsx","file._id","mentions.username","pinned","snippeted","location","slackBotId","slackTs","unread","drid","tmid","tcount","tlm","navigation.token","pinnedBy._id","starred._id"]

$ mongo -u rocketchat -p changeme mongo.local:27017/rocketchat \
    --eval "db.getCollection('rocketchat_message').findOne()" \
    --quiet \
    | sed 's/[A-Za-z]*(\([^)]*\))/\1/g' | tr '\n' ' ' \
  > rocketchat_message_sample.json
$ cat rocketchat_message_sample.json \
    | jq -c '. | keys'
["_id","_updatedAt","groupable","msg","rid","t","ts","u"]

# read last document, schema and number of elements
$ bsondump rocketchat_message.bson | tail -1
{"_id":"u5fo4paTgR2zkqt7k","rid":"qCxWtTijs9mR3SgY5","tmid":"6PeavteZ6tyhgCsLD","msg":"cool","ts":{"$date":"2020-09-16T09:29:20.313Z"},"u":{"_id":"wHCH8we9aWS6qkWpo","username":"admin"},"mentions":[],"channels":[],"_updatedAt":{"$date":"2020-09-16T09:29:20.321Z"}} 
$ bsondump rocketchat_message.bson | tail -1 | jq 'select(objects)|=[.] | map( paths(scalars) | join(".") )'
[
  "_id",
  "rid",
  "tmid",
  "msg",
  "ts.$date",
  "u._id",
  "u.username",
  "_updatedAt.$date"
]
$ bsondump rocketchat_message.bson --quiet | tail -1 | jq 'select(objects)|=[.] | map( paths(scalars) | join(".") ) | length'
8

# read all documents (provided that there are not too many)
$ IFS=$'\n'
$ my_array=( $(bsondump rocketchat_message.bson --quiet) )
$ echo ${#my_array[@]}
5

# read documents schema and number of elements, then keep largest one
$ for line in ${my_array[@]}; do printf "$(echo $line | jq 'select(objects)|=[.] | map( paths(scalars) ) | map( map(select(numbers)="[]") | join(".") ) | length') $line\n"; done | sort -u | head -1
11 {"_id":"gTY7o3NeHXecLFyNy","t":"discussion-created","rid":"zs2TxG6SLQLAhh3Lb","ts":{"$date":"2020-09-16T09:28:41.087Z"},"msg":"my-discussion","u":{"_id":"wHCH8we9aWS6qkWpo","username":"admin"},"groupable":false,"drid":"qCxWtTijs9mR3SgY5","attachments":[],"_updatedAt":{"$date":"2020-09-16T09:29:20.337Z"},"dcount":2,"dlm":{"$date":"2020-09-16T09:29:20.313Z"}}

1. Maintain the original schema by just flattening the object hierarchy in large tables with minimal Primary/Foreign Key (PK/FK) constraints:
   
   • message
   • _id (pk)
   • rid
   • msg
   • ts
   • u__id (fk)
   • u_username 
     
   • urls_0_url
   • etc.
   This can be achieved easily for example in Python by importing the module flatten from the flatten_json library. Still we have a problem when the hierarchy is not flat. In the example, there is a nested table "urls" containing a variable number of records. As we don't want any fix array index size to be part of the target schema, we'd better have a dedicated table.
   
   
2. Factorize the schema by detaching any nested table including maximal integrity constraints:
   
   • message
   • _id (pk)
   • rid
     
   • msg
   • ts
   • etc.
   • message_user
   • message_id (pk, fk)
   • user_id (pk, fk)
   • user
   • _id (pk)
   • username
   • message_url
   • message_id (pk, fk)
   • message_url (pk, fk)
   • url
   • url (pk)
   • etc.
   This can be achieved for example in Python by importing the modules json_normalize and json_build_table from the pandas.io.json library. The approach solves the issue mentioned before but potentially introduces 9 tables alone for the message document object, which may result in an unmanageable schema if you are not careful.

## flatten.py

# read input file
import json
with open(r'rocketchat_message_sample.json') as f:
    dt = json.load(f)

# faltten dictionaty
from flatten_json import flatten
dic_flattened = flatten(dt)
#dic_flattened = (flatten(d) for d in dt)

# use pandas
import pandas as pd
df = pd.DataFrame(dic_flattened, index=[0])
print('-- DF COLUMNS:\n',df.columns)

# create file db
from sqlalchemy import create_engine
engine = create_engine('sqlite:///db.sqlite.flat', echo=False)

# create schema and write records to db
df.to_sql('rocketchat_message', 
        con=engine, 
        if_exists='replace',
        index=False)
print('-- DB SELECT:\n', engine.execute('SELECT * FROM rocketchat_message').fetchall())

-- DF COLUMNS:
 Index(['_id', 't', 'rid', 'ts', 'msg', 'u__id', 'u_username', 'groupable',
       '_updatedAt'],
      dtype='object')
-- DB SELECT:
 [('MshE4AKDejGaiwJkv', 'uj', 'GENERAL', '2020-08-30T15:21:37.974Z', 'tncad', '3jh6AN3eLZKRCD6E9', 'tncad', 0, '2020-08-30T15:21:37.974Z')]

$ sudo apt install sqlite3
$ sqlite3 db.sqlite.flat
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .tables
rocketchat_message
sqlite> .schema rocketchat_message
CREATE TABLE rocketchat_message (
	_id TEXT, 
	t TEXT, 
	rid TEXT, 
	ts TEXT, 
	msg TEXT, 
	u__id TEXT, 
	u_username TEXT, 
	groupable BOOLEAN, 
	"_updatedAt" TEXT, 
	CHECK (groupable IN (0, 1))
);
sqlite> .quit