On Snowflake, all of our listings come with their own unique UDTF (User Defined Table Functions) that is designed to enrich your IP database in the most efficient way possible. You should exclusively use these UDTFs if you are doing bulk IP data enrichment using our database.
Please refer to our Snowflake Listing Technical Guide to learn more.
The IP to Geolocation Extended Database is available on the All in One IP database listing or can be requested through our Personalized database listing. This database provides the accuracy radius field for each row of IP geolocation data.
Suppose the table containing the IP addresses is called
LOGS , and the column containing the IP address is called
IP . You can use the
IP_LOCATION_EXTENDED UDTF included in your purchased IPinfo listing to enrich this IP log dataset.
SELECT * FROM logs l JOIN TABLE(ipinfo.public.ip_location_extended(l.ip))
Please note that
ipinfo on the
ipinfo.public.IP_LOCATION_EXTENDED can be different for you. It depends on the name you gave to our listing when you purchased it.
The UDTF format is:
SELECT <column_containing_ip_addresses> FROM <input_table> <table.alias> JOIN TABLE(ipinfo.public.<UDTF_name>(<table_alias.column_name>))
The UDTF will return the IP geolocation, geoname id for geonames.org and accuracy radius for IPs which includes:
If you want to learn about the technical aspect of how we came up with these UDTFs and the process behind their efficiency, read this article: How to get IP Data in Snowflake