How can I use Psycopg2 to add Partition in Redshift Spectrum -

Question

We have a Redshift Spectrum table built on top of S3 data - we are trying to automate the partition addition in this table - I can run the following ALTER statement in a redshift client or psql shell:

ALTER TABLE analytics_spectrum.page_view ADD PARTITION(date='2017-10-17') LOCATION 's3://data-hub/page_view/2017/10/17/';

But this fails to execute via psycopg2.

sql_query = "ALTER TABLE analytics_spectrum.page_view ADD PARTITION(date='2017-10-17') LOCATION 's3://data-hub/_page_view_v3/2017/10/17/';"
import config
import psycopg2
connection = psycopg2.connect(
            **config.DATABASES['redshift_db']["connection"])
cursor = connection.cursor()
cursor.execute(sql_query)

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.ProgrammingError: syntax error at or near "("
LINE 1: ...ABLE analytics_spectrum.page_view ADD PARTITION(date='201...

In case of psycopg2 it doesn't even send the query to redshift and execution fail in query parsing.

For now I have implemented using subprocess.popen to execute alter statement - but I would like to switch it back to use psycopg2.

p = subprocess.Popen(['psql',
                      '-h', self.spectrum_connection['host'],
                      '-p', self.spectrum_connection['port'],
                      '-d', self.spectrum_connection['dbname'],
                      '-U', self.spectrum_connection['user'],
                      '-c', sql_stmt],
                     env={
    'PGPASSWORD': self.spectrum_connection['password']},
    stdout=subprocess.PIPE,
    stderr=subprocess.PIPE)
out, err = p.communicate()

suggestions /thoughts ?

Thanks, Hussain Bohra

Above post talks about how to add partition in Redshift Spectrum table - but it doesn't say anything about using python and psycopg2. — Hussain Bohra, Nov 14 '17 at 19:26

score 3 · Answer 1 · answered Nov 22 '17 at 12:56

I had the same issue. Query execution without using ISOLATION_LEVEL_AUTOCOMMIT would raise following error:

psycopg2.InternalError: ALTER EXTERNAL TABLE cannot run inside a transaction block

I modified my code a little bit and it worked.

import argparse
import sys, psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

input_data = {}
input_data["db_name"] = <<DB_NAME>>
input_data["db_host"] = <<HOST_NAME>>
input_data["db_port"] = 5439
input_data["db_user"] = <<USER>>
input_data["db_pass"] = <<PASSWORD>>
con = psycopg2.connect(dbname=input_data["db_name"], host=input_data["db_host"], port=input_data["db_port"], user=input_data["db_user"], password=input_data["db_pass"])
con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cur = con.cursor()
query = <<ADD_YOUR_QUERY_HERE>>
cur.execute(query)
cur.close() 
con.close()

score 0 · Answer 2 · edited Jul 05 '19 at 09:06

0

In your query you have to add set autocommit=on; at first to pass transaction block.

Then magic will happen and you can partition your table.

edited Jul 05 '19 at 09:06

Qw3ry

1,319
15
31

answered Jul 05 '19 at 08:26

Mahdouani Yassine

1
1

How can I use Psycopg2 to add Partition in Redshift Spectrum -

2 Answers2