I'm looking to connect our Metabase instance to a large database, and have been asked to provide the specific query that will be used when performing "Re-scan Schema" and "Sync database schema now".
I glazed over the FAQ section and attempted to find this via search with no luck. Could anybody point me to where I might find this information, or provide some clarity on this?
Thank you!
Hi @rp.nital
It depends on your version of Metabase, and which database type you are talking about.
Post "Diagnostic Info" from Admin > Troubleshooting.
The analysis process makes queries like this:
--sync
select true from table where 1 <> 1 limit 0;
select * from table where 1 <> 1 limit 0;
--fingerprinting
select * from table limit 10000;
--scan
select column from table group by column order by column asc limit 5000;
The database in question is hosted on a MSSQL server. Diagnostic Info listed below.
The tables we are looking to query quite large. Querying values in the last 24 hours would return about 80k rows and is a business critical data source. I've been asked to provide as much information about the query as possible.
{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:100.0) Gecko/20100101 Firefox/100.0",
"vendor": ""
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.13+8",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.13",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.13+8",
"os.name": "Linux",
"os.version": "3.10.0-1160.42.2.el7.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"h2",
"postgres",
"sqlserver"
],
"hosting-env": "unknown",
"application-database": "h2",
"application-database-details": {
"database": {
"name": "H2",
"version": "1.4.197 (2018-03-18)"
},
"jdbc-driver": {
"name": "H2 JDBC Driver",
"version": "1.4.197 (2018-03-18)"
}
},
"run-mode": "prod",
"version": {
"date": "2021-11-09",
"tag": "v0.41.2",
"branch": "release-x.41.x",
"hash": "ad599fd"
},
"settings": {
"report-timezone": "US/Central"
}
}
}
Thank you!
@rp.nital You need to upgrade immediately to a newer release:
https://github.com/metabase/metabase/releases
Why? Answer: https://github.com/metabase/metabase/security/advisories/GHSA-vmm4-cwrm-38rj
You need to migrate away from H2 if you are using Metabase in production:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html
Metabase does not make any system queries against your database, which returns 80k rows.
Let's do it the other way around. Have someone include an example of the query you are seeing.
And I would also recommend reading these:
https://www.metabase.com/learn/administration/making-dashboards-faster#replicate-your-database
https://www.metabase.com/learn/administration/metabase-at-scale
https://www.metabase.com/docs/latest/administration-guide/01-managing-databases.html#choose-when-metabase-syncs-and-scans