使用Trino实现GBase 8c与异构数据库的数据迁移与联合查询

关于Trino

Trino是一个开源的分布式 SQL 查询引擎，具有联邦查询、并行查询、水平集群伸缩等特性，可支持使用 SQL 访问任意数据源，能够提供更加灵活与高效的查询服务与数据迁移服务。使用Trino可以方便应用于以下典型场景：

• 通过统一 SQL 访问各类数据源

• 执行多数据源联邦查询

• 执行 SQL 转换与ETL

• 构建虚拟数仓（语义层）

• 构建数据湖查询引擎

关于GBase 8c

GBase 8c是基于一款多模多态数据库，支持行存、列存、内存等多种存储模式和单机、主备与分布式等多种部署形态。GBase 8c具备高性能、高可用、弹性伸缩、高安全性等特性，可以部署在物理机、虚拟机、容器、私有云和公有云，为关键行业核心系统、互联网业务系统和政企业务系统提供安全、稳定、可靠的数据存储和管理服务。

使用Trino可以方便的实现GBase 8c与Oracle、MySQL、PostgreSQL、SQL Server等多种数据库之间进行表定义及数据的迁移、跨库连接查询等操作。

在Trino中配置GBase 8c及Oracle/MySQL

在etc下创建catalog，默认trino库的为jmx.properties

GBase8c配置如下

[trino@gbase8c_5_105 catalog]$ cat gbase8c.properties
connector.name=postgresql
connection-url=jdbc:postgresql://172.16.5.102:5432/tjg_data
connection-user=regress
connection-password=*******

Oracle配置如下

[trino@gbase8c_5_105 catalog]$ cat oracle.properties
connector.name=oracle
# The correct syntax of the connection-url varies by Oracle version and
# configuration. The following example URL connects to an Oracle SID named
# "orcl".
connection-url=jdbc:oracle:thin:@172.16.5.104:1521:orcl
connection-user=sde
connection-password=******

MySQL配置信息如下

[trino@gbase8c_5_105 catalog]$ cat mysql.properties
connector.name=mysql
connection-url=jdbc:mysql://172.16.5.103:3306
connection-user=root
connection-password=******

最佳实践1：数据迁移

使用Trino可以直接使用SQL进行跨库数据迁移，以Oracle到GBase 8c为例，将Oracle中sde LAYERS表迁移到GBase 8c库中。

trino:test> create table gbase8c.public.LAYERS as select * from oracle.sde.LAYERS;
trino:test> drop table gbase8c.public.LAYERS;
DROP TABLE
trino:test> create table gbase8c.public.LAYERS as select * from oracle.sde.LAYERS;
CREATE TABLE: 5 rows

Query 20240415_020048_00008_dj54q, FINISHED, 1 node
Splits: 7 total, 7 done (100.00%)
4.09 [5 rows, 0B] [1 rows/s, 0B/s]

trino:test>

最佳实践2：异构数据库跨库关联查询

实现GBase 8c中 bmsql_stock 表与MySQL中 t 表的跨库关联

 trino:test> select * from gbase8c.public.bmsql_stock a,mysql.test.t b where a.s_i_id=b.id and b.id=888;
 s_w_id | s_i_id | s_quantity | s_ytd | s_order_cnt | s_remote_cnt |                   s_data                    |        s
--------+--------+------------+-------+-------------+--------------+---------------------------------------------+---------
      1 |    888 |         25 |     0 |           0 |            0 | lwuY611D0C4b0VXdTxFVenPrcwP                 | EgtozSes
      2 |    888 |         15 |     0 |           0 |            0 | nWMNFYLsoWJq69IORIGINALrmVuevTOuGOVZHi5jpn5 | WHRyrvd6
      3 |    888 |         86 |     5 |           1 |            0 | lkzYAuFue5gY5JVePgItusUtETyZ2M9             | HbqIpUJt
      4 |    888 |         35 |     0 |           0 |            0 | mZNrLvh3YFO81cdpZabzjp2qBUGHuW8wmKrn9tSO1hJ | ijC9dN7U
      5 |    888 |         32 |     0 |           0 |            0 | mkwWbfYNXnIGg6JDkORIGINALxvERkd0            | YJKNrEWk
(5 rows)	

最佳实践3：SQL监控

使用Trino也可以实现SQL的可视化监控

其它常见问题

1. Trino简单使用可选择单机部署，如果需要部署分布式可参考：https://trino.io/docs/current/
2. 连接MySQL 提示com.mysql.cj.exceptions.CJException: Unknown or incorrect time zone: 'Asia/Shanghai'
   原因为缺少：SET GLOBAL time_zone = 'Asia/Shanghai';--时区
   需要下载：https://dev.mysql.com/downloads/timezones.html中SQL文件在MySQL中执行