R Connection

Table of Contents

R is a programming language and software environment for statistical computing. Machbase CLI can be connected to R via RODBC (1.3.12) package. By using the RODBC package, it is able to bring the data from Machbase database to R for statistical computing and then save the result values to the Machbase immediately.

Install RODBC

RODBC can be installed by issuing simple command. To install RODBC, execute the command below after running R.

install.packages("RODBC")

Install Machbase Windows Client and set DSN in order to connect RODBC. Refer to Install Windows Client and ODBC Settings for detailed information.

How to Use RODBC

It describes major functions and options of RODBC functions that are used for connection with Machbase. To use these features, execute R or RStudio.

Refer to the website below for the instructions on how to use RODBC. https://cran.r-project.org/web/packages/RODBC/RODBC.pdf

First, import RODBC library as below.

install.packages("RODBC")

List of ODBC Functions

odbcConnect()

It opens a connection to a database through a specified DSN. Caution should be exercised that the results will be properly displayed if believeNRows=FALSE is set. In addition, upper case is used internally. Therefore, case="toupper" option should be selected.

odbcConnect("mach", believeNRows=FALSE, case="toupper")
# "mach" is the same DSN that was specified when Windows Client was installed.

odbcClose()

It closes connections to databases and sends channel variables as parameters. Use odbcCloseAll() function to close all the connections at once.

channel <- odbcConnect("mach", believeNRows=FALSE, case="toupper")
odbcClose(channel)

odbcCloseAll()

sqlTables()

It returns the list of table from Machbase.

sqlTables(channel)

# Execution results

> sqlTables(channel)
TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
1 MACH SYS APACHE_ACCESS_LOG TABLE <NA>
2 MACH SYS SENSOR_DATA TABLE <NA>
>

sqlColumns()

It returns a column information of specified tables. Deliver a table name as a parameter.

sqlColumns(channel, "sensor_data")

# Execution results
> sqlColumns(channel, "sensor_data")
TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE
1 <NA> SYS SENSOR_DATA SID 4 INTEGER 11
2 <NA> SYS SENSOR_DATA SIP 2104 IPV4 15
3 <NA> SYS SENSOR_DATA VAL 4 INTEGER 11
BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF
1 0 0 10 1 <NA> <NA>
2 0 0 10 1 <NA> <NA>
3 0 0 10 1 <NA> <NA>
SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE
1 4 0 0 1 YES
2 2104 0 0 2 YES
3 4 0 0 3 YES
>

sqlTypeInfo()

It returns the data type information that are supported by Machbase.

sqlTypeInfo(channel)

# Execution results
> sqlTypeInfo(channel)
TYPE_NAME DATA_TYPE COLUMN_SIZE LITERAL_PREFIX LITERAL_SUFFIX CREATE_PARAMS
1 short 5 6 <NA> <NA> <NA>
2 integer 4 11 <NA> <NA> <NA>
3 long -5 20 <NA> <NA> <NA>
4 float 6 27 <NA> <NA> <NA>
5 double 8 27 <NA> <NA> <NA>
6 datetime 11 31 ' ' <NA>
7 varchar 12 32767 ' ' <NA>
8 ipv4 2104 15 ' ' <NA>
9 ipv6 2106 45 ' ' <NA>
NULLABLE CASE_SENSITIVE SEARCHABLE UNSIGNED_ATTRIBUTE FIXED_PREC_SCALE
1 1 0 2 0 0
2 1 0 2 0 0
3 1 0 2 0 0
4 1 0 2 0 0
5 1 0 2 0 0
6 1 0 0 1 0
7 1 1 3 1 0
8 1 0 2 0 0
9 1 0 2 0 0
AUTO_UNIQUE_VALUE LOCAL_TYPE_NAME MINIMUM_SCALE MAXIMUM_SCALE SQL_DATA_TYPE
1 0 short -12851 -12851 5
2 0 integer -12851 -12851 4
3 0 long -12851 -12851 -5
4 0 float -38 38 6
5 0 double -308 308 8
6 0 datetime -12851 -12851 3
7 0 varchar -12851 -12851 12
8 0 ipv4 -12851 -12851 2104
9 0 ipv6 -12851 -12851 2106
SQL_DATETIME_SUB NUM_PREC_RADIX INTERVAL_PRECISION
1 -12851 10 -12851
2 -12851 10 -12851
3 -12851 10 -12851
4 -12851 10 -12851
5 -12851 10 -12851
6 -12851 -842150451 -12851
7 -12851 -842150451 -12851
8 -12851 -842150451 -12851
9 -12851 -842150451 -12851

sqlQuery()

sqlQuery() executes SQL statements and displays the results. It can use all SQL statements including SELECT, CREATE, INSERT, and TRUNCATE. When you create a new table, refer to Data Type for column types that Machbase supports.

channel <- odbcConnect("mach", believeNRows=FALSE, case="toupper")

sqlQuery(channel, "create table rtest( id integer, name varchar(20) )")
sqlQuery(channel, "insert into rtest values(1, 'aaa')")
sqlQuery(channel, "insert into rtest values(2, 'bbb')")
sqlQuery(channel, "insert into rtest values(3, 'ccc')")

dat <- sqlQuery(channel, "select * from rtest")
str(dat)

# Execution results
> str(dat)
'data.frame': 3 obs. of 2 variables:
$ ID : int 3 2 1
$ NAME: Factor w/ 3 levels "aaa","bbb","ccc": 3 2 1
>

sqlSave()

It saves the data frame in a new table or existing table.

sqlSave(channel, dat, "savetable", rownames=FALSE, fast=TRUE, verbose=FALSE, varTypes=c(ID="integer", NAME="varchar(20)"))
  • Rownames: If it is set to TRUE, columns of rownames are created. It is set to FALSE by default.
  • Fast: If it is set to FALSE, a record is inserted one by one. If it set to TRUE, all the records are inserted at once. Thus, it would be better to set as TRUE in the case of storing a large number of data.
  • Verbose: It determines whether to display the message about the process. It is set to FALSE by default.
  • VarTypes: It sets a type and column name of a newly created table. Refer to Data Type for columns of data types that are supported by Machbase.
  • Append: It needs to set TRUE when adding data to the existing table.
# Retrieve the contents of test table and store them into savetable.

> dat <- sqlQuery(channel, "select * from rtest")
> sqlSave(channel, dat, "savetable", rownames=FALSE, fast=FALSE,verbose=TRUE,varTypes=c(ID="integer", NAME="varchar(20)"))
Query: CREATE TABLE "SAVETABLE" ("ID" integer, "NAME" varchar(20))
Query: INSERT INTO "SAVETABLE" ( "ID", "NAME" ) VALUES ( 3, 'ccc' )
Query: INSERT INTO "SAVETABLE" ( "ID", "NAME" ) VALUES ( 2, 'bbb' )
Query: INSERT INTO "SAVETABLE" ( "ID", "NAME" ) VALUES ( 1, 'aaa' )
> sqlQuery(channel, "select * from savetable")
ID NAME
1 1 aaa
2 2 bbb
3 3 ccc
>

sqlCopy()

It copies SQL results to other table.

sqlCopy(channel, "select * from rtest", "copytable")

sqlDrop()

It deletes the specified table.

sqlDrop(channel, "savetable")

sqlClear()

It deletes the contents of the specified table.

sqlClear(channel, "savetable")

Supported API

See below for Machbase supported APIs from RODBC API.

Table. RODBC Supported API

Function Statement Description
odbcConnect() channel <- odbcConnect(dsn, believeNRows=FALSE, case="toupper") It opens connections.
odbcClose() odbcClose(channel) It closes connections.
odbcCloseAll() odbcCloseAll() It closes all the connections.
sqlClear() sqlClear(channel, tablename) It deletes all data in a table.
sqlDrop() sqlDrop(channel, tablename) It removes a table.
sqlQuery() sqlQuery(channel, query) Execute SQL query.
sqlFetch() sqlFetch() It returns all the records without any condition.
sqlFetchMore() sqlFetchMore() It returns the remaining records from odbcQuery().
sqlColumns() sqlColumns(channel, tablename) It returns column information of a table.
sqlTables() sqlTables(channel) It returns the information of all the tables.
sqlCopy() sqlCopy(channel, query, dsttable) It copies data into other tables.
sqlSave() sqlSave(channel, dat, dsttable) It saves frame data as table.
sqlTypeInfo sqlTypeInfo(channel) It fetches information of data type.
odbcDriverConnect odbcDriverConnect("DSN=MACH"); It opens connections.
odbcGetErrMsg odbcGetErrMsg(channel) It returns ODBC errors.
odbcClearError odbcClearError(channel) It clears ODBC errors.
sqlGetResults sqlGetResults(channel) It fetches all the results from odbcQuery().
odbcFetchRows odbcFetchRows(channel) It fetches all the rows of results from odbcQuery().
odbcQuery odbcQuery(channel, "select .."); It conducts user-defined queries.
odbcTables odbcTables(channel) It returns table information and execute. Use sqlGetResults() to get data.
odbcGetInfo odbcGetInfo(channel) It displays the version information of ODBC driver which is currently connected.
odbcReconnect channel2 <- odbcReconnect( channel ) It re-connects by using the existing connection information.
odbcDataSources odbcDataSources() It returns the list of currently supported ODBC drivers.
sqlPrimaryKeys sqlPrimaryKeys(channel, "table") It returns information of primary key. (It doesn't mean anything in Machbase)
odbcEndTran odbcEndTran(channel, TRUE) (It doesn't mean anything in Machbase)
odbcSetAutoCommit odbcSetAutoCommit(channel, TRUE) Adjust AutoCommit mode. (It doesn't mean anything in Machbase)
sqlCopyTable Unsupported Unsupported
odbcUpdate Unsupported Unsupported
getSqlTypeInfo Unsupported Unsupported
setSqlTypeInfo Unsupported Unsupported
sqlUpdate Unsupported Unsupported

results matching ""

    No results matching ""