sql - 如何从 sql server 将数据导入 mongodb？

Question

如何将数据从 sql server 导入 mongodb？

我在 sql 数据库中有这些表和以下列

州、市、市区

StatesId NameCitiesId Name StatesIdCitiAreaId Name CityId

我想要 mongoDb 中的数据。

{      State:"Orissa",      Cities:{                CitiName:"Phulbani",                CitYArea:{                              "Phulbani","Phulbani2","Pokali","Madira"                         }             }}

是否有任何工具或者我需要为这种数据转换编写代码？

Answer 1

有几种可能的方法来解决这个问题，用你最喜欢的语言编写代码，使用适当的 API 来选择数据、转换数据，然后将其插入 MongoDB。

您也可以使用 SQL、MongoDB 查询语言和 shell 来完成。一种直接的方法是通过 SQL 选择平面数据，将其转储到 CSV 文件中，将其导入 MongoDB 并使用聚合框架将其转换为您想要的格式。

如果您有幸使用支持数组或其他将行分组为单个列表类型的方式的数据库，那么您可以进行单个选择并将其转换为 JSON 或 MongoDB 插入语句。

对于这些示例，我将假设您需要与每个城市的文档等效的格式:

{
      State:"Orissa",
      City:{
           Name:"Phulbani",
           Area:[
                 "Phulbani","Phulbani2","Pokali","Madira"
                ]
           }
}

RDBMS 中的示例数据:

asya=# select * from states;
 id |     name      
----+---------------
  1 | California
  2 | New York
  3 | Massachusetts
(3 rows)

asya=# select * from cities;
 id |     name      | states_id 
----+---------------+-----------
  1 | Los Angeles   |         1
  2 | San Francisco |         1
  3 | San Diego     |         1
  4 | New York      |         2
  5 | Brooklyn      |         2
  6 | Buffalo       |         2
  7 | Boston        |         3
(7 rows)

asya=# select * from cityarea;
 id |        name        | city_id 
----+--------------------+---------
  1 | Beacon Hill        |       7
  2 | Backbay            |       7
  3 | Brookline          |       7
  4 | Park Slope         |       5
  5 | Little Italy       |       4
  6 | SOHO               |       4
  7 | Harlem             |       4
  8 | West Village       |       4
  9 | SoMa               |       2
 10 | South Beach        |       2
 11 | Haight Ashbury     |       2
 12 | Cole Valley        |       2
 13 | Bunker Hill        |       1
 14 | Skid Row           |       1
 15 | Fashion District   |       1
 16 | Financial District |       1
(16 rows)

使用数组的简单方法:

SELECT 'db.cities.insert({ state:"' || states.name || '", city: { name: "' || cities.name || '", areas : [ ' || array_to_string(array_agg('"' || cityarea.name || '"'),',') || ']}});'
FROM states JOIN cities ON (states.id=cities.states_id) LEFT OUTER JOIN cityarea ON (cities.id=cityarea.city_id) GROUP BY states.name, cities.name;

为您提供可以直接进入 MongoDB shell 的输出:

 db.cities.insert({ state:"California", city: { name: "Los Angeles", areas : [ "Financial District","Fashion District","Skid Row","Bunker Hill"]}});
 db.cities.insert({ state:"California", city: { name: "San Diego", areas : [ ]}});
 db.cities.insert({ state:"California", city: { name: "San Francisco", areas : [ "Haight Ashbury","South Beach","SoMa","Cole Valley"]}});
 db.cities.insert({ state:"Massachusetts", city: { name: "Boston", areas : [ "Beacon Hill","Brookline","Backbay"]}});
 db.cities.insert({ state:"New York", city: { name: "Brooklyn", areas : [ "Park Slope"]}});
 db.cities.insert({ state:"New York", city: { name: "Buffalo", areas : [ ]}});
 db.cities.insert({ state:"New York", city: { name: "New York", areas : [ "Little Italy","West Village","Harlem","SOHO"]}});

如果您不支持数组或列表类型，则更长的方法是选择连接数据:

asya=# SELECT states.name as state, cities.name as city, cityarea.name as area 
FROM states JOIN cities ON (states.id=cities.states_id) 
LEFT OUTER JOIN cityarea ON (cities.id=cityarea.city_id);
     state     |     city      |        area        
---------------+---------------+--------------------
 California    | Los Angeles   | Financial District
 California    | Los Angeles   | Fashion District
 California    | Los Angeles   | Skid Row
 California    | Los Angeles   | Bunker Hill
 California    | San Francisco | Cole Valley
 California    | San Francisco | Haight Ashbury
 California    | San Francisco | South Beach
 California    | San Francisco | SoMa
 California    | San Diego     | 
 New York      | New York      | West Village
 New York      | New York      | Harlem
 New York      | New York      | SOHO
 New York      | New York      | Little Italy
 New York      | Brooklyn      | Park Slope
 New York      | Buffalo       | 
 Massachusetts | Boston        | Brookline
 Massachusetts | Boston        | Backbay
 Massachusetts | Boston        | Beacon Hill
(18 rows)

我在 cityarea 上使用了左外连接，因为在我的示例数据中，我有一个城市没有列出任何区域，但我想获取所有州、城市对，即使没有列出任何区域。

您可以以交互方式或通过命令行转储它(为您的 RDBMS 使用适当的语法)。我会以交互方式进行:

asya=# \a
Output format is unaligned.
asya=# \f
Field separator is "|".
asya=# \f ,
Field separator is ",".
asya=# \t
Showing only tuples.
asya=# \o dump.txt                                                                                                                              
asya=# SELECT states.name as state, cities.name as city, cityarea.name as area 
FROM states JOIN cities ON (states.id=cities.states_id) 
LEFT OUTER JOIN cityarea ON (cities.id=cityarea.city_id);
asya=# \q

我现在有一个逗号分隔的文件，其中包含州、城市和地区作为三个字段。我可以通过 mongoimport 实用程序将它加载到 MongoDB 中:

asya$ mongoimport -d sample -c tmpcities --type csv --fields state,city,area < dump.txt 
connected to: 127.0.0.1
2014-08-05T07:41:36.744-0700 check 9 18
2014-08-05T07:41:36.744-0700 imported 18 objects

现在要转换为我想要的格式，我使用聚合:

mongo sample
MongoDB shell version: 2.6.4
connecting to: sample1
> db.tmpcities.aggregate(
{$group:{_id:"$city", state:{$first:"$state"}, areas:{$push:"$area"}}},
{$project:{state:1,_id:0,city:{name:"$_id", areas:"$areas"}}},
{$out:'cities'})
> db.cities.find({},{_id:0})
{ "_id" : "Boston", "state" : "Massachusetts", "areas" : [ "Brookline", "Backbay", "Beacon Hill" ] }
{ "_id" : "New York", "state" : "New York", "areas" : [ "West Village", "Harlem", "SOHO", "Little Italy" ] }
{ "_id" : "Buffalo", "state" : "New York", "areas" : [ "" ] }
{ "_id" : "Brooklyn", "state" : "New York", "areas" : [ "Park Slope" ] }
{ "_id" : "San Diego", "state" : "California", "areas" : [ "" ] }
{ "_id" : "San Francisco", "state" : "California", "areas" : [ "Cole Valley", "Haight Ashbury", "South Beach", "SoMa" ] }
{ "_id" : "Los Angeles", "state" : "California", "areas" : [ "Financial District", "Fashion District", "Skid Row", "Bunker Hill" ] }