In this article, we will look into the process of checking the functions that are available in amazon Athena and how to use them in Athena. There are many functions that can be used with select operations in our Athena. As a good practice before using it, we better check if the function is supported and present with the restore version that Athena is currently using. After logging into the AWS management console navigate to the Athena.
Now be sure that the query editor tab is selected.
Here you can enter the query that you want to run. If we want to check the length of an array that is if we want to find the number of elements in an array. When we run the below query:
-- show functions
We’ll get the list of functions that are supported as shown below :
Now we can search for the respective functions that we want. The description column helps to identify the appropriate function that can be used or if you know the function name but need clarification on the arguments to be used you can directly search the output by searching for the keyword in the browser and then finding the function details. Here we can see that the cardinality function and the name of the function we want is cardinality. Its return type and arguments for an array(ie, array(E) is also provided for reference.
We search and see the arguments to be used and their return type so that we can use this information correctly in our query. Now run a sample query to check the length of an array using the cardinality function as shown below:
Here we can see the output as 3. This is the length of the array.
If we use a function that’s not supported by Athena we get an error such as function: function name not registered. We can test this by running the below example query:
As this function doesn’t exist it will result in the following error: