SCCRIP (Sickle Cell Clinical Research and Intervention Program) established a longitudinal cohort at multiple sites with Sickle Cell Disease (SCD) in 2014 managed by St. Jude Clinical Hematology. A new collaborator for SCCRIP has longitudinal data for 600 SCD patients in OMOP CDM format and this effort is to convert OMOP CDM to SCCRIP format.
This package can be downloaded from https://github.com/OHDSI/CommonDataModel/. It functions to dynamically create the OMOP CDM documentation and DDL scripts to instantiate the CDM tables.
Dependencies and prerequisites
This process required R-Studio to be installed as well as DatabaseConnector and SqlRender.
DatabaseConnector - This R package provides function for connecting to various DBMSs. Together with the SqlRender package, the main goal of DatabaseConnector is to provide a uniform interface across database platforms: the same code should run and produce equivalent results, regardless of the database back end.
Features
Create connections to the various database platforms:
MicrosoftSQL Server
Oracle
PostgresSql
Microsoft Parallel Data Warehouse (a.k.a. Analytics Platform System)
Amazon Redshift
Apache Impala
Google BigQuery
IBM Netezza
SQLite
Spark
Statements for executing queries with
Error reporting to file
Progress reporting
Multiple statements per query
Support for fetching data to Andromeda objects
Insert data frame to a database table
Supports the DBI interface, with SQL statements automatically translated to the appropriate dialect.
Supports the dbplyr interface.
Integrates with RStudio's Connections tab
Instead of providing the server name, it is also possible to provide the JDBC connection string if this is more convenient:
In some organizations using Microsoft SQL Server and Windows, it is possible to use Windows Authentication to connect to the server, meaning you won’t have to provide a user name and password, since your Windows credentials will be used. This will require downloading the SQL Server authentication DLL file, and placing it somewhere on your system path. If you don’t have rights to add files to a place on your system path, you can place it anywhere, and set the PATH_TO_AUTH_DLL environmental variable, either using the Sys.setenv(), or by adding it to your .Renviron file. See this webpage or type ?connect for details on where to get the DLL (and what specific version).
SqlRender - This is an R package for rendering parameterized SQL, and translating it to different SQL dialects. SqlRender can also be used as a stand-alone Java library and a command-line executable.
Typically, new CDM versions and updates are decided by the CDM working group (details to join meetings on homepage). These changes are tracked as issues in the github repo. Once the working group decides which changes make up a version, all the corresponding issues should be tagged with a version number, e.g. v5.4, and added to a project board.
Connecting to a SQLite database
DatabaseConnector also supports SQLite through the RSQLite package, mainly for testing and demonstration purposes. Provide the path to the SQLite file as the server argument when connecting. If no file exists it will be created:
CDM R package OMOP CDM v5.4
This package can be downloaded from https://github.com/OHDSI/CommonDataModel/. It functions to dynamically create the OMOP CDM documentation and DDL scripts to instantiate the CDM tables.
Dependencies and prerequisites This process required R-Studio to be installed as well as DatabaseConnector and SqlRender.
DatabaseConnector - This R package provides function for connecting to various DBMSs. Together with the SqlRender package, the main goal of DatabaseConnector is to provide a uniform interface across database platforms: the same code should run and produce equivalent results, regardless of the database back end.
Features Create connections to the various database platforms: MicrosoftSQL Server Oracle PostgresSql Microsoft Parallel Data Warehouse (a.k.a. Analytics Platform System) Amazon Redshift Apache Impala Google BigQuery IBM Netezza SQLite Spark Statements for executing queries with Error reporting to file Progress reporting Multiple statements per query Support for fetching data to Andromeda objects Insert data frame to a database table Supports the DBI interface, with SQL statements automatically translated to the appropriate dialect. Supports the dbplyr interface. Integrates with RStudio's Connections tab
Instead of providing the server name, it is also possible to provide the JDBC connection string if this is more convenient:
conn <- connect(dbms = "postgresql", connectionString = "jdbc:postgresql://localhost:5432/postgres", user = "joe", password = "secret")
issue / resolution https://forums.ohdsi.org/t/problem-connecting-to-postgres-database-using-database-connector/604
Using Windows Authentication for SQL Server
In some organizations using Microsoft SQL Server and Windows, it is possible to use Windows Authentication to connect to the server, meaning you won’t have to provide a user name and password, since your Windows credentials will be used. This will require downloading the SQL Server authentication DLL file, and placing it somewhere on your system path. If you don’t have rights to add files to a place on your system path, you can place it anywhere, and set the PATH_TO_AUTH_DLL environmental variable, either using the Sys.setenv(), or by adding it to your .Renviron file. See this webpage or type ?connect for details on where to get the DLL (and what specific version).
SqlRender - This is an R package for rendering parameterized SQL, and translating it to different SQL dialects. SqlRender can also be used as a stand-alone Java library and a command-line executable.
https://github.com/OHDSI/CommonDataModel/ Create DDL, Foreign Keys, Primary Keys, and Indexes from R
First, install the package from GitHub
Typically, new CDM versions and updates are decided by the CDM working group (details to join meetings on homepage). These changes are tracked as issues in the github repo. Once the working group decides which changes make up a version, all the corresponding issues should be tagged with a version number, e.g. v5.4, and added to a project board.
Read from Step 0 onwards at https://github.com/OHDSI/CommonDataModel/?tab=readme-ov-file#step-0
Connecting to a SQLite database DatabaseConnector also supports SQLite through the RSQLite package, mainly for testing and demonstration purposes. Provide the path to the SQLite file as the server argument when connecting. If no file exists it will be created: