How to Call External APIs from Snowflake: External Access Integration Guide

0:00 Hey, my name is Annie. Today I'm going to show you how to create a Snowflake external access integration step [music] by step with real code examples. If you've ever tried calling an API from Snowflake and got blocked, this is the solution you've been looking for, [music] stick around to the end because I'll share a realworld example using an open API that you [music] can try right now. Snowflake's documentation on external access integration is excellent and I'll link it in the description for you. It walks through every step in detail. But in this tutorial, I'm going beyond the docs. I'll walk you through every step on a Snowflake [music] trial account so you can follow along with ease. Let's jump right in. Step one is deciding how Snowflake should connect to the external resource, either through the public internet [music] or a private network.

0:51 In simple terms, this means choosing whether your Snowflake environment will reach an API via the open web or through a private secure connection. To keep things straightforward [music] for this tutorial, we'll go with the public internet option. With that decided, we can move [music] to step two, creating a network rule. Let's head over to my Snowflake trial account to see how this is done. I've got the syntax ready for creating a network rule. [music] What we're basically telling Snowflake here is create a network rule called Chuck Norris [music] API network rule. We then set the mode to egress, which [music] simply means outbound traffic. Snowflake is reaching out to the API, [music] not the other way around. In networking terms, egress is just another word for data going out.

1:38 Next, we define the type as host [music] colon port, meaning this rule applies to a specific host name and port. Finally, we specify the host name for the API we want to connect to. Once that's done, the next [music] step, according to Snowflake's documentation, is to create a secret to store credentials. But what does that [music] mean, and why do we need it? Whenever you're connecting to a private API, not a public one, Snowflake needs a way [music] to authenticate itself. This authentication can happen in different ways. commonly with a username and password or more often these days using industry [music] standard methods like ooth tokens. In our case, since we are using the Chuck Norris API, [music] a public API that doesn't require any authentication, we can simply skip this step.

2:27 There's no need to create a secret. So, let's move on to the next step, creating the external access integration. Back in my Snowflake trial account, here's the syntax for creating an external access integration. We start with create or replace external access integration. Give it a name of our choice and then specify the network rule we created earlier in step two using allow [music] network rule. Finally, we set enabled equals [music] true to activate the integration. Normally, I would just run this command. [music] However, because I'm using a trial account, it doesn't support external [music] access, so I can't actually create it on this trial account. However, the syntax is correct, and you can run it on any full Snowflake account.

3:16 [music] I'm showing this so you'll know exactly what to do when you try it yourself. Once the external [music] access integration is set up, the next step, according to Snowflake's documentation, is to create a userdefined function UDF. [music] A UDF is simply a custom function you, the user, define instead of using one of Snowflake's [music] built-in functions. In my example, I'm creating a UDF to call the Chuck Norris API. [music] Here's the syntax for creating the function. We start with create or replace function. Then give our function a name. Next, we define the return [music] type, which in this case is string. We also specify the language [music] which is Python since we'll be writing our code in Python. For the runtime [music] version, we're using 3.9.

4:04 The handler part is a little tricky. This [music] refers to the name of the Python function we defined inside the code. In my example, that function is called get joke. [music] Then we link the function to the external access integration we created earlier [music] by specifying its name. Finally, we list any packages that need to be loaded. [music] After that, between the dollar signs, we place our actual Python code. I recommend writing your Python [music] code in a proper Python editor or interpreter so you can see syntax highlighting. [music] It makes the code much easier to read compared to the plain single color [music] text inside Snowflake. For this demo, I actually used Snowflake notebooks with Python [music] enabled to create the function. Now, let me quickly explain what the code does.

4:51 I've wrapped the logic in a simple try except [music] block. Inside try, I'm calling the API and storing the response. [music] If the request is successful, the function returns the result. If something goes wrong, it returns an error message instead. Once [music] you run this, the function is saved inside snowflake under the database you specify. [music] To use it, you just call it with a select statement, select my function name. If your function requires parameters, you can pass them in [music] directly like select my function name random. Since I'm using a trial account, I can't fully run [music] this because external integrations aren't supported here, but the syntax works perfectly on a full Snowflake account. I'll leave the official Snowflake documentation link in the video description.

5:36 If this tutorial helped you, give it a thumbs up and let me know in the comments how it worked for you or [music] if there's another snowflake topic you'd like me to cover next. Thanks for watching.