Core Model of Sql

Let‘s talk about SQL in two appects: Data Model and Computation Model.

Data Model

　　On one hand, each row in a table is actually an "Object". Columns of a row are fields of an object. Someone may ask: in a programming language we can define recursive data structures, how can SQL achieve it? Yes, it can, only a bit circuitous.  For example, we have a TreeNode class:

    class TreeNode{

        TreeNode left_node;

        TreeNode right_node;

        int value;

    }

We can define a similar structure in sql table(where left_node_id points to another row whose id is "left_node_id"):

    create table TreeNode(

        node_id                   int

        left_node_id    int,

        right_node_id  int,

        value              int

    )

　On the other hand, a table is a "Hash Table". The key of this hash table is the primary key or the unique key, the value is the entire row.

   Finally, above "object" and "Hash Table " views do not  conflict, but can be unified.  When a program runs, all the objects in its memory actually compose a big hashtable. The mapping is just: Object Name->Object data in memory. So, each row is an object; the entire table is a hash table.

Computation Model

Group(group by, distribute by)

　　What indeed does a group operation do ?

　　1. divide rows into partitions.

　　    all the rows in the same partition has the same key.

　　2. use a function to do transformation on the partition.

　　　 when it‘s an aggregate function(such as sum/count/avg/min/max/first/last/order by ......), then:

　　　　　　rows in a partition=> one row

　　　 when it‘s not an aggregate function(such as "distribute by" operation), then:

　　　　　　rows in a partition=> rows in a partition

Filter(select where)

　　Silly simple: rows => rows (via where statement)

Join: set operation

      As is shown in "Data Model", a table is actually a Hash Table. So a table is also a Set. (Because a Set is really a special form of Hash Table, the elements of the Set, are keys of the Hash Table)

　　Join without any filter condition:  SetA + SetB

　　Inner Join:                                 SetA intersection SetB

　　Left/Right Outer Join:                 SetA - SetB

Hive and Map Reduce

　　The key-value nature(Hash Table) of sql table fits very well with MapReduce. That‘s why Hive was born.