Contact Tracing with Oracle Graph Studio Notebooks

In this article, we will explore the use of Oracle Graph notebooks built into the Autonomous Database. To do so, we will use Oracle Graph to explore a contact tracing scenario:

You are the owner of an establishment. Given a dataset of customers, some of whom have been diagnosed with a contagious virus, and people those customers came into contact with, can we determine how much the virus spread at our location? What information can we derive that would be useful for ourselves, and for our customers?

Prerequisites

• This blog assumes you have followed these steps to deploy and Autonomous Database with a Graph enabled user.

Load Data into Autonomous Database

Before we start making graphs, we need to make sure that we have database tables with data loaded into the Autonomous Database.

1. Download this zip file and extract the contents. There should be a contacts.csv and users.csv files, which we will use now, as well as a contact_graph.dsnb file which we will use later.

2. Access Database Actions from the Oracle Cloud Infrastructure Console as the Graph enabled user.

3. Select SQL.

4. Execute the following PL/SQL code to create tables for this dataset.

create table users_table(
   user_id         NUMBER(4),
   first_name      VARCHAR2(50),
   last_name       VARCHAR2(50),
   gender          VARCHAR2(50),
   date_of_birth   TIMESTAMP(3),
   phone_number    VARCHAR2(50),
   infected        VARCHAR2(50),
   date_diagnosis  TIMESTAMP(3),
   constraint pk_uid primary key (user_id)
);
create table contacts(
   contact_id      NUMBER(4),
   reporting_user  NUMBER(4),
   contact_user    NUMBER(4),
   contact_start   TIMESTAMP(3),
   contact_end     TIMESTAMP(3),
   infectious_start TIMESTAMP(3),
   infectious_end  TIMESTAMP(3),
   constraint pk_cid primary key (contact_id),
   constraint FK_rep_user 
      FOREIGN key (reporting_user) references users_table(user_id),
   constraint FK_con_user 
      FOREIGN key (contact_user) references users_table(user_id)
);

5. Refresh the Navigator on the left, right click USERS_TABLE and select Data Loading > Upload Data.

6. Upload the users.csv file. Click Next.

7. In the Data Mapping section, change the date formatting to:

YYYY-MM-DD HH24:MI:SS.FF

8. Click Next, and Finish.

9. Right click the CONTACTS and select Data Loading > Upload Data.

10. Upload the contacts.csv file. Click Next.

11. In the Data Mapping section, change the date formatting to:

YYYY-MM-DD HH24:MI:SS.FF

12. Click Next, and Finish.

Create Graph

1. From your OCI console, navigate to your Autonomous Database Instance, and select Tools > Graph Studio.

2. Log in as your graph user.

3. Select the graphs on the navigation bar, and click create.

4. Select the contacts and users tables, then click Next.

5. Your modeler should look like the following image. Click Next.

6. Click Create Graph

7. You will be prompted to enter a graph name and model name. I will keep the model name as default and call the graph “contact_graph”.

8. After a few minutes, you should see that the environment was created and the graph has been loaded into memory.

Visualize Graph

1. Navigate to the Notebooks page in graph studio, and click Import.

2. Drag in the contact_graph.dsnb file from the zip file you downloaded earlier.

3. Click Import.

4. Click the run button at the top of the notebook to run all paragraphs.

5. Explore the notebook. It has already been created with a series of sql and pgql queries with notes to help you understand what each query is doing, and how it can help us answer our initial questions for this use case.

Please learn more about Oracle Graph from:

• Medium (= all articles with Oracle Graph tag)
• Stack Overflow (= questions with oracle-graph tag)
• Slack AnDOUC (= invitation link, please visit #graph)
• Slack OracleDevRel (= invitation link, please visit #oracle-db-graph)