5 ms·

I've been struggeling with a tough parquet problem for a few months now. I have a 15gb parquet file in a s3 bucket and I need to "unzip" and extract every row

I've been struggeling with a tough parquet problem for a few months now. I have a 15gb parquet file in a s3 bucket and I need to "unzip" and extract every row from the file to write into my database. The contents of the file are emails and I need to integrate them into our search function. Is this possible to do without an unreasonable amount of RAM? Are there any affordable services that can help here? Feel free to contact me (email in bio), happy to pay for a consult at the minimum.

martinky24 2y ago
Wouldn't partial reads fix the RAM problem? e.g. something like this: https://stackoverflow.com/a/69888274 https://stackoverflow.com/a/69888274 It might not be fast, but a quick 1-off solution that you let run for a while would probably do that job. There shouldn't be a need to load the whole file into memory.
memset 2y ago
I can help! Emailing you now :) Our company (scratchdata.com, open source) is literally built to solve the problem of schlepping large amounts of data between sources and destinations, so I have worked on this problem a lot personally and happy to nerd out about what works.
- fock 2y ago
  I - by my HPC-background - am wondering quite a bit what happened that 15GB-files are considered large data? Not being a crazy parquet-user, but: - does this decompress to giant sizes? - can't you split the file easily, because it includes row-based segments? - why does it take months to solve this for one file?
  - semi-extrinsic 2y ago
    As a fellow HPC user, I tried a couple of years ago to do a tricky data format conversion using these newfangled tools. I was essentially just taking a huge (multi-terabyte) 3D dataset, transposing it and changing the endianness. The solutions I was able to put together using Dask and Spark and such were all insanely slow, they just got killed by Slurm without getting anywhere. In the end I went back to good ole' shell scripting with xxd to handle most of the heavy lifting. Finished in under an hour. The appeal of these newfangled tools is that you can work with data sizes that are infeasible to people who only know Excel, yet you don't need to understand a single thing about how your data is actually stored. If you can be bothered to read the file format specification, open up some files in a hex editor to understand the layout, and write low-level code to parse the data - then you can achieve several orders of magnitude higher performance.
    - fifilura 2y ago
      I think command line tools is going to be fine if all you do is process one row at a time. Or if your data has a known order. But if you want to do some kind of grouping or for example pivoting rows to columns, I think you will still benefit from a distributed tool like Spark or Trino. That can do the map/reduce job for you in a distributed way.
  - jjtheblunt 2y ago
    Parquet is column oriented and so row-based manipulation can be inefficient
    - fock 2y ago
      > Hierarchically, a file consists of one or more row groups. https://parquet.apache.org/docs/concepts/ https://parquet.apache.org/docs/concepts/ Maybe the file in question only has one row group. Which would be weird, because the creator had to go out of their way to make it happen.
      - jjtheblunt 2y ago
        Yep. I use it all the time. But, as you said, depends on specific layouts, so can’t expect it to be row-convenient .
  - memset 2y ago
    Because most people don’t have an HPC background, aren’t familiar with parquet internals, don’t know how to make their language stream data instead of buffering it all in memory, have slow internet connections at home, are running out of disk space on their laptops, and only have 4 GB of ram to work with after Chrome and Slack take up the other 12 GB. 15 GB is a real drag to do anything with. So it’s a real pain when someone says “I’ll just give you 1 TB worth of parquet in S3”, the equivalent of dropping a billion dollars on someone’s doorstep in $1 bills.
    - vladsanchez 2y ago
      Funny analogy! I loved it. I'm ready to start with ScratchData which btw and respectfully never heard of. Thanks again for sharing your tool and insightful knowledge.
    - fifilura 2y ago
      How do you see the competition from Trino and Athena in your case? Depends a lot on what you want to do with the data of course, but if you want to filter and slice/dice it, my experience is that it is really fast and stable. And if you already have it on s3, the threshold for using it is extremely small.
    - fock 2y ago
      what is your point? They talked about 15GB of parquet - what does this have to do with 1TB of parquet? Also: How does the tool you sell here solve the problem - the data is already there and can't be processed (15GB - funny that seems to be the scale of YC startups?)? How does a tool to transfer the data into a new database help here?
      - wodenokoto 2y ago
        > How does a tool to transfer the data into a new database help here? Maybe because the problem literally is "how to transfer this data into a database"
dijksterhuis 2y ago
Try pyarrow.ParquetFile.iter_batches() Streams batches of rows https://arrow.apache.org/docs/python/generated/pyarrow.parquet.ParquetFile.html https://arrow.apache.org/docs/python/generated/pyarrow.parqu... Edit — May need to do some extra work with s3fs too from what I recall with the default pandas s3 reading Edit 2 — or check out pyarrow.fs.S3FileSystem :facepalm:
- calderwoodra 2y ago
  I've spent many many hours trying these suggestions, didn't have much luck. iter_batches loads the whole file (or some very large amount of it) into memory.
  - semi-extrinsic 2y ago
    It sounds like maybe your parquet file has no partitioning. Apart from the iterating over row groups like someone else suggested, I suspect there is no better solution than downloading the whole thing to your computer, partitioning it in a sane way, and uploading it again. It's only 15 GB so it should be fine even on an old laptop. Of course then you might as well do all the processing you're interested in while the file is on your local disk, since it is probably much faster than the cloud service disk.
    - okr 2y ago
      What do you mean by the parquet file might have no partitioning? Is the row group size not the implicit partitioning?
wild_egg 2y ago
Have you given DuckDB a try? I'm using it to shuttle some hefty data between postgres and some parquet files on S3 and it's only a couple lines. Haven't noted any memory issues so far
- csjh 2y ago
  Agreed on DuckDB, fantastic for working with most major data formats
  - calderwoodra 2y ago
    Took your advice and tried DuckDB. Here's what I've got so far: ``` def _get_duck_db_arrow_results(s3_key): con = duckdb.connect(config={'threads': 1, 'memory_limit': '1GB'}) con.install_extension("aws") con.install_extension("httpfs") con.load_extension("aws") con.load_extension("httpfs") con.sql("CALL load_aws_credentials('hadrius-dev', set_region=true);") con.sql("CREATE SECRET (TYPE S3,PROVIDER CREDENTIAL_CHAIN);") results = con \ .execute(f"SELECT * FROM read_parquet('{s3_key}');") \ .fetch_record_batch(1024) for index, result in enumerate(results): print(index) return results ``` I ran the above on a 1.4gb parquet file and 15 min later, all of the results were printed at once. This suggests to me that the whole file was loaded loaded into memory at once.
    - wild_egg 2y ago
      It's been a long time since I've used python but that sounds like buffering in the library maybe? I use it from Go and it seems to behave differently. When I'm writing to postgres though I'm doing into entirely inside DuckDB with a `INSERT INTO ... SELECT ...` and that seems to stream it over.
    - akdor1154 2y ago
      You asked it to fetch a batch (15min) then iterated over the batch (all at once). To stream, fetch more batches. What ddb does to get the batches depends on hand wavey magic around available ram, and also the structure of the parquet.
lonesword 2y ago
I work with pyspark and parquet quite a lot. I never had to deal with parquet outside spark, but this is how I would do this: - Write a pandas_udf function in pyspark. - Parition your data into smaller bits so that the pandas_udf does not get too much data at the same time. Something like: ``` from pyspark.sql import SparkSession import pyspark.sql.functions as f @f.pandas_udf(return_type=whatever) def ingest(doc: pd.Series): # doc is a pandas series now # your processing goes here -> write to DB e.t.c pd_series_literal = Create a pd.Series that just contains the integer 0 to make spark happy return pd_series_literal spark = SparkSession.builder.getOrCreate() df = spark.read.parquet("s3 path") df = df.repartition(1000). # bump up this number if you run into memory issues df = df.withColumn("foo", ingest(f.col("doc_column")) ``` Now the trick is, you can limit how much data is given to your pandas_udf by repartitioning your data. The more the partitions, the smaller the pd.Series that your pandas_udf gets. There's also the `spark.sql.execution.arrow.maxRecordsPerBatch` config that you can set in spark to limit memory consumption. ^ Probably overkill to bring spark into the equation, but this is one way to do it. You can use a normal udf (i.e `f.udf()`) instead of a pandas_udf, but apparently that's slower due to java <-> python serialization
- fifilura 2y ago
  Pyspark is probably the way to go. I just wanted to mention that AWS Athena eats 15G parquet files for breakfast. It is trivial to map the file into Athena. But you can't connect it to anything else than file output. But it can help you to for example write it to smaller chunks. Or choose another output format such as csv (although arbitrary email content in a csv feels like you are set up for parsing errors). The benefit is that there is virtually no setup cost. And processing cost for a 15G file will be just a few cents.
  - calderwoodra 2y ago
    Athena is probably my best bet tbh, especially if I can do a few clicks and just get smaller files. Processing smaller files is a no brainer / pretty easy and could be outsourced to lambda.
cycrutchfield 2y ago
My suggestion is to load each row group individually, as they generally will be much smaller than your total file size. You can do this via pyarrow.ParquetFile.read_row_group. To truly optimize this for reading from s3 you could use fsspec’s open_parquet_file library which would allow you to only load each row group one at a time.
jfim 2y ago
Spend a few bucks on an EC2 instance with a few terabytes of RAM for an hour or so. u-3tb1.56xlarge is about $27/hr.
- nucleardog 2y ago
  This is the answer for a one-off or occasional problem unless your time is worthless. $200 to rent a machine that can run the naive solution for an entire day is peanuts compared to the dev time for a “better” solution. Running that machine for eight hours would only cost enough to purchase about a half day of junior engineer time.
arrowleaf 2y ago
Using polars in Python I've gotten similar to work, using LazyFrame and collect in streaming mode: ``` df = pl.scan_parquet('tmp/'+DUMP_NAME+'_cleaned.parquet') with open('tmp/'+DUMP_NAME+'_cleaned.jsonl', mode='w', newline='\n', encoding='utf8') as f: for row in df.collect(streaming=True).iter_rows(named=True): row = {k: v for k, v in row.items() if (v is not None and v != [] and v != '')} f.write(json.dumps(row, default=str) + '\n') ```
- akdor1154 2y ago
  This collects all into memory, then iterates.
chrisjc 2y ago
DuckDB? https://duckdb.org/2024/03/29/external-aggregation.html https://duckdb.org/2024/03/29/external-aggregation.html https://duckdb.org/2021/06/25/querying-parquet.html https://duckdb.org/2021/06/25/querying-parquet.html If your DB is mysql or postgres, then you could read a stream from parquet, transform inline and write out to your DB https://duckdb.org/2024/01/26/multi-database-support-in-duckdb.html https://duckdb.org/2024/01/26/multi-database-support-in-duck... And an unrelated, but interesting read about the parquet bomb https://duckdb.org/2024/03/26/42-parquet-a-zip-bomb-for-the-big-data-age.html https://duckdb.org/2024/03/26/42-parquet-a-zip-bomb-for-the-...
- calderwoodra 2y ago
  https://news.ycombinator.com/item?id=40301549 https://news.ycombinator.com/item?id=40301549
bluedemon 2y ago
Perhaps look into using dlt from https://dlthub.com https://dlthub.com, using pyarrow or polars. It handles large datasets well, especially when using generators to process the data in chunks.
orf 2y ago
Understand the format of your data. Look at the parquet file metadata: use whatever tool you want for that. The Python parquet library is useful and supports s3. How big are your row groups? If it’s one large row group then you will run into this issue. What’s the number of rows in each row group?
sagia 2y ago
I had similiar issue, but for aggreagations. Use case was to "compress" large datasets into smaller aggregations for insertion into a costly db. At first we used duckdb but memory became an issue there and we also bumped into a couple of issues with how duckdb handles arrays. We then moved this workload to clickhouse local, which was faster and had more fine tuning options to our liking. in this case was limiting ram usage with i.e. max_bytes_before_external_group_by
Hikikomori 2y ago
Should be able to do it with Aws Athena?
xk3 2y ago
You may want to give octosql a try. I was able to read 100GiB parquet files and aggregate them using less than 100MiB RAM and it is pretty fast too
392 2y ago
I'm puzzled as to why this is a problem that has lasted months. My phone has enough RAM to work with this file in memory. Do not use pyspark, it is unbelievably slow and memory hogging if you hold it even slightly wrong. Spark is for tb-sized data, at minimum. Have you tried downloading the file from s3 to /tmp, opening it with pandas, iterating through 1000 row chunks, pushing to DB? The default DF to SQL built into pandas doesn't batch the inserts so it will be about 10x slower than necessary, but speeding that up is a quick google->SO away.
katamaster818 2y ago
Maybe check out alteryx designer cloud, airbyte, or estuary?

martinky24 2y ago

Wouldn't partial reads fix the RAM problem? e.g. something like this: https://stackoverflow.com/a/69888274 https://stackoverflow.com/a/69888274 It might not be fast, but a quick 1-off solution that you let run for a while would probably do that job. There shouldn't be a need to load the whole file into memory.

memset 2y ago

I can help! Emailing you now :) Our company (scratchdata.com, open source) is literally built to solve the problem of schlepping large amounts of data between sources and destinations, so I have worked on this problem a lot personally and happy to nerd out about what works.

fock 2y ago
I - by my HPC-background - am wondering quite a bit what happened that 15GB-files are considered large data? Not being a crazy parquet-user, but: - does this decompress to giant sizes? - can't you split the file easily, because it includes row-based segments? - why does it take months to solve this for one file?
- semi-extrinsic 2y ago
  As a fellow HPC user, I tried a couple of years ago to do a tricky data format conversion using these newfangled tools. I was essentially just taking a huge (multi-terabyte) 3D dataset, transposing it and changing the endianness. The solutions I was able to put together using Dask and Spark and such were all insanely slow, they just got killed by Slurm without getting anywhere. In the end I went back to good ole' shell scripting with xxd to handle most of the heavy lifting. Finished in under an hour. The appeal of these newfangled tools is that you can work with data sizes that are infeasible to people who only know Excel, yet you don't need to understand a single thing about how your data is actually stored. If you can be bothered to read the file format specification, open up some files in a hex editor to understand the layout, and write low-level code to parse the data - then you can achieve several orders of magnitude higher performance.
  - fifilura 2y ago
    I think command line tools is going to be fine if all you do is process one row at a time. Or if your data has a known order. But if you want to do some kind of grouping or for example pivoting rows to columns, I think you will still benefit from a distributed tool like Spark or Trino. That can do the map/reduce job for you in a distributed way.
- jjtheblunt 2y ago
  Parquet is column oriented and so row-based manipulation can be inefficient
  - fock 2y ago
    > Hierarchically, a file consists of one or more row groups. https://parquet.apache.org/docs/concepts/ https://parquet.apache.org/docs/concepts/ Maybe the file in question only has one row group. Which would be weird, because the creator had to go out of their way to make it happen.
    - jjtheblunt 2y ago
      Yep. I use it all the time. But, as you said, depends on specific layouts, so can’t expect it to be row-convenient .
- memset 2y ago
  Because most people don’t have an HPC background, aren’t familiar with parquet internals, don’t know how to make their language stream data instead of buffering it all in memory, have slow internet connections at home, are running out of disk space on their laptops, and only have 4 GB of ram to work with after Chrome and Slack take up the other 12 GB. 15 GB is a real drag to do anything with. So it’s a real pain when someone says “I’ll just give you 1 TB worth of parquet in S3”, the equivalent of dropping a billion dollars on someone’s doorstep in $1 bills.
  - vladsanchez 2y ago
    Funny analogy! I loved it. I'm ready to start with ScratchData which btw and respectfully never heard of. Thanks again for sharing your tool and insightful knowledge.
  - fifilura 2y ago
    How do you see the competition from Trino and Athena in your case? Depends a lot on what you want to do with the data of course, but if you want to filter and slice/dice it, my experience is that it is really fast and stable. And if you already have it on s3, the threshold for using it is extremely small.
  - fock 2y ago
    what is your point? They talked about 15GB of parquet - what does this have to do with 1TB of parquet? Also: How does the tool you sell here solve the problem - the data is already there and can't be processed (15GB - funny that seems to be the scale of YC startups?)? How does a tool to transfer the data into a new database help here?
    - wodenokoto 2y ago
      > How does a tool to transfer the data into a new database help here? Maybe because the problem literally is "how to transfer this data into a database"

dijksterhuis 2y ago

Try pyarrow.ParquetFile.iter_batches() Streams batches of rows https://arrow.apache.org/docs/python/generated/pyarrow.parquet.ParquetFile.html https://arrow.apache.org/docs/python/generated/pyarrow.parqu... Edit — May need to do some extra work with s3fs too from what I recall with the default pandas s3 reading Edit 2 — or check out pyarrow.fs.S3FileSystem :facepalm:

calderwoodra 2y ago
I've spent many many hours trying these suggestions, didn't have much luck. iter_batches loads the whole file (or some very large amount of it) into memory.
- semi-extrinsic 2y ago
  It sounds like maybe your parquet file has no partitioning. Apart from the iterating over row groups like someone else suggested, I suspect there is no better solution than downloading the whole thing to your computer, partitioning it in a sane way, and uploading it again. It's only 15 GB so it should be fine even on an old laptop. Of course then you might as well do all the processing you're interested in while the file is on your local disk, since it is probably much faster than the cloud service disk.
  - okr 2y ago
    What do you mean by the parquet file might have no partitioning? Is the row group size not the implicit partitioning?

wild_egg 2y ago

Have you given DuckDB a try? I'm using it to shuttle some hefty data between postgres and some parquet files on S3 and it's only a couple lines. Haven't noted any memory issues so far

csjh 2y ago
Agreed on DuckDB, fantastic for working with most major data formats
- calderwoodra 2y ago
  Took your advice and tried DuckDB. Here's what I've got so far: ``` def _get_duck_db_arrow_results(s3_key): con = duckdb.connect(config={'threads': 1, 'memory_limit': '1GB'}) con.install_extension("aws") con.install_extension("httpfs") con.load_extension("aws") con.load_extension("httpfs") con.sql("CALL load_aws_credentials('hadrius-dev', set_region=true);") con.sql("CREATE SECRET (TYPE S3,PROVIDER CREDENTIAL_CHAIN);") results = con \ .execute(f"SELECT * FROM read_parquet('{s3_key}');") \ .fetch_record_batch(1024) for index, result in enumerate(results): print(index) return results ``` I ran the above on a 1.4gb parquet file and 15 min later, all of the results were printed at once. This suggests to me that the whole file was loaded loaded into memory at once.
  - wild_egg 2y ago
    It's been a long time since I've used python but that sounds like buffering in the library maybe? I use it from Go and it seems to behave differently. When I'm writing to postgres though I'm doing into entirely inside DuckDB with a `INSERT INTO ... SELECT ...` and that seems to stream it over.
  - akdor1154 2y ago
    You asked it to fetch a batch (15min) then iterated over the batch (all at once). To stream, fetch more batches. What ddb does to get the batches depends on hand wavey magic around available ram, and also the structure of the parquet.

lonesword 2y ago

I work with pyspark and parquet quite a lot. I never had to deal with parquet outside spark, but this is how I would do this: - Write a pandas_udf function in pyspark. - Parition your data into smaller bits so that the pandas_udf does not get too much data at the same time. Something like: ``` from pyspark.sql import SparkSession import pyspark.sql.functions as f @f.pandas_udf(return_type=whatever) def ingest(doc: pd.Series): # doc is a pandas series now # your processing goes here -> write to DB e.t.c pd_series_literal = Create a pd.Series that just contains the integer 0 to make spark happy return pd_series_literal spark = SparkSession.builder.getOrCreate() df = spark.read.parquet("s3 path") df = df.repartition(1000). # bump up this number if you run into memory issues df = df.withColumn("foo", ingest(f.col("doc_column")) ``` Now the trick is, you can limit how much data is given to your pandas_udf by repartitioning your data. The more the partitions, the smaller the pd.Series that your pandas_udf gets. There's also the `spark.sql.execution.arrow.maxRecordsPerBatch` config that you can set in spark to limit memory consumption. ^ Probably overkill to bring spark into the equation, but this is one way to do it. You can use a normal udf (i.e `f.udf()`) instead of a pandas_udf, but apparently that's slower due to java <-> python serialization

fifilura 2y ago
Pyspark is probably the way to go. I just wanted to mention that AWS Athena eats 15G parquet files for breakfast. It is trivial to map the file into Athena. But you can't connect it to anything else than file output. But it can help you to for example write it to smaller chunks. Or choose another output format such as csv (although arbitrary email content in a csv feels like you are set up for parsing errors). The benefit is that there is virtually no setup cost. And processing cost for a 15G file will be just a few cents.
- calderwoodra 2y ago
  Athena is probably my best bet tbh, especially if I can do a few clicks and just get smaller files. Processing smaller files is a no brainer / pretty easy and could be outsourced to lambda.

cycrutchfield 2y ago

My suggestion is to load each row group individually, as they generally will be much smaller than your total file size. You can do this via pyarrow.ParquetFile.read_row_group. To truly optimize this for reading from s3 you could use fsspec’s open_parquet_file library which would allow you to only load each row group one at a time.

jfim 2y ago

Spend a few bucks on an EC2 instance with a few terabytes of RAM for an hour or so. u-3tb1.56xlarge is about $27/hr.

nucleardog 2y ago
This is the answer for a one-off or occasional problem unless your time is worthless. $200 to rent a machine that can run the naive solution for an entire day is peanuts compared to the dev time for a “better” solution. Running that machine for eight hours would only cost enough to purchase about a half day of junior engineer time.

arrowleaf 2y ago

Using polars in Python I've gotten similar to work, using LazyFrame and collect in streaming mode: ``` df = pl.scan_parquet('tmp/'+DUMP_NAME+'_cleaned.parquet') with open('tmp/'+DUMP_NAME+'_cleaned.jsonl', mode='w', newline='\n', encoding='utf8') as f: for row in df.collect(streaming=True).iter_rows(named=True): row = {k: v for k, v in row.items() if (v is not None and v != [] and v != '')} f.write(json.dumps(row, default=str) + '\n') ```

akdor1154 2y ago
This collects all into memory, then iterates.

chrisjc 2y ago

DuckDB? https://duckdb.org/2024/03/29/external-aggregation.html https://duckdb.org/2024/03/29/external-aggregation.html https://duckdb.org/2021/06/25/querying-parquet.html https://duckdb.org/2021/06/25/querying-parquet.html If your DB is mysql or postgres, then you could read a stream from parquet, transform inline and write out to your DB https://duckdb.org/2024/01/26/multi-database-support-in-duckdb.html https://duckdb.org/2024/01/26/multi-database-support-in-duck... And an unrelated, but interesting read about the parquet bomb https://duckdb.org/2024/03/26/42-parquet-a-zip-bomb-for-the-big-data-age.html https://duckdb.org/2024/03/26/42-parquet-a-zip-bomb-for-the-...

calderwoodra 2y ago
https://news.ycombinator.com/item?id=40301549 https://news.ycombinator.com/item?id=40301549

bluedemon 2y ago

Perhaps look into using dlt from https://dlthub.com https://dlthub.com, using pyarrow or polars. It handles large datasets well, especially when using generators to process the data in chunks.

orf 2y ago

Understand the format of your data. Look at the parquet file metadata: use whatever tool you want for that. The Python parquet library is useful and supports s3. How big are your row groups? If it’s one large row group then you will run into this issue. What’s the number of rows in each row group?

sagia 2y ago

I had similiar issue, but for aggreagations. Use case was to "compress" large datasets into smaller aggregations for insertion into a costly db. At first we used duckdb but memory became an issue there and we also bumped into a couple of issues with how duckdb handles arrays. We then moved this workload to clickhouse local, which was faster and had more fine tuning options to our liking. in this case was limiting ram usage with i.e. max_bytes_before_external_group_by

Hikikomori 2y ago

Should be able to do it with Aws Athena?

xk3 2y ago

You may want to give octosql a try. I was able to read 100GiB parquet files and aggregate them using less than 100MiB RAM and it is pretty fast too

392 2y ago

I'm puzzled as to why this is a problem that has lasted months. My phone has enough RAM to work with this file in memory. Do not use pyspark, it is unbelievably slow and memory hogging if you hold it even slightly wrong. Spark is for tb-sized data, at minimum. Have you tried downloading the file from s3 to /tmp, opening it with pandas, iterating through 1000 row chunks, pushing to DB? The default DF to SQL built into pandas doesn't batch the inserts so it will be about 10x slower than necessary, but speeding that up is a quick google->SO away.

katamaster818 2y ago

Maybe check out alteryx designer cloud, airbyte, or estuary?