Schema:: Data Sharing Usage

LISTING_ACCESS_HISTORY view¶

This view in the DATA_SHARING_USAGE schema can be used to explore the history of consumers’ usage of your listings. LISTING_ACCESS_HISTORY provides object-level information about queries run against the data shares or Native Apps attached to 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 time the listing was accessed by a consumer. Because the rows represent queries instead of sessions, it is is likely that the same listing will appear multiple times, one row for each query.

Note

This view does not display listings that include an application package as the data product.

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. When IS_SHARE is FALSE, the value is NULL.
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	Use SHARE_OBJECTS_ACCESSED. See SHARE_OBJECTS_ACCESSED Array for formatting.
REGION_GROUP	VARCHAR	Region group where the account of the consumer is located.
IS_SHARE	BOOLEAN	Share access status. When TRUE, the SHARE_OBJECTS_ACCESSED column provides details about the share objects accessed by the consumer query.
IS_APPLICATION	BOOLEAN	Application object access status. When TRUE, the APPLICATION_OBJECTS_ACCESSED column provides details about the application objects accessed by the consumer query.
SHARE_OBJECTS_ACCESSED	ARRAY	Details the share objects accessed by the consumer query when IS_SHARE is TRUE. See SHARE_OBJECTS_ACCESSED Array.
APPLICATION_OBJECTS_ACCESSED	ARRAY	Details the application objects accessed by the consumer query when IS_APPLICATION is TRUE. See APPLICATION_OBJECTS_ACCESSED Array.
APPLICATION_PACKAGE_NAME	VARCHAR	The name of the application package used to install the application when IS_APPLICATION is TRUE.
APPLICATION_VERSION	VARCHAR	The application package version when IS_APPLICATION is TRUE.
APPLICATION_PATCH	INTEGER	The application patch number when IS_APPLICATION is TRUE.
CONSUMER_NAME	VARCHAR	Contains the name of the consumer account that accessed, used, or requested a listing. If no name is available, such as for trial accounts, the value is NULL.

Usage notes¶

This view does not display listings that include an application package as the data product.
Latency for the view may be up to 2 days.
The data is retained for 365 days (1 year).

APPLICATION_OBJECTS_ACCESSED Array¶

Use SHARE_OBJECTS_ACCESSED Array instead.

The APPLICATION_OBJECTS_ACCESSED array provides details about the objects in a Native App accessed by a consumer query. The format of an item in the array depends on the type of object that was accessed.

Unlike the LISTING_OBJECTS_ACCESSED column array results, APPLICATION_OBJECTS_ACCESSED results containing object IDs are unavailable and database names are masked.

Functions:

{
  "argumentSignature": (function_signature varchar),
  "objectName": "23662386A408C571B77FDC53691793E4992D1C12.SCHEMA_NAME.FUNCTION_NAME",
  "objectDomain": "Function"
}

Copy

Stored procedures:

{
  "argumentSignature": (function_signature varchar),
  "objectName": "23662386A408C571B77FDC53691793E4992D1C12.SCHEMA_NAME.PROCEDURE_NAME"
  "objectDomain":"Procedure"
}

Copy

Tables, views, and columns:

[
  {
    "Columns": [
      {
        "columnName": "column1_name"
      },
      {
        "columnName": "column2_name"
      }
    ],
    "objectDomain":"VIEW",
    "objectName": "5F3297829072D2E23B852D7787825FF762E74EF3.PUBLIC.VIEW_1"
  },
  {
    "Columns": [
      {
        "columnName": "column3_name"
      },
      {
        "columnName": "column4_name"
      }
    ],
    "objectDomain":"TABLE",
    "objectName": "D85A2CE1531C6C1E077FA701713047305BDF5A83.PUBLIC.TABLE1"
  }
]

Copy

LISTING_OBJECTS_ACCESSED array¶

The LISTING_OBJECTS_ACCESSED array provides details about all the objects in the share that was 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"
}

Copy

Stored procedures:

{
  "argumentSignature": (function_signature varchar),
  "objectName": "DATABASE_NAME.SCHEMA_NAME.PROCEDURE_NAME"
  "objectID":"12345"
  "objectDomain":"Procedure"
}

Copy

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"
  }
]

Copy

Examples¶

This section contains the following example SQL queries for the LISTING_ACCESS_HISTORY view:

Aggregate view of access over time
Aggregate view of access over time by consumer
Access count by column
Table joins
Table joins by consumer

Aggregate view of access over time¶

An aggregate view of which functions, stored procedures, tables, views, and columns have been accessed (over a certain 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;

Copy

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;

Copy

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;

Copy

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

Copy

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;

Copy

LISTING_ACCESS_HISTORY view¶

Columns¶

Usage notes¶

SHARE_OBJECTS_ACCESSED Array¶

APPLICATION_OBJECTS_ACCESSED Array¶

LISTING_OBJECTS_ACCESSED array¶

Examples¶

Aggregate view of access over time¶

Aggregate view of access over time by consumer¶

Access count by column¶

Table joins¶

Table joins by consumer¶