Materialize: support source formats in Parquet

[rjnn]

Not every input to Materialize will be a Kafka topic. In particular, database backups are a format worth supporting as-is to create a source. In making some simplifying assumptions, we can assume the dumpfile is a set of DDL and INSERT statements in the standard mysqldump format, and is stored in a single file on S3/Google Cloud Storage/Azure Blob Storage, and was backed up with --gtid enabled.

cc @benesch for triage - perhaps this issue seems familiar to you? :)

[rjnn]

Also, support ingesting NDJSON as a source. Notably, this is the only format supported by Snowflake as an EXPORT format.

Also support pg_dump.

Between these three, they cover a broad variety of the database dump formats we can expect to encounter (Oracle does not have a standard dump file format).

[rjnn]

I misread the Snowflake documentation - they only support NDJSON as their JSON export format. They do support three export formats: Parquet, CSV, and NDJSON.

Parquet seems like the most sane format for us to support imports from, as it is compactly serialized, but I could see demand for the others as well. For posterity, here are Snowflake's documented import/export options which seems like a good first pass at the complexity of options available.

[rjnn]

Snowflake notably does not support export into ORC. I think we can perform a close-reading of Snowflake's supported formats to get an early sense as to what sane customers want. An initial pass seems to suggest that Parquet is saner than ORC, and thus should be higher on our priority list unless customers explicitly request ORC import capabilities.

[rjnn]

I'm clearing the milestone on this issue, and retitling it as we now have CSV, NDJSON, and arbitrary Regex source support from local files, which is sufficient interop for now. All this leaves is supporting ingesting Parquet files. I'll leave this issue open as we'll likely want to get to this at some point in the future.

[dazzag24]

+1 for Parquet support. I can see definite use cases for using Materialize to process our parquet datasets stored on Azure blob.

[rjnn]

@dazzag24 thanks for the input! I'd love to grab a longer conversation with you if possible - we have a few different paths to supporting Parquet, and understanding your use cases would be very valuable inputs to those design decisions.

[quodlibetor]

A related (but row- instead of column-oriented) file format that we _do_ support right now is Avro ObjectContainerFiles. Pointing it out because it comes up in similar spaces that folks want to use parquet in but wasn't mentioned in an earlier list of filetypes that we support OOTB.

[rjnn]

I wonder how much work it is to add support for Parquet...

[quodlibetor]

Unfortunately the main rust crate for parquet uses the rust nightly "specialization" feature, and it seems like [they think] that will be hard to remove.

[rjnn]

~the linked issue is Arrow, not Parquet?~

[rjnn]

(redacting my previous comment, i understand now).

[alippai]

I assume @andygrove can help with an authoritative answer

[jdotjdot]

Another big +1 here for Parquet support. I've done a lot of migrations between warehouses, regular dumping of OLTP data to OLAP warehouses, and I also regularly keep flat copies of the OLTP data in S3 for arbitrary queries with Presto or Athena as needed. In all cases, Parquet is the main storage format I use in-between. Parquet would be very useful.

[andrioni]

Good news: with apache/arrow#8698, Arrow (and Parquet) doesn't require nightly anymore. I believe they are planning to cut Arrow 3.0 in early January.

[awang]

Looks like arrow 3.0 just dropped, we should consider leveraging it for Parquet support. Also note that Parquet is the storage format of Delta Lake

[benesch]

Yep, this would definitely be a nice to have, but our bandwidth for maintaining yet another source format is a bit thin at the moment. The data types are here: https://docs.rs/arrow/3.0.0/arrow/datatypes/enum.DataType.html. Per usual they don't map perfectly to ours—notably the existence of unsigned types, which has been a persistent irritation. (Protobuf also has unsigned types.)

On the bright side, the arrow crate looks to be in much better shape than the avro crate was. That's promising. The parquet crate sadly looks a bit spottier, based on the number of missing docs, but still hopefully serviceable.