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 documentation to learn more.
IPinfo’s Listings on Snowflake Marketplace
IP to Privacy Detection Extended Database - Personalized database listing
Using the ip_privacy_extended
UDTF for our IP to Privacy Extended Database
The IP to Privacy Extended Database is available on the All in One IP database listing or can be requested through our Personalized database listing. This database provides insights our methodology in detecting anonymous IPs.
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_PRIVACY_EXTENDED
UDTF included in your purchased IPinfo listing to enrich this IP log dataset.
SELECT *
FROM logs l
LEFT JOIN TABLE(ipinfo.public.IP_PRIVACY_EXTENDED(l.ip))
Please note that ipinfo
on the ipinfo.public.IP_PRIVACY_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 privacy and it’s detection related information for IPs which includes:
- hosting
- proxy
- tor
- vpn
- relay
- vpn_name
- anycast
- census
- device_activity
- whois
- vpn_config
- census_port
For invalid IP addresses or IP addresses that aren’t included in our database, the outputted table will not include their information. The IP to Privacy Extended Database only includes IPv4 data at this moment [May, 2023].
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/