All right, so one of the common topics that you heard me already warn you about when it comes to pricing and other limitations, but it's potentially powerful, right? With great power, comes great responsibility, is the concept of a user-defined function. So if the function doesn't exist already inside a BigQuery, cool news is, you can create it. Or even cooler news is, somebody else has already created it in JavaScript, and you can just invoke that. So some fun examples that I've seen of really cool user-defined functions that you can just check out on the web, natural language processing in BigQuery. So if you're looking at Tweets or Reddit post comments and you want to do some sentiment analysis or seeing the closeness between words, you can actually do that with some pretty cool JavaScript libraries. And inside of BigQuery, you can invoke those or call those JavaScript libraries if they're stored in a Google Cloud Storage bucket. And what you can do then after that is then, in your actual query, you can invoke those functions. So for example here, you see CREATE TEMPORARY FUNCTION as a greeting. And the input is a string value. And it also is going to return a string. So when you pass in whatever value it is, it's pretty much just going to add on or concatenate Hello. And then whatever the field that you're passing it in. And add an exclamation mark at the end. So pretty simple, again, this is something that I would highly recommend doing through SQL and concatenation instead of invoking some JavaScript to do it, just for performance reasons. But I've seen some pretty complex things that you can do with JavaScript. And the two flavors, or two languages, that you can write these user-defined functions in are currently SQL. So you can actually do some SQL functions that don't already exist, you can create them, and JavaScript as well. I'll show you a quick example of just what a UDF looks like, the different pieces. I've got a saved query. I'm going to my query history. And click on Saved Queries, in case you're wondering how to actually get back to the queries that you saved, that's how. And a quick UDF example that I have pulled up here. So this is kind of just a pretty simplistic example but I'll show you the syntax. So this is the CREATE TEMPORARY FUNCTION that says create a function that hopefully doesn't already exist yet. And give it a name, and some input values, and then the data type of those input values. This one takes two inputs, and it just simply multiplies those two inputs together, as you see here. It just says, return it as the JavaScript, right? And so just return x times y as the product, right? And then the second function, you can just chain these together as much as you want. It just says give me one input and I'm just going to divide that input by 2, as you see here, return x / 2. And then we have some numbers that are generated here. And again, using that WITH clause, you can just pretty much create your own demo data on the fly. And then we basically have x and y and some operations that are happening over them based on these user-defined functions. So here, as you might expect, are your inputs from this data table that we've created. 1 and 5, 2 and 10, 3 and 15, that's the data that we have as part of this little table for free here. And then the functions that we're invoking on them are custom, right? Multiply inputs, divide by 2, divide by 2. And of course, you can nest these as you would any other functions, and you get those results here. So again, the warning here is these are pretty trivial functions. So I highly recommend you do those inside of SQL themselves. But you can image with JavaScript, or some pretty advanced SQL, you could create some functions. Say, if your organization has a special way for handling some numeric value where it's just not a function that you might find in the SQL library. So you can create those for yourselves to prevent having to continuously do those calculations over time. So those are UDFs. And in case I didn't make those caveats strong enough, it's the subject of our next key pitfall. So if it's not clear yet, there's a drastic potential performance hit. And if you want to see that in an example here, the concurrent rate limit for queries that don't contain UDFs, you can run 50 queries at the same time. If you don't have UDFs in them, 50 queries at the same time is a ton of queries. But for UDF-queries, you're limited to 6. So you can see it's a drastic quota restriction, and for good reason. Again, your operating JavaScript, potentially, long JavaScript functions that are being called over each of your rows that you're processing those on as well. But it does exist, right? Just wield that power responsibly.
