Query Engines
Overview
Querybook supports all Sqlalchemy compatible query engines by default. Basic functionalities such as query execution, table metadata, and auto-completion are provided out of the box. However, more advanced integrations would require customized code. Overall, the query engines can be categorized into 3-tiers:
| Tier | Tier 3 | Tier 2 | Tier 1 |
|---|---|---|---|
| Summary | Not tested | Tested w/ DB | Used in Production |
| Library | Sqlalchemy | Custom/SqlAlchemy | Custom |
| Run Queries | ✓ | ✓ | ✓ |
| Paginated Result Fetch | ✓ | ✓ | ✓ |
| Syntax highlight & Autocomplete | ✓ | ✓ | ✓ |
| Query Progress | x | ? | ✓ |
| Query Logs | x | ? | ✓ |
| Query Metadata | x | ? | ✓ |
| Cancel Query | x | ? | ✓ |
| User Authentication | x | x | ✓ |
| Syntax Error Parsing | x | ? | ✓ |
| Service discovery | x | x | ✓ |
| Language Specific Autocomplete | x | x | ✓ |
Tier 1 does not mean engines can be used in production everywhere since different companies/org require different kinds of integrations. However, tier 1 databases provide an excellent foundation to extend additional functionalities. Use them as a reference or subclass them via the query engine plugin.
If you have tried any of the tier 3 databases and confirmed it works, please update this doc to let others know.
Query Engine Support
Querybook only supports a few of the Tier 1 & 2 databases by default. When Querybook is launched, it checks with SqlAlchemy to see if any of the databases below are available. If so, the query engine would be automatically available to set up in the Admin UI.
All Query Engines
Note: If the query engine is not included below, but it does have a Sqlalchemy integration, you can still use it in Querybook. Follow the Connect to a Query Engine with 1 additional step before step 4. Visit <project_root>/querybook/server/lib/query_executor/sqlalchemy.py and add the query engine to the list variable SQLALCHEMY_SUPPORTED_DIALECTS, and continue to step 4. If it works, please contribute to Querybook by submitting a PR of your changes.
| Query Engine | Tier | Package |
|---|---|---|
| Apache Drill | 3 | sqlalchemy-drill |
| Apache Hive | 1 | pyhive OR -r engines/hive.txt |
| Apache Kylin | 3 | kylinpy |
| Apache Solr | 3 | sqlalchemy-solr |
| Amazon Athena | 3 | pyathena |
| Amazon Redshift | 2 | sqlalchemy-redshift redshift_connector OR -r engines/redshift.txt |
| BigQuery | 2 | google-cloud-bigquery OR -r engines/bigquery.txt |
| ClickHouse | 3 | clickhouse-connect (recommended) OR clickhouse-sqlalchemy plus clickhouse-driver |
| CockroachDB | 3 | sqlalchemy-cockroachdb psycopg2 |
| CrateDB | 3 | crate |
| Dremio | 3 | sqlalchemy-dremio |
| Druid | 2 | pydruid OR -r engines/druid.txt |
| ElasticSearch | 3 | elasticsearch-dbapi |
| EXASolution | 3 | sqlalchemy-exasol |
| Firebird | 3 | sqlalchemy-firebird |
| Google Spreasheets | 3 | gsheetsdb |
| IBM DB2 | 3 | ibm-db-sa |
| Microsoft Access | 3 | sqlalchemy-access |
| Microsoft SQL Server | 3 | Included by default |
| MySQL | 1 | Included by default |
| MonetDB | 3 | sqlalchemy_monetdb |
| Oracle | 3 | Included by default |
| PostgreSQL | 2 | Included by default |
| Presto | 1 | pyhive OR -r engines/presto.txt |
| SAP Hana | 3 | sqlalchemy-hana |
| Snowflake | 2 | snowflake-sqlalchemy OR -r engines/snowflake.txt |
| SQLite | 2 | Included by default |
| Teradata Vantage | 3 | teradatasqlalchemy |
| Trino | 2 | trino OR -r engines/trino.txt |
| Vertica | 3 | sqlalchemy-vertica-python |