Getting started with SQLite

What is this?

This is a simple docker image hosting mixql-platform and SQLite. It can be used to easily spin up a SQLite database and test mixql queries.

Running the container default mode

If no other options are passed, mixql-platform-demo script is started with hardcoded script.

docker run -it --rm --name demo  mixql/demo:0.4
# Equal to
docker run -it --rm --name demo  mixql/demo:0.4 run

Expected result

########################################################
** Entrypoint for mixql-platform-demo-0.4.0-SNAPSHOT
########################################################
** Executing: ./mixql-platform-demo
** MIXQL_PLATFORM_DEMO_HOME_PATH=/mixql/app/mixql-platform-demo-0.4.0-SNAPSHOT/bin
** DB=
** SCRIPT=
** Cmdline: /mixql/app/mixql-platform-demo-0.4.0-SNAPSHOT/bin/mixql-platform-demo

Mixql engine demo platform: parsing args
Mixql engine demo platform: initialising engines
Init functions for mixql context
Mixql engine demo platform: init Cluster context
...

To execute the mixql-platform-demo script and stay inside the bash after execution use the bash command after run:

docker run -it --rm --name demo mixql/demo:0.4 run bash

Expected result

...
Broker thread finished...
sbt@0e0c062da510:/mixql/app/mixql-platform-demo-0.4.0-SNAPSHOT/bin$

To execute bash inside container without mixql-platform-demo script use only bash:

docker run -it --rm --name demo mixql/demo:0.4 bash

Expected result

########################################################
** Entrypoint for mixql-platform-demo-0.4.0-SNAPSHOT
** Executing 'bash'
sbt@5e287ce3b721:/mixql$

To execute a particular script, pass its name in the environment variable SCRIPT:

$ docker run -it --rm --name demo  \
    -e SCRIPT=/mixql/src/mixql-platform/mixql-platform-demo/src/test/resources/test_simple_func.sql \
    mixql/demo:0.4

Expected result

########################################################
** Entrypoint for mixql-platform-demo-0.4.0-SNAPSHOT
########################################################
** Executing: ./mixql-platform-demo
** MIXQL_PLATFORM_DEMO_HOME_PATH=/mixql/app/mixql-platform-demo-0.4.0-SNAPSHOT/bin
** DB=
** SCRIPT=/mixql/src/mixql-platform/mixql-platform-demo/src/test/resources/test_simple_func.sql
** Cmdline: /mixql/app/mixql-platform-demo-0.4.0-SNAPSHOT/bin/mixql-platform-demo  --sql-file /mixql/src/mixql-platform/mixql-platform-demo/src/test/resources/test_simple_func.sql
...

Similarly, to execute a particular script with a particular database from container, pass its names in the environment variables (SCRIPT and DB):

docker run -it --rm --name demo  \
    -e SCRIPT=/mixql/src/mixql-platform/mixql-platform-demo/src/test/resources/test_simple_func.sql \
    -e DB=/mixql/samples/db/sakila.db \
    mixql/demo:0.4

To set environment variables from single file use option --env-file filename

docker run -it --rm --name demo  \
    --env-file settings.env \
    mixql/demo:0.4

Running container with an already existing database and script

The container can be started with a volume linked to it to persist data and scripts on the host:

docker run -it --rm --name demo  \
    -v "/$(pwd)/samples/:/mixql-host/samples/" \
    -e SCRIPT=/mixql-host/samples/scripts/mixql-test-titanic.sql \
    -e DB=/mixql-host/samples/db/titanic.db \
    mixql/demo:0.4

In this case, you can debug scripts on the host machine (/samples/scripts → /mixql/samples/host/scripts), and use any SQLite dataset from the host machine (/samples/db → /mixql/samples/host/db).

Demo datasets

Sakila

Inside the container on the path /mixql/samples/db/sakila.db is a port of the Sakila training database for SQLite. (Note: Data about the film_text table is not provided in the script files, thus the film_text table is empty. Instead the film_id, title and description fields are included in the film table.)

docker run -it --rm --name demo  \
    mixql/demo:0.4 \
    sqlite3 /mixql/samples/db/sakila.db

of via docker compose:

docker compose run demo sqlite3 /mixql/samples/db/sakila.db

Expected result

########################################################
** Entrypoint for mixql-platform-demo-0.4.0-SNAPSHOT
** Executing 'sqlite3 /mixql/samples/db/sakila.db'
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> select count(1) from actor;
200
sqlite> .quit

Titanic

In this repository on path samples/db/titanic.db is a popular dataset "titanic" from kaggle in .db format from davidjamesknight

docker run -it --rm --name demo  \
    -v "/$(pwd)/samples/db/:/mixql-host/samples/db"     \
    mixql/demo:0.4 \
    sqlite3 /mixql-host/samples/db/titanic.db

of via docker compose:

docker compose run demo sqlite3 /mixql-host/samples/db/titanic.db

Expected result

########################################################
** Entrypoint for mixql-platform-demo-0.4.0-SNAPSHOT
** Executing 'sqlite3 /mixql-host/mixql/db/titanic.db'
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> select count(1) from Observation;
891
sqlite> .quit

Quick Start

Let’s calculate the overall survival rate in titanic.db. Firstly run simple SQLite script in /samples/scripts/sqlite-test.sql from host machine.

docker run -it --rm --name demo  \
    -v "/$(pwd)/samples/:/mixql-host/samples/"    \
    mixql/demo:0.4 \
    sqlite3 /mixql-host/samples/db/titanic.db '.read /mixql-host/samples/scripts/sqlite-test.sql'

Expected result

########################################################
** Entrypoint for mixql-platform-demo-0.4.0-SNAPSHOT
** Executing custom command: 'sqlite3 /mixql-host/samples/db/titanic.db '.read /mixql-host/samples/scripts/sqlite-test.sql'
Not Survived|549|61.62 %|891
Survived|342|38.38 %|891

Next, create similar mixql script /samples/scripts/mixql-test-titanic.sql on host:

let engine "sqlite-local";
let a = (select
            case when f.survived = 1 then 'Survived' else 'Not Survived' end as Survival_status,
            count(*) as Survival_rate,
            printf("%.2f", 100.0 * count(*) / max(f.total_passeng)) || " %" as Percent,
            max(f.total_passeng) as Total_passengers
            from   (
                    select  count(*) over() as total_passeng,
                            t.*
                    from Observation t
                    ) f
            group by f.alive_id;
        )
print($a);

Try it on SQLite database

docker run -it --rm --name demo  \
    -v "/$(pwd)/samples/:/mixql-host/samples" \
    -e SCRIPT=/mixql-host/samples/scripts/mixql-test-titanic.sql \
    -e DB=/mixql-host/samples/db/titanic.db \
    mixql/demo:0.4

Expected result

########################################################
** Entrypoint for mixql-platform-demo-0.4.0-SNAPSHOT
########################################################
** Executing: ./mixql-platform-demo
...

For developers

Folder structure

/mixql                                        <-- preinstalled files in container
|-- app/                                      <-- precompiled app from git
|   `-- mixql-platform-demo-0.4.0-SNAPSHOT
|       |-- bin                               <-- execution script
|       |-- lib                               <-- compiled libs
|   `-- mixql-platform-oozie-0.4.0-SNAPSHOT
|       |-- bin                               <-- execution script
|       |-- lib                               <-- compiled libs
|-- samples
|   |-- db                                    <-- sample dataset in container
|   `-- scripts
|-- src
|    `-- mixql-platform                       <-- source code from git
|
/mixql-host/                                  <-- shared folders from host
|-- app                                       <-- compiled app from /mixql-host/src/
|-- samples                                   <-- samples from host
`-- src                                       <-- source code from host to compile

Environment variables

Variable

Usage

SCRIPT

Specifies a file containing a mixql script to be executed by entrypoint.sh

DB

Specifies the database file SQLite should use entrypoint.sh or create if it not exists in

JAVA_VERSION

Version to install (mixql/demo image)

SCALA_VERSION

Version to install (mixql/demo image)

SBT_VERSION

Version to install (mixql/demo image)

MIXQL_APP_VERSION

Published version for path (mixql/demo image)

MIXQL_GIT_COMMIT

Git version of mixql to checkout (mixql/demo image)

BIGTOP_GIT_COMMIT

Git version of BigTop to checkout (mixql/bigtop image)

Rebuild image

If you want to change versions of apps or source code branch make new image (for example mixql/demo:0.4-local) with new settings (JAVA_VERSION, SCALA_VERSION, SBT_VERSION from sdk) and needed branch in GIT_COMMIT:

docker build --progress=plain \
    --build-arg JAVA_VERSION="8.0.362-tem" \
    --build-arg SCALA_VERSION="3.2.2" \
    --build-arg SBT_VERSION="1.8.2" \
    --build-arg MIXQL_APP_VERSION="0.3.0-SNAPSHOT" \
    --build-arg MIXQL_GIT_COMMIT="main" \
    -t mixql/demo:0.4-local ./dockerfiles/demo

or via docker-compose.yaml, change vars and start rebuild:

docker compose build demo

or with settings.env and one-liner for collectint of --build-arg from settings.env:

docker build --progress=plain \
    $(for i in `cat settings.env`; do out+="--build-arg $i " ; done; echo $out;out="") \
    -t mixql/demo:0.4 ./dockerfiles/demo

Compile from host source

For quick change you can build app from local folder, map source code from host to container (src → /app/src-host/mixql-platform/). Compiled app will be placed to /app/mixql-platform-demo-0.4.0-SNAPSHOT

docker run -it --rm --name demo  \
    -v "/$(pwd)/../mixql-platform/:/mixql-host/src/mixql-platform/" \
    -v "/$(pwd)/app:/mixql-host/app/" \
    mixql/demo:0.4 compile

Set versions before compiling

Before compiling you can install needed version of java, scala, sbt by environment variables via sdk:

docker run -it --rm --name demo  \
    -v "/$(pwd)/samples/:/mixql-host/samples/" \
    -v "/$(pwd)/../mixql-platform/:/mixql-host/src/mixql-platform/" \
    -v "/$(pwd)/app:/mixql-host/app" \
    -e SCRIPT=/mixql-host/samples/scripts/mixql-test-titanic.sql \
    -e DB=/mixql-host/samples/db/titanic.db \
    -e JAVA_VERSION=17.0.6-tem \
    mixql/demo:0.4 compile bash

Prepared commands for container

  • run - run precompiled app from /mixql/app;

  • run-host - run compiled app from /mixql-host/app. Be sure app is ready to use (is compiled);

  • compile - only compile app from /mixql-host/src;

  • compile-run - compile /mixql-host/src and run app from /mixql-host/app.

Use word bash after main command (run, compile…​) to stay in bash after execution: docker run -it --rm mixql/demo:0.4 compile bash