QueryPie 구축(K8s)

참고문서

https://github.com/chequer-io/querypie-deployment

GitHub - chequer-io/querypie-deployment

https://docs.querypie.com/ko/installation/installation

설치하기 - QueryPie ACP

 

 

QueryPie 최소구성 아키텍처

[Browser]
   ↓
[QueryPie Web]
   ↓ (OIDC Redirect)
[Okta]
   ↓ (ID Token)
[QueryPie]

 

 

인프라 권장사항

권장 노드 사양: r5.xlarge (4 vCPU, 32GB RAM)
Kubernetes 버전: 1.24 이상
필요 노드 수: 최소 1개 (프로덕션 환경은 3개 이상 권장)

 

 

namespace 생성

kubectl create namespace querypie

 

 

MetaDB 설치

MySQL은 QueryPie의 메타데이터를 저장하는 데이터베이스로 사용된다.

 

# PV 생성

apiVersion: v1
kind: PersistentVolume
metadata:
  name: local-pv
  labels:
    app: mysql     
spec:
  capacity:
    storage: 50Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: /var/lib/mysql
    type: DirectoryOrCreate

[root@k8s-master terraform-access-control]# vi pv.yaml
[root@k8s-master terraform-access-control]# k apply -f pv.yaml
persistentvolume/local-pv created
[root@k8s-master terraform-access-control]# k get pv
NAME                    CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS      CLAIM                            STORAGECLASS   VOLUMEATTRIBUTESCLASS   REASON   AGE
local-pv                50Gi       RWO            Retain           Available                                                   <unset>                          2s

 

 

MySQL statefulset 배포

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
  namespace: querypie
spec:
  selector:
    matchLabels:
      app: mysql
  serviceName: mysql
  replicas: 1
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
        - name: mysql
          image: mysql:8.0
          env:
            - name: MYSQL_ROOT_PASSWORD
              value: querypie
            - name: MYSQL_DATABASE
              value: querypie
            - name: MYSQL_USER
              value: querypie
            - name: MYSQL_PASSWORD
              value: querypie
          resources:
            requests:
              cpu: "0.5"
              memory: "2Gi"
          ports:
            - containerPort: 3306
              name: mysql
          volumeMounts:
            - name: data
              mountPath: /var/lib/mysql
  volumeClaimTemplates:
    - metadata:
        name: data
      spec:
        accessModes: [ "ReadWriteOnce" ]
        resources:
          requests:
            storage: 50Gi
        storageClassName: ""              
        selector:                       
          matchLabels:
            app: mysql
---
apiVersion: v1
kind: Service
metadata:
  name: mysql
  namespace: querypie
  labels:
    app: mysql
spec:
  type: ClusterIP
  selector:
    app: mysql
  ports:
    - port: 3306
      targetPort: 3306
      protocol: TCP
      name: mysql

[root@k8s-master terraform-access-control]# vi mysql.yaml
[root@k8s-master terraform-access-control]# k apply -f mysql.yaml
statefulset.apps/mysql created
service/mysql created
[root@k8s-master terraform-access-control]# k get svc
NAME    TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)    AGE
mysql   ClusterIP   10.98.161.155   <none>        3306/TCP   72s
[root@k8s-master terraform-access-control]# k get po
NAME      READY   STATUS    RESTARTS   AGE
mysql-0   1/1     Running   0          74s

 

 

데이터베이스 초기화

kubectl exec -it mysql-0 -n querypie -- mysql -u root -p
# 비밀번호: querypie

DROP DATABASE IF EXISTS querypie;
DROP DATABASE IF EXISTS querypie_log;
DROP DATABASE IF EXISTS querypie_snapshot;
 
CREATE database querypie CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE database querypie_log CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE database querypie_snapshot CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
 
GRANT ALL privileges ON querypie.* TO querypie@'%';
GRANT ALL privileges ON querypie_log.* TO querypie@'%';
GRANT ALL privileges ON querypie_snapshot.* TO querypie@'%';
 
FLUSH PRIVILEGES;

exit

 

 

Redis 설치

Redis는 QueryPie의 세션 및 캐시 저장소로 사용된다.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: redis
  namespace: querypie
  labels:
    app: redis
spec:
  selector:
    matchLabels:
      app: redis
  template:
    metadata:
      labels:
        app: redis
    spec:
      containers:
        - name: redis
          image: redis:7
          imagePullPolicy: IfNotPresent
          args: ["--requirepass", "querypie"]
          ports:
            - containerPort: 6379
              protocol: TCP
          resources:
            limits:
              cpu: "0.1"
              memory: "1Gi"
          volumeMounts:
            - name: redis-data
              mountPath: /data
      restartPolicy: Always
      volumes:
        - name: redis-data
          emptyDir: {}   # 파드 재시작 시 데이터 삭제 (캐시 용도면 OK)
---
apiVersion: v1
kind: Service
metadata:
  name: redis
  namespace: querypie
  labels:
    app: redis
spec:
  type: ClusterIP
  selector:
    app: redis
  ports:
    - port: 6379
      targetPort: 6379
      protocol: TCP

[root@k8s-master terraform-access-control]# vi redis.yaml
[root@k8s-master terraform-access-control]# k apply -f redis.yaml
deployment.apps/redis created
service/redis created
[root@k8s-master terraform-access-control]# k get svc
NAME    TYPE        CLUSTER-IP       EXTERNAL-IP   PORT(S)    AGE
mysql   ClusterIP   10.98.161.155    <none>        3306/TCP   19m
redis   ClusterIP   10.108.214.141   <none>        6379/TCP   50s
[root@k8s-master terraform-access-control]# k get po -o wide
NAME                     READY   STATUS    RESTARTS   AGE   IP           NODE         NOMINATED NODE   READINESS GATES
mysql-0                  1/1     Running   0          19m   10.85.2.94   k8s-node02   <none>           <none>
redis-69b5bb5694-bzvb2   1/1     Running   0          54s   10.85.2.95   k8s-node02   <none>           <none>

 

 

QueryPie 설치

harbor.chequer.io

주소는 QueryPie image를 pull 할 수 있는 사설 레지스트리 주소다.

최초 설치 시 고객사 Harbor 계정을 생성하여 사전에 전달하여 설치할 수 있다.

따라서 kubernetes에 실제적인 설치는 못했지만, 설치 메뉴얼을 남긴다.

 

# Harbor 레지스트리 인증 정보 등록

kubectl create secret docker-registry querypie-regcred \
  --docker-server=harbor.chequer.io \
  --docker-username='{harbor-id}' \
  --docker-password='{harbor-pw}' \
  -n querypie

 

# QueryPie 환경 설정(querypie.env로 저장)

# Agent Secret (32자리 임의의 문자열)
AGENT_SECRET=01234567890123456789012345678912
KEK=querypie
 
# QueryPie Meta DB 접속 정보
DB_CATALOG=querypie
DB_HOST=mysql
DB_USERNAME=querypie
DB_PASSWORD=querypie
DB_PORT=3306
 
# QueryPie Log DB 접속 정보
LOG_DB_CATALOG=querypie_log
LOG_DB_HOST=mysql
LOG_DB_USERNAME=querypie
LOG_DB_PASSWORD=querypie
LOG_DB_PORT=3306
 
# QueryPie Snapshot DB 접속 정보
ENG_DB_CATALOG=querypie_snapshot
ENG_DB_HOST=mysql
ENG_DB_USERNAME=querypie
ENG_DB_PASSWORD=querypie
ENG_DB_PORT=3306
 
# Redis 접속 정보
REDIS_NODES=redis:6379
REDIS_PASSWORD=querypie

 

# 환경설정 파일을 secret로 등록

kubectl create secret generic querypie-secret --from-env-file=querypie.env -n querypie

 

# Helm Chart 설정

[root@k8s-master terraform-access-control]# helm repo add querypie https://chequer-io.github.io/querypie-deployment/helm-chart
"querypie" has been added to your repositories

[root@k8s-master terraform-access-control]# helm repo update
Hang tight while we grab the latest from your chart repositories...
...Successfully got an update from the "querypie" chart repository

[root@k8s-master terraform-access-control]# helm repo list
NAME            URL
querypie        https://chequer-io.github.io/querypie-deployment/helm-chart

[root@k8s-master terraform-access-control]# helm search repo querypie
NAME                    CHART VERSION   APP VERSION     DESCRIPTION
querypie/querypie       1.4.4           11.5.0          A Helm chart for QueryPie

 

values.yaml 로 아래 파일 저장

appVersion: &version 11.3.0
 
global:
  image: 
    # -- Default registry used for all images
    registry: harbor.chequer.io
    # -- Default image tags used for all images
    tag: *version
    # -- Default image pull policy for all images
    pullPolicy: IfNotPresent
  # -- Default labels for all resources deployed by the chart
  labels: {}
 
# -- ServiceAccount for QueryPie and QueryPie tools pods
# It is created for authenticating private image registry and AWS API.
# @default -- {}
serviceAccount:
  labels: {}
  annotations: {}
  # -- The name of the secret used for pulling the QueryPie image from the private registry.
  # To create, run `kubectl create secret docker-registry querypie-regcred --docker-server=harbor.chequer.io --docker-username=admin --docker-password=your-password`
  imagePullSecrets:
    - name: querypie-regcred
 
querypie:
  # -- Labels used for QueryPie pods.
  labels: {}
  # -- Annotations used for QueryPie pods.
  annotations: {}
  tolerations: {}
  # -- NodeSelector for QueryPie pods
  nodeSelector: {}
  replicas: 1
  # -- PodManagementPolicy for QueryPie pods. OrderedReady is fine for most cases, but Parallel is also good for large-scale deployments.
  podManagementPolicy: Parallel
  image:
    # -- (string) Registry used for the QueryPie image. If not set, the global registry will be used.
    registry:
    # -- (string) Tag used for the QueryPie image. If not set, the global tag will be used.
    tag:
    # -- (string) PullPolicy used for the QueryPie image. If not set, the global pullPolicy will be used.
    pullPolicy:
    # -- Repository used for the QueryPie image. (required)
    repository: querypie/querypie
  ingress:
    # -- (bool) If true, Ingress resource for QueryPie will be created.
    enabled: true
    labels: {}
    # -- Annotations used for the Ingress resource.
    # It is useful when you want to enable controller-specific feature for the Ingress resource.
    annotations: {}
    # -- (string) Class used for the Ingress resource. If not set, the default class will be used.
    ingressClassName: ""
    # -- Hostname used for the Ingress resource. If not set, every hostname will be accepted.
    host: querypie.querypie.io #실제 설정된 url 입력
    # -- Extra paths used for the Ingress resource.
    # It is mostly used for redirecting HTTP to HTTPS if the ingress controller does not support redirect by default.
 
  proxyService:
    # -- Create a service resource for QueryPie Agent connections.
    # Basically, it opens 9000/tcp port for the QueryPie Agent, and 40000~/tcp for the Agentless connections.
    enabled: true
    labels: {}
    # -- Annotations used for the Service resource.
    # It is useful when you want to enable controller-specific feature for the Service resource.
    annotations: {}
    type: LoadBalancer
    loadBalancerClass: ""
    # -- Set the externalTrafficPolicy to Local is recommended for auditing the real client IP address.
    externalTrafficPolicy: "Local"
    # -- SessionAffinity is not required for the QueryPie.
    sessionAffinity: "None"
  updateStrategy:
    # -- The strategy used for updating the QueryPie pods.
    # It is generally recommended to use the RollingUpdate strategy.
    # But if required, you can change it to OnDelete for manual operations.
    type: RollingUpdate
  resources:
    requests:
      cpu: "2000m"
      memory: 16Gi
    limits:
      # -- More than 2 CPU cores are recommended.
      cpu: "2000m"
      # -- More than 16Gi memory is recommended.
      memory: 16Gi
  logrotator:
    image:
      # -- (string) Registry used for the Logrotate image. If not set, the global registry will be used.
      repository: querypie/logrotate
      # -- (string) Tag used for the Logrotate image. If not set, the global tag will be used.
      tag: latest
  # -- External storage for the QueryPie Object Storage.
  externalStorage:
    # -- Type of the external storage.
    # - none: No external storage.
    # - persistentVolumeClaim: using a single Persistent Volume Claim for all QueryPie pods.
    type: none
    # -- Use a persistent volume claim for the external storage. It will be available if the type is persistentVolumeClaim.
    persistentVolumeClaim:
      # -- Use an existing Persistent Volume Claim for the external storage.
      # If you set this to true, this helm chart will not create a new Persistent Volume Claim.
      useExisting: false
      # -- The name of the existing Persistent Volume Claim to be used.
      claimName: ""
      # -- Metadata of the Persistent Volume Claim.
      metadata:
        annotations: {}
        labels: {}
      # -- Spec of the Persistent Volume Claim.
      spec:
        storageClassName: ""
        resources:
          requests:
            storage: 100Gi
        # -- It is required to have ReadWriteMany access mode on production.
        accessModes:
          - ReadWriteMany
  # -- Extra environment variables used for the QueryPie pods.
  # This is useful for experimental features, debugging, workaround, and so on.
  # for example:
  # API_JVM_HEAPSIZE: '2g' will set the JVM heap size to 2GB.
  extraEnvs: {}
 
tools:
  image:
    # -- Registry used for the QueryPie tools image. If not set, the global registry will be used.
    repository: querypie/querypie-tools
 
config:
  # -- External URL that users use to access the QueryPie via web.
  # Specify the scheme, hostname, and port is required.
  externalURL: "https://querypie.querypie.io" #실제 설정된 url 입력
  secretName: "querypie-secret"
 
  database:
    querypie:
      # -- The maximum number of connections that QueryPie can use for "metastore" purposes.
      connectionPoolSize: 20
 
  dac:
    # -- This setting is used to ignore simple queries sent by client tools such as DataGrip.
    # Please refer to the commented-out example below.
    skipCommandConfigData: "{}"
    # skipCommandConfig: |
    #   {
    #     "mysql": [
    #       "^(/\\*.*?\\*/)?\\s*SELECT\\s+@@session\\s*\\.\\s*\\w+\\s*$",
    #       "^(/\\*.*?\\*/)?\\s*SET\\s+session\\s+transaction\\s+\\w+(\\s+\\w+)*\\s*$",
    #       "^(/\\*.*?\\*/)?\\s*SET\\s+net_write_timeout\\s*=\\s*\\d+\\s*$",
    #       "^(/\\*.*?\\*/)?\\s*SELECT\\s+database\\s*\\(\\s*\\)\\s*$",
    #       "^(/\\*.*?\\*/)?\\s*SET\\s+SQL_SELECT_LIMIT\\s*=\\s*\\w+$",
    #       "^SHOW\\s+VARIABLES\\s+LIKE\\s+'aurora\\\\_version'\\s*$",
    #       "^SELECT\\s+version\\s*\\(\\s*\\)\\s*,\\s*@@version_comment\\s*,\\s*database\\s*\\(\\s*\\)\\s*$",
    #       "^SET\\s+autocommit\\s*=\\s*\\d+$",
    #       "^(/\\*.*?\\*/)\\s*SELECT\\s+((@@session\\s*\\.\\s*|@@)\\w+(\\s+AS\\s+\\w+)?(\\s*,\\s*)?)+\\s*$"
    #     ]
    #   }
    # -- The path where the skip command config file will be mounted.
    skipCommandConfigFile: /app/arisa/skip_command_config.json

 

# QueryPie 설치

helm upgrade --install poc querypie/querypie --version 1.4.1 -n querypie -f values.yaml

 

# 데이터베이스 마이그레이션

kubectl exec -it deployments/poc-querypie-tools -n querypie -- /app/script/migrate.sh runall

 

# 라이선스 등록

kubectl port-forward -n querypie deploy/poc-querypie-tools 8050:8050 &
curl -X POST http://127.0.0.1:8050/license/upload -F file=@license.crt
[License] Upload: Success  # 결과
# port-forward 종료
jobs
kill %1

 

# README.md

## QueryPie Deployment (PoC)

- Helm 기반 QueryPie 배포 구성
- Okta OIDC 연동 설계 및 Terraform IaC 구현
- Private Harbor Registry ImagePullSecret 구성

### Known Limitation
- QueryPie container images are hosted in a private registry (harbor.chequer.io)
- Image pull requires vendor-provided credentials
- Without registry credentials, Pods remain in ErrImagePull state