python redshift_connector executes create table statement with out errors but table not created in redshift cluster

Question

Hi Everyone.
I would like to know how redshift knows what schema a table should be created in when a database contains multiple schemas and the table is being created programmatically from a python script. I want to know this because I have a script where I create a table and copy data to it from S3. Both lines run without errors but when I log into AWS and check my cluster, the table is not there. I currently use a schema_name.table_name format when writing the create statement because I don't know how else to specify the schema the table should be created in.
The relevant lines of code are as follows:

sql = 'CREATE TABLE IF NOT EXISTS ' + schema_name.table_name + ' (column datatype, column datatype, column datatype);'
     cursor.execute(sql)
     sql = """COPY %s FROM '%s' access_key_id '%s' secret_access_key '%s' delimiter '%s' CSV ignoreheader 1 timeformat 'auto';""" % (table_name, s3_path_to_file_name, access_key_id, secret_key,delim)
     cursor.execute(sql)

Like I said the code runs without errors but when I check the cluster, the table is not there.
I would be extremely grateful for any hints that could help resolve this problem.

Answer

Abdul,

When no schema name is provided while creating a table, Redshift relies on 'search_path' to retrieve the default one available. By default 'search_path' value is  `'$user', public. `

Here $user corresponds to the SESSION_USER. If it finds a schema name with the same name as session user, the table is created here. If redshift didn't find a schema, it looks for the next schema name in the list which is 'public'.

If none of the names listed in 'search_path' has a corresponding schema, then create table operation will fail with error "no schema has been selected to create in".

To confirm the table creation is successful, verify the schema name provided from python is referenced in 'search_path'.

-- SQL script to view contents of search_path. If the schema mentioned in the python script isn't listed, then you need to reference it.
  `show search_path`

-- SQL script to include a schema to search_path. Note: this overwrites existing schema names in the list,
  
`set search_path to replace_with_schema_name;`

-- In order to append a new entry to existing search_path. Assuming the existing default values are '$user',public

`set search_path to '$user',public, replace_with_schema_name`

You can find more on search_path here
https://docs.aws.amazon.com/redshift/latest/dg/r_search_path.html

The other possibilities could be

1. Can you confirm if commit()  is called after the table creation.

2. Ensure you are looking at the right aws region, cluster and database name the table was created under.

3. Make sure the logged in user has necessary permissions to view the table.

Querying the system table, will return rows referencing schema name, if the table creation was successful. Please ensure the 'search_path' contains the schema name, referenced in your python code, else no records will be returned.

`SELECT * FROM SVV_TABLE_INFO where "table" like '%replace_with_your_table_name%';`

`SELECT  * FROM  PG_TABLE_DEF where tablename like '%replace_with_your_table_name%';`

You can find more details on Create table syntax here
https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html

python redshift_connector executes create table statement with out errors but table not created in redshift cluster

Relevant content