Categories:: Information Schema , Table functions

EXTERNAL_FUNCTIONS_HISTORY¶

This table function retrieves the history of external functions called by Snowflake for your entire Snowflake account.

Note

This function can return results only for activity within the last 14 days.

Syntax¶

EXTERNAL_FUNCTIONS_HISTORY(
      [ DATE_RANGE_START => <constant_date_expression> ]
      [, DATE_RANGE_END => <constant_date_expression> ]
      [, FUNCTION_SIGNATURE => '<string>' ] )

Copy

Arguments¶

All the arguments are optional.

DATE_RANGE_START => constant_date_expression , . DATE_RANGE_END => constant_date_expression

The date/time range, within the last 2 weeks, for which to retrieve the history:

If an end date is not specified, then CURRENT_DATE is used as the end of the range.
If a start date is not specified, then the range starts 10 minutes prior to the start of DATE_RANGE_END (i.e. the default is to show the previous 10 minutes of history). For example, if DATE_RANGE_END is CURRENT_DATE, then the default DATE_RANGE_START is 11:50 PM on the previous day.

History is displayed in increments of 5 minutes, 1 hour, or 24 hours (depending on the length of the specified range).

If the range falls outside the last 15 days, an error is returned.

FUNCTION_SIGNATURE => string

A string specifying an external function name and the data types of the arguments to the function. (The data types distinguish among overloaded function names.) Only information about that function is returned.

Put the signature inside single quotes, for example:

function_signature => 'mydb.public.myfunction(integer, varchar)'

Copy

Note that the argument data types, but not the argument names, are specified.

If no signature is specified, then the output includes the total for all external functions in use within the time range, and the following columns in the results display NULL:

FUNCTION_NAME.
ARGUMENTS.
FUNCTION_ENDPOINT_URL.
SOURCE_CLOUD.
SOURCE_REGION.
TARGET_CLOUD.
TARGET_REGION.

Usage notes¶

Returns results only for the ACCOUNTADMIN role or any role that has been explicitly granted the MONITOR USAGE global privilege.
When calling an Information Schema table function, the session must have an INFORMATION_SCHEMA schema in use or the function name EXTERNAL_FUNCTIONS_HISTORY must be fully-qualified. For more details, see Snowflake Information Schema.
The output column named ARGUMENTS includes not only the argument data types, but also the return data type. The input parameter named FUNCTION_SIGNATURE should include the data types of the arguments, but not the return data type.
For troubleshooting tips, see Symptom: EXTERNAL_FUNCTIONS_HISTORY returns “…invalid identifier…”.

Output¶

The function returns the following columns:

Column Name	Data Type	Description
START_TIME	TIMESTAMP_LTZ	Start of the specified time range for which to return history.
END_TIME	TIMESTAMP_LTZ	End of the specified time range for which to return history.
NAME	TEXT	Name of the function for which to return history.
ARGUMENTS	TEXT	Data types of the arguments and of the return value. The data types of the arguments distinguish between overloaded function names.
FUNCTION_ENDPOINT_URL	TEXT	HTTPS endpoint that the function calls. This is typically a proxy service.
SOURCE_CLOUD	TEXT	Cloud platform from which rows were sent (e.g. `GCP`, `Azure`, or `AWS`).
SOURCE_REGION	TEXT	Region from which rows were sent (e.g. `eu-west-1`).
TARGET_CLOUD	TEXT	Cloud platform to which rows were sent (e.g. `GCP`, `Azure`, or `AWS`).
TARGET_REGION	TEXT	Region to which rows were sent (e.g. `eu-west-1`).
INVOCATIONS	NUMBER	Number of times that the remote service was called during the START_TIME and END_TIME window. This includes retries (e.g. due to temporary network problems).
SENT_ROWS	NUMBER	Number of rows sent to the external endpoint during the START_TIME and END_TIME window.
RECEIVED_ROWS	NUMBER	Number of rows received from the external endpoint during the START_TIME and END_TIME window.
SENT_BYTES	NUMBER	Number of bytes sent to the external endpoint during the START_TIME and END_TIME window.
RECEIVED_BYTES	NUMBER	Number of bytes received from the external endpoint during the START_TIME and END_TIME window.

Examples¶

Retrieve the history for a 30 minute range, in 5 minute periods, for your account:

select *
  from table(information_schema.external_functions_history(
    date_range_start => to_timestamp_ltz('2020-05-24 12:00:00.000'),
    date_range_end => to_timestamp_ltz('2020-05-24 12:30:00.000')));

Copy

Retrieve the history for the last 12 hours, in 1 hour periods, for a single external function in your account:

select *
  from table(information_schema.external_functions_history(
    date_range_start => dateadd('hour', -12, current_timestamp()),
    function_signature => 'mydb.public.myfunction(integer, varchar)'));

Copy

Retrieve the history for the last 14 days, in 1 day periods, for your account:

select *
  from table(information_schema.external_functions_history(
    date_range_start => dateadd('day', -14, current_date()),
    date_range_end => current_date()));

Copy

Retrieve the history for the last 14 days, in 1 day periods, for a specified function in your account:

select *
  from table(information_schema.external_functions_history(
    date_range_start => dateadd('day', -14, current_date()),
    date_range_end => current_date(),
    function_signature => 'mydb.public.myfunction(integer, varchar)'));

Copy

Troubleshooting¶

Symptom: EXTERNAL_FUNCTIONS_HISTORY returns “…invalid identifier…”¶

Possible Cause:

You might not have put the function signature in single quotes. For example, the following is wrong because it does not include the quotes:

select *
  from table(information_schema.external_functions_history(
    function_signature => mydb.public.myfunction(integer, varchar)));

Copy

Possible Solution:

Correct this by adding quotation marks around the function signature:

select *
  from table(information_schema.external_functions_history(
    function_signature => 'mydb.public.myfunction(integer, varchar)'));

Copy

Symptom: EXTERNAL_FUNCTIONS_HISTORY returns only one row of output, and many of the columns are NULL¶

Possible Cause:

You probably did not include a function signature. If you do not specify a function signature, then EXTERNAL_FUNCTION_HISTORY() returns the aggregate values for columns such as INVOCATIONS, SENT ROWS, etc., and returns NULL for columns such as the function name, the argument lists, etc.

Possible Solution:

If you intended to get information for one function, then include a function signature.

If you intended to get information for all functions, then the NULL values for some columns are correct, and you do not need to fix the query.