Skip to content
RedHatInsightsPublic

About

Floorist is a simple script to dump SQL queries from a PostgreSQL database into S3 buckets in parquet format.

Topics

Resources

Stars

4 stars

Watchers

7 watching

Forks

Repository files navigation

Floorist

Build Status

Floorist is a simple script to dump SQL queries from a PostgreSQL database into S3 buckets in parquet format.

Usage

The tool has been designed for use in Kubernetes or OpenShift, but you can also run it locally by using docker or podman. It can be configured through environment variables and a single floorplan (YAML) file.

docker build -t floorist .

docker run --rm floorist \
  -e POSTGRES_SERVICE_HOST=localhost \
  -e POSTGRESQL_USER=root \
  -e POSTGRESQL_PASSWORD=123456 \
  -e AWS_ACCESS_KEY_ID=your_key_id \
  -e AWS_SECRET_ACCESS_KEY=your_secret_access_key \
  -e AWS_REGION=us-east-1 \
  -e AWS_BUCKET=floorist-dumps \
  -e FLOORPLAN_FILE=floorplan.yml \
  -v floorplan.yml:floorplan.yml

Environment variables

  • POSTGRES_SERVICE_HOST
  • POSTGRESQL_USER
  • POSTGRESQL_PASSWORD
  • POSTGRESQL_DATABASE
  • AWS_ACCESS_KEY_ID
  • AWS_SECRET_ACCESS_KEY
  • AWS_REGION
  • AWS_BUCKET
  • AWS_ENDPOINT - not mandatory, for using with minio
  • FLOORPLAN_FILE - should point to the floorplan (YAML) file

Floorplan file

The floorplan file simply defines a list of a prefix-query pair. The prefix should be a valid folder path that will be created under the bucket if it does not exist. For the queries it is recommended to assign simpler aliases for dynamically created (joins or aggregates) columns using AS. Optionally you can set a custom chunksize for the query (default is 1000) that will serve as the maximum number of records in a single parquet file. If the chunksize is set to 0, all records will be dumped into a single parquet file. Note that this can consume a lot of memory in case of a large SQL result.

- prefix: dumps/people
  query: >-
    SELECT name, email, birthyear FROM people;
- prefix: dumps/cities
  query: >-
    SELECT name AS city_name, zip, country FROM cities;
  chunksize: 100

The example above will create two dumps under the S3 bucket specified in the AWS_BUCKET environment variable into the <prefix>/year_created=<Y>/month_created=<M>/day_created=<D>/<UUID>.parquet files.

Clowder - How to add Floorist to your Clowder template

You only need to add a new job definition on your ClowdApp, and a ConfigMap with the Floorplan definition your app needs.

An example configuring the Floorist job follows, using a secret to host the S3 bucket config, the config map definition, and a floorplan Configmap example for reference.

#Clowdapp.yaml

- apiVersion: cloud.redhat.com/v1alpha1
  kind: ClowdApp
  metadata:
    name: "${APP_NAME}"
  spec:
    jobs:
    - name: floorist
      schedule: ${FLOORIST_SCHEDULE}
      suspend: ${FLOORIST_SUSPEND}
      concurrencyPolicy: Forbid
      podSpec:
        image: ${FLOORIST_IMAGE}:${FLOORIST_IMAGE_TAG}
        env:
        - name: AWS_BUCKET
          valueFrom:
            secretKeyRef:
              name: ${FLOORIST_BUCKET_SECRET_NAME}
              key: bucket
        - name: AWS_REGION
          valueFrom:
            secretKeyRef:
              name: ${FLOORIST_BUCKET_SECRET_NAME}
              key: aws_region
        - name: AWS_ENDPOINT
          valueFrom:
            secretKeyRef:
              name: ${FLOORIST_BUCKET_SECRET_NAME}
              key: endpoint
        - name: AWS_ACCESS_KEY_ID
          valueFrom:
            secretKeyRef:
              name: ${FLOORIST_BUCKET_SECRET_NAME}
              key: aws_access_key_id
        - name: AWS_SECRET_ACCESS_KEY
          valueFrom:
            secretKeyRef:
              name: ${FLOORIST_BUCKET_SECRET_NAME}
              key: aws_secret_access_key
        - name: FLOORPLAN_FILE
          value: "/tmp/floorplan/floorplan.yaml"
        - name: LOGLEVEL
          value: ${FLOORIST_LOGLEVEL}
        volumeMounts:
        - name: floorplan-volume
          mountPath: "/tmp/floorplan"
        volumes:
          - name: floorplan-volume
            configMap:
              name: floorplan
      resources:
          limits:
            cpu: "${CPU_LIMIT_FLOORIST}"
            memory: "${MEMORY_LIMIT_FLOORIST}"
          requests:
            cpu: "${CPU_REQUEST_FLOORIST}"
            memory: "${MEMORY_REQUEST_FLOORIST}"
- apiVersion: v1
  kind: ConfigMap
  metadata:
    name: floorplan
  data:
    floorplan.yaml: |
      - prefix: insights/yout-service-name/hosts-query
        query: >-
          SELECT
            "inventory"."hosts"."id",
              OR "inventory"."hosts"."id" IN (SELECT "test_results"."host_id" FROM "test_results"));
      - prefix: insights/your-service-name/policies-query
        query: >-
          SELECT DISTINCT
            "policies"."id",
            "profiles"."ref_id",
      - prefix: insights/your-service-name/policy_hosts-query
        query: >-
          SELECT "policy_hosts"."host_id", "policy_hosts"."policy_id" FROM "policy_hosts";

parameters:
- name: MEMORY_LIMIT_FLOORIST
  value: 200Mi
- name: MEMORY_REQUEST_FLOORIST
  value: 100Mi
- name: CPU_LIMIT_FLOORIST
  value: 100m
- name: CPU_REQUEST_FLOORIST
  value: 50m
- name: FLOORIST_SCHEDULE
  description: Cronjob schedule definition
  required: true
  value: "0 2 * * *"
- name: FLOORIST_SUSPEND
  description: Disable Floorist cronjob execution
  required: true
  value: "true"
- description: Floorist image name
  name: FLOORIST_IMAGE
  value: quay.io/cloudservices/floorist
- description: Floorist Image tag
  name: FLOORIST_IMAGE_TAG
  required: true
  value: latest
- description: Shared bucket name
  name: FLOORIST_BUCKET_NAME
  required: true
  value: floorist-bucket

Testing

For testing the tool, you will need PostgreSQL and minio. There's two ways of running the tests, you can run them locally using pytest from your localhost or you can run everything from containers like we do on our CI process.

Currently there are 2 ways how to start the tests:

  1. docker-compose/podman compose
  2. in local Minikube cluster

Running tests via docker-compose/podman-compose

There's a Docker Compose file provided in the test folder with everything prepared and configured. The configuration for these two services has to be stored in the tests/env.yaml file, for the Docker Compose setup it's enough to copy the the tests/env.yaml.example to make it work. However, if you would like to bring your own PostgreSQL server or maybe use a real S3 bucket, you have to edit these values accordingly. The tests can be started via pytest.

Explicit

# Create venv
python3 -m venv .venv && source .venv/bin/activate

# Install with Test dependencies
# You might have to escape the square brackets (e.g. when using `zsh`)
pip install -e .[test] -r requirements.txt

# Set the environment config file
cp tests/env.yaml.example tests/env.yaml

# Bring up all the required containers (or use podman-compose)
docker compose -f tests/docker-compose.yml up -d

# Run the tests locally
pytest -vvv -s tests

# Tear-down
docker compose -f tests/docker-compose.yml down

Makefile

Many of the above manual steps have been automated in the Makefile.

cp tests/env.yaml.example tests/env.yaml
make init
make
make compose-down

Running tests from containers

Alternatively, you can also run the same process the CI system runs, locally, by using Minikube. Build the test image first and push it to quay repo:

podman build  -f Dockerfile -t quay.io/user/floorist:latest
podman push quay.io/user/floorist:latest
export CONTAINER_IMAGE="quay.io/user/floorist:latest"

Start Minikube:

minikube start

Deploy postgresql/minio resources and start test job:

scripts/deploy_test_env.sh

the scripts/deploy_test_env.sh script will:

  • Deploys all the resources (postgresql/minio) needed for testing into Minikube
  • Minikube pulls the image which is defined via CONTAINER_IMAGE variable
  • Run the tests in a container using the aforementioned image

Same script is used to start ITS Tekton testing in PR in on-pr and on-push pipelines. For more information what templates are used, check documentaion

Contributing

Bug reports and pull requests are welcome, here are some ideas for improvement:

  • More fine-grained specification of the output path and filename in the floorplan (e.g. custom timestamps)
  • Additional parameters for the parquet exporting (column partitioning, compression, indexing, column types)
  • Support for other databases
  • Support for different object storage services
  • Better tests
  • Pylint support

License

The application is available as open source under the terms of the Apache License, version 2.0.

About

Floorist is a simple script to dump SQL queries from a PostgreSQL database into S3 buckets in parquet format.

Topics

Resources

Stars

4 stars

Watchers

7 watching

Forks

Releases

Packages

Used by

Contributors

Languages