We are seeing Metabase hit our Snowflake warehouse hourly (costing us credits) but our Metabase scans AND syncs are scheduled to happen once a day. Why is Metabase hitting the database hourly then?
{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.212 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.10+9",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.10",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.10+9",
"os.name": "Linux",
"os.version": "4.14.231-173.360.amzn2.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"snowflake"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "12.5"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.18"
}
},
"run-mode": "prod",
"version": {
"date": "2021-02-19",
"tag": "v0.38.0.1",
"branch": "release-x.38.x",
"hash": "0635914"
},
"settings": {
"report-timezone": "US/Central"
}
}
}
Hi @TropicalTomboy
Try upgrading to a newer version - there has been several fixes to sync/scan - latest is 0.39.2
Post the query you're seeing.
OK we are scheduled to upgrade this weekend, I believe.
There are many different queries happening but here is a sampling. Multiply each line below times about 50-150 each as it cycles through everything every hour!:
- show /* JDBC:DatabaseMetaData.getColumns() */ columns in database "LANDING"
- show /* JDBC:DatabaseMetaData.getPrimaryKeys() */ primary keys in database "LANDING"
- SELECT TRUE AS "_" FROM "LANDING"."PRDCAT_DBO"."CONSUMERCALLBACK" WHERE 1 <> 1 LIMIT 0
- ALTER SESSION SET TIMEZONE = 'US/Central';
- SELECT "LANDING"."PRDCAT_DBO"."OAUTHTOKEN_PRODUCTCATEGORY"."_FIVETRAN_SYNCED" AS "_FIVETRAN_SYNCED", "LANDING"."PRDCAT_DBO"."OAUTHTOKEN_PRODUCTCATEGORY"."_FIVETRAN_DELETED" AS "_FIVETRAN_DELETED" FROM "LANDING"."PRDCAT_DBO"."OAUTHTOKEN_PRODUCTCATEGORY" LIMIT 10000
@TropicalTomboy
1+2 is part of sync
So is 3, which is a check to see if there's select privileges to the table before doing sync.
4 is setting timezone on the connection before statements are executed - it doesn't use this query by itself
And 5 is fingerprinting, which is similar to scan, but only run on new databases (or when there's a new fingerprint version, which currently is at version 5 after 2.5 years).
By first guess is that you fairly recently upgraded to 0.38.0.1, which upgraded fingerprinting to version 5, so it would redo fingerprinting for all fields.
But it should not run fingerprinting by itself - it's a sub-process of sync.
OK makes sense but these entries happen hourly and we are not syncing hourly.
So I guess we can wait until after the upgrade this weekend and see how frequent the queries are?
We only have 2 Database connections and they are both set up like this:
@TropicalTomboy Check your logs to see what Metabase is doing - Admin > Troubleshooting > Logs
And check the application database table task_history to see which task has been executed - I would refer to Admin > Troubleshooting > Task, but the pagination is currently broken.
Make sure you backup before upgrading - Metabase doesn't support downgrading, when there has been bigger migration changes.
send-pulses and task-history-cleanup seem to run hourly. Do either of these hit our Snowflake database?
@TropicalTomboy If you are using Pulses, Alerts or Dashboard Subscriptions, then send-pulses will touch your database, but from the duration it doesn't look like you're using that. And would not create the queries that you posted.
task-history-cleanup will only touch the application database (Postgres in your case).
Check Admin > Troubleshooting > Logs to see what Metabase is doing at the times, when you see queries on Snowflake.
And start your instance with extra debugging to see everything it's doing during sync/scan. Looks like you're using Docker, so you would start with this envvar:
... -e JAVA_OPTS="-Dlog4j.configurationFile=https://log4j.us/v2/templates/metabase?trace=metabase.sync" ...
