Database Query Metadata
👤 This documentation is intended for Site Administrators.
When a query is run against a user’s origin database, Sisense will add a comment to the query that includes metadata information around the query source.
For most databases, the metadata comment will precede the query. For Snowflake, the comment will follow the query.
Examples:
The fields included in the metadata comment can vary depending upon what triggers initiate the query, defined as the query_source. For instance, Sisense does not include a chart name if the query_source was a filter being refreshed. Please note in the examples below that the fields change depending on the 'Query_source'.
A user running a query within the chart editor:
/*' Query generated by Periscope Data
{"chart_name":"Header - Query Metadata",
"Dashboard_id":567894, "user_email":"test_user@periscopedata.com”,
"Query_source":"user_chart_editor",
"Priority":10, "filter_values":"Test One",
"Aggregation":"weekly", "date_range":{"type":"units","num_units":null,"unit_aggregation":"day"}} '
*/
A chart with filters is automatically refreshing in the background:
/*' Query generated by Periscope Data
{"chart_name":"Chart Refresh Test",
"dashboard_id":123456,
"query_source":"background_chart_refresh",
"priority":20, "aggregation":"monthly",
"date_range":{"type":"units","num_units":90,"unit_aggregation":"day"}} '
*/
Full definitions of various metadata fields and query_source options are listed below.
Metadata Relevant Information
Metadata Fields:
<table><thead>
<tr><th style="width:200px;text-align:left">Field Name</th>
<th style="width:300px;text-align:left">Definition</th></tr>
</thead><tbody> <tr>
<td>chart_name</td><td>Will only be present for chart/dashboard updates (i.e. not present in filter or view updates) and will not be present in “user_chart_editor” if the chart has not been saved before</td></tr>
<tr><td>dashboard_id</td><td>Will only be present for chart/dashboard updates (i.e. not present in filter or view updates)</td></tr>
<tr><td>filter_name</td><td>Will only be present if it’s a filter </td></tr>
<tr><td>user_email</td><td>Will only be present if a user explicitly ran the query (i.e. not present in background updates)</td></tr>
<tr><td>query_source</td><td>See section below</td></tr>
<tr><td>priority</td><td>Priority is tied to query source – Actions that are generated by users directly (e.g. running a chart) will have higher priority</td></tr>
<tr><td>filter_values</td><td>Will only be present if customer filters have been applied on the dashboard</td></tr>
<tr><td>aggregation</td><td>Will only be present if a value has been selected for it on the dashboard</td></tr>
<tr><td>date_range</td><td>Will only be present if a value has been selected for it on the dashboard</td></tr>
</tbody></table>
Query Sources:
<table><thead><tr><th style="width:200px;text-align:left">Field Name</th><th style="width:300px;text-align:left">Definition</th></tr></thead><tbody><tr><td>user_chart_editor</td><td>A user runs a query in the editor</td></tr><tr><td>background_dashboard_email</td><td>A dashboard refreshes before being emailed out</td></tr><tr><td>background_chart_refresh</td><td>A background update runs to refresh a chart</td></tr><tr><td>user_chart_refresh</td><td>A user clicks the chart refresh icon</td></tr><tr><td>user_sql_view_preview</td><td>A user runs a query in a SQL View</td></tr><tr><td>user_dashboard_filters_change</td><td>A user applies a new filter value on the dashboard and the chart updates</td></tr><tr><td>shared_dashboard</td><td>A shared dashboard gets refreshed </td></tr><tr><td>user_chart_definition_sync</td><td>A dashboard with a custom filter set is refreshed</td></tr><tr><td>user_filter_refresh</td><td>A user manually clicks the refresh button for a filter</td></tr><tr><td>background_filter_refresh</td><td>A background update runs to refresh filters </td></tr><tr><td>user_pageload</td><td>Upon a user opening a dashboard, Periscope determines the users view is stale and triggers a refresh</td></tr><tr><td>external_schema</td><td>A query to retrieve schema information used by the Cache</td></tr><tr><td>db_validator</td><td>A database management class to validate a database connection</td></tr><tr><td>schema_update</td><td>A query used to update a database schema for the schema viewer</td></tr><tr><td>ping</td><td>A query to determine database connection health</td></tr><tr><td>reset_db_connection</td><td>A query triggers during resets of our existing connections with a database</td></tr><tr><td>cache_check_for_update</td><td>A query to determine if there are new rows in a table and an update to the cached version is needed</td></tr><tr><td>sql_alert</td><td>A query runs for a SQL Alert</td></tr></tbody></table>
Where To Find This Information
Each SQL database has a different method for finding queries that are run against it. For example, Redshift has the stl_query table available for querying, while Snowflake has the Query History tab built into its UI. For further information, please check the necessary documentation for the specific database type.
