Connecting SQL Server and Ultipa Graph

Feeling it a challenge to present webs of relationships directly? Tired of writing queries with multiple joins and conditions to track relationships between entities? If the answers are YES, a graph database like Ultipa is likely what you are looking for.

However, some are reserved to make the move due to concerns about the workload-intensive ETL process (data extraction, transformation and loading). Good news is that Ultipa has introduced a new Loader module into the versatile Ultipa Manager to assist with this process, and it now supports connecting from SQL Server to Ultipa. Data connection doesn’t have to require lots of late nights - Ultipa has made it easy for you.

Relational Data Model vs. Graph Structure

Transforming from relational data models into graph structures requires some changes in mindset and approach, but it’s not like entering a completely different world. Let’s have a look at a simple scenario in the banking industry.?

Here is the ER diagram of a system built on the SQL Server database, including tables of Branch, Card, Customer, Employee, Loan, Merchant and Transaction.?The relationships between these tables are formed using primary and foreign keys.?

Let's look at an example of remodeling the system into graph structure in Ultipa. Most tables become node schemas, except the Transaction table, which becomes the edge schema transfers between the Card and Merchant nodes. Other edge schemas are derived from foreign keys, for instance, the CustomerNumber column leads to the owns edge schema, pointing from the Customer node to the Card node. Other columns are saved as properties associated with each schema.

Data Connection

Now we can use Loader to import data from SQL Server into Ultipa.??

Step 1: Graph Settings

Specify the graph and node/edge schema where you want to load the data. The graph or schema will be created during the import process if you don’t have them in advance.

Three modes - Insert, Overwrite, and Upsert – are available for the flexible practical importing needs. Loader also offers advanced configurations like batch size, threads number, whether to halt importing in case of an error, whether to create nonexistent terminal nodes when importing edges, and so on.

Step 2: Connect to SQL Server

Establish the connection to your SQL Server by providing the host, port, credentials, and specifying the database you want to migrate data from.

Step 3: Data Mapping

After connecting to the SQL Server database, you can write simple SQL queries to retrieve data from it (Extraction). The Loader provides a mapping feature to clearly guide you in projecting the retrieved data to the node or edge properties in the graph (Transformation).

Example for Nodes

The table Loan in SQL Server is to be imported as the node schema Loan in Ultipa. Write this SQL query to retrieve all data in the Loan table:

select * from Loan        

Click Preview to run the SQL, which also initiates the automatic data mapping. You’ll see all columns in the table appear under Property, including the LoanID, LoanType, etc.; the Type of each property is also detected and auto populated.

You may edit each property as needed. For example, rename the property LoadID to _id, which is the unique identifier of nodes in Ultipa. Properties that are unnecessary for the Loan nodes are CustomerNumber and EmployeeID. They are used in SQL Server as foreign keys, but in graph the relations will be represented by edges, i.e.,

• Customer -> [applies_for] -> Loan
• Employee -> [reviews] -> Loan

Therefore, uncheck those two properties to ignore them during the import. Click Import when all the configuration is completed.

Example for Edges

To import the applies_for edges that appear between the Customer and Loan nodes, write this SQL query to join the Customer and Loan tables, returning the CustomerNumber and the corresponding LoanID:

select Customer.CustomerNumber, Loan.LoanID
from Customer join Loan
on Customer.CustomerNumber = Loan.CustomerNumber        

In Ultipa, the properties "_from" and "_to" indicate _id of the start and end nodes of an edge. Therefore, you may rename CustomerNumber to "_from", and LoanID to "_to".

Configuring for all node and edge schemas as described above. The Loader can then be executed for all node and edge schemas together or individually.

Query Comparison

To show comprehensive information related to a loan, such as the applicant (customer), the cards the applicant owns, the transactions of those cards, and the associated branch, the SQL query goes like this:?

with  
LoanAppInfo as( 
  	select Customer.CustomerName as Applicant, Customer.CustomerNumber,  Branch.BranchName, Employee.EmployeeName as Reviewer, Loan.LoanStatus 
  	from Customer join Loan on Customer.CustomerNumber = Loan.CustomerNumber 
  	join Employee on Employee.EmployeeID = Loan.EmployeeID 
  	join Branch on Branch.BranchID = Employee.BranchID 
  	where Customer.CustomerName = 'Reuben Mabrey' 
 ), 
 CardInfo as ( 
 	select Customer.CustomerNumber, COUNT(distinct Card.CardNumber) as CardCounts 
 	from Customer join Card on Customer.CustomerNumber = Card.CustomerNumber 
 	group by Customer.CustomerNumber 
 ) 
select Applicant, BranchName, Reviewer, LoanStatus, Card.CardNumber, CardCounts, Merchant.MerchantName, Merchant.Type 
from Card join LoanAppInfo on Card.CustomerNumber = LoanAppInfo.CustomerNumber 
join Transactions on card.CardNumber = Transactions.CardNumber 
join Merchant on Merchant.MerchantNumber = Transactions.MerchantNumber 
join CardInfo on Card.CustomerNumber = CardInfo.CustomerNumber 
order by Type         

However, if you want to know the same thing with Ultipa, the UQL query is much simpler due to its graph structure, you just need to traverse through the edges:

n({_id == '5baafyO2kH'} as n1)
    .e().n({@Loan})
    .e().n({@Employee})
    .e().n({@Branch})
    .e().n(n1)
    .e().n({@Card})
    .e().n({@Merchant}) as p 
return p{*}         

Ultipa offers great visualization to the query results, making it easy to see that the customer (displayed as a blue node) owns three cards, while two of the cards have deals with merchants whose (risk) types are high as level 5.?

Conclusion?

In this article, we describe how smooth the migration from SQL Server to Ultipa Graph can be. With the aid of the Loader module integrated in Ultipa Manager, you can easily re-model and migrate your data without the need for extra coding.?