ORE - Snippet

> Procedural Languages > R

1 - About

ore snippet

2 - Prerequisite

3 - Snippets

3.1 - Load Data Frame to table

Load Data Frame to table

ore.drop(table = "IRIS_TABLE")
# If IRIS_TABLE doesn't exist, you do not get a message.
 
# create a database table with the data contained in iris
ore.create(iris, table = "IRIS_TABLE")
Advertising

3.2 - List the table

ore.ls()

3.3 - Copy a table

Create IRIS_TABLE_N that does not contain SPECIES, the nonnumeric column:

IRIS_TABLE_N=IRIS_TABLE[,c("SEPAL_LENGTH", "SEPAL_WIDTH",  "PETAL_LENGTH", "PETAL_WIDTH")]

3.4 - NULL

The sample null.R is the only sample that does not use iris as data. null.R compares the handling of NULLs in SQL with the handling of NAs in R.

3.4.1 - R code

  • Return all observations where ozone < 30
nrow(airquality[airquality$Ozone < 30,])
92
  • results when NAs are explicitly excluded
nrow(airquality[airquality$Ozone < 30 & !is.na(airquality$Ozone),])
55
Advertising

3.4.2 - ORE Code

  • The default behavior for SQL tables is to exclude NULLS in output
ore.drop(table = "AIRQUALITY")
ore.create(airquality, table = "AIRQUALITY")
nrow(AIRQUALITY[AIRQUALITY$OZONE < 30,])
55
  • To handle NULLs the same way that R handles NA, request the behavior explicitly
options(ore.na.extract = TRUE)
nrow(AIRQUALITY[AIRQUALITY$OZONE < 30,])
92