0

I have a table in MySQL called new_ndnc that contains five fields. Each field contains 10 million rows.

I have read each field into an array, and now I want to insert the whole array into a field in MongoDB.

My code is below.

#!/usr/bin/perl

use MongoDB;
use MongoDB::OID;
use DBI;

$dbs  = 'amrit';
$user = 'root';
$pass = 'walkover';
$dbh  = DBI->connect("DBI:mysql:database=$dbs", $user, $pass)
    or die "Cannot connect to MySQL server\n";

$conn = MongoDB::Connection->new(
  host    => 'localhost',
  port    => 27017,
  db_name => 'amrit'
);
$db     = $conn->get_database('amrit');
$users  = $db->get_collection('hell2');

$abcuid = $dbh->prepare('select Service_Area_Code from new_ndnc');
$abcuid->execute;

@uid;
while (my @row = $abcuid->fetchrow_array()) {
  push(@uid, @row);
}
$hh = $dbh->prepare('select phonenumbers from new_ndnc');
$hh->execute;

@route1;
while (my @row = $hh->fetchrow_array()) {
  push(@route1, @row);
}

$r4 = $dbh->prepare('select Preferences from new_ndnc');
$r4->execute;

@route4;
while (my @row = $r4->fetchrow_array()) {
  push(@route4, @row);
}
$exr4 = $dbh->prepare('select Opstype from new_ndnc');
$exr4->execute;

@exroute4;
while (my @row = $exr4->fetchrow_array()) {
  push(@exroute4, @row);
}
$r5 = $dbh->prepare('select PhoneType from new_ndnc');
$r5->execute;

@route5;
while (my @row = $r5->fetchrow_array()) {
  push(@route5, @row);
}

$users->insert({
    'UserID'           => "[@uid]",
    'route1'           => "[@route1]",
    'route4'           => "[@route4]",
    'route4_extra_bal' => "[@exroute4]",
    'route5'           => "[@route5]"
  }
);
6
  • Please always use strict and use warnings at the start of every Perl program you write, especially if you are asking for help with it. Commented Feb 6, 2014 at 7:11
  • @user2916639 i am running code using #perl -w Commented Feb 6, 2014 at 7:17
  • use warnings is preferable to -w on the command line because it is lexically scoped and individual warning categories can be toggled on or off. And use strict is even more important than enabling warnings. Commented Feb 6, 2014 at 7:19
  • Good advice so far. But where is your MongoDB part? What have you tried. BTW @Borodin is telling you to use strict because you are declaring globals all over the place. Will come back and bite you. Commented Feb 6, 2014 at 7:26
  • @NeilLunn; The $users variable is a collection called hell2 on MongoDB database amrit. The last statement is an attempt to write to it. Commented Feb 6, 2014 at 7:35

1 Answer 1

1

On re-reading your code your approach is all wrong. What you are doing is pulling everything out of a table one column at a time, pushing each row value into an array and trying to write that into MongoDB. As it stands you are trying to write a single document in MongoDB that has each field containing each row value from the table. And this is most certainly what you do not want.

What you likely, actually want to do is:

  1. Select the results from the table with the columns you are going to put in each mongo field. And do this in one query.

  2. As you fetch over the rows of the table, insert each document into your MongoDB collection.

Another misconception you have is the ObjectId's (OID). Each MongoDB document has a default field _id, if you do not specify this value explicitly on an insert then this value will be populated automatically. If you do actually have a reasonable natural primary key that will not duplicate then you can put this into that field. In HashRef notation, { '_id' => 'key_value' }.

For an easier transposition of table rows to a HashRef structure that you need for inserting a row into MongoDB, look at fetchrow_hashref in the DBI documentation.

It's kind of hard to advise beyond this as your code is kind of funky and it's not very clear what you are really trying to do. These obviously aren't small arrays so you don't want to try and push all those values in one document.

Sign up to request clarification or add additional context in comments.

7 Comments

i have used this only .$users->insert({'UserID' => \@uid}); It is displaying "insert is too large: 155340264 max: 16777216 at ./mongo2.pl line 56".
How many rows are you inserting into these arrays? An array field in a document may not be what you want. You may want to be inserting documents into mongo just like the tables in relational. Are you expecting one document per 'UserID'?
there is 1 crore Row in that array, i want to create one Crore row and each document will have one element of the array. should i use for loop for doing so............? i had use forloop before but that was taking too much time for inserting 1 crore element .
you can try batchinsert of monogdb to insert this bulk data. make array in key value . If u are getting error of size then you can make array chunks (bundles) and try to insert this bundles one by one.
@sidd The structure is still all wrong in the program. batchinsert takes an arrayref of hashrefs, each representing a document. The program listing to doing it all wrong and it really needs to be looked at.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.