Dataflows Resource write to db normalized

This library provides some dataflows processing for normalizing a resource.

It has special support for storing normalized data into DB tables.

What is normalization?

In short, it is the process of reducing duplication in a dataset.

More can be read about this concept here.

Example

Let's take, as an example, this world cities dataset (we shall call it the fact resource):

from dataflows import Flow, load, printer

Flow(
    load('https://datahub.io/core/world-cities/r/world-cities.csv', name='cities'),
    printer(num_rows=1)
).process()

cities:

It seems that the country and subcountry columns are quite repetitive - let's extract them into a separate, deduplicated resource (we will call that a dimension resource).

To do that we use the normalize processor.

This processor receives a single resource name, and a list of NormGroup instances. Each of these groups specifies one new dimension resource to be extracted and deduplicated.

Let's see it in action:

from dataflows_normalize import normalize, NormGroup

Flow(
    load('https://datahub.io/core/world-cities/r/world-cities.csv', name='cities'),
    normalize([
       NormGroup(['country', 'subcountry'], 'country_id', 'id') 
    ], resource='cities'),
    printer()
).process()

cities:

cities_country_id:

If we follow the last line in the dataset (Chitungwiza), we can see that an entry for its region (Zimbabwe/Harare) was created with id 2676, and that id was added to the original row instead of the original values.

How much did we gain?

The original CSV file has a size of 895,586 bytes.

If we save the two new resources as CSVs, we would get

542,299 bytes for the fact resource and 68,023 for the regions dimension resource - a total of 610,322 bytes (or a reduction of 31% in size).

Not only this helps with size, it also improves greatly DB performance to store data in normalized form.

DB Normalization

Running similar code to above, only using normalize_to_db will do the following:

• Load existing values from database dimension tables (in case these tables exist)
• Normalize the input data, and split into fact and dimension resources
• Update the DB tables with new values, while reusing existing references

The main difference in usage from normalize is that the names of DB tables are provided.

from dataflows_normalize import normalize_to_db, NormGroup

Flow(
    load('https://datahub.io/core/world-cities/r/world-cities.csv', name='cities'),
    normalize_to_db(
        [
            NormGroup(['country', 'subcountry'], 'country_id', 'id', db_table='countries_db_table') 
        ], 
        'cities_db_table', 'cities',
        db_connection_str='...'
    ),
).process()