Big Data Management. 2 - First Lines of Code

In the previous article, we introduced NoSQL databases and their advantages, use cases, and relevant justifications. Usually in technology there is a maximum premise: "If something works, don't touch it." The problem arises when you reach the technological limits of the architecture. When you understand the limitations of traditional databases for proper, efficient, and sustainable data management, there is no other solution but to turn to structures like ArcticDB that we will explain in this article.

In this article, we will explain the fundamental principles in a more theoretical way. We will take a brief tour of the native datalake functions, trying to provide more theoretical knowledge, this time from a practical point of view.

And subsequently we will create the first part of our datalake code. We will generate different modules that solve certain day-to-day tasks and their contingencies, while collecting the first data, performing the appropriate processes to have two distinct outputs, common to all datasets:

- Data: The data we want to push to the datalake. Remember that being a NoSQL Datalake, when we talk about data, we talk about combinations of 0s and 1s, regardless of whether they are time series data, fundamental data, etc. Datalakes consider data as registered binary information, which is subsequently accessed, unlike SQL databases, which have fixed schemas and any non-typed (adapted to their schemas) information produces an error.

- Metadata: We need to generate metadata to access certain relevant information such as the last time the database was updated, when it was created, what length it has, etc. Metadata is customized values based on the data to be processed.

Once the process is finished, our goal will be to store our information in our S3 object (push data), or in plain English, send it to the cloud.

Deploying the Architecture

Required Packages

The only required package is arcticdb. If you want to install it, you just need to run:

pip install arcticdb

Required Data

First of all, for those who have no idea what an S3 service is:

Introducción a AWS S3 (Amazon Simple Storage Service)

Artículo básico sobre servicio S3 AWS. Conceptos, funcionamiento y características principales.

Mediumdiego.coder

Then here I leave some S3 providers that I have personally tested, and have not given me errors, and I have been satisfied with the performance. I order them by importance. That is, the first is the one I would use in more critical projects, and the last is the one I would use for playing.

Access to an S3:

• https://aws.amazon.com/es/s3/
• https://blog.cloudflare.com/r2-open-beta/
• https://wasabi.com/

At this point, we will already have all the necessary information to create our object.

Our object is a "pointer" that accumulates all the credentials and addresses needed to connect to our datalake. It is like the master key that will allow us to perform all actions within the datalake.

To create the pointer, we do it via:

from arcticdb import Arctic

• endpoint: your S3 URL
• db: your datalake within S3
• access key: access key
• secret key: secret access key

endpoint = 'tu-localizacion.tuproveedor.com'
db = 'datalake.stable' 
access_key = "soymuyseguro" 
secret_key= "noselodigasanadie" 

Now, once the variables are defined, we create the pointer passing all the environment values as parameters.

datalake = Arctic(f's3s://{endpoint}:{db}?access=
{access_key}&secret={secret_key}')

The object we created, the key that gives us total access to our datalake, returns this configuration.

Basic functionalities of the datalake object.

This object is used to communicate with the first layer of our datalake. The first layer handles the following tasks:

• create: This first layer is capable of creating databases within the datalake where we will deposit and query in the future, the place where we will dump all the symbols of our information.
• delete: Opposite to creation, it also allows us to delete an entire database, which implies all tickers and all information inside it.
• list: Shows all active databases within a datalake.
• access: Accesses the second layer of the datalake, pointing to a specific database within the datalake.
• get URI: Returns the URL of the S3s service it is pointing to.

Create

To create a database within the datalake, it is done as follows:

datalake.create_library('nombre.de.la.database')

When creating a database without assigning it to any variable, we get the response; if it has been assigned to a value, we just need to call the value:

As a summary, we can conclude that through the datalake object we access the first layer, and when we execute the create_library function, we make it create a new database within the datalake. A necessary condition to subsequently be able to dump all kinds of information.

To verify that everything was correct, we would just need to run the List command.

Delete

Opposite to create_library(), arcticdb has the delete_library() function. This call deletes the entire database that has been requested for deletion. We are not talking about a specific ticker or a specific day; we are talking about deleting all days of all tickers of a complete database. To delete a ticker, other functions are used.

datalake.delete_library('nombre.de.la.database')

List

When using the list_libraries() function, the datalake returns a list with the unique names of all the databases it contains. With this function we would see the datasets that are currently active within the datalake.

datalake.list_libraries()

A very useful function for verification, and one that will be a constant we will use in data management with arcticdb and Python.

Access

One of the most useful functions is accessing databases. Once we have the pointer correctly defined, we can access them in two ways.

• via []:

datalake['economic.calendar.stable']

datalake['economic.calendar.stable'] would access that database

• via function():

datalake.get_library('economic.calendar.stable')

additionally, assigning these calls facilitates later work with layer 3 (the symbols within the databases, within the datalake)

get URI

This function returns the S3 address the object is pointing to. We won't use it for now, but the call can be made like this:

datalake.get_uri()

And it returns in a human-readable form the values the datalake pointer is executing (the same ones we entered earlier).

Structure Summary

As a summary, you should have access to some S3 storage provider; in my recommendation, for production tasks, Amazon S3 is the best option, and for less critical datalakes, any provider is acceptable, but I would especially recommend wasabisys. Why? Because I only recommend what I know, and it has given me excellent results compared to the price paid per gigabyte.

Additionally, we toured the first layer of the datalake, the "orchestrator" layer, which organizes the creation, deletion, listing, and access to the second-layer databases

Macroeconomic Data

To start with a simple time series data set, we will start with macroeconomic data. The first relevant decision is choosing the provider we want to use. For macroeconomic data, doing a web search, we see:

• TheWorldBank databank
• Reserva Federal ST Louis
• FMI
• Trading Economics
• Nasdaq Datalink

There is a high variety of information sources for obtaining macroeconomic data. And the goal is not to manage data for the sake of managing. The goal is to manage useful, quality data. Consequently, dumping all datasets to the database makes no sense computationally.

We will download a macroeconomic expectations calendar as a first proof of concept,

Plan of Attack

Our goal will be to collect a macroeconomic calendar that includes relevant information about expectations and data, since with that information, we could later analyze how the factor influences a basket of assets, or even against the set of markets, estimate the maximum action point of the event...

Needing conventional information, one could start working on the original datasets and build a complex system that simply executes a delicate task. But there are also other alternatives; in this case I speak of eodhd, a European data provider that aggregates and processes this information.

In summary, we will call the data provider, request the required information, process it to adapt it to our interests, generate its metadata, and dump it into the datalake.

Coming from a paid provider, it's not necessary to process the data, since they do it for us and return the work ready for use.

Proof of Concept

The approximate schema we are looking for in this proof of concept would correspond to something like:

Our main structure will be a loop that goes through all the tickers it recognizes it should process, processes them, and dumps them to the datalake. Something really simple.

For this we will use

import pandas as pd
import numpy as np 
import requests
from arcticdb import Arctic
from alive_progress import alive_bar

In addition to the usual libraries, we will use alive_progress to calculate the process and animate it in a simple way.

First we will program the management of layer 1, and then we will go through the management of layer 2, which basically consists of iterating through a list of tickers, obtained from the data provider, and processing them within the pipeline and dumping them.

Code

In the first part, we will program the layer 1 management methods, trying to encapsulate as much as possible, since they are processes that are used recurrently in data management with arcticdb.

Managing Layer 1 of the Datalake

Through this part, we will manage access to layer 1 of the datalake, managing the databases in an automated way and accessing each of them appropriately.

Managing Datalinks

This function takes from the dataz (which is a list) the names of the databases needed for the data ingestion process and creates them if they are not already created. This function is very extensible, allowing complete management from the start of the algorithm, but since we are programming a functional proof of concept, we keep it as minimal as possible.

def prepare_datalinks(dataz):
    print('[PREPARE DATALINKS] OK')
    for d in dataz:
        if d not in ac.list_libraries():
            ac.create_library(d)
            print('[DATABASE]:',d.upper(),'NOT FOUND, CREATING')
        else:
            print('[DATABASE]:',d.upper(),'IS IN THE DATALAKE')
    print('[QA] - DATALINKS READY)

Getting countries in the Datalake

One way to get all the countries that are part of the dataset is by making a request and creating a list with the unique values of the country field. It's not the most efficient but it's functional and understandable.

def get_countries():
	url = f'https://eodhd.com/api/economic-events?&from=1900-01-01&to=2024-01-01&limit=1000&comparison=qoq&api_token=6326eedae7f1e4.58558306'
    response = requests.get(url)
    if response.status_code == 200:
    	data = response.json()
        return pd.DataFrame.from_dict(data)['country'].unique().tolist()

Managing Candidates

def construct_candidates(update):
    calendar = ac.get_library(dataz[0])
    countries = get_countries()
    if update == False:
        in_datalake = calendar.list_symbols()
        out = [ticker for ticker in countries if ticker not in in_datalake]
        print('[QA PREADOR INGESTING RE STARTED] - COUNTRIES TOTAL', len(countries),'COUNTRIES IN DATALAKE',len(in_datalake),'TO PUSH :',len(out))
        return out
    else:
        print('[QA PREDATOR] - UPDATING ALL DATALAKE')
        return countries

Managing Layer 2 of the Datalake

Once the processes executed on layer 1 of the datalake are done and we have the environment ready, we will manage layer two as follows:

• Request information from the data broker
• If it exists, we process it and push it, and also add metadata to it
• If it doesn't exist, we discard it and continue (maybe some logging would be nice)

with alive_bar(len(countries),title='COUNTRIES',bar='halloween',force_tty=True)as bar:      
    for t in countries:
        data = pd.DataFrame(obtener_datos_simbolo(t,lista=False)).astype(str).set_index('date').sort_index()
        meta = {'inicio':data.index[0],'fin':data.index[-1],'indicadores':len(data['type'].unique()),'last_update':pd.Timestamp.now()}
        calendar.write(t,data,metadata=meta)
        i = i +1
        datalake.loc[t] = meta
        bar()
    metadata.write('CALENDAR',datalake)

Managing Post-Exec Tasks

Once all the second layer tasks are finished, we execute post-execution tasks, such as pushing metadata, listing symbols, or any other necessary maintenance task.

We will only list the symbols, because due to arcticdb's design, whenever there are modifications to symbols, we must perform listing processes. This starts adding some lag to the loading when more than 500 symbols are exceeded. Consequently, we list them at the end of the load, as a way to pre-process the data before it is required for future use.

calendar.list_symbols()

Results

Once the task is executed, we can evaluate the result. Our goal is to access the database we just created and ingest information, and query the generated metadata. Randomly verify 5 values, and done! We have created the first database within the datalake, with the macroeconomic calendars available in the eodhd API.