15 ms·
Parquet: An efficient, binary file format for table data
- lysecret 4y agoOh very interesting didn’t expect so much scepticism on parquet. We use it to store around 100 tb of data in a partitioned way on GCS and query it using BigQuery. Absolutely fantastic solution and incredibly cheap too.
- stewbrew 4y agoParquet is cool. What isn't so cool is that, e g., partitioning isn't supported on all platforms out of the box. Hence, reading a set of files created in one language may require extra work in another language. Partitioning should be supported in all languages.
- Madeindjs 4y agoWhy should not use a simple database instead ?
- grandinteg3 4y agoWhy not use Delta? https://www.vldb.org/pvldb/vol13/p3411-armbrust.pdf https://www.vldb.org/pvldb/vol13/p3411-armbrust.pdf
- llwmg 4y ago[dead]
- domoritz 4y agoIf you need a quick tool to convert your CSV files, you can use csv2parquet from https://github.com/domoritz/arrow-tools https://github.com/domoritz/arrow-tools.
- 0cf8612b2e1e 4y agoI think this remains a usability problem for parquet: most of the tooling sucks. Starting to get better, but parquet has been out for so many years and there are still few tools that natively let someone interact with the format. For years, most of the utilities were exclusively JVM based. My favorite way to explore parquet is to make a virtual link through DuckDB where you can seamlessly treat it like a SQL table. Even browse it in DBeaver.
- simonw 4y agoI mostly ignored Parquet until I realized DuckDB could handle it - now I'm much more likely to poke around with a Parquet file because I've got a solid toolkit available in both the CLI and as a Python library. A couple of notes I've made about that: Using DuckDB in Python to access Parquet data: https://til.simonwillison.net/duckdb/parquet https://til.simonwillison.net/duckdb/parquet Use DuckDB to convert parquet to JSON and then open it in Datasette Lite: https://til.simonwillison.net/duckdb/parquet-to-json https://til.simonwillison.net/duckdb/parquet-to-json
- hackan 4y agoyou can also use pandas in Python to access parquet data, and then i.e. convert it to sqlite
- 0cf8612b2e1e 4y agoNaive pandas is going to limit you to RAM sizes when parquet is deliberately meant for huge datasets.
- FridgeSeal 4y agoAnyone reading who is currently using pandas and running into this issue-polars is generally faster, and now supports (still new though) out-of-core workloads.
- chatmasta 4y agoWe support exporting any query from Splitgraph to Parquet, try it here [0] (click the dice for a random query). You can also upload CSV or SQLite file(s) [1]. Each one will load into a table that you can query with a Postgres client, or export it (or just some subset of it you select with a query) as Parquet. Then you can load it into Seafowl [2]. We also have an API to export from Splitgraph to Seafowl, just need to add the button (this week). [0] https://www.splitgraph.com/query https://www.splitgraph.com/query [1] https://www.splitgraph.com/upload https://www.splitgraph.com/upload [2] https://seafowl.io/docs/guides/uploading-csv-parquet https://seafowl.io/docs/guides/uploading-csv-parquet
- crop_rotation 4y agoS3 Select supports parquet, which means you can push query to individual S3 blobs. I think there is a possibility we might see someone build a postgres foreign data wrapper which pushes the querying via S3 select to S3 blobs and use postgres only for the final aggregations. Someone on HN had done this with sqlite files in S3 queried by lambdas using sqlite, but pushing it wholly to S3 would eliminate a lot of overhead.
- chrisjc 4y agoEffectively a compute engine on top of S3 storage that is smart enough to push-down to S3Select where possible. Well the general idea of a compute engine on top of S3 storage where data is partitioned efficiently with the appropriate prefixing is quite common. Commonly known as an external table. Hive is an example that comes to mind. Couple that with a meta-index/catalogue and a modern file format like parquet and you've got yourself a more modern flavor of an external table. For instance Iceberg or Deltalake. I'm unsure whether the technologies/libraries built around interacting or managing these external table formats can actually push-down to S3Select, but I can't imagine why they can't.
- berkle4455 4y agoWhile people indeed love reinventing the wheel you can literally do that today with clickhouse or duckdb. select * from a parquet file wherever it resides.
- hackan 4y agowe are pretty much literally doing that :D
- crop_rotation 4y agoIt doesn't seem to be the same thing though. Like duckdb doesn't seem to use s3 select. The difference is that duckdb seems to be doing the filtering here. If you use s3 select s3 will give you result of the sql query. Would be good to know if any existing solution does it. > select * from a parquet file wherever it resides. I am sure any tool can do this. The point was to push the query to the source if you are issuing tons of them.
- antipaul 4y agoCan you `grep` it? Text-based files like CSV can be `grep`-ed en masse, which I do often. eg, to find some value across a ton of files. Is that possible with parquet?
- agiacalone 4y agoI feel like there’s some unsaid intrinsic value to having text-based files versus a compiled format like parquet. Size and speed aren’t nearly as critical when size is small, and this seems like adding unneeded complexity for all but some specialized use cases.
- rcoveson 4y ago"Unsaid"? I have the opposite perspective: This is repeated ad nauseam. Every time a binary format or any other complex optimization is mentioned, the performance-doesn't-matter people have to come out of the woodwork and bring up the fact that it "literally doesn't matter" to "$INVENTED_HIGH_PERCENTAGE of use cases". I wonder, do people on the Caterpillar forum have this problem, where people just show up to ask, "Yeah but can you take it on the freeway? Because I can take my Tacoma on the freeway."
- agiacalone 4y agoI’m confused. Are you advocating for complexity regardless of need?
- rcoveson 4y agoHow could anybody advocate for "complexity regardless of need"? What would that even look like? Maybe you were being sarcastic, I'm not sure. But I hope it's obvious that advocating for complexity at all is not the same thing as advocating for complexity regardless of need.
- FridgeSeal 4y agoIf you are under the impression that CSV’s-and-friends are “less complex” because they’re text, I would like to assure you that any “simplicity” in the existing text formats is a heinous lie, and the complexity gets pushed into the code of the consuming layer. I use parquet for small stuff now, because it’s standardised and so much nicer and more reliable to use. It’s faster. It ships a schema. There’s actual data type support. There’s even partitioning support if you need it. It’s quite literally better in every useful way. Long winded way of saying: the complexity has always been there. CSV’s act as if it’s not there, parquet acknowledges it and gives you the tools to deal with it.
- clord 4y ago“Parquet has both a date type and the datetime type (both sensibly recorded as integers in UTC).” What does it mean for a date to be utc? my date, but in the utc timezone? Usually when I write a date to a file, I want a naive date; since that’s the domain of the data. 2020-12-12 sales: $500. But splitting that by some other timezone seems to be introducing a mistake. Often I want to think in local naive time too, especially for things like appointments that might change depending on dst or whatever. Converting to utc involves some scary things. Timestamps are also useful but I don’t want to transcode my data sometimes as the natural format is the most correct.
- hodgesrm 4y agoFor anyone interested in the design approach to Parquet, here's a good interview with the Parquet designers. It covers a lot of the issues they were trying to address with the format. https://www.youtube.com/watch?v=qe0SeC0Hr_k https://www.youtube.com/watch?v=qe0SeC0Hr_k
- deleted 4y ago[deleted]
- the_af 4y agoWhat is a "naive" or "natural" date? A date in your current timezone? That's often a source of bugs if the tz is not explicit.
- anamexis 4y agoIt can be a source of bugs if the tz is explicit, as well. Consider storing people's birthdates. If you accidentally store it as UTC and use some implicit tz handling, suddenly you've got off-by-one birthdates. More succinctly, dates don't have timezones because they don't have times.
- radicality 4y agoI swear that some variant of this bug happened to me in gCal. I set myself some yearly recurring events, I think a birthday or a names-day, and clearly marked it as an “All Day” event and I set it to repeat Yearly. I don’t remember if it was in 1 or 2 or even more years, but Google calendar absolutely did make the event off-by-one day.
- kragen 4y agoi wonder why the words 'apache', 'twitter', 'cloudera', 'cutting', 'rcfile', 'orc', 'cutting', 'trevni', 'thrift', 'hadoop', 'julien', 'jco', 'tianshuo', and 'deng' don't appear in this page i mean i understand not mentioning all of them but it's pretty disappointing to not mention any of them
- xyzzy_plugh 4y agoShould they also mention Dremel? I could go on. Within the context of the article, mentioning any of these names is pretty obviously unwarranted. It sounds like you want a different article. Write it!
- kragen 4y agocrediting the creators of parquet is not unwarranted, no
- tomnipotent 4y agoWhy is this necessary every time someone wants to talk about Parquet? It's perfectly fine to talk about something without wasting the top third of your article recounting history like some recipe blog.
- kragen 4y agoyou seem to be intentionally strawmanning my position if you are going to write about parquet, say something like 'apache parquet' or 'parquet, originally written for hadoop' or 'parquet, originally by julien le dem' instead of making it sound like you invented it yourself this is only the top third of your article if the article is six words long like 'apache parquet, for free, never used'
- tomnipotent 4y agoHe links to the Wikipedia article. If someone is interested enough, they're welcome to do their own research. The article would not have been improved with your recommendation in any appreciable way for the reader.
- nwatson 4y agoParquet fits well into a system like Apache Arrow for good query performance, when considering partitioning (via directories), push-down predicates, and read-only-the-columns-you-need: https://arrow.apache.org/docs/python/dataset.html https://arrow.apache.org/docs/python/dataset.html "Tabular Datasets"
- rf15 4y agoThis is all very interesting (and makes me check out Parquet) but it's painful to see how the text goes to great lengths to describe how it avoids common problems and what they are, but doesn't lose a single word on... how they actually solved these problems. What is the actual boolean type? Which encoding are you actually working in? There's nothing concrete but an implication that it's also compressing data?
- ted_dunning 4y agoThe actual representation doesn't much matter. When you read the data into something like a dataframe, the boolean values are translated into something appropriate for the supporting language. The actual encodings are documented, but generally don't matter for anybody interacting with data encoded as Parquet. Regarding compression, yes, that is very much supported. And if you can sort your data to get lots of repetitions or near repetition in columns, it can work very well. For instance, I had a case with geo-hashes, latitudes, longitudes, times and values (which were commonly zero). When the data are sorted by geohash and then time the ZStandard compression algorithm gave 300:1 compression relative to raw CSV due to the very high repetition (geohash, lat, long are constant for long stretches, time and value barely change). What the article didn't mention is that tools like DuckDB eat parquet and interface with packages like pandas in an incredible symbiotic fashion. This works so well that my favorite way to consume or produce CSV or Parquet files is via duckdb rather than native readers and writers.
- enriquto 4y ago> The actual representation doesn't much matter. This is a site for hackers. The actual representation is probably the only interesting point for many readers.
- cout 4y agoYou may find easy to write parquet from one tool and read into duckdb, but it isn't always that simple. There are multiple variants of parquet, and they are not always compatible. I have to be careful which features of parquet I use when writing from spark (which uses a Java implementation of parquet), because I have a colleague who uses matlab (which uses parquetcpp).
- Helmut10001 4y agoI've used Parquet here to filter/map 400 Million Twitter geotagged tweets [1]. The main advantage was that it automatically partitions data so it can be streamed and chunked during processing. [1]: https://ad.vgiscience.org/twitter-global-preview/00_Twitter_datashader.html https://ad.vgiscience.org/twitter-global-preview/00_Twitter_...
- wodenokoto 4y agoI've still yet to figure out where feather fits into this.
- jiggunjer 4y agoMy understanding: they're like pickle files, but for programs that use the arrow standard.
- pietroppeter 4y agoI was surprised lately to realize there is a not a standard way to import a parquet file in MSSQL. Happy to be proven wrong
- onezaabeel 4y ago[dead]
- ayhanfuat 4y agoI wanted to dive into Parquet internals a couple of times to see how it works but there seems to be no tool to see page level data. The only option seems to be to use Arrow’s C++ library. Is anybody aware of a higher level option?
- mytherin 4y agoYou can query the metadata of a Parquet file using DuckDB's parquet_schema and parquet_metadata functions [1] [1] https://duckdb.org/docs/data/parquet/metadata https://duckdb.org/docs/data/parquet/metadata
- jjgreen 4y agoapt-cache search parquet <nada>
- jatorre 4y agoAnd if you are interested on encoding Geospatial Data there is a format called Geoparquet that is on the way of standarization: https://geoparquet.org/ https://geoparquet.org/ Essentially adding metadata on the extensible schema metadata streucture that parquet supports. Think of WKB in a column. With more exciting stuff coming that way.
- twelvechairs 4y agoMain issue with geoparquet at the moment is theres no spatial indexing. When that comes it becomes a lot more viable to replace both other GIS file types like geopackage and also for some cases even database storage like postgis. Very much looking forward to it though, hoping it covers pointcloud and TIN data well eventually also
- poulpy123 4y agoOne of the main interest of csv is that it's human readable and usable with common text tools (such as grep, cat or any texte editor) so I'm not sure the comparison should be done between parquet and csv, but between parquet and other formats like hdf5 and netcdf. What is the advantage of this new format compared to the older ones ?
- jiggawatts 4y agoSTOP. People insist on making data exchange formats used to transfer data between software 99.99% of the time "easy for humans" that 0.001% of the time, at the expense of the common case. This inevitably results in squishy, inefficient formats like HTML and JSON that become performance quagmires and security land mines: http://seriot.ch/projects/parsing_json.html http://seriot.ch/projects/parsing_json.html CSV is not even easy for humans to process, with or without tools like grep. Can you successfully handle quote escaping and line-wrapping? This ,"is ", valid "Hello",123,"''from the not,what,you, ""think"",next line""" That's one record, by the way. Your level one challenge is: Extract the unescaped value of the "valid" column. Write your robust, tested, "grep" command-line below. Level two: Make that parse consistently across a dozen tools from a dozen vendors. Try ETL tools from Oracle, Sybase, and SAP. Try Java, PowerShell, and Excel. Then import it via an ODBC text driver, and also into PowerBI. I wish you luck.
- xupybd 4y agoExcel is ubiquitous and makes csv easy for humans to read. Machines do the 99.99% of the time that things are working. The .01% when humans need to get in the loop is when something goes wrong and life is better when you can read the data to figure out what went wrong. JSON is amazing for this. CSV not so much but it's still better than a binary format. Machine efficiency is not the most valuable metric. Machines are built for human use. The easier a system is for humans to use the better. Even if it's orders of magnitude less efficient.
- imtringued 4y agoExcel doesn't make CSV easy to read. Type information has to be communicated out of band with CSV. If you want to view the data in Excel just use xlsx files. Compared to CSV it is a joy to work with.
- jiggunjer 4y agoI'm interested in a good tool for converting csv to pq. Seems many encoding features are not supported by most.
- CoBE10 4y agoWhy is the picture of Sutjeska war memorial in the article?
- calpaterson 4y agoI reserve the right to use my holiday snaps as I want on my own blog :)
- majkinetor 4y agoBecause Sutjeska war memorial was "Quicker, but also more convenient" :)
- julik 4y agoThere is a point which one needs to be aware of with Parquet: if you are not stepping into it with tools well suited for it (that is: Go, C++, Java or Python codebase/runtime, mainstream platform...) you are going to have a bad time. For all its advantages, Parquet is _very_ complicated (IMO overcomplicated, on multiple fronts), and uses binary dependencies which you absolutely need to carry around (Thrift?..). Up to a certain data size the advantage of CSV is not that it is a better format (it is decidedly, absolutely worse/more ambiguous than Parquet) but the fact that you can write a valid CSV in a couple of lines of anything, and read in a couple of lines of anything. If you ask me, Parquet - for all its advantages - is a product of the Cambrian explosion of the Apache-big-data-projects. Very little in its design warrants (or requires) using things like Thrift, so I won't exclude that something less overcomplicated (and more welcoming, and requiring less dependencies) will appear on the scene at some point.
- cm2187 4y agoTo be honest any file format that would do 1) strong typing/binary data, 2) string deduplication would capture most of the advantages of parquet, except being a column store format which helps in some cases and hurts in others.
- julik 4y agoAnd even using a columnar format but without all the complexity of Parquet would be comparatively easy (when writing using buffers)
- nomadluap 4y agoAt that point just start using an SQLite database as a data format.
- jonstewart 4y agoSQLite is a row-oriented store, not a column-oriented store, ie, there are fundamental tradeoffs between the two.
- 4y ago
- crabbone 4y ago> On a real world 10 million row financial data table I just tested with pandas I found that Parquet is about 7.5 times quicker to read than csv, ~10 times quicker to write and a about a fifth of the size on disk. So way to think of Parquet is as "turbo csv" - like csv, just faster (and smaller). Come on... who does tests like this? Why is anyone supposed to believe these numbers? What is even the point of making such claims w/o any evidence? --- PS. Also, buried somewhere in the middle of the article is the admission that the format isn't possible to stream. And, if we expand on the file structure, then it becomes apparent that it was inspired by typical relational database storage, except simplified, which made the format awful for insertion / deletion operations. Of course, if you compare to CSV, then there's no loss here, but there are other formats which can handle limited, or even unlimited insertion / deletion with the resource use similar to writes. Even truncation in this format is complicated (compared to CSV). Seems like the format was trying to be the middle ground between proper relational database storage and something like CSV, but not sure if such middle ground is actually necessary...
- cm2187 4y agoOn the other hand because it is column storage if you want to do the sum of one column grouped by another column, and there is a 100 other columns, it is way faster than csv since you only read the parts of the file you need.
- crabbone 4y agoThis is an oversimplification. You will have to read the index to figure out where the parts you need to read are. And if your columns don't align well with the blocks / pages of the storage you are using, this "optimization" may turn out to be worthless, or, perhaps even harmful, if your system is starving for memory.
- calpaterson 4y agoWell, it was me who did that test and frankly I only included it to give some context for people who've never heard of Parquet. If you want evidenced benchmarks I'm sure you can google for those yourself or indeed just run your own - the benefit differs as the data does (financial data obviously is mainly numerical). I'm not, in this post, actually talking about Parquet being faster than csv but instead about how it makes data interchange easier (mostly that is about having an embedded schema).