Skip to main content

Import data from mysql to mongo


To import data from mysql into mongodb we need to export data from mysql in a format mongo likes, this is either a tsv or a csv. Ideally you would like to use the following sql

select columns INTO OUTFILE '/path/to/csv'
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
   LINES TERMINATED BY '\n' from table [where clause]

What the above SQL does is exports the data (only data , no headers) into a file as provided in your path, make sure you have access to location where you are putting the file. If you ignore the path and just use the filename this will be stored in the location where mysql data files are stored.

To get the fields in the data file, we can use the following
   
echo "SELECT * FROM
;" | mysql -uXXX -pXXX > '/path/to/
.tsv'

Finally you want all the be imported into a mongo database, here since our file contains a header we specify the --headerline option. For a complete list of options available with mongoimport please look at the mongodb site (http://docs.mongodb.org/manual/reference/mongoimport/)

mongoimport -d -c -type tsv --headerline

The headerline option uses the first line for fieldnames, this is fine till you have no data in your mysql and still want to import, then you are back to using the first option on OUTFILE but you also need to have a header file generated which you can then use in mongo, lets see how we can do this.

If you run into problems because of an empty data set, an empty set means no column names! use the steps below:

echo "SELECT column_name FROM information_Schema.cloumns WHERE table_schema = DATABASE() AND table_name='';" | mysql -uXXX -pXXX > '/path/to/_headers.tsv'

Then when importing to mongo use the following:

mongoimport -d -c -type tsv --fieldFile /path/to/_header.tsv

Comments

Popular posts from this blog

Create a drupal development environment using docker.

Currently at work i am using docker to streamline and speedup of development and deployment to testing and production, I am using symfony currently and our app is dockerised, i wanted to do the same for drupal based apps.

I searched for a official drupal docker repo and i did find this. https://www.drupal.org/node/2538074

Unfortunately there was very terse explanation but pretty much worked, i found a few hiccups along the way so wanted to share what i did.

Pre-requisite:
Install docker for your environment. Lots of details are available at https://docs.docker.com/ explaing docker and installing docker for your environment.

The docker repo holds the drupal container at https://hub.docker.com/_/drupal/, following the instructions this is how to create the drupal container:

In order to use my code base we need to mount the volume with the -v directive as below, we do this in one command:

docker run -v /$HOME/work/drupal8:/var/www/html -p 8080:80 --name mydrupal -d drupal

this will create…

Use feeds module to import XML data in Drupal 7

Recently i had to setup a xml import process to import data from an external supplier on a regular basis, the filename and format was not changing and i could choose where to store the file. Following is how i set it up.

First we need to download the following modules

feeds
feeds admin ui
feeds_xpathparser
ctools
job_scheduler

enable the above modules.

We will need a sample XML so this will be the one we will use for this demonstration:

<?xml version="1.0" encoding="utf-8" ?>
<!-- Sample for demonstration of processing XML feeds with feeds module -->
<Orders>
<Order>
<CustomerID>10</CustomerID>
<Order_Number>1</Order_Number>
<Order_Total>100</Order_Total>
<Currency>£</Currency>
</Order>
<Order>
<CustomerID>11</CustomerID>
<Order_Number>2</Order_Number>
<Order_Total>40</Order_Total>
<Currency>$</Currency>
</Order>
</Orders>


How do you expose your legacy database tables to views in drupal 6 Part 1

This is a 2 part post, that shows how I exposed a legacy table to views in drupal and used the incredible power of views to create many combinations static and conditional views. This was very helpful for my client.

I had a requirement that warranted exposing existing database table to views, so that views can contain data from this table.  To enable this to happen, the following steps need to be taken.
In a custom module you need to first define the views_api hook as following:
function candidate_views_api(){ $view = array(); $view = array('api' => 2, 'path' => drupal_get_path('module','candidate'). '/views'); return$view; }
candidate module was a custom module which I already had, so it made sense to add this hook here. 
The important bit here is telling the drupal sub-system where the views file will reside. 
Then create a file with your <modulename>.views.inc in my case it was candidate.views.inc, this file should have two hooks, one …