Analisi dei Dati nei Sistemi Transazionali Distribuiti

In questo post cercherò di affrontare il problema dell'analisi dei dati nei sistemi transazionali distribuiti.
Se stai cercando idee per costruire un data warehouse centrale che ti permetta di raccogliere dati da tutto il sistema, indipendentemente dalla sua frammentazione, senza affogare nei costi operativi, questo post fa per te.

Tutto inizia innocentemente

La maggior parte dei sistemi che creiamo quotidianamente memorizza i dati in qualche database relazionale. Una scelta molto popolare e allo stesso tempo valida è PostgreSQL, che negli ultimi anni è diventato quasi uno standard nel settore.

La storia della maggior parte dei progetti di solito è molto simile: iniziamo verificando l'idea, acquisiamo i primi utenti, il sistema inizia a generare ricavi, il business cerca modi per aumentare i profitti, nascono nuove funzionalità. Ogni nuova funzionalità significa alcune nuove tabelle nel database.

Per accelerare lo sviluppo utilizziamo un ORM, generiamo automaticamente le migrazioni che creano e aggiornano lo schema del database.

Inizialmente tutto fila liscio, le nuove funzionalità portano i profitti attesi, il business inizia a scalare. Assumiamo più programmatori per creare più funzionalità in parallelo.

Di tanto in tanto qualcuno segnala che il sistema in alcuni punti inizia a "rallentare", una rapida ricognizione, una diagnosi ancora più rapida, manca un indice in qualche tabella.

Nella configurazione dei mapping ORM aggiungiamo un indice sul campo che il sistema usa molto frequentemente per cercare i dati. Problema risolto.

Il team di programmatori in crescita pone grande attenzione alla qualità, magari usa anche tecniche avanzate di sviluppo software come Event Storming o Domain-Driven Design.
Il CI/CD esegue innumerevoli test, assicurandosi che i cambiamenti non introducano regressioni.

L'idillio continua, il team o forse molti team iniziano ad aggiungere nuovi moduli al sistema. Moduli adeguatamente isolati, responsabili di compiti specifici, che non oltrepassano mai i loro confini e non invadono le competenze di altri moduli.

Per la comunicazione ovviamente vengono utilizzate le code, implementiamo il Pattern Outbox/Inbox

Per garantire un isolamento adeguato, stabiliamo regole che dicono che ogni modulo ha accesso solo alle tabelle del database che gli appartengono. Per ottenere dati da un altro modulo bisogna andare a quel modulo, sia tramite qualche API interna o in qualsiasi altro modo.

Di tanto in tanto il business viene da noi con la domanda potreste generarmi velocemente questo report?. Certo, qualche riga in SQL, forse qualche decina e il report è pronto.

Il business è soddisfatto, il report in formato CSV va in Excel (lo strumento BI più popolare), il business trae conclusioni, pianifica nuove funzionalità e modifiche.

Il tempo passa, nuove tabelle spuntano come funghi

In questo stato di cose possiamo continuare molto a lungo, anche diversi anni buoni.

Nel frattempo qualcuno da qualche parte avrà sicuramente l'idea di aggiungere al sistema la possibilità di generare report. È solo questione di tempo.

I report sullo stato del sistema sono per il business uno degli strumenti più cruciali per avere visibilità sui comportamenti, le preferenze o i trend degli utenti. Permettono non solo di capire cosa sta succedendo, ma anche di pianificare adeguatamente ciò che deve ancora accadere.

Migliori e più dettagliati sono i report, migliori decisioni si possono prendere sulla loro base. Buone decisioni aziendali si traducono in maggiori profitti, maggiori profitti si traducono in budget maggiore. Budget maggiore significa strumenti migliori, team più grandi, stipendi o bonus migliori.

Nell'interesse di ogni programmatore dovrebbe quindi essere fornire al business dati il più possibile migliori e precisi, dopo tutto risultati migliori si traducono direttamente in profitti migliori.

I primi sintomi

Il sistema funziona, genera profitti. È composto da circa 5, forse anche 10 moduli, ogni modulo consiste di 20-50 tabelle nel database. Ogni modulo fornisce i propri report.

• Vendite
• Marketing
• Logistica
• Stato del Magazzino
• Utenti

Ogni modulo espone solo una parte dei dati, un frammento del quadro più grande, ma nessuno fornisce una visione d'insieme.

I team hanno implementato chiavi di riferimento ai dati provenienti da altri moduli, sono riusciti persino nell'interfaccia utente a creare un unico posto da cui generare i report.

Ma questo ancora non basta...

Molto rapidamente si scopre che i report generati in moduli diversi, scritti da programmatori diversi, forse anche in tecnologie diverse, hanno formati dati diversi, standard di denominazione diversi.

Gli intervalli di date vengono interpretati diversamente, un modulo include le date di inizio e fine, un altro le esclude, e un altro ancora crea un intervallo aperto a destra per facilitare la paginazione, perché hanno anche un'API e quell'API usa lo stesso pezzo di codice.

Poiché ogni modulo è indipendente, ha i suoi confini, la sua nomenclatura, a un certo punto ci rendiamo conto che ciò che in un modulo chiamiamo in un certo modo, un altro modulo lo espone con un nome completamente diverso. Perché nel contesto di quel modulo ha senso.

Dopo un po' probabilmente ci renderemo anche conto che ogni team ha definito diversamente la politica di retention e archiviazione dei dati. Nonostante abbiamo nel modulo chiave dati degli ultimi 5 anni, non possiamo farci nulla, perché i moduli che fornivano i dati necessari per arricchire il report di base hanno dati solo degli ultimi 2 anni.

Non sono però problemi che un po' di magia in Excel non possa risolvere (forse tranne la mancanza di dati). A queste colonne cambieremo i nomi, queste le rimuoveremo, aggiungeremo un filtro veloce e basta.

Creeremo un grande file in cui avremo un foglio chiamato "Dashboard", e tutti gli altri saranno di sola lettura, alimenteranno il dashboard.

Forse questo approccio funzionerà anche per un po'. Forse anche più di un po', ma non facciamoci illusioni. Tutto questo alla fine crollerà, e secondo le leggi di Murphy crollerà nel momento peggiore possibile.

Cosa c'è di sbagliato in Excel?

Niente! Excel è uno strumento fantastico. Il problema non sta in Excel, ma nel suo utilizzo.

Tutta questa magia che consiste nel pulire e preparare i dati non dovrebbe avvenire in Excel, non su larga scala. Se parliamo di un report veloce una tantum, nessun problema. Facciamo quello che dobbiamo, scriviamo le formule, analizziamo i dati e dimentichiamo.

Se invece questo deve far parte della nostra routine quotidiana, se ciclicamente dobbiamo passare attraverso lo stesso processo, seguendo i continui cambiamenti e l'evoluzione del sistema, prima o poi si scoprirà che questi fogli sono obsoleti.

Le colonne hanno smesso di esistere o hanno cambiato nome, sono nate nuove colonne, il formato dei dati è cambiato o peggio ancora, uno dei team che si occupa di uno dei moduli ha eliminato alcuni dati senza sapere che erano utilizzati da qualche utente business da qualche parte in uno dei suoi report, che apre una volta al trimestre.

A lungo termine, fogli di calcolo più complessi che attingono dati da report generati automaticamente dal sistema, che vengono poi incollati in base a regole non esplicite, sono impossibili da mantenere.

E se collegassimo qualche strumento BI?

Hanno pensato molti programmatori che si sono ripetutamente scontrati con il problema della generazione di report.

Prendiamo ad esempio Metabase. Uno strumento gratuito che possiamo configurare in pochi minuti usando Docker.

Dargli accesso al nostro database e ad alcune o tutte le tabelle, e tramite un'interfaccia utente molto intuitiva il business potrà generare in modo molto facile e piacevole i report più complessi.

Report che potranno contenere dati da molti moduli contemporaneamente!

Possiamo anche assumere un analista dati con conoscenze di base di SQL, che tutto ciò che non si potrà fare con i click, realizzerà con una query opportunamente preparata.

Solo che questo non risolve il problema

Lo rimanda solo nel tempo.

Se guardiamo attentamente cosa è cambiato, è cambiata solo una cosa. Lo strumento... Abbiamo spostato il problema della pulizia e dell'unione dei dati da Excel a Metabase.

Excel è tornato al suo ruolo originale, ora possiamo caricare i report scaricati da Metabase in Excel.

Ma la nostra logica implicita di unione/pulizia dei dati si è spostata dal foglio di calcolo alle query SQL.

Inoltre, tutti i problemi sono rimasti gli stessi:

• incoerenza dei dati
• incoerenza nella denominazione
• mancanza di una politica uniforme di retrocompatibilità
• mancanza di una politica uniforme di retention dei dati

Allora stabiliamo processi e regole?

La maggior parte dei problemi sopra può essere risolta implementando processi e regole appropriate.

Possiamo stabilire standard di denominazione che dicono che ogni tabella nel database deve contenere nel nome il prefisso del modulo, e le colonne sono denominate con lettere minuscole e separate da underscore.

Possiamo stabilire che ogni modulo conserva i dati degli ultimi 5 anni (hot storage), tutto ciò che è più vecchio viene archiviato. (cold storage)

Possiamo stabilire che gli intervalli di date sono sempre trattati come intervalli aperti a destra.

Possiamo stabilire che non eliminiamo nessuna colonna dal database, o che prima di eliminare qualcosa entriamo prima in un periodo di transizione, durante il quale mostriamo a ogni utente del sistema quali colonne cambieranno e come.

Anche se assumiamo per la discussione che riusciremo a implementare questi processi globalmente tra diversi team, e che questi team li rispetteranno rigorosamente e molto accuratamente, non basta...

Scalare il database non è economico

Specialmente se ci basiamo su soluzioni cloud.

Immaginiamo una situazione in cui nelle ore di punta del sistema (quando gli utenti generano più transazioni) un analista business, che lavora secondo il proprio piano, deve generare un report basato su una tipica query SQL di diverse migliaia di righe?

L'analista lancia la query, il database inizia a macinare. La query dura 5, 10, 15 minuti. Il database inizia a sudare.

Gli utenti bombardano il sistema con nuovi ordini (o qualsiasi altra operazione che genera molte scritture) mentre l'analista aspetta i risultati.

Nello stesso momento qualcuno del business ha bisogno di controllare rapidamente alcuni report, ognuno di essi contiene "il numero totale di righe nella tabella". Ci sono diverse persone così.

Tutte queste operazioni si sovrappongono, il nostro database già molto carico non ce la fa.

Alcune transazioni degli utenti non vanno a buon fine.
Il sistema respira a malapena. Il tempo di attesa per le operazioni più basilari si misura in secondi.

E ora la ciliegina sulla torta, quando tutte queste scene dantesche hanno luogo, quando Pager Duty è rovente per ogni tipo e genere di incidenti, quando i team nel panico cercano di riportare in vita il sistema, i devops cercano di capire come scalare rapidamente il database...

Il CEO inizia la presentazione per un potenziale partner commerciale, con cui la collaborazione dovrebbe rivelarsi cruciale nella strategia di sviluppo dell'azienda...

Allora mettiamo semplicemente una replica?

Alla fine i report non sovraccaricheranno il nostro database transazionale.

Raddoppieremo i costi di mantenimento del database, ma ridurremo il rischio di sovraccarico del sistema e potremo collegare lo strumento di business intelligence preferito direttamente alla replica, il che ci darà dati in tempo reale.

Sembra fantastico, ma in pratica non è così semplice.

Tralasciando anche i potenziali problemi derivanti dalla natura stessa della replica, il problema principale e fondamentale che incontro più spesso è la percezione.

Un programmatore che ha generato quelle tabelle tramite mapping ORM guarderà le tabelle nel database in modo completamente diverso rispetto a un analista dati.

Il programmatore saprà quali tabelle devono essere unite per ottenere il quadro completo. Comprenderà i vincoli e le condizioni incorporati da qualche parte nel codice dell'applicazione. Soprattutto il programmatore conosce o almeno dovrebbe orientarsi su come appare il ciclo di vita del sistema (dei suoi dati).

Tutta questa conoscenza di solito non è disponibile per gli analisti.

È come dire a qualcuno di guardare qualcosa attraverso il buco della serratura. Qualcosa si può sicuramente vedere. Si possono trarre alcune conclusioni, ma sarà molto difficile ricostruire l'intero quadro.

Basta che abbiamo nel database una colonna di tipo JSONB in cui memorizziamo alcune strutture dati. Supponiamo che il sistema ammetta 3 combinazioni corrette della stessa struttura, ma una è super rara, così rara che nel sistema non si è ancora verificata. Guardando i dati, anche complessivamente, l'analista semplicemente non può sapere che esistono 3 combinazioni di una struttura. Durante la normalizzazione considererà 2 casi, mentre il terzo diventerà una bomba a orologeria che esploderà come sempre nel momento meno atteso.

In altre parole, se abbiamo nel sistema diversi moduli indipendenti. Ognuno con il proprio database, o almeno le proprie tabelle nel database. Il che in totale ci dà 200-300 tabelle, aspettarsi che l'analista lo gestisca senza problemi, non commetta errori e i report non si discostino dalle aspettative, è per usare un eufemismo ingenuo.

Nonostante tutto, esporre una copia/replica del database per gli analisti e darle un nome di 4 lettere derivato dalla parola "analytics" è ancora ampiamente utilizzato.

Gli strumenti BI competono per chi creerà un'interfaccia utente migliore, grazie alla quale i report si potranno generare con i click. Promettono che potremo analizzare i dati senza SQL.

Sì, questo può funzionare, in molti posti funziona proprio così. Di cosa però non parliamo apertamente è:

• Problemi con la retrocompatibilità e i cambiamenti nella struttura dei dati
• Problemi con il mantenimento / versionamento / test appropriati di query SQL/script giganteschi che normalizzano i dati al volo
• Repliche/Copie generano costi aggiuntivi
• La riduzione delle risorse delle repliche è impossibile o impedisce la generazione di report in tempi accettabili

Il che di conseguenza si riflette sulla qualità dei dati e sull'efficacia del processo decisionale aziendale.

Cosa ci rimane?

Forse prima stabiliamo quali problemi vogliamo risolvere in primo luogo:

1. L'analisi dei dati / generazione di report non deve avere alcun impatto sul funzionamento del sistema.
2. I dati nei report devono essere sempre freschi (è ammissibile un ritardo nei dati, stabilito individualmente)
3. I report devono riflettere lo stato reale, non distorto del sistema
4. La struttura dei dati deve essere resistente alla regressione
5. Politica coerente di retention e archiviazione dei dati

1) Separazione delle Risorse

Non è niente di rivoluzionario, se non vogliamo che il nostro sistema sia esposto a sovraccarichi derivanti dall'abuso del database attraverso la generazione di report, dobbiamo configurare un database separato.

Quale database scegliere per l'analisi?

Questo è in realtà un argomento per un articolo separato o addirittura una serie di articoli. Ci sono molte soluzioni, alcune migliori, altre peggiori. Non esiste una soluzione magica per tutti i problemi.

Il mio consiglio, specialmente per team più piccoli, inesperti nella gestione dei dati, è di non buttarsi su tecnologie con cui non abbiamo esperienza.

La chiave è il formato dati appropriato. Dopo aver convertito molte tabelle strette in una larga, molto probabilmente si scoprirà che generare lo stesso report solo senza usare 20x JOIN non richiede più 10 minuti ma meno di mezzo secondo.

E se il problema sono le aggregazioni, non le join?

Allora, invece di aggregare al volo, è meglio preparare una tabella contenente questi dati in forma aggregata, non grezza.

2) Dati Freschi

Va bene, ma se creiamo un nuovo database indipendente, come faremo in modo che i dati in questo database siano freschi e aggiornati?

Qui molto dipende dal ritardo accettabile nella sincronizzazione dei dati. Più spesso è sufficiente che il database analitico sia circa 24 ore indietro rispetto al database transazionale. Cioè contenga dati fino a "ieri", includendo tutto "ieri".

Perché? Perché poche decisioni aziendali vengono prese sul momento. Se alcune decisioni devono essere prese in così poco tempo, allora si costruiscono le automazioni appropriate.

Se un ritardo di 24 ore è accettabile (a volte non lo è e ci sono modi anche per questo), basta che eseguiamo le sincronizzazioni diverse volte al giorno. Ovviamente anche qui non c'è una regola d'oro. Così come non c'è una regola che dica quanto grande intervallo sincronizzare alla volta.

C'è però una buona pratica che facilita la sincronizzazione. Consiste nell'assicurarsi che le tabelle principali nel sistema transazionale contengano la data di creazione/modifica del record.

Avendo queste due informazioni siamo in grado di restringere la finestra di sincronizzazione a un periodo di tempo specifico.

Come appare in pratica? Possiamo ad esempio avviare il processo di sincronizzazione ogni 6 ore, raccogliendo solo i record modificati nelle ultime 24 ore.
Ovviamente questi sono numeri di esempio, questi valori devono essere stabiliti in base alle dimensioni e al comportamento dei dati.

Perché da 24 ore? Una protezione aggiuntiva. Potremmo prendere i dati solo da 7 ore, ma se per qualsiasi motivo la sincronizzazione non viene eseguita e non lo rileviamo, potremmo perdere dati.

3) Riflettere lo Stato del Sistema

La mia opinione su questo argomento può sembrare controversa, ma credo che la migliore conoscenza sui dati e sul comportamento del sistema o modulo ce l'abbia il team che costruisce quel sistema/modulo.

È proprio questo team che dovrebbe essere responsabile affinché i dati generati dal sistema o dalla sua parte di cui il team è responsabile, arrivino al repository dati centrale.

In altre parole, è proprio il team che implementa una data funzionalità che dovrebbe, sulla base dei requisiti raccolti in precedenza, trasformare questi dati nel formato appropriato e inviarli avanti.

Questo è probabilmente il modo più semplice per assicurarsi che i dati siano completi e che i programmatori del team siano consapevoli che questi dati vengono utilizzati da qualche parte. Il formato dei dati analitici diventa per loro una sorta di contratto – un contratto che devono rispettare.

Non è molto diverso da un contratto per lo schema API.

4) Resistenza alla regressione

Questo punto è probabilmente il più complicato. L'implementazione corretta dell'evoluzione dello schema dati è spesso non tanto difficile quanto problematica.

In breve, le regole sono così:

• Non eliminiamo mai le colonne
• Tutte le colonne che aggiungiamo devono essere nullable o avere un valore predefinito
• I tipi di colonne possiamo solo estendere ad esempio, int possiamo cambiare in bigint ma non viceversa
• Non cambiamo i nomi delle colonne

Quindi non possiamo eliminare nulla?

Possiamo, ma non a casaccio. In generale, come e quanto spesso romperemo la retrocompatibilità dipende solo da noi.

Se dalla nostra fonte di dati analitici utilizziamo solo internamente e, diciamo, l'analista che si occupa della costruzione dei report è aggiornato con i cambiamenti nel sistema, con un coordinamento appropriato potremmo aggiungere nuove tabelle e poi eliminare quelle vecchie, dandogli un momento per aggiornare i report.

Se invece la nostra fonte di dati analitici viene utilizzata per Data Science, ma lavoriamo in un ambiente multi-tenancy e i dati analitici/report sono resi disponibili ai clienti, allora dobbiamo affrontare la questione in modo completamente diverso.

Politica di archiviazione e conservazione dei dati

Come ho menzionato sopra, è molto importante che i dati nel database analitico, specialmente forniti da diversi moduli, siano soggetti alle stesse regole riguardo al tempo di conservazione.

Se manteniamo gli stati del magazzino nel sistema solo dall'ultimo anno, e gli ordini degli ultimi 5 anni, gli analisti non saranno in grado di costruire un report che conterrà dati da entrambe queste fonti.

Questo è più un problema di natura formale che tecnica. Sembrerebbe che basti semplicemente mettersi d'accordo, tuttavia in pratica non è così semplice.

Per stabilire una politica comune di conservazione e archiviazione dei dati bisogna considerare non solo gli aspetti tecnici, ma anche legali, aziendali o proprio analitici, il che può richiedere compromessi.

Esempi

Guardiamo ora un semplice esempio di processo ETL, il cui compito è trasferire i dati dal database transazionale al database analitico.

In questo esempio userò Flow PHP, ma non è qualcosa di particolarmente unico per PHP. In qualsiasi linguaggio possiamo costruire qualcosa di molto simile usando qualsiasi libreria che faciliti la creazione di applicazioni CLI e qualche strumento per l'elaborazione dei dati.

L'esempio seguente (in forma leggermente modificata) proviene dalla sessione di live streaming che ho avuto il piacere di registrare con Roland che gestisce il canale Never Code Alone. Il materiale video lo trovi su YouTube cercando "Flow PHP"

Supponiamo che questo sia più o meno il formato degli ordini:

schema
|-- order_id: ?uuid
|-- seller_id: uuid
|-- created_at: datetime
|-- updated_at: datetime
|-- cancelled_at: ?datetime
|-- discount: ?float
|-- email: string
|-- customer: string
|-- address: map<string, string>
|-- notes: list<string>
|-- items: list<structure{sku: string, quantity: integer, price: float}>

Il nostro obiettivo è trasferire questi ordini al database analitico, prepariamo quindi lo schema dei dati di input e di destinazione.

<?php

declare(strict_types=1);

namespace App\DataFrames;

// use statements

final class Orders
{
    public static function sourceSchema() : Schema
    {
        return schema(
            uuid_schema("order_id"),
            uuid_schema("seller_id"),
            datetime_schema("created_at"),
            datetime_schema("updated_at", nullable: true),
            datetime_schema("cancelled_at", nullable: true),
            float_schema("discount", nullable: true),
            string_schema("email"),
            string_schema("customer"),
            map_schema("address", type: type_map(key_type: type_string(), value_type: type_string())),
            list_schema("notes", type: type_list(element: type_string())),
            list_schema("items", type: type_list(element: type_structure(elements: ["item_id" => type_string(), "sku" => type_string(), "quantity" => type_integer(), "price" => type_float()]))),
        );
    }

    public static function destinationSchema() : Schema
    {
        return self::sourceSchema()
            ->replace('updated_at', datetime_schema("updated_at"))
            ->remove('address')
            ->add(
                string_schema('street', metadata: DbalMetadata::length(2048)),
                string_schema('city', metadata: DbalMetadata::length(512)),
                string_schema('zip', metadata: DbalMetadata::length(32)),
                string_schema('country', metadata: DbalMetadata::length(128)),
            )
            ->remove('items')
            ->add(
                uuid_schema('item_id', metadata: DbalMetadata::primaryKey()),
                string_schema('sku', metadata: DbalMetadata::length(64)),
                integer_schema('quantity'),
                integer_schema('price'),
                string_schema('currency', metadata: DbalMetadata::length(3)),
            )
            ;
    }
}

Notiamo che la struttura della tabella di destinazione non è più orientata agli ordini, ma agli articoli ordinati. Il nostro obiettivo è decomprimere gli articoli degli ordini in modo che ognuno sia una riga separata.

Grazie a questo l'analista che dovrà generare un report non dovrà più combinare e decomprimere il json al volo.

Anche la colonna Indirizzo è stata divisa in diverse colonne, grazie alle quali il report potrà essere facilmente filtrato.

Un'altra trasformazione importante è la conversione di price da float a int moltiplicando il valore in virgola mobile per 100.

L'ultimo cambiamento sarà l'aggiunta di informazioni sulla valuta in cui sono indicati i prezzi. Ma da dove viene questa informazione? Questo è proprio un dettaglio molto importante derivante da un'implementazione non molto buona. In questo caso specifico tutti gli ordini sono in dollari. Il sistema lo sa, i programmatori lo sanno, ma una persona che guarda le tabelle nel database senza contesto potrebbe non avere tale conoscenza.

La nostra struttura di destinazione dovrebbe apparire più o meno così:

schema
|-- order_id: uuid
|-- seller_id: uuid
|-- created_at: datetime
|-- updated_at: datetime
|-- cancelled_at: ?datetime
|-- discount: ?float
|-- email: string
|-- customer: string
|-- notes: list<string>
|-- street: string
|-- city: string
|-- zip: string
|-- country: string
|-- item_id: uuid
|-- sku: string
|-- quantity: integer
|-- price: integer
|-- currency: string

Il passo successivo sarà creare la tabella appropriata nel database analitico. Possiamo ottenerlo relativamente facilmente grazie all'adapter per Doctrine DBAL.

<?php

declare(strict_types=1);

namespace App\Dbal;

// use statements

final class SchemaProvider implements MigrationsSchemaProvider
{
    public const ANALYTICAL_ORDER_LINE_ITEMS = 'order_line_items';

    public function createSchema(): Schema
    {
        return new Schema(
            tables: [
                to_dbal_schema_table(Orders::destinationSchema(), self::ANALYTICAL_ORDER_LINE_ITEMS),
            ]
        );
    }
}

Nel database analitico memorizzeremo quindi una versione "semplificata" o "normalizzata" della tabella degli ordini. La normalizzazione consiste nel decomprimere gli articoli dell'ordine e renderli righe separate e dividere la colonna "Indirizzo" in diverse colonne.

Diamo quindi un'occhiata al comando CLI che sarà responsabile del trasferimento dei dati dal database transazionale al database analitico.

<?php

namespace App\Command;

// use statements

#[AsCommand(
    name: 'app:orders:import',
    description: 'Import orders from the transactional database to the analytical database.',
)]
class OrdersImportCommand extends Command
{
    public function __construct(
        private readonly Connection $transactional,
        private readonly Connection $analytical,
    )
    {
        parent::__construct();
    }

    protected function configure()
    {
        $this->addOption('start-date', null, InputOption::VALUE_REQUIRED, 'Start date for the data pull.', '-24 hours')
            ->addOption('end-date', null, InputOption::VALUE_REQUIRED, 'End date for the data pull.', 'now')
        ;
    }


    protected function execute(InputInterface $input, OutputInterface $output): int
    {
        $io = new SymfonyStyle($input, $output);

        $io->title('Importing orders');

        $startDate = type_datetime()->cast($input->getOption('start-date'));
        $endDate = type_datetime()->cast($input->getOption('end-date'));

        $io->progressStart();

        $report = data_frame()
            ->read(
                from_dbal_key_set_qb(
                    $this->transactional,
                    $this->transactional->createQueryBuilder()
                        ->select('*')
                        ->from(SchemaProvider::ORDERS)
                        ->where('updated_at BETWEEN :start_date AND :end_date')
                        ->setParameter('start_date', $startDate->format('Y-m-d H:i:s'))
                        ->setParameter('end_date', $endDate->format('Y-m-d H:i:s')),
                    pagination_key_set(
                        pagination_key_desc('updated_at'),
                        pagination_key_desc('order_id')
                    )
                )->withSchema(Orders::sourceSchema())
            )
            ->withEntry('_address', ref('address')->unpack())
            ->renameEach(rename_replace('_address.', ''))
            ->withEntry('_item', ref('items')->expand())
            ->withEntry('_item', ref('_item')->unpack())
            ->renameEach(rename_replace('_item.', ''))
            ->drop('_item', 'items', 'address')
            ->withEntry('currency', lit('USD'))
            ->withEntry('price', ref('price')->multiply(100))
            ->constrain(constraint_unique('item_id'))
            ->match(Orders::destinationSchema())
            ->write(
                to_dbal_table_insert(
                    $this->analytical,
                    SchemaProvider::ORDER_LINE_ITEMS,
                    SqliteInsertOptions::fromArray([
                        'conflict_columns' => ['item_id'],
                    ])
                )
            )
            ->run(function (Rows $rows) use ($io) {
                $io->progressAdvance($rows->count());
            }, analyze: analyze())
        ;

        $io->progressFinish();

        $io->newLine();

        $io->definitionList(
            'Orders Import Summary',
            new TableSeparator(),
            ['Execution time ' => \number_format($report->statistics()->executionTime->highResolutionTime->seconds) . ' seconds'],
            ['Memory usage ' => \number_format($report->statistics()->memory->max()->inMb()) . ' MB'],
            ['Rows inserted ' => \number_format($report->statistics()->totalRows())],
        );

        return Command::SUCCESS;
    }
}

Ovviamente non è la forma più bella o nemmeno più corretta. Normalmente il comando CLI non conterrebbe la definizione della pipeline ETL, ma per gli scopi dell'esempio è un buon inizio.

Un data warehouse centrale dedicato è senza dubbio un'opzione allettante, specialmente nei luoghi dove la mancanza di visibilità impedisce di prendere decisioni efficaci.

Fortunatamente questo è il tipo di funzionalità che può essere aggiunta praticamente in qualsiasi fase della vita del progetto.

Può richiedere l'introduzione di processi aggiuntivi e una certa disciplina da parte dei team, ma i benefici di tale soluzione sono enormi.

• Non c'è timore che l'analisi influenzi il funzionamento del sistema
• Abbiamo accesso a tutti gli angoli del nostro sistema, ogni microservizio o modulo
• Un tale database centrale è il miglior regalo per gli analisti
• Il Data Science non consiste più nel bruciare tempo a pulire i dati
• Possiamo collegare facilmente e in sicurezza praticamente qualsiasi strumento di Business Intelligence
• Creiamo una cultura del lavoro con i dati all'interno della nostra organizzazione

Ovviamente come tutto ciò che è nuovo, tali cambiamenti possono sembrare difficili da introdurre. La mancanza di esperienza nel lavorare con i dati almeno nelle fasi iniziali fa sì che il compito possa sembrare addirittura impossibile.

Dalla mia esperienza però risulta che il più difficile è iniziare, quando abbiamo già:

• Alcune prime Pipeline che elaborano i dati
• Alcuni o una dozzina di schemi dei nostri dati
• Alcune trasformazioni più complesse
• Test preparati
• Processi e procedure stabiliti

Il lavoro procede proprio in modo meccanico.

Vale però la pena ricordare che non esiste una soluzione universale che si adatti a ogni sistema. In ogni caso bisogna adattare l'approccio alle specificità del sistema e dell'organizzazione.

Come iniziare?

Se hai bisogno di aiuto nella costruzione di un data warehouse centrale, sarò felice di aiutarti.
Contattami e insieme creeremo una soluzione perfettamente adattata alle tue esigenze.

Ti invito anche a visitare il server Discord - Flow PHP, dove possiamo parlare direttamente.