Python Glue job - Snowflake stored procedure not returning exact error message

user_13194083 0
Viewed 2190

I am trying to execute a snowflake stored procedure via python glue job, but when I get any error in the SQL statements in the stored procedure, I want the procedure to exit and that glue job should fail.

I am using this code in my snowflake stored procedure:

create or replace procedure schema.PROC_TEST 
    (input_sql varchar(16777216))
returns string not null
language javascript
as
$$
snowflake.execute({sqlText:`BEGIN TRANSACTION;`});
try
                {
                    var QueryExec = INPUT_SQL+ ";";
                    var QueryResult = snowflake.execute({sqlText: QueryExec});
                }
                catch (err)                                     // Catching any error if occurs in INPUT_SQL
                {
                    result = 'Error: ' + err;
                    snowflake.execute({sqlText:`ROLLBACK;`});
                    return result;
                    throw err.message;
                }
$$;

Now if I pass input query as below (here proc_test_final1 is an incorrect table name) :

insert into schema.proc_test_final1 
    select id, name, sal 
    from schema.proc_test_src;

Instead of throwing an error 'incorrect table name', the procedure returns a generic error like

Error: 090232 (25000): Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back

I thought it might be because of ROLLBACK command so I tried removing the command but still it failed with the same above error. I also tried removing result variable in catch block but still above error was caught.

I want to catch the exact specific SQL error (syntax error/code error) in my glue job.

Below is my glue job code where I am catching the error:

input_sql = 'insert into schema.proc_test_final1 select id, name, sal from schema.proc_test_src'
try:
    f_CallProc = "call TEST_PROC('"+input_sql+"');"
    cur.execute(f_CallProc)
    print(cur.fetchall())
    cur.close()
    ctx.close()

except snowflake.connector.errors.ProgrammingError as err:
    print('Error: {0} ({1})'.format(err.msg, err.sfqid))
    cur.close()
    ctx.close()
    sys.exit(1)
aws-glue snowflake-cloud-data-platform stored-procedures
1 Answers

The error "Scoped transaction started in stored procedure is incomplete and it was rolled back" will happen when you run the SP that has explicit START TRANSACTION, but no explicit "ROLLBACK" or "COMMIT".

In my test, if I do not have below line:

snowflake.execute({sqlText:`ROLLBACK;`});

Your SP will fail with such error.

However, when I have this line, it will fail with below error instead:

Error: SQL compilation error:
Schema 'ERICLIN.SCHEMA' does not exist or not authorized.

Which I think is what you are after.

Can you please double check your SP again? Did you copy and paste the correct one?

By the way, your original SP won't run because there is no variable called "input_sql", it should be "INPUT_SQL", as every variable in Snowflake's SP is in UPPER case.

=========================

UPDATE

Please see below execution output:

create or replace procedure PROC_TEST 
    (input_sql varchar(16777216))
returns string not null
language javascript
as
$$
    snowflake.execute({sqlText:`BEGIN TRANSACTION;`});
    try {
        var QueryExec = INPUT_SQL + ";";
        var QueryResult = snowflake.execute({sqlText: QueryExec});
    }
    catch (err)  {
        result = 'Error: ' + err;
        snowflake.execute({sqlText:`ROLLBACK;`});
        return result;
    }
$$;
call PROC_TEST('insert into schema.proc_test_final1 select id, name, sal from schema.proc_test_src');

+-----------------------------------------------------------+
| PROC_TEST                                                 |
|-----------------------------------------------------------|
| Error: SQL compilation error:                             |
| Schema 'ERICLIN.SCHEMA' does not exist or not authorized. |
+-----------------------------------------------------------+

Without the ROLLBACK call, it will produce an error same as yours:

create or replace procedure PROC_TEST 
    (input_sql varchar(16777216))
returns string not null
language javascript
as
$$
    snowflake.execute({sqlText:`BEGIN TRANSACTION;`});
    try {
        var QueryExec = INPUT_SQL + ";";
        var QueryResult = snowflake.execute({sqlText: QueryExec});
    }
    catch (err)  {
        result = 'Error: ' + err;
        return result;
    }
$$;
call PROC_TEST('insert into schema.proc_test_final1 select id, name, sal from schema.proc_test_src');

090232 (25000): Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back.
user_98793790
Related