Mongo非关系型数据库关系化

2019独角兽企业重金招聘Python工程师标准>>>

Mongo接入分表策略梳理：

mongo基本信息：

 

• 连接信息：
  • host：选填 默认 localhost
  • port：选填 默认 27017
  • username:  选填 默认 admin
  • password： 选填
• 数据库对象关系：
  • 一个数据库连接对应多个数据库
  • 一个数据库对应多个collection
  • 一个collection对应多个document
• 接入流程：
  1. 填写连接信息
  2. 选择数据库
  3. 选择collection，并填写相应的document模板
  4. 用户预览根据模板生成的bdp表结构schema
  5. 用户对预览表结构schema进行自定义修改
  6. 根据schema取数同步

 

 

技术要点：

抽象mongo文档的数据结构关系，假设有如下模板文档，凡出现1对多的关系则分表，使用父文档的主键（索引）作为外键依赖：

{
    "_id" : 0,
    "hometown" : "Hollywood Hills",
    "familyName" : "Partridge",
    "familyMembers" : [ 
        {
            "attributes" : [ 
                {
                    "name" : "instrument",
                    "value" : "singer"
                }, 
                {
                    "name" : "role",
                    "value" : "mom"
                }
            ],
            "age" : 42,
            "firstname" : "Shirley"
        }, 
        {
            "attributes" : [ 
                {
                    "name" : "instrument",
                    "value" : "guitar"
                }, 
                {
                    "name" : "role",
                    "value" : "son"
                }
            ],
            "age" : 18,
            "firstname" : "Keith"
        }, 
        {
            "attributes" : [ 
                {
                    "name" : "instrument",
                    "value" : "keyboard"
                }, 
                {
                    "name" : "role",
                    "value" : "sister"
                }
            ],
            "age" : 16,
            "firstname" : "Laurie"
        }
    ]
}

 

将会生成如下table列表，分为三张表Demo，Demo.familyMembers， Demo.Demo.familyMembers.attributes, 并分别与父文档通过_id或者ArrayIndex建立联系：

[
  {
    "name": "Demo",
    "collection": "Demo", 
    "uniq_keys": [
      "_id"
    ], 
    "base_path": "", 
    "columns": [
      {
        "name": "_id", 
        "path": "_id"
      }, 
      {
        "name": "hometown", 
        "path": "hometown"
      }, 
      {
        "name": "familyName", 
        "path": "familyName"
      }
    ]
  }, 
  {
    "name": "Demo.familyMembers", 
    "collection": "Demo", 
    "base_path": "familyMembers", 
    "columns": [
      {
        "name": "_id", 
        "path": "_id"
      }, 
      {
        "name": "familyMembers._index", 
        "path": "familyMembers._index"
      }, 
      {
        "name": "age", 
        "path": "familyMembers.age"
      }, 
      {
        "name": "firstname", 
        "path": "familyMembers.firstname"
      }
    ], 
    "uniq_keys": [
      "_id", 
      "familyMembers._index"
    ]
  }, 
  {
    "name": "Demo.familyMembers.attributes", 
    "collection": "Demo", 
    "base_path": "familyMembers.attributes", 
    "columns": [
      {
        "name": "_id", 
        "path": "_id"
      }, 
      {
        "name": "familyMembers._index", 
        "path": "familyMembers._index"
      }, 
      {
        "name": "familyMembers.attributes._index", 
        "path": "familyMembers.attributes._index"
      }, 
      {
        "name": "name", 
        "path": "familyMembers.attributes.name"
      }, 
      {
        "name": "value", 
        "path": "familyMembers.attributes.value"
      }
    ], 
    "uniq_keys": [
      "_id", 
      "familyMembers._index", 
      "familyMembers.attributes._index"
    ]
  }
]

3、3张表根据schema取数据如下：

Demo
**************************************************
[0, u'Hollywood Hills', u'Partridge']


Demo.familyMembers
**************************************************
[0, 0, 42, u'Shirley']
[0, 1, 18, u'Keith']
[0, 2, 16, u'Laurie']


Demo.familyMembers.attributes
**************************************************
[0, 0, 0, u'instrument', u'singer']
[0, 0, 1, u'role', u'mom']
[0, 1, 0, u'instrument', u'guitar']
[0, 1, 1, u'role', u'son']
[0, 2, 0, u'instrument', u'keyboard']
[0, 2, 1, u'role', u'sister']

 

转载于:https://my.oschina.net/Cleverdada/blog/850584