Create a Graph from Existing Relational Data using Graph Studio

Introduction

In this lab you will explore Graph Studio and learn about how you can create graphs from existing relational data stored in your Autonomous Data Warehouse - Shared Infrastructure (ADW) or Autonomous Transaction Processing - Shared Infrastructure (ATP) instance.

Estimated Time: 30 minutes.

Objectives

Learn how to model a graph from the Sales History (SH) sample data set
Learn how to monitor a graph creation job
Learn how inspect created graphs and graph models

Prerequisites

The following lab requires an Autonomous Data Warehouse - Shared Infrastructure or Autonomous Transaction Processing - Shared Infrastructure account.
It assumes you have completed the previous lab which explains how you can create a graph enabled user.

Task 1: Create the Required Views

In this lab we use the Sales History (SH) sample schema to create our demo graph. The SH schema is available in all Autonomous Database instances. Create Views for CUSTOMERS, TIMES, CHANNELS, PRODUCTS, and PROMOTIONS from the SH tables, using only a subset of the columns from those tables.

CREATE OR REPLACE VIEW SH_CUSTOMERS_VIEW (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_EMAIL, CUST_GENDER, CUST_CITY, CUST_STATE_PROVINCE, COUNTRY_ID)
    DEFAULT COLLATION "USING_NLS_COMP"  AS
    select cust_id, cust_first_name, cust_last_name, cust_email, cust_gender, cust_city, cust_state_province, country_id from sh.customers;

CREATE OR REPLACE  VIEW SH_CHANNELS_VIEW (CHANNEL_ID, CHANNEL_DESC, CHANNEL_CLASS) DEFAULT COLLATION "USING_NLS_COMP"  AS
    select channel_id, channel_desc, channel_class from sh.channels ;

CREATE OR REPLACE VIEW SH_TIMES_VIEW (ID, TIME_ID, DAY_NAME, DAY_NUMBER_IN_MONTH, CALENDAR_MONTH_NUMBER,  CALENDAR_YEAR)
    DEFAULT COLLATION "USING_NLS_COMP"  AS select rownum id, time_id, day_name, day_number_in_month, calendar_month_number, calendar_year from sh.times ;

CREATE OR REPLACE VIEW SH_PRODUCTS_VIEW (PROD_ID, PROD_NAME, PROD_DESC, PROD_CATEGORY, PROD_STATUS)
    DEFAULT COLLATION "USING_NLS_COMP"  AS select cast(prod_id as number) as prod_id, prod_name, prod_desc, prod_category, prod_status from sh.products;

CREATE OR REPLACE VIEW SH_PROMOTIONS_VIEW (PROMO_ID, PROMO_NAME, PROMO_SUBCATEGORY, PROMO_CATEGORY, PROMO_COST)
    DEFAULT COLLATION "USING_NLS_COMP"  AS select cast(promo_id as number) promo_id, promo_name, promo_subcategory, promo_category, promo_cost from sh.promotions;

CREATE OR REPLACE VIEW SH_SALES_VIEW (SALE_ID, CUST_ID, PROD_ID, PROMO_ID, DATE_OF_SALE_ID, CHANNEL_ID, AMOUNT_SOLD, QUANTITY_SOLD)
    DEFAULT COLLATION "USING_NLS_COMP" AS
    select rownum sale_id, s.cust_id, s.prod_id, s.promo_id, tv.id as date_of_sale_id, s.channel_id, s.amount_sold, s.quantity_sold from sh.sales s, sh_times_view tv where s.time_id = tv.time_id;

Run the copied code to create views for CUSTOMERS, TIMES, CHANNELS, PRODUCTS, and PROMOTIONS

Add the relevant Primary Keys to the Views

ALTER VIEW SH_CUSTOMERS_VIEW ADD CONSTRAINT SH_CUSTOMER_VIEW_PK PRIMARY KEY (CUST_ID) DISABLE ;

ALTER VIEW SH_CHANNELS_VIEW ADD CONSTRAINT SH_CHANNEL_VIEW_PK PRIMARY KEY (CHANNEL_ID) DISABLE ;

ALTER VIEW SH_TIMES_VIEW ADD CONSTRAINT SH_TIMES_VIEW_PK PRIMARY KEY (ID) DISABLE ;

ALTER VIEW SH_PRODUCTS_VIEW ADD CONSTRAINT SH_PRODUCT_VIEW_PK PRIMARY KEY (PROD_ID) DISABLE;

ALTER VIEW SH_PROMOTIONS_VIEW ADD CONSTRAINT SH_PROMO_VIEW_PK PRIMARY KEY (PROMO_ID) DISABLE;

ALTER VIEW SH_SALES_VIEW ADD CONSTRAINT SH_SALES_VIEW_PK PRIMARY KEY (SALE_ID) DISABLE;

Add the relevant Primary Keys to the Views

Add the relevant Foreign Keys for the Sales View

ALTER VIEW SH_SALES_VIEW ADD CONSTRAINT SH_SALES_PROMO_VIEW_FK FOREIGN KEY (PROMO_ID) REFERENCES SH_PROMOTIONS_VIEW (PROMO_ID) DISABLE;

ALTER VIEW SH_SALES_VIEW ADD CONSTRAINT SH_SALES_PRODUCT_VIEW_FK FOREIGN KEY (PROD_ID) REFERENCES SH_PRODUCTS_VIEW (PROD_ID) DISABLE;

ALTER VIEW SH_SALES_VIEW ADD CONSTRAINT SH_SALES_CUST_VIEW_FK FOREIGN KEY (CUST_ID) REFERENCES SH_CUSTOMERS_VIEW (CUST_ID) DISABLE ;

ALTER VIEW SH_SALES_VIEW ADD CONSTRAINT SH_SALES_CHANNEL_VIEW_FK FOREIGN KEY (CHANNEL_ID) REFERENCES SH_CHANNELS_VIEW (CHANNEL_ID) DISABLE ;

ALTER VIEW SH_SALES_VIEW ADD CONSTRAINT SH_SALES_TIMES_VIEW_FK FOREIGN KEY (DATE_OF_SALE_ID) REFERENCES SH_TIMES_VIEW (ID) DISABLE ;

Add the relevant Foreign Keys for SH_SALES_VIEW

Task 2: Log into Graph Studio

Task 3: Select the Tables to Create the Graph From

The following screenshot shows Graph Studio user interface with the menu, or navigation, icons on the left. They navigate to the Home, Models, Graphs, Notebooks, Templates, and Jobs pages respectively.
Click on the Graphs menu icon, and click Create Graph.

In this lab we use the views created from the Sales History (SH) sample schema to create our demo graph. The SH schema is available in all Autonomous Database instances. You can, however, apply the steps of this lab to any relational data available in your database, regardless of where the data originated from. All the schemas and tables - including views - that you have access to will show up as possible input tables at the start of the modeling workflow.
Enter SH_PGVIEW_GRAPH as the graph name, then click Next. The description field is optional. That graph name is used throughout the next lab. Do not enter a different name because then the queries and code snippets in the next lab will fail.
Open up the GRAPHUSER schema and double click on the SH_PRODUCTS_VIEW table.

You see some details about this table, like all the columns it has and their types, as well as which column is the primary key:
On the bottom left, click on the GRAPHUSER.SH_PRODUCTS_VIEW - Data tab.

You see a preview of the values of that table. By default, it displays the first ten rows, but you can increase the sample size. You can also search for any value in that table and paginate through the preview.
For this lab, we choose all views we created earlier as input for our graph model. If your Graph user only contains those views, you can select GRAPHUSER and then click the button in the middle to move all views to the selected section on the right. Otherwise, select the relevant views, and then click the button in the middle to move all views to the selected section on the right.
Click on the Next button on the top right to move to the next step. Graph Studio analyzes the foreign key constraints and suggests a possible mapping from the selected views to a property graph structure. This may take a few seconds. Once done, you see all the input you selected on the left, and the mapping to vertex and edge tables on the right. Also ensure PGQL Property Graph is selected to create a graph using PGQL.

This model can be modified if necessary.

Task 4: Edit your Graph Model

Click on the SH_CHANNELS_VIEW vertex table.

We will create this graph by converting each row of the SH_CHANNELS_VIEW input into a vertex in the property graph. Vertex and edges can have a label to categorize them into different types of vertices and edges. Each label can have a different set of properties associated with it. The label to the name of the input table is automatically set. That way we can later easily identify of which type each vertex or edge in our generated graph is. In addition, we see that each column of the input table was converted into a property in our graph. In property graph, properties are arbitrary key/value pairs associated with each vertex or edge in our graph. By mapping all column values into properties, we make sure that all input data is preserved in our graph.

However, the graph modeler allows you to customize how data is mapped and remove unnecessary tables and or columns not relevant for your analysis. Reducing the amount of data to be processed will reduce the amount of processing resources and storage required for graph analysis. We removed unnecessary columns when creating the views for this graph, so there is nothing to remove in this instance.
Rename the vertex label to CHANNELS and make sure you click on the check mark to save the change.

Rename the other vertex tables:

Table 1: Get Started with Graph Studio | Lab 3: Create a Graph from the SH tables

Current View Name

Renamed Vertex Label

SH_SALES_VIEW

SALES

SH_CUSTOMERS_VIEW

CUSTOMERS

SH_TIMES_VIEW

TIMES

SH_PROMOTIONS_VIEW

PROMOTIONS

SH_PRODUCTS_VIEW

PRODUCTS

Click on the SH_SALES_VIEW_SH_CUSTOMERS_VIEW edge table.

As you can see from the Source Vertex and Destination Vertex information, this edge type maps all relations from SH_SALES_VIEW to SH_CUSTOMERS_VIEW. In other words, it models bought by relationships. By default, this edge label is SH_SALES_VIEW_SH_CUSTOMERS_VIEW.

Note: If the edge table is not visible you can change the size of the upper pane. Use the splitter (the horizontal line with three dots ... separating the two panes) to increase its size. Click on the splitter and drag it down or up.
Rename the edge label to TO_CUSTOMER:
Repeat for the other all other edge tables: