Showing posts with label sql graph. Show all posts
Showing posts with label sql graph. Show all posts

Tuesday, May 09, 2017

SQL Server 2017: SQL Graph



With the CTP2 build of SQL Server 2017, you now have the ability to use SQL Graph

I decided to play around with it a little, I based the code here on the sample I found in Books On Line. To follow along, first create this database, you have to be on SQL Server 2017 CTP2 or higher in order for the code to work



CREATE DATABASE GraphDBTest;
GO

USE  GraphDBTest;
GO


In SQL Graph, you have node tables and edge table, here is the description for both tables from Books On Line


A node table represents an entity in a graph schema. Every time a node table is created, along with the user defined columns, an implicit $node_id column is created, which uniquely identifies a given node in the database. The values in $node_id are automatically generated and are a combination of object_id of that node table and an internally generated bigint value

It is recommended that users create a unique constraint or index on the $node_id column at the time of creation of node table, but if one is not created, a default unique, non-clustered index is automatically created.



An edge table represents a relationship in a graph. Edges are always directed and connect two nodes. An edge table enables users to model many-to-many relationships in the graph. An edge table may or may not have any user defined attributes in it. Every time an edge table is created, along with the user defined attributes, three implicit columns are created in the edge table:

Column nameDescription
$edge_idUniquely identifies a given edge in the database. It is a generated column and the value is a combination of object_id of the edge table and a internally generated bigint value. However, when the $edge_id column is selected, a computed value in the form of a JSON string is displayed. $edge_id is a pseudo-column, that maps to an internal name with hex string in it. When you select $edge_id from the table, the column name will appear as $edge_id_\<hex_string>. Using pseudo-column names in quereis is the recommended way of querying the internal $edge_id column and using internal name with hex string should be avoided.
$from_idStores the $node_id of the node, from where the edge originates.
$to_idStores the $node_id of the node, at which the edge terminates.
Similar to the $node_id column, it is recommended that users create a unique index or constraint on the $edge_id column at the time of creation of the edge table, but if one is not created, a default unique, non-clustered index is automatically created on this column. It is also recommended, for OLTP scenarios, that users create an index on ($from_id, $to_id) columns, for faster lookups in the direction of the edge.

Here is what a node table looks like, the only difference is the AS NODE at the end

CREATE TABLE Person (
  ID INT PRIMARY KEY,
  name VARCHAR(100)
) AS NODE;

Inserting into a node table is same as inserting into a regular table


INSERT INTO Person VALUES (1,'Denis');
INSERT INTO Person VALUES (2,'Shidhar');
INSERT INTO Person VALUES (3,'Geoff');
INSERT INTO Person VALUES (4,'Kevin');
INSERT INTO Person VALUES (5,'Liz');
INSERT INTO Person VALUES (6,'Melanie');

Let's see what the output looks like

SELECT * FROM Person


$node_id_A0703BD81C174451BEC7688009010A5B ID name
{"type":"node","schema":"dbo","table":"Person","id":0} 1 Denis
{"type":"node","schema":"dbo","table":"Person","id":1} 2 Shidhar
{"type":"node","schema":"dbo","table":"Person","id":2} 3 Geoff
{"type":"node","schema":"dbo","table":"Person","id":3} 4 Kevin
{"type":"node","schema":"dbo","table":"Person","id":4} 5 Liz
{"type":"node","schema":"dbo","table":"Person","id":5} 6 Melanie

As you can see, there is JSON in the $node_id column

The sys.tables view has 2 new columns, is_edge and is_node. If we query the sys.tables view now for the Person table we just created, you will see that is_node = 1

SELECT t.is_edge,t.is_node,*
FROM sys.tables t
WHERE name = 'Person'

Here is the result

is_edgeis_nodenameobject_idschema_idtype_desccreate_date
01Person9015782501USER_TABLE2017-05-09 08:55:30.513


Now we will add another table, this table will have some data about DB products



CREATE TABLE DBProduct (
  ID INT NOT NULL, 
  name VARCHAR(100)
) AS NODE;



INSERT INTO DBProduct VALUES (1,'SQL Server');
INSERT INTO DBProduct VALUES (2,'Cassandra');
INSERT INTO DBProduct VALUES (3,'Oracle');
INSERT INTO DBProduct VALUES (4,'FoundationDB');
INSERT INTO DBProduct VALUES (5,'MongoDB');
INSERT INTO DBProduct VALUES (6,'Vertica');
INSERT INTO DBProduct VALUES (7,'Volt');



And now it is time for our edge tables, we will have 2 of them, a likes table and a friendOf table

CREATE TABLE likes (rating INT) AS EDGE;
CREATE TABLE friendOf AS EDGE;


To insert into the likes table, we have to say which person likes what product. The person will go into the $from_id column, the DBProduct will got into the $to_id column. You $Node_id to grab the id to populate this table


The node_id for Person with an id of 1 will go into the $from_id column, the node_id for DBProduct with an id of 1 will go into the $to_id column. That looks like this

((SELECT $node_id FROM Person WHERE id = 1), 
       (SELECT $node_id FROM DBProduct WHERE id = 1),5);

Here are the insert statements to populate this table












INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE id = 1), 
       (SELECT $node_id FROM DBProduct WHERE id = 1),5);
INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE id = 1), 
       (SELECT $node_id FROM DBProduct WHERE id = 2),15);
INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE id = 2), 
      (SELECT $node_id FROM DBProduct WHERE id = 1),6);
INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE id = 3), 
      (SELECT $node_id FROM DBProduct WHERE id = 3),7);
INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE id = 4), 
      (SELECT $node_id FROM DBProduct WHERE id = 4),8);
INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE id = 5), 
      (SELECT $node_id FROM DBProduct WHERE id = 5),9);








Let's look at what it looks like in the likes edge table






SELECT * FROM likes






Here is what it looks like, as you can see it is a bunch of JSON.. (click on the image for a bigger version)





 




 Now we will also insert some data into the friendOf table, it is the same as with the likes table, lookup the node ids and insert the data






INSERT INTO friendof VALUES ((SELECT $NODE_ID FROM person WHERE ID = 1), 
    (SELECT $NODE_ID FROM person WHERE ID = 2));
INSERT INTO friendof VALUES ((SELECT $NODE_ID FROM person WHERE ID = 1), 
    (SELECT $NODE_ID FROM person WHERE ID = 5));

INSERT INTO friendof VALUES ((SELECT $NODE_ID FROM person WHERE ID = 2), 
    (SELECT $NODE_ID FROM person WHERE ID = 3));
INSERT INTO friendof VALUES ((SELECT $NODE_ID FROM person WHERE ID = 3), 
    (SELECT $NODE_ID FROM person WHERE ID = 1));
INSERT INTO friendof VALUES ((SELECT $NODE_ID FROM person WHERE ID = 4), 
    (SELECT $NODE_ID FROM person WHERE ID = 2));
INSERT INTO friendof VALUES ((SELECT $NODE_ID FROM person WHERE ID = 5), 
    (SELECT $NODE_ID FROM person WHERE ID = 4));








Now it is time to run some queries




This query will return all the products that Denis likes






-- Find DBProducts that Denis likes
SELECT DBProduct.name
FROM Person, likes, DBProduct
WHERE MATCH (Person-(likes)->DBProduct)
AND Person.name = 'Denis';






name

-------------

SQL Server

Cassandra



Not surprisingly, SQL Server is in that list



What about my friends? What DB products do they like?






-- Find DBProducts that Denis' friends like
SELECT DBProduct.name as DBName,person2.name  
FROM Person person1, Person person2, likes, friendOf, DBProduct
WHERE MATCH(person1-(friendOf)->person2-(likes)->DBProduct)
AND person1.name='Denis';








DBName                  name

---------------         ------------

SQL Server              Shidhar

MongoDB                 Liz





Okay, SQL Server and MongoDB, I can live with that



What about Liz, what does her friend Kevin like?






SELECT DBProduct.name as DBName,person2.name 
FROM Person person1, Person person2, likes, friendOf, DBProduct
WHERE MATCH(person1-(friendOf)->person2-(likes)->DBProduct)
AND person1.name='Liz';








DBName             name   

------   -------

FoundationDB     Kevin



FoundationDB? Didn't Apple buy that and then shut down the downloads?





So what product is there that both friends like?



To do that we specify that person1 is a friendOf person 2 

and both person1 and person2 like the same product. Since you cannot use the same table twice, you need to use an alias, in this case I have used l2 as an alias for the likes table








SELECT DBProduct.name as DBName,person1.name,person2.name 
FROM Person person1, Person person2, likes, friendOf, DBProduct, likes as l2
WHERE MATCH(person1-(friendOf)->person2-(likes)->DBProduct 
  AND person1-(l2)->DBProduct)








DBName        name       name

-----------

SQL Server    Denis      Shidhar



What a surprise, can you believe it, it is SQL Server!!







Let's take a look at some meta data, this time, we are adding some columns from the sys.columns view, I have added graph_type_desc and is_edge






select t.name, c.name, c.graph_type_desc,t.is_edge,t.is_node 
from sys.columns c
join sys.tables t on c.object_id = t.object_id
where graph_type is not null
order by t.name, c.name






Here is the output from that query







name name graph_type_desc is_edge is_node 


DBProduct $node_id_4C8D1144EF7745FD8BACDB7178353A65 GRAPH_ID_COMPUTED 0 1 


DBProduct graph_id_8FA26FC01AE94CF9921364ECEFB71621 GRAPH_ID 0 1 


friendOf $edge_id_FBBA05CB4E0F49E7919EC346DBB21F92 GRAPH_ID_COMPUTED 1 0 


friendOf $from_id_143FE9B1B3E942EB99FF8396B292CD19 GRAPH_FROM_ID_COMPUTED 1 0 


friendOf $to_id_DFF0DCFA07FE45E3A31E1925F5E6F886 GRAPH_TO_ID_COMPUTED 1 0 


friendOf from_id_71FB1C6C6D82428889276A07A9AF32E4 GRAPH_FROM_ID 1 0 


friendOf from_obj_id_4310B8C357D641EC990E91329687BDAA GRAPH_FROM_OBJ_ID 1 0 


friendOf graph_id_3722B217CA404686BE44855E883AA6FA GRAPH_ID 1 0 


friendOf to_id_D01976015F664F91A0A29819D79EE6A5 GRAPH_TO_ID 1 0 


friendOf to_obj_id_8C26534B00DF47FBA87FBED6D14C3201 GRAPH_TO_OBJ_ID 1 0 


likes $edge_id_CA70E11B83A44BD99DAD3B14EFCC2E08 GRAPH_ID_COMPUTED 1 0 


likes $from_id_FF1F089BA1224A00A1C94F68415C6B08 GRAPH_FROM_ID_COMPUTED 1 0 


likes $to_id_7728587E08684D94961B4F2234702B87 GRAPH_TO_ID_COMPUTED 1 0 


likes from_id_ACD968F7815C4A3A9B5B6C5506235EDE GRAPH_FROM_ID 1 0 


likes from_obj_id_185C6B5EB64F4C0A8E3E1CF65092F706 GRAPH_FROM_OBJ_ID 1 0 


likes graph_id_16575BE2C0764232B5B440524166DF15 GRAPH_ID 1 0 


likes to_id_D164826C35374D3E98EDBC3FA77457B3 GRAPH_TO_ID 1 0 


likes to_obj_id_373354D4D7014EC89771C48F961561BB GRAPH_TO_OBJ_ID 1 0 


Person $node_id_A0703BD81C174451BEC7688009010A5B GRAPH_ID_COMPUTED 0 1 


Person graph_id_40FFD8418C1343639EA62CA66B6CE414 GRAPH_ID 0 1 









You might have noticed that SQL Graph queries use old style joins

So instead of this






SELECT DBProduct.name as DBName,person1.name,person2.name 
FROM Person person1, Person person2, likes, friendOf, DBProduct, likes as l2






I can do this right?








SELECT DBProduct.name as DBName,person1.name,person2.name 
FROM Person person1
CROSS JOIN Person person2
CROSS JOIN likes
CROSS JOIN friendOf
CROSS JOIN DBProduct
CROSS JOIN likes as l2








Yes that works, both queries return 54432 rows



Will it work with the WHERE clause?






SELECT DBProduct.name as DBName,person1.name,person2.name 
FROM Person person1
CROSS JOIN Person person2
CROSS JOIN likes
CROSS JOIN friendOf
CROSS JOIN DBProduct
CROSS JOIN likes as l2
WHERE MATCH(person1-(friendOf)->person2-(likes)->DBProduct 
  AND person1-(l2)->DBProduct)








Msg 13920, Level 16, State 1, Line 164

Identifier 'person1' in a MATCH clause is used with a JOIN clause or APPLY operator. JOIN and APPLY are not supported with MATCH clauses.

Msg 13920, Level 16, State 1, Line 164

Identifier 'friendOf' in a MATCH clause is used with a JOIN clause or APPLY operator. JOIN and APPLY are not supported with MATCH clauses.

Msg 13920, Level 16, State 1, Line 164

Identifier 'person2' in a MATCH clause is used with a JOIN clause or APPLY operator. JOIN and APPLY are not supported with MATCH clauses.

Msg 13920, Level 16, State 1, Line 164

Identifier 'person2' in a MATCH clause is used with a JOIN clause or APPLY operator. JOIN and APPLY are not supported with MATCH clauses.

Msg 13920, Level 16, State 1, Line 164

Identifier 'likes' in a MATCH clause is used with a JOIN clause or APPLY operator. JOIN and APPLY are not supported with MATCH clauses.

Msg 13920, Level 16, State 1, Line 164

Identifier 'DBProduct' in a MATCH clause is used with a JOIN clause or APPLY operator. JOIN and APPLY are not supported with MATCH clauses.

Msg 13920, Level 16, State 1, Line 165

Identifier 'person1' in a MATCH clause is used with a JOIN clause or APPLY operator. JOIN and APPLY are not supported with MATCH clauses.

Msg 13920, Level 16, State 1, Line 165

Identifier 'l2' in a MATCH clause is used with a JOIN clause or APPLY operator. JOIN and APPLY are not supported with MATCH clauses.

Msg 13920, Level 16, State 1, Line 165

Identifier 'DBProduct' in a MATCH clause is used with a JOIN clause or APPLY operator. JOIN and APPLY are not supported with MATCH clauses.





Boooo ... that is a big fat nope







Also keep the following in mind

There are certain limitations on node and edge tables in this release:






    

  • Local or global temporary tables cannot be node or edge tables.
    • 

  • Table types and table variables cannot be declared as a node or edge table.
    • 

  • Node and edge tables cannot be created as system-versioned temporal tables.
    • 

  • Node and edge tables cannot be memory optimized tables.
    • 

  • Users cannot update the $from_id and $to_id columns of an edge using UPDATE statement. To update the nodes that an edge connects, users will have to insert the new edge pointing to new nodes and delete the previous one.
    • 






This was just a first look at Graph SQL, I will have to play around with it a little more to get more familiar with it and see if there are any use cases for me





In the meantime, visit Books On Line and start playing around with SQL Graph: https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-overview













Posted by



at





No comments:
  
























Labels:
,


















        

      









Subscribe to:
Posts (Atom)