R

You can use R to interact with a SQream DB cluster.

This tutorial is a guide that will show you how to connect R to SQream DB.

JDBC

1. Get the SQream DB JDBC driver.

2. In R, install RJDBC

   > install.packages("RJDBC")
   Installing package into 'C:/Users/r/...'
   (as 'lib' is unspecified)
   
   package 'RJDBC' successfully unpacked and MD5 sums checked
   

3. Import the RJDBC library

   > library(RJDBC)
   

4. Set the classpath and initialize the JDBC driver which was previously installed. For example, on Windows:

   > cp = c("C:\\Program Files\\SQream Technologies\\JDBC Driver\\2020.1-3.2.0\\sqream-jdbc-3.2.jar")
   > .jinit(classpath=cp)
   > drv <- JDBC("com.sqream.jdbc.SQDriver","C:\\Program Files\\SQream Technologies\\JDBC Driver\\2020.1-3.2.0\\sqream-jdbc-3.2.jar")
   

5. Open a connection with a JDBC connection string and run your first statement

   > con <- dbConnect(drv,"jdbc:Sqream://127.0.0.1:3108/master;user=rhendricks;password=Tr0ub4dor&3;cluster=true")
   
   > dbGetQuery(con,"select top 5 * from t")
      xint  xtinyint xsmallint xbigint
   1    1       82      5067       1
   2    2       14      1756       2
   3    3       91     22356       3
   4    4       84     17232       4
   5    5       13     14315       5
   

6. Close the connection

   > close(con)
   

A full example

> library(RJDBC)
> cp = c("C:\\Program Files\\SQream Technologies\\JDBC Driver\\2020.1-3.2.0\\sqream-jdbc-3.2.jar")
> .jinit(classpath=cp)
> drv <- JDBC("com.sqream.jdbc.SQDriver","C:\\Program Files\\SQream Technologies\\JDBC Driver\\2020.1-3.2.0\\sqream-jdbc-3.2.jar")
> con <- dbConnect(drv,"jdbc:Sqream://127.0.0.1:3108/master;user=rhendricks;password=Tr0ub4dor&3;cluster=true")
> dbGetQuery(con,"select top 5 * from t")
   xint  xtinyint xsmallint xbigint
1    1       82      5067       1
2    2       14      1756       2
3    3       91     22356       3
4    4       84     17232       4
5    5       13     14315       5
> close(con)

ODBC

1. Install the SQream DB ODBC driver for your operating system, and create a DSN.

2. In R, install RODBC

   > install.packages("RODBC")
   Installing package into 'C:/Users/r/...'
   (as 'lib' is unspecified)
   
   package 'RODBC' successfully unpacked and MD5 sums checked
   

3. Import the RODBC library

   > library(RODBC)
   

4. Open a connection handle to an existing DSN (my_cool_dsn in this example)

   > ch <- odbcConnect("my_cool_dsn",believeNRows=F)
   

5. Run your first statement

   > sqlQuery(ch,"select top 5 * from t")
      xint  xtinyint xsmallint xbigint
   1    1       82      5067       1
   2    2       14      1756       2
   3    3       91     22356       3
   4    4       84     17232       4
   5    5       13     14315       5
   

6. Close the connection

   > close(ch)
   

A full example

> library(RODBC)
> ch <- odbcConnect("my_cool_dsn",believeNRows=F)
> sqlQuery(ch,"select top 5 * from t")
   xint  xtinyint xsmallint xbigint
1    1       82      5067       1
2    2       14      1756       2
3    3       91     22356       3
4    4       84     17232       4
5    5       13     14315       5
> close(ch)