I am trying to load data from an EC2 instance into Redshift tables but cannot figure out how to do this using the copy command. I have tried the following to create the sql queries:
def copy_query_creator(table_name, schema):
copy_sql_template = sql.SQL("COPY {table_name} from stdin iam_role 'iam_role' DATEFORMAT 'MM-DD-YYYY' TIMEFORMAT 'MM-DD-YYYY HH12:MI:SS AM' ACCEPTINVCHARS fixedwidth {schema}").format(table_name = sql.Identifier(table_name),schema = schema)
return copy_sql_template
and
def copy_query_creator_2(table_name, iam_role, schema):
copy_sql_base = """
COPY {} FROM STDIN iam_role {} DATEFORMAT 'MM-DD-YYYY' TIMEFORMAT 'MM-DD-YYYY HH12:MI:SS AM' ACCEPTINVCHARS fixedwidth {}""".format(table_name, iam_role, schema)
print(copy_sql_base)
return copy_sql_base
where schema is the fixedwidth_spec in the example snippet below:
copy table_name from 's3://mybucket/prefix'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
fixedwidth 'fixedwidth_spec';
The function that uses the query created looks like so:
def copy_query(self, filepath):
schema = Query.create_schema() #returns the formatted fixedwidth_spec
table_name = Query.get_table_def() #returns the table_name
print(copy_query_creator_2(table_name, iam_role, schema))
self.connect()
with self.connection.cursor() as cursor:
try:
with open(filepath) as f:
cursor.copy_expert(copy_query_creator_2(table_name, iam_role, schema), f)
print('copy worked')
logging.info(f'{copy_query_creator_2(table_name, iam_role, schema)} ran; {cursor.rowcount} records copied.')
except (Exception, psycopg2.Error) as error:
logging.error(error)
print(error)
The two attempts return errors. The first returns 'Composed elements must be Composable, got %r instead' while the latter returns 'error at or near STDIN'. Please help.