We use cookies to store information on your computer. By continuing to use our site, you consent to our cookies. If you are not happy with the use of these cookies, please review our cookie policy to learn how they can be disabled. By disabling cookies, some features of the site will not work.
New UDFs in Presto: currency conversion and geocoding tools
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 exampleThis 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.
