Cannot COPY into nonexistent table when table exists-CodePudding

so, I have a table nba_schedule which is created below. When I try to copy data from an s3 csv file to insert to the table using COPY, I receive this error InternalError_: Cannot COPY into nonexistent table newsletter_schedule.

I'm thinking it's because this is all taking place in the same transaction, which is what I am expected to do here. Also, the redshift variables are located in an env file, I'm not sharing the code that loads that in.

redshift_table = 'nba_schedule'


# Connect to redshift
conn_string = "dbname={} port={} user={} password={} host={}".format(
    redshift_dbname, redshift_port, redshift_user, redshift_password, redshift_host)
conn = psycopg2.connect(conn_string)
cursor = conn.cursor()


logging.info("Creating newsletter_schedule table in Redshift")

sql = f"""DROP TABLE IF EXISTS {schema   "."   redshift_table}"""
cursor.execute(sql)
sql = f"""CREATE TABLE IF NOT EXISTS {schema   "."   redshift_table} (
             Date DATE,
             Player_Name VARCHAR(255),
             Player_Nickname VARCHAR(13),
             Player_No VARCHAR(13),
             Points VARCHAR(255),
             Rebounds VARCHAR(255),
             Assists VARCHAR(255),
             Blocks VARCHAR(1),
             3PM VARCHAR(1),
             3PA VARCHAR(1),
             FGM VARCHAR(50),
             FGA VARCHAR(255),
             three_percent VARCHAR(50),
             fg_percent VARCHAR(50)
             )
        """
cursor.execute(sql)
sql =f"""
        COPY newsletter_schedule
        FROM 's3://random_sample_data/nba_redshift/{s3_file}'
        CREDENTIALS 'aws_iam_role=arn:aws:iam::4254514352:role/SampleRole'
        DELIMITER ','
        IGNOREHEADER 1
        EMPTYASNULL
        QUOTE '"'
        CSV
        REGION 'us-east-1';

    """
cursor.execute(sql)
    
conn.commit()

Any thoughts?

CodePudding user response：

My first thought is that the CREATE TABLE is with the schema explicitly defined but the COPY command w/o the schema defined, just the table name. Now I don't know what schema you are using or what the search path is for this user on Redshift but it seems like you should check that this isn't just a schema search path issue.

What happens if you use schema.table in the COPY command? (this debug path is easier to explain than describing how to evaluate the user's search path)

There are other more subtle ways this could be happening but I've learned to look at the simple causes first - they are easier to rule out and more often than not the root cause.