Compilare l'analisi da milioni di righe in PostgreSQL

0

Come compiliamo l'analisi da milioni di righe in una tabella PostgreSQL?

Estraiamo i dati degli ordini da più CRM e abbiamo bisogno di compilare i dati per la segnalazione e ogni CRM ha la propria tabella degli ordini. Compiliamo queste tabelle in una tabella compiled_orders in incrementi di 24 ore.

La nostra attuale implementazione utilizza le viste SQL per aggregare i risultati e SUM le colonne

CREATE OR REPLACE VIEW crm1_sql_views AS
  SELECT
      account_id
    , name
    , COUNT(*) AS order_count
    , SUM(CASE WHEN
        status = 0
        THEN 1 ELSE 0 END) AS approved_count
    , SUM(CASE WHEN
        status = 0
        THEN total ELSE 0 END) AS approved_total
  FROM crm1_orders
  WHERE
    AND is_test = false
  GROUP BY
    account_id
    , name
  ;

Selezioniamo i dati che vogliamo da questa vista. Il problema che stiamo incontrando è che una query come questa recupera tutti i dati dell'ordine per un client in memoria. Se un client ha ordini 20M, diventa estremamente lento e talvolta i risultati della query sono più grandi della memoria / cache disponibile.

Come facciamo a incrementare / coerentemente / rapidamente i record 20M in una tabella e compilarli in un'altra tabella?

Aumentare l'hardware è una soluzione, ma riteniamo che non sia la soluzione corretta al momento. Abbiamo esaminato le visualizzazioni materializzate, ma poiché ogni CRM ha le proprie tabelle, avrebbe importanti implicazioni sulla manutenzione ogni volta che abbiamo aggiunto un nuovo CRM alla nostra offerta.

L'obiettivo è che i nostri utenti finali rispondano a domande come: - Quanti ordini abbiamo ricevuto la scorsa settimana / mese / anno? - In quale giorno della settimana ricevo la maggior parte degli ordini?

Quali tecnologie / metodologie / termini abbiamo bisogno di guardare e ricercare?

  • Sharding
  • ETL
  • Pipelines di dati
  • Strumenti "Big Data"
  • NoSQL
posta Diggity 06.01.2017 - 19:39
fonte

2 risposte

4

Non vedo la necessità di cambiare l'intera tecnologia o infrastruttura db solo perché è necessario un po 'di ottimizzazione qui. Inizia con qualcosa di semplice come scrivere una stored procedure (o magari un programma client nel tuo linguaggio di programmazione preferito) e raccogliere i risultati in una nuova tabella. Se lo fai bene, la memoria necessaria sarà proporzionale al numero di coppie diverse ( account_id,name ), non di più. Immagino che quel numero sia molto più piccolo del numero di ordini.

Su una scala più ampia, informati sul data warehouseing e su come modellare cose come uno "schema a stella" per il tipo di query che hai menzionato. Troverete un sacco di libri, tutorial e informazioni sul web per questo. "ETL" sarà davvero il termine giusto per la ricerca, dal momento che è il tipo di processo necessario per riempire il tuo "data warehouse".

    
risposta data 06.01.2017 - 19:52
fonte
0

Sono d'accordo con Doc Brown, se stai cercando una soluzione permanente a lungo termine, considera di caricare i tuoi dati in un data warehouse.

L'implementazione di un data warehouse aumenterà la velocità della query e le prestazioni generali del sistema.

I data warehouse sono appositamente progettati e costruiti con particolare attenzione alla velocità di recupero e analisi dei dati. Inoltre, un data warehouse è progettato per la memorizzazione di grandi volumi di dati e la possibilità di interrogare rapidamente i dati.

Inoltre, il data warehouse consente di rimuovere un grande carico di sistema dall'ambiente operativo e distribuire efficacemente il carico del sistema sull'infrastruttura tecnologica dell'intera organizzazione.

Una volta che un data warehouse è presente e popolato con i dati, diventerà parte di una soluzione di BI, i tuoi utenti finali creeranno direttamente report dei loro ordini.

Per caricare i tuoi dati di PosgreSQL in un data warehouse avrai bisogno di uno strumento ETL come Alooma.

    
risposta data 22.08.2017 - 12:14
fonte

Leggi altre domande sui tag