{"id":387,"date":"2017-06-19T06:38:27","date_gmt":"2017-06-19T13:38:27","guid":{"rendered":"http:\/\/www.kenwalger.com\/blog\/?p=387"},"modified":"2017-10-16T16:58:48","modified_gmt":"2017-10-16T23:58:48","slug":"importing-data-mongoimport","status":"publish","type":"post","link":"https:\/\/www.kenwalger.com\/blog\/nosql\/mongodb\/importing-data-mongoimport\/","title":{"rendered":"Importing data with mongoimport"},"content":{"rendered":"

There comes a time in almost everyone’s experience with databases when it would be great to bring in data from an outside source. Often the data is in a spreadsheet format (CSV or TSV) or perhaps a JSON format. I discussed some of the command line tools MongoDB<\/a> provides in a previous post<\/a>. Importing data into a MongoDB database is made easy with the CLI tool, mongoimport.<\/p>\n

For many use cases, mongoimport is pretty straight forward. It is, in fact, highly used in the MongoDB University<\/a> courses as a way to quickly populate a database, for example. I’d like to look at some use cases beyond simply populating an empty collection<\/a>, however.<\/p>\n

mongoimport<\/h3>\n
Connections<\/h5>\n

The mongoimport will connect to a running mongod<\/a> or mongos<\/a>, instance running, by default, on port 27017 on localhost. The syntax of the mongoimport command is fairly straightforward. If for example, we want to populate the posts<\/code> collection in the blog<\/code> database with a posts.json<\/code> file it is simple enough to run the following command.<\/p>\n

mongoimport --db blog --collection posts --file posts.json\n<\/pre>\n
That is pretty easy. We can make it easier too by using the shorthand version of those flags.<\/p>\n
mongoimport -d blog -c posts --file posts.json\n<\/pre>\n
If we want to make sure that our posts<\/code> collection is dropped and only the new data is there, we can use the --drop<\/code> flag.<\/p>\n
mongoimport -d blog -c posts --drop --file posts.json\n<\/pre>\n
If you need to change the host or port number, there are flags for that as well, --host<\/code> and --port<\/code>, respectively. --host<\/code> is even more convenient because it allows you to add the port at the end, and use a shorter flag -h<\/code>. So the following are the same:<\/p>\n
mongoimport --host 123.123.123.1 --port 1234 -d blog -c posts --file posts.json\n<\/pre>\n
mongoimport -h 123.123.123.1:1234 -d blog -c posts --file posts.json\n<\/pre>\n
That’s easy enough as well. What if, however, our MongoDB server requires user authentication, like any good server should?<\/p>\n
mongoimport Server Authentication<\/h5>\n
Data security<\/a>\"\" should be on everyone’s mind when it comes to server management. With that in mind, MongoDB offers a variety of ways to secure your data. Assuming that one needs to get authenticated access to the server, how can one use mongoimport to do so?\u00a0You guessed it, there are flags for that too. --username<\/code>\u00a0or -u<\/code>\u00a0and --password<\/code>\u00a0or -p<\/code> are your friends.<\/p>\n
mongoimport -h 123.123.123.1:1234 -u user -p \"pass\" -d blog -c posts --file posts.json\n<\/pre>\n
We can add in some extra assurances by leaving off the --password<\/code> flag and mongoimport will prompt for an appropriate password.<\/p>\n
That works great for some simpler\u00a0authentication options, but what if we have a more involved authentication system with an\u00a0authentication database<\/a>? We can specify one with the --authenticationDatabase<\/code> flag. That’s pretty handy to keep only authorized people from importing data into your collection.<\/p>\n
mongoimport provides a great range of flag options for connecting to secured servers. I would highly recommend looking at the documentation<\/a> for specifics based on your environment.<\/p>\n
File & Column Types<\/h3>\n
As stated earlier, mongoimport works on CSV, TSV, and JSON documents. By default the import format is JSON. With the --type<\/code> flag we can import CSV or TSV files. Since CSV and TSV files can contain some special features, let’s look at some of the options for working with them and mongoimport.<\/p>\n
Many times a CSV or TSV file will include a header line. It would be handy if we could utilize those header values as field names in our MongoDB documents<\/a>, right? Well, mongoimport offers a --headerline<\/code> flag that accomplishes that for us.<\/p>\n
For times in which our CSV or TSV file\u00a0doesn’t<\/em> include header information, mongoimport has a solution for that as well. With the --fields<\/code> flag, one can provide a comma-separated list of field names. Alternatively, you can generate a file of field names, with one name per line, and pass it along with the --fieldFile<\/code>\u00a0flag.<\/p>\n
Along with some of the other features<\/a> new to MongoDB version 3.4, there are some new features added to mongoimport. One of them is the
\n--columnsHaveTypes<\/code> flag. When used in conjunction with the --fields<\/code>, \u00a0--fieldFile<\/code>, or --headerline<\/code>\u00a0flag it allows you to specify the types of each field. You pass in the field name in the format of columnName.type()<\/code> along with any arguments into the type()<\/code> method. So, for example, if you were typing a filed called isAdmin<\/code> you would use isAdmin.bool()<\/code>.\u00a0Have a look at the --columnsHaveTypes<\/code> documentation<\/a> for a list of available types and supported arguments.<\/p>\n
Fair warning here, the flags dealing with header information are for CSV and\/or TSV files. If one attempts to use them with a JSON formatted file, mongoimport gets grumpy and returns an error.<\/p>\n
Importing into an existing collection<\/h3>\n
One last concept and list of flags I’d like to cover is for those instances in which you want to import data into an existing collection. The
\n--mode<\/code>\u00a0flag offers a way to tell mongoimport how to handle existing collection documents which match incoming ones. There are three options to the --mode<\/code> flag, insert<\/strong>, upsert<\/strong>, and merge<\/strong>.<\/p>\n