Radio-Duck

Connect to a Duckdb server over Http.

Project Objectives

• Talk to duckDb over transport protocols like Http.
• Provide an alternative to embedding duckDb
• Make DuckDb highly available for apps like Business intelligence dashboards
• Ability to query data while data is being loaded.

Use cases

• Act as data backend for Business intelligence dashboards like (metabase/superset) without embedding duckDb

• Highly available Duckdb

Features

Run sql on Duckdb over http

Run sql

You can send a sql via http post request. refer to docs @ http://localhost:8000/docs

Transaction support

It's possible but in a limited fashion (i.e. in a single http post request)

#example:
curl -X 'POST' \
  'http://localhost:8000/v1/sql/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "sql": "BEGIN; select * from pond; COMMIT;"
}'

Load data from cloud blob storage like s3/azure

It's the same as you are interacting with duckDb using sql.

Consume data from streams like kafka

[todo]

Requirements

• install mamba https://mamba.readthedocs.io/en/latest/
• install docker

cd PROJECT_DIR
mamba create -n radio-duck python=3.10
mamba activate radio-duck
mamba install poetry
poetry install

Run Tests

pytest

Live Demo

Feel free to run some sql with https://radioduck.pythonanywhere.com/docs

Note: Please try it on firefox/chrome. safari has some issue.

Try me with Docker

• Image available at dockerHub for Platforms: linux/arm64,linux/amd64.
• https://hub.docker.com/r/jaihind213/radio-duck

#pull from docker hub
docker pull jaihind213/radio-duck:latest
# for arm64 -> 'docker run --platform linux/arm64 -p 8000:8000 -t jaihind213/radio-duck:latest'
docker run -p 8000:8000 -t jaihind213/radio-duck:latest
#or
docker run -p 8000:8000 -v <path_to_data_dir>:/radio-duck/pond -t jaihind213/radio-duck:latest
#or
docker run -p 8000:8000 -v <path_to_data_dir>:/radio-duck/pond -v <path_to_my_config.ini>:/radio-duck/pond/my_config.ini -t jaihind213/radio-duck:latest python3 /radio-duck/server.py /radio-duck/pond/my_config.ini

Then access http://localhost:8000/docs and play with the api.

Configure RadioDuck with your secret credentials to access Azure blob storage / Aws s3

refer to https://duckdb.org/docs/configuration/secrets_manager.html

Build Docker image

sh buildDocker.sh <proj_version> <duckb_version>
#sh buildDocker.sh 0.1.2 0.9.0 local no

Docker Image versioning

The docker image version is in the following format:

d<DUCKDB_VERSION>-v<PROJECT_VERSION>-<DOCKERFILE_VERSION>
ex: d0.10.0-v0.1.2-c1d9c346 

This specifies that radio duck is running with duckdb=0.10.0, the project version being 0.1.2.
These versions are derived from pyproject.toml

Try me with Apache Superset

#run radio-duck
docker run -p 8000:8000 -t jaihind213/radio-duck:latest
# for macM1 -> 'add --platform linux/amd64 '
#run superset
docker pull jaihind213/superset-radioduck:latest
# for macM1 -> 'add --platform linux/amd64 '
docker run -e TALISMAN_ENABLED="False" -e ADMIN_USERNAME=admin -e ADMIN_PASSWORD=admin -e SUPERSET_SECRET_KEY=<some_good_key> -p 8088:8088 jaihind213/superset-radioduck:latest

Then access http://localhost:8088/

Then configure database with alchemy URI 'radio_duck+district5://user:pass@<HOST_IP>:8000/?api=/v1/sql/&scheme=http'

Video of Demo.

Try me with Python code via District5 library

https://github.com/jaihind213/district5

#python 3.9^
pip install district5
#start the duckdb server i.e. radio-duck
docker run -p 8000:8000 -t jaihind213/radio-duck:latest
#the duckdb starts up with a sample table whose ddl is: 
#'create table pond(duck_type string, total int)'
echo "we will try to query that"

from sqlalchemy import create_engine, text
from sqlalchemy.dialects import registry

registry.register(
    "radio_duck.district5", "radio_duck.sqlalchemy", "RadioDuckDialect"
)


#run docker instance of radio-duck
#docker run -p 8000:8000 -t jaihind213/radio-duck:latest
engine = create_engine(
    "radio_duck+district5://user:pass@localhost:8000/?api=/v1/sql/&scheme=http"
)
# Establish a database connection
conn = engine.connect()

# Define a SQL query using qmark style or positional style
try:
    query_1 = text("""SELECT duck_type, total FROM pond where total > :total""")
    params = {"total": 0}
    result = conn.execute(query_1, params)
    # Fetch and print the results
    for row in result:
        print(row)

    print("--------------")
    query_2 = "SELECT duck_type, total FROM pond where total > ?"
    result = conn.execute(query_2, (0,))

    for row in result:
        print(row)

except Exception as e:
    print(f"An error occurred: {e}")

finally:
    # Close the database connection
    conn.close()
    engine.dispose()

Check for Docker image Vulnerabilities

sh buildDocker.sh <version>
sh docker_sec_check.sh
# github actions is scanning for Vulnerabilities

Notes:

• todo: stream large datasets / compress results say json-smile?
• todo: limit number of conns to duckdb ?
• todo: return query_id ,useful for debugging.
• todo: handle transactions over http ? i.e. multiple http requests.