Importing data with mongoimport

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 provides in a previous post. Importing data into a MongoDB database is made easy with the CLI tool, mongoimport.

For many use cases, mongoimport is pretty straight forward. It is, in fact, highly used in the MongoDB University 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, however.

mongoimport

Connections

The mongoimport will connect to a running mongod or mongos, 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 collection in the blog database with a posts.json file it is simple enough to run the following command.

mongoimport --db blog --collection posts --file posts.json

That is pretty easy. We can make it easier too by using the shorthand version of those flags.

mongoimport -d blog -c posts --file posts.json

If we want to make sure that our posts collection is dropped and only the new data is there, we can use the --drop flag.

mongoimport -d blog -c posts --drop --file posts.json

If you need to change the host or port number, there are flags for that as well, --host and --port, respectively. --host is even more convenient because it allows you to add the port at the end, and use a shorter flag -h. So the following are the same:

mongoimport --host 123.123.123.1 --port 1234 -d blog -c posts --file posts.json

mongoimport -h 123.123.123.1:1234 -d blog -c posts --file posts.json

That’s easy enough as well. What if, however, our MongoDB server requires user authentication, like any good server should?

mongoimport Server Authentication

Data security 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? You guessed it, there are flags for that too. --username or -u and --password or -p are your friends.

mongoimport -h 123.123.123.1:1234 -u user -p "pass" -d blog -c posts --file posts.json

We can add in some extra assurances by leaving off the --password flag and mongoimport will prompt for an appropriate password.

That works great for some simpler authentication options, but what if we have a more involved authentication system with an authentication database? We can specify one with the --authenticationDatabase flag. That’s pretty handy to keep only authorized people from importing data into your collection.

mongoimport provides a great range of flag options for connecting to secured servers. I would highly recommend looking at the documentation for specifics based on your environment.

File & Column Types

As stated earlier, mongoimport works on CSV, TSV, and JSON documents. By default the import format is JSON. With the --type 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.

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, right? Well, mongoimport offers a --headerline flag that accomplishes that for us.

For times in which our CSV or TSV file doesn’t include header information, mongoimport has a solution for that as well. With the --fields 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 flag.

Along with some of the other features new to MongoDB version 3.4, there are some new features added to mongoimport. One of them is the
--columnsHaveTypes flag. When used in conjunction with the --fields,  --fieldFile, or --headerline flag it allows you to specify the types of each field. You pass in the field name in the format of columnName.type() along with any arguments into the type() method. So, for example, if you were typing a filed called isAdmin you would use isAdmin.bool(). Have a look at the --columnsHaveTypes documentation for a list of available types and supported arguments.

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.

Importing into an existing collection

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
--mode flag offers a way to tell mongoimport how to handle existing collection documents which match incoming ones. There are three options to the --mode flag, insert, upsert, and merge.

  • Insert allows the documents to get put into the collection with the only check being on fields with a unique index. If there are duplicate values, mongoimport logs an error.
  • Upsert replaces documents in the database with the new documents from the import file. All other documents get inserted.
  • Merge, well, it merges existing documents with matching incoming documents and inserts the others. This is another new feature of version 3.4.

If you are needing to import documents in one of these ways, look at the documentation for options on upserting and merging based on field other than _id.

Wrap Up

MongoDB also provides a similar, but inverse, function mongoexport. While both tools are powerful they do not preseve the BSON data types than MongoDB uses. As such, these tools should not be used for production backups. MongoDB provides other tools for backup methods.

I hope that this post has given you some insights into one of the powerful MongoDB Package Component tools that are provided “out of the box”, mongoimport. Some programming languages have developed their own separate tools for importing data. Some of them are better than others. For me, since such a powerful import tool is already provided, I find myself using mongoimport more often than not.

If you haven’t tried it out yet yourself, I would encourage you to do so.

There are several MongoDB specific terms in this post. I created a MongoDB Dictionary skill for the Amazon Echo line of products. Check it out and you can say “Alexa, ask MongoDB for the definition of authentication?” and get a helpful response.

Follow me on Twitter @kenwalger to get the latest updates on my postings.

Facebooktwitterredditlinkedinmail

MicroPython Temperature Sensor

I’ve shown an example of the “Hello World” in IoT, a blinking light, in this post. Blinking lights are great and make for a nice visual experience, but what if we want to do something with an IoT device, such as a NodeMCU ESP8266 that goes beyond the visual? Let’s take our NodeMCU and add a temperature sensor. Then, with MicroPython, we’ll get our readings.

Needed Equipment

To get started on this project you’ll need the following equipment:

I have found this kit of basic electronic components to be pretty good for starting one’s IoT device journey. It doesn’t include things like the TMP36 temperature sensor, but it has a wide variety of other pieces that are ultimately useful.

TMP36 Specs

Since this project will be using the TMP36, let’s discuss it briefly.

TMP36 Temperature Sensor

This temperature sensor is a low voltage sensor, requiring 2.7 V to 5.5 VDC input. It returns a Celsius temperature reading from the Vout pin in an operating range of -40°C to +125°C. It is reasonably accurate, especially for hobby/demo situations with a ±2°C accuracy. You can download the specs for the TMP36 here.

Temperature Sensor Project

Hardware Configuration

Make sure the NodeMCU is disconnected from USB when making connections.

Temperature Sensor schematic

  1. To add the TMP36 sensor to the NodeMCU we need to make sure that it is properly oriented. The flat side of the temperature sensor should be facing the bottom of the board.
  2. Connect the rightmost lead, in position a8 to the negative rail on the board.
  3. The negative rail then is connected to the GND pin (pin a29)on the NodeMCU.
  4. Connect the leftmost lead (a10) of the TMP36 to the positive rail.
  5. Connect the 3v3 pin (a30) on the NodeMCU to the positive rail.
  6. Finally, connect a jumper wire from the center lead (c9) to the A0 pin on the NodecMCU (j16).

Temperature Sensor bread board

The A0 pin on the NodeMCU is the analog-to-digital conversion (ADC) pin.

Code

With the hardware side of things built, let’s see what we can do in MicroPython to get a temperature! Fortunately, MicroPython’s machine library makes this pretty simple for us.

I’ll be working in the console REPL.  The code should work the same, however, if you are working in the WebREPL environment.

Our first step is to handle our imports

from machine import ADC

That brings in our necessary ADC connections, and we can assign a variable to pin 0 for that

adc = ADC(0)

We can print out the value from the TMP36 now with adc.read() which returns the Celsius temperature, well almost. The value it returns is ten times the temperature. Let’s write a function that will handle that conversion for us.

def temp(value):
    return value/10

While we’re at it, let’s write a function to convert to Fahrenheit as well.

def fahrenheit(celsius):
    return (celsius * (9/5)) + 32

With those in place, we can get, and display our readings.

reading = adc.read()

celsius_temp = temp(reading)

fahrenheit_temp = fahrenheit(celsius_temp)

print("TMP36 reading {}\nDegrees Celsius {}\nDegrees Fahrenheit {}".format(reading, celsius_temp, fahrenheit_temp))

After executing our print statement we should get back our readings. MicroPython certainly has made things easy for us with the ADC methods.

Temperature Sensor REPL

For your convenience I have included the code is available as a Gist on GitHub as well.

Wrap Up

In this post, I have shown how to get temperature readings from an analog temperature sensor, such as the TMP36. In just a few lines of MicroPython code, we are able to get quite a bit of functionality. This is one of the many great things about MicroPython, the direct access to hardware is generally pretty easy.

I think the next step in exploring MicroPython and the NodeMCU will be to take these temperature readings and see if we can connect them to a service such as Losant and generate some visualizations of our temperatures.

Follow me on Twitter @kenwalger to get the latest updates on my postings on MicroPython and IoT and let me know what you are building with MicroPython.

Facebooktwitterredditlinkedinmail