An Ibis backend for GizmoSQL
Project description
ibis-gizmosql
Setup (to run locally)
Install Python package
You can install ibis-gizmosql
from PyPi or from source.
Option 1 - from PyPi
# Create the virtual environment
python3 -m venv .venv
# Activate the virtual environment
. .venv/bin/activate
pip install ibis-gizmosql
Option 2 - from source - for development
git clone https://github.com/gizmodata/ibis-gizmosql
cd ibis-gizmosql
# Create the virtual environment
python3 -m venv .venv
# Activate the virtual environment
. .venv/bin/activate
# Upgrade pip, setuptools, and wheel
pip install --upgrade pip setuptools wheel
# Install the Ibis GizmoSQL back-end - in editable mode with client and dev dependencies
pip install --editable .[dev,test]
Note
For the following commands - if you running from source and using --editable
mode (for development purposes) - you will need to set the PYTHONPATH environment variable as follows:
export PYTHONPATH=$(pwd)/ibis_gizmosql
Usage
In this example - we'll start a GizmoSQL server with the DuckDB back-end in Docker, and connect to it from Python using Ibis.
First - start the GizmoSQL server - which by default mounts a small TPC-H database:
docker run --name gizmosql \
--detach \
--rm \
--tty \
--init \
--publish 31337:31337 \
--env TLS_ENABLED="1" \
--env GIZMOSQL_PASSWORD="gizmosql_password" \
--env PRINT_QUERIES="1" \
--pull missing \
gizmodata/gizmosql:latest
Next - connect to the GizmoSQL server from Python using Ibis by running this Python code:
import os
import ibis
from ibis import _
# Kwarg connection example
con = ibis.gizmosql.connect(host="localhost",
user=os.getenv("GIZMOSQL_USERNAME", "gizmosql_username"),
password=os.getenv("GIZMOSQL_PASSWORD", "gizmosql_password"),
port=31337,
use_encryption=True,
disable_certificate_verification=True
)
# URL connection example
# con = ibis.connect("gizmosql://gizmosql_username:gizmosql_password@localhost:31337?disableCertificateVerification=True&useEncryption=True")
print(con.tables)
# assign the LINEITEM table to variable t (an Ibis table object)
t = con.table('lineitem')
# use the Ibis dataframe API to run TPC-H query 1
results = (t.filter(_.l_shipdate.cast('date') <= ibis.date('1998-12-01') + ibis.interval(days=90))
.mutate(discount_price=_.l_extendedprice * (1 - _.l_discount))
.mutate(charge=_.discount_price * (1 + _.l_tax))
.group_by([_.l_returnflag,
_.l_linestatus
]
)
.aggregate(
sum_qty=_.l_quantity.sum(),
sum_base_price=_.l_extendedprice.sum(),
sum_disc_price=_.discount_price.sum(),
sum_charge=_.charge.sum(),
avg_qty=_.l_quantity.mean(),
avg_price=_.l_extendedprice.mean(),
avg_disc=_.l_discount.mean(),
count_order=_.count()
)
.order_by([_.l_returnflag,
_.l_linestatus
]
)
)
print(results.execute())
You should see output:
l_returnflag l_linestatus sum_qty sum_base_price sum_disc_price sum_charge avg_qty avg_price avg_disc count_order
0 A F 380456.00 532348211.65 505822441.49 526165934.00 25.58 35785.71 0.05 14876
1 N F 8971.00 12384801.37 11798257.21 12282485.06 25.78 35588.51 0.05 348
2 N O 765251.00 1072862302.10 1019517788.99 1060424708.62 25.47 35703.76 0.05 30049
3 R F 381449.00 534594445.35 507996454.41 528524219.36 25.60 35874.01 0.05 14902
Handy development commands
Version management
Bump the version of the application - (you must have installed from source with the [dev] extras)
bumpver update --patch
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
ibis_gizmosql-0.0.4.tar.gz
(25.7 kB
view hashes)
Built Distribution
Close
Hashes for ibis_gizmosql-0.0.4-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 3a050f2c2bd5b0efa92d5a12a8cbd9f7f96741d792d165ed31a105a847906079 |
|
MD5 | 43f806bac7cd75b5fdc7e29781f3136e |
|
BLAKE2b-256 | 8bc2b0a28b4b2c8aacb2f7100469d3ab38d200bc3c8d7f7111c6a642ccf0e5bd |