Teradata "CTAS" error - BT/ET is not closed properly

[pkasinathan]

Hi Team,

ct syntax to create table on sqlachemy-teradata is not working as expected.

When I run ct pp_scratch.temp_pxk as (select username from dbc.usersV) with data;, it's getting successfully executed. But, when I run some select sql, its throwing error [Teradata][ODBC Teradata Driver][Teradata Database] Only an ET or null statement is legal after a DDL Statement. ')

When I deep dive, I found that it's adding BT before running the ct statement, but it's not adding ET after the statement execution. It's a DDL statement, so I expect ET would have been added implicitly, but not.

Create Table, Drop Table are all working. The problem is with ct syntax.

For more details, I attached the Jupyter notebook to provide more information.

Please let me know how to solve this issue.

How to reproduce this error?
sqlalchemy_teradata_ctas_error.pdf

export LD_LIBRARY_PATH=/opt/teradata/client/14.10/odbc_64/lib64:/opt/teradata/client/14.10/odbc_64/lib:/usr/lib:$LD_LIBRARY_PATH:

export ODBCINI=/opt/teradata/odbc.ini

[10:59]:[root@lvshdcjupy001:~]# ipython
Python 3.5.2 |Anaconda 4.1.1 (64-bit)| (default, Jul  2 2016, 17:53:06) 
IPython 4.2.0 -- An enhanced Interactive Python.

In [1]: from sqlalchemy import create_engine

In [2]: td_engine1 = create_engine('teradata://myuser:mypass@teradata_system:22/')

In [3]: result = td_engine1.execute("ct mydb.temp as (select username from dbc.usersV) with data")

In [4]: result = td_engine1.execute("select * from mydb.temp")
---------------------------------------------------------------------------
DatabaseError                             Traceback (most recent call last)
/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1138                         parameters,
-> 1139                         context)
   1140         except Exception as e:

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    449     def do_execute(self, cursor, statement, parameters, context=None):
--> 450         cursor.execute(statement, parameters)
    451 

/opt/anaconda3/lib/python3.5/site-packages/teradata/tdodbc.py in execute(self, query, params, queryTimeout)
    591                 self.hStmt, _inputStr(_convertLineFeeds(query)), SQL_NTS)
--> 592             checkStatus(rc, hStmt=self.hStmt, method="SQLExecDirectW")
    593         self._handleResults()

/opt/anaconda3/lib/python3.5/site-packages/teradata/tdodbc.py in checkStatus(rc, hEnv, hDbc, hStmt, method, ignore)
    208                                  "variables are correctly set.")
--> 209                 raise DatabaseError(i[2], u"[{}] {}".format(i[0], msg), i[0])
    210             else:

DatabaseError: (3807, "[42S02] [Teradata][ODBC Teradata Driver][Teradata Database] Object 'mydb.temp' does not exist. ")

The above exception was the direct cause of the following exception:

DatabaseError                             Traceback (most recent call last)
<ipython-input-4-b93a630e7bf3> in <module>()
----> 1 result = td_engine1.execute("select * from mydb.temp")

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in execute(self, statement, *multiparams, **params)
   1989 
   1990         connection = self.contextual_connect(close_with_result=True)
-> 1991         return connection.execute(statement, *multiparams, **params)
   1992 
   1993     def scalar(self, statement, *multiparams, **params):

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in execute(self, object, *multiparams, **params)
    904         """
    905         if isinstance(object, util.string_types[0]):
--> 906             return self._execute_text(object, multiparams, params)
    907         try:
    908             meth = object._execute_on_connection

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _execute_text(self, statement, multiparams, params)
   1052             statement,
   1053             parameters,
-> 1054             statement, parameters
   1055         )
   1056         if self._has_events or self.engine._has_events:

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1144                 parameters,
   1145                 cursor,
-> 1146                 context)
   1147 
   1148         if self._has_events or self.engine._has_events:

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1339                 util.raise_from_cause(
   1340                     sqlalchemy_exception,
-> 1341                     exc_info
   1342                 )
   1343             else:

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py in raise_from_cause(exception, exc_info)
    200     exc_type, exc_value, exc_tb = exc_info
    201     cause = exc_value if exc_value is not exception else None
--> 202     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    203 
    204 if py3k:

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    183             value.__cause__ = cause
    184         if value.__traceback__ is not tb:
--> 185             raise value.with_traceback(tb)
    186         raise value
    187 

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1137                         statement,
   1138                         parameters,
-> 1139                         context)
   1140         except Exception as e:
   1141             self._handle_dbapi_exception(

/opt/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    448 
    449     def do_execute(self, cursor, statement, parameters, context=None):
--> 450         cursor.execute(statement, parameters)
    451 
    452     def do_execute_no_params(self, cursor, statement, context=None):

/opt/anaconda3/lib/python3.5/site-packages/teradata/tdodbc.py in execute(self, query, params, queryTimeout)
    590             rc = odbc.SQLExecDirectW(
    591                 self.hStmt, _inputStr(_convertLineFeeds(query)), SQL_NTS)
--> 592             checkStatus(rc, hStmt=self.hStmt, method="SQLExecDirectW")
    593         self._handleResults()
    594         return self

/opt/anaconda3/lib/python3.5/site-packages/teradata/tdodbc.py in checkStatus(rc, hEnv, hDbc, hStmt, method, ignore)
    207                                  "and the ODBCINI or ODBCINST environment "
    208                                  "variables are correctly set.")
--> 209                 raise DatabaseError(i[2], u"[{}] {}".format(i[0], msg), i[0])
    210             else:
    211                 logger.debug(

DatabaseError: (teradata.api.DatabaseError) (3807, "[42S02] [Teradata][ODBC Teradata Driver][Teradata Database] Object 'mydb.temp' does not exist. ") [SQL: 'select * from mydb.temp']

In [5]: quit()

[pkasinathan]

We are getting similar error after “Collect Stats” query too. Please help to fix.

[sandan]

@prabhu1984 Have you found a solution? The problem is that you are not committing the previous query. See http://docs.sqlalchemy.org/en/latest/core/connections.html#understanding-autocommit.

[alexisrolland]

I confirm that I face the same error with Collect Stats.
I have a script which:

• Executes a create table statement
• Executes a session commit
• Executes a collect stats statement

it fails with error:

sqlalchemy.exc.DatabaseError: (teradata.api.DatabaseError) (3932, '[25000] [Teradata][ODBC Teradata Driver][Teradata Database] Only an ET or null statement is legal after a DDL Statement. ')

It's like the commit statement is not taken into account.
Here is the connection string I am using:

# Connect
self.engine = create_engine('teradata://' + self.user + ':' + self.password + '@' + self.host + ':22/' + self.database)
self.db_session = scoped_session(sessionmaker(autocommit=False, bind=self.engine))
self.db_session.execute('SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;')  # To avoid locking tables when doing select on tables
self.db_session.commit()

And here is the piece of code which executes both create table and collect stats statements:

def execute_sql_statement(self, query):
    """Generic method to execute a SQL statement on target environment."""
    query_list = sorted(query.keys())  # Sort dictionary key, in particular to execute table_stats query after table query
    for query_name in query_list:
        # Sanitize SQL to avoid parameter binding by SQLAlchemy
        sql_string = text(query[query_name].replace(':', '\:'))
        self.target_environment.db_session.execute(sql_string)
        self.target_environment.db_session.commit()
    return True