About MongoDB and SQL Server using the self-brought tools to realize the rapid migration of data and considerations

Background introduction

With the development of business and the change of demand, we pursue the use of different types of databases and give full play to their respective characteristics. If you decide to adopt a new type of database, you need to migrate the existing data to the new database. In this type of requirement, it is particularly important to import data from SQL Server into MongoDB.

In the face of this demand, we first think of Baidu to find third-party tools, unfortunately, until now, there is no accepted a more satisfactory data migration tool. Baidu is not, we may be going back to develop their own. In this way, it costs a lot of manpower and material resources and valuable energy. Even, because there is no ideal migration tool, project optimization is repeatedly delayed.

In fact, you needn't have been so! Because, there is also a tool, and performance is not the same.

That is to fully excavate the export import function of the database itself.

Principle

The MongoDB database can be exported and imported by Mongoexport/mongoimport command, and the file CSV format is supported;

A SQL Server database can be exported to import the task, export data/import data, and its data can be saved to a CSV file as well.

So, can we use a CSV file to migrate data between the two types of databases?

The answer is YES! The answer is YES! The answer is YES!

In addition, the data is exported directly to the import, one order of magnitude faster than other tools.

That is , the fast migration of data can be achieved through the import and export functions of SQL Server and MongoDB itself .

importing data from SQL Server into MongoDB

Step 1 Select the specified database for "Export data"

Step 2 Select the exported data source information

Because execution is performed on the SQL Server server, the server name can be selected locally (. ), authentication can use window authentication (such as SQL Server to disable this window login method, then use SQL Server Authentication), and then select the database.

Step 3 Select Export Target (this is the key step )

Target be sure to select "flat File Destination"

Select browse for the export file name, select the export file type "CSV file (*.csv)" on the Open Interface

For example, the file is named SQLToMongoDB01

Click "Next"

Step 4 Specify Table copy or query

You can specify whether to copy one or more tables and views from the data source, or to copy the query knot from the data source through the T_SQL statement (you can specify the selection column or the data after the column two processing).

Step 5 Configuring the Flat File destination

In this step, select the table or view you want to export

If you want to make changes to the field field, you can select edit Map

Rename the column you want to modify, such as naming areacode in SQL Server and naming it in MongoDB Areacodemongodb

Then click "Next"

Step 6 Save and run the package

Click "Next" Directly

Step 7 Complete the wizard

Click "Done" directly

Data export Success

Export Data 773838 pen data.

Step 8 Copy the resulting file to the MongoDB server

Step 9 executes the Mongoimport command on the MongoDB server and imports the CSV data exported from SQL Server into MongoDB.

The Execute command is

/data/mongodb/mongobin344/bin/mongoimport--file/data/mongodb_back/sqltomongodb01.csv-h 172.X.X.XXX--port Port-- Type CSV--headerline-u user name-P ' password '-D testdba0906--authenticationdatabase authentication Database-C SQLToMongoDB01

Successful execution, import data 773838 documents. And can be seen soon, less than 15S.

This section tests the validation ok!

Importing data from MongoDB into SQL Server

Step 1 uses Mongoexport to export data from a MONGODB database

Execute command

/data/mongodb/mongobin344/bin/mongoexport-h 172.x.x.x--port Port--type csv-u user name-P ' password '-D testdba0906--authentica Tiondatabase Authentication Database-C sqltomongodb01-f field 1, Field 2, Field 3, Field 4, field 5-o/data/mongodb_back/mongodbtosql.csv

Step 2 uploads the CSV file exported from MongoDB to the SQL Server server.

Step 3 Import a CSV file into SQL Server

(This process, which is not the focus of this article, is no longer detailed and indicates only critical steps.) ）

Select the data source, select flat File source

Select source table and Source view "You can edit the mapping, modify the column name, modify the field type, modify the field length"

Step 4 Data Export success

Check that the amount of data is consistent with the source table data.

This section tests the validation ok!

Precautions

Note 1: Because the Mongoimport import file is in JSON format by default, the CSV file must be specified in the command--type CSV

Otherwise, the data in the file will not be recognized. Error message:

Failed:error Processing document #1: Invalid character ' X ' looking for beginning of value

(x represents the first character in a file)

Note 2 : Mongoimport The imported CSV file, be sure to execute the –headerline parameter, indicating that the first row is not imported. File first behavior column name in CSV format.

Otherwise, be sure to indicate the field properties, error message:

Error validating Settings:must Specify--fields,--fieldfile or--headerline to import the This file type

Note 3 : The CSV file cannot contain "" in the column data (Chinese double quotation marks).

Otherwise, when you import this row of data, you are prompted with an error message

Failed:read Error on Entry #XXXX: line XXXX, Column 110:bare "in Non-quoted-field

Note 4: If the data contains Chinese, you need to check whether the import becomes garbled.

In order to prevent garbled, the CSV document needs to be changed to Utf-8 encoding method. For example, using the edit with Notepad + + editor to open the file, save as Utf-8.

Note 5: MONGODB export CSV format Be sure to specify the field name through the –f parameter, which is responsible for the error:

Failed:csv mode requires a field list.

Note 6: MongoDB DB Data mode is free, and the document can be embedded, so it is difficult to import data from MongoDB into SQL Server, especially when the Chinese statement is included.

Note 7 : When SQL Server exports data, the field data cannot contain special characters such as nchar (9), nchar (32), nchar (160), or otherwise, when the export data generates a CSV file in the wrong times.

This article copyright belongs to the author, without the consent of the author, thank you!!!