SQL到Cassandra数据模型结构 [英] SQL to Cassandra Data Model Structure

问题描述

原谅我可以解释一些可能在其他地方解释，但我没有找到一个简单和简单的转换/解释的SQL模型到Cassandra模型。

是为组织中的员工详细信息和记录设计DB结构的用例。在SQL（有多年的经验），我可以使用规范化技术建模，但进入NoSQL的世界，它需要我有一段时间，坚持设计DB的NoSQL，因此我在这里（为了更好的理解）

有人可以将这个SQL模型转换为NoSQL（Cassandra）模型，从而给了很多新手（像我一样）一个简单和平凡的SQL转换到NoSQL迁移。

>

因为SO工作在一个概念先尝试然后询问，所以我想到了一个结构。

由于Cassandra中的数据可以被非规范化，我想到了这个结构。

  Emploee（ColumnFamily）= {
01234（EmployeeId）：{
EmpName：Jack，
mail： [e-mail]，
phone：[9999900000，8888888888]，
DOB：4/1/91，
联系人： ：XYZ2，City：ABC，Pincode：PQR}，
UnitID：{UnitName：XYZ，UnitHead：ABC}，
RoleID：{ ：Manage，Band：Something}，
}，
01235（EmploeeId）：{
EmpName：Jackyyy，
 [e-mail]，
phone：[99565600000，88888846468]，
DOB：4/1/91，
Contact：{Strreet ：XYZ2，City：ABC1，Pincode：PQR}，
UnitID：{UnitName：XYZ1，UnitHead：ABC1}，
RoleID：{ ：Faculty，Band：Something}，
}，
 
等等... 
} 
 
 
项目ColumnFamily）= {
1213（ProjectId）：{
EmpID：[01234,01235]，
StartDate：4/1/2001，
EndDateDate ：4/1/2012，
ClientName：Apple 
Description：Something，
}，
 
等等... 
} 
 
 
注意事项（ColumnFamily）= {
1213（DetailId）：{
EmpID：01234，
 SwipeInTime：Something，
SwipeOutTime：Something，
Status：Apple 
}，
 
等等... 
} b $ b  

首先请让我知道，如果这个结构是正确的。如果是，我将如何设计以下查询？

  1）选择电话号码= 
 
 2）选择居住在XYZ位置的员工; 
 
 3）选择年龄>> 40年; 
 
 4）选择名称为XYZ的经理的员工; 
 
 5）选择每天工作超过1小时的员工; 
 
 6）获取为客户端'Apple'工作的所有员工的名字（而不是ID）。 
  

让我知道如果我能提供更清晰的问题！

解决方案

您的结构不正确，因为您无法表达您的任何6项查询： - （

Cassandra建模的主要规则是：从你的查询和denormalize开始，在你的case，你会有6个表employee_by_phone，employee_by_location，employee_by_age等等
< a href =http://www.datastax.com/dev/blog/basic-rules-of-cassandra-data-modeling =nofollow> http://www.datastax.com/dev/blog/但是如果你有很多多条件查询，Cassandra（Datastax企业版）有SolR扩展，这将让你表达更丰富的查询。在这种情况下，你的模型可能是正确的。

Forgive me for asking something that is probably explained elsewhere, but I didnt found a simple and plain conversion/explanation of SQL model to Cassandra Model.

Lets say I've a use-case of designing a DB structure for employee details and records in a organization. In SQL(having years of experience), I could have modelled it using normalization techniques, but coming into the world of NoSQL, it would take me sometime to have hold over designing DB for NoSQL, hence I'm here (for better understanding).

Can someone transform this SQL model into a NoSQL(Cassandra) model, thereby giving a lot of newbies(like me) a simple and plain transformation of SQL to NoSQL migration.

Since SO works on a concept Try First and then ask, so Ive thought of a structure as well. Let me know if that works well.

Since data can be denormalized in Cassandra, I thought of this structure.

Emploee(ColumnFamily) = {
    "01234"(EmployeeId) : { 
        "EmpName" : "Jack",
        "mail" : "[e-mail]",
        "phone" : ["9999900000","8888888888"],
        "DOB": 4/1/91,
        "Contact":{ "Street" : XYZ2 , "City":ABC, "Pincode":PQR},
        "UnitID":{ "UnitName" : XYZ , "UnitHead":ABC},
        "RoleID":{ "Designation" : Manage , "Band":Something},
    },
    "01235"(EmploeeId) : { 
        "EmpName" : "Jackyyy",
        "mail" : "[e-mail]",
        "phone" : ["99565600000","88888846468"],
        "DOB": 4/1/91,
        "Contact":{ "Strreet" : XYZ2 , "City":ABC1, "Pincode":PQR},
        "UnitID":{ "UnitName" : XYZ1 , "UnitHead":ABC1},
        "RoleID":{ "Designation" : Faculty , "Band":Something},
    },

    and so on...
}


Projects(ColumnFamily) = {
    "1213"(ProjectId) : { 
        "EmpID" : [01234,01235],
        "StartDate" : 4/1/2001,
        "EndDateDate": 4/1/2012,
        "ClientName": Apple
        "Description": "Something",
    },

    and so on...
}


Attentance Detail(ColumnFamily) = {
    "1213"(DetailId) : { 
        "EmpID" : 01234,
        "SwipeInTime" : Something,
        "SwipeOutTime": Something,
        "Status": Apple
    },

    and so on...
}

Firstly please let me know, if this structure is correct. If yes, how would I design queries for the following?

1) Select employee whose phone number = something;

2) Select employees who lives in 'XYZ' location;

3) Select employees whose age is > 40 years ;

4) Select employee whose Designation is a 'Manager' of Unit Name 'XYZ' ;

5) Select employees who work for over 1o hours a day;

6) Get names(not IDs) of all employees wh were working for client 'Apple';

Let me know If I can provide more clarity on the question!!!

解决方案

Your structure is not correct because you won't be able to express any of your 6 queries :-(

The main rule of Cassandra modeling is: start from your queries and denormalize. In your case, you would have 6 tables employee_by_phone, employee_by_location, employee_by_age and so on. http://www.datastax.com/dev/blog/basic-rules-of-cassandra-data-modeling

However if you have a lot of multi-criteria queries like these, Cassandra (Datastax Enterprise edition) has SolR extension which will let you express richer queries. In this case your model may be right.

这篇关于SQL到Cassandra数据模型结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！