Doris ODBC外表使用

PostgreSQL

Doris Version:Baidu Palo 0.15.1

参考资料

Apache Doris ODBC外表之Postgresql使用指南
https://zhuanlan.zhihu.com/p/452442524

Linux/Unix ODBC的原理、安装、配置和编程
http://blog.chinaunix.net/uid-22312037-id-3485506.html

通过外部表同步数据
http://palo.baidu.com/docs/%E6%93%8D%E4%BD%9C%E6%89%8B%E5%86%8C/%E6%95%B0%E6%8D%AE%E5%AF%BC%E5%85%A5/%E9%80%9A%E8%BF%87%E5%A4%96%E9%83%A8%E8%A1%A8%E5%90%8C%E6%AD%A5%E6%95%B0%E6%8D%AE

开始：

一开始没有在系统上安装ODBC驱动和postgresql odbc驱动，直接建表会有如下提示

于是开始

配置ODBC驱动

sudo wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.4.tar.gz
tar -zxvf unixODBC-2.3.4.tar.gz
cd unixODBC-2.3.4/
sudo ./configure --prefix=/usr/local/unixODBC-2.3.7 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etc
make && sudo make install

/usr/inlucde：头文件存储路径
/usr/lib：库文件存储路径
/usr/bin：可执行文件存储路径
/etc：配置文件存储路径

验证是否安装成功
odbcinst -j

安装Postgresql ODBC驱动

在数据库中查看数据库版本

select version();
PostgreSQL 10.7 (Debian 10.7-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

Postgresql ODBC驱动下载地址：https://www.postgresql.org/ftp/odbc

# wget https://ftp.postgresql.org/pub/odbc/versions/src/psqlodbc-10.03.0000.tar.gz
# tar -zxvf psqlodbc-10.03.0000.tar.gz
# cd psqlodbc-10.03.0000
# apt-get update
# apt-get install libpq-dev
# ./configure --without-libpq   (注：由于本机未安装postgresql，故使用without-libpq选项) 
# ./configure
# make && make install

验证ODBC驱动是否成功
配置注册Postgresql ODBC驱动
vim /etc/odbcinst.ini

[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/local/lib/psqlodbcw.so
Driver64 = /usr/local/lib/psqlodbcw.so
Setup = /usr/lib/libodbc.so    ##注意这里是在第二节安装的unixODBC的so文件路径
Setup64 = /usr/lib/libodbc.so
FileUsage = 1

配置PG 数据源
vim /etc/odbc.ini

[PostgresDB]
Driver = PostgreSQL    ###这里的名称和odbcinst.ini里配置的名称一致
Description = Postgres DSN
Servername = localhost
Database = demo
Username = dbuser
Password = dbpasswd
Port = 5432
ReadOnly = No

验证是否成功
isql -v PostgresDB dbuser dbpasswd

修改doris配置

修改BE节点conf/odbcinst.ini文件,加入刚才/etc/odbcinst.ini添加的一样内容，并删除原先的PostgreSQL配置

[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/local/lib/psqlodbcw.so
Driver64 = /usr/local/lib/psqlodbcw.so
Setup = /usr/lib/libodbc.so
Setup64 = /usr/lib/libodbc.so
FileUsage = 1

在doris中配置odbc resource

CREATE EXTERNAL RESOURCE `oracle_odbc`
PROPERTIES (
    "type" = "odbc_catalog",
    "host" = "192.168.0.1",
    "port" = "8086",
    "user" = "test",
    "password" = "test",
    "database" = "test",
    "odbc_type" = "oracle",
    "driver" = "Oracle"
);

创建了一个名为 oracle_odbc 的 Resource，其类型为 odbc_catalog，表示这是一个用于存储 ODBC 信息的 Resource。odbc_type 为 oracle，表示这个 OBDC Resource 是用于连接 Oracle 数据库的。关于其他类型的资源，具体可参阅 资源管理 文档。

创建外部表

CREATE EXTERNAL TABLE `ext_oracle_tbl` (
  `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
COMMENT "ODBC"
PROPERTIES (
    "odbc_catalog_resource" = "oracle_odbc",
    "database" = "test",
    "table" = "baseall"
);

这里我们创建一个 ext_oracle_tbl 外部表，并引用了之前创建的 oracle_odbc Resource。

postgresql实现mysql中的show create table XXX

引用自:
https://www.iteye.com/blog/xiaofengtoo-1815103

定义公用函数: findattname

CREATE OR REPLACE FUNCTION findattname(namespace character varying, tablename character varying, ctype character varying)  
  RETURNS character varying AS  
$BODY$  
  
declare  
tt oid ;  
aname character varying default '';  
  
begin  
       tt := oid from pg_class where relname= tablename   
    and relnamespace =(select oid from pg_namespace  where nspname=namespace) ;  
    -- select oid from pg_namespace where nspowner=(select datdba from pg_database where datname=namespace)  
        
       aname:=  array_to_string(  
        array(  
               select a.attname  from pg_attribute  a   
                where a.attrelid=tt and  a.attnum   in (          
                select unnest(conkey) from pg_constraint c where contype=ctype   
                and conrelid=tt  and array_to_string(conkey,',') is not null    
            )   
        ),',')  
    ;  
      
    return aname;  
end   
      
      
    $BODY$  
  LANGUAGE plpgsql VOLATILE  
  COST 100;  

showcreatetable：

CREATE OR REPLACE FUNCTION showcreatetable(namespace character varying, tablename character varying)  
  RETURNS character varying AS  
    
$BODY$  
declare   
tableScript character varying default '';  
  
begin  
-- columns  
tableScript:=tableScript || ' CREATE TABLE '|| tablename|| ' ( '|| array_to_string(  
  array(  
select concat( c1, c2, c3, c4, c5, c6 ) as column_line  
from (  
  select column_name || ' ' || data_type as c1,  
    case when character_maximum_length > 0 then '(' || character_maximum_length || ')' end as c2,  
    case when numeric_precision > 0 and numeric_scale < 1 then '(' || numeric_precision || ')' end as c3,  
    case when numeric_precision > 0 and numeric_scale > 0 then '(' || numeric_precision || ', ' || numeric_scale || ')' end as c4,  
    case when is_nullable = 'NO' then ' NOT NULL' end as c5,  
    case when column_default is not Null then ' DEFAULT' end || ' ' || replace(column_default, '::character varying', '') as c6  
  from information_schema.columns  
  where table_name = tablename  
  -- and table_schema=namespace  
  order by ordinal_position  
) as string_columns  
),' , ') ||',' ;  
  
  
-- 约束  
tableScript:= tableScript || array_to_string(  
array(  
    select concat(' CONSTRAINT ',conname ,c ,u,p,f)   from (  
        select conname,  
        case when contype='c' then  ' CHECK('|| consrc ||')' end  as c  ,  
        case when contype='u' then  ' UNIQUE('|| ( select findattname(namespace,tablename,'u') ) ||')' end as u ,  
        case when contype='p' then ' PRIMARY KEY ('|| ( select findattname(namespace,tablename,'p') ) ||')' end  as p  ,  
        case when contype='f' then ' FOREIGN KEY('|| ( select findattname(namespace,tablename,'u') ) ||') REFERENCES '||   
        (select p.relname from pg_class p where p.oid=c.confrelid )  || '('|| ( select findattname(namespace,tablename,'u') ) ||')' end as  f  
        from pg_constraint c  
        where contype in('u','c','f','p') and conrelid=(   
            select oid  from pg_class  where relname=tablename and relnamespace =(  
            select oid from pg_namespace where nspowner=(select datdba from pg_database where datname=namespace)  
            )  
         )  
    ) as t    
) ,',' ) || ' ); ';  
      
-- indexs   
  
-- CREATE UNIQUE INDEX pg_language_oid_index ON pg_language USING btree (oid); -- table pg_language  
  
  
--   
/** **/  
--- 获取非约束索引 column  
-- CREATE UNIQUE INDEX pg_language_oid_index ON pg_language USING btree (oid); -- table pg_language  
tableScript:= tableScript || array_to_string(  
    array(  
        select 'CREATE UNIQUE INDEX ' || indexrelname || ' ON ' || tablename || ' USING btree '|| '(' || attname || ');' from (  
         SELECT   
            i.relname AS indexrelname ,  x.indkey,   
            ( select array_to_string (  
            array(   
                select a.attname from pg_attribute a where attrelid=c.oid and a.attnum in ( select unnest(x.indkey) )  
  
                 )   
             ,',' ) )as attname  
              
           FROM pg_class c  
           JOIN pg_index x ON c.oid = x.indrelid  
           JOIN pg_class i ON i.oid = x.indexrelid  
           LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
           WHERE  c.relname=tablename and i.relname not in  
              ( select constraint_name from information_schema.key_column_usage  where  table_name=tablename  )  
        )as t  
) ,',' );  
              
  
-- COMMENT COMMENT ON COLUMN sys_activity.id IS '主键';  
tableScript:= tableScript || array_to_string(  
array(  
SELECT ' COMMENT ON COLUMN' || tablename || '.' || a.attname ||' IS  '|| ''''|| d.description ||''''  
FROM pg_class c  
JOIN pg_description d ON c.oid=d.objoid  
JOIN pg_attribute a ON c.oid = a.attrelid  
WHERE c.relname=tablename  
AND a.attnum = d.objsubid),',') ;  
  
return tableScript;  
  
end  
$BODY$  
  LANGUAGE plpgsql VOLATILE  
  COST 100;

测试
SQL代码

select showcreatetable('public','pg_seclabel'); 

MySQL

参考资料：
系列教程｜Apahce Doris ODBC 外表使用指南之 Ubuntu下 MySQL 外表使用方法及配置（二）…
https://blog.csdn.net/ucanuup_/article/details/122362758

Apache Doris ODBC mysql外表注意事项
https://blog.csdn.net/hf200012/article/details/120285051

ubuntu安装odbc及（mysql驱动）
http://t.zoukankan.com/yoyotl-p-9980269.html

开始
首先安装unixODBC，这个在上面配置postgresql odbc时已经手动安装过了，所以这里先跳过

接下来安装mysql驱动
apt-get install libmyodbc

出现以下信息

root@h2:~# apt-get install libmyodbc
Reading package lists... Done
Building dependency tree
Reading state information... Done
Package libmyodbc is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source

E: Package 'libmyodbc' has no installation candidate

尝试手动安装
下载地址
https://dev.mysql.com/downloads/connector/odbc/

选这个64位的下载即可
如果mysql是5.7版本的需要下载5.3.13版本的驱动
我这里是把两个版本的都下载了以下。

下载后节压
将./mysql-connector-odbc-8.0.28-linux-glibc2.12-x86-64bit/lib/libmyodbc8w.so文件复制到/usr/lib64/路径下

注册mysql驱动,在解压后的bin路径下执行
./myodbc-installer -a -d -n “MySQL ODBC 8.0.28 Unicode Driver” -t “Driver=/usr/lib64/libmyodbc8w.so”

注册5.3.13
./myodbc-installer -a -d -n “MySQL ODBC 5.3.13 Unicode Driver” -t “Driver=/usr/lib64/libmyodbc5w.so”

myodbc-installer -a -d -n “MySQL ODBC 8.0.26 ANSI Driver” -t “Driver=/root/mysql-odbc-8.0.26/lib/libmyodbc8a.so”

修改be/conf/odbcinst.ini文件中的驱动设置新增如下内容

编辑vim /etc/odbc.ini,添加ODBC数据源，注意key的格式，一开始我照着上面pg库添加ODBC数据源的格式写，结果配置不生效。

[MySQL]    ## 数据源名称
Driver = MySQL ODBC 5.3.13 Unicode Driver  ## 驱动名称
Server=ip
Database=database
User=root
Password=root
Port=3306
ReadOnly=No

测试ODBC数据源是否可以连接
isql -v MySQL root