4 ms·

Tangential: Anyone here used both BigQuery and Redshift? Here's the primary reason that is currently keeping me on Redshift: Redshift is basically Postgres at

Tangential: Anyone here used both BigQuery and Redshift? Here's the primary reason that is currently keeping me on Redshift: Redshift is basically Postgres at the query layer which is insanely cool: All the Postgres tooling works with it. All the Postgres expertise I have carries over. It feels like a lot less of a "lock in". Anyone care to chime in?

elvinyung 9y ago
I don't know why more things aren't PostgreSQL protocol compatible. It seems like a relatively easy way to make a system accessible to a huge ecosystem. CockroachDB is the only other well-known project I know of that does this (but of course, it's not really an analytic database right now).
manigandham 9y ago
It’s still SQL at the end of the day so not a big deal, BigQuery has a newer Standard SQL dialect now too. It is nice to be able to reuse existing tooling for redshift but we’re willing to make the trade for bigquery since the no-ops model is so much nicer. It’s basically magical how well it works.
sturgill 9y ago
We dual write event data to BigQuery and Redshift. BigQuery is incredibly cheap at the storage level, but can be pricey to run queries against large datasets. Personally I am not a fan of paying per-query. Historically BigQuery data was immutable. You could delete a table, but you couldn't modify or delete a single row. That support was added recently, but there are daily limits imposed on those actions. I'm a big fan of immutable data, up until you find that someone made a mistake somewhere. Naturally you can make modifications in ETL, but if you do that long enough your ETL job is a mess of time-based if statements. We originally moved to also support Redshift because of BigQuery not supporting row level updates at that time. Someone on the content site would forget to put an & between URL params and our source data would be busted. Like I said, immutable data is great as long as you never make mistakes... I'm also not a huge fan of nested records. If you use BigQuery, do yourself a favor and make sure you have a uuid field (it's a good practice any way). When you get data back from the client it can be a pain to piece records back together (nested records come back as single rows for the most part). This makes certain queries really fast, but it can be a pain to work with in aggregate. My preferred setup these days is to dual write to Redshift and BigQuery. We run almost all analysis off of Redshift, but have a natural backup of data stored elsewhere. And like I said, BigQuery storage rates are dirt cheap. And if our Redshift cluster goes down for any reason, we can hop over to BigQuery to find what we need (and backfill any missing data points).
- scrollaway 9y ago
  Wait, what's the advantage of dual-writing? My understanding is that there's a lot of cases where BigQuery is significantly cheaper, but if you're paying for Redshift, there's really nothing Redshift can't do that BigQuery can.
  - sturgill 9y ago
    Redundancy across providers. BigQuery storage costs very little. It was implemented first and we introduced Redshift later to deal with data issues we didn't want to handle long-term in ETL. It didn't make sense to kill off the bigquery integration and I've decided for $20/mo or so that the backup storage on a separate provider is worth it. Redshift and BigQuery are both solid services. And BigQuery has gotten better with time. But I still have some reservations with it as a pure Redshift replacement.
    - scrollaway 9y ago
      Thanks for the details! Could I ask you about your query patterns and the kind of data loads you're pushing into those?
      - sturgill 9y ago
        Most of our use case is storing raw event data: user clicked on X; interacted with feature Y; was bucketed into test group Z; audit logs for subscription data points (joined, upgraded, downgraded, paid, payment denied, etc). Basically everything we care about, but that doesn't matter as a core concern of providing the service. The main app needs to know if a user has access to entitlement A, but the history of that process is outside the concern of the app itself. We throw everything into BQ/Redshift. Then when we find a need for it, we bring it into a separate reporting dataset in Redshift via ETL. The nice part about this is you can join your sanitized ETL data against raw event data if you need to run a once off query like "for everyone put into variant A of experiment B, how many converted from a free trial to a paid subscription?" I haven't found a great way to programmatically recreate ETL data into BQ without recreating a new dataset every day (because of the history of immutable data). It's not that it's impossible, it's just not as great (imho) as an upsert type workflow. Occasionally we'll have a once off query we access by hand, but that's relatively rare at this point. Jobs run around 1am and everything is ready to go when the team gets in in the morning. You have the odd job where you have to manage distkeys, and that's rarely fun, but it's a trade off we're comfortable making. And, while I'm pimping other people's services, Redshift + Looker is incredible.
nothrabannosir 9y ago
I have used both. My biggest aha moment with BQ was realising it isn’t “sql to query a database”, but “map reduce as a sql”. Redshift really feels like a DB: I’m always worrying about the number of the runtime complexity of my sql, the number of nodes in my fleet, how my joins work, it just feels like a big big DB. BQ is less like that. I’m thinking in terms of memory complexity of my operation : N, log N, etc. Always having as many nodes available as it can be parallelised to. You’re writing a map reduce job on your data, and it’s... it feels like the more appropriate boundary for abstraction. This is a bit hand wavy, but there is really no better way to get a feel for it than to try it. And think of it less as a db, and more as a map reduce cluster :) if that helps.
- barrkel 9y ago
  Your comment is a little bit perplexing to me because I don't really recognize much difference between composing relational operations and map / reduce operations. SQL `select` is map, `group by` is reduce, `where` is a flatMap to zero or one items, and `join` is a kind of specialized flatMap to zero or more items. You have to consider IO quantity and throughput (both disk and memory) to get decent performance either way. For example, if you're writing straight-up SQL, the fewer columns you project out before you start sorting the better off you are, it's better to join stuff after you've done your sort than the reverse because it means physically less data shuffling.
  - nothrabannosir 9y ago
    In redshift, I control the size of nodes. That means I can brute force my way through an expensive operation by increasing the memory capacity of the nodes. In BigQuery, you play by the rules or you’re out. It forces you to think in map reduce, because there really is nothing else under the hood. Also, you get immediate parallelisation across O(n) nodes. Again; not in redshift. These are crucially different to regular DBs. They’re both semantically sql, nobody denies that, but they describe different underlying models. E.g.: in BigQuery, you can’t sort your entire column, even if you do other stuff afterwards. That makes sense in a map reduce system, but not in a “normal” DB.
nerdponx 9y ago
Is Azure SQL Data Warehouse comparable to either of these? It's distributed on the backend, and more or less standard T-SQL on the front end.
djhworld 9y ago
If you rely on JDBC support, BQ has a JDBC driver but it's written by a third party
zerop 9y ago
Not sure if this relevant to what you want: Analyzing AWS Detailed Billing Reports Using BigQuery and ReDash https://blog.powerupcloud.com/analyzing-aws-detailed-billing-reports-using-bigquery-and-redash-e20a2d2130b8 https://blog.powerupcloud.com/analyzing-aws-detailed-billing...
IU84 9y ago
I guess - if you consider 30 year old legacy technologies insanely cool... Redshift is a shared-nothing architecture, so you'll always face scalability and concurrency issues. Snowflake is a brand new MPP shared-data architecture, and it can scale compute and storage separately. Blows Redshift away.

elvinyung 9y ago

I don't know why more things aren't PostgreSQL protocol compatible. It seems like a relatively easy way to make a system accessible to a huge ecosystem. CockroachDB is the only other well-known project I know of that does this (but of course, it's not really an analytic database right now).

manigandham 9y ago

It’s still SQL at the end of the day so not a big deal, BigQuery has a newer Standard SQL dialect now too. It is nice to be able to reuse existing tooling for redshift but we’re willing to make the trade for bigquery since the no-ops model is so much nicer. It’s basically magical how well it works.

sturgill 9y ago

We dual write event data to BigQuery and Redshift. BigQuery is incredibly cheap at the storage level, but can be pricey to run queries against large datasets. Personally I am not a fan of paying per-query. Historically BigQuery data was immutable. You could delete a table, but you couldn't modify or delete a single row. That support was added recently, but there are daily limits imposed on those actions. I'm a big fan of immutable data, up until you find that someone made a mistake somewhere. Naturally you can make modifications in ETL, but if you do that long enough your ETL job is a mess of time-based if statements. We originally moved to also support Redshift because of BigQuery not supporting row level updates at that time. Someone on the content site would forget to put an & between URL params and our source data would be busted. Like I said, immutable data is great as long as you never make mistakes... I'm also not a huge fan of nested records. If you use BigQuery, do yourself a favor and make sure you have a uuid field (it's a good practice any way). When you get data back from the client it can be a pain to piece records back together (nested records come back as single rows for the most part). This makes certain queries really fast, but it can be a pain to work with in aggregate. My preferred setup these days is to dual write to Redshift and BigQuery. We run almost all analysis off of Redshift, but have a natural backup of data stored elsewhere. And like I said, BigQuery storage rates are dirt cheap. And if our Redshift cluster goes down for any reason, we can hop over to BigQuery to find what we need (and backfill any missing data points).

scrollaway 9y ago
Wait, what's the advantage of dual-writing? My understanding is that there's a lot of cases where BigQuery is significantly cheaper, but if you're paying for Redshift, there's really nothing Redshift can't do that BigQuery can.
- sturgill 9y ago
  Redundancy across providers. BigQuery storage costs very little. It was implemented first and we introduced Redshift later to deal with data issues we didn't want to handle long-term in ETL. It didn't make sense to kill off the bigquery integration and I've decided for $20/mo or so that the backup storage on a separate provider is worth it. Redshift and BigQuery are both solid services. And BigQuery has gotten better with time. But I still have some reservations with it as a pure Redshift replacement.
  - scrollaway 9y ago
    Thanks for the details! Could I ask you about your query patterns and the kind of data loads you're pushing into those?
    - sturgill 9y ago
      Most of our use case is storing raw event data: user clicked on X; interacted with feature Y; was bucketed into test group Z; audit logs for subscription data points (joined, upgraded, downgraded, paid, payment denied, etc). Basically everything we care about, but that doesn't matter as a core concern of providing the service. The main app needs to know if a user has access to entitlement A, but the history of that process is outside the concern of the app itself. We throw everything into BQ/Redshift. Then when we find a need for it, we bring it into a separate reporting dataset in Redshift via ETL. The nice part about this is you can join your sanitized ETL data against raw event data if you need to run a once off query like "for everyone put into variant A of experiment B, how many converted from a free trial to a paid subscription?" I haven't found a great way to programmatically recreate ETL data into BQ without recreating a new dataset every day (because of the history of immutable data). It's not that it's impossible, it's just not as great (imho) as an upsert type workflow. Occasionally we'll have a once off query we access by hand, but that's relatively rare at this point. Jobs run around 1am and everything is ready to go when the team gets in in the morning. You have the odd job where you have to manage distkeys, and that's rarely fun, but it's a trade off we're comfortable making. And, while I'm pimping other people's services, Redshift + Looker is incredible.

nothrabannosir 9y ago

I have used both. My biggest aha moment with BQ was realising it isn’t “sql to query a database”, but “map reduce as a sql”. Redshift really feels like a DB: I’m always worrying about the number of the runtime complexity of my sql, the number of nodes in my fleet, how my joins work, it just feels like a big big DB. BQ is less like that. I’m thinking in terms of memory complexity of my operation : N, log N, etc. Always having as many nodes available as it can be parallelised to. You’re writing a map reduce job on your data, and it’s... it feels like the more appropriate boundary for abstraction. This is a bit hand wavy, but there is really no better way to get a feel for it than to try it. And think of it less as a db, and more as a map reduce cluster :) if that helps.

barrkel 9y ago
Your comment is a little bit perplexing to me because I don't really recognize much difference between composing relational operations and map / reduce operations. SQL `select` is map, `group by` is reduce, `where` is a flatMap to zero or one items, and `join` is a kind of specialized flatMap to zero or more items. You have to consider IO quantity and throughput (both disk and memory) to get decent performance either way. For example, if you're writing straight-up SQL, the fewer columns you project out before you start sorting the better off you are, it's better to join stuff after you've done your sort than the reverse because it means physically less data shuffling.
- nothrabannosir 9y ago
  In redshift, I control the size of nodes. That means I can brute force my way through an expensive operation by increasing the memory capacity of the nodes. In BigQuery, you play by the rules or you’re out. It forces you to think in map reduce, because there really is nothing else under the hood. Also, you get immediate parallelisation across O(n) nodes. Again; not in redshift. These are crucially different to regular DBs. They’re both semantically sql, nobody denies that, but they describe different underlying models. E.g.: in BigQuery, you can’t sort your entire column, even if you do other stuff afterwards. That makes sense in a map reduce system, but not in a “normal” DB.

nerdponx 9y ago

Is Azure SQL Data Warehouse comparable to either of these? It's distributed on the backend, and more or less standard T-SQL on the front end.

djhworld 9y ago

If you rely on JDBC support, BQ has a JDBC driver but it's written by a third party

zerop 9y ago

Not sure if this relevant to what you want: Analyzing AWS Detailed Billing Reports Using BigQuery and ReDash https://blog.powerupcloud.com/analyzing-aws-detailed-billing-reports-using-bigquery-and-redash-e20a2d2130b8 https://blog.powerupcloud.com/analyzing-aws-detailed-billing...

IU84 9y ago

I guess - if you consider 30 year old legacy technologies insanely cool... Redshift is a shared-nothing architecture, so you'll always face scalability and concurrency issues. Snowflake is a brand new MPP shared-data architecture, and it can scale compute and storage separately. Blows Redshift away.