Schema:

Data Sharing Usage

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

TIMESTAMP

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.

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;
Back to top