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: ASN Database
IPinfo ASN Database— Snowflake Marketplace
Note that the ASN database is different than our IPinfo Country ASN free database as the paid ASN database contains two additional fields –
AS_TYPEandAS_COUNTRY
Using the IP_ASN UDTF for our IPinfo ASN database
Let’s say your table containing the IP addresses is called LOGS, and the column containing the IP address is called IP. Based on this, use the provided IP_ASN UDTF from the public schema of your IPinfo shared database.
SELECT *
FROM logs l
LEFT JOIN TABLE(ipinfo.public.IP_ASN(l.ip));
Please note that, the ipinfo on the ipinfo.public.IP_ASN 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(<data_share_name>.public.<UDTF_name>(<table_alias.column_name>))
The UDTF will return the ASN related information for IPs which includes:
- ASN
- AS organization name
- AS official domain/website
- AS organization type (ISP, Business, Hosting and Education)
- AS organization country
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: How to get IP Data in Snowflake