4 ms·

Not a bad read, but it’s written from the perspective of large mature operations. If your company is just starting out, the advice is actually there but not qui

Not a bad read, but it’s written from the perspective of large mature operations. If your company is just starting out, the advice is actually there but not quite spelled out - use S3/GCS to store data (ideally in parquet format) and query it using Athena/bigquery.

meritt 7y ago
Fairly new to this topic and coming from a traditional RDBMS background. How do you go about deciding how many rows/records to store per object? And how does Athena/Bigquery know which objects to query? Do people use partitioning methods (e.g. by time or customer ID etc) to reduce the need to scan the entire corpus every time you run a query?
- dswalter 7y ago
  If you're using AWS athena for querying, you're also using the aws glue catalog (managed hive metastore-ish service) to know where partitions are, but yeah, you'll need to partition and sort your data to make sure you're not doing full table scans.
  - sologoub 7y ago
    Glue worked well for my previous gig, but honestly it felt like a bit of an overkill. If you have a large org and a lot of tribal knowledge + new fields showing up out of the blue, yes you need to organize and keep track. If you are a relatively small operation, I’d recommend weighing additional complexity over the benefits. Sometimes a few we’ll written pages can suffice, other times you need to make the investment.
- lmkg 7y ago
  From the Google side: In traditional BigQuery, the answer to all three questions are related. You shard the files by partition key and put the key into the file name. You can filter the file name in the WHERE clause, and the query will skip filtered objects, but otherwise fully scan every object it touches. There is apparently now experimental support for using Hive partitions natively. Never used it, literally found out two minutes ago. The number of records per object is usually "all of them" (restricted by partition keys). The main exception is live queries of compressed JSON or CSV data, because BigQuery can't parallelize them. But generally you trust the tool to handle workload distribution for you. This works a little differently if you load the data into BigQuery instead of doing queries against data that lives in Cloud Storage. You can use partitioning and clustering columns to cut down on full-table scans.
  - sologoub 7y ago
    That’s basically how GA export worked from my previous work - everything in a session is nested. Upshot is basically what’s above - easy to filter and you don’t get partial data. The catch is if you need to filter by a property of the session, you are opening every session in range to check if it’s the one you want. That gets expensive quickly and is a bit slow. For data lakes, parquet and Spark support fairly sane date partitioning. Partitioning by anything else is a question of whether you need it, such as a customer ID, etc. but remember this is a data lake, not a source table for your CEOs daily report. The purpose of the lake is to capture everything that you sanely can. When you can’t store everything, usually due to cost, you then have to aggregate and only keep the most valuable data. For example in AdTech, real-time bidding usually involves a single ad request, hundreds of bid requests, a few bid responses and the winning bid. Value here is inversely related to size - bid requests without responses are useful for predicting whether you should even ask next time, but the winning bid + the runner up tell you a lot about the value of the ad request. For structuring warehousing for reporting/ad hoc querying, to me the flatter the better - this uses the native capabilities of columnar stores and makes analysis a lot faster. Downside, good luck keeping everything consistent and up to date. Usually you end up just reprocessing everything each day/hour/whatever the need is, and at a certain point say no new updates to rows older than X. The cool thing about modern data warehouses, is that they include interfaces to talk to the data lakes, so your analysts don’t have to jump to different tool chains, such as Redshift Spectrum (which is basically Athena) and the aforementioned BigQuery ability to use tables, streams and files from GCP. It’s an incredibly productive time to be working with all this! Even 10 years ago, you’d need a lot of budget and a team to just keep the lights on, today it’s all compressed into these services and software.
- sologoub 7y ago
  To summarize the answers below - it all depends on what you are trying to do. Data lakes are generally less structured than other things. They can also contain non-text things, like images and videos that can also be mined. Sounds like you are thinking more of a data warehouse, which is structured data on an engine that’s designed for querying large volumes of data. I’d recommend first starting with your objectives and then going for what solves with least amount of “stuff”. I don’t work on data warehousing or pipelines now, but when I did a year ago, AWS and GCP both offered great tools with slight differences, where AWS was a bit pricier to start, but focused on more predictable pricing and GCP was much cheaper with pay as you go, but you could get yourself in trouble with cost by not following their best practices.
- FridgeSeal 7y ago
  First step is to figure out whether you actually need a datalake. I’d recommend starting off with an OLAP database and going from there, reaching for a datalake once-and only once-you’ve reached the limits of the OLAP db.
mattbillenstein 7y ago
Can you query parquet from bigquery without loading it into a table from gcs? I've gotten pretty far with jsonl on gcs and bigquery - even some bigquery streaming for more real-time stuff.
- lmkg 7y ago
  If the data is in Cloud Storage, BigQuery can query it in-place without loading it. BigQuery calls this an External Data Source. https://cloud.google.com/bigquery/external-data-sources https://cloud.google.com/bigquery/external-data-sources My biggest papercut with using this was having to make sure that all of the locations matched exactly.
dx034 7y ago
Importantly, there are also open source tools out there. Especially if you're starting out, locking into AWS or GCP can quickly become extremely expensive and limiting. Setting up a vendor independent data lake isn't that much more work and can pay off quickly.
- sologoub 7y ago
  This depends on the skill set available and the goal of the company. My previous employer tried the open source route, but then the normal things happened - people left, documentation was lacking, new people preferred other tools, then those new people left eventually. After a few years, it was a tangle of half-done implementations and no one there fully understood how these worked. Committing to rolling your own really does mean committing. Maintenance is not cheap, so paying for part of it with “vendor lock-in” could be practical for some. My comment was intended for those just starting out. If you don’t really know what you are doing yet with data, it best to focus on your core company objectives and not burn valuable engineering time on infra you can buy for now. Unless that data stack is your core business.

meritt 7y ago

Fairly new to this topic and coming from a traditional RDBMS background. How do you go about deciding how many rows/records to store per object? And how does Athena/Bigquery know which objects to query? Do people use partitioning methods (e.g. by time or customer ID etc) to reduce the need to scan the entire corpus every time you run a query?

dswalter 7y ago
If you're using AWS athena for querying, you're also using the aws glue catalog (managed hive metastore-ish service) to know where partitions are, but yeah, you'll need to partition and sort your data to make sure you're not doing full table scans.
- sologoub 7y ago
  Glue worked well for my previous gig, but honestly it felt like a bit of an overkill. If you have a large org and a lot of tribal knowledge + new fields showing up out of the blue, yes you need to organize and keep track. If you are a relatively small operation, I’d recommend weighing additional complexity over the benefits. Sometimes a few we’ll written pages can suffice, other times you need to make the investment.
lmkg 7y ago
From the Google side: In traditional BigQuery, the answer to all three questions are related. You shard the files by partition key and put the key into the file name. You can filter the file name in the WHERE clause, and the query will skip filtered objects, but otherwise fully scan every object it touches. There is apparently now experimental support for using Hive partitions natively. Never used it, literally found out two minutes ago. The number of records per object is usually "all of them" (restricted by partition keys). The main exception is live queries of compressed JSON or CSV data, because BigQuery can't parallelize them. But generally you trust the tool to handle workload distribution for you. This works a little differently if you load the data into BigQuery instead of doing queries against data that lives in Cloud Storage. You can use partitioning and clustering columns to cut down on full-table scans.
- sologoub 7y ago
  That’s basically how GA export worked from my previous work - everything in a session is nested. Upshot is basically what’s above - easy to filter and you don’t get partial data. The catch is if you need to filter by a property of the session, you are opening every session in range to check if it’s the one you want. That gets expensive quickly and is a bit slow. For data lakes, parquet and Spark support fairly sane date partitioning. Partitioning by anything else is a question of whether you need it, such as a customer ID, etc. but remember this is a data lake, not a source table for your CEOs daily report. The purpose of the lake is to capture everything that you sanely can. When you can’t store everything, usually due to cost, you then have to aggregate and only keep the most valuable data. For example in AdTech, real-time bidding usually involves a single ad request, hundreds of bid requests, a few bid responses and the winning bid. Value here is inversely related to size - bid requests without responses are useful for predicting whether you should even ask next time, but the winning bid + the runner up tell you a lot about the value of the ad request. For structuring warehousing for reporting/ad hoc querying, to me the flatter the better - this uses the native capabilities of columnar stores and makes analysis a lot faster. Downside, good luck keeping everything consistent and up to date. Usually you end up just reprocessing everything each day/hour/whatever the need is, and at a certain point say no new updates to rows older than X. The cool thing about modern data warehouses, is that they include interfaces to talk to the data lakes, so your analysts don’t have to jump to different tool chains, such as Redshift Spectrum (which is basically Athena) and the aforementioned BigQuery ability to use tables, streams and files from GCP. It’s an incredibly productive time to be working with all this! Even 10 years ago, you’d need a lot of budget and a team to just keep the lights on, today it’s all compressed into these services and software.
sologoub 7y ago
To summarize the answers below - it all depends on what you are trying to do. Data lakes are generally less structured than other things. They can also contain non-text things, like images and videos that can also be mined. Sounds like you are thinking more of a data warehouse, which is structured data on an engine that’s designed for querying large volumes of data. I’d recommend first starting with your objectives and then going for what solves with least amount of “stuff”. I don’t work on data warehousing or pipelines now, but when I did a year ago, AWS and GCP both offered great tools with slight differences, where AWS was a bit pricier to start, but focused on more predictable pricing and GCP was much cheaper with pay as you go, but you could get yourself in trouble with cost by not following their best practices.
FridgeSeal 7y ago
First step is to figure out whether you actually need a datalake. I’d recommend starting off with an OLAP database and going from there, reaching for a datalake once-and only once-you’ve reached the limits of the OLAP db.

mattbillenstein 7y ago

Can you query parquet from bigquery without loading it into a table from gcs? I've gotten pretty far with jsonl on gcs and bigquery - even some bigquery streaming for more real-time stuff.

lmkg 7y ago
If the data is in Cloud Storage, BigQuery can query it in-place without loading it. BigQuery calls this an External Data Source. https://cloud.google.com/bigquery/external-data-sources https://cloud.google.com/bigquery/external-data-sources My biggest papercut with using this was having to make sure that all of the locations matched exactly.

dx034 7y ago

Importantly, there are also open source tools out there. Especially if you're starting out, locking into AWS or GCP can quickly become extremely expensive and limiting. Setting up a vendor independent data lake isn't that much more work and can pay off quickly.

sologoub 7y ago
This depends on the skill set available and the goal of the company. My previous employer tried the open source route, but then the normal things happened - people left, documentation was lacking, new people preferred other tools, then those new people left eventually. After a few years, it was a tangle of half-done implementations and no one there fully understood how these worked. Committing to rolling your own really does mean committing. Maintenance is not cheap, so paying for part of it with “vendor lock-in” could be practical for some. My comment was intended for those just starting out. If you don’t really know what you are doing yet with data, it best to focus on your core company objectives and not burn valuable engineering time on infra you can buy for now. Unless that data stack is your core business.