Build a graph database with Amazon Neptune Part 2 - Load data
Published: 2022-09-15 09:04:50

## Introduction

The previous [blog](https://dev.to/aws-builders/build-a-graph-database-with-amazon-neptune-part-1-launch-aws-cloudformation-stack-43k6) explored different use cases for using a graph database such as customer journey mapping, social networking and fraud detection.

A graph database is useful for reporting or describing relationships within your dataset.

This is my solution overview below:

![solution](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dizu9zpjkow56k0eyhdi.jpg)

In this second blog, we will load data into Amazon Neptune.

## Recap - Create a graph database

A graph database was created using AWS CloudFormation.The Neptune DB cluster is shown below:


![Ineptune cluster](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/olrfaeotq5qqxb76rmqf.jpg)

### Learning Outcomes

* Create an IAM policy to allow access to data files in S3 bucket
* Create an Amazon S3 bucket
* Load your data
* Query data using Amazon Athena
* Load data into Amazon Neptune
* Query data 

### Prerequisites to load data

The instructions to allow an IAM Role and Amazon S3 bucket to access the Amazon Neptune DB cluster are outlined [here](https://docs.aws.amazon.com/neptune/latest/userguide/bulk-load-tutorial-IAM.html).

* Create an IAM role to allow Amazon Neptune to access Amazon S3 resources
* Add IAM Role to an Amazon Neptune Cluster
* Create the Amazon S3 VPC Endpoint

### Tutorial 1: Create an IAM policy to allow access to data files in S3 bucket

We will follow the instructions provided [here](https://docs.aws.amazon.com/neptune/latest/userguide/bulk-load-tutorial-IAM.html) to create permissions for Amazon Neptune to access the S3 bucket.

Step 1: Login to the AWS Management Console with your [IAM Admin User account](https://dev.to/aws-builders/creating-your-first-iam-admin-user-and-user-group-in-your-aws-account-machine-learning-part-1-3cne).


Step 2: Navigate to the IAM console by typing 'IAM' in the search bar.


![iam](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6y78oh3mxbq1b20kuqxg.jpg)

Click **Roles** on the left-hand-menu.


![role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/812xuh7ecjl9kqktyudz.jpg)


Step 3: Click **Create role**.


![create role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9wemteac3elr3wuzts13.jpg)

Step 4: Type **S3** under 'Use cases for other AWS Services' and click **Next**.


![S3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wttyjsazm5jeclxaut2a.jpg)

Step 5: Under the filter, type *S3readonlyaccess* and click **Next**.


![read only access](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zh27wdrqge2q5rlr1pgj.jpg)

Step 6: Create a **role name** and enter a description


![role name](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x5iqs8oln8prb0ruvmnl.jpg)

Click **Create role**.


![create](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gq80gdf94nxgldk45iwj.jpg)

Step 7:After the role has been created, type in the search bar *NeptuneLoadFromS3* and click the hyperlink.


![type role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2agacq77w6h466hgh9mj.jpg)

Step 8: Click **Edit Trust Relationship** on the 'Trust Relationship tab'.


![edit](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lrl6ey8zcml7sgs457l1.jpg)

Copy and paste this code:


![copy code](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5w2i1m3w1yi8ythus5d4.jpg)


Click **Update policy**


![Upate policy](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wlpt0yb8gc7vebbu7g4c.jpg)


### Tutorial 2: Add IAM Role to an Amazon Neptune Cluster

Step 1: Add this IAM role to the Neptune cluster.

Navigate to the Neptune console by typing *Neptune* in the search bar and click on the left-hand menu **Databases**


![neptune search](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w73j0i1emwaqav1hj0ci.jpg)

Step 2: Select the cluster and under the **Actions** drop-down menu, choose **Manage IAM roles**



![manage iam](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8glleynzneyuisro53su.jpg)

Step 3: Select the new IAM role that was created in *Tutorial 1: Step 6* and click **Done**.


![iam done](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lypzlxwl3cr5uy6faltn.jpg)

Step 4: The cluster is now available.


![avaialble](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1lyhrlpfdcfmfp93a1aa.jpg)


### Tutorial 3: Create the Amazon S3 VPC Endpoint

You may follow the instructions here from the [Amazon Neptune User Guide](https://docs.aws.amazon.com/neptune/latest/userguide/bulk-load-tutorial-IAM.html#bulk-load-tutorial-IAM-add-role-cluster) to create an Amazon S3 VPC endpoint.

Step 1: In the search bar, type 'VPC'


![VPC](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xhluhyerj6y2m40ijp2c.jpg)

Step 2: On the right-hand side click **Endpoints**


![endpoints](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xjhrru0t9io45ppd3bxt.jpg)

Step 3: Click **Create endpoint**


![create endpoints](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tqtd9c6gw0fjm8kiqlsc.jpg)

Step 4:In this section, enter 's3' in the search field and select 'com.amazonaws.ap-southeast-2.s3'


![region endpoint](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4yqqgklg22mdxelmbnq9.jpg)

Step 5:Choose the VPC to create your endpoint:


![VPC endpoint](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bor2d3mnziuzads5p96p.jpg)

Step 6: Check the box of the route table associated with the subnet

Step 7: Click **Choose endpoint**.

### Gremlin load data format

Data loaded into Amazon Neptune as a property graph is queried using [Gremlin](https://docs.aws.amazon.com/neptune/latest/userguide/bulk-load-tutorial-format.html) must be in a *csv* format.

The *csv* file must be saved as a [UTF-8 format](https://docs.aws.amazon.com/neptune/latest/userguide/bulk-load-tutorial-format-gremlin.html)

#### Dataset 

I created this dataset from the [Amazon Community Builders Directory](https://aws.amazon.com/developer/community/community-builders/community-builders-directory/?cb-cards.sort-by=item.additionalFields.cbName&cb-cards.sort-order=asc&awsf.builder-category=*all&awsf.location=*all&awsf.year=*all) which contains a record of 457 Community Builders in Asia-Pacific region with details such as:

* Name
* City
* Country
* Category
* Year
* Location 

![Directiry](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5igxg91ckjtk3j50uiqt.jpg)

This is a preview of a subset of data:


![Isample](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dnq26jfrcj1d74u46r2h.jpg)

In this dataset, **Name** represents *Nodes* and other variables describing relationships are the **Edges**.


### Tutorial 4: ETL Process and Data Prep for Loading data into Amazon Neptune

The following [tutorial](https://github.com/aws-samples/amazon-neptune-samples/blob/master/gremlin/etl-from-relational-model/README.md) will help to transform data into Gremlin data load format.

Step 1: Create an AWS account and sign in with IAM Admin user. You may follow instructions [here](https://dev.to/aws-builders/getting-started-with-aws-a-sweet-journey-5cjj) to create an account if you do not have one.

Step 2: Create a S3 bucket and name it *aws-community-builders- apj* with lowercase letters and select a region that is geographically close to you, for example *Asia-Pacific(Sydney)-ap-southeast-2*.

You may follow the instructions [here](https://dev.to/aws-builders/creating-your-first-amazon-s3-bucket-and-new-feature-release-2c03) to create your first S3 bucket.


![name bucket](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/74gjruvbusi957751fnp.jpg)
The S3 bucket was successfully created as shown below:

![created bucket](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pul7wfyqczsc97z5sh5e.jpg)

Step 3: Create two folders and name them **raw** and **processed**


![create folder](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3l0fv2dad4m2kv7lcz81.jpg)


![folders](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oddbaqlzhyxrrzyo3feq.jpg)

Step 4: Add the 'apj_cb_list.csv' file into the **raw** folder and click **upload**.


![upload file](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kgs5y896ef6mmgf7mqz0.jpg)
Th csv file has successfully uploaded into S3 bucket in the raw folder and stored in a standard class.

![raw object](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6j1hy492ni9b3inr2rqi.jpg)

Upload the *csv* file in UTF-8 format into the S3 bucket


![utf](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nqag2ac96hxznv37cxc7.jpg)


The file has been added to the S3 bucket in the 'raw' folder.


![utf uploaded](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/98mge51zitgqxizrwkyh.jpg)

Step 5: Navigate to **AWS Glue** console to create a crawler and obtain the schema within the Glue data catalog.


![glue navigation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/arhsveyxdq8f9on00mmk.jpg)

Click on the left hand menu **crawler**.

![crawler](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qqziagrmujtgvxrhzh6k.jpg)**.

You may follow all the steps from this ETL tutorial [here](https://dev.to/aws-builders/how-to-etl-with-aws-glue-and-amazon-glue-studio-to-transform-your-data-part-2-3ng5) to understand how to create a crawler and access a table from the AWS Glue data catalog.

Step 6: A crawler named 'aws-community-builder-apj-crawler'
was created as shown below.


![runing crawler](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eulz6v5gvy6umyma511p.jpg)

After the crawler job has completed, you will see a 'ready' status and a table is created.

![crawler is ready](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ujl1ajkgxd8mkvh8nn0l.jpg)

Step 7: Browse the data catalog by clicking **Table** on the left-hand menu under the database **Community** that was previously created in the ETL job.


![inspect table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/phckroy87a4kx6yugejl.jpg)

Step 8: Click into the 'aws-community-builder-apj-crawler' and click **edit**

The *csv* file saved in a UTF-8 file format was selected in the data store.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e3m2z4uruzp6il8pqwp3.jpg)

Step 9: Re-run the crawler to determine the schema for the dataset.


![re-run the crawler](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dbfqmiybtmmfwiwf4qva.jpg)

The crawler job has completed as reflected with a 'ready' status.


![crawler is redy](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zkub1slrn0bk8dfsn9td.jpg)

Step 10: Select **Tables** on the left-hand side and check the box for the second table to check the schema.


![Tables](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ic6z4y9g9aro47tr7cdd.jpg)

Check the schema of the table.


![schema](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7etudcwoltwwd0pkp63i.jpg)


Step 11:  On the top-right corner, click **Edit schema** so that you can edit the names of the column headers as per the original data set.


![edit schema](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oj7t1wenxhglq73pbtf9.jpg)

Rename the column names and then click **Save**.


![remane](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kg54zwywb30zy5xcrkdm.jpg)


![save schema](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cyf3zpzq16tckghxsuk9.jpg)

Step 12: Type 'Athena' in the search bar and navigate to the Amazon Athena console.


![Inavigate to athena](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8t3sxj4emd3begicugre.jpg)

Step 13: You will be able to view the 'Community' database that was created from AWS Glue and also two tables.


![tables](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3g3n9904k5ykaq6f1oze.jpg)


Step 14: Click **Create** and select the option to create a table from a data source from **S3 bucket data**

Provide a name of the table *APJ_CB*

Under **Column Details** enter the schema from the table 'apj_cb_list_utf.csv'


Step 15: After the table ' apj_cb' table is created, click on the three dots to preview the table.


![Ipreview tablen](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/92i3fv140pi57khifk4z.jpg)

You can inspect the result set of the first 10 records.


![result set](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cayjo5w6y9zibsnxece7.jpg)

Step 16: Create a graph data model from the 'apj_cb' table


![data modeling](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k6otanhpxtpxqo9uo3d9.jpg)

In the next blog (Part 3), we will explore Gremlin statements in the Neptune data model in greater detail with [four components](https://docs.aws.amazon.com/neptune/latest/userguide/gremlin-explain-background-statements.html):

* subject (S) - relationship of two vertices (ie.nodes)
* predicate (P) - edge label
* object (O) - target vertex (i.e. node)
* graph (G)

Step 17: Use Amazon Athena to run a SQL query named **Edge query** and export the data out as a *csv* file by clicking **Download results**.


![edge query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z2xwylw8cl72n026h9bg.jpg)


![download results](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0oqatlqcg30aebri2rzy.jpg)


Step 18: Use Amazon Athena to run a SQL query named **Node query** and export the data out as a *csv* file by clicking **Download results**.


![node query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/acirv0kjzodxjw74zf7x.jpg)

![node output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nuzzsizlxtjmiwmyi4e2.jpg)

Step 19: Navigate to your S3 bucket which will store the Amazon Athena exported CSV files from the two SQL queries with the pre-fixes identical to query names *node_query* and *apj-cb-edges* see below:


![S3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tviczz5wo7lecg2k7fwe.jpg)

Create a new bucket to move these CSV files into a new S3 Bucket for Bulk data loading e.g *workingbucketapjneptune*


![workingbucket](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7411iozfehffvvbinhxc.jpg)

Create two folders to move these CSV files.


![create folder](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a839z5z39obl6v4kwih5.jpg)

Step 20: Load data into a Neptune instance by following the instructions [here](https://docs.aws.amazon.com/neptune/latest/userguide/bulk-load-data.html)

Navigate to the  Neptune console by typing the word *Neptune* into the search bar.


![neptune](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zzqnv2usdlu295475st9.jpg)

On the left-handside menu, select **Databases** to find the Neptune Cluster endpoint under the tab **Connectivity and Security**.



![connect](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m5z67jw8i6om1t9manyc.jpg)


Step 21: Make a REST API call to the Neptune loader to commence the bulk loading process.

Connect to EC2 instance using SSH for Windows by following the Amazon documentation [here](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/putty.html)
or you may follow the steps provided by Stephane Maarek in his Youtube video **SSH to EC2 Instances using Windows Tutorial**

{% embed https://www.youtube.com/watch?v=jv-dgOfFN4o %}.

I was able to successfully connect to a Linux instance for Windows using PuTTy as shown below:


![Putty](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h2n0wq0oz42otv3a190e.jpg)


![AMI](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ievkq7yjeq3tgrq26sww.jpg)

Step 22: Navigate to the Amazon Neptune console, click on **Cluster** and go to **Action** and select **Manage IAM role**. Check to ensure that you have an existing IAM role to allow Neptune to connect to S3 buckets for the bulk load API call (i.e. NeptuneFromS3role)

Add the role from the drop-down menu and add this additional IAM role.

![rol](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hthol9mfm9ydz8o42tk4.jpg)

Step 23: Make a REST API call to load the data into Neptune.

The sample code includes the following parameters for a data load.

![Bulk load](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t5jdjrmqnk5lpwcsm2sf.jpg)  

### Query Data

You may query data from a Neptune DB cluster or use a Graph Notebook linked to Amazon Sagemaker using the following languages:

* Gremlin
* Open Cypher
* SPARQL


![notebook](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y87uye5oyp8kg30wdx48.jpg)


Open the sample graph notebooks with tutorials provided by Amazon created from Neptune cluster in a jupyter notebook hosted on Python 3. You can create your own jupyter notebook.

![open](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bg8hldcvrxv72a952ei0.jpg)

Write gremlin code for your vertex and edge statements.

![edge statement](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m6svce7a090hell05iww.jpg)

Until the next lesson, happy learning ! 😁

### Next Lesson

In Part 3 the final instalment of the graph database series you will learn how to visualize network graphs and explore relationships.

### References

* [Loading data into Neptune DB instance](https://docs.aws.amazon.com/neptune/latest/userguide/bulk-load-data.html)

* [Gremlin data load format](https://docs.aws.amazon.com/neptune/latest/userguide/bulk-load-tutorial-format-gremlin.html)

* [Query results in Amazon Neptune workbench](https://aws.amazon.com/blogs/database/visualize-query-results-using-the-amazon-neptune-workbench/)

* [Amazon Neptune User Guide](https://docs.aws.amazon.com/neptune/latest/userguide/graph-get-started.html)

* [Amazon Neptune Resources](https://aws.amazon.com/neptune/developer-resources/)

* [Amazon Neptune documentation](https://docs.aws.amazon.com/neptune/latest/userguide/intro.html)

* [Getting started with Amazon Neptune](https://aws.amazon.com/neptune/?c=db&sec=srv)

* [Introducing Amazon Neptune Global Database](https://aws.amazon.com/blogs/database/introducing-amazon-neptune-global-database/)


### Additional Resources

* [Amazon Neptune features](https://docs.aws.amazon.com/neptune/latest/userguide/feature-overview.html)

* [AWS Graph Notebooks](https://github.com/aws/graph-notebook)

* [About Amazon Neptune](https://aws.amazon.com/neptune/)

* [Learning Path for Amazon Neptune](https://pages.awscloud.com/AWS-Learning-Path-Getting-Started-with-Amazon-Neptune_2020_LP_0009-DAT.html)

* [Keep up to date withe the latest news from AWS Database Blogs for Amazon Neptune](https://aws.amazon.com/blogs/database/category/database/amazon-neptune/)

* [Amazon Neptune simplifies graph analytics and machine learning workflows with Python integration](https://aws.amazon.com/about-aws/whats-new/2022/06/amazon-neptune-simplifies-graph-analytics-machine-learning-workflows-python-integration/)


* [Setting up Curl](https://docs.aws.amazon.com/neptune/latest/userguide/get-started-access-graph.html)

* [Curl](https://curl.se/docs/sslcerts.html)

###  AWS Innovate Online Conference APJ - 23 & 25 August 2022


![AWS Innovate](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/st6r8cnixap7o4log7yw.jpg)

Register at this [link](https://aws.amazon.com/events/aws-innovate/apj/data/)


### 🚀 Hot off the press: Introducing Amazon Neptune Global Database 

From 27 July 2022, AWS Neptune Global Database can build graph relationships in multiple regions including US East (N. Virginia), US East (Ohio), US West (N. California), US West (Oregon), Europe (Ireland), Europe (London), and Asia Pacific (Tokyo) Regions.

You can read about this announcement from the AWS Database Blog written by author Navtanay Sinha [here](https://aws.amazon.com/blogs/database/introducing-amazon-neptune-global-database/).

### Register for Australia's biggest data engineering conference DataEngBytes:

* Melbourne: 27 September 2022
* Sydney: 29 September 2022

Register here: https://dataengconf.com.au/


![Data Eng Bytes](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qzzqs2s8tte2hzqrf6n8.jpg)