[Trino] 설치 및 맛보기

mysql 설치

먼저 mysql 두 개를 설치합니다.

$ kubectl create ns mysql-1
$ kubectl create ns mysql-2

 

mysql-1.yaml

apiVersion: v1
kind: Service
metadata:
  name: mysql-1
spec:
  type: ClusterIP
  selector:
    app: mysql-1
  ports:
    - protocol: TCP
      port: 3306
      targetPort: 3306
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-1
  labels:
    app: mysql-1
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql-1
  template:
    metadata:
      labels:
        app: mysql-1
    spec:
      containers:
      - name: mysql
        image: mysql:8.0
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: root
        ports:
        - containerPort: 3306

 

mysql-2.yaml

apiVersion: v1
kind: Service
metadata:
  name: mysql-2
spec:
  type: ClusterIP
  selector:
    app: mysql-2
  ports:
    - protocol: TCP
      port: 3306
      targetPort: 3306
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-2
  labels:
    app: mysql-2
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql-2
  template:
    metadata:
      labels:
        app: mysql-2
    spec:
      containers:
      - name: mysql
        image: mysql:8.0
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: root
        ports:
        - containerPort: 3306

 

$ kubectl apply -f mysql-1.yaml -n mysql-1
service/mysql-1 created
deployment.apps/mysql-1 created

$ kubectl apply -f mysql-2.yaml -n mysql-2
service/mysql-2 created
deployment.apps/mysql-2 created

---

Helm을 이용한 trino 설치

$ helm repo add trino https://trinodb.github.io/charts
"trino" has been added to your repositories

 

$ helm pull trino/trino
$ ls
trino-0.11.0.tgz

 

$ tar xvf trino-0.11.0.tgz 
trino/Chart.yaml
trino/values.yaml
trino/templates/NOTES.txt
trino/templates/_helpers.tpl
trino/templates/autoscaler.yaml
trino/templates/configmap-catalog.yaml
trino/templates/configmap-coordinator.yaml
trino/templates/configmap-worker.yaml
trino/templates/deployment-coordinator.yaml
trino/templates/deployment-worker.yaml
trino/templates/secret.yaml
trino/templates/service.yaml
trino/templates/serviceaccount.yaml
trino/README.md
trino/ci/custom-values.yaml

 

$ ls
trino  trino-0.11.0.tgz

 

$ cd trino/
$ ls
Chart.yaml  README.md  ci  templates  values.yaml

 

values.yaml 파일에서 additionalCatalogs를 아래와 같이 변경

additionalCatalogs:
  mysql_1: |-
    connector.name=mysql
    connection-url=jdbc:mysql://mysql-1.mysql-1.svc.cluster.local:3306
    connection-user=root
    connection-password=root
  mysql_2: |-
    connector.name=mysql
    connection-url=jdbc:mysql://mysql-2.mysql-2.svc.cluster.local:3306
    connection-user=root
    connection-password=root

 

trino 설치 (values.yaml이 있는 위치에서)

$ ls
Chart.yaml  README.md  ci  templates  values.yaml

$ kubectl create ns trino
namespace/trino created

$ helm install trino . -f values.yaml -n trino
NAME: trino
LAST DEPLOYED: Wed Aug  9 23:24:24 2023
NAMESPACE: trino
STATUS: deployed
REVISION: 1
TEST SUITE: None
NOTES:
Get the application URL by running these commands:
  export POD_NAME=$(kubectl get pods --namespace trino -l "app=trino,release=trino,component=coordinator" -o jsonpath="{.items[0].metadata.name}")
  echo "Visit http://127.0.0.1:8080 to use your application"
  kubectl port-forward $POD_NAME 8080:8080

 

Trino 접속 및 테스트

pod 확인

$ kubectl get pod
NAME                                 READY   STATUS    RESTARTS   AGE
trino-coordinator-54bf94c5d5-gvftz   1/1     Running   0          58s
trino-worker-df8c6b8c9-lwqn5         1/1     Running   0          58s

 

coordinator 파드 접속

$ kubectl exec -it trino-coordinator-54bf94c5d5-gvftz -- trino
trino>

 

쿼리 동작 확인

trino> show catalogs;
 Catalog 
---------
 mysql_1 
 mysql_2 
 system  
 tpcds   
 tpch    
(5 rows)

Query 20230809_143524_00000_twsr6, FINISHED, 1 node
Splits: 5 total, 5 done (100.00%)
1.90 [0 rows, 0B] [0 rows/s, 0B/s]

 

trino> show schemas from mysql_1;
       Schema       
--------------------
 information_schema 
 performance_schema 
(2 rows)

Query 20230809_143623_00004_twsr6, FINISHED, 2 nodes
Splits: 5 total, 5 done (100.00%)
1.27 [2 rows, 46B] [1 rows/s, 36B/s]

 

trino> show schemas from mysql_2;
       Schema       
--------------------
 information_schema 
 performance_schema 
(2 rows)

Query 20230809_143649_00005_twsr6, FINISHED, 2 nodes
Splits: 5 total, 5 done (100.00%)
0.39 [2 rows, 46B] [5 rows/s, 119B/s]

---

mysql 쿼리 테스트

mysql-1 파드 접속

$ kubectl get pod -n mysql-1
NAME                       READY   STATUS    RESTARTS   AGE
mysql-1-5bf78b677c-9wd9z   1/1     Running   0          27m

$ kubectl exec -it mysql-1-5bf78b677c-9wd9z -n mysql-1 -- mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

 

database, table, 샘플 데이터 생성

mysql> create database company_1;
Query OK, 1 row affected (0.04 sec)

mysql> use company_1;
Database changed

CREATE TABLE emp (
      id INT NOT NULL PRIMARY KEY,
      name VARCHAR(30) NOT NULL,
      age INT NULL
);

mysql> CREATE TABLE emp (
    ->       id INT NOT NULL PRIMARY KEY,
    ->       name VARCHAR(30) NOT NULL,
    ->       age INT NULL
    -> );
Query OK, 0 rows affected (0.07 sec)

INSERT INTO emp VALUES
(1, 'AAA', 11),
(2, 'BBB', 22),
(3, 'CCC', 33),
(4, 'DDD', 44);

mysql> INSERT INTO emp VALUES
    -> (1, 'AAA', 11),
    -> (2, 'BBB', 22),
    -> (3, 'CCC', 33),
    -> (4, 'DDD', 44);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

 

mysql-2 파드에서도 동일하게 진행

mysql> create database company_2;
mysql> use company_2;

CREATE TABLE emp (
      id INT NOT NULL PRIMARY KEY,
      name VARCHAR(30) NOT NULL,
      age INT NULL
);

INSERT INTO emp VALUES
(3, 'CCC', 33),
(4, 'DDD', 44),
(5, 'EEE', 55),
(6, 'FFF', 66);

 

Trino 쿼리 테스트

trino> show tables from mysql_1.company_1;
 Table 
-------
 emp   
(1 row)

trino> select * from mysql_1.company_1.emp;
 id | name | age 
----+------+-----
  1 | AAA  |  11 
  2 | BBB  |  22 
  3 | CCC  |  33 
  4 | DDD  |  44 
(4 rows)

 

trino> show tables from mysql_2.company_2;
 Table 
-------
 emp   
(1 row)

trino> select * from mysql_2.company_2.emp;
 id | name | age 
----+------+-----
  3 | CCC  |  33 
  4 | DDD  |  44 
  5 | EEE  |  55 
  6 | FFF  |  66 
(4 rows)

 

join

trino> select * from mysql_1.company_1.emp as A , mysql_2.company_2.emp as B;
 id | name | age | id | name | age 
----+------+-----+----+------+-----
  1 | AAA  |  11 |  3 | CCC  |  33 
  2 | BBB  |  22 |  3 | CCC  |  33 
  3 | CCC  |  33 |  3 | CCC  |  33 
  4 | DDD  |  44 |  3 | CCC  |  33 
  1 | AAA  |  11 |  4 | DDD  |  44 
  2 | BBB  |  22 |  4 | DDD  |  44 
  3 | CCC  |  33 |  4 | DDD  |  44 
  4 | DDD  |  44 |  4 | DDD  |  44 
  1 | AAA  |  11 |  5 | EEE  |  55 
  2 | BBB  |  22 |  5 | EEE  |  55 
  3 | CCC  |  33 |  5 | EEE  |  55 
  4 | DDD  |  44 |  5 | EEE  |  55 
  1 | AAA  |  11 |  6 | FFF  |  66 
  2 | BBB  |  22 |  6 | FFF  |  66 
  3 | CCC  |  33 |  6 | FFF  |  66 
  4 | DDD  |  44 |  6 | FFF  |  66 
(16 rows)

 

join + where 조건문

trino> select * from mysql_1.company_1.emp as A , mysql_2.company_2.emp as B where A.age=B.age;
 id | name | age | id | name | age 
----+------+-----+----+------+-----
  3 | CCC  |  33 |  3 | CCC  |  33 
  4 | DDD  |  44 |  4 | DDD  |  44 
(2 rows)