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