Try Opteryx

Run the Opteryx Notebooks

Several pages in these docs have a companion notebook that runs the page's examples for you. This page lists all of them in one place. Each opens in Google Colab with one click, or you can download the .ipynb file and run it in Jupyter on your own machine.

Each notebook installs what it needs in its first cell, so there is nothing to set up first. Only one needs an account: the SQLAlchemy notebook connects to opteryx.app.

Reading Files with Python

These four load a file into pandas with Rugo, then query the same file with SQL using Opteryx. Each downloads a small public dataset of orbital launches, so you don't need any data of your own.

Notebook What it covers Guide
Read a Parquet file Load into pandas, select columns, filter rows while reading, inspect a file before reading it, query with SQL Read a Parquet file
Read a JSON Lines file Load into pandas, fix column types, filter while reading, handle malformed lines, query with SQL Read a JSON Lines file
Read an Avro file Read the schema, select nested fields, read through a reader schema, convert to Parquet Read an Avro file
Read an ORC file Read the footer, select columns, take fields out of a nested struct, convert to Parquet Read an ORC file

Analysing Data

Exploratory analysis with Opteryx Core runs the examples from Exploratory analysis in notebooks in order: aggregate in SQL, take a fast first look at a dataset, chart a yearly summary with pandas, and iterate rows in Python. It uses a public dataset of 4,630 orbital launches in place of the transactions table the guide queries.

Open in Colab · Download

Querying Opteryx.app

Both of these read the public National Vulnerability Database (NVD) dataset on opteryx.app.

Query public data with OData uses only requests and pandas. It discovers the public datasets, reads a schema from $metadata, filters, sorts and counts, pages with @odata.nextLink, and aggregates CISA's known-exploited list. It needs no account. The guide is Querying via OData.

Open in Colab · Download

Query Opteryx.app with SQLAlchemy connects with the opteryx-sqlalchemy dialect, queries the NVD dataset into pandas, joins it to CISA's known-exploited list, then runs the same connection against your own datasets. It needs an opteryx.app account and a client ID and secret. The guide is Using SQLAlchemy from a notebook.

Open in Colab · Download

Benchmarks: DuckDB vs Opteryx

These two run workloads derived from the TPC-H and TPC-DS benchmarks against DuckDB and Opteryx side by side, on whatever machine runs the notebook. Each one generates the data, exports it to Parquet with DuckDB so both engines read the same files, runs every query on both engines in fresh processes, cross-checks row counts, and charts the result.

Notebook Queries Data
TPC-H The 22 TPC-H queries Generated with DuckDB's dbgen, scale factor 1 by default
TPC-DS The 99 queries, reporting coverage first: how many each engine plans and runs Generated with DuckDB's dsdgen, scale factor 1 by default

A Colab runtime is small and shared, so compare the two engines with each other, not with figures published anywhere else. These runs are not audited TPC benchmark results and are not comparable with published TPC results. Benchmarking explains the method and what the numbers are and are not.

Running a Notebook Locally

Download the .ipynb file and open it in Jupyter or VS Code. The notebooks install their packages with %pip and write their files into the notebook's working folder, so they run unchanged. The two benchmark notebooks use a tpch_bench or tpcds_bench folder next to the notebook when they are not on Colab. They are also the heavy ones: for a quick check, lower SCALE_FACTOR to 0.1.

TPC, TPC Benchmark, TPC-H, and TPC-DS are trademarks of the Transaction Processing Performance Council.