Metadata-Version: 2.4
Name: nonosql
Version: 1.0.0
Summary: No NoSQL please. Python library that converts NoSQL nonsense into relational database compatible formats
Author-email: "Datale Pte. Ltd." <hello@datale.io>
License: MIT
Project-URL: Homepage, https://github.com/dataleio/nonosql
Classifier: Development Status :: 5 - Production/Stable
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Topic :: Database
Requires-Python: >=3.8
Description-Content-Type: text/markdown
License-File: LICENSE
Dynamic: license-file

# 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](https://docs.python.org/3/library/venv.html) if you do not install into a dedicated environment already.

**Install from GitHub** (no clone):

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

**Install from a local clone**:

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

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

```bash
pip install -e .
```

Project metadata and the build are defined in `pyproject.toml` ([PEP 517](https://peps.python.org/pep-0517/)); `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`:

```json
{
  "id": 123,
  "contactDetails": {
    "phoneNumbers": ["+1 1234567", "+44 1234567", "+65 1234567"],
    "emailAddresses": ["example@example.com"]
  },
  "createdAt": "2022-02-01T12:33:44.1212"
}
{
  "id": 2,
  "contactDetails": {
    "emailAddresses": ["example@example.com"]
  },
  "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:

```bash
$ nonosql detect schema example.json
```

Which is going to give you the following:

```json
[
    {
        "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.

```json
[
    {
        "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:

```bash
$ 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.

```json
{
    "contactDetails": {
        "emailAddresses": [
            "example@example.com"
        ],
        "phoneNumbers": [
            "+1 1234567",
            "+44 1234567",
            "+65 1234567"
        ]
    },
    "createdAt": "2022-02-01T12:33:44.1212",
    "id": 1,
    "updatedAt": null
}
{
    "contactDetails": {
        "emailAddresses": [
            "example@example.com"
        ],
        "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:

```python
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

```python
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`.

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

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

```json
[
    {
        "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.

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

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

```json
[
    {
        "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:

```json
{
    "createdAt": "2022-02-01T12:33:44.1212",
    "id": 1,
    "updatedAt": null,
    "contactDetails_emailAddresses": "[\"example@example.com\"]",
    "contactDetails_phoneNumbers": "[\"+1 1234567\", \"+44 1234567\", \"+65 1234567\"]",
    "services": "null"
}
{
    "createdAt": "2022-02-01T15:34:44.1212",
    "id": 2,
    "updatedAt": "2022-02-01T15:34:44.1212",
    "contactDetails_emailAddresses": "[\"example@example.com\"]",
    "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.

```json
[
    {
        "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:

```json
...
{
    "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.

```bash
nonosql transform schema.json example.json
```

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

```bash
{
    "createdAt": "2022-02-01T12:33:44.1212",
    "id": 1,
    "updatedAt": null,
    "contactDetails_emailAddresses": [
        "example@example.com"
    ],
    "contactDetails_phoneNumbers": [
        "+1 1234567",
        "+44 1234567",
        "+65 1234567"
    ]
}
{
    "createdAt": "2022-02-01T15:34:44.1212",
    "id": 2,
    "updatedAt": "2022-02-01T15:34:44.1212",
    "contactDetails_emailAddresses": [
        "example@example.com"
    ],
    "contactDetails_phoneNumbers": null
}
```

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

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

which returns:

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

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

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

Which will return with the values and the connection:

```json
{
    "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.
