Adopting SQLRender to Netezza

saradempster · February 11, 2015, 4:05pm

Thanks Martijn. Yes - that was just a typo in translating to the wiki.

So I tried starting a fresh R session, loading the SqlRender package and passing the file path on the first call to SqlTranslate which did the trick! Now we have:

translatedSql <- rJava::J(“org.ohdsi.sql.SqlTranslate”)$translateSql(“select getdate() from SARA;”, “sql server”, “netezza”, pathToReplacementPatterns)
translatedSql
[1] “select CURRENT_DATE from SARA;”

To @Christian_Reich’s point, if we’re actively changing the csv file and debugging, we have to clear the SqlRender package and reload each time? Is there any workaround so that each call to translateSql will pick up the new csv file each time?

Vojtech_Huser · September 22, 2016, 7:40pm

I spoke today to someone who has their OMOP CDM data in Netezza. @saradempster, did you finish your goal of extending the translation file?

I don’t see netezza listed in this file https://github.com/OHDSI/SqlRender/blob/master/inst/csv/replacementPatterns.csv

If the decision was to give up, what was the set of reasons not to implement Netezza in the end? (just the drop if exists?)

mgkahn · September 25, 2016, 11:14pm

A bit off-topic but Colorado gave up bringing Google BigQuery and Google CloudSQL into the SqlRender fold because of missing functionalties that we couldn’t figure out (or didn’t spend enough time creating a hack). With Google BigQuery, there are rapid releases of new SQL syntax functionality so we may be able to bring that environment into the SqlRender world in the future. CloudSQL, which is based on MySQL, may be a lost cause…

Robert_Carroll · January 12, 2017, 2:37pm

@saradempster @Christian_Reich

Hi everyone, bumping an old thread here. We are in the process of implementing the CDM in a netezza environment at Vanderbilt, and we are wondering if there’s been any progress on the translate file. I’d be glad for any follow up information, though we can start from what was posted earlier in the thread if there’s nothing new.

Thank you for the help!

Christian_Reich · January 12, 2017, 8:29pm

@Robert_Carroll:

@saradempster needs to weigh in, but as far as I know nothing more happened. I left AZ (and the Netezza box I used to love), and then there was nobody to pick it up. Sara is a scientist. So, sorry. However, if you run into trouble I might help with advice. Maybe.

Robert_Carroll · January 12, 2017, 8:44pm

Not a problem, thank you for the response. We’ll start with the above posted translate file. Were there any big “to-do’s” on your list? Either way this is a helpful start!

lee_evans · January 13, 2017, 3:39pm

Hi @Robert_Carroll I used to manage the AstraZeneca Netezza box that Christian mentioned.

My company http://ltscomputingllc.com has been looking at how to add Netezza support to SQLRender.

Send me an instant message on this forum with your email address and let’s talk.

Vojtech_Huser · January 13, 2017, 4:39pm

Two other netezza sites (Marshfield Clinic and NIH IRP) would also be interested in netezza support. The SQL patterns is one problem I can help with (and a rather small problem, I would say) but the driver files (their proper inclusion in the SQL Render package) is something that requires technical expertise (e.g., @lee_evans)

lee_evans · January 13, 2017, 5:02pm

Hi @Vojtech_Huser do you have contact info for the interested folks at those two Netezza sites? I’d be happy to speak with them if you’d like to instant message me.

Gowtham_Rao · January 13, 2017, 6:59pm

@Vojtech_Huser @lee_evans @Robert_Carroll We are in the process of deploying OMOP on Netezza and this would be a good opportunity to collaborate. Happy to facilitate a phone call. Please PM me.

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