Connect to SQream Using 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.

In this topic:

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")
> 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)


> 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)