Styjun

I have a script firing from lambda that executes the following query:

 COPY test.error_log__c
 FROM 's3://sfdc-etl-jp-test/sfdc_etl/json/error_log__c_json/2019/03/23/'
 iam_role 'arn:aws:iam::<account>:role/LambdaFullAccessRole'
 TRUNCATECOLUMNS
 JSON 'auto'

However, the tables are empty even though the query completes successfully. There are anywhere between 1 and 100 files in these paths. I am guessing that the copy command isn't smart enough to know what the file name is and that is why this isn't working. Am I right? if so how do I tell it to load multiple files?

Here is the code being executed in case this isn't a query issue:

create_engine('postgres://:@/ibdrs'.format(igersUser, igersPwd, igersHost), encoding="utf-8")

 loadQuery = '''
 COPY .
 FROM '/'
 iam_role 'arn:aws:iam::<account>:role/LambdaFullAccessRole' 
 TRUNCATECOLUMNS
 JSON 'auto'
 EMPTYASNULL
 TIMEFORMAT 'auto'
 DATEFORMAT 'auto'
 COMPUPDATE OFF
 STATUPDATE ON
 '''.format(igersSchema, nextObj, s3Destination, s3Path.format(nextObj), dated_path)

with igers.connect() as conn:
 try :
 conn.execute(drop_table)
 print('completed drop table')
 conn.execute(ddl_str)
 print('completed create table')
 conn.execute(loadQuery).execution_options(autocommit=True)
 print('completed load query')
 for row in range(len(groupPerms)) :
 perms_statement = grantPerms.format(groupPerms['namespace'].iloc[row],groupPerms['item'].iloc[row],groupPerms['groname'].iloc[row])
 conn.execute(perms_statement)
 print('completed grant group permissions')
 conn.close()
 except exc.SQLAlchemyError as e :
 print(e)

Note: Yes I know there are other queries, they are not shown as they are executing. Drop table, recreate table, re-apply permissions are all working and verified working. It is just the copy from S3 that is doing nothing.

The answer is .... apparently order matters to sqlalchemy:

create_engine('postgres://:@/ibdrs'.format(igersUser, igersPwd, igersHost), encoding="utf-8")

 loadQuery = '''
 COPY .
 FROM '/'
 iam_role 'arn:aws:iam::<account>:role/LambdaFullAccessRole' 
 TRUNCATECOLUMNS
 JSON 'auto'
 EMPTYASNULL
 TIMEFORMAT 'auto'
 DATEFORMAT 'auto'
 COMPUPDATE OFF
 STATUPDATE ON
 '''.format(igersSchema, nextObj, s3Destination, s3Path.format(nextObj), dated_path)

with igers.connect() as conn:
 try :
 conn.execute(drop_table)
 print('completed drop table')
 conn.execute(ddl_str)
 print('completed create table')
 conn.execution_options(autocommit=True).execute(loadQuery)
 print('completed load query')
 for row in range(len(groupPerms)) :
 perms_statement = grantPerms.format(groupPerms['namespace'].iloc[row],groupPerms['item'].iloc[row],groupPerms['groname'].iloc[row])
 conn.execute(perms_statement)
 print('completed grant group permissions')
 conn.close()
 except exc.SQLAlchemyError as e :
 print(e)

you'll notice the order change to the auto_commit.