4 ms·

Anyone have suggestions for tools to help with data analysis on moderate numbers of very large records? We're working with close a million records per year, whi

by jobu 9y ago

Anyone have suggestions for tools to help with data analysis on moderate numbers of very large records? We're working with close a million records per year, which isn't "Big Data", but the records themselves usually contain over a thousand fields. (Is that "Wide Data"?) Right now the records are stored in JSON documents, and we're working on ways to process those documents for reporting and analysis. There were some previous attempts to store the records "normalized" in SQL, but it quickly became difficult to modify and maintain.

cle 9y ago
Depends on not only the input, but also the output. What kind of analysis do you to perform?
- jobu 9y ago
  We want to be able to generate things like pivot tables (charts, graphs, etc) using location and/or time data from the records compared with many other fields in the record. We're feeding a little of the data into Microsoft SSRS, but setting it up is time-consuming and the results are slow and ugly. I've done this in the past with OLAP cubes (SSAS) with much larger numbers of small records. The difference here is there are so many more fields and we have 20-30 new or modified fields each year. It doesn't seem like this should be a unique problem, but I'm having a hard time finding tools that might make it easier and less time-consuming. Maybe I'm just not asking the right questions.
kwillets 9y ago
There are some SQL/JSON combinations that allow you to keep the unstructured stuff in a blob and promote some of the attributes as keys, etc.
rglullis 9y ago
I find it hard to believe that there is no normalization that can be done in a thousand-column record. If you use any decent ORM, you can look at these records and find out what are the real classes/entities and their relations. But if you really have some crazy single record with thousands of attributes, look at CouchDB. If you know your way around Javascript and don't mind writing map/reduce functions, it should be the easiest way for you to do reporting/analytics. Or if you just want to avoid a database altogether and know some Python, take a look at Pandas.
doppel 9y ago
A potential "quick" solution could be to identify the most frequent fields, create a postgres table with those as columns (with relevant indexes) and then add the rest to a JSONB fields. This would allow you quick lookup on specific columns, while still giving you access to the rest of your data as part of SQL queries (postgres 9.5+ has a lot of nice JSON operators as well as indexing options). Besides that, a column store might be what you are looking for if you are more interested in aggregate data on specific columns?
ac2u 9y ago
It really depends on what type of querying you're doing. If the data is wide as you say, but you end up performing a large number of aggregate queries on columns, like SUMming a bunch of values, then it might be worth looking at a columar store which structures your data on disk by column rather than by row. https://en.wikipedia.org/wiki/Column-oriented_DBMS https://en.wikipedia.org/wiki/Column-oriented_DBMS examples include AWS Redshift or even just Postgres with some bells and whistles that end up doing the same thing. Again though, I go back to my first point and try and get a good characterization of how the data is typically access, a million rows per year isn't much and you might just get a lot further with some indexing strategies in your regular RDBMS.
SmirkingRevenge 9y ago
Datasets I've worked with have topped out probably at around 500-600 fields - so not as wide but often weighing in the hundreds of GB, and often with hundreds of millions of rows. Spark is our primary tool to handle cleansing, analysis, feature engineering, joins, machine learning, etc. It does quite nicely. I'm generally for people not over complicating their stack with tools beyond their actual needs - but a lot of this stuff is quite some distance from where it was in 2013 when this article is written, in ease-of-use, ops, tooling and maturity. Its simply becoming cheaper and easier to throw even modest amounts of data through a "big data" engine like spark in many cases, than it is to use more traditional tools which might be able to do the job, but require more advanced tuning, ops, and possibly infrastructure. And there are lots of compelling managed solutions out there these days. Amazon's EMR (elastic map reduce) is a popular option, gives you lots of tools to choose from, including Spark. Google Data Proc is similar I believe.

by jobu 9y ago

cle 9y ago

Depends on not only the input, but also the output. What kind of analysis do you to perform?

jobu 9y ago
We want to be able to generate things like pivot tables (charts, graphs, etc) using location and/or time data from the records compared with many other fields in the record. We're feeding a little of the data into Microsoft SSRS, but setting it up is time-consuming and the results are slow and ugly. I've done this in the past with OLAP cubes (SSAS) with much larger numbers of small records. The difference here is there are so many more fields and we have 20-30 new or modified fields each year. It doesn't seem like this should be a unique problem, but I'm having a hard time finding tools that might make it easier and less time-consuming. Maybe I'm just not asking the right questions.

kwillets 9y ago

There are some SQL/JSON combinations that allow you to keep the unstructured stuff in a blob and promote some of the attributes as keys, etc.

rglullis 9y ago

I find it hard to believe that there is no normalization that can be done in a thousand-column record. If you use any decent ORM, you can look at these records and find out what are the real classes/entities and their relations. But if you really have some crazy single record with thousands of attributes, look at CouchDB. If you know your way around Javascript and don't mind writing map/reduce functions, it should be the easiest way for you to do reporting/analytics. Or if you just want to avoid a database altogether and know some Python, take a look at Pandas.

doppel 9y ago

A potential "quick" solution could be to identify the most frequent fields, create a postgres table with those as columns (with relevant indexes) and then add the rest to a JSONB fields. This would allow you quick lookup on specific columns, while still giving you access to the rest of your data as part of SQL queries (postgres 9.5+ has a lot of nice JSON operators as well as indexing options). Besides that, a column store might be what you are looking for if you are more interested in aggregate data on specific columns?

ac2u 9y ago

It really depends on what type of querying you're doing. If the data is wide as you say, but you end up performing a large number of aggregate queries on columns, like SUMming a bunch of values, then it might be worth looking at a columar store which structures your data on disk by column rather than by row. https://en.wikipedia.org/wiki/Column-oriented_DBMS https://en.wikipedia.org/wiki/Column-oriented_DBMS examples include AWS Redshift or even just Postgres with some bells and whistles that end up doing the same thing. Again though, I go back to my first point and try and get a good characterization of how the data is typically access, a million rows per year isn't much and you might just get a lot further with some indexing strategies in your regular RDBMS.

SmirkingRevenge 9y ago

Datasets I've worked with have topped out probably at around 500-600 fields - so not as wide but often weighing in the hundreds of GB, and often with hundreds of millions of rows. Spark is our primary tool to handle cleansing, analysis, feature engineering, joins, machine learning, etc. It does quite nicely. I'm generally for people not over complicating their stack with tools beyond their actual needs - but a lot of this stuff is quite some distance from where it was in 2013 when this article is written, in ease-of-use, ops, tooling and maturity. Its simply becoming cheaper and easier to throw even modest amounts of data through a "big data" engine like spark in many cases, than it is to use more traditional tools which might be able to do the job, but require more advanced tuning, ops, and possibly infrastructure. And there are lots of compelling managed solutions out there these days. Amazon's EMR (elastic map reduce) is a popular option, gives you lots of tools to choose from, including Spark. Google Data Proc is similar I believe.