Running SQL code

With the Netezza R library package, it is possible to run any SQL query on the Netezza system and return its results into the R client by using the nzQuery() and nzScalarQuery() functions.

Running SQL queries

The nzQuery() function returns a data.frame object with the query results, whereas the nzScalarQuery() function returns the query result forced to a single scalar value.

The following examples show both functions.
t = nzQuery("SELECT * FROM _V_DUAL_DSLICE")
t
# DSID
#11
#23
#34
#4 2
t = nzScalarQuery("SELECT COUNT(*) FROM _V_DUAL_DSLICE")
t
#[1] 4

Debugging

For debugging, the Netezza R ;ibrary package provides the nzDebug() function, which switches debugging on or off, so that some functions available in the package print additional debug information.

This example repeats a sample that is shown previously in this section, this time with debugging switched on.
nzDebug(TRUE)
nzadult = nz.data.frame("adult")
#select current_schema
#SELECT CAST(COUNT(*) AS INTEGER) AS field FROM _v_obj_relation WHERE objname
= #'ADULT' AND schema ='ADMIN'
#select current_schema
#SELECT attname AS field FROM _V_RELATION_COLUMN WHERE name = 'ADULT' AND #schema
='ADMIN' ORDER BY ATTNUM

Details

For the query functions, all parts of the input query are concatenated with paste(... , sep="") and the result is passed to the Netezza system. The nzDebug() function sets up a global variable .nzDebug with a value that is equal to the value of the onoff parameter. The nzDependencies() function accepts a vector containing names of packages to check.
nzQuery(..., as.is = TRUE)
nzScalarQuery(..., as.is = TRUE)
nzDebug(onoff = TRUE)
Where:
...
Specifies any number of query parts passed to paste.
as.is
Denotes whether R should leave the result column as is or run the default RODBCtype conversions.
onoff
Turns debugging on (TRUE) or off (FALSE).
types
Denotes a vector that contains the names of packages to check.

Netezza SQL command wrappers

Two Netezza SQL (nzsql) wrappers are used frequently. To check if a table exists, use the nzExistTable() function. To delete a table, use the nzDeleteTable() function. These are wrappers of nzsql commands or basic functions.

Other wrappers on nzsql commands or common functions include:
  • nzCreateView
  • nzDropView
  • nzJoin
  • nzJoin.permanent
  • nzMerge
  • nzTruncateTable