Hi Happen, I have a question. If user already has a Mysql external table which uses the mysql driver, is it all right when user updates new version of Doris?
Ling Miao Lee Happen <happen...@hotmail.com> 于2020年8月17日周一 下午4:00写道： > > Motivation > > At present, many customers need to access the external table through > Doris, but Doris only supports accessing the external table of MySQL at > present. This problem makes it difficult for users of other databases to > use Doris. > > Open Database Connectivity (ODBC) is a standard application programming > interface for accessing database management systems: > https://en.wikipedia.org/wiki/Open_Database_Connectivity > > So should we support ODBC Scan node as a general external table access > method, it is provided to users. user can use Driver Manager of ODBC to > determine the databases that need to be accessed. > > It can bring us the following benefits: > > 1. First, Doris can eazily support a variety of different databases as > external table, likes: Oracle, PostgreSQL, SQLServer. Not only RDMS, ODBC > also support some NoSQL database. likes: Redis, MongoDB. > > 1. ODBC help Doris have a unified development interface instead of > focusing on the connection implementation of different databases. > > 2. UNIX ODBC is a static library based on LGPL, which can avoid the > commercial risk of using different database client APIs > > How To Use > > It is very similar to the previous use of MySQL external table. The table > creation statement is as follows. > > CREATE EXTERNAL TABLE `baseall_oracle` ( > `k1` decimal(9, 3) NOT NULL COMMENT "", > `k2` char(10) NOT NULL COMMENT "", > `k3` datetime NOT NULL COMMENT "", > `k5` varchar(20) NOT NULL COMMENT "", > `k6` double NOT NULL COMMENT "" > ) ENGINE=ODBC > PROPERTIES ( > "host" = "192.168.1.2", > "port" = "4234", > "user" = "happenlee", > "password" = "happenlee", > "database" = "happen", > "table" = "baseall", > "driver" = "Oracle 19 ODBC driver", > "type" = "oracle" > ); > > > Noted that it needs to add some additional info： > > * type： To identify which database the table belongs to. For different > databases, there are some differences in ODBC connection and query. > > * driver： To identify which odbc driver the table use. Use should > installed proper driver and config it in be/conf/odbcinst.ini like： > > # Example driver definitions > > # Driver from the postgresql-odbc package > # Setup from the unixODBC package > [PostgreSQL] > Description = ODBC for PostgreSQL > Driver = /usr/lib/psqlodbc.so > Setup = /usr/lib/libodbcpsqlS.so > Driver64 = /usr/lib64/psqlodbc.so > Setup64 = /usr/lib64/libodbcpsqlS.so > FileUsage = 1 > > > # Driver from the mysql-connector-odbc package > # Setup from the unixODBC package > [MySQL ODBC 8.0 Unicode Driver] > Description = ODBC for MySQL > Driver = /home/happen/mysql/mysql_odbc_rpm/usr/lib64/libmyodbc8w.so > FileUsage = 1 > > [Oracle 19 ODBC driver] > Description=Oracle ODBC driver for Oracle 19 > Driver=/usr/lib/oracle/19.8/client64/lib/libsqora.so.19.1 > Setup= > FileUsage= > CPTimeout= > CPReuse= > > > > Implementation > > 1. First, we should add third party static library of Unix ODBC. > > 2. Second，Modify thrift file to determine the interface of ODBC scan > node. like： > > 1. Modify the code of be part, implementation ODBC_SCAN_NODE and > ODBC_SCANNER > > 2. Modify the code of FE part to support adding external tables of ODBC > engine. > > Some Problem Need To Talk > > 1. Now the odbc driver of mysql have a function library conflict with > mysql-server-mysql-5.7.18，which may cause core of BE. So we should disable > WITH_MYSQL compile options，when we need to use ODBC_SCAN_NODE to connect > mysql. > > 2. odbc scan node should support old external table of MySQL. > > > Best Wish > > HappenLee > > >
