Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Giving meaning to 100B analytics events a day with Kafka, Dataflow and BigQuery

medium.com

Post reply on HN
Print view View original

31–40 of 70 posts

Re: Giving meaning to 100B analytics events a day with Kafka, Dataflow and BigQuery

#31

Post by djhworld »

buremba wrote: post #11

Sounds like they over-engineered the solution. If you have ad-hoc use-case, BigQuery is great but it's quite expensive. If you just need to pre-calculate the metrics using SQL, Athena / Prestodb / Clickhouse / Redshift Spectrum might be much easier and cost-efficient.

The downside to Athena/PrestoDB is you need to get the data into an optimal format/file size to get the best performance (e.g. Parquet or other columnar format)

This is fine for batch workloads where 'real time', i.e. latency of but adds complication if you need to make data available quickly, because you either adopt some hybrid approach where you have 'recent' data in database and everything else in S3 - meaning your query layer has added complexity.

I believe this is how BigQuery works with streaming inserts, where recent streamed data is actually stored in BigTable, and asynchronously copied into Capacitor over time (this might be outdated information)

So while BigQuery seems expensive, the other solutions have a lot of other costs that you need to factor in!

Re: Giving meaning to 100B analytics events a day with Kafka, Dataflow and BigQuery

#32

Post by buremba »

throwaway66666 wrote: post #20

You 'd be surprised how good modern hardware is. We are being hit with between 1.5 and 2 million requests per minute (steady traffic no spikes except increased usage in the weekends), and our analytics solution runs on just 2 main servers and costs $3k per month (total associated costs except human labour which is 1 engineer working on it part time now). We talked with a famous analytics company and they gave us a qu…

If you're pre-calculating the metrics and dropping the raw data from your systems, you can't actually get the benefits of the ad-hoc systems. You can't ask a new question to your existing analytics data and you need to do custom development every time you need to see a new metric.

Re: Giving meaning to 100B analytics events a day with Kafka, Dataflow and BigQuery

#33

Post by buremba »

vgt wrote: post #16
buremba wrote: post #11

Sounds like they over-engineered the solution. If you have ad-hoc use-case, BigQuery is great but it's quite expensive. If you just need to pre-calculate the metrics using SQL, Athena / Prestodb / Clickhouse / Redshift Spectrum might be much easier and cost-efficient.

BigQuery PM here. I'd love to genuinely understand why you have that impression. BigQuery's on-demand model charges you EXACTLY for what you consume. Meaning, your resource efficiency is 100% [0]. By contrast, typical "cluster pricing" technologies require you to pay for 100% of your cluster uptime. In private data centers, it's difficult to get above 30% average efficiency. BigQuery also takes care of all software,…

BigQuery charges you for the storage so it's not just the queries. It also charges for the streaming inserts which is also not that cheap for large volumes of data.

I might be biased since I'm running an analytics company and this is my core business but here's our alternative:

The query and storage layer should be separated because often the expensive one the is the query layer. We have API servers which ingest the data from SDKs, enrich/sanitize and send it to a commit log such as Kinesis and Kafka. One API server is capable of handling 10k r/q.

We have the Kinesis/Kafka consumers that consume the data in micro-batches, convert them to ORC files, store it on S3 and index the metadata in a single Mysql server. The throughput is around 15k r/q.

S3 or Cloud Storage is cheap, reliable and can be used for many other use-cases as well but you need high-memory nodes for the query layer if you're dealing large volumes of data.

We have a Presto cluster which spins up when you need to run ad-hoc queries, allows you to pre-materialize the data so that you can power your dashboards. If you're running expensive queries on raw data, you can spin up 10 Presto worker, execute the queries and then just shut down them when you don't need.

That way, we're able to access all the historical data, no extra cost for the data ingestion and storage and it's even better than serverless since it will be much cheaper. The system can be automatic based on the hour of the day (during your analysts working hours) the CPU and memory load so unfortunately "BigQuery's on-demand model" is not a killing feature anymore.

Re: Giving meaning to 100B analytics events a day with Kafka, Dataflow and BigQuery

#34

Post by scapecast »

vgt wrote: post #23
slap_shot wrote: post #21

Earlier quoted context omitted.

The answer comes down to one line in your BigQuery Under the Hood article[0]: "The answer is very simple — BigQuery has this much hardware (and much much more) available to devote to your queries for seconds at a time. BigQuery is powered by multiple data centers, each with hundreds of thousands of cores, dozens of petabytes in storage capacity, and terabytes in networking bandwidth. The numbers above — 300 disks, 30…

Genuinely thanks for the info, this is super insightful. I'll argue that BigQuery's per-query pricing charges you JUST for the resources you use (well, data scaned), so it SHOULD be far less expensive than a model that charges you for the luxury of having a cluster sit idle (and often at only 30% utilization), correct? Can you help me unpack this further? I think pay-per-query is ultra-efficient, but difficult to pre…

For background, our customers use Redshift, and we are also a heavy Redshift user for our own back-end. The ones that have done a bake-off with BigQuery (and Snowflake too, btw) come all back with the conclusion "too expensive". And if you're the BigQuery PM, I'd love to talk to you because we're planning to build for BigQuery what we've built for Amazon Redshift. lars at intermix dot io

I think the difference here is in the use case, and I think it boils down to "ETL" vs. "ELT". If you do ETL, and your analyst team runs a few ad-hoc queries, then the pay-per-query approach makes a lot of sense. It would agree that it's "ultra-efficient".

If you do "ELT", where you ingest fairly raw data on a continuous basis, and then run complex transformations within your data warehouse, then the "buy a cluster" pricing will win. The data loads (we've seen load frequency up to every 2 minutes) require resources, and then so do the transformations.

===================

side note:

When it comes to utilization, I always have to think of this great research paper by Andrew Odlyzko:

http://www.dtc.umn.edu/~odlyzko/doc/network.utilization.pdf

"Data networks are lightly utilized, and will stay that way"

===============================

Agreed that most warehouses sit at 30% utilization. The customers we work with have more utilization than 30% though. That's because they're continuously ingesting data, transforming it within Redshift, and then lots of ad-hoc queries by analyst teams and data services that feed other applications. If you have a business that runs on a two or more continents, then you don't even have downtime during US nighttime, as Europe, Asia, etc. are running. And so in those cases, we've seen that customers don't want any surprises and would rather pay for the cluster. Predictability becomes more important than paying the lowest amount possible per query.

---------

I'm a co-founder at https://www.intermix.io - we provide monitoring for data infrastructure

Re: Giving meaning to 100B analytics events a day with Kafka, Dataflow and BigQuery

#35

Post by manigandham »

xstartup wrote: post #3

Here is an example Pipeline which supports loading data from an unbounded source to BigQuery in batches using load jobs (evading BigQuery's Streaming Insert cost) See: https://zero-master.github.io/posts/pub-sub-bigquery-beam/

You can also skip pub/sub and/or use it to write files to cloud storage, then load from there by using a cloud function that will trigger the load job when a new object is created.

Re: Giving meaning to 100B analytics events a day with Kafka, Dataflow and BigQuery

#36

Post by mentat »

AWebOfBrown wrote: post #13
xstartup wrote: post #3

Here is an example Pipeline which supports loading data from an unbounded source to BigQuery in batches using load jobs (evading BigQuery's Streaming Insert cost) See: https://zero-master.github.io/posts/pub-sub-bigquery-beam/

Are you the author? OT but I'm amazed the author charged merely 100 euro for implementing that solution for the subject startup, even if they're cash-strapped. I'm not familiar with BigQuery, but I'm curious what a normal rate for solving that issue would look like.

That's shockingly cheap for the value.

Re: Giving meaning to 100B analytics events a day with Kafka, Dataflow and BigQuery

#37

Post by maxnevermind »

"In digital advertising, ..." Stopped reading right there. Maybe a great article though. I started working on that thing called BigData not that long time ago but now realized that 50% of the job is advertisement, not fan of it at all, I want to like the end product or at least be neutral about it.

Re: Giving meaning to 100B analytics events a day with Kafka, Dataflow and BigQuery

#38

Post by hinkley »

manigandham wrote: post #27
hinkley wrote: post #26

Earlier quoted context omitted.

A hundred billion events a day isn’t “knowing how people use your website”. It’s mass scale surveillance. It’s stalking your customers.

Is there some magic scale threshold?

Are you asking permission for misbehaving by trying to determine exactly how much is too much?

Is there some magic scale threshold for excessive force? No. There is no fine line because you shouldn't be anywhere near the line.

100 billion. It's not 'lots' or millions, or a billion we're talking about here. It's one hundred billion. That's 80 events per day on average for every human they've ever seen (1.2 billion, in the about section). If they see half of those on any given day that's 160 events per person, maybe 200. Per day.

Fine grained tracking you do on your own site to determine why people leave and whether they see your new content? I could see my boss asking for that. I wouldn't be enthusiastic. I might make excuses that I was too busy doing other things to help. I might even complain, but we probably aren't running that forever anyway because it tells you less and less over time but still costs the same.

But this is an ad network, not a usability study. We are currently busy handwringing about ad networks, and I'm a little taken aback by the dissonance here.

Re: Giving meaning to 100B analytics events a day with Kafka, Dataflow and BigQuery

#39

Post by jwilliams »

I've been working with all these tools for a while now (and a bunch more along the way).

It's not the orthodox cloud-thinking, but you're often best off processing at the point of creation (or ingestion). Normalize the data as much as you can, (probably) compress[1], and send as close to the target as possible.

If you grab the data, send elsewhere, transform ... it all gets slow and expensive pretty quickly. Also a headache to manage failures.

This is especially true if you're using BigQuery. Stage your near-raw data into BigQuery and then use it's muscle as much as you can. A classic example here might be de-duplicating data. A painful prospect for many distributed systems, but pretty easy on the BigQuery side.

This is all especially true for time-series data. With BigQuery time partitions you can keep the queries fast (and the costs reasonable).

Also limits the range of technologies and languages you need to wrangle too.

1: Choosing your data format and compression approach can make a huge difference.

Re: Giving meaning to 100B analytics events a day with Kafka, Dataflow and BigQuery

#40

Post by manigandham »

vgt wrote: post #16
buremba wrote: post #11

Sounds like they over-engineered the solution. If you have ad-hoc use-case, BigQuery is great but it's quite expensive. If you just need to pre-calculate the metrics using SQL, Athena / Prestodb / Clickhouse / Redshift Spectrum might be much easier and cost-efficient.

BigQuery PM here. I'd love to genuinely understand why you have that impression. BigQuery's on-demand model charges you EXACTLY for what you consume. Meaning, your resource efficiency is 100% [0]. By contrast, typical "cluster pricing" technologies require you to pay for 100% of your cluster uptime. In private data centers, it's difficult to get above 30% average efficiency. BigQuery also takes care of all software,…

I think that's the issue, sure some companies will overprovision but as you get closer to 100% utilization, BigQuery quickly becomes extremely expensive compared to the other options, unless you're able to afford the 40k/month fee to get to flat-rate.

Also Snowflake Data is another option that supports automatic provisioning and pausing resources which is even easier to manage than redshift. Changing bigquery pricing to be compressed data stored and scanned would go a long way towards making it more attractive for full-time usage.

Post reply on HN