ORE - Snippet

About

ore snippet

Prerequisite

• Connect to the database.

Snippets

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

List the table

ore.ls()

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

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.

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

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