nonosql 1.0.0

No NoSQL please. Python library that converts NoSQL nonsense into relational database compatible formats

nonosql

No NoSQL please. Python library that converts NoSQL nonsense into relational database compatible formats.

Installation

Python 3.8 or newer is required. Use a virtual environment if you do not install into a dedicated environment already.

Install from GitHub (no clone):

pip install git+https://github.com/dataleio/nonosql.git

Install from a local clone:

git clone https://github.com/dataleio/nonosql.git
cd nonosql
pip install .

Editable install (for development; changes in the working tree are used immediately):

pip install -e .

Project metadata and the build are defined in pyproject.toml (PEP 517); setup.py is only a thin compatibility shim for older tooling.

Introduction

Detect Schema of downloaded JSON documents

No matter whether your data comes from MongoDB, CosmosDB, DocumentDB you will have a tons of unstructured JSON files with numerous unused attributes due to years of continuous development. And obviously, no one knows what those represents. :)

We have the following JSON document named example.json:

{
  "id": 123,
  "contactDetails": {
    "phoneNumbers": ["[phone]", "[phone]", "[phone]"],
    "emailAddresses": ["[e-mail]"]
  },
  "createdAt": "2022-02-01T12:33:44.1212"
}
{
  "id": 2,
  "contactDetails": {
    "emailAddresses": ["[e-mail]"]
  },
  "createdAt": "2022-02-01T15:34:44.1212",
  "updatedAt": "2022-02-01T15:34:44.1212",
  "services": [
    {
      "name": "integration",
      "lastUsed": "2021-12-30",
      "subservices": ["a", "b", "c"]
    }
  ]
}

And you want to understand the schema of these records. Please note, for schema detection you can only spectate limited number of rows per datasets.

You can run detection such as:

$ nonosql detect schema example.json

Which is going to give you the following:

[
    {
        "name": "contactDetails",
        "jsonpath": "$.contactDetails",
        "type": "RECORD",
        "sampled_ratio": 1.0,
        "fields": [
            {
                "name": "emailAddresses",
                "jsonpath": "$.contactDetails.emailAddresses",
                "mode": "REPEATED",
                "sampled_ratio": 1.0,
                "type": "STRING"
            },
            {
                "name": "phoneNumbers",
                "jsonpath": "$.contactDetails.phoneNumbers",
                "mode": "REPEATED",
                "sampled_ratio": 0.5,
                "type": "STRING"
            }
        ]
    },
    {
        "name": "createdAt",
        "jsonpath": "$.createdAt",
        "sampled_ratio": 1.0,
        "type": "DATETIME"
    },
    {
        "name": "id",
        "jsonpath": "$.id",
        "sampled_ratio": 1.0,
        "type": "BIGINT"
    },
    {
        "name": "services",
        "jsonpath": "$.services",
        "mode": "REPEATED",
        "type": "RECORD",
        "sampled_ratio": 0.5,
        "fields": [
            {
                "name": "name",
                "jsonpath": "$.services.*.name",
                "sampled_ratio": 0.5,
                "type": "STRING"
            },
            {
                "name": "subservices",
                "jsonpath": "$.services.*.subservices",
                "mode": "REPEATED",
                "sampled_ratio": 0.5,
                "type": "STRING"
            },
            {
                "name": "lastUsed",
                "jsonpath": "$.services.*.lastUsed",
                "sampled_ratio": 0.5,
                "type": "DATE"
            }
        ]
    },
    {
        "name": "updatedAt",
        "jsonpath": "$.updatedAt",
        "sampled_ratio": 0.5,
        "type": "DATETIME"
    }
]

This gives you basic information such as:

• sampled_ratio: What percentage of records have this data stored.
• type: What would be the correct type of this row.

You can edit the above schema and drop few columns you consider rubbish based on the sampled ratio.

Transforming JSON documents

When you have a saved schema file (e.g.: schema.json) it serves as a configuration file for transformations.

For example, I removed the services block entirely.

[
    {
        "name": "contactDetails",
        "jsonpath": "$.contactDetails",
        "type": "RECORD",
        "sampled_ratio": 1.0,
        "fields": [
            {
                "name": "emailAddresses",
                "jsonpath": "$.contactDetails.emailAddresses",
                "mode": "REPEATED",
                "sampled_ratio": 1.0,
                "type": "STRING"
            },
            {
                "name": "phoneNumbers",
                "jsonpath": "$.contactDetails.phoneNumbers",
                "mode": "REPEATED",
                "sampled_ratio": 0.5,
                "type": "STRING"
            }
        ]
    },
    {
        "name": "createdAt",
        "jsonpath": "$.createdAt",
        "sampled_ratio": 1.0,
        "type": "DATETIME"
    },
    {
        "name": "id",
        "jsonpath": "$.id",
        "sampled_ratio": 1.0,
        "type": "BIGINT"
    },
    {
        "name": "updatedAt",
        "jsonpath": "$.updatedAt",
        "sampled_ratio": 0.5,
        "type": "DATETIME"
    }
]

We run the following script to generate the transformed version of the JSON document:

$ nonosql transform schema.json example.json

Which is going to return the following transformed JSON document, keeping only the values we want. The transformation also adds empty columns for missing values which is often required by various data warehouses for importing the data.

{
    "contactDetails": {
        "emailAddresses": [
            "[e-mail]"
        ],
        "phoneNumbers": [
            "[phone]",
            "[phone]",
            "[phone]"
        ]
    },
    "createdAt": "2022-02-01T12:33:44.1212",
    "id": 1,
    "updatedAt": null
}
{
    "contactDetails": {
        "emailAddresses": [
            "[e-mail]"
        ],
        "phoneNumbers": null
    },
    "createdAt": "2022-02-01T15:34:44.1212",
    "id": 2,
    "updatedAt": "2022-02-01T15:34:44.1212"
}

Running transformations from Python

You can do a transformation based on a nonosql schema from python in the following way:

import json
import nonosql

t = nonosql.Transform(schema=json.load(open('nonosql_schema.json')))  # initialise nonosql with a schema

with open('input.jsonl') as source, open('output.jsonl', 'w') as target:  # open source and target file descriptors
    transformed_items = t.transform_file(source)  # iterate over the source file line by line
    for transformed_item in transformed_items:  # iterate over the transformed data as it may have been unnested
        target.write(json.dumps(transformed_item) + "\n")  # write the transformed data into a file

you can also do this with python objects

import json
import nonosql

t = nonosql.Transform(schema=json.load(open('nonosql_schema.json')))  # initialise nonosql with a schema
transformed_items = t.transform_object({
    "id": 32,
    "hobbies": ["PROGRAMMING", "FOOD"]
})

Schema for Relational Databases

As you can see above, these are useful to understand the data we have, but we still create nested JSON blobs which is unusable without data preprocessing.

No NoSQL provides few built in transformations you can use to generate more hands on schema configurations.

Unpack Values from nested objects

You can move nested attributes from depth level into the top level. You can also specify a nested_as_json value to convert any nested complex types (dicts or repeatable fields) into a string JSON column. When we work with relational databases it's usually ok to keep JSON fields in VARCHAR columns if those are not accesses often, and if we don't want to introduce complex nested ARRAYs and STRUCTs.

$ nonosql detect unpack --depth 3 --nested_as_json 1 example.json

The result will move the contactDetails.emailAddresses field to the top node.

[
    {
        "name": "createdAt",
        "jsonpath": "$.createdAt",
        "sampled_ratio": 1.0,
        "type": "DATETIME"
    },
    {
        "name": "id",
        "jsonpath": "$.id",
        "sampled_ratio": 1.0,
        "type": "BIGINT"
    },
    {
        "name": "updatedAt",
        "jsonpath": "$.updatedAt",
        "sampled_ratio": 0.5,
        "type": "DATETIME"
    },
    {
        "name": "contactDetails_emailAddresses",
        "jsonpath": "$.contactDetails.emailAddresses",
        "mode": "REPEATED",
        "sampled_ratio": 1.0,
        "type": "STRING"
    },
    {
        "name": "contactDetails_phoneNumbers",
        "jsonpath": "$.contactDetails.phoneNumbers",
        "mode": "REPEATED",
        "sampled_ratio": 0.5,
        "type": "STRING"
    },
    {
        "name": "services",
        "jsonpath": "$.services",
        "mode": "REPEATED",
        "type": "RECORD",
        "sampled_ratio": 0.5,
        "fields": [
            {
                "name": "name",
                "jsonpath": "$.services.*.name",
                "sampled_ratio": 0.5,
                "type": "STRING"
            },
            {
                "name": "subservices",
                "jsonpath": "$.services.*.subservices",
                "type": "JSON",
                "sampled_ratio": 0.5
            },
            {
                "name": "lastUsed",
                "jsonpath": "$.services.*.lastUsed",
                "sampled_ratio": 0.5,
                "type": "DATE"
            }
        ]
    }
]

In case you don't want to keep complex fields in your dataset, just set the nested_as_json to 0.

$ nonosql detect unpack --depth 2 --nested_as_json 0 example.json

which then will convert all complex types to stringified JSON blobs.

[
    {
        "name": "createdAt",
        "jsonpath": "$.createdAt",
        "sampled_ratio": 1.0,
        "type": "DATETIME"
    },
    {
        "name": "id",
        "jsonpath": "$.id",
        "sampled_ratio": 1.0,
        "type": "BIGINT"
    },
    {
        "name": "updatedAt",
        "jsonpath": "$.updatedAt",
        "sampled_ratio": 0.5,
        "type": "DATETIME"
    },
    {
        "name": "contactDetails_emailAddresses",
        "jsonpath": "$.contactDetails.emailAddresses",
        "type": "JSON",
        "sampled_ratio": 1.0
    },
    {
        "name": "contactDetails_phoneNumbers",
        "jsonpath": "$.contactDetails.phoneNumbers",
        "type": "JSON",
        "sampled_ratio": 0.5
    },
    {
        "name": "services",
        "jsonpath": "$.services",
        "type": "JSON",
        "sampled_ratio": 0.5
    }
]

and will convert your data to look like the following:

{
    "createdAt": "2022-02-01T12:33:44.1212",
    "id": 1,
    "updatedAt": null,
    "contactDetails_emailAddresses": "[\"[e-mail]\"]",
    "contactDetails_phoneNumbers": "[\"[phone]\", \"[phone]\", \"[phone]\"]",
    "services": "null"
}
{
    "createdAt": "2022-02-01T15:34:44.1212",
    "id": 2,
    "updatedAt": "2022-02-01T15:34:44.1212",
    "contactDetails_emailAddresses": "[\"[e-mail]\"]",
    "contactDetails_phoneNumbers": "null",
    "services": "[{\"name\": \"integration\", \"lastUsed\": \"2021-12-30\", \"subservices\": [\"a\", \"b\", \"c\"]}]"
}

Unprocessed format

You can specify a schema which puts the entire JSON blob into one column. Though this is great and a simple transformation, this puts a lots of pressure on the data warehouse performance (many JSON functions will be called) and not cleans or standardises the NoSQL mess.

[
    {
        "name": "id",
        "jsonpath": "$.id",
        "sampled_ratio": 1.0,
        "type": "BIGINT"
    },
    {
        "name": "json_blob",
        "jsonpath": "$",
        "sampled_ratio": 1.0,
        "type": "JSON"
    }
]

Multiple datasets for relational databases

We can also generate relational datasets by simply adding the following format to the schema file:

...
{
    "name": "services",
    "jsonpath": "$.services",
    "mode": "REPEATED",
    "type": "RECORD",
    "sampled_ratio": 0.5,
    "dataset": "services",
    "dataset_connection_key": {
        "name": "_id",
        "jsonpath": "$.id",
        "sampled_ratio": 1.0,
        "type": "BIGINT"
    },
    "fields": [
        {
            "name": "name",
            "jsonpath": "$.services.*.name",
            "sampled_ratio": 0.5,
            "type": "STRING"
        },
        {
            "name": "lastUsed",
            "jsonpath": "$.services.*.lastUsed",
            "sampled_ratio": 0.5,
            "type": "DATE"
        },
        {
            "name": "subservices",
            "jsonpath": "$.services.*.subservices",
            "sampled_ratio": 0.5,
            "type": "STRING",
            "mode": "REPEATED",
            "dataset": "subservices",
            "dataset_connection_key": {
                "name": "_name",
                "jsonpath": "$.services.*.name",
                "sampled_ratio": 1.0,
                "type": "BIGINT"
            }
        }
    ]
}
...

As you can see, we specify two new blocks:

• dataset: This is a unique name
• dataset_connection_key: This is a unique key we want to move from upstream

If you specify the dataset attributes, the transformation scripts will remove these from the datasets by default.

nonosql transform schema.json example.json

You will see it will look like this (using unpacked version for 3 depth):

{
    "createdAt": "2022-02-01T12:33:44.1212",
    "id": 1,
    "updatedAt": null,
    "contactDetails_emailAddresses": [
        "[e-mail]"
    ],
    "contactDetails_phoneNumbers": [
        "[phone]",
        "[phone]",
        "[phone]"
    ]
}
{
    "createdAt": "2022-02-01T15:34:44.1212",
    "id": 2,
    "updatedAt": "2022-02-01T15:34:44.1212",
    "contactDetails_emailAddresses": [
        "[e-mail]"
    ],
    "contactDetails_phoneNumbers": null
}

Now, if I want to pull the "services" dataset and attach the ID of the rows, I simply call:

$ nonosql transform schema.json example.json --dataset services

which returns:

{
    "name": "integration",
    "lastUsed": "2021-12-30",
    "_id": 2
}

It works well also with static lists and also with multiple nested rows.

$ nonosql transform schema.json example.json --dataset subservices

Which will return with the values and the connection:

{
    "value": "a",
    "_name": "integration"
}
{
    "value": "b",
    "_name": "integration"
}
{
    "value": "c",
    "_name": "integration"
}

However, this functionality works brilliantly - generating schema files with datasets automatically is not possible.

License

Copyright © 2019-2023 Datale Pte. Ltd.

Distributed under the MIT License.