By using AWS re:Post, you agree to the Terms of Use

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

0

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.

1 Answer
1

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

answered 6 months ago
  • Thanks AJP for your very detailed answer. I will try out your suggestions and let you know how it goes.

  • Thanks so much for the help AJP. A missing commit() was indeed the problem, or part of the problem. Prior to noticing the missing commit(), I had tried updating the search_path but it wouldn't update because the cluster was using the default parameter group. So I created a new one. The problem was still occuring after rebooting the cluster. It was then I noticed your comment about the commit(). Thanks so much for your help.

  • Glad to hear commit was the missing piece of the puzzle. Much appreciate if you can accept the response as answer.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions