If you’re using Materialize to serve up-to-date query results to your users or applications as new data flows in from your production systems, chances are that you’ll want to incoporate these results into your heavy-duty analytical workloads…at some point.
To easily pipe results from Materialize to wherever else you need them, you can now trigger bulk exports to object storage, starting with Amazon S3! This is useful to perform tasks like periodic backups for auditing, and additional downstream processing in analytical data warehouses like Snowflake, Databricks or BigQuery.
How does it work?
We’ve extended the COPY TO command
to work as a “one-shot sink” that can (efficiently) write files to an S3 path in
your AWS account. All you need is to:
1. Create an AWS connection to allow Materialize to securely authenticate with your S3 service:
CREATE CONNECTION aws_conn TO AWS (
ASSUME ROLE ARN = 'arn:aws:iam::001234567890:role/MZS3Exporter'
);2. Double-check that you didn’t miss any IAM configuration steps (🙋♀️):
VALIDATE CONNECTION aws_conn;3. And then trigger the copy command with either the object or the query for which you want results to be exported:
COPY my_view TO 's3://mz-to-snow/202406030/'
WITH (
AWS CONNECTION = aws_conn,
FORMAT = 'parquet'
);That’s it!
From here, you can use the file(s) as you please! One last note: in the first iteration of bulk exports, you can export results as CSV- or Parquet-formatted files. In the future, we plan to extend support to table formats like Apache Iceberg or Delta Lake.
How do you get started?
For an overview of what is and isn’t supported in Private Preview, check out the reference documentation for bulk exports, and the first of many integration guides: Snowflake (via S3).
Because this feature is in Private Preview, you’ll need to ping our team on Slack to get early access. If you have any feedback, or just suggestions about how this feature could be the most useful to your use case, we’d absolutely love to hear about it!