南大通用GBase 8c基于trino的迁移交互实现

本篇文章来自南大通用GBase技术社区，更多精彩内容请访问 https://www.gbase.cn/community

GBase 8c支持与其他数据库（oracle、mysql、postgresql、sql server等）之间进行跨库查询、连接查询、表及数据的迁移。本文章介绍Trino与GBase 8c的交互使用方法。

Trino参考文档：Trino documentation — Trino 449 Documentation

1、工具下载

trino server / trino client下载路径：Trino | Get started with Trino

java 环境需要支持java-jdk22以上版本。

将解压后的jdk对应路径下,配置java环境变量：

export JAVA_HOME=/home/trino/deploy/java22
export PATH=$JAVA_HOME/bin:$PATH

执行java -version命令验证，例如：

 [trino@gbase8c_5_105 deploy]$  java -version
openjdk version "21.0.2" 2024-01-16
OpenJDK Runtime Environment (build 21.0.2+13-58)
OpenJDK 64-Bit Server VM (build 21.0.2+13-58, mixed mode, sharing)

2、文件配置

1. 将trino server 下载后，解压到指定文件夹：例如：/opt/trino。

2. 创建配置文件：

mkdir /opt/trino/etc

• 配置节点信息

vim /opt/trino/etc/config.properties

修改参数：

node-scheduler.include-coordinator=true
http-server.http.port=8080
discovery.uri=http://172.16.5.105:8080

• 配置JVM信息，根据机器大小配置

vim /opt/trino/etc/jvm.config 

修改参数：

-server
-Xmx4096m
-XX:InitialRAMPercentage=80
-XX:MaxRAMPercentage=80
-XX:G1HeapRegionSize=8M
-XX:+ExplicitGCInvokesConcurrent
-XX:+ExitOnOutOfMemoryError
-XX:+HeapDumpOnOutOfMemoryError
-XX:-OmitStackTraceInFastThrow
-XX:ReservedCodeCacheSize=512M
-XX:PerMethodRecompilationCutoff=10000
-XX:PerBytecodeRecompilationCutoff=10000
-Djdk.attach.allowAttachSelf=true
-Djdk.nio.maxCachedBufferSize=2000
-XX:+UnlockDiagnosticVMOptions
-XX:+UseAESCTRIntrinsics
-XX:-G1UsePreventiveGC

• 配置日志信息

vim /opt/trino/etc/log.config

修改参数：

io.trino=INFO

• 配置搭建的节点信息，包括名称/ID标识/路径

vim /opt/trino/etc/node.properties

修改参数：

node.environment=trino_dev
node.id=ffffffff-ffff-ffff-ffff-ffffffffffff
node.data-dir=/home/trino/trino_data

3、数据库配置

etc下创建catalog，例如：

mkdir catalog #该文件夹下添加各个数据库的配置信息。
默认trino库的为jmx.properties，内容如下：

connector.name=jmx

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=******

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=******

GBase8c配置信息：

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

4、trino启动

执行命令启动：

/opt/trino/bin/launcher start

状态查看命令：/opt/trino/bin/launcher status
客户端连接：bin/trino --server http://IP:8080。例如：

[trino@gbase8c_5_105 trino]$ bin/trino --server http://172.16.5.105:8080
trino> show catalogs;
  Catalog
------------
 gbase8c
 jmx
 mysql
 oracle
 postgresql
 system
(6 rows)

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

trino> show schemas from gbase8c;
          Schema
--------------------------
 blockchain
 compat_tools
 cstore
 db4ai
 dbe_perf
 dbe_pldebugger
 dbe_pldeveloper
 dbms_alert
 dbms_application_info
 dbms_assert
 dbms_job
 dbms_lob
 dbms_lock
 dbms_metadata
 dbms_obfuscation_toolkit
 dbms_output
 dbms_pipe
 dbms_random
 dbms_snapshot
 trino> use mysql.test;
USE
trino:test> show tables from gbase8c.public;
      Table
------------------
 bmsql_config
 bmsql_customer
 bmsql_district
 bmsql_history
 bmsql_item
 bmsql_new_order
 bmsql_oorder
 bmsql_order_line
 bmsql_stock
 bmsql_stock1
 bmsql_warehouse
 dual
 layers
(13 rows)

Query 20240415_015431_00005_dj54q, FINISHED, 1 node
Splits: 5 total, 5 done (100.00%)
0.87 [13 rows, 362B] [14 rows/s, 417B/s]
	

• 多库、表之间交互查询：

 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)

• 多库直接表及数据迁移：

例如将Oracle中sde.LAYERS 表迁移至GBase8c：
执行：

 create table gbase8c.public.LAYERS as select * from oracle.sde.LAYERS;

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>

• 网页监测

记录所有sql