| |
1, export data in mongodb, write a pace expmongo.sh, shell script is as follows
#! / Bin / sh
datestr = `date '+% Y-% m-% d'`
/usr/local/mongodb/mongodb-linux-x86_64-2.4.4/bin/mongoexport -im_user -pxxxeedd -d im -c message -f txnType, issNo, sn, merchantorder, paymentType, paymentTime, authNo, expDate, cardNo, amount, batchNo, refNo, merchantName, voucher, acqNo --csv -o /tmp/im_$datestr.txt && sz /tmp/im_$datestr.txt
Im out guide data, / tmp / im_ $ datestr.txt, there are probably more than 6,000 records.
Data like this:
txnType, issNo, sn, merchantorder, paymentType, paymentTime, authNo, expDate, cardNo, amount, batchNo, refNo, merchantName, voucher, acqNo
"0", "", "WP14521000000342", "", "card", "1440050366667", "", "", "6216615625", "10", "2", "891094135928", "delicious fragrance museum "," 000235 "," "
"1" ,, "WP14521000000342" ,,,,,,, "10" ,,,,,
"0", "", "WP14521000000422", "", "cash", "1440050722137", "", "", "", "0.01", "", "", "old tailor textile", "", ' "
2, inside the mysql database, build a table of data stored mongodb:
Built Table statements are as follows:
CREATE TABLE sap_im (
txnType varchar (8) DEFAULT NULL,
issNo varchar (128) DEFAULT NULL,
sn varchar (128) DEFAULT NULL,
merchantorder varchar (128) DEFAULT NULL,
paymentType varchar (128) DEFAULT NULL,
paymentTime varchar (128) DEFAULT NULL,
authNo varchar (128) DEFAULT NULL,
expDate varchar (128) DEFAULT NULL,
cardNo varchar (128) DEFAULT NULL,
amount varchar (128) DEFAULT NULL,
batchNo varchar (128) DEFAULT NULL,
refNo varchar (128) DEFAULT NULL,
merchantName varchar (128) DEFAULT NULL,
voucher varchar (128) DEFAULT NULL,
acqNo varchar (128) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
3, which introduced into mysql
Upload im_ $ datestr.txt to mysql server first and then export the file into a table name
mv /tmp/im_detail_2015-11-12.txt /tmp/sap_im.csv
Start the import:
mysql> LOAD DATA INFILE "/tmp/sap_im.csv" REPLACE INTO TABLE sap_im FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' "' ESCAPED BY '"' LINES TERMINATED BY '\ r \ n';
Query OK, 1 row affected (0.02 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
Strange, introduced here only the first row of records, it is estimated newline problem, remove the command line \ r, the '\ r \ n' into '\ n', as shown below:
mysql>
mysql>
mysql> LOAD DATA INFILE "/tmp/sap_im.csv" REPLACE INTO TABLE sap_im FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' "' ESCAPED BY '"' LINES TERMINATED BY '\ n';
Query OK, 6555 rows affected (1.81 sec)
Records: 6555 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
Finally normal import more than 6000 records out. |
|
|
|