{"id":22545,"date":"2021-11-04T14:13:52","date_gmt":"2021-11-04T14:13:52","guid":{"rendered":"http:\/\/identifying_countries_by_ip_address_in_columnar_databases_through_sql"},"modified":"2022-03-28T17:10:45","modified_gmt":"2022-03-28T17:10:45","slug":"identifying_countries_by_ip_address_in_columnar_databases_through_sql","status":"publish","type":"post","link":"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql","title":{"rendered":"Identifying Countries by IP Address in Columnar Databases Through SQL"},"content":{"rendered":"

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.<\/p>\n

A glance at any General Data Protection Regulation (GDPR) article would reveal that\u2014unlike Android device IDs (AID), through which users can reset their web address\u2014keeping 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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

The Mapping Database<\/h2>\n

Most providers offer the following logical dataset to map IP to country with three columns: IPNumFrom<\/code>, IPNumTo<\/code>, and CountryCode<\/code>. About 200K rows would take up 2.5 MB of disk space. IPNum<\/code> is arrived at through a simple calculation done from the IP. For example, 1.2.3.4 equals 16,909,060 = 1256<\/em>256256+2<\/em>256256+3<\/em>256+4, hence 16,909,060 modulus 256 = 4.<\/p>\n

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

SELECT ip2country.country_code,COUNT(1) requests\nFROM (SELECT CAST(SPLIT_PART(ip,'.',1) AS INT)*256*256*256\n+CAST(SPLIT_PART(ip,'.',2) AS INT)*256*256\n+CAST(SPLIT_PART(ip,'.',3) AS INT)*256 AS ipnum\nFROM log\nLIMIT 100) log\nJOIN ip2country ON log.ipnum BETWEEN ip2country.ipnum_from AND ip2country.ipnum_to\nGROUP BY 1 ORDER BY 2 DESC LIMIT 100\n<\/code><\/pre>\n
Study 2:<\/strong> If you perform the following query, which converts through the SELECT SUBSELECT BETWEEN<\/code> 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:<\/p>\n
SELECT (SELECT ip2country.country_code\nFROM ip2country\nWHERE log.ipnum BETWEEN ip2country.ipnum_from AND ip2country.ipnum_to) AS country_code\n,COUNT(1) requests\nFROM (SELECT CAST(SPLIT_PART(ip,'.',1 AS INT)*256*256*256\n+CAST(SPLIT_PART(ip,'.',2) AS INT)*256*256\n+CAST(SPLIT_PART(ip,'.',3) AS INT)*256 AS ipnum\nFROM log\nLIMIT 10000) log\nGROUP BY 1 ORDER BY 2 DESC LIMIT 100\n<\/code><\/pre>\n
    \n
  • The 1-5K and 10-25K numbers are rough estimates only. Depending on the database performance, the sizes might vary.<\/li>\n
  • 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<\/code> or the BETWEEN<\/code> clause as CARTESIAN JOIN<\/code> to FULL SCAN<\/code> on each record\u2019s dataset.<\/li>\n
  • SQL databases usually perform JOIN<\/code> tasks more effectively with an exact EQUAL<\/code> (=) rather than a BETWEEN<\/code> condition. At times, solutions like spatial indexing can better imitate the BETWEEN<\/code> logic.<\/li>\n
  • 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.<\/li>\n<\/ul>\n
    Transformation of Range-Value Datasets to Key-Value Datasets<\/h2>\n
    So, here\u2019s 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\u2014in theory\u2014a 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.<\/p>\n
    Instead, we did the following:<\/p>\n
      \n
    1. Reverse-engineer the range-value dataset into a key-value dataset.<\/li>\n
    2. Keep the key-value dataset to the lowest octets\u207d\u00b9\u207e as possible, up to a maximum of three octets.\n\u207d\u00b9\u207eOctet is the four IP components that run between 0-255.<\/li>\n<\/ol>\n
      In other words:<\/p>\n
        \n
      • In case 100.0.0.0-100.255.255.255 matches the country U.S.A., specify only<\/strong> 100.0.0.0-US in the first octet dataset.<\/li>\n
      • Since you don\u2019t want to receive four-octet IPs any more, discard the four-level octet altogether.<\/li>\n
      • If feasible, keep the three-octet datasets apart for higher performance.<\/li>\n
      • Update the datasets monthly or quarterly.<\/li>\n<\/ul>\n
        Conversion Results<\/h2>\n
        Before doing the conversion, let\u2019s have a look at the results.<\/p>\n
        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.<\/p>\n
        Study 3:<\/strong> As expected, the following query performs much better on a columnar database:<\/p>\n
        SELECT COALESCE(ipnum12country.country_code, ipnum22country.country_code, ipnum32country.country_code) AS country_code\n,COUNT(1) requests\nFROM (SELECT CAST(SPLIT_PART(ip,'.',1) AS INT) *256*256*256 AS ipnum1\n,CAST(SPLIT_PART(ip,'.',2) AS INT) *256*256 AS ipnum2\n,CAST(SPLIT_PART(ip,'.',3) AS INT) *256 AS ipnum3\nFROM log\nLIMIT 1000000) log\nLEFT JOIN ipnum12country ON ipnum12country.ipnum1 = log.ipnum1\nLEFT JOIN ipnum22country ON ipnum12country.country_code IS NULL AND ipnum22country.ipnum2 = log.ipnum2\nLEFT JOIN ipnum32country ON ipnum12country.country_code IS NULL AND ipnum22country.country_code IS NULL AND ipnum32country.ipnum3 = log.ipnum3\nGROUP BY 1 ORDER BY 2 DESC LIMIT 100\n<\/code><\/pre>\n
        Before, the JOIN<\/code> 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.<\/p>\n
        You might find the data below of interest.<\/p>\n
        Note:<\/strong> 
        The data and results cited in this article were collected from Cloudinary\u2019s open web traffic and the IP histogram only<\/strong>.<\/p><\/div>\n
        Size and Usage by Octet<\/strong><\/p>\n\n   \n      \n         \n      \n         
        Octet<\/th>\n         Total Size of the Dataset<\/th>\n         Total No. of Keys in the Dataset<\/th>\n         No. of Used Keys Against the Dataset<\/th>\n         No. of Distinctly Matching IPs<\/th>\n         No. of Processed Requests<\/th>\n      <\/tr>\n   <\/thead>\n   
        1<\/th>\n         1 KB<\/td>\n         52<\/td>\n         8<\/th>\n         11,505<\/td>\n         44,111<\/td>\n      <\/tr>\n         2<\/td>\n         180 KB<\/td>\n         42,801<\/td>\n         25,941<\/td>\n         11,231,825<\/td>\n         64,582,450<\/td>\n      <\/tr>\n         3<\/td>\n         10 MB<\/td>\n         2,411,552<\/td>\n         282,110<\/td>\n         1,528,017<\/td>\n         54,303,033<\/td>\n      <\/tr>\n        <\/tbody>\n<\/table>\n
        Total Percentage of Usage<\/strong><\/p>\n\n   \n      \n         \n      \n         
        Octet<\/th>\n         % of the Distinct IPs<\/th>\n         % of Requests<\/th>\n      <\/tr>\n   <\/thead>\n   
        1<\/th>\n         0.09%<\/td>\n         0.04%<\/td>\n      <\/tr>\n         2<\/td>\n         87.94%<\/td>\n         54.30%<\/td>\n      <\/tr>\n         3<\/td>\n         11.96%<\/td>\n         45.66%<\/td>\n      <\/tr>\n        <\/tbody>\n<\/table>\n
        Estimates of Key Results<\/strong><\/p>\n\n   \n      \n         \n      \n         
        Octet<\/th>\n         No. of Requests Per Dataset Record<\/th>\n         No. of Distinct IPs Per Record<\/th>\n      <\/tr>\n   <\/thead>\n   
        1<\/th>\n         5,513.88<\/td>\n         1,438.13<\/td>\n      <\/tr>\n         2<\/td>\n         2,489.59<\/td>\n         432.98<\/td>\n      <\/tr>\n         3<\/td>\n         192.49<\/td>\n         5.42<\/td>\n      <\/tr>\n        <\/tbody>\n<\/table>\n
        Here are the conclusions:<\/p>\n
          \n
        • 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\u2014a much less efficient approach.<\/li>\n
        • 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.<\/li>\n
        • A split of the dataset results shows that 55% of the requests processed  did not<\/strong> have to explore the major 10-MB, three-octet dataset.<\/li>\n<\/ul>\n
          In response to a request from our business partners, we did a similar study on the conversion of the U.S.A. states:<\/p>\n\n   \n      \n         \n      \n         
          Octet<\/th>\n         Total Size of the Dataset<\/th>\n         No. of Used Keys Against the Dataset<\/th>\n         No. of Used Keys Against the Dataset<\/th>\n         No. of Distinctly Matching IPs<\/th>\n         No. of Processed Requests<\/th>\n      <\/tr>\n   <\/thead>\n   
          1<\/th>\n         1 KB<\/td>\n         9<\/td>\n         3<\/th>\n         285<\/td>\n         1,056<\/td>\n      <\/tr>\n         2<\/td>\n         50 KB<\/td>\n         10,910<\/td>\n         2,404<\/td>\n         523,086<\/td>\n         3,582,907<\/td>\n      <\/tr>\n         3<\/td>\n         10 MB<\/td>\n         2,729,087<\/td>\n         661,224<\/td>\n         3,926,536<\/td>\n         56,661,971<\/td>\n      <\/tr>\n        <\/tbody>\n<\/table>\n
          Note:<\/strong> 
            \n
          • Unlike before, from a performance perspective, the conversion for over 95% of the traffic came from the three-octet dataset. That\u2019s a less efficient process compared to the country conversion.<\/li>\n
          • We realize that the states in the U.S.A cannot be properly converted with only the first three octets of the IP.<\/li>\n
          • The range-value dataset for the U.S.A. is usually derived from the IP2City<\/code> datasets.<\/li>\n<\/ul><\/div>\n
            Conversion Steps<\/h2>\n
            To reverse-engineer the range-value logic to key-value logic, follow these steps:<\/p>\n
              \n
            1. \n
              Convert the IPNum<\/code> range to separate ranges per octet and extract the exact octet:<\/p>\n
              From IPNumFrom, IPNumTo to Octet1From, Octet2From, Octet3From, Octet4From, and Octet1To, Octet2To, Octet3To, Octet4To.\n<\/code><\/pre>\n<\/li>\n
            2. \n
              Create Octet12Country<\/code>:\n2.1. UNNEST<\/code> the values between Octet1From<\/code> and Octet1To<\/code>.\n2.2. GROUP BY Octet1 HAVING COUNT(DISTINCT country_code) = 1<\/code><\/p>\n<\/li>\n
            3. \n
              Create Octet22Country<\/code>:\n3.1. UNNEST<\/code> the values between Octet1From<\/code> and Octet1To<\/code>.\n3.2. IGNORE<\/code> the values in Octet12Country<\/code>.\n3.3. Override the Octet2<\/code> ranges in the gaps:\n3.3.1. If Octet1<\/code> is greater than Octet1From<\/code>, set Octet2From<\/code> to 0<\/code>.\n3.3.2. If Octet1<\/code> is smaller than Octet1To<\/code>, set Octet2To<\/code> to 255<\/code>.\n3.4. UNNEST<\/code> the values between Octet2From<\/code> and Octet2To<\/code> into Octet2<\/code>.\n3.5. GROUP BY Octet1,Octet2 HAVING COUNT(DISTINCT country_code) = 1<\/code><\/p>\n<\/li>\n
            4. \n
              Create Octet32Country<\/code>:\n4.1. UNNEST<\/code> the values between Octet1From<\/code> and Octet1To<\/code>.\n4.2. IGNORE<\/code> the values in Octet12Country<\/code>.\n4.3. Override the Octet2<\/code> ranges in the gaps:\n4.3.1. If Octet1<\/code> is greater than Octet1From<\/code>, set Octet2From<\/code> to 0<\/code>.\n4.3.2. If Octet1<\/code> is smaller thanOctet1To<\/code>, set Octet2To<\/code> to 255<\/code>.\n4.4. UNNEST<\/code> the values between Octet2From<\/code> and Octet2To<\/code> into Octet2<\/code>.\n4.5. IGNORE<\/code> the values in Octet22Country<\/code>.\n4.6. Override the Octet3<\/code> ranges in the gaps:\n4.6.1. If Octet2<\/code> is bigger than Octet2From<\/code>, set Octet3From<\/code> to 0<\/code>.\n4.6.2. If Octet2<\/code> is smaller than Octet2To<\/code>, set Octet3To<\/code> to 255<\/code>.\n4.7. UNNEST<\/code> the values between Octet3From<\/code> and Octet3To<\/code> into Octet3<\/code>.\n4.8. GROUP BY Octet1,Octet2,Octet3 HAVING COUNT(DISTINCT country_code) = 1<\/code><\/p>\n<\/li>\n
            5. \n
              Convert Octet12Country<\/code>, Octet22Country<\/code>, and Octet32Country<\/code> into IPNum12Country<\/code>, IPNum22Country<\/code>, and IPNum32Country<\/code>.<\/p>\n<\/li>\n
            6. \n
              Update the results monthly or quarterly.<\/p>\n<\/li>\n<\/ol>\n
              Note:<\/strong> 
              This logic can be performed in multiple ways. The original dataset for the approach described above weighs only 20 MB, which makes it easier to debug floating octet values.<\/p><\/div>\n
              More Thoughts<\/h2>\n
              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!<\/p>\n<\/div>","protected":false},"excerpt":{"rendered":"","protected":false},"author":41,"featured_media":22546,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_cloudinary_featured_overwrite":false,"footnotes":""},"categories":[1],"tags":[352],"class_list":["post-22545","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized","tag-data"],"acf":[],"yoast_head":"\nIdentifying Countries by IP Address in Columnar Databases Through SQL<\/title>\n<meta name=\"description\" content=\"Analyze Cloudinary's data on its web traffic for business-critical data, e.g., the country of origin of web requests and the number of web sessions.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Identifying Countries by IP Address in Columnar Databases Through SQL\" \/>\n<meta property=\"og:description\" content=\"Analyze Cloudinary's data on its web traffic for business-critical data, e.g., the country of origin of web requests and the number of web sessions.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql\" \/>\n<meta property=\"og:site_name\" content=\"Cloudinary Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-11-04T14:13:52+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-03-28T17:10:45+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/res.cloudinary.com\/cloudinary-marketing\/images\/v1645223444\/website-2021\/blog\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1-png?_i=AA\" \/>\n\t<meta property=\"og:image:width\" content=\"1540\" \/>\n\t<meta property=\"og:image:height\" content=\"847\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"NewsArticle\",\"@id\":\"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql#article\",\"isPartOf\":{\"@id\":\"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql\"},\"author\":{\"name\":\"\",\"@id\":\"\"},\"headline\":\"Identifying Countries by IP Address in Columnar Databases Through SQL\",\"datePublished\":\"2021-11-04T14:13:52+00:00\",\"dateModified\":\"2022-03-28T17:10:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql\"},\"wordCount\":10,\"publisher\":{\"@id\":\"https:\/\/cloudinary.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql#primaryimage\"},\"thumbnailUrl\":\"https:\/\/res.cloudinary.com\/cloudinary-marketing\/images\/f_auto,q_auto\/v1649725918\/Web_Assets\/blog\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1.png?_i=AA\",\"keywords\":[\"Data\"],\"inLanguage\":\"en-US\",\"copyrightYear\":\"2021\",\"copyrightHolder\":{\"@id\":\"https:\/\/cloudinary.com\/#organization\"}},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql\",\"url\":\"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql\",\"name\":\"Identifying Countries by IP Address in Columnar Databases Through SQL\",\"isPartOf\":{\"@id\":\"https:\/\/cloudinary.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql#primaryimage\"},\"image\":{\"@id\":\"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql#primaryimage\"},\"thumbnailUrl\":\"https:\/\/res.cloudinary.com\/cloudinary-marketing\/images\/f_auto,q_auto\/v1649725918\/Web_Assets\/blog\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1.png?_i=AA\",\"datePublished\":\"2021-11-04T14:13:52+00:00\",\"dateModified\":\"2022-03-28T17:10:45+00:00\",\"description\":\"Analyze Cloudinary's data on its web traffic for business-critical data, e.g., the country of origin of web requests and the number of web sessions.\",\"breadcrumb\":{\"@id\":\"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql#primaryimage\",\"url\":\"https:\/\/res.cloudinary.com\/cloudinary-marketing\/images\/f_auto,q_auto\/v1649725918\/Web_Assets\/blog\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1.png?_i=AA\",\"contentUrl\":\"https:\/\/res.cloudinary.com\/cloudinary-marketing\/images\/f_auto,q_auto\/v1649725918\/Web_Assets\/blog\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1.png?_i=AA\",\"width\":1540,\"height\":847},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/cloudinary.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Identifying Countries by IP Address in Columnar Databases Through SQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/cloudinary.com\/blog\/#website\",\"url\":\"https:\/\/cloudinary.com\/blog\/\",\"name\":\"Cloudinary Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/cloudinary.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/cloudinary.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/cloudinary.com\/blog\/#organization\",\"name\":\"Cloudinary Blog\",\"url\":\"https:\/\/cloudinary.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/cloudinary.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/res.cloudinary.com\/cloudinary-marketing\/images\/f_auto,q_auto\/v1649718331\/Web_Assets\/blog\/cloudinary_logo_for_white_bg_1937437aa7_19374666c7_193742f877\/cloudinary_logo_for_white_bg_1937437aa7_19374666c7_193742f877.png?_i=AA\",\"contentUrl\":\"https:\/\/res.cloudinary.com\/cloudinary-marketing\/images\/f_auto,q_auto\/v1649718331\/Web_Assets\/blog\/cloudinary_logo_for_white_bg_1937437aa7_19374666c7_193742f877\/cloudinary_logo_for_white_bg_1937437aa7_19374666c7_193742f877.png?_i=AA\",\"width\":312,\"height\":60,\"caption\":\"Cloudinary Blog\"},\"image\":{\"@id\":\"https:\/\/cloudinary.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Identifying Countries by IP Address in Columnar Databases Through SQL","description":"Analyze Cloudinary's data on its web traffic for business-critical data, e.g., the country of origin of web requests and the number of web sessions.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql","og_locale":"en_US","og_type":"article","og_title":"Identifying Countries by IP Address in Columnar Databases Through SQL","og_description":"Analyze Cloudinary's data on its web traffic for business-critical data, e.g., the country of origin of web requests and the number of web sessions.","og_url":"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql","og_site_name":"Cloudinary Blog","article_published_time":"2021-11-04T14:13:52+00:00","article_modified_time":"2022-03-28T17:10:45+00:00","og_image":[{"width":1540,"height":847,"url":"https:\/\/res.cloudinary.com\/cloudinary-marketing\/images\/v1645223444\/website-2021\/blog\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1-png?_i=AA","type":"image\/png"}],"twitter_card":"summary_large_image","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"NewsArticle","@id":"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql#article","isPartOf":{"@id":"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql"},"author":{"name":"","@id":""},"headline":"Identifying Countries by IP Address in Columnar Databases Through SQL","datePublished":"2021-11-04T14:13:52+00:00","dateModified":"2022-03-28T17:10:45+00:00","mainEntityOfPage":{"@id":"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql"},"wordCount":10,"publisher":{"@id":"https:\/\/cloudinary.com\/blog\/#organization"},"image":{"@id":"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql#primaryimage"},"thumbnailUrl":"https:\/\/res.cloudinary.com\/cloudinary-marketing\/images\/f_auto,q_auto\/v1649725918\/Web_Assets\/blog\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1.png?_i=AA","keywords":["Data"],"inLanguage":"en-US","copyrightYear":"2021","copyrightHolder":{"@id":"https:\/\/cloudinary.com\/#organization"}},{"@type":"WebPage","@id":"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql","url":"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql","name":"Identifying Countries by IP Address in Columnar Databases Through SQL","isPartOf":{"@id":"https:\/\/cloudinary.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql#primaryimage"},"image":{"@id":"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql#primaryimage"},"thumbnailUrl":"https:\/\/res.cloudinary.com\/cloudinary-marketing\/images\/f_auto,q_auto\/v1649725918\/Web_Assets\/blog\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1.png?_i=AA","datePublished":"2021-11-04T14:13:52+00:00","dateModified":"2022-03-28T17:10:45+00:00","description":"Analyze Cloudinary's data on its web traffic for business-critical data, e.g., the country of origin of web requests and the number of web sessions.","breadcrumb":{"@id":"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql#primaryimage","url":"https:\/\/res.cloudinary.com\/cloudinary-marketing\/images\/f_auto,q_auto\/v1649725918\/Web_Assets\/blog\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1.png?_i=AA","contentUrl":"https:\/\/res.cloudinary.com\/cloudinary-marketing\/images\/f_auto,q_auto\/v1649725918\/Web_Assets\/blog\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1.png?_i=AA","width":1540,"height":847},{"@type":"BreadcrumbList","@id":"https:\/\/cloudinary.com\/blog\/identifying_countries_by_ip_address_in_columnar_databases_through_sql#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/cloudinary.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Identifying Countries by IP Address in Columnar Databases Through SQL"}]},{"@type":"WebSite","@id":"https:\/\/cloudinary.com\/blog\/#website","url":"https:\/\/cloudinary.com\/blog\/","name":"Cloudinary Blog","description":"","publisher":{"@id":"https:\/\/cloudinary.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/cloudinary.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/cloudinary.com\/blog\/#organization","name":"Cloudinary Blog","url":"https:\/\/cloudinary.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/cloudinary.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/res.cloudinary.com\/cloudinary-marketing\/images\/f_auto,q_auto\/v1649718331\/Web_Assets\/blog\/cloudinary_logo_for_white_bg_1937437aa7_19374666c7_193742f877\/cloudinary_logo_for_white_bg_1937437aa7_19374666c7_193742f877.png?_i=AA","contentUrl":"https:\/\/res.cloudinary.com\/cloudinary-marketing\/images\/f_auto,q_auto\/v1649718331\/Web_Assets\/blog\/cloudinary_logo_for_white_bg_1937437aa7_19374666c7_193742f877\/cloudinary_logo_for_white_bg_1937437aa7_19374666c7_193742f877.png?_i=AA","width":312,"height":60,"caption":"Cloudinary Blog"},"image":{"@id":"https:\/\/cloudinary.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":""}]}},"jetpack_featured_media_url":"https:\/\/res.cloudinary.com\/cloudinary-marketing\/images\/f_auto,q_auto\/v1649725918\/Web_Assets\/blog\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1\/Cld_Blog_FeatImg_Oct2k21_Identifying-countries-by-IP_2-1.png?_i=AA","_links":{"self":[{"href":"https:\/\/cloudinary.com\/blog\/wp-json\/wp\/v2\/posts\/22545","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cloudinary.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cloudinary.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cloudinary.com\/blog\/wp-json\/wp\/v2\/users\/41"}],"replies":[{"embeddable":true,"href":"https:\/\/cloudinary.com\/blog\/wp-json\/wp\/v2\/comments?post=22545"}],"version-history":[{"count":1,"href":"https:\/\/cloudinary.com\/blog\/wp-json\/wp\/v2\/posts\/22545\/revisions"}],"predecessor-version":[{"id":23637,"href":"https:\/\/cloudinary.com\/blog\/wp-json\/wp\/v2\/posts\/22545\/revisions\/23637"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/cloudinary.com\/blog\/wp-json\/wp\/v2\/media\/22546"}],"wp:attachment":[{"href":"https:\/\/cloudinary.com\/blog\/wp-json\/wp\/v2\/media?parent=22545"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cloudinary.com\/blog\/wp-json\/wp\/v2\/categories?post=22545"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cloudinary.com\/blog\/wp-json\/wp\/v2\/tags?post=22545"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}