Doris配置外表以及多个Hive外表的配置

1.场景分析

以Clickhouse、Doris、Starrocks等为代表的mpp分析数据库正在快速的兴起，以其高效查询、跨库整合能力收到广大技术人员的喜爱。本文主要浅显介绍下作者在使用Doris时，通过建立catlog进行跨库查询。
废话不多少，直接上代码

2.相关配置

#Tidb外表，jdbc方式连接，如果没有服务器权限可以直接在driver_url上填写jar包的maven地址#如果报connect timeout，建议直接找运维将jar包手动放在服务器本地CREATE CATALOG TiDB_catalog PROPERTIES ("type"="jdbc","user"="xxxx","password"="xxx","jdbc_url"="jdbc:mysql://xxx:4000","driver_url"="https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar","driver_class"="com.mysql.cj.jdbc.Driver");#mysql外表，jdbc方式连接，如果有服务器权限包的maven地址#需将 Jar 包预先存放在 FE 和 BE 部署目录的 jdbc_drivers/ 目录下。系统会自动在这个目录下寻找。该目录的位置，也可以由 fe.conf 和 be.conf 中的 jdbc_drivers_dir 配置修改。#本地绝对路径。如 file:///path/to/mysql-connector-java-5.1.47.jar。需将 Jar 包预先存放在所有 FE/BE 节点指定的路径下。CREATE CATALOG mysql_catalog PROPERTIES ("type"="jdbc","user"="xxx","password"="xxx","jdbc_url"="jdbc:mysql://xxxx:3306","driver_url"="mysql-connector-java-8.0.28.jar","driver_class"="com.mysql.cj.jdbc.Driver");#psql外表，jdbc方式连接，如果没有服务器权限可以直接在driver_url上填写jar包的maven地址CREATE CATALOG postgresql_catalog PROPERTIES ("type"="jdbc","user"="xxx","password"="xxx","jdbc_url"="jdbc:postgresql://xxxx:5432/xxxx","driver_url"="https://repo1.maven.org/maven2/org/postgresql/postgresql/42.5.1/postgresql-42.5.1.jar","driver_class"="org.postgresql.Driver");#hive外表，不用再复制配置文件到相应的问题，当然自己手动xml配置文件到指定位置也可CREATE CATALOG hive_old PROPERTIES ('type'='hms','hive.metastore.uris'='thrift://xxx:9083,thrift://xxx:9083','hadoop.username'='hadoop','dfs.nameservices'='xxx','dfs.ha.namenodes.mycluster'='nn1,nn2','dfs.namenode.http-address.mycluster.nn1'='xxx:9870','dfs.namenode.http-address.mycluster.nn2'='xxx:9870','dfs.namenode.rpc-address.mycluster.nn1'='xxx:8020','dfs.namenode.rpc-address.mycluster.nn2'='xxx:8020','dfs.client.failover.proxy.provider.mycluster'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider','fs.defaultF'='hdfs://xxx','ha.zookeeper.quorum'='xxx:2181,xxx:2181,xxx:2181','javax.jdo.option.ConnectionURL'='jdbc:mysql://xxx:3306/metastore?useSSL=false&createDatabaseIfNotExist=true&characterEncoding=UTF-8','javax.jdo.option.ConnectionDriverName'='com.mysql.jdbc.Driver','javax.jdo.option.ConnectionUserName'='xxx','javax.jdo.option.ConnectionPassword'='xxxx','hive.metastore.warehouse.dir'='/user/hive/warehouse','hive.server2.thrift.bind.host'='xxx','hive.server2.zookeeper.namespace'='hiveserver2_zk','hive.zookeeper.quorum'='xxxx:2181,xxx:2181,xxxx:2181','hive.zookeeper.client.port'='2181');

3.注意事项

腾讯的TCHouse-D （腾讯改版doris）已经配置了jar包，无需自己手动配置
腾讯官网介绍：https://cloud.tencent.com/document/product/1387/100593
Doris官网介绍：https://doris.apache.org/zh-CN/docs/1.2/lakehouse/external-table/jdbc