search

Teradata / PyTd

A Python Module to make it easy to script powerful interactions with Teradata Database in a DevOps friendly way.
MIT License
108 stars 43 forks source link

Teradata not recognizing drivers when using conda. #108

Closed aborsu closed 4 years ago

aborsu commented 6 years ago

I've taken the liberty to create a new issue as the no drivers recognized has a couple of issues open already, but I think the problem comes from the use of a conda install of python rather than a system install (I'm not sure).

When using tdxodbc everything works even from python, but from conda the drivers are not recognized. I believe that when using conda, it by defaults uses the libodbc.so library that is packaged with conda rather than the system one.

$ export ODBCINI=/home/users/some_user/odbc.ini
$ export ODBCINST=/home/users/some_user/odbcinst.ini
$ /opt/teradata/client/16.10/bin/tdxodbc64 -C "DRIVER=Teradata; DBCNAME=some_db; UID=some_user; PWD=some_password;"

Connecting with SQLDriverConnect("DRIVER=Teradata; DBCNAME=some_db; UID=some_user; PWD=*;")...

.....ODBC connection successful.

$ ipython
Python 3.6.1 |Anaconda custom (64-bit)| (default, May 11 2017, 13:09:58)
Type "copyright", "credits" or "license" for more information.

IPython 5.3.0 -- An enhanced Interactive Python.
?         -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help      -> Python's own help system.
object?   -> Details about 'object', use 'object??' for extra details.

In [1]: !/opt/teradata/client/16.10/bin/tdxodbc64 -C "DRIVER=Teradata; DBCNAME=some_db; UID=some_user; PWD=some_password;"

Connecting with SQLDriverConnect("DRIVER=Teradata; DBCNAME=some_db; UID=some_user; PWD=*;")...

.....ODBC connection successful.

In [2]: import teradata

In [3]: udaexec = teradata.UdaExec(appName="HelloWorld",
   ...:                            version="1.0",
   ...:                            logConsole=True,
   ...:                            logLevel='DEBUG')
2018-06-21 11:08:03,587 - teradata.udaexec - INFO - Initializing UdaExec...
2018-06-21 11:08:03,587 - teradata.udaexec - INFO - Reading config files: ['/etc/udaexec.ini: Not Found', '/home/users/some_user/udaexec.ini: Not Found', '/home/users/some_user/udaexec.ini: Not Found']
2018-06-21 11:08:03,587 - teradata.udaexec - INFO - Found run number file: "/home/users/some_user/.runNumber"
2018-06-21 11:08:03,587 - teradata.udaexec - INFO - Cleaning up log files older than 90 days.
2018-06-21 11:08:03,587 - teradata.udaexec - INFO - Removed 0 log files.
2018-06-21 11:08:03,587 - teradata.udaexec - DEBUG - Git path not specified, using system path.
2018-06-21 11:08:03,773 - teradata.udaexec - DEBUG - Git information is not available: fatal: Not a git repository (or any parent up to mount point /home/users)
Stopping at filesystem boundary (GIT_DISCOVERY_ACROSS_FILESYSTEM not set).
.
2018-06-21 11:08:03,784 - teradata.udaexec - INFO - Checkpoint file not found: /home/users/some_user/HelloWorld.checkpoint
2018-06-21 11:08:03,784 - teradata.udaexec - INFO - No previous checkpoint found, executing from beginning...
2018-06-21 11:08:03,785 - teradata.udaexec - INFO - Execution Details:
/********************************************************************************
 * Application Name: HelloWorld
 *          Version: 1.0
 *       Run Number: 20180621110803-121
 *             Host: spal0062
 *         Platform: Linux-2.6.32-696.6.3.el6.x86_64-x86_64-with-redhat-6.6-Santiago
 *          OS User: some_user
 *   Python Version: 3.6.1
 *  Python Compiler: GCC 4.4.7 20120313 (Red Hat 4.4.7-1)
 *     Python Build: ('default', 'May 11 2017 13:09:58')
 *  UdaExec Version: 15.10.0.21
 *     Program Name: /appl/icpy01/opt/customer_analytics/bin/ipython
 *      Working Dir: /home/users/some_user
 *          Log Dir: /home/users/some_user/logs
 *         Log File: /home/users/some_user/logs/HelloWorld.20180621110803-121.log
 *     Config Files: ['/etc/udaexec.ini: Not Found', '/home/users/some_user/udaexec.ini: Not Found', '/home/users/some_user/udaexec.ini: Not Found']
 *      Query Bands: ApplicationName=HelloWorld;Version=1.0;JobID=20180621110803-121;ClientUser=some_user;Production=False;udaAppLogFile=/home/users/some_user/logs/HelloWorld.20180621110803-121.log;UtilityName=PyTd;UtilityVersion=15.10.0.21
********************************************************************************/
2018-06-21 11:08:03,787 - teradata.udaexec - DEBUG - Configuration Details:
/********************************************************************************
 *   appName: HelloWorld
 * runNumber: 20180621110803-121
 *   version: 1.0
********************************************************************************/

In [4]: session = udaexec.connect(method="odbc", DRIVER='Teradata', system="some_db", username="some_user", password="some_password");
2018-06-21 11:08:30,593 - teradata.udaexec - INFO - Creating connection: {'method': 'odbc', 'DRIVER': 'Teradata', 'system': 'some_db', 'username': 'some_user', 'password': 'XXXXXX'}
2018-06-21 11:08:30,604 - teradata.tdodbc - INFO - Loading ODBC Library: libodbc.so
2018-06-21 11:08:30,605 - teradata.tdodbc - INFO - Available drivers:
2018-06-21 11:08:30,606 - teradata.udaexec - ERROR - Unable to create connection: {'method': 'odbc', 'DRIVER': 'Teradata', 'system': 'some_db', 'username': 'some_user', 'password': 'XXXXXX'}
Traceback (most recent call last):
  File "/appl/wrdi01/data/shared/apm/python/lib/python3.6/site-packages/teradata/udaexec.py", line 183, in connect
    **args))
  File "/appl/wrdi01/data/shared/apm/python/lib/python3.6/site-packages/teradata/tdodbc.py", line 427, in __init__
    connectParams["DRIVER"] = determineDriver(dbType, driver)
  File "/appl/wrdi01/data/shared/apm/python/lib/python3.6/site-packages/teradata/tdodbc.py", line 391, in determineDriver
    "Available drivers: {}".format(dbType, ",".join(drivers)))
teradata.api.InterfaceError: ('DRIVER_NOT_FOUND', "No driver found for 'Teradata'.  Available drivers: ")
---------------------------------------------------------------------------
InterfaceError                            Traceback (most recent call last)
<ipython-input-5-d9bf8a0580c4> in <module>()
----> 1 session = udaexec.connect(method="odbc", DRIVER='Teradata', system="some_db", username="some_user", password="some_password");

/appl/wrdi01/data/shared/apm/python/lib/python3.6/site-packages/teradata/udaexec.py in connect(self, externalDSN, dataTypeConverter, **kwargs)
    181                                          odbcLibPath=self.odbcLibPath,
    182                                          dataTypeConverter=dataTypeConverter,
--> 183                                          **args))
    184             else:
    185                 raise api.InterfaceError(

/appl/wrdi01/data/shared/apm/python/lib/python3.6/site-packages/teradata/tdodbc.py in __init__(self, dbType, system, username, password, autoCommit, transactionMode, queryBands, odbcLibPath, dataTypeConverter, driver, **kwargs)
    425         connectParams = collections.OrderedDict()
    426         if "dsn" not in extraParams:
--> 427             connectParams["DRIVER"] = determineDriver(dbType, driver)
    428         if system:
    429             connectParams["DBCNAME"] = system

/appl/wrdi01/data/shared/apm/python/lib/python3.6/site-packages/teradata/tdodbc.py in determineDriver(dbType, driver)
    389                 "DRIVER_NOT_FOUND",
    390                 "No driver found for '{}'.  "
--> 391                 "Available drivers: {}".format(dbType, ",".join(drivers)))
    392         else:
    393             retval = matches[len(matches) - 1]

InterfaceError: ('DRIVER_NOT_FOUND', "No driver found for 'Teradata'.  Available drivers: ")
msarahan commented 6 years ago

Hi, I work for Anaconda, and we'd like to understand what's going wrong here. Unfortunately, I don't know enough about Teradata to know what the problem is yet. It looks to me like /opt/teradata/client/16.10/bin/tdxodbc64 is a binary in a totally separate world - one that presumably finds its own libodbc. Whatever libodbc we're providing via conda presumably isn't the correct one. We'd be happy to point the python teradata at the correct one if we knew what it should be - or better yet, how to build a package with the correct one.

nehaljwani commented 6 years ago

I was able to reproduce this problem. The issue is pretty straight forward. If we take a look at the debug output:

2018-06-21 11:08:30,604 - teradata.tdodbc - INFO - Loading ODBC Library: `libodbc.so`
2018-06-21 11:08:30,605 - teradata.tdodbc - INFO - Available drivers:

This debug information provides us two clues:

What it doesn't tell you:

Let's take a look at:

# ls -l /usr/lib64/lib{odbc,odbcinst,ddicu28}.so
lrwxrwxrwx. 1 root root 45 Aug  3 06:36 /usr/lib64/libddicu28.so -> /opt/teradata/client/16.20/lib64/libddicu28.so
lrwxrwxrwx. 1 root root 42 Aug  3 06:36 /usr/lib64/libodbc.so -> /opt/teradata/client/16.20/lib64/libodbc.so
lrwxrwxrwx. 1 root root 46 Aug  3 06:36 /usr/lib64/libodbcinst.so -> /opt/teradata/client/16.20/lib64/libodbcinst.so

These are all symlinks created by the installation of the tdodbc driver (in my case, tdodbc1620-16.20.00.36-1.noarch.rpm)

Now, with a vanilla installation of Miniconda and then conda install teradata or pip install teradata, I don't see this problem. That's because it ends up loading the correct libodbc.so via ctypes.cdll.LoadLibrary() .

However, when I had installed unixodbc in the same environment, the problem reported by @aborsu popped up, with the exact error message. To make it go away, I had to uninstall unixodbc, because it provides it's own odbc shared library which goes by the same name: libodbc.so and is sits at $CONDA_PREFIX/lib/libodbc.so, which is preferred by ctypes.cdll.LoadLibrary().

However, it is not necessary that libodbc.so is provided only by the unixodbc conda package. It could have been provided by an installation by the system package manager too. And when that happens, it could also break the symlinks shown above.

So, to summarize, the problem really is that ctypes.cdll.LoadLibrary('libodbc.so') is loading the wrong shared library. To verify that, one can use the following snippet:

import ctypes.util
from pathlib import Path

libdl = ctypes.CDLL(ctypes.util.find_library('dl'))
libodbc = ctypes.cdll.LoadLibrary('libodbc.so')

class linkmap(ctypes.Structure):
    _fields_ = (('l_addr', ctypes.c_void_p),
                ('l_name', ctypes.c_char_p))

libdl.dlinfo.argtypes = (ctypes.c_void_p, ctypes.c_int, ctypes.c_void_p)

if __name__ == '__main__':
    import sys

    lmptr = ctypes.c_void_p()
    result = libdl.dlinfo(libodbc._handle, 2, ctypes.byref(lmptr))
    abspath = ctypes.cast(lmptr, ctypes.POINTER(linkmap)).contents.l_name

    if result == 0 and abspath:
        abspath = Path(abspath.decode(sys.getfilesystemencoding())).resolve()
    else:
        abspath = u'Not Found'

    print(u'libdbc.so path: %s' % abspath)

With unixodbc in the environment:

(tdata) [root@somemachine ~] # python /path/to/snippet.py
libdbc.so path: /tdata/lib/libodbc.so.2.0.0

Without unixodbc in the conda environment and no installation by system package manager:

(tdata) [root@somemachine ~] # python /path/to/snippet.py
libdbc.so path: /opt/teradata/client/16.20/lib64/libodbc.so

In case of unixodbc being installed in the same conda environment, /opt/teradata/client/16.10/bin/tdxodbc64 is unaffected because it has libodbc.so in it's DT_NEEDED section and picks it up from /usr/lib64/libodbc.so, which is a symlink to the correct driver as shown above.

nehaljwani commented 6 years ago

Another way to mitigate this issue is to pass the argument odbcLibPath:

from teradata import tdodbc
session = tdodbc.connect(method="odbc", ...  odbcLibPath="/path/to/teradata/libodbc.so" );

In OP's case, it would be: /opt/teradata/client/16.10/lib64/libodbc.so

aborsu commented 6 years ago

@nehaljwani I've tried this but it doesn't work either. I also have No driver found for Teradata. Unfortunately I cannot uninstall unixodbc from the environment as I need to communicate with oracle databases from the same environment so i'm stuck using pyodbc for the moment. This means no sqlAlchemy and all errors (syntax authentication, ...) are presented as a generic error.

nehaljwani commented 6 years ago

@aborsu I patched the taradata package and changed the line:

odbc = ctypes.cdll.LoadLibrary(odbcLibPath)

to:

odbc = ctypes.cdll.LoadLibrary("/absolute/path/to/teradata/driver/provided/libodbc.so")

And now along with ODBCINST and ODBCINI pointing to the correct ini files provided by the teradata odbc driver package, everything works. I don't even have to remove unixodbc.

mrianclarito commented 6 years ago

Hi team,

I am trying the suggested workarounds for the exact same scenario (unixodbc installed and has been removed, trying to pytd module via Anaconda returns "DRIVER_NOT_FOUND") by explicitly defining the libodbc.so actual path inside the tdodbc.py file:

def initOdbcLibrary(odbcLibPath=None): ... odbcLibPath = "/opt/teradata/client/ODBC_64/lib/libodbc.so" odbc = ctypes.cdll.LoadLibrary(odbcLibPath) ...

Even with this I am still getting the error. I wanted to check if there's a way to debug from the code where it returns the "DRIVER_NOT_FOUND" -- would you have any ideas? def initDriverList(): ... rc = SQL_SUCCESS ... rc = odbc.SQLDrivers(hEnv, direction, description, len(description), ADDR(descriptionLength), None, 0, attributesLength) #rc returns a value of "100" which is SQL_NO_DATA => DRIVER_NOT_FOUND ...

Thanks.

aborsu commented 6 years ago

Hello,

Same thing for me, specifying the odbcLibPath does not resolve the issue.

awburgess commented 5 years ago

Hey all,

I'm having the exact same issue. Even if I set ODBCINI and/or ODBCINST environment variables, I still get no available drivers. Desperate for any solution. This is using a conda Python 3 instance.

aborsu commented 4 years ago

We switched to the new teradatasql package. No issue anymore

HerculesGomez commented 4 years ago

Had the same issue. Tried many of the above but nothing worked. I removed anaconda and installed python36 and it is working now on oracle linux.

This is all I have in my .bash_profile: export ODBCINI=/opt/teradata/client/ODBC_64/odbc.ini

And then the contents of /opt/teradata/client/ODBC_64/odbc.ini are: [ODBC Drivers] Teradata Database ODBC Driver 16.20=Installed

[Teradata Database ODBC Driver 16.20] Description=Teradata Database ODBC Driver 16.20 Driver=/opt/teradata/client/ODBC_64/lib/tdataodbc_sb64.so CPTimeout=60

[ODBC] InstallDir=/opt/teradata/client/ODBC_64 Trace=no Pooling=yes

[ODBC Data Sources] Teradata ODBC DSN=Teradata Database ODBC Driver 16.20

[TDP6] Description=Teradata Database ODBC Driver 16.20

Driver: The location where the ODBC driver is installed to.

Driver=/opt/teradata/client/ODBC_64/lib/tdataodbc_sb64.so

Required: These values can also be specified in the connection string.

DBCName1=<put your teradata ip here> UID= PWD=

Hope this helps!