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:
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
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:
- What is the syntax to enable QVCI directly for Teradata?
- Is there a way to CAST CLOB to DATE? How?
- Why
if_exists='replace'
depends on QVCI but notif_exists='append'
?
Thank you!