Quand il s’agit de connecter des bases de données Teradata avec un script écrit en langage Python, la chose n’est pas aisée.
La documentation mise en ligne par Teradata manque de clareté quand il s’agit de connecter un script Python avec Teradata. Après moult tentatives, la meilleure manière – et qui fonctionne pour se connecter – a été écrite en anglais sur ce blog.
Voici en backup le mode d’emploi de connexion.
While doing some work for Raiteas, I recently needed to connect to a Teradata database from our Ubuntu server using an ODBC connection. Here are the steps (for when I need them later).
I am using Ubuntu 12.04 (also tested on 12.10), 64 bit version. On other operating systems you will need to make adjustments.
Unzip / untar the downloaded file tdodbc__linux_imdep.14.10.00.00-1.tar (http://downloads.teradata.com/download/connectivity/odbc-driver/linux) and also untar each of the 3 components within it to get 3 rpms. Convert these to deb format using Alien, and install using dpkg:
sudo apt-get install alien sudo alien tdodbc-14.10.00.00-1.noarch.rpm sudo alien tdicu-14.10.00.00-1.noarch.rpm sudo alien TeraGSS_linux_x64-14.10.00.06-1.noarch.rpm sudo dpkg -i tdodbc_14.10.00.00-2_all.deb sudo dpkg -i tdicu_14.10.00.00-2_all.deb sudo dpkg -i teragss-linux-x64_14.10.00.06-2_all.deb
Copy and modify the ODBC definitions:
cp /opt/teradata/client/14.10/odbc_64/odbc.ini ~/.odbc.ini cp /opt/teradata/client/14.10/odbc_64/odbcinst.ini ~/.odbcinst.ini
Edit them to replace all “TDODBC_INSTALL_DIR” with “/opt/teradata/client/14.10/odbc_64” and replace “TDODBC_LIB_DIR” with “lib”
Install pyodbc and odbcinst:
sudo apt-get install python-pyodbc odbcinst
Add the teradata files to ldconfig, by creating a new file, /etc/ld.so.conf.d/Zteradata.conf with the contents:
/opt/teradata/client/14.10/odbc_64/lib /opt/teradata/client/14.10/tdicu/lib64 /opt/teradata/teragss/linux-x8664/14.10.00.06/lib
Refresh the cache to include these new lib directories:
sudo ldconfig -v
At this point, the following python test script is useful:
import pyodbc pyodbc.pooling = False conn=pyodbc.connect('DRIVER={Teradata};DBCNAME=87.254.29.172;UID=dbc;PWD=dbc;QUIETMODE=YES;', ANSI=True, autocommit=True) curs=conn.cursor() curs.execute('select current_timestamp') print( curs.fetchone() )
The ANSI=True parameter in the test script is neccessary! Without it you will get an error when you run it with Python:
pyodbc.Error: ('IM002', '[IM002] [DataDirect][ODBC lib] Data source name not found and no default driver specified (0) (SQLDriverConnectW)')
At the moment, running the test script will fail with a cryptic error message:
eaaa[DCTrdt rvr nbet e aao tig
By default, the drivers point to the default (non teradata) files. See and Redirect them:
cd /usr/lib/x86_64-linux-gnu ls -lha | grep odbc (You should see the files below which are to be replaced). sudo mv libodbc.so.1.0.0 Xlibodbc.so.1.0.0 sudo ln -s /opt/teradata/client/14.10/odbc_64/lib/libodbc.so libodbc.so.1.0.0 sudo mv libodbcinst.so.1.0.0 Xlibodbcinst.so.1.0.0 sudo ln -s /opt/teradata/client/14.10/odbc_64/lib/libodbcinst.so libodbcinst.so.1.0.0
At this point the test script will still give an (at least now English) error when run:
pyodbc.Error: ('HY000', '[HY000] [Teradata][ODBC Teradata Driver] Major Status=0x04bd Minor Status=0xe0000007-[terasso]Cannot load TDGSS library. (0) (SQLDriverConnect)')
To fix this:
sudo mkdir /usr/teragss/site sudo cp -R /opt/teradata/teragss/linux-x8664/14.10.00.06/etc/* /usr/teragss/site sudo mkdir /usr/teragss/linux-x8664 sudo mkdir /usr/teragss/linux-x8664/client cd /usr/teragss/linux-x8664/client sudo ln -s /opt/teradata/teragss/linux-x8664/14.10.00.06/lib lib cd /opt/teradata/teragss/linux-x8664/14.10.00.06/bin/ sudo ./run_tdgssconfig
You should now be able to connect using the test script above. There may still be problems if you are using unicode (passing unicode strings as parameters to curs.execute() caused problems that I overcame by converting them to ASCII. There is probably a better solution.
curs.execute( SQL, (someStringParameter.encode('ascii', 'ignore')) )
This whole process took 2 of us several hours to muddle through, possibly some of the steps are not neccessary / could be improved. While debugging, we made heavy use of “strace somebinary.so 2>&1 | grep odbc”, and “ldd” on binaries. These 2 blog posts also helped immensely: http://forums.teradata.com/forum/connectivity/getting-both-teradata-odbc-and-aster-odbc-working-together-with-the-pyodbc-python http://cjgoldsmith.blogspot.co.uk/2011/03/teradata-odbc-python-pyodbc-on-fedora.html
Encore merci aux développeurs d’avoir mis en ligne leur mode d’emploi.
[aff cat= »livre-sql »]