Importing data in RethinkDB

Introduction

There will be moments in which you need to move around your data between different database softwares. Fortunately RethinkDB can take data from JSON and CSV files exported from other services as MySQL or PostgreSQL.

Requeriments

• A running RethinkDB installation.
• The python package rethinkdb installed to your machine with pip. The import tool to perform data importing is bundled in that package. It is recommended to use the Python 3 version package, as Python 2 is reaching its end of life soon.

Preparing the data

For this tutorial you will need two files, one json and one csv. Preferably placed in your home folder. Something important to note is that you do not need to already have created your destination table because RethinkDB takes care of it.

The first file is called temp_data1.json, and should contain the following contents:

[
  {
    "id": 1, "day": "monday", "max": 23, "min": 12
  },
  {
    "id": 2, "day": "tuesday", "max": 22, "min": 12
  },
  {
    "id": 3, "day": "wednesday", "max": 23, "min": 12
  },
  {
    "id": 4, "day": "thursday", "max": 24, "min": 12
  },
  {
    "id": 5, "day": "friday", "max": 25, "min": 12
  },
  {
    "id": 6, "day": "saturday", "max": 26, "min": 13
  },
  {
    "id": 7, "day": "sunday", "max": 27, "min": 13
  }
]

And the second one is called temp_data2.csv with the following contents:

id, day, max, min
8, 'monday', 24, 12
9, 'tuesday', 21, 11
10, 'wednesday', 26, 14
11, 'thursday', 24, 13
12, 'friday', 25, 12
13, 'saturday', 25, 10
14, 'sunday', 22, 14

Note that it is recommended to use commas as separators for the second file. Also check that you have your RethinkDB instance running in the way you are more confortable with.

Importing the JSON file

After having created the files you are ready to import data. You will use the import command of RethinkDB with the name of the file and the name of the table where data will be stored. In this case data is going to be in test database.

rethinkdb import -f temp_data1.json --table test.TempData1

Then you will get a message with the amount of rows imported to your table.

Importing the CSV file

Importing a CSV file is just as easy as importing a JSON file, you just have to specify the file format with the --format option:

rethinkdb import -f temp_data2.csv --table test.TempData2 --format csv

Again, you will get the amount of rows imported to your table. Note that this time you are importing to different table.

Wrapping things up

After having success importing data to your database, it is time to query it so you can see it. Run each command separately in the Web Panel located at localhost:8080 in the Data Explorer section:

r.db('test').table('TempData1')
r.db('test').table('TempData2')

There is difference between both queries' result, everything in a CSV is imported as string, you must either convert the already imported data in RethinkDB or transform the CSV to JSON with specialized tools.

To finish this part keep in mind that in the case you already created the destination tables in the database just add the --force option to your command to force data writing to those tables.

Conclusion

RethinkDB provides a fast and useful tool for importing data. Fortunately for us, the tools used in this tutorial can be automated with the help of programming languages and its respective bindings. With this we can incorporate data from external service in a breeze.