# Why do the numbers of columns not match between the CSV/JSON and MMDB files? **URL:** \ **Category:** Database Downloads\ **Tags:** database, mmdb\ **Created:** [February 28, 2024, 7:33pm UTC](https://community.ipinfo.io/t/why-do-the-numbers-of-columns-not-match-between-the-csv-json-and-mmdb-files/5531 "2024-02-28T19:33:33Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Abdullah](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.ipinfo.io/abdullah/32/4973_2.png) [@Abdullah](https://community.ipinfo.io/u/Abdullah)\ **Post date:** [February 28, 2024, 7:33pm UTC](https://community.ipinfo.io/t/why-do-the-numbers-of-columns-not-match-between-the-csv-json-and-mmdb-files/5531/1 "2024-02-28T19:33:33Z")
> Please read this blog first: [How to choose the best file format for your IPinfo database? - IPinfo.io](https://ipinfo.io/blog/ipinfo-database-formats) > **[How to choose the best file format for your IPinfo database?](https://ipinfo.io/blog/ipinfo-database-formats)** > > We're the trusted source for IP address information, handling 50 billion IP geolocation API requests per month for over 1,000 businesses and 100,000+ developers The MMDB file format is a special type of binary database, designed for efficient IP lookup and returning its IP metadata information. You pass in an IP address and get its metadata information ([geolocation](https://ipinfo.io/products/ip-geolocation-database), [company](https://ipinfo.io/products/ip-company-database), [ASN](https://ipinfo.io/products/asn-database), etc.) back. In contrast to the binary MMDB database, the CSV and JSON database contain the IP data in plaintext format. You can easily look up the contents of CSV and JSON databases while the MMDB database contains data in binary format. ![WindowsTerminal_PZ81ZSZdNU-ezgif.com-optimize](https://canada1.discourse-cdn.com/flex035/uploads/ipinfo/original/2X/0/0779373f8d0ac943293f368ec23027c729b71149.gif) > To read the binary MMDB database file, you need specialized tools such as our [mmdbctl tool](https://github.com/ipinfo/mmdbctl) or [MMDB reader libraries](https://community.ipinfo.io/t/list-of-mmdb-reader-libraries/2821). ## The columns in these databases The CSV and JSON files contain IP data in the form of IP ranges. These IP ranges columns are: - start\_ip - end\_ip - [join\_key](https://community.ipinfo.io/t/ipinfos-join-key-column-explained/5526) This IP range column information is converted to binary data within the MMDB file. The MMDB file does not _generally_ return the range information and the `join_key` as they are not “IP metadata information”. You get the IP metadata when you look up an IP address from the MMDB. Consider the [IP to Company database](https://ipinfo.io/products/ip-company-database): The CSV file contains 3 IP range columns (including the `join_key`) and 8 IP metadata columns containing IP to company information: | # | Field Name | Column Type | | --- | --- | --- | | 1 | start\_ip | IP Range Columns (1) | | 2 | end\_ip | IP Range Columns (2) | | 3 | join\_key | IP Range Columns (3) | | 4 | name | IP Metadata Columns (1) | | 5 | domain | IP Metadata Columns (2) | | 6 | type | IP Metadata Columns (3) | | 7 | asn | IP Metadata Columns (4) | | 8 | as\_name | IP Metadata Columns (5) | | 9 | as\_domain | IP Metadata Columns (6) | | 10 | as\_type | IP Metadata Columns (7) | | 11 | country | IP Metadata Columns (8) | ![image](https://canada1.discourse-cdn.com/flex035/uploads/ipinfo/original/2X/f/f79a3df58e7ae6340b4491849139b50f24d0ecc6.png) It has the same number of columns in the JSON database as well. ![image](https://canada1.discourse-cdn.com/flex035/uploads/ipinfo/original/2X/7/7aadd4c210ec66f11899d8434411e3d0e24e4d4a.png) ## How many columns does the MMDB database have? Let’s use [mmdbctl tool](https://github.com/ipinfo/mmdbctl) to answer that: > Target IP address: `54.38.15.136` Let’s look up the target IP address using the [mmdbctl tool](https://github.com/ipinfo/mmdbctl): ```auto mmdbctl read 54.38.15.136 company_sample.mmdb ``` ![image](https://canada1.discourse-cdn.com/flex035/uploads/ipinfo/original/2X/2/291c2ee8f455b86e008c57f8b4beaf20e0484557.png) The `mmdbctl` tool _generally_ only returns the IP metadata information of the IP address being looked up. Because of the structure of the MMDB, IP ranges are converted to a binary representation, and they are used to return only the IP metadata information and not IP range information. ## Special point: Is it always only IP metadata? Outside the scope of the MMDB database, let’s talk about the capabilities of the [mmdbctl tool](https://github.com/ipinfo/mmdbctl). The mmdbctl tool can do extra things to return extra information in special circumstances. ### [mmdbctl](https://github.com/ipinfo/mmdbctl) can return input IP addresses When you are outputting to `TSV` or `CSV` format data, the `mmdbctl` tool will add an extra column natively called `IP`, which represents the input IP address: ```auto mmdbctl read 54.38.15.136 company_sample.mmdb -f csv ``` ![WindowsTerminal_o9DIoyliRe](https://canada1.discourse-cdn.com/flex035/uploads/ipinfo/original/2X/c/c2040f3e49ce3c26e5cd3df1d20889b6d48f1e30.png) ### mmdbctl can support outputting IP range column You can return the IP range or network information when querying the MMDB database. For that, you have to import/compile the MMDB database yourself from the CSV database. > IPinfo does not add the `network` column to their mmdb database by default. We use the `--no-network` declaration when importing/compiling to MMDB databases. Compile the CSV IP address database to an MMDB database using the following command. ```auto mmdbctl import --in company_sample.csv --out company_sample_network.mmdb ``` Then query the MMDB database as usual: ```auto mmdbctl read 54.38.15.136 company_sample_network.mmdb ``` ![image](https://canada1.discourse-cdn.com/flex035/uploads/ipinfo/original/2X/2/28d2e3997922d0fa6df5fc69c5ec8e8853d1f874.png) Notice the `network` column there. The network column just represents the range in the IP database. **It does not indicate the parent range or prefix of the input IP address**. > The `network` column has no impact or significance from a parent range perspective. The `network` column does not indicate parent range or prefix declaration as derived from WHOIS and BGP records. The network column has no significance outside the IP database’s boundaries. Use the [ASN API](https://ipinfo.io/products/asn-api) service to get an IP address’s parent range or prefix information. ![image](https://canada1.discourse-cdn.com/flex035/uploads/ipinfo/original/2X/9/9b6a5e330d273b09505ca35b0b9c7da93bf9653a.png) ### Representing IP range values in CIDR format We can even take this one step further. Converting the IP address range (`start_ip`, `end_ip`) to their CIDR equivalent. If you precompile the CSV to have their `start_ip`, and `end_ip` be converted to their CIDR equivalent using the [IPinfo CLI](https://github.com/ipinfo/cli), then import/compile the CSV file to MMDB format using the CLI, you can get the network value in the MMDB file in their CIDR format: ```auto ipinfo range2cidr company_sample.csv > company_sample_cidr.csv mmdbctl import --in company_sample_cidr.csv --out company_sample_network.mmdb mmdbctl read 54.38.15.136 company_sample_network.mmd ``` ![image](https://canada1.discourse-cdn.com/flex035/uploads/ipinfo/original/2X/9/9f3f754b544a608d69f3c59d97de28cdc08e7229.png) * * * Feel free to reply to this post if you have any questions or feedback. ---
**Author:** ![Suhas](https://avatars.discourse-cdn.com/v4/letter/s/4af34b/32.png) [@Suhas](https://community.ipinfo.io/u/Suhas)\ **Post date:** [March 1, 2024, 9:05am UTC](https://community.ipinfo.io/t/why-do-the-numbers-of-columns-not-match-between-the-csv-json-and-mmdb-files/5531/2 "2024-03-01T09:05:52Z")
Hello Abdullah, I tried using the mentioned steps to generate an mmdb file from the company csv file. But the process keeps getting killed with Out of Memory. I tried with an 8Gig machine initially, then tried with a 32Gig machine having 20Gig of storage. But still the process gets killed. I am able to convert the sample csv to mmdb format, but the tool does not work for full company csv file. Can you please let me know if you have tried and are able to convert the full company csv file to mmdb format. (The company csv file is around 4Gig) Logs: Mar 1 07:09:33 ip-172-31-27-69 kernel: [1245.636384] oom-kill:constraint=CONSTRAINT\_NONE,nodemask=(null),cpuset=chrony.service,mems\_allowed=0,global\_oom,task\_memcg=/user.slice/user-1000.slice/session-1.scope,task=mmdbctl,pid=3250,uid=1000 Mar 1 07:09:33 ip-172-31-27-69 kernel: [1245.636440] Out of memory: Killed process 3250 (mmdbctl) total-vm:33098176kB, anon-rss:32353404kB, file-rss:128kB, shmem-rss:0kB, UID:1000 pgtables:63472kB oom\_score\_adj:0 Mar 1 07:09:33 ip-172-31-27-69 systemd[1]: session-1.scope: A process of this unit has been killed by the OOM killer. Thanks, Suhas ---
**Author:** ![Max](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.ipinfo.io/max/32/113_2.png) [@Max](https://community.ipinfo.io/u/Max)\ **Post date:** [March 1, 2024, 12:58pm UTC](https://community.ipinfo.io/t/why-do-the-numbers-of-columns-not-match-between-the-csv-json-and-mmdb-files/5531/3 "2024-03-01T12:58:46Z")
Hi Suhas, The MMDB writing process does indeed require a lot of memory, but we can help here 🙂 @Abdullah mentioned that you wanted to keep the range information in the MMDB. Is that correct? If so, note that the range in the CSV file (start\_ip, end\_ip) has no specific meaning. It just represents a range of consecutive IPs with the same values, but it does not necessarily match the BGP (routed prefix) or WHOIS range (registered range). Could you clarify which kind of information you would like to obtain? We can build an MMDB that contains what you need. Thanks, Max ---
**Author:** ![Suhas](https://avatars.discourse-cdn.com/v4/letter/s/4af34b/32.png) [@Suhas](https://community.ipinfo.io/u/Suhas)\ **Post date:** [March 4, 2024, 1:31pm UTC](https://community.ipinfo.io/t/why-do-the-numbers-of-columns-not-match-between-the-csv-json-and-mmdb-files/5531/4 "2024-03-04T13:31:01Z")
Hi Max, That’s right, we want to keep the range information in MMDB. Is it possible to include the start\_ip, end\_ip (from CSV file), routed prefix(from BGP) and the registered range (from WHOIS). Thanks, Suhas ---
**Author:** ![Suhas](https://avatars.discourse-cdn.com/v4/letter/s/4af34b/32.png) [@Suhas](https://community.ipinfo.io/u/Suhas)\ **Post date:** [March 13, 2024, 3:03pm UTC](https://community.ipinfo.io/t/why-do-the-numbers-of-columns-not-match-between-the-csv-json-and-mmdb-files/5531/5 "2024-03-13T15:03:47Z")
Hello @Max and @Abdullah, Any update on this? Thanks, Suhas ---
**Author:** ![Abdullah](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.ipinfo.io/abdullah/32/4973_2.png) [@Abdullah](https://community.ipinfo.io/u/Abdullah)\ **Post date:** [March 13, 2024, 3:18pm UTC](https://community.ipinfo.io/t/why-do-the-numbers-of-columns-not-match-between-the-csv-json-and-mmdb-files/5531/6 "2024-03-13T15:18:12Z")
My apologies for the delay, @Suhas. Give me a moment. I have reached out to our sales team to see if they have contacted your team. ---
**Author:** ![Abdullah](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.ipinfo.io/abdullah/32/4973_2.png) [@Abdullah](https://community.ipinfo.io/u/Abdullah)\ **Post date:** [March 19, 2024, 2:25am UTC](https://community.ipinfo.io/t/why-do-the-numbers-of-columns-not-match-between-the-csv-json-and-mmdb-files/5531/7 "2024-03-19T02:25:30Z")
Hey @Suhas, I hope you are doing well. Please let me know what you think of the solution: ## Using the [free IP to ASN database](https://ipinfo.io/developers/ip-to-asn-database) as a secondary data source The dataset is free, updated daily, and provides full accuracy. There should not be any compromise with data quality, but there is a small caveat: you have to use two MMDB datasets. We will discuss this issue in the later section. ## Instructions First, download the [IP to ASN free database](https://ipinfo.io/developers/ip-to-asn-database) in the CSV format: ```auto curl -L https://ipinfo.io/data/free/asn.csv.gz?token=$TOKEN -o asn.csv.gz ``` Your existing token will work just fine. The IP dataset is available for free everyone. Unzip gzipped CSV file: ```auto gunzip asn.csv.gz ``` Add the range/network column to the database using the IPinfo CLI: ```auto ipinfo range2cidr asn.csv > asn_cidr.csv ``` Convert it to MMDB database: ```auto mmdbctl import --in asn_cidr.csv --out asn_cidr.mmdb ``` And now you will have access to `range` information of ASN. ## Usage ```auto mmdbctl read 86.196.240.78 asn_cidr.mmdb | jq ``` ```auto { "asn": "AS3215", "domain": "orange.com", "name": "Orange S.A.", "network": "86.192.0.0/11" } ``` ## Caveats **Using two MMDB database** I understand that we discussed an idea about a single dataset, but considering our challenges for the moment, it is a simple compromise. The MMDB file format is incredibly fast, so you should be able to support lookup from two MMDB datasets. If you need any assistance, please let me know. I am always happy to help. **Discrepency between ASN names in between ASN database and Company database** This is a non-issue, as using the IP to ASN database will give you the best possible result. I recommend sticking with the ASN database for ASN data. Here is a recommended read: [Differences in data for the ASN and the IP to Company database - Docs / Knowledgebase - IPinfo Community](https://community.ipinfo.io/t/differences-in-data-for-the-asn-and-the-ip-to-company-database/730) **Database range is not a representation of WHOIS records or other information** Because we aggregate and flatten ranges, the range representation in the IP database is not the same as how they are represented in [WHOIS records](https://ipinfo.io/products/whois-database) and other public routing records. However, I think (I have not verified this) the range information that will be generated from the method I highlighted will represent the largest prefix possible that spans across multiple neighboring prefixes as our data pipeline aggregates the ranges, resulting in smaller ranges merging to their highest possible common range. * * * I really appreciate your participating in the community. Let me know if you need any help in adopting this solution. Again, thank you very much!