New UDFs in Presto: currency conversion and geocoding tools

Today, we introduced three new UDFs (user-defined functions) to to Treasure Data‘s Presto offering. They are:

TD_CURRENCY_CONV

string TD_CURRENCY_CONV(string date, string from_currency, string to_currency, float value)

TD_IP_TO_COUNTRY_CODE

string TD_IP_TO_COUNTRY_CODE(string ip)

TD_IP_TO_COUNTRY_NAME

string TD_IP_TO_COUNTRY_NAME(string ip)

Let’s look at some example queries using them.

TD_CURRENCY_CONV

This function allows us to convert from one currency to another, based on the conversion rates on a given date.   These are the currency codes you should use.

Our documentation provides an example of a query converting a single value:

SELECT TD_CURRENCY_CONV(‘2015-01-01’, ‘USD’, ‘JPY’, 1.0)

This converts US Dollars to Japanese Yen based on the conversion rate on January 1, 2015. Let’s look at a slightly more complex example.

Let’s say you’re an international vendor with income sources from all over the world, in a variety of currencies (click the table for full view):

You want to convert all values to US dollars.  Let’s make a query that converts the currencies and  outputs them into a table which includes the countries and original amount of each transaction:

SELECT TD_CURRENCY_CONV(‘2015-04-10’,country, ‘USD’, amount) AS in_USD, country, amount FROM example

This gives us (click the table for full view):

TD_IP_TO_COUNTRY_CODE

This function allows us to convert an IP address to a country code.  Here is an example of a Presto statement converting a single IP address:

SELECT TD_IP_TO_COUNTRY_CODE(‘184.168.221.1’)

In our next example, we’ll convert a table containing a list of IP addresses to their corresponding country codes and group them by country, so we can see how many sessions are originating from which country. This kind of Geo-IP lookup wasn’t possible before, and the user had to map IPs to country code outside of Treasure Data. From today, it’s a single UDF away.

For a table like this one, which contains the fields “td_ip” and “td_client_id”:

The following query will convert our table to one that counts the number of sessions from each respective country, tallying a total for each. The countries are then sorted in descending order by number of hits.

SELECT TD_IP_TO_COUNTRY_CODE(td_ip) AS country, COUNT(DISTINCT td_client_id) AS num_sessions FROM pageviews GROUP BY TD_IP_TO_COUNTRY_CODE(td_ip) ORDER BY num_sessions DESC

Thus giving us:

TD_IP_TO_COUNTRY_NAME

This works similarly to TD_IP_TO_COUNTRY_CODE.  Why not try it out?

For more information, check out our documentation on the subject.