How to use SingleStore with Spark ML for Fraud Detection — 1/3

Configure Databricks CE

Abstract

SingleStore is a database technology that can easily integrate with a wide range of big data tools and services. One such tool is Apache Spark™. In this series of articles, we’ll see how easy it is to use the SingleStore Spark Connector and the performance benefits it provides. We’ll also discuss a Credit Card Fraud Detection case study using actual data that we save into SingleStore, and then develop a Machine Learning model using Spark to determine if a Credit Card transaction is fraudulent or not.

The notebook files used in this article series are available on GitHub in DBC, HTML and iPython formats.

Introduction

Despite technological advances in the recent past, fraud remains a problem today for many financial institutions worldwide. Having been the victim of credit card fraud on several occasions during the past few years, financial fraud is an issue close to my heart. In this article, we’ll work through an example of how we can store some credit card transaction data in SingleStore and use Spark’s Machine Learning capabilities to develop a Logistic Regression model to predict financial fraud. We’ll build everything in the cloud using the SingleStore Managed Service and Databricks Community Edition (CE) for ease of use.

To begin with, we need to create a free Managed Service account on the SingleStore website, and a free Community Edition (CE) account on the Databricks website. At the time of writing, the Managed Service account from SingleStore comes with $500 of Credits. This is more than adequate for the case study described in this article. For Databricks CE, we need to sign-up for the free account rather than the trial version.

This is a multi-part article series, and it is structured as follows:

1. Configure Databricks CE.
2. Load the Credit Card data into SingleStore.
3. Create and evaluate a Logistic Regression model.

This first article covers Part 1, Configure Databricks CE.

Configure Databricks CE

First, we need to log in to our Databricks CE account.

From the left navigation pane, we’ll select Compute (1) and then + Create Cluster (2), as shown in Figure 1.

Figure 1. Create Cluster.

On the Create Cluster page, we’ll give the cluster a name (1) and select 7.3 LTS ML (2) for the Runtime Version, as shown in Figure 2.

Figure 2. Cluster Options.

Next, we’ll click the Create Cluster button, as shown in Figure 3.

Figure 3. Create Cluster.

After a short time, a solid green ball should appear next to the cluster name (1), as shown in Figure 4. Next, we’ll click on the Libraries link (2).

Figure 4. Active Cluster.

We’ll click on Install New on the Libraries page, as shown in Figure 5.

Figure 5. Install New.

Under Install Library, we’ll select Maven (1) and then Search Packages (2), as shown in Figure 6.

Figure 6. Maven.

We’ll choose Maven Central (1) and enter singlestore (2) into the search box, and a list of packages should appear, as shown in Figure 7. As shown, we’ll choose the highest version of the connector for Spark (3).

Figure 7. SingleStore Packages.

Now we’ll click Install, as shown in Figure 8.

Figure 8. Install Spark Connector.

After a short delay, the connector should be installed, as shown in Figure 9.

Figure 9. Spark Connector Installed.

To correctly set up the Databricks environment for SingleStore, the MariaDB JDBC driver is also required. Downloaded the JAR file from the MariaDB download site. When writing this article, the latest version was Java 8 Connector/J 2.7.4.

We’ll select Install New again to load the MariaDB JDBC JAR file mentioned above. In Figure 10, we can either drag the JAR file where it says Drop JAR here or use the file browser to locate the JAR file.

Figure 10. Load JAR file.

Once the JAR file has successfully been uploaded, there should be a checkmark next to the file (1), and it can be installed (2), as shown in Figure 11.

Figure 11. Install JAR file.

After a short delay, the JAR file should be installed, as shown in Figure 12.

Figure 12. JAR file installed.

All the required libraries have been installed, so we can now create a new Python notebook by selecting Workspace from the left navigation pane and then drilling down, as shown in Figure 13.

Figure 13. Create a new Python Notebook.

We’ll call this Notebook Setup (1) and then Create it (2), as shown in Figure 14.

Figure 14. Setup Notebook.

The notebook should open up, as shown in Figure 15.

Figure 15. Python Notebook.

We can enter the following in the code cell, as shown in Figure 16.

server = "<TO DO>"
password = "<TO DO>"

port = "3306"
cluster = server + ":" + port

Figure 16. Python Notebook.

The <TO DO> for server and password should be replaced with the values obtained from the SingleStore Managed Service when creating a cluster. As a reminder, we can create a Managed Service account by following the Sign Up to Get Started section of a previous article.

Finally, the SingleStore Managed Service provides a firewall. Therefore, we need to add the public IP address of the Databricks CE Cluster on AWS to the firewall to allow incoming connections to SingleStore. To find the public IP address of the Databricks CE Cluster on AWS, we can use some Python code in a notebook. For example:

hostname = spark.conf.get("spark.databricks.clusterUsageTags.driverPublicDns")

address = hostname.split(".")[0]
address = address.replace("ec2-", "")
address = address.replace("-", ".")

print(address)

Summary

In this article, we have taken our first steps to using SingleStore with Spark. Using a Databricks CE account, we have created a Spark cluster, attached the appropriate libraries and prepared a notebook that contains our connection information to a database cluster running in our SingleStore Managed Service account.

We’ll load credit card data into our Spark environment in the next article. We’ll also compare the performance of the SingleStore Spark Connector against JDBC for loading our data into SingleStore from Spark.