KO | EN

GitLite

Minimalist Open Source Repository Search Engine

gitlite — search

#dsh #dsh-plugin #developer-tools #ai-agent #claude-code #codex #typescript #ai-agents #deepseek-harness #mcp #cli #rust

opteryx

★ 113 Open GitHub ↗

🦖 A SQL-on-everything Query Engine you can execute over multiple databases and file formats. Query your data, where it lives.

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# Opteryx
### Query your data, where it lives.

> ⚠️ **Repository status:** This repository is currently in a **bug‑fix only** state while the internals of the engine undergo a major rewrite in the separate [`opteryx-core`](https://github.com/mabel-dev/opteryx-core) repository. New features and breaking changes live there; this repo will remain stable to avoid surprises.

_A unified SQL interface to unlock insights across your diverse data sources, from blobs stores to databases - **effortless cross-platform data analytics**._

![Opteryx](https://raw.githubusercontent.com/mabel-dev/opteryx.dev/main/assets/terminal.gif) We're excited to have you join our journey. Let us know how we can help!

[![PyPI Latest Release](https://img.shields.io/pypi/v/opteryx.svg)](https://pypi.org/project/opteryx/)
[![Downloads](https://static.pepy.tech/badge/opteryx)](https://pepy.tech/project/opteryx)
[![codecov](https://codecov.io/gh/mabel-dev/opteryx/branch/main/graph/badge.svg?token=sIgKpzzd95)](https://codecov.io/gh/mabel-dev/opteryx)
[![opteryx](https://snyk.io/advisor/python/opteryx/badge.svg?style=flat-square)](https://snyk.io/advisor/python/opteryx)
[![PyPI Latest Release](https://img.shields.io/badge/Python-3.11%20%7C%203.12%20%7C%203.13-blue?logo=python)](https://pypi.org/project/opteryx/)

[**Install**](#install) •
[**Examples**](#examples) •
[**Get Involved**](#community)

Resource        | Location
--------------- | -------------
Source Code     | https://github.com/mabel-dev/opteryx
Documentation   | https://opteryx.dev/
Download        | https://pypi.org/project/opteryx/

## What is Opteryx?

Opteryx is a Python library enabling SQL queries across diverse data sources like Apache Iceberg, Postgres, Parquet, and MongoDB. Opteryx champions the SQL-on-everything approach, streamlining cross-platform data analytics by federating SQL queries across diverse data sources. The goal is to enhance your data analytics process by offering a unified way to access data from across your organization. Opteryx combines the simplicity of SQLite and DuckDB with federated query capabilities found in Presto and Trino.

Opteryx offers the following features:

- SQL queries on data files generated by other processes, such as logs
- A command-line tool for filtering, transforming, and combining files
- Integration with familiar tools like pandas and Polars
- Embeddable as a low-cost engine, enabling portability and allowing for hundreds of analysts to leverage ad hoc databases with ease
- Unified and federated access to data on disk, in the cloud, and in on-premises databases, not only through the same interface but in the same query

## How Does it Work?

Opteryx processes queries by first determining the appropriate query language to interact with different downstream data platforms. It translates your query into SQL, CQL, or another suitable query language based on the data source. This enables Opteryx to efficiently retrieve the necessary data from systems such as MySQL or MongoDB to respond to your query.

![Opteryx](https://raw.githubusercontent.com/mabel-dev/opteryx.dev/main/assets/sankeymatic_20240501_224124_1600x1200.png)

## Why Use Opteryx?

### __Familiar Interface__

Opteryx supports key parts of the [Python DBAPI](https://peps.python.org/pep-0249/) and [SQL92 standard](https://opteryx.dev/latest/get-started/external-standards/sql92/) standards which many analysts and engineers will already know how to use.

### __Consistent Syntax__

Opteryx creates a common SQL-layer over multiple data platforms, allowing backend systems to be upgraded, migrated or consolidated without changing any Opteryx code.

Where possible, errors and warnings returned by Opteryx help the user to understand how to fix their statement to reduce time-to-success for even novice SQL users.

### __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 have low-volume usage, or multiple environments, where the costs of many traditional database deployment can quickly add up.

### __Python Ecosystem__

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. Opteryx integrates with many of your favorite Python data tools, you can use Opteryx to run SQL against pandas and Polars DataFrames, and even execute a `JOIN` on an in-memory DataFrame and a remote SQL dataset.

### __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. You can even self-join tables historic data, great for finding deltas in datasets over time. _(data must be structured to enable temporal queries)_

### __Fast__

Benchmarks on M2 Pro Mac running an ad hoc `GROUP BY` over a 6 million row parquet file via the CLI in ~1/4th of a second from a cold start (no caching and predefined schema). _(different systems will have different performance characteristics)_

### __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.

### __Bring your own Data__

![Opteryx](https://github.com/mabel-dev/opteryx.dev/raw/main/assets/data-stores.png)

Opteryx supports multiple query engines, dataframe APIs and storage formats. You can mix-and-match sources in a single query. Opteryx can even `JOIN` datasets stored in different formats and different platforms in the same query, such as Parquet, Apache Iceberg and MySQL.

Opteryx allows you to query your data directly in the systems where they are stored, eliminating the need to duplicate data into a common store for analytics. This saves you the cost and effort of maintaining duplicates.

Opteryx can push parts of your query to the source query engine, allowing queries to run at the speed of the backend, rather than your local computer.

And if there's not a connector in the box for your data platform; feel free to submit a pull request to add one.

## Install

Installing from PyPI is recommended.

~~~bash
pip install opteryx
~~~

To build Opteryx from source, refer to the [contribution guides](https://opteryx.dev/latest/contributing/contributing/).

Opteryx installs with a small set of libraries it needs for core functionality, such as [Numpy](https://numpy.org/doc/stable/index.html), [PyArrow](https://arrow.apache.org/), and [orjson](https://github.com/ijl/orjson). Some features require additional libraries to be installed, you are notified of these libraries as they are required.

## Examples

<details>
<summary>Filter a Dataset on the Command Line</summary>

In this example, we are running Opteryx from the command line to filter one of the internal example datasets and display the results on the console.

~~~bash
python -m opteryx "SELECT * FROM \$astronauts WHERE 'Apollo 11' IN UNNEST(missions);"
~~~

![Opteryx](https://github.com/mabel-dev/opteryx.dev/raw/main/assets/cli.png)
_this example is complete and should run as-is_
</details>

<details>
<summary>Execute a Simple Query in Python</summary>

In this example, we are showing the basic usage of the Python API by executing a simple query that makes no references to any datasets.

~~~python
# Import the Opteryx SQL query engine library.
import opteryx

# Execute a SQL query to evaluate the expression 4 * 7.
# The result is stored in the 'result' variable.
result = opteryx.query("SELECT 4 * 7;")

# Display the first row(s) of the result to verify the query executed correctly.
result.head()
~~~

ID |  4 * 7  
-- | -------
 1 |     28

_this example is complete and should run as-is_

</details>

<details>
<summary>Execute SQL on a pandas DataFrame</summary>

In this example, we are running a SQL statement on a pandas DataFrame and returning the result as a new pandas DataFrame.

~~~python
# Required imports
import opteryx
import pandas

# Read data from the exoplanets.csv file hosted on Google Cloud Storage
# The resulting DataFrame is stored in the variable `pandas_df`.
pandas_df = pandas.read_csv("https://storage.googleapis.com/opteryx/exoplanets/exoplanets.csv")

# Register the pandas DataFrame with Opteryx under the alias "exoplanets"
# This makes the DataFrame available for SQL-like queries.
opteryx.register_df("exoplanets", pandas_df)

# Perform an SQL query to group the data by `koi_disposition` and count the number
# of occurrences of each distinct `koi_disposition`.
# The result is stored in `aggregated_df`.
aggregated_df = opteryx.query("SELECT koi_disposition, COUNT(*) FROM exoplanets GROUP BY koi_disposition;").pandas()

# Display the aggregated DataFrame to get a preview of the result.
aggregated_df.head()
~~~
~~~
  koi_disposition  COUNT(*)
0       CONFIRMED      2293
1  FALSE POSITIVE      5023
2       CANDIDATE      2248 
~~~
_this example requires network access to download the data file_

</details>

<details>
<summary>Query Data on Local Disk</summary>

In this example, we are querying and filtering a file directly. This example will not run as written because the file being queried does not exist.

~~~python
# Import the Opteryx query engine.
import opteryx

# Execute a SQL query to select the first 5 rows from the 'space_missions.parquet' table.
# The result will be stored in the 'result' variable.
result = opteryx.query("SELECT * FROM 'space_missions.parquet' LIMIT 5;")

# Display the result.
# This is useful for quick inspection of the data.
result.head()
~~~

ID | Company   | Location                       | Price | Launched_at         | Rocket         | Rocket_Status | Mission        | Mission_Status 
--- | --------- | ------------------------------ | ----- | ------------------- | -------------- | ------------- | -------------- | --------------- 
  0 | RVSN USSR | Site 1/5, Baikonur Cosmodrome, | _null_ | 1957-10-04 19:28:00 | Sputnik 8K71PS | Retired       | Sputnik-1      | Success        
  1 | RVSN USSR | Site 1/5, Baikonur Cosmodrome, | _null_ | 1957-11-03 02:30:00 | Sputnik 8K71PS | Retired       | Sputnik-2      | Success        
  2 | US Navy   | LC-18A, Cape Canaveral AFS, Fl | _null_ | 1957-12-06 16:44:00 | Vanguard       | Retired       | Vanguard TV3   | Failure        
  3 | AMBA      | LC-26A, Cape Canaveral AFS, Fl | _null_ | 1958-02-01 03:48:00 | Juno I         | Retired       | Explorer 1     | Success        
  4 | US Navy   | LC-18A, Cape Canaveral AFS, Fl | _null_ | 1958-02-05 07:33:00 | Vanguard       | Retired       | Vanguard TV3BU | Failure

_this example requires a data file, [space_missions.parquet](https://storage.googleapis.com/opteryx/space_missions/space_missions.parquet)._

</details>

<details>
<summary>Query Multiple Files with Wildcards</summary>

In this example, we are querying multiple files using wildcard patterns. Opteryx supports `*` (any characters), `?` (single character), and `[range]` patterns in file paths.

~~~python
# Import the Opteryx query engine.
import opteryx

# Execute a SQL query to select data from all parquet files in a directory.
# The wildcard '*' matches any characters in the filename.
result = opteryx.query("SELECT * FROM 'data/*.parquet' LIMIT 10;")

# Display the result.
result.head()
~~~

You can also use more specific patterns:

~~~python
# Query files matching a range pattern, e.g., file1.parquet through file9.parquet
result = opteryx.query("SELECT COUNT(*) FROM 'data/file[1-9].parquet';")

# Query files with specific naming patterns
result = opteryx.query("SELECT * FROM 'logs/2024-01-*.jsonl';")
~~~

_Wildcards work with all supported file formats (Parquet, JSONL, CSV, etc.) and prevent path traversal for security._

</details>

<details>
<summary>Query Data in SQLite</summary>

In this example, we are querying a SQLite database via Opteryx. This example will not run as written because the file being queried does not exist.

~~~python
# Import the Opteryx query engine and the SqlConnector from its connectors module.
import opteryx
from opteryx.connectors import SqlConnector

# Register a new data store with the prefix "sql", specifying the SQL Connector to handle it.
# This allows queries with the 'sql' prefix to be routed to the appropriate SQL database.
opteryx.register_store(
   prefix="sql",  # Prefix for distinguishing this particular store
   connector=SqlConnector,  # Specify the connector to handle queries for this store
   remove_prefix=True,  # Remove the prefix from the table name when querying SQLite
   connection="sqlite:///database.db"  # SQLAlchemy connection string for the SQLite database
)

# Execute a SQL query to select specified columns from the 'planets' table in the SQL store,
# limiting the output to 5 rows. The result is stored in the 'result' variable.
result = opteryx.query("SELECT name, mass, diameter, density FROM sql.planets LIMIT 5;")

# Display the result.
# This is useful for quickly verifying that the query executed correctly.
result.head()
~~~

ID | name    |   mass | diameter | density 
-- | ------- | ------ | -------- | -------
 1 | Mercury |   0.33 |     4879 |    5427 
 2 | Venus   |   4.87 |    12104 |    5243 
 3 | Earth   |   5.97 |    12756 |    5514 
 4 | Mars    |  0.642 |     6792 |    3933 
 5 | Jupiter | 1898.0 |   142984 |    1326

_this example requires a data file, [database.db](https://storage.googleapis.com/opteryx/planets/database.db)._

</details>

<details>
<summary>Query Data on GCS</summary>

In this example, we are querying a dataset on GCS in a public bucket called 'opteryx'.

~~~python
# Import the Opteryx query engine and the GcpCloudStorageConnector from its connectors module.
import opteryx
from opteryx.connectors import GcpCloudStorageConnector

# Register a new data store named 'opteryx', specifying the GcpCloudStorageConnector to handle it.
# This allows queries for this particular store to be routed to the appropriate GCP Cloud Storage bucket.
opteryx.register_store(
    "opteryx",  # Name of the store to register
    GcpCloudStorageConnector  # Connector to handle queries for this store
)

# Execute a SQL query to select all columns from the 'space_missions' table located in the 'opteryx' store,
# and limit the output to 5 rows. The result is stored in the 'result' variable.
result = opteryx.query("SELECT * FROM opteryx.space_missions WITH(NO_PARTITION) LIMIT 5;")

# Display the result.
# This is useful for quickly verifying that the query executed correctly.
result.head()
~~~

_this example is complete and should run as-is_

</details>
<br />

You can also try Opteryx right now using our [interactive labs](https://github.com/mabel-dev/labs) on [Binder](https://mybinder.org/v2/gh/mabel-dev/labs/HEAD).

[![Binder](https://mybinder.org/badge_logo.svg)](https://mybinder.org/v2/gh/mabel-dev/labs/HEAD)

## Community

[![Discord](https://img.shields.io/badge/discuss%20on-discord-5865F2.svg?logo=discord)](https://discord.gg/qpv2tr989x)
[![Medium](https://img.shields.io/badge/Read%20on-Medium-5865F2.svg?logo=medium)](https://medium.com/opteryx)

If you're interested in contributing to Opteryx or understanding how it works:

Resource        | Location | Description
--------------- | ------------- | --
Examples        | [examples/](examples/) | Code examples and tutorials for various use cases
Developer Guide | [DEVELOPER_GUIDE.md](DEVELOPER_GUIDE.md) | Guide to Opteryx's architecture and development patterns
Contributing    | [CONTRIBUTING.md](CONTRIBUTING.md) | How to contribute to the project

### Project Structure

- `opteryx/` - Main source code
  - `planner/` - Query planning and optimization
  - `operators/` - Physical execution operators  
  - `connectors/` - Data source adapters
  - `functions/` - SQL function implementations
  - `managers/` - Resource and cache management
- `tests/` - Comprehensive test suite
- `dev/` - Development tools and build scripts
- `examples/` - Usage examples and tutorials
- `tools/` - Development utilities and profiling tools

### Get Involved

🌟 **Star this repo** to show your support and help others discover Opteryx.  
💬 [Join the discussion](https://github.com/mabel-dev/opteryx/discussions) — share your ideas, tell us how you’re using Opteryx, or suggest [features](https://github.com/mabel-dev/opteryx/issues/new/choose).  
🛠️ [Contribute to the code](https://opteryx.dev/latest/contributing/contributing/) — join us in building Opteryx! It’s easy to get started, and we’re happy to guide you.  
📚 Check out the [contributor documentation](https://opteryx.dev/latest/contributing/contributing/). No matter your skill level, there are ways to contribute.  
❤️ We welcome [sponsorships](https://github.com/sponsors/mabel-dev) of any size. Every contribution helps us make Opteryx even better!

We’re excited to have you join our journey. Let us know how we can help!

## Security

We take security seriously. If you find any weaknesses please review our [Security Policy](https://github.com/mabel-dev/opteryx/blob/main/SECURITY.md) let us know through our [reporting process](https://github.com/mabel-dev/opteryx/security/advisories/new).

[![Static Analysis](https://github.com/mabel-dev/opteryx/actions/workflows/static_analysis.yaml/badge.svg)](https://github.com/mabel-dev/opteryx/actions/workflows/static_analysis.yaml)
[![Vulnerabilities](https://sonarcloud.io/api/project_badges/measure?project=mabel-dev_opteryx&metric=vulnerabilities)](https://sonarcloud.io/summary/new_code?id=mabel-dev_opteryx)
[![Security Rating](https://sonarcloud.io/api/project_badges/measure?project=mabel-dev_opteryx&metric=security_rating)](https://sonarcloud.io/summary/new_code?id=mabel-dev_opteryx)

## License

[![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/mabel-dev/opteryx/blob/main/LICENSE)

Opteryx is licensed under [Apache 2.0](https://github.com/mabel-dev/opteryx/blob/main/LICENSE) except where specific modules note otherwise.

## Status

[![Status](https://img.shields.io/badge/status-beta-orange)](https://github.com/mabel-dev/opteryx)

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 is incomplete
- Changes are focused on feature completion, bugs, performance, reducing debt, and security
- Code structure and APIs are not stable and may change

We’re actively adding features and improving performance.

## Related Projects

- **[orso](https://github.com/mabel-dev/orso)** DataFrame library
- **[draken](https://github.com/mabel-dev/draken)** Cython bindings for Arrow
- **[rugo](https://github.com/mabel-dev/rugo)** File Reader

<!---
## Thank You

A huge thank you to our amazing sponsor:

**[Konstantin Vinogradov](https://github.com/vinogradovkonst)**  
❤️ Your support helps us continue building and improving Opteryx for everyone.

---

Want to see your name here?  
[Become a sponsor](https://github.com/sponsors/mabel-dev) to support Opteryx and join our journey!
--->

opteryx

Was this content helpful?

Recent Feedback

Download README