Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Re: perl group by and sort from a csv input file

by sundialsvc4 (Abbot)
on Jul 28, 2017 at 14:43 UTC ( #1196208=note: print w/replies, xml ) Need Help??


in reply to perl group by and sort from a csv input file

When faced with a similar requirement, I shrewdly anticipated that this requirement was the tip of a larger iceberg:   that the client would very soon be asking for the result of other queries against the data.   So, I designed the process from the get-go to use an SQLite database ... which, of course, is just a disk-file.   (There is no “server.”)   It is very easy even with command-line tools (incorporated as a bash-script, say) to import a CSV and make a table from it.   Even gigantic amounts of data can be efficiently processed.   (Sqlite is frankly an awesome piece of work ...)

DBD::Sqlite2 is particularly handy because it includes a copy of Sqlite as part of the package.

And then, you just solve the problem with queries:
 SELECT column1, column2, SUM(column3) FROM mytable GROUP BY column1, column2 ORDER BY column1, column2.

Anything and everything else that they next dream up, can be answered quickly and in the same way.   (You can also remind them that they can access the data and run queries against it using any spreadsheet ...)


The only “gotcha” that you should be aware of with SQLite, particularly when you are updating information but also when you are reading it, is that you should do everything in a “transaction.”   If you do not, you will see that everything runs very slowly, because SQLite is then purposely not using “lazy reads and writes.”

Replies are listed 'Best First'.
Re^2: perl group by and sort from a csv input file
by Corion (Pope) on Jul 28, 2017 at 15:10 UTC

    Please do not recommend DBD::SQLite2.

    There is DBD::SQLite, which packages the more recent versions of SQLite and unless you have very specific reasons to use SQLite v2, do not use it.

    Why did you recommend DBD::SQLite2 at all?

      A reply falls below the community's threshold of quality. You may see it by logging in.
Re^2: perl group by and sort from a csv input file
by Anonymous Monk on Jul 28, 2017 at 15:30 UTC
    you will see that everything runs very slowly, because SQLite is then purposely not using “lazy reads and writes.”

    got any proof?

      he can't even understand the docs of the module that shouldn't be used, of course he has no proof, or code, or anything helpful
      A reply falls below the community's threshold of quality. You may see it by logging in.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1196208]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (3)
As of 2019-07-21 10:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    If you were the first to set foot on the Moon, what would be your epigram?






    Results (7 votes). Check out past polls.

    Notices?