8 ms·

This is so true. I do business intelligence at Amazon, and I've seen this play out millions of times over. The fetishization of big data ends up meaning that ev

This is so true. I do business intelligence at Amazon, and I've seen this play out millions of times over. The fetishization of big data ends up meaning that everybody thinks their problem needs big data. After 4 years in a role where I am expected to use big data clusters regularly, I've really only needed it twice. To be fair, in a complex environment with multiple data sources (databases, flat files, excel docs, service logs), ETL can get really absurdly complicated. But that is still no excuse to introduce big data if your data isn't actually big. I really hate pat-myself-on-the-back stories, but I'm really proud of this moment, so I'm gonna share. One time a principal engineer came to me with a data analysis request and told me that the data would be available to me soon, only to come to me an hour later with the bad news that the data was 2 terabytes and I'd probably have to spin up an EMR cluster. I borrowed a spinning disk USB drive, loaded all the data into a SQLite database, and had his analysis done before he could even set up a cluster with Spark. The proud moment comes when he tells his boss that we already had the analysis done despite his warning that it might take a few days because "big data". It was then that I got to tell him about this phenomenal new technology called SQLite and he set up a seminar where I got to teach big data engineers how to use it :) P.S. If you do any of this sort of large dataset analysis in SQLite, upgrade to the latest version with every release, even if it means you have to `make; make install;` Seemingly every new release since about 3.8.0 has given me usable new features and noticeable query optimizations that are relevant for large query data analysis.

RivieraKid 11y ago
What technologies / architecture does Amazon use for business intelligence? I've just done a business intelligence course so I'm interested how do more "technology-centered" companies approach BI, whether it's the same thing I learned in the course (put everything into an integrated relational database if I simplify a lot).
- saosebastiao 11y ago
  Lots and lots of SQL (a very good thing IMO). This can come in the form of Oracle, Redshift, or any of the commonly available RDS databases (probably not SQL Server though). This is augmented with a lot of Big Data stuff, which used to be pretty diverse (Pig, Hive, raw Hadoop, etc.) but is moving very quickly towards a Spark-centric platform. There is occasionally some commercial software like Tableau/Microstrategy. Apart from that, it is a whole lot of homegrown technology. As far as architecture is concerned, every team is different. At least on the retail side, we tend to center around a large data warehouse that is currently transitioning from oracle to redshift, and with daily log parsing ETL jobs from most services to populate tables in that data warehouse. Downstream of the data warehouse is fair game for pretty much anything.
  - drited 11y ago
    Would you know why sql server generally isn't used?
    - deleted 11y ago
      [deleted]
    - sshykes 11y ago
      Probably because: * SQL Server is proprietary * SQL Server licenses are expensive * SQL Server runs only on Windows (or least used to?)
      - hunterjrj 11y ago
        I chuckled a bit because the GP mentioned Oracle. I'm sure you've heard this one: Customer: "How much does an Oracle database license cost?" Oracle Rep: "Well, how much do you have?"
        true_religion 11y ago
        That's actually not a joke. Oracle liked to argue you should accept server licences as being a fraction of your budget, so when it went up you'd automatically pay them more.
      - avon 11y ago
        Amazon actually does allow SQL Server. The poster saying it probably didn't was influenced by the fact that Azure, Microsoft's own cloud solution is an AWS competitor. To your own points: SQL Server is as proprietary as Oracle SQL Server is cheaper than Oracle SQL Server is being ported to Linux in 2017 :)
        newjersey 11y ago
        Amazon.com seems to strongly encourage the companies it acquires to use aws. Apparently, woot.com is fixing to rewrite the website in Java or something just so they do not have to use Windows. Apparently, it makes sense to them.
ignoramous 11y ago
Fellow amazonian here. We switched from a massively distributed datastore (not to be named) to rodb for storage and found 10x improvement, not to mention eliminating cost and other head-aches; kind of expected since rodb is an embedded db...
- pneumatics 11y ago
  What is rodb?
  - sshykes 11y ago
    Good question. Is it this [0]? [0] http://researcher.watson.ibm.com/researcher/view_group.php?id=4165 http://researcher.watson.ibm.com/researcher/view_group.php?i...
  - csears 11y ago
    I'm guessing he means a "Real-time Operational Database". This seems to be a generic term for system like a data warehouse that contains current data, instead of just historical data. If you are taking the output of a Spark flow and storing it in Postgres or MongoDB or HBase for applications to query, then those could be considered RODBs. Since this is Amazon, I suspect he is referring to SPICE (or their internal version), which was released last fall as part of AWS's QuickSight BI offering... "SPICE: One of the key ingredients that make QuickSight so powerful is the Super-fast, Parallel, In-memory Calculation Engine (SPICE). SPICE is a new technology built from the ground up by the same team that has also built technologies such as DynamoDB, Amazon Redshift, and Amazon Aurora. SPICE enables QuickSight to scale to many terabytes of analytical data and deliver response time for most visualization queries in milliseconds. When you point QuickSight to a data source, data is automatically ingested into SPICE for optimal analytical query performance. SPICE uses a combination of columnar storage, in-memory technologies enabled through the latest hardware innovations, machine code generation, and data compression to allow users to run interactive queries on large datasets and get rapid responses." http://www.allthingsdistributed.com/2015/10/amazon-quicksight.html http://www.allthingsdistributed.com/2015/10/amazon-quicksigh...
  - sharpy 11y ago
lomnakkus 11y ago
The really fun aspect of this for me, personally, is that I've been doing computers for ages (25 yrs or so?) now and 3TiB still intuitively feels like a massive amount of data even though I think I have something like 6TiB free space on my home server disks... which, in total, didn't even cost as much as a months' grocery shopping. Sometimes it really takes effort to rid yourself of these old intuitions that don't really work any more. (I'm getting better at it!) EDIT: And my desktop machine has -- let's see -- about 500000 times the RAM my first computer had. Truly astounding if you think about it.
- 7952 11y ago
  I can remember going through this with a 10MB file about 15 year ago. It felt like a lot after growing up with floppy disks. But even a modest CPU could iterate over it quickly, I just didn't realise. I just assumed I would need to process it in a database!
  - lomnakkus 11y ago
    Exactly! On a somewhat related note: The original Another World[1] would probably fit into the caches that your CPU has as a matter of course these days. [1] https://www.youtube.com/watch?v=Zgkf6wooDmw https://www.youtube.com/watch?v=Zgkf6wooDmw
    - aidenn0 11y ago
      My first hard drive was only 2MB larger than the caches on my CPU.
chubot 11y ago
That's awesome... I'm working with some 15-20 GB sqlite databases. Though 2 TB sounds kind of big? Was it 2 TB before compression? Because sqlite does blow up the data size over the raw data usually (depending on the original format obviously). It can be kind of wasteful, and I ended up storing some fields as compressed JSON for this reason (that actually beats the sqlite format). Also, the sqlite insert speed can be much slower than the disk's sequential write speed (even if you make sure you're not committing/flushing on every row, and if you have no indices to update, etc.) So I think inserting and laying on the data could be nontrivial. But the queries should be fast as long as they are indexed. In theory, sqlite queries should be slower for a lot of use cases because it is row-oriented, but in practice distributed systems usually add 10x overhead themselves anyway...
Buttons840 11y ago
Me and a coworker were laughing at the parent comment, and I told him: "I guarantee that somewhere, sometime, an engineer has been like 'hay guys, I loaded our big data into SQLite on my laptop and it ended up being faster than our fancy cluster'". We then joked that the engineer would be fired a few weeks later for not being a "cultural fit". A few minutes later you commented with your story. I hope you didn't get fired? :)
- yeukhon 11y ago
  There was a HN story in which the author had the same "big data claim" (it was his intention to show there is very little big data in the real world, for most people out there) but instead he only needed to process couple GBs. He just used standard Unix commands and the performance was proven to be incredibly awesome. Here is an example for SQLite: https://news.ycombinator.com/item?id=9359568 https://news.ycombinator.com/item?id=9359568
  - amake 11y ago
    I think you're talking about this: http://aadrake.com/command-line-tools-can-be-235x-faster-than-your-hadoop-cluster.html http://aadrake.com/command-line-tools-can-be-235x-faster-tha... I quite enjoyed it as well.
    - yeukhon 11y ago
      Wow thanks! I couldn't find it in search :-) glad you found it.
    - sjclemmy 11y ago
      I used to work somewhere that did a lot of ETL and we used MS databases - mainly, ahem, Access. At the time I had no idea about *nix. I've often thought since how much easier and quicker it would be to solve lots of the processing jobs with this sort of thing, but never thought about implementation details. This is a great reminder that new is not always best and reminds me that a little knowledge is a dangerous thing!
    - 11y ago
banku_brougham 11y ago
thanks for the SQLite tip, I've been meaning to add it to my tool set. question: is SQLite incrementally helpful when I'm already comfortable with a local pgsql db to handle the use case you suggested? would SQLite be redundant for me in this case? question: between postgres and unix tools (sed, awk) is there reason to use SQLite?
- FraaJad 11y ago
  No. If you are already comfortable in PgSQL, there is no need to use SQLite. You will see PostgreSQL will come in handy once you get beyond the initial import stage.. PostgreSQL's type system will come to your aid . SQlite essentially treats everything as string, which can turn nasty when you get serious with your queries etc.,
- jjoonathan 11y ago
  Reasons to prefer sed/awk: you're in bash Reasons to prefer sqlite: it's easier to embed in an app, you want sort, join, split-apply-combine, scale, transactions, compression, etc. Reasons to prefer pgsql: sqlite's perf tools suck compared to pgsql (last time I got stuck anyway) and I'm sure there are lots of sql-isms that sqlite doesn't handle if that's your jam. EDIT: forgot everything-is-a-string in sqlite, just wanted to add that it has bit me before.
  - rhizome 11y ago
    don't forget bdb!
  - rspeer 11y ago
    Another reason to use Unix tools like sed and awk (and grep and sort and...) is that, if you just need to iterate over all your data and don't need a random-access index, they are really really fast.
pacala 11y ago
In all fairness, one can run Spark on a single machine. The key insight and that "single machine" has gotten pretty big these days, the bit shuffling technology may be secondary.
ap22213 11y ago
How long does it take you to load 2 TB into SQLite? How long do queries take? I believe you, but I'm in disbelief that it could be close to as efficient as throwing into ram. I mean, an EMR cluster takes like 5 minutes to spin up. Where do I learn how to do this? I've tried loading a TiB (one table one index) into SQLite on disk before, and it took forever. Granted this was a couple years ago, but I must be doing something fundamentally wrong. I want to try this out. I've got 6TiB here of uncompressed CSV, 32 GiB ram. Is this something I could start tonight and complete a few queries before bed? Actually, out of curiosity, I looked it up on the sqlite site. If I'm reading the docs correctly, with atomic sync turned off, I should expect 50,000 inserts per second. So, with my data set of 50B rows, I should expect to have it all loaded in ... Just 13 days. What am I missing?
- saosebastiao 11y ago
  There was a little bit of unfair comparison. I didn't have to load the data over a busy network connection, and I didn't have to decrypt the data once it was loaded (I had the benefit of a firewall and the raw data on a USB3 hard drive). I think there was a conversion from CSV to parquet on the cluster as well. And the engineer who set up the cluster was multitasking so I'm sure there was some latency issues just from that. But my analysis still only took a few hours (5? Maybe 6?). There are a handful of things that make a difference. First of all, don't use inserts, use the .import command. This alone is enough to saturate all the available write bandwidth on a 7200rpm drive. It is not transactional, so you don't have to worry about that...it bypasses the query engine entirely, really is more like a shell command that marshals data directly into the table's on disk representation. You can also disable journaling and increase page sizes for a tiny boost. Once imported into SQLite you get the benefit of binary representation which (for my use case) really cut down on the dataset size for the read queries. I only had a single join and it was against a dimensional table that fit in memory, so indexes were small and took insignificant time to build. One single table scan with some aggregation, and that was it.
  - ap22213 11y ago
    Good tips. I'll give it a try!
Trundle 11y ago
Hey, would you be willing to go a little in to your background/qualifications as well as maybe what your daily/weekly workload and goals look like? BI is a field I'm curious about because it sounds very interesting, very important, and as far as I can tell, with tons of surplus demand even for entry level. It makes me wonder if the actual day to day must be horrible for those things to be true.
x0x0 11y ago
I worked at a place that shall remain anonymous. An engineer later terminated for cause spent almost 2 months setting up a hadoop cluster with all the stuff that goes along with it to analyze some log data. When a senior engineer manager finally got incredibly tired of waiting for the results, said senior engineer manager wrote the desired analysis in an afternoon in ruby, and it ran in a couple hours mostly limited by the spindle speed on his laptop drive. The productivity factor of ruby or python in a single memory space vs hadoop is at least 10x.

RivieraKid 11y ago

What technologies / architecture does Amazon use for business intelligence? I've just done a business intelligence course so I'm interested how do more "technology-centered" companies approach BI, whether it's the same thing I learned in the course (put everything into an integrated relational database if I simplify a lot).

saosebastiao 11y ago
Lots and lots of SQL (a very good thing IMO). This can come in the form of Oracle, Redshift, or any of the commonly available RDS databases (probably not SQL Server though). This is augmented with a lot of Big Data stuff, which used to be pretty diverse (Pig, Hive, raw Hadoop, etc.) but is moving very quickly towards a Spark-centric platform. There is occasionally some commercial software like Tableau/Microstrategy. Apart from that, it is a whole lot of homegrown technology. As far as architecture is concerned, every team is different. At least on the retail side, we tend to center around a large data warehouse that is currently transitioning from oracle to redshift, and with daily log parsing ETL jobs from most services to populate tables in that data warehouse. Downstream of the data warehouse is fair game for pretty much anything.
- drited 11y ago
  Would you know why sql server generally isn't used?
  - deleted 11y ago
    [deleted]
  - sshykes 11y ago
    Probably because: * SQL Server is proprietary * SQL Server licenses are expensive * SQL Server runs only on Windows (or least used to?)
    - hunterjrj 11y ago
      I chuckled a bit because the GP mentioned Oracle. I'm sure you've heard this one: Customer: "How much does an Oracle database license cost?" Oracle Rep: "Well, how much do you have?"
      - true_religion 11y ago
        That's actually not a joke. Oracle liked to argue you should accept server licences as being a fraction of your budget, so when it went up you'd automatically pay them more.
    - avon 11y ago
      Amazon actually does allow SQL Server. The poster saying it probably didn't was influenced by the fact that Azure, Microsoft's own cloud solution is an AWS competitor. To your own points: SQL Server is as proprietary as Oracle SQL Server is cheaper than Oracle SQL Server is being ported to Linux in 2017 :)
      - newjersey 11y ago
        Amazon.com seems to strongly encourage the companies it acquires to use aws. Apparently, woot.com is fixing to rewrite the website in Java or something just so they do not have to use Windows. Apparently, it makes sense to them.

ignoramous 11y ago

Fellow amazonian here. We switched from a massively distributed datastore (not to be named) to rodb for storage and found 10x improvement, not to mention eliminating cost and other head-aches; kind of expected since rodb is an embedded db...

pneumatics 11y ago
What is rodb?
- sshykes 11y ago
  Good question. Is it this [0]? [0] http://researcher.watson.ibm.com/researcher/view_group.php?id=4165 http://researcher.watson.ibm.com/researcher/view_group.php?i...
- csears 11y ago
  I'm guessing he means a "Real-time Operational Database". This seems to be a generic term for system like a data warehouse that contains current data, instead of just historical data. If you are taking the output of a Spark flow and storing it in Postgres or MongoDB or HBase for applications to query, then those could be considered RODBs. Since this is Amazon, I suspect he is referring to SPICE (or their internal version), which was released last fall as part of AWS's QuickSight BI offering... "SPICE: One of the key ingredients that make QuickSight so powerful is the Super-fast, Parallel, In-memory Calculation Engine (SPICE). SPICE is a new technology built from the ground up by the same team that has also built technologies such as DynamoDB, Amazon Redshift, and Amazon Aurora. SPICE enables QuickSight to scale to many terabytes of analytical data and deliver response time for most visualization queries in milliseconds. When you point QuickSight to a data source, data is automatically ingested into SPICE for optimal analytical query performance. SPICE uses a combination of columnar storage, in-memory technologies enabled through the latest hardware innovations, machine code generation, and data compression to allow users to run interactive queries on large datasets and get rapid responses." http://www.allthingsdistributed.com/2015/10/amazon-quicksight.html http://www.allthingsdistributed.com/2015/10/amazon-quicksigh...
- sharpy 11y ago

lomnakkus 11y ago

The really fun aspect of this for me, personally, is that I've been doing computers for ages (25 yrs or so?) now and 3TiB still intuitively feels like a massive amount of data even though I think I have something like 6TiB free space on my home server disks... which, in total, didn't even cost as much as a months' grocery shopping. Sometimes it really takes effort to rid yourself of these old intuitions that don't really work any more. (I'm getting better at it!) EDIT: And my desktop machine has -- let's see -- about 500000 times the RAM my first computer had. Truly astounding if you think about it.

7952 11y ago
I can remember going through this with a 10MB file about 15 year ago. It felt like a lot after growing up with floppy disks. But even a modest CPU could iterate over it quickly, I just didn't realise. I just assumed I would need to process it in a database!
- lomnakkus 11y ago
  Exactly! On a somewhat related note: The original Another World[1] would probably fit into the caches that your CPU has as a matter of course these days. [1] https://www.youtube.com/watch?v=Zgkf6wooDmw https://www.youtube.com/watch?v=Zgkf6wooDmw
  - aidenn0 11y ago
    My first hard drive was only 2MB larger than the caches on my CPU.

chubot 11y ago

That's awesome... I'm working with some 15-20 GB sqlite databases. Though 2 TB sounds kind of big? Was it 2 TB before compression? Because sqlite does blow up the data size over the raw data usually (depending on the original format obviously). It can be kind of wasteful, and I ended up storing some fields as compressed JSON for this reason (that actually beats the sqlite format). Also, the sqlite insert speed can be much slower than the disk's sequential write speed (even if you make sure you're not committing/flushing on every row, and if you have no indices to update, etc.) So I think inserting and laying on the data could be nontrivial. But the queries should be fast as long as they are indexed. In theory, sqlite queries should be slower for a lot of use cases because it is row-oriented, but in practice distributed systems usually add 10x overhead themselves anyway...

Buttons840 11y ago

Me and a coworker were laughing at the parent comment, and I told him: "I guarantee that somewhere, sometime, an engineer has been like 'hay guys, I loaded our big data into SQLite on my laptop and it ended up being faster than our fancy cluster'". We then joked that the engineer would be fired a few weeks later for not being a "cultural fit". A few minutes later you commented with your story. I hope you didn't get fired? :)

yeukhon 11y ago
There was a HN story in which the author had the same "big data claim" (it was his intention to show there is very little big data in the real world, for most people out there) but instead he only needed to process couple GBs. He just used standard Unix commands and the performance was proven to be incredibly awesome. Here is an example for SQLite: https://news.ycombinator.com/item?id=9359568 https://news.ycombinator.com/item?id=9359568
- amake 11y ago
  I think you're talking about this: http://aadrake.com/command-line-tools-can-be-235x-faster-than-your-hadoop-cluster.html http://aadrake.com/command-line-tools-can-be-235x-faster-tha... I quite enjoyed it as well.
  - yeukhon 11y ago
    Wow thanks! I couldn't find it in search :-) glad you found it.
  - sjclemmy 11y ago
    I used to work somewhere that did a lot of ETL and we used MS databases - mainly, ahem, Access. At the time I had no idea about *nix. I've often thought since how much easier and quicker it would be to solve lots of the processing jobs with this sort of thing, but never thought about implementation details. This is a great reminder that new is not always best and reminds me that a little knowledge is a dangerous thing!
  - 11y ago

banku_brougham 11y ago

thanks for the SQLite tip, I've been meaning to add it to my tool set. question: is SQLite incrementally helpful when I'm already comfortable with a local pgsql db to handle the use case you suggested? would SQLite be redundant for me in this case? question: between postgres and unix tools (sed, awk) is there reason to use SQLite?

FraaJad 11y ago
No. If you are already comfortable in PgSQL, there is no need to use SQLite. You will see PostgreSQL will come in handy once you get beyond the initial import stage.. PostgreSQL's type system will come to your aid . SQlite essentially treats everything as string, which can turn nasty when you get serious with your queries etc.,
jjoonathan 11y ago
Reasons to prefer sed/awk: you're in bash Reasons to prefer sqlite: it's easier to embed in an app, you want sort, join, split-apply-combine, scale, transactions, compression, etc. Reasons to prefer pgsql: sqlite's perf tools suck compared to pgsql (last time I got stuck anyway) and I'm sure there are lots of sql-isms that sqlite doesn't handle if that's your jam. EDIT: forgot everything-is-a-string in sqlite, just wanted to add that it has bit me before.
- rhizome 11y ago
  don't forget bdb!
- rspeer 11y ago
  Another reason to use Unix tools like sed and awk (and grep and sort and...) is that, if you just need to iterate over all your data and don't need a random-access index, they are really really fast.

pacala 11y ago

In all fairness, one can run Spark on a single machine. The key insight and that "single machine" has gotten pretty big these days, the bit shuffling technology may be secondary.

ap22213 11y ago

How long does it take you to load 2 TB into SQLite? How long do queries take? I believe you, but I'm in disbelief that it could be close to as efficient as throwing into ram. I mean, an EMR cluster takes like 5 minutes to spin up. Where do I learn how to do this? I've tried loading a TiB (one table one index) into SQLite on disk before, and it took forever. Granted this was a couple years ago, but I must be doing something fundamentally wrong. I want to try this out. I've got 6TiB here of uncompressed CSV, 32 GiB ram. Is this something I could start tonight and complete a few queries before bed? Actually, out of curiosity, I looked it up on the sqlite site. If I'm reading the docs correctly, with atomic sync turned off, I should expect 50,000 inserts per second. So, with my data set of 50B rows, I should expect to have it all loaded in ... Just 13 days. What am I missing?

saosebastiao 11y ago
There was a little bit of unfair comparison. I didn't have to load the data over a busy network connection, and I didn't have to decrypt the data once it was loaded (I had the benefit of a firewall and the raw data on a USB3 hard drive). I think there was a conversion from CSV to parquet on the cluster as well. And the engineer who set up the cluster was multitasking so I'm sure there was some latency issues just from that. But my analysis still only took a few hours (5? Maybe 6?). There are a handful of things that make a difference. First of all, don't use inserts, use the .import command. This alone is enough to saturate all the available write bandwidth on a 7200rpm drive. It is not transactional, so you don't have to worry about that...it bypasses the query engine entirely, really is more like a shell command that marshals data directly into the table's on disk representation. You can also disable journaling and increase page sizes for a tiny boost. Once imported into SQLite you get the benefit of binary representation which (for my use case) really cut down on the dataset size for the read queries. I only had a single join and it was against a dimensional table that fit in memory, so indexes were small and took insignificant time to build. One single table scan with some aggregation, and that was it.
- ap22213 11y ago
  Good tips. I'll give it a try!

Trundle 11y ago

Hey, would you be willing to go a little in to your background/qualifications as well as maybe what your daily/weekly workload and goals look like? BI is a field I'm curious about because it sounds very interesting, very important, and as far as I can tell, with tons of surplus demand even for entry level. It makes me wonder if the actual day to day must be horrible for those things to be true.

x0x0 11y ago

I worked at a place that shall remain anonymous. An engineer later terminated for cause spent almost 2 months setting up a hadoop cluster with all the stuff that goes along with it to analyze some log data. When a senior engineer manager finally got incredibly tired of waiting for the results, said senior engineer manager wrote the desired analysis in an afternoon in ruby, and it ran in a couple hours mostly limited by the spindle speed on his laptop drive. The productivity factor of ruby or python in a single memory space vs hadoop is at least 10x.