Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

How to get identity from one row insert?

We would like to generate id for each job execution.
CREATE TABLE public.etlbatch
(
_etlbatchid INTEGER NOT NULL IDENTITY(1, 1),
datetimeid TIMESTAMP DEFAULT ('now'::character varying)::timestamp without time zone NOT NULL,
source VARCHAR(60) NOT NULL
)
DISTSTYLE EVEN;

This is SQL code:
BEGIN;

INSERT INTO etlbatch (source) VALUES('NewFile.txt');

SELECT MAX(_etlbatchid) AS newid FROM etlbatch;

END;

How can I do this from Matillion.
Thank you,
Dmitriy

7 Community Answers

Matillion Agent  

Kalyan Arangam —

Hi Dmitry,

You could use a Python script component and its cursor object to execute the final SELECT and store the value in a variable.
The python script component has an example of running SQL using the cursor object.

In fact, you could run any DDL DML using “cursor.execute()” method.

Hope that helps.

Best
Kalyan


Dmitriy (Consultant) Burtsev —

How can I translate transaction from Python 3 script to Jython?
This is my Python 3 code:
import psycopg2
def main():
#print("here")
con=psycopg2.connect(dbname='xyz', host='xyz.xyz.us-east-1.redshift.amazonaws.com',port='5439', user='xyz', password='xyz')
con.set_session(isolation_level=psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE, autocommit=False)
cur = con.cursor()
cur.execute("INSERT INTO etlbatch (source) VALUES ('NewFilefuyfkuy.txt')")
cur.execute("SELECT MAX(_etlbatchid) AS newid FROM etlbatch")

new_id = cur.fetchone()
con.commit()
con.close()
print(new_id)
pass


Matillion Agent  

Ian Funnell —

Hi Dmitriy,

The main difference is that in Jython you can access the database defined in the current Environment via the ‘cursor’ object provided.

cursor = context.cursor()

Please see this document for more information.

I’d recommend that where possible you perform this kind of logic in a Transformation job rather than in Jython/Python.

Best regards,
Ian


Dmitriy (Consultant) Burtsev —

Hello
"Transactions are handled for you, either automatically (Auto-commit mode) or manually using the Begin/Commit/Rollback components."

I need execute two SQL statements as one transaction. How can I manually start a transaction?


Dmitriy (Consultant) Burtsev —

If it possible to get back IDENTITY value after insert using Transformation job, I would like to see an example. SQL statements are in my first post.
Thank you,
Dmitriy Burtsev


Matillion Agent  

Kalyan Arangam —

You may try enclosing your PYTHON component between a BEGIN and COMMIT component. Connect the OnError branch to a ROLLBACK component. This would work when using Jython interpreter.

A simpler option may be to include then in your code with the cursor object.

cursor = context.cursor()
cursor.execute('BEGIN TRANSACTION')
try:
  cursor.execute('INSERT INTO...."')
  cursor.execute('COMMIT"')
except:
  cursor.execute('ROLLBACK')

Hope that helps.

Best
Kalyan


Dmitriy (Consultant) Burtsev —

Thank you, it works.

Post Your Community Answer

To add an answer please login