
<p>Today the <a href="https://aws.amazon.com/blogs/aws/user-defined-functions-for-amazon-redshift/" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">Redshift team announced support</a> for <a href="https://docs.aws.amazon.com/redshift/latest/dg/user-defined-functions.html" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">User Defined Functions</a>! UDFs allow you to create your own SQL functions, backed by the power and flexibility of Python. Now you can add your favorite functions from other databases or invent new ones to make your data analysis much easier.</p>



<p>Based on what we’ve seen customers doing in Redshift, we put together a few dozen helpful UDFs for everyone to use. <a href="https://github.com/PeriscopeData/redshift-udfs/blob/master/udfs.sql" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">They are hosted on GitHub</a>. Here’s what’s available so far:</p>



<h2 class="wp-block-heading"><strong>New Redshift Functions from Sisense for Cloud Data Teams</strong></h2>



<h4 class="wp-block-heading"><strong>JSON Array Support</strong></h4>



<ul><li>json_array_first &#8211; get the first element of the array</li><li>json_array_last &#8211; get the last element of the array</li><li>json_array_nth &#8211; gets the Nth element in the array</li><li>json_array_sort &#8211; sorts the elements in the array</li><li>json_array_reverse &#8211; reverses the order of elements in the array</li><li>json_array_pop &#8211; returns the array without the last element</li><li>json_array_push &#8211; returns the array with the new element at the end</li><li>json_array_concat &#8211; combines two arrays into one</li></ul>



<h4 class="wp-block-heading"><strong>MySQL Date Compatibility Helpers</strong></h4>



<ul><li>mysql_year &#8211; extracts the year from the timestamp</li><li>mysql_month &#8211; extracts the month (1-12) from the timestamp</li><li>mysql_day &#8211; extracts the date (1-31) from the timestamp</li><li>mysql_hour &#8211; extracts the hour (0-23) from the timestamp</li><li>mysql_minute &#8211; extracts the minute from the timestamp</li><li>mysql_second &#8211; extracts the second from the timestamp</li><li>mysql_yearweek &#8211; formats a timestamp as a year-week (YYYYWW)</li></ul>



<h4 class="wp-block-heading"><strong>Number Utilities</strong></h4>



<ul><li>format_num &#8211; formats a number with commas, percents, padding, etc.</li><li>second_max &#8211; gets the second-highest value from a numeric column</li></ul>



<h4 class="wp-block-heading"><strong>Varchar Utilities</strong></h4>



<ul><li>email_name &#8211; extracts the mailbox name from the email address</li><li>email_domain &#8211; extracts the domain from the email address</li><li>url_protocol &#8211; extracts the protocol from the URL</li><li>url_domain &#8211; extracts the domain from the URL</li><li>url_path &#8211; extract the path from the URL</li><li>url_param &#8211; extract a parameter’s value from the URL</li><li>split_count &#8211; split a varchar into parts and get the count of those parts</li><li>titlecase &#8211; title-case the varchar value</li><li>str_multiply &#8211; repeats the varchar several times</li><li>str_index &#8211; find the leftmost index of a substring in the varchar</li><li>str_rindex &#8211; find the rightmost index of a substring in the varchar</li><li>str_count &#8211; counts the number of occurrences of a substring within the varchar</li></ul>



<h4 class="wp-block-heading"><strong>Time Helpers</strong></h4>



<ul><li>now &#8211; a common alias for getdate</li><li>posix_timestamp &#8211; get the number of seconds since the epoch for a timestamp</li></ul>



<p>All of these UDFs were built using a framework we created to make managing and testing UDFs easy.</p>



<h2 class="wp-block-heading"><strong>Installing, Developing and Contributing UDFs</strong></h2>



<p>To make development easier, we built a framework for developing UDFs. The framework is available in our <a href="https://github.com/PeriscopeData/redshift-udfs" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">GitHub repo</a>. Creating a new UDF is as simple as adding a few lines to a config file.</p>



<p>Here’s the config to make a UDF called email_domain, it extracts the domain from an email address. The config defines the name, input and output types, the Python function body, and several unit tests:</p>



<pre class="wp-block-code"><code>{
  type:        :function,
  name:        :email_domain,
  description: "Gets the domain from the email address",
  params:      "email varchar(max)",
  return_type: "varchar(max)",
  body:        %~
    if not email:
      return None
    return email.split('@')[-1]
  ~,
  tests:  [
    { query: "select ?('sam@company.com')",
      expect: 'company.com',
      example: true
    }, {
      query: "select ?('alex@othercompany.com')",
      expect: 'othercompany.com',
      example: true
    },
  ]
}</code></pre>



<p>And the udf.rb runner takes care of creating, dropping, testing, and pretty-printing the UDF’s SQL:</p>



<pre class="wp-block-code"><code>Usage:
  ruby udf.rb &lt;action> [udf_name]
Actions:
  load    Loads UDFs into your database
  drop    Removes UDFs into your database
  test    Runs UDF unit tests on your database
  print   Pretty-print SQL for making the UDFs
Examples:
  ruby udf.rb load
  ruby udf.rb drop url_domain
  ruby udf.rb test json_array_first
  ruby udf.rb print</code></pre>



<p>For example, to load this UDF, run:</p>



<pre class="wp-block-code"><code>ruby udf.rb load email_domain</code></pre>



<p>To test this UDF, run:</p>



<pre class="wp-block-code"><code>ruby udf.rb test email_domain</code></pre>



<p>And you’ll see output like this:</p>



<pre class="wp-block-code"><code>Making function email_domain
Testing function email_domain....</code></pre>



<p>With this framework it only takes a couple minutes to make a UDF, the unit tests prevent the UDFs from breaking, and the automation makes adding UDFs to new clusters a breeze.</p>



<p>We hope you’ll find this framework useful when creating and maintaining UDFs, and look forward to feature requests, improvements, and many more UDFs!</p>



<p>To better understand how UDFs work, let’s dive in to the syntax for creating them.</p>



<h2 class="wp-block-heading"><strong>How To Write Scalar UDFs</strong></h2>



<p>The first kind of UDF supported by Redshift are Scalar UDFs. They apply to individual values. They work like a lot of the functions you’re used to: date_trunc, json_extract_path_text, getdate, round, etc. Here’s a UDF to get the domain from an email address:</p>



<pre class="wp-block-code"><code>/*
EMAIL_DOMAIN
Gets the domain from the email address
Examples:
  select email_domain('sam@company.com')
    --> 'company.com'
  select email_domain('alex@othercompany.com')
    --> 'othercompany.com'
*/
create or replace function email_domain (email varchar(max))
  returns varchar(max)
  stable as $$
    if not email:
      return None
    return email.split('@')[-1]
  $$ language plpythonu</code></pre>



<p>The function is defined by create or replace email_domain, the first and only parameter is a varchar called email, and the return type is also a varchar.</p>



<p>There are three kinds of function types and they are used by the query planner to know when it can optimize out function calls. This function uses stable:</p>



<ul><li>stable used for functions that return the same value for the same input params (most common)</li><li>volatile used for functions that can return different values for the same input param (e.g. random)</li><li>immutable used for functions that always return constants</li></ul>



<p>And the Python function body is between the $$, followed by the language definition.</p>



<h2 class="wp-block-heading"><strong>Library Support</strong></h2>



<p>Redshift’s UDFs have access to the full standard library and several popular packages including numpy, pandas, python-dateutil, pytz, and scipy. Here’s an example of importing the JSON library to make working with JSON arrays easy:</p>



<pre class="wp-block-code"><code>/*
JSON_ARRAY_SORT
Returns sorts a JSON array and returns it as a string,
  second param sets direction
Examples:
  select json_array_sort('["a","c","b"]', true)
    --> '["a", "b", "c"]'
  select json_array_sort('[1, 3, 2]', true)
    --> '[1, 2, 3]'
*/
create or replace function json_array_sort
    (j varchar(max), ascending boolean)
  returns varchar(max)
  stable as $$
    import json
    if not j:
      return None
    try:
      arr = json.loads(j)
    except ValueError:
      return None
    if not ascending:
      arr = sorted(arr, reverse=True)
    else:
      arr = sorted(arr)
    return json.dumps(arr)
  $$ language plpythonu;</code></pre>



<p>Keep in mind that UDFs are per-database. So if you have multiple databases in your Redshift cluster, you’ll need to add the UDFs to each database.</p>


Redshift User Defined Functions in Python

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article