Teradata Python Module

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Enthusiast

Re: Teradata Python Module

This module is awesome and I've been able to run queries and return to Pandas for data analysis, which is great! I'm having an issue with writing queries that create volatile tables. I keep getting an error: (3932, '[25000] [Teradata][ODBC Teradata Driver][Teradata Database] Only an ET or null statement is legal after a DDL Statement. ')

 

Can anyone help with this?

 

import teradata as td
import pandas as pd
from teradata import tdodbc

 

udaExec = td.UdaExec (appName="dpull", version="1.0",logConsole=False)

with udaExec.connect(method="ODBC", dsn="tdata",username="un", password="pw") as session:

 

query = """
CREATE VOLATILE TABLE vt_1
,NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL AS

(
SELECT
id
,seq_num
,key

from src as src

)
WITH DATA PRIMARY INDEX (id)
ON COMMIT PRESERVE ROWS;

 

SELECT
vt.id
,vt.seq_num
,vt.key
,t2.adj_rank

FROM vt_1 as vt

inner join table2 as t2

on vt.id = t2.id

"""
df = pd.read_sql(query,session)

Enthusiast

Re: Teradata Python Module

Does the query work on Teradata Assistant or BTEQ?

You could split the query:

query1 = """
CREATE VOLATILE TABLE vt_1
,NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL AS

(
SELECT
id
,seq_num
,key

from src as src



)
WITH DATA PRIMARY INDEX (id)
ON COMMIT PRESERVE ROWS; """



query2 =
"""

SELECT
vt.id
,vt.seq_num
,vt.key
,t2.adj_rank

FROM vt_1 as vt

inner join table2 as t2

"""

session.excute(query1)
df = pd.read_sql(query2,session)

Because you are in the same session you VOL table will still be accessible.

Looking at your second SELECT are we not missing ON conditions of our link between vt_1 and t2?
Enthusiast

Re: Teradata Python Module

Thanks so much for your help! I tried your script and got a different error. I did have a typo missing the ON in the example query. If I run the query I receive:

 


File "\teradata\tdodbc.py", line 192, in getDiagnosticInfo
"Invalid handle passed to SQLGetDiagRecW.")

InterfaceError: Invalid handle passed to SQLGetDiagRecW.

 

I'm not sure if it's assistant or BTEQ, how would I know?

Enthusiast

Re: Teradata Python Module

Okay, I must've had some issue with my SQL code, because that worked!! Thanks so much for the help. You can actually do several queries following the logic you provided:

 

session.execute(query1)
session.execute(query2)
df = pd.read_sql(query3,session)

Enthusiast

Re: Teradata Python Module - help with udaexec.ini

I can't seem to get the module to recognize my udaexec.ini config file. I made sure it's saved in the same directory as the ipython notebook that I'm using to call the query. Below is my initial code and I have the file configured based on the instructions provided. Any idea what I could

 

 

import teradata as td
import pandas as pd
from teradata import tdodbc

udaExec = td.UdaExec()

with udaExec.connect("{$udaexec.ini}") as session:
InterfaceError: ('CONFIG_ERROR', 'Unable to resolve "{$udaexec.ini}".  Parameter not found: \'udaexec.ini\'.  If parameter substitution is not intended, escape \'$\' by adding another \'$\'.')

 

Highlighted
Teradata Employee

Re: Teradata Python Module - help with udaexec.ini

Two things wrong:

The $ needs to be outside the { and the value inside the { } needs to be a keyword parameter name found in the [CONFIG] section within the udaexec.ini file - not the name of the file itself.

 

Enthusiast

Re: Teradata Python Module - help with udaexec.ini

Thank you for your help! I realized the typo after looking at it, but the rest wasn't quite as straightforward, but was able to figure it out. I realize I had to enter my data source name/address in the "system" parameter and then use the below:

 

udaExec = td.UdaExec(appConfigFile="udaexec.ini")

with udaExec.connect("${dataSourceName}") as session: