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_CONVstring TD_CURRENCY_CONV(string date, string from_currency, string to_currency, float value)
TD_IP_TO_COUNTRY_CODEstring TD_IP_TO_COUNTRY_CODE(string ip)
TD_IP_TO_COUNTRY_NAMEstring TD_IP_TO_COUNTRY_NAME(string ip)
Let’s look at some example queries using them.
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):
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('188.8.131.52')
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:
This works similarly to TD_IP_TO_COUNTRY_CODE. Why not try it out?
For more information, check out our documentation on the subject.