SQLalchemy slow with Redshift
<p>I have a 44k rows table in a pandas Data Frame. When I try to export this table (or any other table) to a Redshift database, the process takes ages. I'm using sqlalchemy to create a conexion like this:</p>

<pre><code>import sqlalchemy as sal
engine = sal.create_engine('redshift+psycopg2://blablamyhost/myschema')
</code></pre>

<p>The method I use to export the tables is Pandas <code>to_sql</code> like this:</p>

<pre><code>dat.to_sql(name="olap_comercial",con=eng,schema="monetization",index=False,if_exists="replace" ,dtype={"description":sal.types.String(length=271),"date_postoffer":sal.types.DATE})
</code></pre>

<p>Is it normal that it is so slow? I'm talking about more than 15 minutes.</p>

<p>Yes, it is normal to be that slow (and possibly slower for large clusters). Regular sql inserts (as generated by sqlalchemy) are very slow for Redshift, and should be avoided.</p>

<p>You should consider using S3 as an intermediate staging layer, your data flow will be:
dataframe->S3->redshift</p>

<p>Ideally, you should also gzip your data before uploading to S3, this will improve your performance as well.</p>

<p>This can be coordinated from your python script using BOTO3 and psycopg2 
<a href="https://boto3.readthedocs.io/en/latest/" rel="nofollow">https://boto3.readthedocs.io/en/latest/</a></p>

python sqlalchemy