microsoft / graphview

GraphView is a DLL library that enables users to use SQL Server or Azure SQL Database to efficiently manage graphs.

GraphView

GraphView is a DLL library that enables users to use SQL Server or Azure SQL Database to manage graphs. It connects to a SQL database locally or in the cloud, stores graph data in tables and queries graphs through a SQL-extended language. It is not an independent database, but a middleware that accepts graph operations and translates them to T-SQL executed in SQL Server or Azure SQL Database. As such, GraphView can be viewed as a special connector to SQL Server/Azure SQL Database. Developers will experience no differences than the default SQL connector provided by the .NET framework (i.e., SqlConnection), only except that this new connector accepts graph-oriented statements.

Features

GraphView is a DLL library through which you manage graph data in SQL Server (version 2008 and onward) and Azure SQL Database (v12 and onward). It provides features a standard graph database is expected to have. In addition, since GraphView relies on SQL databases, it inherits many features in the relational world that are often missing in native graph databases.

GraphView offers the following major features:

• Graph database A graph database in GraphView is a conventional SQL database. The graph database consists of one or more types of nodes and edges, each of which may have one or more properties.

• Data manipulations GraphView provides an SQL-extended language for graph manipulation, including inserting/deleting nodes and edges. The syntax is similar to INSERT/DELETE statements in SQL, but is extended to accommodate graph semantics.

• Queries GraphView's query language allows users to match graph patterns against the graph in a graph database. The query language extends the SQL SELECT statement with a MATCH clause, in which the graph pattern is specified. Coupled with loop/iteration statements from T-SQL, the language also allows users to perform iterative computations over the graph. Overall, the query language is sufficiently expressive and easy to use, so that query languages supported by existing native graph databases can easily be expressed.

• Indexes To accelerate query processing, GraphView also allows users to create indexes. All indexes supported by SQL Server and Azure SQL Database are available, including not only conventional B-tree indexes but also new indexing technologies such as columnstore indexes.

• Transactions All operations in GraphView are transaction-safe. What is more, there is no limit on a transaction’s scope; a transaction can span nodes, edges or even graphs.

• SQL-related features GraphView inherits many administration features from SQL Server and Azure SQL Database. Below is a short list of features that are crucial to administration tasks:

  1. Access control. GraphView uses the authentication mechanism of SQL Server to control accesses to graph databases. A user can access a graph database if SQL Server says so.
  2. Replication & backup. GraphView stores graph data in a SQL database. A replication/backup of the database will result in a replication/backup of all graph data.
  3. Cloud-related features. When using GraphView to connect to Azure SQL Database, you enjoy many features of cloud computing, such as geo-replication and multi-tenancy.

Dependency

GraphView needs Microsoft.SqlServer.TransactSql.ScriptDom.dll. Download and install SQL Server Data Tools.

Build

Prerequisites

• Visual Studio, programming languages -> Visual C# -> Common Tools for Visual C#
• Install SQL Server Data Tools

Build

• Clone the source code: git clone https://github.com/Microsoft/GraphView.git
• Open GraphView.csproj
• Set the configuration to "release"
• Build the project and generate GraphView.dll

Getting Started

GraphView is a DLL library. You reference the library in your application and open a graph database by instantiating a GraphViewConnection object with the connection string of a SQL database.

using GraphView;
......
string connectionString = "Data Source= (local); Initial Catalog=GraphTesting; Integrated Security=true;";
GraphViewConnection gdb = new GraphViewConnection(connectionString);
try {
  // Connects to a database. 
  gdb.Open(true);
}
catch(DatabaseException e) {
  // Exception handling goes here
}

When the connection string points to an Azure SQL Database instance, you open a graph database in Azure:

using GraphView;
......
var sqlConnectionBuilder = new SqlConnectionStringBuilder();
sqlConnectionBuilder["Server"] = "tcp:graphview.database.windows.net,1433";
sqlConnectionBuilder["User ID"] = "xxx";
sqlConnectionBuilder["Password"] = "xxx";
sqlConnectionBuilder["Database"] = "GraphTesting";
GraphViewConnection gdb = new GraphViewConnection(sqlConnectionBuilder.ToString());
try {
  gdb.Open(true);
}
catch(DatabaseException e) {
  // Exception handling goes here
}

Once you open a database, you send graph queries through the connector and retrieve results using the .NET standard data reader DataReader if needed.

try {
  gdb.Open(true);
  string queryString = "......";       // A graph query
  GraphViewCommand gcmd = new GraphViewCommand(queryString, gdb);
  DataReader dataReader = gcmd.ExecuteReader();
  While (dataReader.Read()) {
    // Retrieve results through DataReader
  }
  dataReader.Close();
  gcmd.Dispose();
  gdb.Close();
}

Please read the user manual for the full language specification, functionality and programming API's.

Get Help

User manual GraphView's user manual is the first place to get help. It introduces the full query language, functionality and programming API's. It also includes many code samples.

GitHub The GitHub repository contains a short introduction. You can use Github's issue tracker to report bugs, suggest features and ask questions.

Email If you prefer to talk to us in private, write to [email protected]

Dev branches

• Clean-Up
• DocDB-merge-temp-partition

License

GraphView is under the MIT license.

graphview's Issues

Update User Manual

I've tried to follow the User Manual as mentioned on your ReadMe: http://research.microsoft.com/pubs/259290/GraphView%20User%20Manual.pdf
I found some issues with the documentation.
For example to delete nodes, we should use: gdb.DropNodeTable("DROP TABLE EmployeeN");
Instead of: gdb.CreateNodeTable("DROP TABLE EmployeeNode");

For the query creating the table EmployeeNode, I think an "@" is missing at beginning of the query.
I wish if you could revise and update the docs as it will accelerate the adoption of the project.

Thanks !

Contiguous Integration and Code Formatter

Glad to see GraphView is getting noticed to more people now! However, now that we have published our code in open source community, we will have to pay more attention to our code style and quality. Although our code is mainly a research prototype, we still need a mechanism to do some testing and checking, esp. for a system project like this.

It would be great if we can have a CI integrated into our project, which can run unit tests and style checker automatically whenever we have a new commit. Jenkins and Travis CI would be two greet options. This style checker will also be great to help us clean up our code.

Jianhong

Data operations don't handle apostrophes in string literals

For example:
INSERT NODE INTO RailwayStations (Id, StationName) VALUES (1,'St. Peter's Square')
fails because of the apostrophe in St Peter's. Have tried various escape sequences but none seem to work (including the usual t-sql double quote).

This repo is missing important files

There are important files that Microsoft projects should all have that are not present in this repository. A pull request has been opened to add the missing file(s). When the pr is merged this issue will be closed automatically.

Microsoft teams can learn more about this effort and share feedback within the open source guidance available internally.

Merge this pull request

Update documentation to include dependency on SQLCLR

This project requires CLR integration to be enabled on SQL Server and the default for that feature is disabled. The documentation should include instructions for enabling the CLR:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Support Azure SQL Elastic Tools

Awesome work Guys!

I would like to know if GraphView will ever support Azure SQL Elastic Pool/Tools so we can apply sharding and multi-tenancy (maybe using RLS?) to the GraphView.

Thanks!

Performance on large graphs?

Hi,
thank you for the dll. It is great.

What about performance on large graphs?
e.g. 10000 nodes? 1 000 000 nodes?

CREATE PROCEDURE Error

I have a procedure.
When I create it, will throw a error:

CREATE PROCEDURE AddTrade
@buyerId nvarchar(50),
@platform INT,
@mobile varchar(20),
@telephone varchar(20),
@orderId varchar(50),
@fullname nvarchar(20)
AS
BEGIN
    if not exists(select 1 from [Account] where id=@buyerId)
    begin
        INSERT INTO [Account](id) VALUES(@buyerId)
    end
    if not exists(select 1 from [Mobile] where id=@mobile)
    begin
        INSERT INTO [Mobile](id) VALUES(@mobile)
    end
    if @telephone <> @mobile and not exists(select 1 from [Mobile] where id=@telephone)
    begin
        INSERT INTO [Mobile](id) VALUES(@telephone)
    end
    if not exists(select 1 from [Trade] where orderId=@orderId)
    begin
        INSERT INTO [Trade](orderId,[platform],fullname) VALUES(@orderId,@platform,@fullname)
    end

    INSERT EDGE INTO Account.HasTrade
           SELECT a,t FROM Account a , Trade t
               WHERE a.id = @buyerId AND t.orderId = @orderId ;
    INSERT EDGE INTO Account.UseMobile
           SELECT a,t FROM Account a , Mobile t
               WHERE a.id = @buyerId AND t.id = @mobile ;


    INSERT EDGE INTO Mobile.HasTrade
            SELECT a,t FROM Mobile a , Trade t
                WHERE a.id = @mobile AND t.orderId = @orderId ;


    INSERT EDGE INTO Mobile.HasAccount
            SELECT a,t FROM Mobile a , Account t
                WHERE a.id = @mobile AND t.id = @buyerId ;




    INSERT EDGE INTO Trade.UseAccount
            SELECT t,a FROM Account a , Trade t
                WHERE a.id = @buyerId AND t.orderId = @orderId ;

    INSERT EDGE INTO Trade.UseSecMobile
            SELECT t,a FROM Mobile a , Trade t
                WHERE a.id = @telephone AND t.orderId = @orderId ;
    INSERT EDGE INTO Trade.UseMainMobile
            SELECT t,a FROM Mobile a , Trade t
                WHERE a.id = @mobile AND t.orderId = @orderId ;

    if not (@telephone=@mobile) -- the same as @telephone<>@mobile
    begin
  INSERT EDGE INTO Account.UseMobile
            SELECT a,t FROM Account a , Mobile t
                WHERE a.id = @buyerId AND t.id = @telephone ;

        INSERT EDGE INTO Mobile.HasTrade
            SELECT a,t FROM Mobile a , Trade t
                WHERE a.id = @telephone AND t.orderId = @orderId ;

        INSERT EDGE INTO Mobile.HasAccount
                SELECT a,t FROM Mobile a , Account t
                    WHERE a.id = @telephone AND t.id = @buyerId ;
    end

END"

And the if block diff position in code ,the error is different.

I resolve this by the code:

    if (@telephone=@mobile)
    begin
               return 
    end
INSERT EDGE INTO Account.UseMobile
            SELECT a,t FROM Account a , Mobile t
                WHERE a.id = @buyerId AND t.id = @telephone ;

        INSERT EDGE INTO Mobile.HasTrade
            SELECT a,t FROM Mobile a , Trade t
                WHERE a.id = @telephone AND t.orderId = @orderId ;

        INSERT EDGE INTO Mobile.HasAccount
                SELECT a,t FROM Mobile a , Account t
                    WHERE a.id = @telephone AND t.id = @buyerId ;

GraphView in NET Core.

Is the library moving to net core in the near future?

Thanks!

How to write this Graph Query?

Hi,

We need your help to write one query using MATCH clause.

We have the following data definitions.

Person node table has three edge properties
Name (NodeID)
LivesIn (Edge) to Country node table
Reads (Edge) to Book node table
Rides (Edge) to Car node table

Country node table
Name (NodeID)
Book node table
Name (NodeID)
Car node table
Name (NodeID)

We would like to use MATCH clause for traversing and filtering results.

Query :

Return Persons (Person name) and their details (Reads - Book name, Rides - Car name) who Lives in US.

Some persons who lives in US has no edges for Reads or Rides.
We would like to list such persons with NULL value in Book name and Car name columns.

How do we accomplish this in Graph queries using Match clause.

The below query does not list the persons who DOES NOT have edges to Book and Car. I think MATCH internally maps to INNER JOIN.

SELECT X.NAME AS [PERSON NAME], Y.NAME AS [BOOK NAME], Z.NAME AS [CAR NAME]
FROM PERSON X, BOOK Y, CAR Z, COUNTRY A
MATCH X-[LivesIn]->A, X-[Reads]->Y, X-[Rides]->Z
WHERE A.NAME = 'US';

We can use LEFT JOIN in the FROM clause, but as a user, we do not know the structure of the node table.

Can you please let us know how to achieve this functionality.

Kind Regards,
Pavan Kumar D.

Query syntax Question

This query works well:

select x.id, y.orderId, y.fullname from mobile x,trade y match x-[hastrade]->y

This exception is reported:

select x.id, y.orderId, y.fullname from mobile x,trade y match x-hastrade->y

I think they should be equivalent.

How to alter the definition of node type table?

After creating a node type table, how can I add new properties / edge types?