Comment connecter Python avec des bases de données Teradata

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 »]