Look up IP addresses from IPinfo’s IP to Company database on Snowflake

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.

:link: Please refer to our Snowflake documentation to learn more:

:link: IPinfo’s Listings on Snowflake Marketplace

:link: Documentation and Database Schema: IP to Company Database

:link: IPinfo IP to Company — Snowflake Marketplace

Using the IP_COMPANY UDTF for our IP to Company database

Suppose the table containing the IP addresses is called LOG, and the column containing the IP address is called IP. By using the IP_COMPANY UDTF included in your purchased IPinfo listing, we can enrich this IP log dataset. This will add company-level information to your IP address dataset.

FROM log l
LEFT JOIN TABLE(ipinfo.public.IP_COMPANY(l.ip))

Please note that ipinfo on the ipinfo.public.IP_COMPANY 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 company/organization-related information for IPs which includes:

  • Name of the organization
  • Domain / Website of the organization
  • Type of the organization
  • ASN
  • Name of the AS
  • Domain / Website of the AS
  • AS Type
  • 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: https://ipinfo.io/blog/ip-address-data-in-snowflake/