See why hundreds of industry leaders trust Secoda to unlock their data's full potential.
See why hundreds of industry leaders trust Secoda to unlock their data's full potential.
Get the guideExternal functions in Snowflake are user-defined functions (UDFs) that allow the execution of code outside of Snowflake within a remote service. This architecture enables complex computations and integrations that extend beyond the native capabilities of Snowflake itself. External functions operate similarly to traditional UDFs within SQL statements. They accept parameters, process these parameters via a remote service, and return the results to Snowflake. The interaction with the remote service typically occurs through a proxy service, enhancing security and facilitating various billing models.
The workflow of external functions involves several steps to ensure seamless execution and data processing. Here is a detailed overview:
1. A SQL statement that calls an external function is executed within Snowflake.
2. Snowflake reads the external function definition and the corresponding API integration information.
3. An HTTP POST request is sent from Snowflake to the proxy service, containing the data in JSON format.
4. The proxy service forwards the request to the remote service, which processes the data and returns the result.
5. If asynchronous processing is required, Snowflake sends multiple HTTP GET requests until a timeout or error occurs.
This workflow ensures that data is processed efficiently and securely, leveraging the capabilities of remote services.
Snowflake external functions consist of several key components that work together to enable their functionality:
Begin by defining the external function in Snowflake. This involves specifying the remote service and the parameters it will accept.
CREATE OR REPLACE FUNCTION my_external_function(input_param STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
RUNTIME_VERSION = '1.0'
HANDLER = 'handler'
API_INTEGRATION = 'my_api_integration'
AS 'https://my-remote-service.com/execute';
This code defines an external function that calls a remote service to process the input parameter and return a result.
Next, create the API integration that will handle the communication between Snowflake and the remote service.
CREATE OR REPLACE API INTEGRATION my_api_integration
API_PROVIDER = 'aws_api_gateway'
API_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/my-role'
API_ALLOWED_PREFIXES = ('https://my-remote-service.com/');
This code sets up the API integration with the necessary security and configuration settings.
Finally, execute the external function within a SQL statement to process data using the remote service.
SELECT my_external_function('input_data') FROM my_table;
This SQL statement calls the external function, passing in the input data and receiving the processed result from the remote service.
While using Snowflake external functions, users may encounter several common challenges. Here are some solutions:
Snowflake external functions offer a powerful way to extend the capabilities of the Snowflake platform by leveraging remote services. Here are the key takeaways:
By understanding and applying these concepts, users can effectively utilize Snowflake external functions to enhance their data processing and analysis capabilities.
Built for data teams, designed for everyone so you can get more from your data stack.
© 2024 Secoda, Inc. All rights reserved.
By using this website, you accept our Terms of Use and Privacy Policy.
