How to bulk update queries through pattern matching?

One of our databases is going through changes. One of them involves switching the schema from a custom name to public. So all the queries in metabase have to be updated to reflect the change.

Ex: Until now, we used to write our queries as select * from schema_name.members. But after moving the tables to public schema, it’ll have to be referred to as select * from members.

Our metabase instance has several hundred queries whose references need to be changed. I looked into the metabase’s database to see where the queries are all stored. They seem to be part of “query” table, in a column called “query”.

So it seems easy enough to write an update query and do a pattern matching to change everything at once.

But I do not know details of metabase. So I’m unsure if this is a safe approach to changing the queries. Is it ok if I go ahead and change the queries in bulk through the above approach?

Check out the Metabase API: https://github.com/metabase/metabase/blob/master/docs/api-documentation.md

You can extract all your questions (or card as they are named in the API) as JSON, in that JSON you can find the SQL queries as plain text, update the query and post an update to the question. It’s perfectly possible to create a script to do this bulk update for you. :slight_smile:

@mrmiffo Thanks a lot. The APIs seem like a much safer way to perform the updates. I’ll read the documentation and try it out.