Re:[Proposal] Support ODBC Scan Node for Doris

[41108453]

This will cause users who currently use doris to be unable to smoothly upgrade 
to the new version, especially those who use the WITH_MYSQL syntax
Can this syntax be retained and extended to support other databases such as 
WITH_ORACLE, WITH_POSTGRESQL, etc.?





------------------ Original ------------------
From:                                                                           
                                             "dev"                              
                                                      
<happen...@hotmail.com&gt;;
Date:&nbsp;Mon, Aug 17, 2020 04:00 PM
To:&nbsp;"dev@doris.apache.org"<dev@doris.apache.org&gt;;

Subject:&nbsp;[Proposal] Support ODBC Scan Node for Doris




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:

&nbsp; 1.&nbsp; 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.

&nbsp; 1.&nbsp; ODBC help Doris have a unified development interface instead of 
focusing on the connection implementation of different databases.

&nbsp; 2.&nbsp; 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` (
&nbsp; `k1` decimal(9, 3) NOT NULL COMMENT "",
&nbsp; `k2` char(10) NOT NULL COMMENT "",
&nbsp; `k3` datetime NOT NULL COMMENT "",
&nbsp; `k5` varchar(20) NOT NULL COMMENT "",
&nbsp; `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??

&nbsp; *&nbsp;&nbsp; type?? To identify which database the table belongs to. 
For different databases, there are some differences in ODBC connection and 
query.

&nbsp; *&nbsp;&nbsp; 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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = /usr/lib/psqlodbc.so
Setup&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = /usr/lib/libodbcpsqlS.so
Driver64&nbsp;&nbsp;&nbsp; = /usr/lib64/psqlodbc.so
Setup64&nbsp;&nbsp;&nbsp;&nbsp; = /usr/lib64/libodbcpsqlS.so
FileUsage&nbsp;&nbsp; = 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL ODBC 8.0 Unicode Driver]
Description = ODBC for MySQL
Driver&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = 
/home/happen/mysql/mysql_odbc_rpm/usr/lib64/libmyodbc8w.so
FileUsage&nbsp;&nbsp; = 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

&nbsp; 1.&nbsp; First, we should add third party static library of Unix ODBC.

&nbsp; 2.&nbsp; Second??Modify thrift file to determine the interface of ODBC 
scan node. like??

&nbsp; 1.&nbsp; Modify the code of be part, implementation ODBC_SCAN_NODE and 
ODBC_SCANNER

&nbsp; 2.&nbsp; Modify the code of FE part to support adding external tables of 
ODBC engine.

Some Problem Need To Talk

&nbsp; 1.&nbsp; 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.

&nbsp; 2.&nbsp; odbc scan node should support old external table of MySQL.

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 Best Wish
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 HappenLee