Installing on AWS EKS Environment

brew install awscli
aws configure  # Requires Access Key and Secret Key

brew install kubectl

brew install helm

brew install kubectx  # Context switching tool
brew install fzf      # Command-line fuzzy finder

kubectl create namespace querypie

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

kubectl apply -f pv.yml

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: "{your-mysql-password}"
          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

kubectl apply -f mysql.yml

kubectl get pods -w -n querypie | grep mysql

kubectl exec -it mysql-0 -n querypie -- mysql -u root -p
# The password is {your-mysql-password} in above example.

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

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", "{your-redis-password}"]
          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: {}
          # Data is deleted on pod restart. It is OK for cache purposes.
---
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

kubectl apply -f redis.yml

kubectl get pods -w -n querypie | grep redis

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

# Agent Secret (32-character random string)
AGENT_SECRET=01234567890123456789012345678912
KEK=querypie
 
# QueryPie Meta DB connection info
DB_CATALOG=querypie
DB_HOST=mysql
DB_USERNAME=querypie
DB_PASSWORD={your-mysql-password}
DB_PORT=3306
 
# QueryPie Log DB connection info
LOG_DB_CATALOG=querypie_log
LOG_DB_HOST=mysql
LOG_DB_USERNAME=querypie
LOG_DB_PASSWORD={your-mysql-password}
LOG_DB_PORT=3306
 
# QueryPie Snapshot DB connection info
ENG_DB_CATALOG=querypie_snapshot
ENG_DB_HOST=mysql
ENG_DB_USERNAME=querypie
ENG_DB_PASSWORD={your-mysql-password}
ENG_DB_PORT=3306
 
# Redis connection info
REDIS_NODES=redis:6379
REDIS_PASSWORD={your-redis-password}

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

# Add repository
helm repo add querypie https://chequer-io.github.io/querypie-deployment/helm-chart
 
# Verify repository list
helm repo list

# -- appVersion: QueryPie version to install. This is an example value.
# -- Please refer to the Product Versions document for available versions.
appVersion: &version 11.5.1
 
global:
  image:
    # -- Default registry used for all images
    # For Docker Hub: docker.io
    # For Harbor: harbor.chequer.io
    registry: docker.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.
  # For Docker Hub: Set imagePullSecrets to an empty array ([]) or remove this section.
  # For Harbor: Create the querypie-regcred secret and uncomment below.
  imagePullSecrets: []
  # Uncomment below when using Harbor registry:
  # 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 # Replace with your actual 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 configuration
  # Starting from version 11.3.0, log rotation is built into the main container.
  # The logrotator image is only available on Harbor registry.
  # When using Docker Hub, this must be disabled.
  logrotator:
    enabled: false
    # Uncomment below and set enabled: true when using Harbor registry:
    # image:
    #   repository: querypie/logrotate
    #   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" # Replace with your actual 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

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

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

kubectl get pods -n querypie

kubectl logs pod/poc-querypie-0 -f

kubectl port-forward -n querypie statefulsets/poc-querypie 80:80

# Verify cluster access
kubectl cluster-info
 
# Check current context
kubectl config current-context
 
# Verify namespace access permissions
kubectl auth can-i create deployment -n querypie

# Check node resource status
kubectl top nodes
 
# Check available storage classes
kubectl get storageclass

# Check pod status
kubectl get pods -n querypie
 
# Check pod details
kubectl describe pod <pod-name> -n querypie
 
# Check pod logs
kubectl logs <pod-name> -n querypie
kubectl logs <pod-name> -n querypie --previous  # Check previous logs if restarted

# Check service status
kubectl get svc -n querypie
 
# Check endpoints
kubectl get endpoints -n querypie
 
# Test MySQL service connection
kubectl exec -it mysql-0 -- mysql -u querypie -p -h mysql querypie
 
# Test Redis service connection
kubectl exec -it $(kubectl get pod -l app=redis -o jsonpath='{.items[0].metadata.name}') -- redis-cli -a querypie ping

# Check ingress status
kubectl get ingress -n querypie
 
# Check ingress details
kubectl describe ingress -n querypie

# Check pod CPU/Memory usage
kubectl top pods -n querypie
 
# Check node CPU/Memory usage
kubectl top nodes
 
# Check resource limits for a specific pod
kubectl get pod <pod-name> -n querypie -o jsonpath='{.spec.containers[0].resources}'

# Check ConfigMap
kubectl get configmap -n querypie
 
# Check Secret list
kubectl get secrets -n querypie
 
# Check PV/PVC status
kubectl get pv
kubectl get pvc -n querypie

# Kubernetes version
kubectl version --short
 
# Node information
kubectl get nodes -o wide
 
# Helm version
helm version

# Helm deployment status
helm status poc -n querypie
 
# Pod details
kubectl describe pod/poc-querypie-0 -n querypie
 
# Recent events list
kubectl get events -n querypie --sort-by='.lastTimestamp'

# Save QueryPie logs
kubectl logs pod/poc-querypie-0 -n querypie > querypie.log
 
# Save related component logs
kubectl logs mysql-0 > mysql.log
kubectl logs $(kubectl get pod -l app=redis -o jsonpath='{.items[0].metadata.name}') > redis.log