Adopting SQLRender to Netezza

Christian_Reich · January 13, 2017, 7:39pm

Friends:

I think you have a quorum to found a working group. Hadoop has one, no reason for Netezza users not to have one as well.

But before you get too happy: Make sure you all are on the same firmware. I know that Netezza has a whole cascade of modules you have to buy (or not), giving you different level of SQL functionality and compatibility with other dialects.

Gowtham_Rao · January 13, 2017, 8:38pm

That would be great. We will ask I BM to be involved in workgroup. How do we go about creating the workgroup?

Christian_Reich · January 13, 2017, 9:04pm

You tell @MauraBeaton t hat you have one, who is in it, when you meet and what it is all about, and she adds you to the OHDSI page. You’ll need an overview page kind of like this. Then you start a rolling calendar like this, and go.

Gowtham_Rao · January 14, 2017, 1:58pm

Great - will PM

Vojtech_Huser · January 19, 2017, 8:26pm

Summary written by Lee Evans

There was a very productive call on Wednesday to discuss the level of interest in adding Netezza support to the OHDSI tools.

All of the call attendees expressed interest in enabling support for both basic Netezza SQL translation & connectivity (e.g. running Achilles) and full Netezza OHDSI tools integration (e.g. running Atlas).

Call Attendees:
Gowtham Rao & Ravi Ravindra - Blue Cross Blue Shield
Sajan Khosla & Matt Tyson - AstraZeneca
Robert Carroll - Vanderbilt
Vojtech Huser - NIH
Lee Evans - LTS Computing LLC

The attendees decided that the shared interests of the group will be best served by a focused effort to implement Netezza support within the OHDSI tools.

It was also agreed on the call that OHDSI code changes developed to add Netezza support will be contributed back to the OHDSI community as open source.

Vojtech_Huser · February 6, 2017, 10:07pm

I am excited to see that SQLRender now can translate my query into netezza.
(see github recent changes)

A question for @schuemie or @Gowtham_Rao :
If I have my Netezza driver in here: c:\nz_driver\xxxx.xx and my server IP is 130.22.33.44 - how do I create a connection details and now run my netezza translated query? (how do I point the DatabaseConnector to my xxxx.xx driver file?)

Gowtham_Rao · February 6, 2017, 11:04pm

@Vojtech_Huser – yes, the Netezza translation is very stable right now and has passed all our tests @schuemie did bring back Netezza GitHub - OHDSI/DatabaseConnector: An R package for connecting to databases using JDBC.

(I dont know the answer to your question )

saradempster · February 8, 2017, 10:20pm

Hi Vojtech,
I dug back into my notes. If I understand your question correctly, you would do something along the lines of what I pasted below. Can’t promise below is exactly correct, but gives the general idea.
-Sara

source(“DatabaseConnector.R”)

connectionDetails <- createConnectionDetails(dbms = “netezza”,
server = “xxx.xxx.xxx.net”,
user = “user”,
password = “XXXXXX”,
port=5480,
schema=“mslr_cdm4”)

connection <- connect(connectionDetails)
sql = readSql(“query.sql”)
translatedSql <- translateSql(sql, targetDialect=“netezza”)
querySql(connection,translatedSql)

Vojtech_Huser · February 8, 2017, 10:33pm

With no hints from Martijn, I looked into the code and found a need to specify pathToDriver.

github.com

OHDSI/DatabaseConnector/blob/master/R/DatabaseConnector.R#L438


# @file DatabaseConnector.R
#
# Copyright 2018 Observational Health Data Sciences and Informatics
#
# This file is part of DatabaseConnector
# 
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
# 
#     http://www.apache.org/licenses/LICENSE-2.0
# 
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.


#' DatabaseConnector
#'
#' @docType package
#' @name DatabaseConnector
#' @importFrom utils sessionInfo setTxtProgressBar txtProgressBar object.size write.csv write.table
#' install.packages menu
NULL


.onLoad <- function(libname, pkgname) {
rJava::.jpackage(pkgname, jars = "DatabaseConnector.jar", lib.loc = libname)
}


#' @name jdbcDrivers
#'
#' @title
#' How to download JDBC drivers for the various data platforms.
#'
#' @section
#' PostgresSql: Go to \href{https://jdbc.postgresql.org/download.html}{the PostgresSQL JDBC site} and
#' download the current version. The file is called something like 'postgresql-42.2.2.jar'.
#'
#' @section
#' Oracle: Go to
#' \href{http://www.oracle.com/technetwork/database/features/jdbc/jdbc-drivers-12c-download-1958347.html}{the
#' Oracle JDBC site}. Select 'Accept License Agreement' and download the jar file. The file is called
#' something like 'ojdbc7.jar'.
#'
#' @section
#' SQL Server and PDW: Go to \href{https://www.microsoft.com/en-us/download/details.aspx?id=11774}{the
#' Microsoft SQL Server JDBC site}, click 'Download' and select the tar.gz file. Click 'Next' to start
#' the download. Decompress the file and find a file called seomthing like 'sqljdbc41.jar' in the a
#' folder named something like 'sqljdbc_6.0/enu/jre7'.
#'
#' @section
#' RedShift: Go to the
#' \href{https://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html#download-jdbc-driver}{Amazon
#' RedShfit JDBC driver page} and download the latest JDBC driver. The file is called something like
#' 'RedshiftJDBC42-1.2.12.1017.jar'.
#'
#' @section
#' Netezza: Read the instructions
#' \href{https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.datacon.doc/t_datacon_setup_JDBC.html}{here}
#' on how to obtain the Netezza JDBC driver.
#'
#' @section
#' BigQuery: Go to \href{https://cloud.google.com/bigquery/partners/simba-drivers/}{Google's site} and
#' download the latest JDBC driver. Unzip the file, and locate the appropriate jar files.
#'
#' @section
#' Impala: Go to
#' \href{https://www.cloudera.com/downloads/connectors/impala/jdbc/2-5-5.html}{Cloudera's site}, pick
#' your OS version, and click "GET IT NOW!'. Register, and you should be able to download the driver.
NULL

So the change your code would be

connection<-connect(connectionDetails,pathToDriver=‘c:/nz_driver’)

and the file name must be nzjdbc.jar

I have to try from a different NIH building so it may take me a while to actually test this.
Perhaps @Gowtham_Rao will beat me to testing this on actual nz server.

schuemie · February 13, 2017, 10:04am

Thanks @Vojtech_Huser for answering this question!

Just so you know, each of our packages has a manual that could be helpful. You’ll find the PDF of the manual on the Github repo page under Getting Involved. Or, you can type

help(package="DatabaseConnector")

to open the manual in R