Official client Libraries

Snowflake

IPinfo on Snowflake Marketplace

IPinfo is the premier IP data provider in the Snowflake Marketplace. To make the most of our data in Snowflake, we recommend that you go through this documentation.

Snowflake header image

IPinfo on the Snowflake Marketplace

You can check out our IP databases from our storefront as well. Each listing page contains a description of the product, available column/field names, use cases, sample queries, and other useful information to get you up and running. We currently have the following IP datasets available in the Snowflake Marketplace.

Product	Description
IPinfo Lite	Open access, full-accuracy IP datasets that provides IP to country and ASN details.
IPinfo Core	City-level precision and network connection context (anonymous+, satellite and carrier)
IPinfo Plus	Comprehensive IP intelligence, including location, anonymity/privacy, ASN, carrier, and stability.
IPinfo Residential Proxy	Detect residential and mobile proxy IPs with service and usage metrics.
IPinfo IP Geolocation	Accurate IP location with detailed and granular precision.
IPinfo Company IP Address Data	Identify companies and organizations through IP addresses.
IPinfo Privacy Detection (VPN, TOR, Relay, Proxy etc.)	Detect hosting, VPN, proxy, Relay and Tor IPs.
Comprehensive IP WHOIS Data	Structured IP WHOIS data for ASN, Organization, Maintainer, Network & POC Records
IPinfo: IP Geolocation Training/Education Sample	Snowflake's "The Hands-On Essentials Workshop Series" workshop practices data.

Database Operations

Let's discuss some of the basic IPinfo IP database operations. For this guide, we expect that you are familiar with the Snowflake platform. However, if you need any assistance, our data experts are happy to guide you.

IPinfo Basic UDFs (User-Defined Functions)

To ease the process of using our database products and enable our users to use the best-performing solutions, we have developed many handy UDFs (User Defined Functions).

🔗 Snowflake Documentation on UDF.

Our basic UDFs are scalar in nature and output a single value in TEXT or multiple values contained within an ARRAY.

User-Defined Functions (UDF)	Description
`RANGE_TO_CIDR`	Converts `start_ip` and `end_ip` columns to their network range / CIDR equivalent. Data outputted as an array.
`RAND_IP`	Returns an array of random IPv4 or IPv6 addresses.
`IP_CONTINENT`	Returns the continent name for a single IP address lookup.
`COUNT_IP`	Counts the IP addresses between the `start_ip` and `end_ip` values.

Additionally, we provide a complimentary table called “COUNTRIES” that includes country codes, country names, and continent names.

Account and share status and metadata

For account and listing metadata information, you can use our included UDTFs: STATUS and STATUS_DETAILS.

STATUS returns artifact/dataset and account-related information. The returned table contains the following information:

ACCT: Account ID.
ORG: Organization ID.
REGION: Cloud platform and region name.
REFRESH_FREQUENCY: The update frequency of the current dataset/artifact.
LAST_ALTERED: Last alter or update date and time.

SELECT *
FROM TABLE(IPINFO.PUBLIC.STATUS());

Status table UDF

The IPINFO is the dataset name.

STATUS_DETAILS returns the following information for all the resources included in an IPinfo Snowflake dataset:

NAME: Name of the resource
TYPE: Type of the resource
REFRESH_FREQUENCY: The refresh frequency of the resource
LAST_ALTERED: Last alter or update date and time.

SELECT *
FROM TABLE(IPINFO.PUBLIC.STATUS_DETAILS());

Status Details table UDF

Sample IP address table

With all of our listings, we include the ip_sample table, which contains 1,000 sample IP addresses, including 500 IPv4 addresses and 500 IPv6 addresses. You can use these IP addresses for query testing.

Log data IP sample

IPinfo Query Function

The IPinfo Query Functions are UDTFs or Table Functions that return tabular data when queried against a specific IP dataset using IP addresses. To enable faster and more efficient IP address lookups from our database, we have developed these UDTFs. These custom functions remove the guesswork from querying our database and ensure best practices.

UDTF demo

The query functions are available in the public schema of the database listing. When you purchase our listing, you have the option to provide a name for the shared database. You can learn more about this in our IPinfo community post.

Ff you have purchased our IP geolocation listing and named our shared database ipinfo, then your functions, views, and tables will be available in the ipinfo.public namespace. Here, ipinfo is the name of the shared database, and public is the schema. To access the IP_LOCATION UDTF, you would call ipinfo.public.ip_location. Similarly, you can access the IP_GEOLOCATION view by calling ipinfo.public.ip_geolocation.

If you have access to the database product, you will also have access to the associated UDTF. Each UDTF corresponds to its respective database.

Views, UDTFs and Database Names

Reference list for the database names, views/tables, and UDTFs.

Database Name: The database name is assigned when you purchase or trial our database listing. We recommend changing the name upon purchasing the database, as the standard database names can be quite long. We are mentioning the standard database names here for reference purposes only.

View: This is where our data lives. Please note that composite database listings may contain multiple views as they usually consist of multiple databases.

Query Function - UDTF: A table function used to query our IP databases.

Composite listings: Some of our listings contain multiple IP databases. They include multiple views and multiple UDTFs under one database. For example: IPinfo Whois Full Data

Product	UDTF	VIEW / TABLE
IPinfo Lite	GET_IP_LITE	LITE
IPinfo Core	GET_IP_CORE	IP_CORE
IPinfo Plus	GET_IP_PLUS	IP_PLUS
IPinfo Residential Proxy	IP_PROXY_RESIDENTIAL	IP_PROXY_RESIDENTIAL
IPinfo IP Geolocation	IP_LOCATION	IP_GEOLOCATION
IPinfo Company IP Address Data	IP_COMPANY	IP_COMPANY
IPinfo Privacy Detection (VPN, TOR, Relay, Proxy etc.)	IP_PRIVACY	IP_PRIVACY
Comprehensive IP WHOIS Data		IP_WHOIS_NET IP_WHOIS_ASN IP_WHOIS_MNT IP_WHOIS_ORG IP_WHOIS_POC

Accessing Our Daily Updated Databases on Snowflake Marketplace

Our standard database listings on Snowflake Marketplace are updated on a monthly basis. However, we offer a daily updated database as part of our Learn more from our IPinfo community post.

Our IPinfo Lite data on Snowflake is updated daily and provides full accuracy.

Joining our database to your database using UDTFs

Our User-Defined Table Functions (UDTFs) provide a highly efficient method for merging our database insights with your log or IP address column. We have presented a list of all the available UDTFs in the preceding section.

For example, let's consider our logs database, which appears as follows:

joining_database_sample_ip

IP_LOCATION UDTF using the IP to Geolocation database:

SELECT *
FROM logs l
JOIN TABLE (ipinfo.public.ip_location(l.ip));

Result:

joining_database_sample_ips_enriched_with_ip_location_udtf

UDTF code Template:

SELECT *
FROM <db_table> <db_alias>
JOIN TABLE(<ipinfo_database_name>.<ipinfo_schema>.<UDTF_name>(<table_alias>.<column_containing_ip_addresses>));

<input_db>: The input database contains IP addresses. It can be a table, view, database, or even a CTE. In the example, it is logs.
<db_alias>: Just an alias for the input database. In the example, we used l.
<ipinfo_database_name>: The IPinfo database name of the share. It is usually the "Standard Listing Name" we discussed in the "Views, UDTFs, and Database Names" section. But you can also change it to anything you want when purchasing the listing. In the example, it is just ipinfo.
ipinfo_schema: Usually, the value is public.
UDTF_name: The UDTF you want to use to enrich your log or IP database. In the example, we want to add location information to our IP logs, so we used the IP_LOCATION UDTF.
<column_containing_ip_addresses>: The column that contains the IP addresses in your input database. In the example, it is ip.

Another example of a UDTF, IP_HOSTED_DOMAINS UDTF uses the IP to Hosted Domains:

WITH sample_ips (ip) AS (
    SELECT *
    FROM (
        VALUES ('8.8.8.8'),
               ('198.35.26.98')
    )
)
SELECT *
FROM (
    SELECT ip
    FROM sample_ips -- sample log database
) logs
JOIN TABLE (ipinfo.public.ip_hosted_domains(logs.ip));

sample_ips_enriched_with_hosted_domains_udtf

Let's look at another UDTF, IP_PRIVACY, which comes with our IP to Privacy Detection database. We will be using our logs table again.

SELECT *
FROM logs l
JOIN TABLE(ipinfo.public.ip_privacy(l.ip));

identifying_vpn_hosting_tor_proxy_ip_addresses_with_ip_privacy_udtf

Please note that even though the logs table contains multiple IP addresses, we can only see a few rows here. That is because the code used performs an INNER JOIN.

IPinfo Lite - IPinfo's Free IP database on Snowflake Marketplace

With our open access IPinfo Lite's GET_IP_LITE you can get the country and asn information of IP addresses.

SELECT *
FROM logs l
JOIN TABLE(GET_IP_LITE(l.ip));

get_ip_lite_udtf

IPinfo Snowflake selected Queries (IP data query Cookbook)

In this section, we will cover some general queries that can be applied to any database product we have. These queries can be quite useful in threat intelligence operations and IP address research.

Preview the database

Preview the database by selecting the first 10 rows.

SELECT *
FROM ipinfo.public.IP_RIR_WHOIS
LIMIT 10;

We package each listing with samples of the database as well as samples of all our listings. Learn about it here: Snowflake listings include our data samples with 50 Rows

Looking up the IP data of an individual IP address.

You can look up individual IP addresses using UDTFs as well.

-- input IP address: 8.8.8.8
SELECT *
FROM TABLE(ipinfo.public.ip_location('8.8.8.8'));

Enriching IP addresses from multiple IPinfo DBs

If you have purchased multiple database products from us or our All-in-one database and want to perform multiple UDTF operations, as well as join them into a comprehensive table, you should run an OUTER JOIN operation.

SELECT
  l.ip, -- log IP address column
  carrier.name carrier, -- asn columns
  -- ...
  carrier.mnc mnc, 
  privacy.vpn vpn, -- privacy columns
  -- ...
  privacy.service privacy_service,
  ip_geo.city city, -- ip geolocation columns
  -- ...
  ip_geo.timezone timezone
FROM logs l
LEFT JOIN (
  SELECT *
  FROM logs l
  JOIN TABLE(ipinfo.public.ip_carrier(l.ip))
) carrier
ON l.ip = carrier.ip
LEFT JOIN (
  SELECT *
  FROM logs l
  JOIN TABLE(ipinfo.public.ip_privacy(l.ip))
) privacy
on l.ip = privacy.ip
LEFT JOIN (
  SELECT *
  FROM logs l
  JOIN TABLE(ipinfo.public.ip_location(l.ip))
) ip_geo
on l.ip = ip_geo.ip;

Converting Start IP & End IP to their CIDR/Network Range equivalent

You can convert the Start IP (start_ip) & End IP (end_ip) to their CIDR or network range equivalent using RANGE_TO_CIDR UDF. Please note that this will result in an array where there will be one or more items. Example:

SELECT
  ipinfo.public.range_to_cidr(start_ip, end_ip) as "cidr",
  *
FROM ipinfo.public.ip_carrier
LIMIT 10;

Describe the database

Use the DESCRIBE or DESC (Snowflake Documentation) command to get the schema and other information of the table.

DESCRIBE TABLE ipinfo.public.ip_rir_whois;

Generating random IP addresses

For testing purposes, you might need to generate IP addresses. In that case, you can use the included, RAND_IP UDF. The RAND_IP UDF can generate any number of IPv4 or IPv6 addresses.

The RAND_IP UDF takes two parameters:

Number of IP addresses: It could be any number of IP addresses.
IP Address type: For IPv4 it is "4", for IPv6 it is "6".

-- SELECT IPINFO.PUBLIC.RAND_IP(<number_of_ips>, <type_of_ip>)
SELECT IPINFO.PUBLIC.RAND_IP(3,6);

As the random IPs are generated in an array, if you want to convert the array into rows use the following query:

SELECT
  value as "ip"
FROM
  TABLE(FLATTEN(IPINFO.PUBLIC.RAND_IP(3,6)));

Please note a couple of things:

When you are generating random IP addresses it might include some bogon IP addresses and you will not find information regarding them when you run a UDTF command on them.
The vast majority of IPv6 addresses are unassigned, so there is no information associated with them. So, randomized IPv6 addresses cannot be effective for testing purposes.

IP to Geolocation: Nearest IP addresses

Using our IP to location database and the HAVERSINE function provided by Snowflake (documentation), we can retrieve information on the IP addresses closest to a given geographical coordinate.

-- Pass the input lat, lng as the first two arguments
-- to the Haversine function. (e.g. 51.1740, -1.8262)
SELECT
  HAVERSINE(51.1740, -1.8262, lat, lng) as distance,
  start_ip,
  end_ip,
  city,
  region,
  country
FROM ipinfo.public.ip_geolocation
ORDER BY distance
LIMIT 10;

IP to Geolocation: Get Continent

To obtain only the continent information of a single IP address, you can utilize the IP_CONTINENT UDF.

SELECT IPINFO.PUBLIC.IP_CONTINENT('8.8.8.8');

However, please note that this UDF only takes an IP address and returns the name of the continent by utilizing the IP_LOCATION database and the COUNTRIES complementary database. If you are already using the IP_LOCATION UDTF or you have to look up multiple IP addresses, you can join the output table to the COUNTRIES complementary database.

SELECT *
FROM (
  SELECT ip
  FROM logs -- sample log database
) logs
JOIN TABLE(ipinfo.public.ip_location(logs.ip)) l
LEFT JOIN ipinfo.public.countries c
WHERE l.country=c.cc;

IP to ASN: Number of ASNs per country

Get the number of ASNs in a country from the free IP to Country ASN database or the ASN database.

SELECT
  country_code,
  count(1) as ASNs
FROM ipinfo.public.lite
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

Similar queries can be used on all database products.

IPs That Might Be Static Residential Proxies (Seen >90% of the Time)

By using the Residential Proxy Detection database, you can discover IPs that may be static residential proxy IP addresses.

SELECT ip, service, percent_days_seen
FROM proxy_residential
WHERE CAST(percent_days_seen AS INTEGER) > 90
ORDER BY percent_days_seen DESC;

Get IP ranges of an ASN / Organization

You can obtain the IP ranges in CIDR format of one or more ASN with the following query.

-- Example asn: AS39927
-- Using the ASN database
SELECT
  flat_data.value as IP_RANGE
  FROM (
    SELECT ipinfo.public.range_to_cidr(start_ip, end_ip) as ip_range
        FROM ipinfo.public.ip_asn
        WHERE asn='AS39927'
    ) as_ips,
TABLE(FLATTEN(as_ips.ip_range)) flat_data

Please note that for identifying organizations, the domain field essentially acts like a primary key, as multinational organizations operate with different names across different countries.

IPinfo Lite database / IP to Geolocation database: Generate IP ranges by location

You can retrieve all the ranges for one or more specific countries, regions/states, or cities with the following query.

-- Example country: Nepal (NP)
-- Using the IP to Country ASN database
SELECT
  flat_data.value as IP_RANGE
  FROM (
    SELECT ipinfo.public.range_to_cidr(start_ip, end_ip) as ip_range
        FROM ipinfo.public.lite
        WHERE country_code='NP'
    ) nepal_ips,
TABLE(FLATTEN(nepal_ips.ip_range)) flat_data

IPinfo Community

In our IPinfo community, we have created a repository of all our Snowflake-related queries and technical documentation. Through our community, you can learn and explore the technical features of our Snowflake listings as well as ask and participate in discussions about IP data and IPinfo.

🔗 IPinfo Snowflake Community Documentation

Frequently Asked Questions (FAQ)

What is the update interval for the databases?

IPinfo provides data updates in three intervals - daily, weekly, and monthly. For the Snowflake listings:

Standard self service datasets are updated monthly, except for the IPinfo Lite data.
Custom data shares provide flexibility for customers to select their desired refresh frequency from the options available on each dataset.

Are UDTFs available through manual ingestion or secondary data softwares like dbt?

IPinfo's UDTFs are part of the marketplace listings, but they are not available for manual data ingestion of IPinfo's data downloads to Snowflake. If Snowflake is being used through dbt or any intermediate software solution, the UDTFs might also not be available. In that case, the user must hard code the UDTF code. Instructions for hard coding UDTFs are available here.

We highly recommend that users do not write their own custom query codes unless they have a solid grasp of IP lookup mechanisms. The UDTFs were developed with expert consultation between IPinfo's engineers and Snowflake.

Is IPv6 data available?

Yes. All IPinfo's IP data downloads include IPv4 and IPv6 data in a single database.

Can I enrich "n" number of IP address?

If it is under a hundred million IP addresses, you should be good with UDTFs. IPv6 enrichment will be a bit slow but it will still be performant. If you are approaching hundreds of millions or billions of unique IP addresses, reach out to us. We will discuss a solution with you.

Can I request custom data shares?

Yes, IPinfo has a few custom datasets that are only available upon request. Please reach out to us at snowflake@ipinfo.io, and we will set up a private share.

Additional Support

Resources from IPinfo and Snowflake

To enable our customers to learn and effectively use our data, we regularly publish articles and resources that focus on using our data products on the Snowflake platform.

Also, please feel free to check our documentation for individual dataset documentation.

Besides the technical documentation, we highly recommend users check out our articles on IPinfo's Snowflake integration. If you have any queries regarding our Snowflake partnership, reach out to us at snowflake@ipinfo.io.

Snowflake has published many articles and resources to enable IPinfo customers to effectively achieve their data goals.

Additionally, you should also check out the Snowflake documentation:

Usage Conditions

IPinfo's data contained within the standard self-service listings in the Snowflake Marketplace is subject to an internal use license. It may solely be utilized within business operations, tools, applications, or services intended exclusively for internal staff members.

1. Limited License: The data is licensed for internal use only. This means the data can only be used in business operations, tools, applications or services where the end users are internal members of your staff. For the avoidance of doubt, usage under this license strictly excludes any of the following scenarios (note that this is not an exhaustive list):

Reselling, repackaging or republishing the data
Sharing the data with any third party
Integrating the data into any customer (&/or partner) facing product or service, whether paid or free, including managed services solutions

2. Breach and Termination: Any violation of these terms constitutes a breach and may result in the immediate termination of your access to the data at IPinfo's sole discretion.

3. Data Ownership: IPinfo retains all ownership rights to the data. You are not granted any ownership rights or intellectual property rights by accessing or using the data.

4. Compliance: You are responsible for ensuring that your use of the data complies with all applicable laws and regulations.

5. Modifications: We reserve the right to modify these terms and conditions at any time. Your continued use of the data following any changes constitutes your acceptance of the revised terms.

If you have any questions, feedback, guidance or support queries, feel free to reach out to us: snowflake@ipinfo.io

Official client Libraries

Python

Django

Java

Spring

C#

NodeJS

ExpressJS

PHP

Laravel

Go

Ruby

Ruby on Rails

Rust

Perl

Erlang

Swift

IPinfo on Snowflake Marketplace

IPinfo on the Snowflake Marketplace

Database Operations

Views, UDTFs and Database Names

Accessing Our Daily Updated Databases on Snowflake Marketplace

Joining our database to your database using UDTFs

IPinfo Lite - IPinfo's Free IP database on Snowflake Marketplace

IPinfo Snowflake selected Queries (IP data query Cookbook)

IPinfo Community

Frequently Asked Questions (FAQ)

Additional Support

IP to Geolocation

Privacy Detection

ASN

IP to Mobile Carrier

IP Whois

IP Ranges

IP to Company

Hosted Domains

Abuse Contact

Residental Proxy

Official client Libraries

Python

Django

Java

Spring

C#

NodeJS

ExpressJS

PHP

Laravel

Go

Ruby

Ruby on Rails

Rust

Perl

Erlang

Swift

IPinfo on Snowflake Marketplace

IPinfo on the Snowflake Marketplace

Database Operations

Views, UDTFs and Database Names

Accessing Our Daily Updated Databases on Snowflake Marketplace

Joining our database to your database using UDTFs

IPinfo Lite - IPinfo's Free IP database on Snowflake Marketplace

IPinfo Snowflake selected Queries (IP data query Cookbook)

IPinfo Community

Frequently Asked Questions (FAQ)

Additional Support