> For the complete documentation index, see [llms.txt](https://csse-uwa.gitbook.io/data-warehousing-lab-sheets/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://csse-uwa.gitbook.io/data-warehousing-lab-sheets/week-9-import-data-from-a-relational-database-into-neo4j.md). # Week 9 - Import data from a relational database into Neo4j In this lab, we'll learn how to import data from a relational database into Neo4j. By the end of this session, you should be able to design and create your graph for Project 2. ## 1. Dataset {% file src="/files/f1g6eZ2sshM2SxxwJlvU" %} Week 9 Lab Dataset (3 CSV files) {% endfile %} It is a star schema of a sales warehouse with one fact table and two dimension tables:

## 2. Design and ETL ### 2.1 Graph Database Design **Step 1:** Design a graph database schema using the [Arrows Tool](https://arrows.app/) that reflects the warehouse's star schema. Think about the general principles that * A row is a node * A table name is a label name * A join table or foreign key is a relationship Your schema might look like this:

Relationship labels and property names are self-designed based on the data. Feel free to use different names. ### **2.2**. Prepare CSV Files Nodes Let's implement it in a Python dataframe.

import pandas as pd
# load CSV files into pandas DataFrames. 
# Remember to change the data path
sales_table = pd.read_csv('./data/sales.csv')
products_table = pd.read_csv('./data/products.csv')
shops_table = pd.read_csv('./data/shops.csv')

# use ".head()" to review your tables
# sales_table.head()
# products_table.head()
# shops_table.head()

After comparing the graph schema (Figure 1) with the data, we can find that "shop" nodes come from the "shops\_table" and "product" nodes come from the "products\_table". However, the "sale" nodes require some ETL processing: ```python # Keeping only specified columns using loc method sales_node_table = sales_table.loc[:, ['Sale ID', 'Date', 'Quantity', 'Unit price']] # export to csv sales_node_table.to_csv('./data/sales_node.csv', index=False) ``` Now, we have all CSV files for nodes. Let's import them into Neo4j. ### **2.3.** Import CSV Files The screenshots below are from Neo4j Desktop. If you're using the cloud version, the steps are similar. {% hint style="info" %} We assume you have created an instance and a database for this lab; if you haven't, please refer to the Week 8 lab to create them. {% endhint %} #### **2.3.1.** Import CSV Files Click the folder icon to open the directory.

Move CSV files lto the "**import"** directory. (Note: if the "import" directory doesn't exist, you can create it yourself.)

#### **2.3.2.** Create Nodes Let's start by creating "Shop" nodes. Under the "Query" sub-menu, write the code below and click the "run" button on the right. The variable name "row" represents each row in the imported CSV file, in this case, "shops.csv". You can rename "row" to another variable name if you prefer. Use "row\.HEADER\_NAME" to refer to each header. For instance, row: "Shop ID" refers to the "Shop ID" column in "shops.csv". **The HEADER\_NAME must exactly match the CSV header, including case sensitivity.** The properties before the ":" are the ones you've named in your schema design (Figure 1).

```cypher LOAD CSV WITH HEADERS FROM 'file:///shops.csv' AS row CREATE (d:Shop { shopID: row.`Shop ID`, city: row.City, stateOrRegion: row.`State or region`, country: row.Country, shopSize: row.`Shop size` }) ``` You have 2 ways to check nodes you created. * Solution 1: Under the "query" menu, click "Database overview"

You can click the "shop" bubble to check nodes.

There are 3 different views: Graph, Table and Raw. * Solution 2: Click "Explore", then click "Show me a graph".

But only one node is showing in this solution.

Follow a similar process for creating "Product" and "Sale" nodes: ```cypher LOAD CSV WITH HEADERS FROM 'file:///products.csv' AS row CREATE (d:Product { productID: row.`Product`, category: row.Category, subcategory: row.`Sub category`, productSize: row.Size, purchasePrice: row.`Purchase price`, color: row.Color, brand:row.Brand }) ``` ```cypher LOAD CSV WITH HEADERS FROM 'file:///sales_node.csv' AS row CREATE (d:Sale { saleID: row.`Sale ID`, date: row.Date, quantity: row.Quantity, unitPrice:row.`Unit price` }) ```

**Your turn: Assign different colours to each label. You can refer to the Week 8 lab sheet for guidance. For example:**

### **2.4**. Prepare CSV files for Relationships #### **2.4.1.** Create Relationships Based on the graph schema (Figure 1), we would like to create the relationship "CONTAINS" from "Sale" to "Product", "SOLD\_IN" from "Sale" to "Shop": ```python # as you can see, the relationship between "Sale" and "Product" can obtain from "sales_table" contain_table = sales_table.loc[:, ['Sale ID', 'Product']] contain_table.to_csv('./data/rel_contains.csv', index=False) ``` ```python # as you can see, the relationship between "Sale" and "Shop" can also obtain from "sales_table" sold_in_table = sales_table.loc[:, ['Sale ID', 'Shop']] sold_in_table.to_csv('./data/rel_sold_in.csv', index=False) ``` #### **2.4.2.** Import CSV Files and Create Relationships Copy "rel\_sold\_in.csv" and "rel\_contains.csv" into the "import" folder and run the following code. "->" indicates the direction of the relationship. If you want to create an undirected relationship, use `CREATE (s)-[:CONTAINS]-(p)`. ```cypher LOAD CSV WITH HEADERS FROM 'file:///rel_contains.csv' AS row MATCH (s:Sale {saleID: row.`Sale ID`}), (p:Product { productID: row.Product}) CREATE (s)-[:CONTAINS]->(p) ``` ```cypher LOAD CSV WITH HEADERS FROM 'file:///rel_sold_in.csv' AS row MATCH (s:Sale {saleID: row.`Sale ID`}) MATCH(p:Shop { shopID: row.Shop}) CREATE (s)-[:SOLD_IN]->(p) ``` {% hint style="info" %} When running the two queries above, why does the first query produce a warning but the second one runs without any warning? {% endhint %}

Now that you've created your graph database from a relation database, **you might wonder if you can have additional node labels**. For instance, separating "country" from "shop" or "category" and "subcategory" from "product". The answer is "Yes". There's no fixed answer in design; it depends on how you intend to use your graph. Refer to the week 8 lab sheet, can you * Show nodes only * Show nodes with relationships * Delete relationships first, then delete all nodes * Delete all nodes and relationships together. ### 2.5 Create nodes and relationships using the GUI {% hint style="warning" %} Note that this method is not permitted in Project 2. Marks will be awarded for the Cypher queries used to create nodes and relationships. {% endhint %} #### 2.5.1 Import CSV files Under the "Import", you can import all CSV files.

#### 2.5.2 Start sketching a model Click "Add node label". A new interface like Arrow\.app will display.

#### 2.5.3 Setup nodes Type a label name, select a file, set up properties and ID.

You have two methods to set up properties: * Map from file

* Manually set up. Click the "+" icon, manually type the property names, data types and select columns.

#### 2.5.4 Delete property If a property needs to be deleted, select the property name, click delete.

#### 2.5.5 Create a relationship You can create nodes and relationships in the interface the same way you do in Arrow\.app.

{% hint style="success" %} Set up the sales node by yourself. {% endhint %} #### 2.5.6 Setup relationships In the graph, the direction should be from sales to product; we can reverse the direction by clicking "Reverse direction".

Type the relationship label, select the CSV file and map the nodes.

#### 2.5.7 Run import Click "Run import" to import nodes and relationships.

You should see the results, and you can explore the results.

{% hint style="info" %} Your turn: Create the shop nodes and the relationship between the shop and sales. {% endhint %} Next week, we'll explore querying graph databases using Cypher.