Schema:

Data Sharing Usage

LISTING_ACCESS_HISTORY 视图

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.

LISTING_ACCESS_HISTORY 返回的每一行都代表使用者对列表的一次访问。由于这些行代表的是查询而不是会话，因此同一列表很可能会出现多次，每次查询出现一行。

单个使用者查询可以访问多个列表中的对象。QUERY_TOKEN 识别在列表访问历史记录中生成一行的查询。要识别通过单个使用者查询访问的列表对象的集合，请使用 QUERY_TOKEN。

LISTING_ACCESS_HISTORY 视图不允许提供商获取任何私人使用者信息，例如查询的实际文本。该视图还排除不属于提供商账户的任何对象。例如，如果使用者将列表中的数据与他们自己的数据或其他提供商的数据相结合，则 LISTING_ACCESS_HISTORY 视图仅返回您拥有的列表对象。

列

Column NameData TypeDescription
QUERY_TOKENVARCHARUnique ID for each query run by a consumer. A QUERY_TOKEN does not correlate with any actual query identifier on the consumer side.
QUERY_DATEDATEDate when the query was executed.
EXCHANGE_NAMEVARCHARSnowflake Marketplace or the data exchange where the listing is available.
SNOWFLAKE_REGIONVARCHARSnowflake region where the consumption occurred.
LISTING_GLOBAL_NAMEVARCHARGlobal name of the listing in the Snowflake Marketplace or data exchange that provides the share.
PROVIDER_ACCOUNT_LOCATORVARCHARAccount locator of the share owner.
PROVIDER_ACCOUNT_NAMEVARCHARAccount name of the share owner.
SHARE_NAMEVARCHARName of the data share that consumers accessed. When IS_SHARE is FALSE, the value is NULL.
CONSUMER_ACCOUNT_LOCATORVARCHARAccount locator of the consumer.
CONSUMER_ACCOUNT_NAMEVARCHARAccount name of the consumer.
CONSUMER_ACCOUNT_ORGANIZATIONVARCHARName of the organization for the consumer account.
LISTING_OBJECTS_ACCESSEDARRAYUse SHARE_OBJECTS_ACCESSED as it contains the same data. When IS_SHARE is FALSE, the value is NULL. See LISTING_OBJECTS_ACCESSED 数组 for formatting.
REGION_GROUPVARCHARRegion group where the account of the consumer is located.
CONSUMER_NAMEVARCHARContains 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.
IS_SHAREBOOLEANTRUE if the access was on a share. When TRUE, the SHARE_OBJECTS_ACCESSED column provides details about the share objects accessed by the consumer query.
IS_APPLICATIONBOOLEANTRUE if the access was on an application. When TRUE, APPLICATION_OBJECTS_ACCESSED column provides details about the application objects accessed by the consumer query.
SHARE_OBJECTS_ACCESSEDARRAYDetails the share objects accessed by the consumer query. When IS_SHARE is FALSE, the value is NULL. See SHARE_OBJECTS_ACCESSED 数组 for formatting.
APPLICATION_OBJECTS_ACCESSEDARRAYDetails the application objects accessed by the consumer query. When IS_APPLICATION is FALSE, the value is NULL. See APPLICATION_OBJECTS_ACCESSED 数组.
APPLICATION_PACKAGE_NAMEVARCHARThe current name of the application package from which the application was installed. When IS_APPLICATION is FALSE, the value is NULL.
APPLICATION_VERSIONVARCHARThe version of the application when the query occurred. When IS_APPLICATION is FALSE, the value is NULL
APPLICATION_PATCH_IDNUMBERThe application patch number when the query occurred. When IS_APPLICATION is FALSE, the value is NULL.

使用说明

  • 视图的延迟时间最长可达 2 天。
  • 数据会保留 365 天（1 年）。

SHARE_OBJECTS_ACCESSED 数组

SHARE_OBJECTS_ACCESSED 数组提供有关使用者查询访问的共享中的对象的详细信息。数组中项目的格式取决于所访问的对象的类型。

函数：

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

存储过程：

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

表、视图和列：

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

Cortex Search Service：

[
  {
    "objectDomain":"Cortex Search Service",
    "objectId": 12345,
    "objectName": "DATABASE_2.PUBLIC.SHARED_CKE_NAME",
    "hashedDocumentIds": [##hashed_id1##, ##hashed_id2##],
    "hashVersion": "V1"
  }
]

APPLICATION_OBJECTS_ACCESSED 数组

APPLICATION_OBJECTS_ACCESSED 数组提供有关使用者查询访问的原生应用程序中的对象的详细信息。数组中项目的格式取决于所访问的对象的类型。

与 LISTING_OBJECTS_ACCESSED 列数组结果不同，包含对象 IDs 的 APPLICATION_OBJECTS_ACCESSED 结果不可用，且数据库名称已掩码。

函数：

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

存储过程：

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

表、视图和列：

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

LISTING_OBJECTS_ACCESSED 数组

Use SHARE_OBJECTS_ACCESSED Array instead.

示例

本节包含 LISTING_ACCESS_HISTORY 视图的以下示例 SQL 查询。

不同时间段访问情况汇总视图

汇总视图，其中显示了（在特定时间段内）已访问的函数、存储过程、表、视图和列以及总次数。

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;

使用者随时间推移的访问情况汇总视图

This example is similar to 不同时间段访问情况汇总视图, 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;

访问次数（按列）

对于给定对象（表、视图），每列被访问的次数。

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;

表格联接

该视图显示了要将哪些表组合联接在一起。

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

表格联接（按使用者）

该视图显示了按使用者细分的哪些表被联接在一起（成对）。

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;