0

I am trying to use pandas to_sql to an existing table in Teradata database but I got this QVCI error. After some research online, I upgraded my ODBC driver to 17.00 but I still get the same error.

Existing Table:

enter image description here

I am using sqlalchemy_teradata

Here is the code: a_df.to_sql(name='TEST_LA_TEMP', con=engine, schema='DB', if_exists='replace', index=False)

I then tried to specify the types but got the same error.

from sqlalchemy.types import INTEGER, VARCHAR, Date

a_df.head()
a_df.to_sql(name='TEST_LA_TEMP', con=engine_ldap, schema='DB', if_exists='replace', index=False
            , dtype={'id': INTEGER,
                     'p_name': VARCHAR(20),
                     'dept': VARCHAR(20),
                     'update_date': Date})

ERROR:

DatabaseError: (teradata.api.DatabaseError) (9719, '[HY000] [Teradata][ODBC Teradata Driver][Teradata Database](-9719)QVCI feature is disabled.')
[SQL: SELECT * 
FROM dbc.ColumnsQV 
WHERE DatabaseName (NOT CASESPECIFIC) = CAST(? as VARCHAR(128)) (NOT CASESPECIFIC) AND TableName=?]
[parameters: ('DB', 'TEST_LA_TEMP')]

When I use the same code to import to an NEW (non-existing) table, it works fine but returns CLOB as datatype for most columns

enter image description here

I then tried to CAST CLOB Types to the correct Types but it said CLOBs can only be converted to CHAR TO VARCHAR missing my update_date column.

I can get it working if I use if_exists='append' instead, and then drop and create table again. but I still want to understand why and how to resolve these errors.

Questions:

  1. What is the syntax to enable QVCI directly for Teradata?
  2. Is there a way to CAST CLOB to DATE? How?
  3. Why if_exists='replace' depends on QVCI but not if_exists='append'?

Thank you!

lvv
  • 41
  • 6
  • 2
    1) Don't enable QVCI, it's unsupported, and current Teradata drivers would not reference it. Perhaps you have both old and new driver installed it's still picking up the old one (e.g. you may need explicit driver= in your connection string). Consider using _teradatasqlalchemy_ vs _sqlalchemy_teradata plus ODBC_. 2) You can CAST first to VARCHAR then to DATE. 3) For "replace" the code is apparently trying to save the existing column attributes so it can DROP and re-CREATE under the covers. – Fred Sep 09 '20 at 16:34
  • In the future please limit each SO "question" to a single issue. To control the data types of the columns see the `dtype` argument in the [documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html) for `to_sql`. And as for your third question see the related answer [here](https://stackoverflow.com/a/60150030/2144390). – Gord Thompson Sep 09 '20 at 16:40
  • It's possible that the connection is still tied to the old driver, just like Fred said. QVCI issue has been addressed since v16.20 going forward. I would delete DSN and recreate it, making sure you link it to the proper driver. Also, make sure you are doing it on the machine that actually runs your code. If you connect to DB from an app server, your driver has to be updated there, and not on the client box. – access_granted Sep 11 '20 at 00:23

0 Answers0