ACCESS_HISTORY View¶
This Account Usage view can be used to query the access history of Snowflake objects (e.g. table, view, column) within the last 365 days (1 year).
Columns¶
There are two tables in this section:
The first table defines the columns in the ACCESS_HISTORY view.
The second table defines the fields in the JSON array for the BASE_OBJECTS_ACCESSED, DIRECT_OBJECTS_ACCESSED, and OBJECTS_MODIFIED columns.
Column Name |
Data Type |
Description |
Example |
---|---|---|---|
QUERY_ID |
TEXT |
An internal, system-generated identifier for the SQL statement. This value is also mentioned in the QUERY_HISTORY View. |
|
QUERY_START_TIME |
TIMESTAMP_LTZ |
The statement start time (UTC time zone). |
|
USER_NAME |
TEXT |
The user who issued the query. |
|
DIRECT_OBJECTS_ACCESSED |
ARRAY |
A JSON array of data objects such as tables, views, and columns directly named in the query explicitly or through shortcuts such as
using an asterisk (i.e. |
[
{
"columns": [
{
"columnId": 68610,
"columnName": "CONTENT"
}
],
"objectDomain": "Table",
"objectId": 66564,
"objectName": "TEST_DB.TEST_SCHEMA.T1"
}
]
|
BASE_OBJECTS_ACCESSED |
ARRAY |
A JSON array of all base data objects, specifically, columns of tables to execute the query. Note: This field will never specify view names or view columns, including virtual columns. |
[
{
"columns": [
{
"columnId": 68610,
"columnName": "CONTENT"
}
],
"objectDomain": "Table",
"objectId": 66564,
"objectName": "TEST_DB.TEST_SCHEMA.T1"
}
]
|
OBJECTS_MODIFIED |
ARRAY |
A JSON array that specifies the objects that were associated with a write operation in the query. |
[
{
"columns": [
{
"columnId": 68611,
"columnName": "CONTENT"
}
],
"objectDomain": "Table",
"objectId": 66566,
"objectName": "TEST_DB.TEST_SCHEMA.T6"
}
]
|
The fields in the JSON array for the DIRECT_OBJECTS_ACCESSED
, BASE_OBJECTS_ACCESSED
, and OBJECTS_MODIFIED
columns are
described below.
Field |
Data Type |
Description |
---|---|---|
columnId |
NUMBER |
A column ID that is unique within the account. This value is identical to the columnID in the COLUMNS view. |
columnName |
TEXT |
The name of the accessed column. |
objectId |
NUMBER |
An identifier for the object, which is unique within a given account and domain. This number will match: |
objectName |
TEXT |
The fully qualified name of the object that was accessed. If a stage was accessed, this value will be the:
|
objectDomain |
TEXT |
One of the following: |
location |
TEXT |
The URL of the external location when the data access is an external location (e.g. |
stageKind |
TEXT |
When writing to a stage, one of the following: If the query does not access a stage, this field is omitted. |
Usage Notes¶
The view displays data starting from February 22, 2021.
For increased performance, filter queries on the QUERY_START_TIME column and choose narrower time ranges. For sample queries, see Querying the ACCESS_HISTORY View.
This view supports read queries of the following type:
SELECT, including CREATE TABLE … AS SELECT (i.e. CTAS).
Snowflake records the SELECT subquery in a CTAS operation.
CREATE TABLE … CLONE
Snowflake records the source table in a CLONE operation.
COPY INTO … TABLE
Snowflake logs this query only when the table is specified as the source in a FROM clause.
DML operations that read data (e.g. contains a SELECT subquery, specifies certain columns in WHERE or JOIN): INSERT … SELECT, UPDATE, DELETE, and MERGE.
User-defined functions (i.e. UDFs) and Tabular SQL UDFs (UDTFs) if tables are included in queries inside the functions. This is logged in the
BASE_OBJECTS_ACCESSED
field.
This view supports write operations of the following type:
GET
<internal_stage>
PUT
<internal_stage>
DELETE
TRUNCATE
INSERT
INSERT INTO … FROM SELECT *
INSERT INTO TABLE … VALUES ()
MERGE INTO … FROM SELECT *
UPDATE
UPDATE TABLE … FROM SELECT * FROM …
UPDATE TABLE … WHERE …
Data loading statements:
COPY INTO TABLE FROM internalStage
COPY INTO TABLE FROM externalStage
COPY INTO TABLE FROM externalLocation
Data unloading statements:
COPY INTO internalStage FROM TABLE
COPY INTO externalStage FROM TABLE
COPY INTO externalLocation FROM TABLE
CREATE:
CREATE DATABASE … CLONE
CREATE SCHEMA … CLONE
CREATE TABLE … CLONE
CREATE TABLE … AS SELECT
This view does not log accesses of the following types:
Table Functions or other Account Usage views.
RESULT_SCAN to obtain prior results.
Additionally, this view does not support:
Sequences, including generating new values.
Data that enters or leaves Snowflake while using an External Function.
Intermediate views accessed between the base table and direct object.
For example, consider a query on View_A with the following object structure: View_A » View_B » View_C » Base_Table.
The ACCESS_HISTORY view records the query on View_A and the Base_Table, not View_B and View_C.
The operations to populate views, materialized views, and streams.
Data movement resulting from replication.
If a Data Sharing Provider account shares objects to Data Sharing Consumer accounts through a share:
Data Sharing Provider accounts: the queries and logs on the shared objects executed in the Provider account will not be visible to Data Sharing Consumer accounts.
Data Sharing Consumer accounts: the queries on the data share executed in the Consumer account will be logged and only visible to the Consumer account, not the Data Sharing Provider account. The base tables accessed by the data share will not be logged.
Secure Views. The log record contains the underlying base table (i.e.
BASE_OBJECTS_ACCESSED
) to generate the view. Examples include queries on other Account Usage views and queries on base tables for extract, transform, and load (i.e. ETL) operations.