[HN Gopher] My experience with exporting Postgres RDS partitione...
___________________________________________________________________
My experience with exporting Postgres RDS partitioned tables to S3
Author : higeorge13
Score : 31 points
Date : 2022-05-15 17:54 UTC (5 hours ago)
(HTM) web link (geopet85.github.io)
(TXT) w3m dump (geopet85.github.io)
| metadata wrote:
| Scripting data movement is easy only for small and easy jobs.
| With many thousands of tables and more than a few TB, all kinds
| of issues start popping up. I read somewhere that 85% of large
| data migration projects fail. Data warehouses really need an
| optimal Parquet file sizes to work efficiently, and for Snowflake
| it's roughly 100-200MB per file. The good way to copy that is
| relying on DB statistics to determine the optimal number of
| records per chunk. Then, to have the job finish in a reasonable
| time, one needs to read a certain number of data chunks in
| parallel and stream that data into Parquet at S3 (or Azure
| Blobs). Once the data is up, Snowflake can ingest it.
|
| Shameless plug: my company created a commercial solution which
| does exactly that (https://www.spectralcore.com/omniloader).
| Happy to answer any questions.
| akdor1154 wrote:
| It sounds like you don't have AWS support? In case you do, give
| it a shot.. it takes a while but eventually you can get a
| knowledgeable answer. Though im in 50% on it being 'yes this is
| by design, please watch the RDS change log for future
| improvements' in this case.
| fdr wrote:
| ....that seems like an unusually enormous number of partitions.
| Marazan wrote:
| Ive had issues in the other direction.
|
| Importing data using the copy from s3 command has a nasty failure
| state if the client loses connection to the db AND the import
| command has a problem.
|
| The query hangs with a row exclusive lock and the query _cannot_
| be killed. Only option was to reboot the DB.
| higeorge13 wrote:
| You mean this function: https://docs.aws.amazon.com/AmazonRDS/l
| atest/UserGuide/USER_...? I don't get that Postgres RDS only
| allows you to import from csv, while the exports are strictly
| parquet. I know that Postgres implements the COPY command using
| csv or binary, but i would expect some end-to-end lifecycle
| implementation by AWS and parquet would at least enforce some
| schema and reduce some errors including those which caused
| yours.
| iknownothow wrote:
| As far I can tell, exporting query results from Postgres RDS
| to S3 uses Postgres's Copy and only supports CSV and Text
| [1]. Can you provide a link for parquet exports?
|
| [1] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/po
| stg...
| vanpythonista wrote:
| You can use a RDS Snapshot to export to S3 in Parquet
| format: https://docs.aws.amazon.com/AmazonRDS/latest/UserGu
| ide/USER_...
| singlow wrote:
| Might consider using the Athena Federated Query. Essentially it
| is a lambda that knows how to get data from a database instance
| and write it to s3. It is very well optimized for partitioned
| tables.
|
| https://github.com/awslabs/aws-athena-query-federation/tree/...
|
| Once that is configured, execute a Create-Table-As (CTAS) or
| insert query using a select from the configured lambda-backed
| catalog.
|
| I have only tryed the mysql version, but they docs indicate they
| are very similar.
| tomatowurst wrote:
| what about using sqlite diff stored in dynamodb that gets synced
| when lambda is called? thought i saw a repo for that.
|
| i think that has merit too reading this im reminded.
| higeorge13 wrote:
| It would be great if you could share some specifics or share
| the repo.
___________________________________________________________________
(page generated 2022-05-15 23:01 UTC)