Importa file csv di grandi dimensioni

4

Ho ricevuto l'incarico di interrogare 2 file csv di grandi dimensioni ciascuno di circa 1 GB ciascuno. I file contengono dati correlati, quindi il file uno può contenere un elenco di numeri di ordine, date di ordinazione, ecc. E il file può contenere righe di ordine, codici di magazzino, quantità, ecc.

Altre restrizioni:

  • Nessun accesso a un database, quindi non posso inserire e interrogare in blocco
  • Deve essere in grado di aggiungere nuove query in un secondo momento

Il tipo di query che ho bisogno di scrivere è Somma tutte le righe raggruppate per Codice Stock o Quanti ordini ci sono stati tra due date.

La mia idea iniziale è quella di creare due modelli che rappresentano il foglio di calcolo e quindi importare il lotto e la query tramite Linq. Tuttavia questo potrebbe causare un problema a causa della dimensione dei dati (e non posso usare un database per il caricamento lazy, ecc.). Anche l'interrogazione dei modelli potrebbe essere piuttosto lenta.

L'altra idea che avevo era di creare una routine di importazione che valuti i dati riga per riga, cioè se devo contare quanti codici azionari ci sono in un file, posso semplicemente interrogare solo una colonna nel foglio di calcolo su una riga per linea e poi contarli. Ciò significherebbe che dovrei scrivere una importazione CSV e una sorta di classe di query.

Qualche idea o idea su come gestire questo o l'approccio migliore? Esiste già un modello per questo?

    
posta Rhodes73 06.10.2016 - 09:49
fonte

5 risposte

4

Non so quale, dalle altre risposte, funzionerà meglio per te.

AFAICT, osserverei, per lo più dal punto di vista dell'implementazione, che hanno tutti un senso, ma dal punto di vista del design, quale scala sarà migliore, sarà meglio mantenibile, ecc. Tutto dipende da altri fattori che potresti avere lasciato fuori dalla tua domanda così com'è.

Posso pensarci due.

1) la forma attuale dei dati flat che hai già a disposizione (indipendentemente dal volume di esso)

2) se disponi o meno della conoscenza completa di un set limitato e prevedibile dei criteri di query necessari per l'applicazione.

Ad esempio, se si possono fare due ipotesi più forti su questo (e di cui si conosce solo) - e attenzione, sono davvero molto forti:

a) se assumiamo che la risposta a (2) sia "sì";

e

b) il CSV flat è già ordinato su: ID ordine > riga ordine, con la data dell'ordine strettamente crescente dopo il primo (id ordine).

Quindi la risposta di radarbob potrebbe essere la migliore - dove tutto ciò che ti serve sarebbe un lettore CSV linea per linea veloce - implementando, ad es., IEnumerable < RawOrderLine > - con un piccolo codice da scrivere, facile da mantenere e senza altre dipendenze.

per esempio:.

(molto ipotetico e non testato codice)

OrderNo         OrderLine       OrderDate       StockCode       Quantity        Etc

1001            1               1/4/2016        Product1        5               ...
1001            2               1/4/2016        Product2        3               ...
1002            1               5/7/2016        Product1        10              ...
1003            1               2/8/2016        Product2        4               ...

ricevi tutti gli ordini per un determinato codice azionario e data data:

API:

IEnumerable<RawOrderLine> GetOrders(IEnumerable<RawOrderLine> csv, string stockCode, DateTime orderDate)

Procedura:

csv.Where(line => line.StockCode == stockCode && line.OrderDate == orderDate)

ricevi tutti gli ordini per un determinato codice azionario e rientra in un determinato intervallo di date:

API:

IEnumerable<RawOrderLine> GetOrders(IEnumerable<RawOrderLine> csv, string stockCode, DateTime startDate, DateTime endDate)

Procedura:

csv.SkipWhile(line => line.OrderDate < startDate).
    TakeWhile(line => line.OrderDate <= endDate).
    Where(line => line.StockCode == stockCode)

ottieni la somma delle quantità per un determinato codice azionario di tutti gli ordini che rientrano in un determinato intervallo di date:

API:

int GetQuantity(IEnumerable<RawOrderLine> csv, string stockCode, DateTime startDate, DateTime endDate)

Procedura:

csv.SkipWhile(line => line.OrderDate < startDate).
    TakeWhile(line => line.OrderDate <= endDate).
    Where(line => line.StockCode == stockCode).
    Aggregate
    (
        0,
        (sum, line) => sum += line.Quantity
    );

Etc, ecc.

Per un campione completo veloce e sporco, questo sotto genererà esattamente 3 milioni di ordini casuali nel futuro, distanziati tra 1 e 2 minuti l'uno dall'altro (vedi OrderDate), e con 1 a 10 righe di ordine ciascuno (di distinti coppie prodotto / quantità) - risultante in un file ~ 500 MB (se in codifica ASCII / ANSI comunque).

Le due query di esempio (negli ultimi 3 giorni del 2016, per "Product9") richiedono solo un paio di secondi dalla mia parte (macchina piuttosto lenta).

(la cosa principale che è fastidiosamente lenta è sputare quei dati di esempio casuali, in primo luogo)

//#define GENERATE_SAMPLE
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

/*
 * 
OrderNo         OrderLine       OrderDate       StockCode       Quantity        Etc
1001            1               1/4/2016        Product1        5               ...
1001            2               1/4/2016        Product2        3               ...
1002            1               5/7/2016        Product1        10              ...
1003            1               2/8/2016        Product2        4               ...
 * 
 */
namespace _332960
{
    public class RawOrderLine
    {
        public override string ToString()
        {
            return string.Format("{0},{1},{2},{3},{4}", OrderNo, OrderLine, OrderDate.ToString("yyyy-MM-dd HH:mm"), StockCode, Quantity);
        }

        public RawOrderLine Parse(string data)
        {
            var columns = data.Split(',');
            OrderNo = int.Parse(columns[0]);
            OrderLine = int.Parse(columns[1]);
            OrderDate = DateTime.Parse(columns[2]);
            StockCode = columns[3];
            Quantity = int.Parse(columns[4]);
            return this;
        }

        public int OrderNo { get; set; }

        public int OrderLine { get; set; }

        public DateTime OrderDate { get; set; }

        public string StockCode { get; set; }

        public int Quantity { get; set; }
    }

    public class RawOrderReader
    {
        public RawOrderReader(string filePath)
        {
            FilePath = filePath;
        }

        protected string FilePath { get; private set; }

        public IEnumerable<RawOrderLine> Data
        {
            get
            {
                using (var reader = new StreamReader(FilePath))
                {
                    string line;
                    while ((line = reader.ReadLine()) != null)
                    {
                        yield return new RawOrderLine().Parse(line);
                    }
                }
            }
        }
    }

    class Program
    {
        public const int BaseOrderCount = 1000 * 1000;

        public static string[] StockCodes =
            new[]
            {
                "Product0", "Product1", "Product2", "Product3", "Product4",
                "Product5", "Product6", "Product7", "Product8", "Product9"
            };
        static Random random = new Random(DateTime.Now.Millisecond);
        static DateTime lastOrderDate = DateTime.Now;
        static int lastOrderNo = BaseOrderCount - 1;

        static IEnumerable<RawOrderLine> NewRandomOrder()
        {
            var lines = new List<RawOrderLine>();

            // for random selection of 1 to 10 order lines (incl.)
            // one product per line
            var codes = 1 + random.Next(1023);
            var codeMask = 512; // idem
            var product = 9; // idem

            var orderLine = 0;
            lastOrderDate = lastOrderDate.AddMinutes(1 + random.Next(2));
            lastOrderNo++;

            while (codeMask > 0)
            {
                if ((codes & codeMask) > 0)
                {
                    lines.
                    Add
                    (
                        new RawOrderLine
                        {
                            OrderNo = lastOrderNo,
                            OrderLine = ++orderLine,
                            OrderDate = lastOrderDate,
                            StockCode = StockCodes[product],

                            // random quantity from 1 to 20 units (incl.)
                            Quantity = 1 + random.Next(20)
                        }
                    );
                }
                codeMask >>= 1;
                product--;
            }
            return lines;
        }

        public class CsvQuery
        {
            public IEnumerable<RawOrderLine> GetOrders(IEnumerable<RawOrderLine> csv, string stockCode, DateTime orderDate)
            {
                return
                    csv.Where(line => line.StockCode == stockCode && line.OrderDate == orderDate);
            }

            public IEnumerable<RawOrderLine> GetOrders(IEnumerable<RawOrderLine> csv, string stockCode, DateTime startDate, DateTime endDate)
            {
                return
                    csv.SkipWhile(line => line.OrderDate < startDate).
                        TakeWhile(line => line.OrderDate <= endDate).
                        Where(line => line.StockCode == stockCode);
            }

            public int GetQuantity(IEnumerable<RawOrderLine> csv, string stockCode, DateTime orderDate)
            {
                return
                    csv.Where(line => line.StockCode == stockCode && line.OrderDate == orderDate).
                        Aggregate
                        (
                            0,
                            (sum, line) => sum += line.Quantity
                        );
            }

            public int GetQuantity(IEnumerable<RawOrderLine> csv, string stockCode, DateTime startDate, DateTime endDate)
            {
                return
                    csv.SkipWhile(line => line.OrderDate < startDate).
                        TakeWhile(line => line.OrderDate < endDate).
                        Where(line => line.StockCode == stockCode).
                        Aggregate
                        (
                            0,
                            (sum, line) => sum += line.Quantity
                        );
            }
        }

        static void Main(string[] args)
        {
#if GENERATE_SAMPLE
            // create 3 millions of orders (of 1 to 10 order lines each)
            for (var i = 0; i < 3 * BaseOrderCount; i++)
            {
                var lines = NewRandomOrder();
                foreach (var line in lines)
                {
                    Console.WriteLine(line);
                }
            }
#else
            var csvReader = new RawOrderReader("data.csv");
            var csvQuery = new CsvQuery();

            Console.WriteLine();
            Console.WriteLine("Get last 3 days of Product9 orders... " + DateTime.Now);
            var lastThreeDaysOf2016Product9Orders =
                csvQuery.
                GetOrders
                (
                    csvReader.Data,
                    "Product9",
                    new DateTime(2016, 12, 29),
                    new DateTime(2016, 12, 31).AddDays(1).Subtract(new TimeSpan(0, 0, 1))
                ).
                ToArray();
            Console.WriteLine("... done @ " + DateTime.Now);

            Console.WriteLine();
            Console.WriteLine("Get last 3 days of Product9 quantities..." + DateTime.Now);
            var lastThreeDaysOf2016Product9Quantity =
                csvQuery.
                GetQuantity
                (
                    csvReader.Data,
                    "Product9",
                    new DateTime(2016, 12, 29),
                    new DateTime(2016, 12, 31).AddDays(1).Subtract(new TimeSpan(0, 0, 1))
                );
            Console.WriteLine("... done @ " + DateTime.Now);

            Console.WriteLine();
            Console.WriteLine("Details...");
            Console.WriteLine();
            foreach (var order in lastThreeDaysOf2016Product9Orders)
            {
                Console.WriteLine(order);
            }
            var quantityCheck = lastThreeDaysOf2016Product9Orders.Sum(order => order.Quantity);
            Console.WriteLine();
            Console.WriteLine("{0} = {1} ?", lastThreeDaysOf2016Product9Quantity, quantityCheck);
            Console.WriteLine();
#endif
            Console.ReadKey();
        }
    }
}

'Spero che questo aiuti.

    
risposta data 06.10.2016 - 20:26
fonte
7

A Stream non ha bisogno di leggere l'intero file in memoria, quindi ...

Non preoccuparti affatto di Excel se riesci ad accumulare le informazioni necessarie durante la lettura dei record:

  • Somma come vengono letti i record
  • Crea un dizionario di StockCode & count
  • Crea un dizionario di StockCode & sum
  • Definisci tipi di classi DTO snelli se devi avere una collezione completa.
  • Una raccolta o dizionario di detti oggetti DTO, digitati in .. qualunque sia

Forse classi separate per ogni scopo separato, possibilmente basato su una classe astratta con codice di lettura file comune - proofing futuro.

Link che parlano di efficiente I / O di streaming

risposta data 06.10.2016 - 17:10
fonte
4

Ummm ... Usa Excel. Excel può importare file CSV. Tutte le funzionalità che descrivi qui possono essere fatte in Excel.

Se si tratta di attività ricorrenti, è possibile scrivere alcune macro per automatizzare il caricamento dei file e semplicemente distribuire il file xlsx se necessario.

Ultima versione dei limiti di Excel:

Dimensioni del foglio di lavoro

1.048.576 righe per 16.384 colonne

Se i file contengono più dati di questi limiti, opterei per la soluzione @ MainMa / RadarBob.

    
risposta data 06.10.2016 - 15:42
fonte
3

Ovviamente puoi utilizzare l'importazione in blocco. Se non è possibile accedere al database aziendale per alcuni motivi, è necessario installarne uno localmente e utilizzarlo. Poiché sembra che tu stia lavorando con lo stack di Microsoft, SQL Server Express Edition è gratuito. Potrebbero essere utilizzati anche database open source (e sarebbe probabilmente più facile / veloce da installare).

Altrimenti, come già suggerito nei commenti, potresti essere in grado di caricare entrambi i file in memoria: 2 GB CSV tradurrebbe in meno di 2 GB di memoria utilizzata, che dovrebbe essere fattibile anche su hardware di basso livello, senza contare gli sviluppatori macchinari. Questo approccio, tuttavia, probabilmente non funzionerebbe bene se è necessario avviare e arrestare l'applicazione frequentemente: il caricamento di CSV di grandi dimensioni per eseguire solo una o due query non sarà efficiente come eseguire query sul database direttamente.

    
risposta data 06.10.2016 - 12:23
fonte
0

Un'altra soluzione tecnica [potenzialmente complessa e dolorosa] alla ricerca di un problema.

... query 2 large csv files which are roughly 1 GB in size ...

... No Access to a database ...

Perché no ?

Un database è lo strumento giusto per il lavoro.

Qualsiasi altra cosa sarà [più] ingombrante, lenta [er], meno affidabile e, in definitiva, costo di più (che attirerà la loro attenzione).

Tutti motivi convincenti per tornare indietro e sfidare questo cosiddetto "Requisito".

    
risposta data 07.10.2016 - 13:16
fonte

Leggi altre domande sui tag