LISTING_ACCESS_HISTORY 视图
DATA_SHARING_USAGE 架构中的此视图可用于浏览使用者的列表使用历史记录。LISTING_ACCESS_HISTORY 提供有关针对列表所附数据共享或原生应用程序运行的查询的对象级信息。有关 LISTING_ACCESS_HISTORY 视图提供的数据的更多信息，请参阅 列 部分。
LISTING_ACCESS_HISTORY 返回的每一行都代表使用者对列表的一次访问。由于这些行代表的是查询而不是会话，因此同一列表很可能会出现多次，每次查询出现一行。
单个使用者查询可以访问多个列表中的对象。QUERY_TOKEN 识别在列表访问历史记录中生成一行的查询。要识别通过单个使用者查询访问的列表对象的集合，请使用 QUERY_TOKEN。
LISTING_ACCESS_HISTORY 视图不允许提供商获取任何私人使用者信息，例如查询的实际文本。该视图还排除不属于提供商账户的任何对象。例如，如果使用者将列表中的数据与他们自己的数据或其他提供商的数据相结合，则 LISTING_ACCESS_HISTORY 视图仅返回您拥有的列表对象。
使用说明
视图的延迟时间最长可达 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"
}
]
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_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;
使用者随时间推移的访问情况汇总视图
此示例类似于 不同时间段访问情况汇总视图，按使用者细分。
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;