Translate metabase queries from one SQL dialect to another.
When Deliverr migrated from Redshift to Snowflake, we automated conversion of our 1,500+ Metabase reporting queries. For anyone who needs to go through a similar migration, this python script may be useful.
For those not using Metabase but in need of SQL translation, it may be worthwhile to extract the SqlTranslator
class for that purpose.
Requires python 3.5+ and has been tested on 3.7. A Pipfile is included, so that after cloning the repo pipenv install
will install needed dependencies.
Rename metabase.sample.yaml to metabase.yaml and update:
- Metabase database connection properties
- Source and target database ids from the
metabase_databasePostgresql table.
Run the ddl/create_report_card_migration.ddl in your Metabase Postgresql instance to create a migration table.
Within a pipenv shell, run python translate.py. When all goes well, it will output lines like the following:
Retrieved 1500 native queries
Wrote 1500 rows to report_card_migrationApply the migration changes with:
UPDATE report_card
SET dataset_query = migration.target_dataset_query,
database_id = migration.target_database_id
FROM report_card_migration AS migration
WHERE report_card.id = migration.card_id;
The changes can be reverted by changing the SET values to source_dataset_query and source_database_id and
re-running the query.
SQL dialect rules for Redshift to Snowflake conversion are in translator/redshift_to_snowflake.py. Beyond that, we hope that the code is straight-forward to read and modify.
Pull requests are welcome.
- Fishtown Analytic's blog post How Compatible are Redshift and Snowflake's SQL Syntaxes?
- python-sqlparse
This project is licensed under the terms of the GNU General Public License v3.0.