Identifying Countries by IP Address in Columnar Databases Through SQL

Cloudinary reaps a myriad of open web traffic, from ad networks to e-commerce sites. Our Data Science team is dedicated to analyzing the data for use internally and externally.

A glance at any General Data Protection Regulation (GDPR) article would reveal that—unlike Android device IDs (AID), through which users can reset their web address—keeping user identifiers, such as Internal Protocol (IP) and Media Access Control (MAC) addresses, as well as International Mobile Equipment Identity (IMEI), violates privacy. As a solution, you can discard all privacy identifications or make them visible to users for reset.

Even though IP addresses are less user friendly, the data we can extract from them, e.g., popular locations (country, state) or the number of distinct sessions (without the help of cookies), is crucial to our business success. Until recently, to calculate such dimensions, a geo provider or content delivery network (CDN) required the full IP address for processing. For tuning reasons, geo providers offer to have companies cache the key-value results internally or grant companies download privilege of the main logic-dataset.

Recently, while examining Cloudinary access logs for data analysis, we discovered that we received approximately 12.5 million different IP addresses worldwide from a sample of over 118 million requests. Over 99% of those addresses are IPv4s because IPv6 is not exactly ubiquitous yet. This post describes how to pinpoint key details, such as the country of origin, from a columnar database through standard SQL.

Most providers offer the following logical dataset to map IP to country with three columns: IPNumFrom, IPNumTo, and CountryCode. About 200K rows would take up 2.5 MB of disk space. IPNum is arrived at through a simple calculation done from the IP. For example, 1.2.3.4 equals 16,909,060 = 1256256256+2256256+3256+4, hence 16,909,060 modulus 256 = 4.

Study 1: If you perform the following query, which converts through JOIN ON BETWEEN on less than 1-5K records in a regular Relational database management system (RDBMS), it might work just fine:

SELECT ip2country.country_code,COUNT(1) requests
FROM (SELECT CAST(SPLIT_PART(ip,'.',1) AS INT)*256*256*256
+CAST(SPLIT_PART(ip,'.',2) AS INT)*256*256
+CAST(SPLIT_PART(ip,'.',3) AS INT)*256 AS ipnum
FROM log
LIMIT 100) log
JOIN ip2country ON log.ipnum BETWEEN ip2country.ipnum_from AND ip2country.ipnum_to
GROUP BY 1 ORDER BY 2 DESC LIMIT 100

Study 2: If you perform the following query, which converts through the SELECT SUBSELECT BETWEEN level, it might work more slowly on a few records but faster on more of them, i.e., up to a large volume of approximately 10-25K rows:

SELECT (SELECT ip2country.country_code
FROM ip2country
WHERE log.ipnum BETWEEN ip2country.ipnum_from AND ip2country.ipnum_to) AS country_code
,COUNT(1) requests
FROM (SELECT CAST(SPLIT_PART(ip,'.',1 AS INT)*256*256*256
+CAST(SPLIT_PART(ip,'.',2) AS INT)*256*256
+CAST(SPLIT_PART(ip,'.',3) AS INT)*256 AS ipnum
FROM log
LIMIT 10000) log
GROUP BY 1 ORDER BY 2 DESC LIMIT 100

• The 1-5K and 10-25K numbers are rough estimates only. Depending on the database performance, the sizes might vary.
• In both cases, the real problem occurs when you increase the number of records to millions, after which the query performance will dramatically decline according to how well the database optimizer sees the JOIN or the BETWEEN clause as CARTESIAN JOIN to FULL SCAN on each record’s dataset.
• SQL databases usually perform JOIN tasks more effectively with an exact EQUAL (=) rather than a BETWEEN condition. At times, solutions like spatial indexing can better imitate the BETWEEN logic.
• Particularly in columnar databases, those types of solutions might fail altogether since we usually process over 100 million records with more than a million distinct values.

So, here’s the ultimate question: How do you transform a range-value dataset to a key-value dataset? One option is to take the data and cache the results. For the above example, you would need—in theory—a 12.5 million key-value cache versus a 200K-range cache, which might require a load of bandwidth to join as the dataset dynamically grows.

Instead, we did the following:

1. Reverse-engineer the range-value dataset into a key-value dataset.
2. Keep the key-value dataset to the lowest octets⁽¹⁾ as possible, up to a maximum of three octets. ⁽¹⁾Octet is the four IP components that run between 0-255.

In other words:

• In case 100.0.0.0-100.255.255.255 matches the country U.S.A., specify only 100.0.0.0-US in the first octet dataset.
• Since you don’t want to receive four-octet IPs any more, discard the four-level octet altogether.
• If feasible, keep the three-octet datasets apart for higher performance.
• Update the datasets monthly or quarterly.

Before doing the conversion, let’s have a look at the results.

In converting the 200K-range dataset to three octet datasets, one for each octet, you can see that, since each octet presents a different size of data (50, 50K, and 2.5MM rows), keeping them apart might be a smart move. Moreover, if a preceding dataset has already given you the result, you can skip the step of joining datasets.

Study 3: As expected, the following query performs much better on a columnar database:

SELECT COALESCE(ipnum12country.country_code, ipnum22country.country_code, ipnum32country.country_code) AS country_code
,COUNT(1) requests
FROM (SELECT CAST(SPLIT_PART(ip,'.',1) AS INT) *256*256*256 AS ipnum1
,CAST(SPLIT_PART(ip,'.',2) AS INT) *256*256 AS ipnum2
,CAST(SPLIT_PART(ip,'.',3) AS INT) *256 AS ipnum3
FROM log
LIMIT 1000000) log
LEFT JOIN ipnum12country ON ipnum12country.ipnum1 = log.ipnum1
LEFT JOIN ipnum22country ON ipnum12country.country_code IS NULL AND ipnum22country.ipnum2 = log.ipnum2
LEFT JOIN ipnum32country ON ipnum12country.country_code IS NULL AND ipnum22country.country_code IS NULL AND ipnum32country.ipnum3 = log.ipnum3
GROUP BY 1 ORDER BY 2 DESC LIMIT 100

Before, the JOIN task took up over 80% of the total execution time. Now it takes less than 5%, with the remaining tasks being the normal ones: the actual data scan, sort, and aggregation.

You might find the data below of interest.

Size and Usage by Octet

Total Percentage of Usage

Estimates of Key Results

Here are the conclusions:

• The first two octets uncover 88% of the countries by IP address. Using only a three-octet dataset would increase the size from 10 MB to at least 60 MB—a much less efficient approach.
• The last three-octet dataset represents only 12% of the IP addresses, where half (45%) of the traffic are derived from there. That means that about three octet keys are repeated more often, hence generating more traffic.
• A split of the dataset results shows that 55% of the requests processed did not have to explore the major 10-MB, three-octet dataset.

In response to a request from our business partners, we did a similar study on the conversion of the U.S.A. states:

To reverse-engineer the range-value logic to key-value logic, follow these steps:

1. Convert the IPNum range to separate ranges per octet and extract the exact octet:

   From IPNumFrom, IPNumTo to Octet1From, Octet2From, Octet3From, Octet4From, and Octet1To, Octet2To, Octet3To, Octet4To.
   

2. Create Octet12Country: 2.1. UNNEST the values between Octet1From and Octet1To. 2.2. GROUP BY Octet1 HAVING COUNT(DISTINCT country_code) = 1

3. Create Octet22Country: 3.1. UNNEST the values between Octet1From and Octet1To. 3.2. IGNORE the values in Octet12Country. 3.3. Override the Octet2 ranges in the gaps: 3.3.1. If Octet1 is greater than Octet1From, set Octet2From to 0. 3.3.2. If Octet1 is smaller than Octet1To, set Octet2To to 255. 3.4. UNNEST the values between Octet2From and Octet2To into Octet2. 3.5. GROUP BY Octet1,Octet2 HAVING COUNT(DISTINCT country_code) = 1

4. Create Octet32Country: 4.1. UNNEST the values between Octet1From and Octet1To. 4.2. IGNORE the values in Octet12Country. 4.3. Override the Octet2 ranges in the gaps: 4.3.1. If Octet1 is greater than Octet1From, set Octet2From to 0. 4.3.2. If Octet1 is smaller thanOctet1To, set Octet2To to 255. 4.4. UNNEST the values between Octet2From and Octet2To into Octet2. 4.5. IGNORE the values in Octet22Country. 4.6. Override the Octet3 ranges in the gaps: 4.6.1. If Octet2 is bigger than Octet2From, set Octet3From to 0. 4.6.2. If Octet2 is smaller than Octet2To, set Octet3To to 255. 4.7. UNNEST the values between Octet3From and Octet3To into Octet3. 4.8. GROUP BY Octet1,Octet2,Octet3 HAVING COUNT(DISTINCT country_code) = 1

5. Convert Octet12Country, Octet22Country, and Octet32Country into IPNum12Country, IPNum22Country, and IPNum32Country.

6. Update the results monthly or quarterly.

We conducted this research to find out how well we could enrich data on the fly. It might often seem easier to adopt out-of-the-box solutions, but every company handles its data differently, from IP to user agent and URL. Even though it takes time and effort to cleanse, format, or tune the usage algorithm, each and every attribute promises to offer metadata-enrichment opportunities that are just waiting out there. Have fun!