trino与GBase8c的交互使用

trino与GBase8c的交互使用

trinoGBase8c迁移跨库查询

支持GBase8c与其他数据库（oracle、mysql、postgresql、sql server等）之间进行跨库查询、连接查询、表及数据的迁移

Trino参考文档：https://trino.io/docs/current/

---

1、工具下载

trino server / trino client：https://trino.io/download.html
百度网盘下载：链接：https://pan.baidu.com/s/1qNZGGcLk--syCYqrClS7XA?pwd=odgm
1）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、文件配置

将trino server 下载后，解压到指定文件夹：例如：/opt/trino
创建配置文件：
mkdir /opt/trino/etc
1）vim /opt/trino/etc/config.properties #配置节点信息

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

2）vim /opt/trino/etc/jvm.config #配置JVM信息，根据机器大小配置

-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

3）vim /opt/trino/etc/log.config #配置日志信息

io.trino=INFO

4）vim /opt/trino/etc/node.properties #配置搭建的节点信息，包括名称/ID标识/路径

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://172.16.5.102:5432/tjg_data
connection-user=regress
connection-password=*******

4、trino启动

/opt/trino/bin/launcher start
日志路径：/home/trino/trino_data/var/log/server.log
状态查看：/opt/trino/bin/launcher status
客户端连接：
bin/trino --server http://172.16.5.105: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

常见问题：
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中执行