Troubleshooting External Functions for GCP

This topic provides troubleshooting information for external functions.

In this Topic:


Platform-Independent Symptoms

Actual Return Values for a Data Type Do Not Match Expected Return Values

When passing arguments to or from an external function, ensure that the data types are appropriate. If the value sent can’t fit into the data type being received, the value might be truncated or corrupted in some other way.

For more details, see Ensure that Arguments to the External Function Correspond to Arguments Parsed By the Remote Service.

When you call your function from SQL, you get a message about row numbers being out of order

Possible Cause(s)

Remember that the row numbers you return within each batch should be monotonically ascending integers starting at 0. The input row numbers must also follow that rule, and each output row should match the corresponding input row (e.g. the output in output row 0 should correspond to the input in input row 0).

Possible Solution(s)
  1. Make sure that the row numbers you return are the same as the row numbers you received, and that each output value uses the row number of the corresponding input. That should work. If it doesn’t, then it is possible that the input row numbers were not right, or that you did not return the rows in the correct order, so move on to step 2 below.

  2. Make sure that the output row numbers start from 0, increase by 1, and are in order.

For more information about data input and output formats, see Remote Service Input and Output Data Formats.

When trying to call the external function, you get the message “Error parsing JSON: Invalid response”

Possible Causes(s)

The most likely cause is that the JSON returned by the remote service (e.g. AWS Lambda function) is not constructed correctly.

Possible Solution(s)

Make sure that you return an array of arrays, with one inner array returned for each input row received. Review the description of the output format at Data Format Received by Snowflake.

An error message saying that the format of the returned value is not JSON

Possible Cause(s)

One possible cause of this is that your return value includes double quotes inside the value.

Possible Solution(s)

Although JSON strings are delimited by double quotes, the string itself should not start and end with a quotation mark in most cases. If the embedded double quotes are incorrect, then remove them.

An error message saying that the function received the wrong number of rows

Possible Cause(s)

The remote service probably tried to return more or fewer rows than it received. (Remember, even though the function is nominally scalar, it might receive multiple rows in the “body” field of the “event” parameter, and should return exactly as many rows as it received.)

Possible Solution(s)

Make sure that the remote service returns one row for each row that it receives.

Platform-Specific Symptoms

Request fails with ‘{“message”:”Audiences in Jwt are not allowed”,”code”:403} ‘

Possible Cause(s)
  • This failure indicates that the value in the API integration’s google_audience field is not allowed.

Possible Solution(s)
  • Please verify that the API Integration’s google_audience value matches the managed service name of your API, which should be recorded in the “Managed Service Identifier” field in the worksheet.

  • If you added an x-google-audiences field to the securityDefinitions section of your API config file, make sure that the value in x-google-audiences matches the value in the google_audience field of the API integration.

For more information about authenticating with Google, please see the Google service account authentication documentation.

Request fails with ‘{“message”:”Jwt is missing”,”code”:401} ‘

Possible Cause(s)
  • The value of the x-google-issuer field in the securityDefinitions field in the configuration file might not match the value of the API_GCP_SERVICE_ACCOUNT in the API integration (recorded in the worksheet).

  • The value in x-google-issuer might contain extra whitespace.

Possible Solution(s)
  • Update the x-google-issuer to match the API_GCP_SERVICE_ACCOUNT.

  • Remove unneeded whitespace.

Request fails with ‘403 Forbidden’

Possible Cause(s)

One possible cause for this failure is that the service account using the config does not have the appropriate permissions on the backend.

Possible Solution(s)

Update the service account’s permissions.