opteryx 0.8.2

Python SQL Query Engine

Query your data, where it lives.

Opteryx is a SQL Engine designed for embedded and cloud-native environments, and with command-line skills.

Documentation | Examples | Contributing

Use Cases

• Using SQL to query data written by another process, such as logs
• As a command line tool - Run SQL directly on files - bring the power and flexibility of SQL to filter and transform files
• As an embeddable engine - a low-cost option to allow hundreds of analysts to each have part-time databases

Features

Feature Rich

Supports most of the base SQL92 standard and multiple extensions from modern SQL platforms like Snowflake and Trino.

High Availability

Shared Nothing/Shared Disk design means each query can run in a separate container instance making it nearly impossible for a rogue query to affect any other users. (compute and storage can be shared)

If a cluster, region or datacentre is unavailable, if you have instances able to run in another location, Opteryx will keep responding to queries. (inflight queries may not be recovered)

Query In Place

Opteryx queries your data in the systems you store them in saving you from the cost and effort of maintaining duplicates your data into a common store for analytics.

You can store your data in parquet files on disk or Cloud Storage, and in MongoDB or Firestore and access all of these data in the same query.

Bring your own Files

Opteryx supports many popular data formats, including Parquet, ORC, Feather and JSONL, stored on local disk or on Cloud Storage. You can mix-and-match formats, so one dataset can be Parquet and another JSONL, and Opteryx will be able to JOIN across them.

Consumption-Based Billing Friendly

Opteryx is well-suited for deployments to environments which are pay-as-you-use, like Google Cloud Run. Great for situations where you low-volume usage, or many environments, where the costs of many traditional database deployment can quickly add up.

Python Native

Opteryx is Open Source Python, it quickly and easily integrates into Python code, including Jupyter Notebooks, so you can start querying your data within a few minutes.

Time Travel

Designed for data analytics in environments where decisions need to be replayable, Opteryx allows you to query data as at a point in time in the past to replay decision algorithms against facts as they were known in the past. (data must be structured to enable temporal queries)

Schema Evolution

Opteryx supports some change to schemas and paritioning without requiring any existing data to be updated. (data types can only be changed to compatitble types)

Fast

Benchmarks on M1 Pro Mac running an ad hoc GROUP BY over 1Gb of data via the CLI in 1/5th of a second. (different systems will have different performance characteristics)

Rows	Columns	File Size	Query Time
561225	81	1Gb	0.22sec
1064539	81	2Gb	0.27sec

Instant Elasticity

Designed to run in Knative and similar environments like Google Cloud Run, Opteryx can scale down to zero, and scale up to respond to thousands of concurrent queries within seconds.

Try Opteryx

Install from PyPI

pip install opteryx

Query Data (Command Line)

Example usage, filtering one of the internal example datasets and displaying the results on the console.

python -m opteryx "SELECT * FROM \$astronauts LIMIT 10;"

Query Data (Python)

Example usage, querying one of the internal example datasets.

import opteryx

conn = opteryx.connect()
cur = conn.cursor()
cur.execute("SELECT 4 * 7;")

print(cur.head())

For more example usage, see Example Notebooks and the Getting Started Guide.

Community

How do I get Support?

For support join our Gitter Community.

How Can I Contribute?

We are looking for volunteers to help build and direct Opteryx. If you are interested please use the Issues to let use know.

All contributions, bug reports, documentation improvements, enhancements, and ideas are welcome.

Want to help build Opteryx? See the Contribution and Set Up Guides.

Security

See the project Security Policy for information about reporting vulnerabilities.

License

Opteryx is licensed under Apache 2.0.

Status

Opteryx is in beta. Beta means different things to different people, to us, being beta means:

• Core functionality has good regression test coverage to help ensure stability
• Some edge cases may have undetected bugs
• Performance tuning may be incomplete
• Changes are focused on feature completion, bugs, performance, reducing debt, and security
• Code structure and APIs are not stable and may change