Connecting to SingleStore Database Using R script

This article covers the different ways you can connect to the SingleStore database using R script — particularly using RMySQL and RJDBC libraries.

In this article, I will be describing the different ways you can connect to the SingleStore database using R. In particular, we will be discussing the RMySQL and RJDBC libraries.

One interesting fact about SingleStore is although its architecture has nothing to do with the open source MySQL database, SingleStore makes it fairly easy to connect to the SingleStore databases using MySQL database connectivity drivers. In short, you can connect to the SingleStore database using the same drivers and protocols as you do for MySQL.

Although this document has been written to work both on cluster-in-box and Distributed mode, the success of the connection will depend on the property of the server to accept connection from outside its environment — and of course, the correct connection parameters of the code.

Connecting using RMySQL library :

Steps:

1. Open the RGui

2. Install the RMySQL library using the command -

   > install.packages(“RMySQL”)
   

3. Load this library in the R session :

   >library(RMySQL)
   

4. Now create the connection variable for accessing the SingleStore database:

   > memsql_connection <-
     RMySQL::dbConnect(
       RMySQL::MySQL()
       ,host = "database server url or ip"
       ,port = <server port>
       ,user = "<user>"
       ,password = "<password>"
       ,dbname = “<database name>”)
   

5. Check if connection was successful :

   RMySQL::dbListTables(memsql_connection)
   

Connecting using the RJDBC library:

Pre-Requisites:

1. Java runtime environment must be setup and must be mapped in the environment variables
2. MySQL JDBC library must be downloaded from - https://cdn.mysql.com//Downloads/Connector-J/mysql-connector-java-8.0.22.zip

Steps:

1. Open the RGui

2. Install the RJBDC library using the command -

   > install.packages(“RJDBC”)
   

3. Load this library in the R session :

   >library(RJDBC)
   

4. Now create the connection variable for accessing the SingleStore database:

   > jdbc_drv <- RJDBC::JDBC(“com.mysql.cj.jdbc.Driver" , classPath = list.files("<PATH
   TO MYSQL JDBC Driver >/mysql-connector-java-8.0.22", pattern="jar$",full.names=T),
   identifier.quote="'")
   
   >jdbc_con <- dbConnect(jdbc_drv, "jdbc:mysql://<IP>:<PORT>/<DBNAME>",
   "username", "password")
   

5. Check if connection was successful :

There are few other ways to connect to the SingleStoreDB Self-Managed using R, a common method on a windows machine would be the ODBC. If you are looking for the ODBC mode, just create the ODBC variable on your machine and connect using the DSN.

If you find yourself stuck on any steps, simply check if the correct connection parameters and case has been used. You can also post issues and other methods in the SingleStore forum!