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:
IPinfo’s Listings on Snowflake Marketplace
Documentation and Database Schema: IP to Privacy Detection
IPinfo IP to Privacy Detection — Snowflake Marketplace
Using the IP_PRIVACY UDTF for our IP to Privacy Detection database
The IP to Privacy Detection database or Anonymous IP database includes the IP addresses that are associated with VPN, proxy, Tor exit node, data center/cloud provider/hosting service, and relay service.
Suppose, the table containing the IP addresses is called LOG, and the column containing the IP address is called IP. You can use the IP_PRIVACY UDTF included in your purchased IPinfo listing to enrich this IP log dataset. This function flags IP addresses associated with anonymous IP services such as VPNs, proxies, Tor, and Apple/ CDN relay.
SELECT *
FROM log l
LEFT JOIN TABLE(ipinfo.public.IP_PRIVACY(l.ip))
Please note that ipinfo on the ipinfo.public.IP_PRIVACY 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 privacy-related information for IPs which includes:
- Hosting
- Proxy
- Tor
- VPN
- Relay
- Name of the service (if available)
For invalid IP addresses or IP addresses that aren’t included in our database, the outputted table will not include their information
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: https://ipinfo.io/blog/ip-address-data-in-snowflake/