Showing posts with label data transfer. Show all posts
Showing posts with label data transfer. Show all posts

Monday, May 7, 2012

PHP script to migrate mysql table to mongo collection


This is a naive PHP script I wrote to migrate data from mysql to mongo DB. I have tested transferring 10 million rows with no problem. Mongo and mysql DB credentials are not handled in this code. Even though code doesnot handle many mongo exceptions, it is good enough for normal use cases. Anybody is welcome to refine the code.
<?
// Usage ./mysqltomongo.php mysqldb mysqltable mongodb mongocollection
function mongo_connect($db,$collection) {
 $m = new Mongo(); //handle username,ports and passwords
 $mydb = $m->$db;
 if(!isset($collection)) return $mydb;
 else {
  $mycollection = $mydb->$collection;
  return $mycollection;
 }
}


$mysqldb=$argv[1];
$mysqltablename=$argv[2];
$mongodb=$argv[3];
$mongocollection=$argv[4];

$link = mysql_connect(...); //give proper info
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

mysql_select_db($mysqldb);
$result = mysql_query("SELECT * from ".$mysqltablename);

$fields = mysql_num_fields($result);
$coltypes=array();
for ($i=0; $i < $fields; $i++) {
    $coltypes[mysql_field_name($result, $i)]=mysql_field_type($result, $i);
}

$mdb = mongo_connect($mongodb);
$mdb->dropCollection($mongocollection);
$collection= mongo_connect($mongodb,$mongocollection);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
 $newrow=transform($row,$coltypes);
 try {
         $collection->insert($newrow);
 }
 catch(MongoException $e) {
  print_r($e);
 }
}

function transform($row,$coltypes) {
 $val;
 $ret=array();
 foreach($row as $k=>$v) {
        $val=utf8_encode($v);
        if($coltypes[$k]=="real") $val=floatval($val);
        else if($coltypes[$k]=="int") $val=intval($val);
        $ret[$k]=$val;
 }
 return $ret;
}

mysql_close();
?>