PythonDB - Python as a Partitioned Database

PythonDB is an open source experiment to expose python instances as databases. In particular it exposes DuckDB and Polars SQL via a MySQL interface.

Video Demo
Component Pieces
Parquet Format
Arrow Memory Structure
Demo Code

Summary

Polars / DuckDB are amazing for data analysis.
We will be using Parquet + PyArrow in future.

Component Pieces

Snake = Python - Data Analysis lingua franca
Duck = DuckDB - Fast free column database. 2024-06-03 - Version 1.0
PolarBear = Polars - “Dataframes for the new era”
- Numpy - 2005 - large, multi-dimensional arrays
- Pandas - ~2009 - Dataframes + functions for analysis
- Polars - 2024-07-01 - Version 1.0 + DF + SQL
Dolphin = MySQL - Humongously popular database

Parquet File Structure

Parquet is an open source, column-oriented data file format designed for efficient data storage and retrieval. It provides efficient data compression and encoding schemes with enhanced performance to handle complex data in bulk. Apache Parquet is designed to be a common interchange format for both batch and interactive workloads.

Credit Michael Berk

Arrow Memory Structure

Apache Arrow contains a standardized column-oriented memory format that is able to represent flat and hierarchical data for efficient analytic operations on modern CPU and GPU hardware.

Demo Code

-- Run python
-- Can select from remotely hosted data sources HTTP / S3 / Delta Lake
-- F:\temp\data-analysis>pythondb --language python  --webport 6060
pythondb.setlang('python')
pd_df2 = pd.DataFrame(data={'col1': [1, 2], 'col2': [3, 4]})
pl_df = pl.DataFrame({"a": [6, 7], "b": [8, 9]})

duckdb.sql('select * from pl_df where b<9')
duckdb.sql("SELECT date,AVG(orderType) FROM read_parquet('https://www.timestored.com/kola/data_0.parquet') WHERE date > '2023-09-01' AND ticker='META' GROUP BY date;")
duckdb.sql("CREATE TABLE mortgage AS SELECT * FROM read_csv('https://fred.stlouisfed.org/graph/fredgraph.csv?id=MORTGAGE30US')")
duckdb.sql("COPY mortgage TO 'F:\\temp\\data-analysis\\mortgage.parquet' (FORMAT PARQUET);")
-- We can use DuckDB to query local and remote easily with DataFrames handled easily.

con = duckdb.connect('F:\\temp\\data-analysis\\rule605-20240311.duckdb')
con.sql("SHOW tables;")
con.sql("SELECT * FROM monthly limit 10")
con.sql("COPY monthly TO 'F:\\temp\\data-analysis\\monthly' (FORMAT PARQUET, PARTITION_BY (date));")
duckdb.sql("CREATE VIEW m AS SELECT * FROM read_parquet('F:\\temp\data-analysis\\monthly\\*\\*.parquet', hive_partitioning = true);")
-- We can create date partitioned databases


-- CREATE TABLE t AS SELECT * FROM monthly ORDER BY date,ticker
duckdb.sql("SELECT * FROM m WHERE date='2023-04-01' AND ticker='META' LIMIT 100")
dk>SELECT * FROM m WHERE date='2023-04-01' AND ticker='META' LIMIT 100
pythondb.setlang('duckdb')
SELECT * FROM m WHERE date='2023-04-01' AND ticker='META' LIMIT 100
SELECT date,AVG(orderType) FROM read_parquet('https://www.timestored.com/kola/data_0.parquet') WHERE date > '2023-09-01' AND ticker='META' GROUP BY date;
-- CREATE VIEW mortgage AS SELECT * FROM read_parquet('F:\temp\data-analysis\mortgage.parquet');
-- SEE SLIDE with parquet format
SELECT * FROM parquet_metadata('F:\\temp\\data-analysis\\monthly\\date=2023-04-01\\data_0.parquet') WHERE path_in_schema = 'ticker';
SELECT row_group_id,path_in_schema,row_group_num_rows,stats_min,stats_max FROM parquet_metadata('F:\\temp\\data-analysis\\monthly\\date=2023-04-01\\data_0.parquet') WHERE path_in_schema = 'firmId';
SELECT numCoveredOrders FROM m WHERE date='2023-04-01' AND firmId='virtu' AND ticker='META' 
SELECT count(*) FROM m WHERE date='2023-04-01' AND firmId='virtu'
-- Show in procmon64 on desktop?
-- OK so fast and efficient date partitioned databases
-- TO recap what technology we seen

-- Now access via qStudio
SELECT COUNT(DISTINCT(date)) FROM m
SELECT COUNT(DISTINCT(ticker)) FROM m
SELECT COUNT(*) FROM m
SELECT marketId,ticker,SUM(orderSize),SUM(tsCancelled) AS orderSize FROM m WHERE ticker IN ('META','GOOG') GROUP BY marketId,ticker 


-- Run in qStudio
-- Run in Pulse
SELECT * FROM 
    (PIVOT (SELECT date,firmId,(sum(tsExecAtImprovement)+sum(tsExecAtQuote)+sum(tsExecOutside)) as execed 
                     FROM m 
                           WHERE ticker like 'GME' AND date>'2020-01-01' 
                           GROUP BY date,firmId ORDER BY date) 
       ON firmId USING sum(execed))
    ORDER BY date 
    
-- Polars as well

>>>mdf = pl.scan_parquet('F:\\temp\\data-analysis\\monthly')
>>>mdf.sql('SELECT * FROM self LIMIT 10').collect()
>>>mdf.filter(pl.col("date") > datetime(2023, 9, 1)).filter(pl.col("ticker") == 'META').group_by("date").agg(pl.col("ticker").count()).collect()
>>>pl.sql("SELECT date,count(ticker) FROM mdf WHERE date > '2023-09-01' AND ticker='META' GROUP BY date;").collect()

>>>num_rows = 5000
>>>rng = np.random.default_rng(seed=7)
>>>buildings_data = {"sqft": rng.exponential(scale=1000, size=num_rows), "year": rng.integers(low=1995, high=2023, size=num_rows),"building_type": rng.choice(["A", "B", "C"], size=num_rows), }
>>>buildings = pl.DataFrame(buildings_data)

pl>SELECT * FROM buildings
dk>SELECT * FROM buildings

q)til 10
import mypythondb
mypythondb.start(port=3145, webport=9090, language='duckdb')

>>>2+5