-1

What is the best possible way to copy a table (with millions of rows) from one type of database to other type using pandas or python?

I have a table in PostreSQL database consisting of millions of rows, I want to move it to Amazon Redshift. What can be the best possible way to achieve that using pandas or python?

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Dhiraj
  • 87
  • 6
  • best way could be to use Postgresql tools to copy it without using python or pandas: [Copying PostgreSQL database to another server](https://stackoverflow.com/questions/1237725/copying-postgresql-database-to-another-server) – furas Oct 18 '19 at 11:07
  • What do you mean by "best"? – norok2 Oct 18 '19 at 11:32

2 Answers2

1

The Amazon Database Migration Service (DMS) can handle:

Alternatively, if you wish to do it yourself:

  • Export the data from PostgreSQL into CSV files (they can be gzip compressed)
  • Upload the files to Amazon S3
  • Create the destination tables in Amazon Redshift
  • Use the COPY command in Amazon Redshift to load the CSV files into Redshift
John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
  • I use the `copy` command. It's actually the best way to insert tons of data on an existent table. – rpanai Oct 19 '19 at 03:23
0

  • If you're using Aws services it might be good to use aws Glue, it uses python scripts for its ETL operations, very optimal for Dynamo-->Redshift for example.

  • If you're not using only Aws services, Try to Export your Redshift data as csv? (i did this for millions of rows) & create a migration tool using c# or whatever to read the csv file & insert your rows after converting them or whatever [Check if the Database technology you're using can take the csv directly so you can avoid doing the migration yourself].

WareN The GaMeR
  • 76
  • 1
  • 6