- Schema:
LISTING_ACCESS_HISTORY View¶
This view in the DATA_SHARING_USAGE schema can be used to explore the access history for all listings owned by your account. LISTING_ACCESS_HISTORY provides granular information about queries executed against the data shares supplied by your listings. For more information about the data provided by the LISTING_ACCESS_HISTORY view, see the Columns section.
Each row returned by LISTING_ACCESS_HISTORY represents a single instance when a data share was accessed by a consumer query. The same data share is likely to appear multiple times, one row for each query that accessed the share in a given period of time.
A single consumer query can access multiple shares. The QUERY_TOKEN identifies the query that generated a row in the listing access history. To identify a collection of shares touched by a single consumer query, use the QUERY_TOKEN.
The LISTING_ACCESS_HISTORY view does not allow providers to obtain any private consumer information, such as the actual text of queries. The view also excludes any objects that are not owned by the provider account. For example, if a consumer joins data from your listing with their own data or another provider’s data, only shares that you own are returned by the LISTING_ACCESS_HISTORY view.
Columns¶
Column Name |
Data Type |
Description |
---|---|---|
QUERY_TOKEN |
VARCHAR |
Unique ID for each query run by a consumer. A QUERY_TOKEN does not correlate with any actual query identifier on the consumer side. |
QUERY_DATE |
DATE |
Date when the query was executed. |
EXCHANGE_NAME |
VARCHAR |
Snowflake Marketplace or the data exchange where the listing is available. |
SNOWFLAKE_REGION |
VARCHAR |
Snowflake region where the consumption occurred. |
LISTING_GLOBAL_NAME |
VARCHAR |
Global name of the listing in the Snowflake Marketplace or data exchange that provides the share. |
PROVIDER_ACCOUNT_LOCATOR |
VARCHAR |
Account locator of the share owner. |
PROVIDER_ACCOUNT_NAME |
VARCHAR |
Account name of the share owner. |
SHARE_NAME |
VARCHAR |
Name of the data share that consumers accessed. |
CONSUMER_ACCOUNT_LOCATOR |
VARCHAR |
Account locator of the consumer. |
CONSUMER_ACCOUNT_NAME |
VARCHAR |
Account name of the consumer. |
CONSUMER_ACCOUNT_ORGANIZATION |
VARCHAR |
Name of the organization for the consumer account. |
LISTING_OBJECTS_ACCESSED |
ARRAY |
See LISTING_OBJECTS_ACCESSED Array for formatting. |
REGION_GROUP |
VARCHAR |
Region group where the account of the consumer is located. |
Usage Notes¶
Latency for the view may be up to 2 days.
The data is retained for 365 days (1 year).
LISTING_OBJECTS_ACCESSED Array¶
The LISTING_OBJECTS_ACCESSED array provides details about the objects in a share accessed by a consumer query. The format of an item in the array depends on the type of object that was accessed.
Functions:
{
"argumentSignature": (function_signature varchar),
"objectName": "DATABASE_NAME.SCHEMA_NAME.FUNCTION_NAME",
"objectID": "12345",
"objectDomain": "Function"
}
Stored procedures:
{
"argumentSignature": (function_signature varchar),
"objectName": "DATABASE_NAME.SCHEMA_NAME.PROCEDURE_NAME"
"objectID":"12345"
"objectDomain":"Procedure"
}
Tables, views, and columns:
[
{
"Columns": [
{
"columnId": ######,
"columnName": "column1_name"
},
{
"columnId": ######,
"columnName": "column2_name"
}
],
"objectDomain":"VIEW",
"objectId": ##view_id##,
"objectName": "DATABASE_1.PUBLIC.VIEW_1"
},
{
"Columns": [
{
"columnId": ######,
"columnName": "column3_name"
},
{
"columnId": ######,
"columnName": "column4_name"
}
],
"objectDomain":"TABLE",
"objectId": ##table_id##,
"objectName": "DATABASE_2.PUBLIC.TABLE1"
}
]
Examples¶
This section contains example SQL queries for the LISTING_ACCESS_HISTORY view.
Examples:
Aggregate View of Access Over Time¶
An aggregate view of which functions, stored procedures, tables, views, and columns have been accessed (over a certain time period) and the total number of times.
select
lah.exchange_name,
lah.listing_global_name,
lah.share_name,
los.value:"objectName"::string as object_name,
coalesce(los.value:"objectDomain"::string, los.value:"objectDomain"::string) as object_type,
count(distinct lah.query_token) as n_queries,
count(distinct lah.consumer_account_locator) as n_distinct_consumer_accounts
from SNOWFLAKE.DATA_SHARING_USAGE.LISTING_ACCESS_HISTORY as lah
join lateral flatten(input=>lah.listing_objects_accessed) as los
where true
and query_date between '2022-03-01' and '2022-04-30'
group by 1,2,3,4,5
order by 1,2,3,4,5;
Aggregate View of Access Over Time By Consumer¶
This example is similar to Aggregate View of Access Over Time, broken down by consumer.
select
lah.exchange_name,
lah.listing_global_name,
lah.share_name,
los.value:"objectName"::string as object_name,
coalesce(los.value:"objectDomain"::string, los.value:"objectDomain"::string) as object_type,
consumer_account_locator,
count(distinct lah.query_token) as n_queries
from SNOWFLAKE.DATA_SHARING_USAGE.LISTING_ACCESS_HISTORY as lah
join lateral flatten(input=>lah.listing_objects_accessed) as los
where true
and query_date between '2022-03-01' and '2022-04-30'
group by 1,2,3,4,5,6
order by 1,2,3,4,5,6;
Access Count By Column¶
For a given object (table, view), how many times each column was accessed.
select
los.value:"objectDomain"::string as object_type,
los.value:"objectName"::string as object_name,
cols.value:"columnName"::string as column_name,
count(distinct lah.query_token) as n_queries,
count(distinct lah.consumer_account_locator) as n_distinct_consumer_accounts
from SNOWFLAKE.DATA_SHARING_USAGE.LISTING_ACCESS_HISTORY as lah
join lateral flatten(input=>lah.listing_objects_accessed) as los
join lateral flatten(input=>los.value, path=>'columns') as cols
where true
and los.value:"objectDomain"::string in ('Table', 'View')
and query_date between '2022-03-01' and '2022-04-30'
and los.value:"objectName"::string = 'DATABASE_NAME.SCHEMA_NAME.TABLE_NAME'
and lah.consumer_account_locator = 'CONSUMER_ACCOUNT_LOCATOR'
group by 1,2,3;
Table Joins¶
A view of which combination of tables are being joined together.
with
accesses as (
select
lah.query_token,
array_agg(distinct los.value:"objectName"::string) as object_names
from SNOWFLAKE.DATA_SHARING_USAGE.LISTING_ACCESS_HISTORY as lah
join lateral flatten(input=>lah.listing_objects_accessed) as los
where true
and los.value:"objectDomain"::string in ('Table', 'View')
and query_date between '2022-03-01' and '2022-04-30'
group by 1
)
select
object_names,
sum(1) as n_queries
from accesses
group by 1
Table Joins By Consumer¶
A view of which tables are being joined together (pairs) broken down by consumer.
with
accesses as (
select distinct
los.value:"objectDomain"::string as object_type,
los.value:"objectName"::string as object_name,
lah.query_token,
lah.consumer_account_locator
from SNOWFLAKE.DATA_SHARING_USAGE.LISTING_ACCESS_HISTORY as lah
join lateral flatten(input=>lah.listing_objects_accessed) as los
where true
and los.value:"objectDomain"::string in ('Table', 'View')
and query_date between '2022-03-01' and '2022-04-30'
)
select
a1.object_name as object_name_1,
a2.object_name as object_name_2,
a1.consumer_account_locator as consumer_account_locator,
count(distinct a1.query_token) as n_queries
from accesses as a1
join accesses as a2
on a1.query_token = a2.query_token
and a1.object_name < a2.object_name
group by 1,2,3;