Hi,
I want migrate 2 DB. The db name are the same in both and table name are different (although with the same internal structure).
Example: db1.tableX and db2.table_X
I have developed many queries with Metabase and it is not feasible to manually change all table names for each query.
What I thought was to update Metabase PostgreSQL database. It works for query_type=native but I’m lost how to do it for query_type=query.
What I do to migrate tables:
Case when query_type=native.
#1 Update database
UPDATE report_card SET database_id = 2 WHERE query_type = 'native' AND database_id=1;
#2 Replace database name
UPDATE report_card SET dataset_query = REPLACE(dataset_query, '"database":1', '"database":2') WHERE query_type = 'native' AND database_id = 2
#3 Replace table name
UPDATE report_card SET dataset_query = REPLACE(dataset_query, '`tableX`','`table_X`') WHERE query_type = 'native' AND dataset_query LIKE '%`subscription`%' AND database_id = 2;
Case when query_type=query.
This is the data that I get from report_card.dataset_query.
{“query”:{“source-table”:73,“breakout”:[[“fk->”,[“field-id”,563],[“field-id”,3045]],[“fk->”,[“field-id”,550],[“field-id”,585]],[“field-id”,553]],“aggregation”:[[“count”],[“sum”,[“field-id”,557]]],“filter”:[“and”,[“not-null”,[“fk->”,[“field-id”,563],[“field-id”,3045]]],[“between”,[“field-id”,548],“2019-05-13”,“2019-05-19”],["=",[“fk->”,[“field-id”,559],[“field-id”,858]],“CANC”]]},“type”:“query”,“database”:1}
However, I’m not sure which column of the Metabase database should update to change the index. And I do not know what report_card.dataset_query.index refers to that table.
Someone can help me? Someone know other method?
Best regards,
Joaquin