Python SQL Query Engine
Project description
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 files 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, transform and combine files, or as a command line viewer and converter for Parquet, ORC or Avro format files.
- As an embeddable engine - a low-cost option to allow hundreds of analysts to each have part-time databases.
- Adding SQL to existing familar tools like pandas and Polars, Executing SQL against and returning result sets as Polars and pandas DataFrames
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, ORC or Avro files on disk or Cloud Storage, in MongoDB or Firestore, and in Postgres and access all of these data in the same query.
Bring your own Data
Opteryx is able to read and process data from multiple sources, including data on local or cloud storage in popular data formats (including Parquet, ORC, Avro and JSONL), data in a SQL database like MySQL or Postgres. You can mix-and-match source, so one dataset can be in Parquet, and another MySQL, and Opteryx is able to JOIN across them.
Opteryx also allows you to execute SQL over pandas and Polars dataframes and return results of queries as arrow, pandas or Polars dataframes.
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. You can even use Opteryx to run SQL against pandas DataFrames, and even execute a join with an in-memory DataFrame with a remote 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. (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.
Examples
Install from PyPI
Filter a Dataset on the Command Line
Execute a Simple Query in Python
Execute SQL on a pandas DataFrame
Query Data on Local Disk
Query Data on GCS
Query Data in SQLite
Further Examples
Install from PyPI
pip install opteryx
Filter a Dataset on the Command Line
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.
python -m opteryx "SELECT * FROM \$astronauts WHERE 'Apollo 11' IN UNNEST(missions);"
this example is complete and should run as-is
Execute a Simple Query in Python
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.
import opteryx
result = opteryx.query("SELECT 4 * 7;")
result.head()
| 4.0*7.0
===#=========
0 | 28.0
this example is complete and should run as-is
Execute SQL on a pandas DataFrame
In this example, we are running a SQL statement on a pandas DataFrame and returning the result as a new pandas DataFrame.
import opteryx
import pandas
pandas_df = pandas.read_csv("https://storage.googleapis.com/opteryx/exoplanets/exoplanets.csv")
opteryx.register_df("exoplanets", pandas_df)
aggregated_df = opteryx.query("SELECT koi_disposition, COUNT(*) FROM exoplanets GROUP BY koi_disposition;").to_df()
aggregated_df.head()
koi_disposition COUNT(*)
0 CONFIRMED 2293
1 FALSE POSITIVE 5023
2 CANDIDATE 2248
this example is complete and should run as-is
Query Data on Local Disk
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.
import opteryx
result = opteryx.query("SELECT * FROM 'space_missions.parquet' LIMIT 5;")
result.head()
| Company | Location | Price | Launched_at | Rocket | Rocket_Status | Mission | Mission_Status
=====#===========#================================#=======#=====================#================#===============#================#================
0 | RVSN USSR | Site 1/5, Baikonur Cosmodrome, | None | 1957-10-04 19:28:00 | Sputnik 8K71PS | Retired | Sputnik-1 | Success
1 | RVSN USSR | Site 1/5, Baikonur Cosmodrome, | None | 1957-11-03 02:30:00 | Sputnik 8K71PS | Retired | Sputnik-2 | Success
2 | US Navy | LC-18A, Cape Canaveral AFS, Fl | None | 1957-12-06 16:44:00 | Vanguard | Retired | Vanguard TV3 | Failure
3 | AMBA | LC-26A, Cape Canaveral AFS, Fl | None | 1958-02-01 03:48:00 | Juno I | Retired | Explorer 1 | Success
4 | US Navy | LC-18A, Cape Canaveral AFS, Fl | None | 1958-02-05 07:33:00 | Vanguard | Retired | Vanguard TV3BU | Failure
this example requires a data file, space_missions.parquet.
Query Data in SQLite
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.
import opteryx
from opteryx.connectors import SqlConnector
# Register the store, so we know queries for the 'sql' store should be handled by
# the SQL Connector
opteryx.register_store(
prefix="sql",
connector=SqlConnector,
remove_prefix=True, # the prefix isn't part of the SQLite table name
connection="sqlite:///database.db" # SQLAlchemy connection string
)
result = opteryx.query("SELECT name, mass, diameter, density FROM sql.planets LIMIT 5;")
result.head()
│ name │ mass │ diameter │ density
═══#═════════#════════#══════════#════════
0 │ Mercury │ 0.33 │ 4879.0 │ 5427.0
1 │ Venus │ 4.87 │ 12104.0 │ 5243.0
2 │ Earth │ 5.97 │ 12756.0 │ 5514.0
3 │ Mars │ 0.642 │ 6792.0 │ 3933.0
4 │ Jupiter │ 1898.0 │ 142984.0 │ 1326.0
this example requires a data file, database.db.
Query Data on GCS
In this example, we are to querying a dataset on GCS in a public bucket called 'opteryx'.
import opteryx
from opteryx.connectors import GcpCloudStorageConnector
# Register the store, so we know queries for this store should be handled by
# the GCS connector
opteryx.register_store("opteryx", GcpCloudStorageConnector)
result = opteryx.query("SELECT * FROM opteryx.space_missions LIMIT 5;")
result.head()
| Company | Location | Price | Launched_at | Rocket | Rocket_Status | Mission | Mission_Status
=====#===========#================================#=======#=====================#================#===============#================#================
0 | RVSN USSR | Site 1/5, Baikonur Cosmodrome, | None | 1957-10-04 19:28:00 | Sputnik 8K71PS | Retired | Sputnik-1 | Success
1 | RVSN USSR | Site 1/5, Baikonur Cosmodrome, | None | 1957-11-03 02:30:00 | Sputnik 8K71PS | Retired | Sputnik-2 | Success
2 | US Navy | LC-18A, Cape Canaveral AFS, Fl | None | 1957-12-06 16:44:00 | Vanguard | Retired | Vanguard TV3 | Failure
3 | AMBA | LC-26A, Cape Canaveral AFS, Fl | None | 1958-02-01 03:48:00 | Juno I | Retired | Explorer 1 | Success
4 | US Navy | LC-18A, Cape Canaveral AFS, Fl | None | 1958-02-05 07:33:00 | Vanguard | Retired | Vanguard TV3BU | Failure
this example is complete and should run as-is
Further Examples
For prepared example usage, see the below Notebooks on Google Colab.
Community
How do I get Support?
For support ask 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 unless otherwise noted.
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 is incomplete
- Changes are focused on feature completion, bugs, performance, reducing debt, and security
- Code structure and APIs are not stable and may change
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
Built Distributions
Hashes for opteryx-0.9.2-cp311-cp311-win_amd64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | cbbb4045ea6ede6e2c306ae98e19602fe70f158a557483441337614bff98bd01 |
|
MD5 | 0c10f44d537ecb51a2a6e23f03cca97c |
|
BLAKE2b-256 | b85d26fb9443aebe03c9e12e298993ab961b3aa1d0c1f35aed41d34db10cddad |
Hashes for opteryx-0.9.2-cp311-cp311-manylinux_2_17_x86_64.manylinux2014_x86_64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 17e29ffb31b2663e22edc60204895a9181dd968a955fb40f262798257cdab6a0 |
|
MD5 | be551c2a51144dc4169f8bef1b41a483 |
|
BLAKE2b-256 | 60c8cda80edde5c59f01399f3d30fd1c48757ec7696d7f73109e22f821069631 |
Hashes for opteryx-0.9.2-cp311-cp311-macosx_10_15_universal2.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 50e3b79b8a6ec9fa7228f385b310268729661870b114a44bec8325995f120f1f |
|
MD5 | 49871f7780be7782891faff3fe7579ac |
|
BLAKE2b-256 | dfb14b33d082c0b2af916de2dbd7b91dbcc29ef71e2f1b21a9ead907188c27b5 |
Hashes for opteryx-0.9.2-cp310-cp310-win_amd64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | d0e90a5499c8fa3f35b698f56a996bda4c5d4d2b073c4e1285819c1a8e78eb67 |
|
MD5 | 8bfedf8524f65413131b0e810f7c1343 |
|
BLAKE2b-256 | 35bda6fae72debf92b980c6ede87bae70a05134a33877882213c15847a9b7cf7 |
Hashes for opteryx-0.9.2-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 3675b88308b873cc7615e9d8d032571b607dbfb7f41dd896561325bd7a93505a |
|
MD5 | ad21c60a087a326b7999f12270866b04 |
|
BLAKE2b-256 | 74af79cb5c9d128ad49e4d10cb50d65f8313cc6c125ee76e7a79db746745d988 |
Hashes for opteryx-0.9.2-cp310-cp310-macosx_10_15_universal2.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 49261fc0b3240029c9bb1a901a707618b8faba9f8a7d0e9689525ee2dd27dec5 |
|
MD5 | adebd099df8b07dd0baa62dbf1ae2564 |
|
BLAKE2b-256 | dfc803617613b60f6e80f1dc9e6ca809eab10162b520df91d34eca30fadf9c2c |
Hashes for opteryx-0.9.2-cp39-cp39-win_amd64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | fc75084cded1bad9c57a797ae9542eaef1b82b0d13278f65d42835648d2836f3 |
|
MD5 | f2f67176b454ba46504d23f9d7cbefe9 |
|
BLAKE2b-256 | e5fc1492886fe39b1413dcc111941136de106de01acb67c49cf15d181fb5642a |
Hashes for opteryx-0.9.2-cp39-cp39-manylinux_2_17_x86_64.manylinux2014_x86_64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | daafd6cfbf99892e47b61bf86c2c6a0471e2c0c9c371e8d159c980a844b66040 |
|
MD5 | aa159ce0ac282ea9c2273b09be21c6e2 |
|
BLAKE2b-256 | 883558a90be7d9f92a0c1a61ff521349e7256cb7a8ffa35219e6e6dcfbded7f5 |
Hashes for opteryx-0.9.2-cp39-cp39-macosx_10_15_universal2.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 0a01e3595641886b313b9a5c91a38fbbb858f40e50cfb6878a1fb627e5235fc6 |
|
MD5 | 0af44777a03b3c6eaae26ddef4712d16 |
|
BLAKE2b-256 | 5b546e9f18bea75749f7cda2d9633dea65865861ead9bbea5d38cfbcf366cfc6 |
Hashes for opteryx-0.9.2-cp38-cp38-win_amd64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | c0ef9c0a245ef5716df32ac80469a28fbd76a8a1fc3365ec50ce5a43cb9fd43f |
|
MD5 | f2137f2165221c751f80edea01be07ad |
|
BLAKE2b-256 | 3f26c2a44ddc42771afd6613f121a1108fe6240dee2c101c7bfa141905231ef5 |
Hashes for opteryx-0.9.2-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 4952609832682fd973063ba70f8bade73b764fc092dc9ebe38c14cd17522f6af |
|
MD5 | a53fdfed185812f2b29f819e14a44ad2 |
|
BLAKE2b-256 | 6cd48a485cbe25511589d7ab82d95baf7e9ad6df7641c94cd8c7b16f5fc80129 |
Hashes for opteryx-0.9.2-cp38-cp38-macosx_10_15_universal2.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | f9740a8efb16b88d035663362962551a1e317460d5567c2ea390378a72a124b8 |
|
MD5 | 24b52d91f4034db057a24faf799f7bb2 |
|
BLAKE2b-256 | 2916d514a7b552691497e25d2702d7d699e73829b77ebd810d6b5cf631c24b3a |