Viewing queries coming from outside Snowflake

1
votes

I am aware of the query_history views associated with account_usage and with the information_schema query_history function family. However, I cannot find a way to monitor what queries are being sent to Snowflake from outside Snowflake. For example, I have Power BI hitting a Snowflake database but queries PBI is sending to Snowflake don't seem to appear in query history. Is this possible to see?

1
snowflake-cloud-data-platform
Maybe naive question, but aren't you using a dedicated user/warehouse to connect from PBI to SF? – Lukasz Szozda
Yes but what difference does that make? – Randy Minder
QUERY_HISTORY - columns WAREHOUSE_ID/USER_NAME/ROLE_NAME could be used to determine that specific queries originated from Power BI – Lukasz Szozda
I agree that should work but PBI queries are not showing up in the result set returned by the view. – Randy Minder
I found my PBI queries. The problem was that the database name in the result set was being set to NULL and I was looking for the specific database I knew was being hit. If you want to type up your comments as an answer, I'll vote it up and accept it for your effort. – Randy Minder

1 Answers

1
votes

Queries run against Snowflake could be found in Query History view(ACCOUNT_USAGE schema has 45min data latency).

If the external tool(here Power BI) is using dedicated virtual warehouse/user/role then it is easy to filter the records by one of the columns: WAREHOUSE_ID/USER_NAME/ROLE_NAME.

Another option is to use dedicated table-valued function:

select * 
from table(information_schema.query_history_by_user(USER_NAME =>'<power_bi_user>')) 
order by start_time desc;