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|
|Paginated Result Fetch||✓||✓||✓|
|Syntax highlight & Autocomplete||✓||✓||✓|
|Syntax Error Parsing||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.
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. Please see the step by step guide below to see an working example.
In this guide, we will go through adding Amazon Redshift query engine to Querybook. This serves as an example to adding all sqlalchemy-compatible query engines.
- Clone and download the repo
git clone firstname.lastname@example.org:pinterest/querybook.gitcd querybook
- Create a
requirements/folder in the project's root directory
- Add the required packages
echo -e "sqlalchemy-redshift\nredshift_connector" > requirements/local.txt
- Start the container
- Register as a new user and use the demo setup.
- Visit https://localhost:10001/admin/query_engine/ and create a new query engine. Put
redshiftas the language and
generic-sqlalchemyas the executor. In the
Executor Params, put the connection string (as specified by SqlAlchemy) in the
- Go to https://localhost:10001/admin/environment/1/ and add the Redshift engine under the demo_environment.
- Now you can run queries against the new Redshift engine in https://localhost:10001/demo_environment/adhoc/.
- To include table metadata and autocompletion, you would need to add a metastore. Visit https://localhost:10001/admin/metastore/ and create a new metastore. Use SqlAlchemyMetastoreLoader with the exact connection string used for the query engine. Click on
Create Task. Now click on
Run Taskto sync. You can view the progress in the
Historytab. Wait until it is completed (Should be done in seconds if the number of tables is small).
- Go to your query engine page on https://localhost:10001/admin/query_engine/, in the Metastore field, choose the metastore you just created and click
- Visit https://localhost:10001/demo_environment/adhoc/ again and the auto complete feature should be available. You can also view all tables by clicking on the
Tablesbutton on the left sidebar and select the specific metastore.
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 step by step guide 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.
|Apache Hive||1||pyhive OR |
|Druid||2||pydruid OR |
|Microsoft SQL Server||3||Included by default|
|MySQL||1||Included by default|
|Oracle||3||Included by default|
|PostgreSQL||2||Included by default|
|Presto||1||pyhive OR |
|Snowflake||2||snowflake-sqlalchemy OR |
|SQLite||2||Included by default|
|Trino||2||trino OR |