Bulk Loader Capability for Postgres SQL

[Nelavv]

Hi,

     Currently loading large files to Postgres SQL(over 100 MB) takes an extremely long time. For example writing a 1GB file to Postgres SQL takes 27 minutes! This is serious impacting our ability to use Alteryx as an ETL tool for loading our target Postgres Data Warehouse. We would really like to see the bulk load capacity to Postgres supported by Alteryx to help alleviate the performance issues.

Thanks,

Vijaya

[jemerson]

Any movement on this? My company will not be renewing licenses if this bug is not fixed as we are migrating to a PostGres platform.

[bora_perusic]

We are going to speed up the PostgreSQL inserts, hopefully already in 19.3. We also have the bulk updates (in general) in the plans for the second half of 2019 and PostgreSQL is one of the top candidates on our list.

[davidhenington]

Fantastic news Bora! 

[davidhenington]

Bora, regarding speeding up the inserts, is this via standard output tools as well as write in-db? I see huge value in both angles. 

[alexisjensen]

Hi,

Is there a timeline update on this?  knowing the timeline will help us decide our way forward

[bora_perusic]

I cannot unfortunately yet commit to any timelines here, we are constantly revising the resourcing. It is among the top 10 of the connectors activities.

Regarding In-DB (Stream In/Out): we will see about the batching (multi value inserts); bulk loaders are more difficult here and while we are planning it this is more a longer term

[jemerson]

To elaborate on my previous comment, decision time for renewing is coming in early September. If we do not see this issue remedied by end of August, we will not renew our licenses. 

Please use the statement above in your internal meetings regarding resourcing to focus on this issue so that you can keep a customer who would like to stay a customer and actually wants to expand the number of licenses purchased, but can't due to this issue.

Might be too late for us by then, but at least wanted to provide a public comment  you can refer to that this issue is important to some of your customers, and that you will lose revenue as a result of not being fixed.

[kirk_yates]

Good day!

Is there an update for Bulk Data Loader for Postgres SQL?

Also, see comments related to Bulk loader for GreenPlum being "cancelled".  

Is Greenplum on the roadmap for Bulk Loader?

Thank you!

[Yuges81]

Hi,

If Alteryx doesn't help us, we can use Python to improve time :

import psycopg2
import os
from datetime import datetime
from simple_timer import Timer
import time

try:
    pg_user = "user"
    pg_pass = "pass"
    pg_host = "pg_address"
    pg_port = "pg_port"
    pg_dbname = "database"
    pg_schema = "schema"
    pg_table = '"table"'

    file_csv = 'path_to_csv_file'

    pg_connect = "user='%s' password='%s' host='%s' port='%s' dbname='%s'"%(pg_user, pg_pass, pg_host, pg_port, pg_dbname)

    # Log start
    m_timer = Timer()

    # Database connection
    connection = psycopg2.connect(pg_connect)

    cursor = connection.cursor()

    # Print PostgreSQL version
    cursor.execute("SELECT version();")
    record = cursor.fetchone()
    print("You are connected to : ", record,"\n")

    # SET schema
    cursor.execute("SET search_path TO %s" % pg_schema)

    # TRUNCATE Table
    pg_SQL = 'TRUNCATE %s;' % pg_table
    cursor.execute(pg_SQL)
    connection.commit()
    print("Exec OK : %s" % pg_SQL)

    # Open CSV & Skip first line
    print("Ouverture fichier CSV : %s" % file_csv)
    csvfile = open(file_csv, 'r')
    # Skip First Line (si entête)
    next(csvfile)
    
    # Import et Commit
    cursor.copy_from(csvfile, pg_table, sep=';')
    connection.commit()

    # Trace fin
    time.sleep(0.001)
    m_timer.stop()

    print("Job did in {:3f} sec.".format(m_timer.duration))

except (Exception, psycopg2.Error) as error :
    print ("Error while connecting to PostgreSQL", error)
finally:
    #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

[alexisjensen]

Can we please get an update where this bulk loader is on the roadmap?

Regards,

Alexis