Error running KeppraAngiodema

[charhart]

Hi,

We're trying to run Keppra Angiodema on Oracle, and I'm having issues when it is trying to create/select into cohort_person. It looks like it is creating the temp table in the CDM schema, and it should be coming from the workDatabaseSchema, right? Here's the error. It seems like it might be coming from a SELECT...INTO.

DBMS:
oracle

Error:
execute JDBC update query failed in dbSendUpdate (ORA-01031: insufficient privileges
)

SQL:
CREATE TABLE  OMOPV5_DE.pwpkmcrscohort_person
   AS
SELECT
     ROW_NUMBER() OVER (ORDER BY person_id, cohort_start_date) AS row_id,
        subject_id,

        cohort_definition_id,

        cohort_start_date,
        ( cohort_start_date -  observation_period_start_date) AS days_from_obs_start,

                ( cohort_end_date -  cohort_start_date)
         AS days_to_cohort_end,

                ( observation_period_end_date -  cohort_start_date)
         AS days_to_obs_end

FROM
   (SELECT     subject_id,

                cohort_definition_id,

                cohort_start_date,
                cohort_end_date
          FROM   OHDSI.ohdsi_keppra_19239 exposure_table

          WHERE  cohort_definition_id IN (1, 2)

         ) raw_cohorts

INNER JOIN OMOPV5_DE.observation_period
        ON subject_id = person_id
  WHERE  cohort_start_date <= observation_period_end_date
        AND cohort_start_date >= observation_period_start_date

R version:
R version 3.3.0 (2016-05-03)

Platform:
x86_64-pc-linux-gnu

Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base

Other attached packages:
- CohortMethod (2.0.2)
- FeatureExtraction (1.0.0)
- Cyclops (1.2.0)
- KeppraAngioedema (0.1.0)
- DatabaseConnector (1.4.0)
- RJDBC (0.2-5)
- rJava (0.9-8)
- DBI (0.4-1)

[charhart]

Here's the full nohup.out if that helps.

R version 3.3.0 (2016-05-03) -- "Supposedly Educational"
Copyright (C) 2016 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.

  Natural language support but running in an English locale

R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.

Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.

> library(KeppraAngioedema)
Loading required package: DatabaseConnector
Loading required package: RJDBC
Loading required package: DBI
Loading required package: rJava
> 
> cohortTable = paste("ohdsi_keppra", Sys.getpid(), sep="_")
> print(paste("Cohort Table - ", cohortTable))
[1] "Cohort Table -  ohdsi_keppra_19239"
> 
> outputDir = paste("/home/chilton/ohdsi_keppra/run", Sys.getpid(), sep="_")
> print(paste("Output Dir - ", outputDir))
[1] "Output Dir -  /home/chilton/ohdsi_keppra/run_19239"
> 
> connectionDetails <- createConnectionDetails(dbms = "oracle",
+                                              user = "OHDSI",
+                                              port = "1521",
+                                              password = "xxx",
+                                              server = "sasridg02.uits.iupui.edu/I2B21STG")
> 
> execute(connectionDetails,
+         cdmDatabaseSchema = "OMOPV5_DE",
+         workDatabaseSchema = "OHDSI",
+         studyCohortTable = cohortTable,
+         oracleTempSchema = "OHDSI",
+         outputFolder = outputDir,
+         maxCores = 4)
Creating exposure and outcome cohorts
Connecting using Oracle driver
- Creating treatment cohort
  |                                                                            
  |                                                                      |   0%
  |                                                                            
  |=========                                                             |  12%
  |                                                                            
  |==================                                                    |  25%
  |                                                                            
  |==========================                                            |  38%
  |                                                                            
  |===================================                                   |  50%
  |                                                                            
  |============================================                          |  62%
  |                                                                            
  |====================================================                  |  75%
  |                                                                            
  |=============================================================         |  88%
  |                                                                            
  |======================================================================| 100%
Analysis took 5.33 hours
- Creating comparator cohort
  |                                                                            
  |                                                                      |   0%
  |                                                                            
  |=========                                                             |  12%
  |                                                                            
  |==================                                                    |  25%
  |                                                                            
  |==========================                                            |  38%
  |                                                                            
  |===================================                                   |  50%
  |                                                                            
  |============================================                          |  62%
  |                                                                            
  |====================================================                  |  75%
  |                                                                            
  |=============================================================         |  88%
  |                                                                            
  |======================================================================| 100%
Analysis took 2.76 hours
- Creating angioedema cohort
  |                                                                            
  |                                                                      |   0%
  |                                                                            
  |=========                                                             |  12%
  |                                                                            
  |==================                                                    |  25%
  |                                                                            
  |==========================                                            |  38%
  |                                                                            
  |===================================                                   |  50%
  |                                                                            
  |============================================                          |  62%
  |                                                                            
  |====================================================                  |  75%
  |                                                                            
  |=============================================================         |  88%
  |                                                                            
  |======================================================================| 100%
Analysis took 8.4 mins
- Creating negative control outcome cohort
  |                                                                            
  |                                                                      |   0%
  |                                                                            
  |======================================================================| 100%
Analysis took 3.38 hours
Cohort counts:
    cohortDefinitionId count
1                    1 13262
2                    2  4553
3                    3 13671
4                29056   144
5                29735  1058
6                73842   236
7                74396   394
8                75344  3376
9                75576   915
10               77650   157
11               78786   663
12               78804   226
13               79072   237
14               79903  1685
15               80217   737
16               80494   229
17               80665   463
18               80951  1194
19              133141   646
20              133228  1697
21              133834   780
22              134453   533
23              134461   588
24              134898   326
25              136773   152
26              136937   202
27              137057   917
28              138387   581
29              139099  1502
30              140480   706
31              140949   681
32              141663   445
33              192367   450
34              192606   467
35              192964   517
36              193016   703
37              193326   934
38              194997   204
39              195562  1781
40              195588  1202
41              195873  1112
42              196162   657
43              197032   754
44              197320  2162
45              197684  3110
46              198075   163
47              198199  1022
48              199067   625
49              199876   186
50              200528   867
51              200588  1353
52              253796   444
53              256722   271
54              258180   398
55              260134   540
56              261326   563
57              261880  3432
58              312437  6649
59              313792   788
60              314054   767
61              316993  1490
62              317109   385
63              317585   306
64              318800  6172
65              319843  1602
66              321596   695
67              372409  1097
68              373478  2940
69              374914   714
70              376103   682
71              376415  3728
72              378160   428
73              378424  3466
74              378425  1013
75              380731  1854
76              432436   334
77              432851   501
78              433163  1911
79              433440  2625
80              433516  2041
81              434056  2217
82              434926   162
83              435459  1582
84              436027   239
85              437409  2275
86              437833  4337
87              439080   312
88              440328   389
89              440358   354
90              440448   265
91              440814   315
92              441284   327
93              441589   359
94              442013  1086
95              443344   215
96             4193869   702
97             4205509   238
98             4291005   791
99             4311499   384
100            4324765  2921
101           43531027  1272
                                                     cohortName
1                                                     Treatment
2                                                    Comparator
3                                                    Angioedema
4                                                 Sialoadenitis
5                                          Candidiasis of mouth
6                                  Enthesopathy of elbow region
7                              Temporomandibular joint disorder
8                                  Intervertebral disc disorder
9                                      Irritable bowel syndrome
10                                     Aseptic necrosis of bone
11                                                     Pleurisy
12                                Fibrocystic disease of breast
13                              Inflammatory disorder of breast
14                                            Effusion of joint
15                                                 Anal finding
16                 Arthropathy associated with another disorder
17                                 Malignant neoplasm of thorax
18                               Candidiasis of urogenital site
19                                                  Tinea pedis
20                                                Dental caries
21                                            Atopic dermatitis
22                                                     Bursitis
23                                             Tietze's disease
24                                  Non-toxic uninodular goiter
25                                                      Rosacea
26                           Benign neoplasm of endocrine gland
27                                                   Paronychia
28                                               Thyrotoxicosis
29                                               Ingrowing nail
30                                                     Impetigo
31                 Infestation by Sarcoptes scabiei var hominis
32                                                Osteomyelitis
33                                          Dysplasia of cervix
34                                                   Paraplegia
35                                Infectious disorder of kidney
36                                     Cystic disease of kidney
37                                   Urge incontinence of urine
38                                                  Prostatitis
39                                                  Hemorrhoids
40                                                     Cystitis
41                                                   Leukorrhea
42                           Inflammatory disease of the uterus
43                                      Hyperplasia of prostate
44                                 Acute renal failure syndrome
45                                                      Dysuria
46                                         Condyloma acuminatum
47                                               Pyelonephritis
48  Inflammatory disease of female pelvic organs AND/OR tissues
49                            Prolapse of female genital organs
50                                                      Ascites
51                                            Injury of abdomen
52                                                 Pneumothorax
53                                             Bronchopneumonia
54                      Pneumonia due to Gram negative bacteria
55                                                        Croup
56                                              Viral pneumonia
57                                                  Atelectasis
58                                                      Dyspnea
59                                       Paroxysmal tachycardia
60                                        Aortic valve disorder
61                                     Tricuspid valve disorder
62                                           Respiratory arrest
63                                              Aortic aneurysm
64                              Gastroesophageal reflux disease
65                                        Mitral valve disorder
66                              Peripheral venous insufficiency
67                                                     Sciatica
68                                                   Presbyopia
69                                                  Tetraplegia
70                                                  Retinopathy
71                                                Hypermetropia
72                                                     Otorrhea
73                                                  Astigmatism
74                                                  Blepharitis
75                                               Otitis externa
76                                         Symbolic dysfunction
77                       Secondary malignant neoplastic disease
78                                 Deficiency of macronutrients
79                                                    Dysthymia
80                                                   Duodenitis
81                      Late effects of cerebrovascular disease
82                                                Iridocyclitis
83                            Staphylococcal infectious disease
84                                      Coxsackie virus disease
85                                          Intracranial injury
86                                                  Hypokalemia
87                                                  Dyspareunia
88                              Pneumococcal infectious disease
89                                                       Lipoma
90                                                 Appendicitis
91                                                  Torticollis
92                                          Open-angle glaucoma
93                                                 Endocarditis
94                                                         Burn
95                                          Barrett's esophagus
96                      Bacterial intestinal infectious disease
97                                           Arthritis of elbow
98                                              Viral hepatitis
99              Primary malignant neoplasm of respiratory tract
100                                   Arthropathy of knee joint
101                                Mononeuropathy of upper limb

Running analyses
*** Creating cohortMethodData objects ***
Loading required package: CohortMethod
Loading required package: Cyclops
Loading required package: FeatureExtraction
Connecting using Oracle driver

Constructing treatment and comparator cohorts
  |                                                                            
  |                                                                      |   0%
  |                                                                            
  |===================================                                   |  50%Error executing SQL: Error in .local(conn, statement, ...): execute JDBC update query failed in dbSendUpdate (ORA-01031: insufficient privileges
)

An error report has been created at  /home/chilton/errorReport.txt
Error in value[[3L]](cond) : no loop for break/next, jumping to top level
Calls: execute ... tryCatch -> tryCatchList -> tryCatchOne -> <Anonymous>
In addition: Warning message:
replacing previous import ‘Rcpp::LdFlags’ by ‘RcppParallel::LdFlags’ when loading ‘Cyclops’ 
Execution halted

[chrisknoll]

Not an oracle expert, but insufficient privs might mean your account you are executing this under does not have rights to create tables in the schema 'OMOPV5_DE', or does not have rights to select from your CDM tables (in this case it's the same schema). I thought we adopted a implementation strategy to create a separate schema for results than the CDM schema to avoid those cases where the CDM schema is read-only.

[charhart]

I can login as that user, and definitely see data. It would have failed a long time before this if that were the case. I think the issue is the create. This script should be using workDatabaseSchema for the create (OHDSI) and should be able to select from cdmDatabaseSchema (OMOPV5_DE).

[chrisknoll]

Ok, so probably a bug in the script: there's a part of the script that's executing something that looks like this:

select {column list}
INTO #cohort_person
FROM {subquery}

Seems to be starting at the point that the console logs "Constructing treatment and comparator cohorts". Are you able to look at the source code of this analysis, and maybe find that line? If it is inserting into a temp table (in sql template looks like #{tablename}), then it's something wrong with SQLRender: the table that is beign created is using the work schema but I believe it should use the oracle temp schema (which you provided, and is set to OHDSI).

@schuemie , any ideas why this would fail?

[charhart]

Looks like it could be this in CohortMethod.

https://github.com/OHDSI/CohortMethod/blob/419e14043cc03b274b93a84063335c5f932b11e6/R/DataLoadingSaving.R#L161

[schuemie]

I think I fixed the problem in this commit. Could you try installing the new version of CohortMethod? Make sure all instances of R are closed, open one instance and run:

devtools::install_github("ohdsi/CohortMethod")

[schuemie]

@cahilton P.s. to speed up things, you can now skip the step of creating the study cohorts (which takes HOW LONG on your system?) by using createCohorts = FALSE:

execute(connectionDetails,
         cdmDatabaseSchema = "OMOPV5_DE",
         workDatabaseSchema = "OHDSI",
         studyCohortTable = cohortTable,
         oracleTempSchema = "OHDSI",
         outputFolder = outputDir,
         maxCores = 4,
         createCohorts = FALSE)

[charhart]

Thanks @schuemie. Updated successfully, and kicking if off again. Also, thanks for the tip about the createCohorts flag. As I'm sure you've heard from @jduke99, our database isn't the most performant... It only took about 12 hours to run the first time.

[charhart]

Alright - I'm past this step now, on to 'Constructing default covariates'. Closing ticket. Thanks again.