Unload multiple files from Redshift to S3

Question

Hi I am trying to unload multiple tables from Redshift to a particular S3 bucket getting below error:

 psycopg2.InternalError: Specified unload destination on S3 is not empty. Consider using a different bucket / prefix, manually removing the target files in S3, or using the ALLOWOVERWRITE option.

if I add 'allowoverwrite' option on unload_function, it is overwritting before table and unloading last table in S3.

This is the code I have given:

import psycopg2

def unload_data(r_conn, aws_iam_role, datastoring_path, region, table_name):
     unload = '''unload ('select * from {}')
                    to '{}'
                    credentials 'aws_iam_role={}'
                    manifest
                    gzip
                    delimiter ',' addquotes escape parallel off '''.format(table_name, datastoring_path, aws_iam_role)

     print ("Exporting table to datastoring_path")
     cur = r_conn.cursor()
     cur.execute(unload)
     r_conn.commit()

def main():
     host_rs = 'dataingestion.*********.us******2.redshift.amazonaws.com'
     port_rs = '5439'
     database_rs = '******'
     user_rs = '******'
     password_rs = '********'
     rs_tables = [ 'Employee', 'Employe_details' ]

     iam_role = 'arn:aws:iam::************:role/RedshiftCopyUnload'
     s3_datastoring_path = 's3://mysamplebuck/'
     s3_region = 'us_*****_2'
     print ("Exporting from source")
     src_conn = psycopg2.connect(host = host_rs,
                                 port = port_rs,
                                 database = database_rs,
                                 user = user_rs,
                                 password = password_rs)
     print ("Connected to RS")

     for i, tabe in enumerate(rs_tables):
          if tabe[0] == tabe[-1]:
              print("No files to read!")
          unload_data(src_conn, aws_iam_role = iam_role, datastoring_path = s3_datastoring_path, region = s3_region, table_name = rs_tables[i])
          print (rs_tables[i])


if __name__=="__main__":
main()

Answer 1

It is complaining that you are saving the data to the same destination.

This would be like copying all the files on your computer to the same directory -- there will be files overwritten.

You should change your datastoring_path to be different for each table, such as:

.format(table_name, datastoring_path + '/' + table_name, aws_iam_role)