Presto/Trino Connector

소개

이 커넥터는 Presto/Trino에 연결하고, API와 IoT 형식으로 SCLAB 쪽에 데이터를 연결할 수 있게 해줍니다.

기능

• Presto/Trino에 연결하기
• SQL 쿼리를 사용하여 일정 간격으로 MQTT 메시지 발행하기
• SQL 쿼리로 REST API 엔드포인트 생성하기
• HTTP 인증을 위한 JWT
• 결과 데이터를 JSON 형식으로 처리하기
• SQL Injection 차단 (기본 on)
• Mybatis 매핑

.env 파일에 "QUERY_1=mqtt;query;topic;interval ms" 형식으로 쿼리를 추가하면, SQL을 사용하여 데이터 가져오고 SCLAB과 연결합니다.

두 가지 방법을 지원합니다: MQTT와 API, 각각 다른 변수 형식을 사용합니다:

MQTT 형식:

QUERY_#=mqtt;SQL Query;topic;interval MS

API 형식:

QUERY_#=api;SQL Query;Endpoint URL

설치방법

사전 준비사항

• trino 또는 presto (https://trino.io/docs/current/installation.html)
• 접속 정보
• docker 또는 nodejs 설치

소스 받기

$ git clone https://github.com/sclab-io/sclab-presto-connector

API 보안을 위한 JWT 키파일 생성

$ mkdir jwt
$ ssh-keygen -t rsa -b 4096 -m PEM -f ./jwt/jwtRS256.key
# empty passphrase - just press enter
$ openssl rsa -in ./jwt/jwtRS256.key -pubout -outform PEM -out ./jwt/jwtRS256.key.pub

환경변수 파일 생성 .env.production.local

$ vi .env.production.local

# Presto/Trino Connection
PRESTO_HOST=172.17.0.1
PRESTO_PORT=8080
PRESTO_USER=sclab-trino-client

# BASIC AUTH
PRESTO_AUTH=BASIC
PRESTO_BASIC_USER=user
#PRESTO_BASIC_PASSWORD=password

# CUSTOM AUTH
#PRESTO_AUTH=CUSTOM
#PRESTO_CUSTOM_AUTH=Sets HTTP Authorization header with the provided string.

# SCLAB IoT (Remove this environment if you do not need to use MQTT)
MQTT_TOPIC=yourtopic/
MQTT_HOST=yourhost
MQTT_CLIENT_ID=your-client-id/1
MQTT_ID=your-id
MQTT_PASSWORD=your-password

# QUERY_#=mqtt;query;topic;interval ms
# QUERY_#=api;query;endPoint
QUERY_1=api;SELECT ROUND( RAND() * 100 ) AS value, NOW() AS datetime;/api/1
QUERY_2=api;SELECT ${field} from ${table} where name="${name}";/api/2
# QUERY_3=mqtt;SELECT ROUND( RAND() * 100 ) AS value, NOW() AS datetime;test0;1000
# QUERY_4=mqtt;SELECT ROUND( RAND() * 1000 ) AS value, NOW() AS datetime;test1;5000

# PORT
PORT=3000

# MyBatis
MY_BATIS_FILE_FOLDER=./mybatis

# TOKEN
SECRET_KEY=secretKey
JWT_PRIVATE_KEY_PATH=./jwt/jwtRS256.key
JWT_PUBLIC_KEY_PATH=./jwt/jwtRS256.key.pub

# LOG
LOG_FORMAT=combined
LOG_DIR=../logs

# CORS
ORIGIN=your.domain.com
CREDENTIALS=true

# SQL INJECTION
SQL_INJECTION=1

시작

# docker compose
$ ./run.sh

# nodejs
$ npm run start

# pm2
$ npm run deploy:prod

중지

# docker compose
$ ./stop.sh

# pm2
$ ./node_modules/.bin/pm2 stop 0

로그 확인

$ ./logs.sh

API 엔드포인트 접속

API에 접속하기 위해서는 인증정보를 해더값에 추가해야 합니다. 형식은 아래와 같습니다.

authorization: yourkey

yourkey 부분에 들어갈 내용은 로그에서 확인이 가능합니다.

소스 빌드 방법

nodejs 설치

curl -sL https://rpm.nodesource.com/setup_18.x | sudo bash -
sudo yum install nodejs

소스 빌드

$ npm install
$ npm run build

API를 이용한 데이터 연결

QUERY_2에 접속하기

QUERY_2=api;SELECT ${field} from ${table} where name="${name}";/api/2

1. SCLAB Studio에서 데이터 에디터 화면으로 이동 후 API 를 추가해줍니다.

주소는 지금 연결할 API 엔드포인트 주소를 넣어주시면 됩니다. 도커를 이용할 경우 기본 포트는 9180으로 설정되어 있습니다.

아래와 같은 형식으로 주소를 입력해주세요.

http://ip:9180/api/2

2. Headers 탭을 선택한 후 인증키 값을 넣어줍니다.

Key	Value Type	Value
authorization	Fixed	yourkeyfromlog

3. 파라메터가 설정된 쿼리인경우 Params탭에 설정값을 추가해 줍니다.

Key	Value Type	Value
field	Variable	*
table	Variable	product
name	Variable	Bike

4. SEND 버튼을 눌러서 연결을 합니다.

5. 데이터가 정상적으로 들어오는지 확인 합니다.

6. 데이터가 들어온 경우 패스설정을 통해 필드와 레이블 값을 매핑해줍니다.

7. SAVE 버튼을 눌러서 데이터를 저장합니다.

8. 이후 차트나 테이블등의 형식으로 데이터를 시각화를 진행하면 됩니다.

Mybatis 매핑 하기

• Mybatis는 https://github.com/OldBlackJoe/mybatis-mapper/tree/master 를 이용하고 있고 여기서 지원하는 형식만 가능합니다.
• 사용하기 위해서는 mybatis 폴더를 만들어주세요.

if문 사용 예제

• mybatis 폴더에 아래와 같은 형식으로 입력하시면 됩니다.

./mybatis/sample.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="sample">  
  <select id="test">
    SELECT
      *
    FROM
      POST
    WHERE
      1=1
      <if test="id != null and id != ''">
      AND id = #{id}
      </if>
  </select>
</mapper>

• 위 예제는 if 조건문을 이용하여 id 값이 파라미터로 넘어온 경우에만 id 조건으로 검색하고 그렇지 않은 경우는 모든 데이터를 반환하는 예제입니다.
• xml파일을 추가한 뒤 환경 변수에 설정을 추가합니다.

.env.production.local

QUERY_mybatis_1=mybatis;sample;test;/api/mybatistest

• 환경 변수를 추가한 뒤 재시작 하면 해당 엔드포인트로 요청 시 mybatis 폴더의 'sample.test' 와 연결이 되어 쿼리를 만들고 데이터를 가져올 수 있습니다.

기간쿼리 예제

./mybatis/sample2.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="sample2">  
  <select id="test">
    SELECT 
      DATE_FORMAT(date_parse(date, '%Y%m%d'), '%Y') AS year, 
      DATE_FORMAT(date_parse(date, '%Y%m%d'), '%m') AS month, 
      SUM(newusercount) AS total_new_user_count 
    FROM mycatalog.mydb.statistics 
    WHERE 
      date_parse(date, '%Y%m%d') BETWEEN 
      date_parse(${startDate}, '%Y%m%d') AND 
      date_parse(${endDate}, '%Y%m%d') 
    GROUP BY 
      DATE_FORMAT(date_parse(date, '%Y%m%d'), '%Y'), 
      DATE_FORMAT(date_parse(date, '%Y%m%d'), '%m') 
    ORDER BY year, month
  </select>
</mapper>

• 위 쿼리는 통계 테이블에서 기간동안 가입한 회원수를 가져오는 쿼리 입니다.
• 해당 테이블의 데이터는 date가 20201201 과 같이 'YYYYMMDD' 의 형식으로 저장 되어있습니다.
• 월별로 데이터를 뽑기위해서 월별로 신규 가입자 계산하는 쿼리입니다.
• 일별 데이터만 저장되어있기 때문에 SUM으로 합계를 구하고, Group by 를 통해 년 월 의 형식으로 데이터를 가져옵니다.

.env.production.local

QUERY_mybatis_2=mybatis;sample2;test;/api/mybatistest2

• 환경변수를 추가한 후 재시작 해줍니다.

curl "http://localhost:9180/api/mybatistest2?startDate=20230101&endDate=20230630" -H 'authorization: TOKEN'

• 위와 같이 데이터를 요청하면 2023년 1월 부터 2023년 6월까지의 월별 신규 회원가입자 수를 가져올 수 있습니다.

{
  "rows":[
    {"year":"2023","month":"01","total_new_user_count":3567},
    {"year":"2023","month":"02","total_new_user_count":3416},
    {"year":"2023","month":"03","total_new_user_count":4345},
    {"year":"2023","month":"04","total_new_user_count":4025},
    {"year":"2023","month":"05","total_new_user_count":4488},
    {"year":"2023","month":"06","total_new_user_count":4453}
  ]
}⏎