ODBC Connector
ODBC Connector
Plugin module Data provider name Automatic metadata detection
Yes
ODBC ODBC
The ODBC connector allows external tables to fetch data from other databases using ODBC calls. If the remote DBMS is
Kognitio 7.9 or later, use the unloader connector for better performance. See the Unloader Connector reference sheet.
Prerequisites
•
•
•
(Recommended) An ODBC driver manager such as UnixODBC, providing libodbc.so, installed on all nodes.
An ODBC driver for the external DBMS you want to connect to, in the same place on all Kognitio DB nodes.
Connectivity to this external DBMS from all Kognitio DB nodes.
Examples
Load the plugin Create an external table create module odbc; create external table customer2013 from myodbccon target 'query
"select * from customer where year=2013", splitexp customer_id'; alter module odbc set mode active;
Create a connector (using driver manager-defined DSN) create connector mysqlconnector source odbc target 'driver libodbc.so, connect "DSN=mydsn;UID=fred;PWD=trellis;DBNAME=mydb"';
Create a connector (without driver manager-defined DSN) create connector mysqlconnector source odbc target 'driver libodbc.so, connect "DRIVER=MySQL Driver;Server=172.30.21.1;UID=fred;PWD=trellis;DBNAME=mydb"';
Attributes
Attribute Type Default Description driver kognitio string
Path to the ODBC driver. If the value of this attribute is kognitio, the Kognitio ODBC driver is used directly without using a driver manager. To use a driver manager, specify libodbc.so, a file which should be provided by the driver manager. bitness connect (32 or
64) integer 32 if driver is otherwise Specify whether the driver is 32-bit or 64-bit. If you are using a driver manager, this is kognitio, 64 the bitness of your driver manager's libodbc.sofile. The driver itself must be of the same bitness. If the driverattribute is kognitio, the default is 32, otherwise it is 64. string none The ODBC connection string to connect with, for example (required)
DSN=mydsn;UID=fred;PWD=thunk. This example connection string assumes that a dsn called mydsnis defined in the driver manager's config files on all nodes. none string The query to run on the remote system to fetch the data. query (required) splitexp string none If specified, each loader thread (one per ramstore) runs the query with the extra condition:
WHERE splitexp mod number_of_loader_threads = loader_id
Therefore, splitexp should be a column name or expression which evaluates to an integer suitable for partitioning the data between threads, e.g. the table's primary key. If splitexpis not given, the whole load will be performed by one thread. extcharset integer 1021 (UTF-8) If the ODBC driver does not return strings in UTF-8 when the locale is UTF-8, set this attribute to tell it what character encoding it uses, e.g. 1 for LATIN-1. The attributes extcharset1, extcharset2, etc, can be set to override the setting for individual result columns.
Note: The generic target string format attributes, which begin with "fmt_", have no effect for this connector.
Notes
•
The ODBC connector uses ODBC calls to determine the column types, so when you create the external table you don't need to give a column list. The plugin will connect to the server, find out the column types and the table will be created appropriately. If you do give a column list, it overrides what the plugin would have done. Note that if the table's column types on the remote system subsequently change, the external table will need to be recreated.
Last updated: 08 August 2017 Data classification: PUBLIC