Differences

This shows you the differences between two versions of the page.

--- omop_cdm_bigquery_to_postgress_extraction [2020/04/20 14:19]
podalv
+++ omop_cdm_bigquery_to_postgress_extraction [2021/01/27 09:18] (current)
podalv
@@ Line 8: / Line 8: @@
 module load google
 gcloud auth application-default login
 </code>
@@ Line 42: / Line 43: @@
 create or replace table `som-nero-phi-nigam-starr.starr_omop_cdm5_deid_20200328.note` as select * from  `som-rit-phi-starr-prod.starr_omop_cdm5_deid_latest.note`;
 create or replace table `som-nero-phi-nigam-starr.starr_omop_cdm5_deid_20200328.note_nlp` as select * from  `som-rit-phi-starr-prod.starr_omop_cdm5_deid_latest.note_nlp`;
-#create or replace table `som-nero-phi-nigam-starr.starr_omop_cdm5_deid_20200328.observation` as select * from  `som-rit-phi-starr-prod.starr_omop_cdm5_deid_latest.observation`;
+create or replace table `som-nero-phi-nigam-starr.starr_omop_cdm5_deid_20200328.observation` as select * from  `som-rit-phi-starr-prod.starr_omop_cdm5_deid_latest.observation`;
+create or replace table `som-nero-phi-nigam-starr.starr_omop_cdm5_deid_20200328.observation_no_flowsheets` as select * from `som-rit-phi-starr-prod.starr_omop_cdm5_deid_latest.observation` where load_table_id <> 'shc_ip_flwsht_meas' and load_table_id <> 'lpch_ip_flwsht_meas';
 create or replace table `som-nero-phi-nigam-starr.starr_omop_cdm5_deid_20200328.observation_period` as select * from  `som-rit-phi-starr-prod.starr_omop_cdm5_deid_latest.observation_period`;
 create or replace table `som-nero-phi-nigam-starr.starr_omop_cdm5_deid_20200328.payer_plan_period` as select * from  `som-rit-phi-starr-prod.starr_omop_cdm5_deid_latest.payer_plan_period`;
@@ Line 54: / Line 56: @@
 create or replace table `som-nero-phi-nigam-starr.starr_omop_cdm5_deid_20200328.visit_occurrence` as select * from  `som-rit-phi-starr-prod.starr_omop_cdm5_deid_latest.visit_occurrence`;
 create or replace table `som-nero-phi-nigam-starr.starr_omop_cdm5_deid_20200328.vocabulary` as select * from  `som-rit-phi-starr-prod.starr_omop_cdm5_deid_latest.vocabulary`;
 </code>
@@ Line 59: / Line 62: @@
 <code>
+gsutil -m rm gs://cdm-stride8/*
 bq extract --compression GZIP 'som-nero-phi-nigam-starr:starr_omop_cdm5_deid_20190830004605_fbbdcj.concept' gs://cdm-stride8/concept*.csv.gz
 bq extract --compression GZIP 'som-nero-phi-nigam-starr:starr_omop_cdm5_deid_20190830004605_fbbdcj.attribute_definition' gs://cdm-stride8/attribute_definition*.csv.gz
@@ Line 83: / Line 87: @@
 bq extract --compression GZIP 'som-nero-phi-nigam-starr:starr_omop_cdm5_deid_20190830004605_fbbdcj.note' gs://cdm-stride8/note*.csv.gz
 bq extract --compression GZIP 'som-nero-phi-nigam-starr:starr_omop_cdm5_deid_20190830004605_fbbdcj.note_nlp' gs://cdm-stride8/note_nlp*.csv.gz
-#bq extract --compression GZIP 'som-nero-phi-nigam-starr:starr_omop_cdm5_deid_20190830004605_fbbdcj.observation' gs://cdm-stride8/observation*.csv.gz
+bq extract --compression GZIP 'som-nero-phi-nigam-starr:starr_omop_cdm5_deid_20190830004605_fbbdcj.observation_no_flowsheets' gs://cdm-stride8/observation*.csv.gz
 bq extract --compression GZIP 'som-nero-phi-nigam-starr:starr_omop_cdm5_deid_20190830004605_fbbdcj.observation_period' gs://cdm-stride8/observation_period*.csv.gz
 bq extract --compression GZIP 'som-nero-phi-nigam-starr:starr_omop_cdm5_deid_20190830004605_fbbdcj.payer_plan_period' gs://cdm-stride8/payer_plan_period*.csv.gz
@@ Line 95: / Line 99: @@
 bq extract --compression GZIP 'som-nero-phi-nigam-starr:starr_omop_cdm5_deid_20190830004605_fbbdcj.visit_occurrence' gs://cdm-stride8/visit_occurrence*.csv.gz
 bq extract --compression GZIP 'som-nero-phi-nigam-starr:starr_omop_cdm5_deid_20190830004605_fbbdcj.vocabulary' gs://cdm-stride8/vocabulary*.csv.gz
 </code>
@@ Line 101: / Line 106: @@
 <code>
 gsutil -m cp gs://cdm-stride8/* /share/pi/nigam/podalv/cdm
 </code>
@@ Line 109: / Line 115: @@
 <code>
 sftp podalv@nero.compute.stanford.edu
 </code>
@@ Line 114: / Line 121: @@
 <code>
-get /share/pi/nigam/podalv/cdm/* /vol0/data5/stride8/temp/cdm
+get /share/pi/nigam/podalv/cdm/* /vol0/data5/stride8_070119/temp/cdm
 </code>
@@ Line 121: / Line 129: @@
 <code>
 quit
 </code>
@@ Line 126: / Line 135: @@
 <code>
-   scp /vol0/data5/stride8/temp/cdm/* podalv@shahlab-dev2.stanford.edu:/labs/shahlab/projects/cdm
+   scp /vol0/data5/stride8_070119/temp/cdm/* podalv@shahlab-dev2.stanford.edu:/labs/shahlab/projects/cdm
 </code>
@@ Line 134: / Line 144: @@
 <code>
 rename '.csv' '.csv.gz' /labs/shahlab/projects/cdm/*.csv
 </code>
@@ Line 139: / Line 150: @@
 <code>
-psql -h ohdsi.shahlab.stanford.edu -p 5432 -d starr_omop_cdm_20200404 -U achilles
+psql -h ohdsi.shahlab.stanford.edu -p 5432 -d starr_omop_cdm_20200620 -U achilles
 create database starr_omop_cdm_20200404;
 \q
 psql -h ohdsi.shahlab.stanford.edu -p 5432 -d starr_omop_cdm_20200404 -U achilles
 psql -h ohdsi.shahlab.stanford.edu -e -f /labs/shahlab/projects/podalv/create_tables.sql -p 5432 -d starr_omop_cdm_20200404 -U achilles
 </code>
@@ Line 149: / Line 161: @@
 <code>
-create table attribute_definition(attribute_definition_id INTEGER, attribute_name TEXT, attribute_description TEXT, attribute_type_concept_id INTEGER, attribute_syntax TEXT);
+create table attribute_definition(attribute_definition_id INTEGER, attribute_name TEXT, attribute_description TEXT, attribute_type_concept_id bigint, attribute_syntax TEXT);
-create table concept(concept_id int not null,concept_name text not null,domain_id varchar(20) not null,vocabulary_id varchar(50) not null,concept_class_id varchar(20) not null,standard_concept char null,concept_code text not null,valid_start_date date not null,valid_end_date date not null,invalid_reason char null, load_table_id varchar(25), load_row_id bigint);
+create table concept(concept_id bigint not null,concept_name text not null,domain_id varchar(20) not null,vocabulary_id varchar(50) not null,concept_class_id varchar(20) not null,standard_concept char null,concept_code text not null,valid_start_date date not null,valid_end_date date not null,invalid_reason char null, load_table_id varchar(25), load_row_id bigint);
-create table concept_ancestor(ancestor_concept_id integer not null,descendant_concept_id integer not null,min_levels_of_separation integer not null,max_levels_of_separation integer not null,load_table_id varchar(25), load_row_id bigint);
+create table concept_ancestor(ancestor_concept_id bigint not null,descendant_concept_id bigint not null,min_levels_of_separation integer not null,max_levels_of_separation integer not null,load_table_id varchar(25), load_row_id bigint);
-create table care_site(care_site_id bigint not null,care_site_name varchar(255) not null,place_of_service_concept_id integer not null,location_id integer not null,care_site_source_value varchar(255),place_of_service_source_value varchar(255),trace_id varchar(255),unit_id varchar,load_table_id varchar);
+create table care_site(care_site_id bigint not null,care_site_name varchar(255) not null,place_of_service_concept_id bigint not null,location_id bigint not null,care_site_source_value varchar(255),place_of_service_source_value varchar(255),trace_id varchar(255),unit_id varchar,load_table_id varchar);
 create table cdm_source(cdm_source_name varchar,cdm_source_abbreviation varchar,cdm_holder varchar,source_description varchar,source_documentation_reference varchar,cdm_etl_reference varchar,source_release_DATE date,cdm_release_DATE date,cdm_version varchar,vocabulary_version varchar,unit_id varchar,load_table_id varchar);
-create table concept_class(concept_class_id varchar not null,concept_class_name varchar not null,concept_class_concept_id integer not null,load_table_id varchar, load_row_id bigint);
+create table concept_class(concept_class_id varchar not null,concept_class_name varchar not null,concept_class_concept_id bigint not null,load_table_id varchar, load_row_id bigint);
-create table concept_relationship(concept_id_1 integer not null,concept_id_2 integer not null,relationship_id varchar not null,valid_start_DATE date not null,valid_end_DATE date not null,invalid_reason varchar,load_table_id varchar, load_row_id bigint);
+create table concept_relationship(concept_id_1 bigint not null,concept_id_2 bigint not null,relationship_id varchar not null,valid_start_DATE date not null,valid_end_DATE date not null,invalid_reason varchar,load_table_id varchar, load_row_id bigint);
-create table concept_synonym(concept_id integer,concept_synonym_name varchar,language_concept_id integer,load_table_id varchar, load_row_id bigint);
+create table concept_synonym(concept_id bigint,concept_synonym_name varchar,language_concept_id bigint,load_table_id varchar, load_row_id bigint);
-create table condition_era(condition_era_id bigint not null,person_id bigint not null,condition_concept_id integer,condition_era_start_DATE date not null,condition_era_end_DATE date not null,condition_occurrence_count integer,trace_id varchar, unit_id varchar,load_table_id varchar);
+create table condition_era(condition_era_id bigint not null,person_id bigint not null,condition_concept_id bigint,condition_era_start_DATE date not null,condition_era_end_DATE date not null,condition_occurrence_count integer,trace_id varchar, unit_id varchar,load_table_id varchar);
-create table condition_occurrence(condition_occurrence_id bigint not null,person_id bigint not null,condition_concept_id integer,condition_start_DATE date not null,condition_start_DATETIME timestamp,condition_end_DATE date,condition_end_DATETIME timestamp,condition_type_concept_id int,stop_reason varchar(30),provider_id bigint,visit_occurrence_id bigint,visit_detail_id bigint,condition_source_value varchar(100),condition_source_concept_id int,condition_status_source_value varchar(100),condition_status_concept_id int,trace_id int,unit_id varchar(100),load_table_id varchar(50));create table cost(cost_id bigint,cost_event_id bigint,cost_domain_id bigint,cost_type_concept_id int,currency_concept_id int,total_charge decimal,total_cost decimal,total_paid decimal,paid_by_payer decimal,paid_by_patient decimal,paid_patient_copay decimal,paid_patient_coinsurance decimal,paid_patient_deductible decimal,paid_by_primary decimal,paid_ingredient_cost decimal,paid_dispensing_fee decimal,payer_plan_period_id bigint,amount_allowed decimal,revenue_code_concept_id int,revenue_code_source_value varchar,drg_concept_id int,drg_source_value varchar);
+create table condition_occurrence(condition_occurrence_id bigint not null,person_id bigint not null,condition_concept_id bigint,condition_start_DATE date not null,condition_start_DATETIME timestamp,condition_end_DATE date,condition_end_DATETIME timestamp,condition_type_concept_id bigint,stop_reason varchar(30),provider_id bigint,visit_occurrence_id bigint,visit_detail_id bigint,condition_source_value varchar(100),condition_source_concept_id bigint,condition_status_source_value varchar(100),condition_status_concept_id bigint,trace_id int,unit_id varchar(100),load_table_id varchar(50));create table cost(cost_id bigint,cost_event_id bigint,cost_domain_id bigint,cost_type_concept_id bigint,currency_concept_id bigint,total_charge decimal,total_cost decimal,total_paid decimal,paid_by_payer decimal,paid_by_patient decimal,paid_patient_copay decimal,paid_patient_coinsurance decimal,paid_patient_deductible decimal,paid_by_primary decimal,paid_ingredient_cost decimal,paid_dispensing_fee decimal,payer_plan_period_id bigint,amount_allowed decimal,revenue_code_concept_id bigint,revenue_code_source_value varchar,drg_concept_id bigint,drg_source_value varchar);
-create table death(person_id bigint not null,death_DATE date not null,death_DATETIME timestamp,death_type_concept_id int,cause_concept_id int,cause_source_value varchar,cause_source_concept_id bigint,trace_id varchar,unit_id varchar,load_table_id varchar);
+create table death(person_id bigint not null,death_DATE date not null,death_DATETIME timestamp,death_type_concept_id bigint,cause_concept_id bigint,cause_source_value varchar,cause_source_concept_id bigint,trace_id varchar,unit_id varchar,load_table_id varchar);
-create table device_exposure(device_exposure_id bigint not null,person_id bigint not null,device_concept_id int not null,device_exposure_start_DATE date not null,device_exposure_start_DATETIME timestamp,device_exposure_end_DATE date,device_exposure_end_DATETIME timestamp,device_type_concept_id int,unique_device_id varchar,quantity int,provider_id bigint,visit_occurrence_id bigint,visit_detail_id bigint,device_source_value varchar,device_source_concept_id int,trace_id varchar,unit_id varchar,load_table_id varchar);
+create table device_exposure(device_exposure_id bigint not null,person_id bigint not null,device_concept_id bigint not null,device_exposure_start_DATE date not null,device_exposure_start_DATETIME timestamp,device_exposure_end_DATE date,device_exposure_end_DATETIME timestamp,device_type_concept_id bigint,unique_device_id varchar,quantity int,provider_id bigint,visit_occurrence_id bigint,visit_detail_id bigint,device_source_value varchar,device_source_concept_id bigint,trace_id varchar,unit_id varchar,load_table_id varchar);
-create table domain(domain_id varchar not null,domain_name varchar not null,domain_concept_id int not null,load_table_id varchar, load_row_id bigint);
+create table domain(domain_id varchar not null,domain_name varchar not null,domain_concept_id bigint not null,load_table_id varchar, load_row_id bigint);
-create table dose_era(dose_era_id bigint,person_id bigint,drug_concept_id int,unit_concept_id int,dose_value decimal,dose_era_start_DATE date,dose_era_end_DATE date);
+create table dose_era(dose_era_id bigint,person_id bigint,drug_concept_id bigint,unit_concept_id bigint,dose_value decimal,dose_era_start_DATE date,dose_era_end_DATE date);
-create table drug_era(drug_era_id bigint,person_id bigint,drug_concept_id int,drug_era_start_date date,drug_era_end_date date,drug_exposure_count int,gap_days int,trace_id varchar(50), unit_id varchar,load_table_id varchar);
+create table drug_era(drug_era_id bigint,person_id bigint,drug_concept_id bigint,drug_era_start_date date,drug_era_end_date date,drug_exposure_count int,gap_days int,trace_id varchar(50), unit_id varchar,load_table_id varchar);
-create table drug_exposure(drug_exposure_id bigint not null,person_id bigint not null,drug_concept_id int not null,drug_exposure_start_DATE date not null,drug_exposure_start_DATETIME timestamp,drug_exposure_end_DATE date not null,drug_exposure_end_DATETIME timestamp,verbatim_end_DATE date,drug_type_concept_id int not null,stop_reason varchar,refills int,quantity decimal,days_supply int,sig varchar,route_concept_id int,lot_number varchar,provider_id bigint,visit_occurrence_id bigint,visit_detail_id bigint,drug_source_value varchar,drug_source_concept_id int,route_source_value varchar,dose_unit_source_value varchar,trace_id varchar,unit_id varchar,load_table_id varchar);
+create table drug_exposure(drug_exposure_id bigint not null,person_id bigint not null,drug_concept_id bigint not null,drug_exposure_start_DATE date not null,drug_exposure_start_DATETIME timestamp,drug_exposure_end_DATE date not null,drug_exposure_end_DATETIME timestamp,verbatim_end_DATE date,drug_type_concept_id bigint not null,stop_reason varchar,refills int,quantity decimal,days_supply int,sig varchar,route_concept_id bigint,lot_number varchar,provider_id bigint,visit_occurrence_id bigint,visit_detail_id bigint,drug_source_value varchar,drug_source_concept_id bigint,route_source_value varchar,dose_unit_source_value varchar,trace_id varchar,unit_id varchar,load_table_id varchar);
-create table drug_strength(drug_concept_id int not null,ingredient_concept_id int not null,amount_value decimal,amount_unit_concept_id int,numerator_value decimal,numerator_unit_concept_id int,denominator_value decimal,denominator_unit_concept_id int,box_size int,valid_start_DATE date not null,valid_end_DATE date not null,invalid_reason varchar,load_table_id varchar, load_row_id bigint);
+create table drug_strength(drug_concept_id bigint not null,ingredient_concept_id bigint not null,amount_value decimal,amount_unit_concept_id bigint,numerator_value decimal,numerator_unit_concept_id bigint,denominator_value decimal,denominator_unit_concept_id bigint,box_size int,valid_start_DATE date not null,valid_end_DATE date not null,invalid_reason varchar,load_table_id varchar, load_row_id bigint);
 create table fact_relationship(domain_concept_id_1 bigint not null,fact_id_1 bigint not null,domain_concept_id_2 bigint not null,fact_id_2 bigint not null,relationship_concept_id bigint not null,trace_id varchar,unit_id varchar,load_table_id varchar);
 create table location(location_id bigint not null,address_1 varchar,address_2 varchar,city varchar,state varchar,zip varchar,county varchar,location_source_value varchar,unit_id varchar,load_table_id varchar);
-create table measurement(measurement_id bigint not null,person_id bigint,measurement_concept_id int not null,measurement_DATE date not null,measurement_DATETIME timestamp,measurement_time varchar,measurement_type_concept_id int not null,operator_concept_id int not null,value_as_number decimal,value_as_concept_id int,unit_concept_id int,range_low decimal,range_high decimal,provider_id bigint,visit_occurrence_id bigint,visit_detail_id bigint,measurement_source_value varchar,measurement_source_concept_id int,unit_source_value varchar,value_source_value varchar,trace_id varchar,unit_id varchar,load_table_id varchar);
+create table measurement(measurement_id bigint not null,person_id bigint,measurement_concept_id bigint not null,measurement_DATE date not null,measurement_DATETIME timestamp,measurement_time varchar,measurement_type_concept_id bigint not null,operator_concept_id bigint,value_as_number decimal,value_as_concept_id bigint,unit_concept_id bigint,range_low decimal,range_high decimal,provider_id bigint,visit_occurrence_id bigint,visit_detail_id bigint,measurement_source_value varchar,measurement_source_concept_id bigint,unit_source_value varchar,value_source_value varchar,trace_id varchar,unit_id varchar,load_table_id varchar);
-create table metadata(metadata_concept_id int not null,metadata_type_concept_id int not null,name varchar not null,value_as_string varchar,value_as_concept_id int,metadata_DATE date,metadata_DATETIME timestamp,unit_id varchar,load_table_id varchar);
+create table metadata(metadata_concept_id bigint not null,metadata_type_concept_id bigint not null,name varchar not null,value_as_string varchar,value_as_concept_id bigint,metadata_DATE date,metadata_DATETIME timestamp,unit_id varchar,load_table_id varchar);
-create table note(note_id bigint not null,person_id bigint not null, note_DATE date, note_DATETIME timestamp, note_type_concept_id int, note_class_concept_id int, note_title varchar(250), note_text TEXT, encoding_concept_id int, language_concept_id int, provider_id int, visit_occurrence_id bigint,visit_detail_id bigint, note_source_value bigint);
+create table note(note_id bigint not null,person_id bigint not null, note_DATE date, note_DATETIME timestamp, note_type_concept_id bigint, note_class_concept_id bigint, note_title varchar(250), note_text TEXT, encoding_concept_id bigint, language_concept_id bigint, provider_id int, visit_occurrence_id bigint,visit_detail_id bigint, note_source_value bigint);
-create table note_nlp(note_nlp_id bigint, note_id bigint, section_concept_id bigint, snippet varchar, offset_nr varchar, lexical_variant varchar, note_nlp_concept_id int, note_nlp_source_concept_id int, nlp_system varchar, nlp_DATE date, nlp_DATETIME timestamp, term_exists varchar, term_temporal varchar, term_modifiers varchar);
+create table note_nlp(note_nlp_id bigint, note_id bigint, section_concept_id bigint, snippet varchar, offset_nr varchar, lexical_variant varchar, note_nlp_concept_id bigint, note_nlp_source_concept_id bigint, nlp_system varchar, nlp_DATE date, nlp_DATETIME timestamp, term_exists varchar, term_temporal varchar, term_modifiers varchar);
-create table observation(observation_id bigint not null,person_id bigint not null,observation_concept_id int not null,observation_DATE date not null,observation_DATETIME timestamp,observation_type_concept_id int not null,value_as_number decimal,value_as_string varchar,value_as_concept_id int,qualifier_concept_id int,unit_concept_id int,provider_id bigint,visit_occurrence_id bigint,visit_detail_id bigint,observation_source_value varchar,observation_source_concept_id int,unit_source_value varchar,qualifier_source_value varchar,trace_id varchar,unit_id varchar,load_table_id varchar);
+create table observation(observation_id bigint not null,person_id bigint not null,observation_concept_id bigint not null,observation_DATE date not null,observation_DATETIME timestamp,observation_type_concept_id bigint not null,value_as_number decimal,value_as_string varchar,value_as_concept_id bigint,qualifier_concept_id bigint,unit_concept_id bigint,provider_id bigint,visit_occurrence_id bigint,visit_detail_id bigint,observation_source_value varchar,observation_source_concept_id bigint,unit_source_value varchar,qualifier_source_value varchar,trace_id varchar,unit_id varchar,load_table_id varchar);
-create table observation_period(observation_period_id bigint not null,person_id bigint not null,observation_period_start_DATE date not null,observation_period_end_DATE date not null,period_type_concept_id int not null,trace_id varchar,unit_id varchar,load_table_id varchar);
+create table observation_period(observation_period_id bigint not null,person_id bigint not null,observation_period_start_DATE date not null,observation_period_end_DATE date not null,period_type_concept_id bigint not null,trace_id varchar,unit_id varchar,load_table_id varchar);
-create table payer_plan_period(payer_plan_period_id bigint,person_id bigint,payer_plan_period_start_DATE date,payer_plan_period_end_DATE date,payer_concept_id int,payer_source_value varchar,payer_source_concept_id bigint,plan_concept_id int,plan_source_value varchar,plan_source_concept_id int,sponsor_concept_id int,sponsor_source_value varchar,sponsor_source_concept_id int,family_source_value varchar,stop_reason_concept_id int,stop_reason_source_value varchar,stop_reason_source_concept_id int);
+create table payer_plan_period(payer_plan_period_id bigint,person_id bigint,payer_plan_period_start_DATE date,payer_plan_period_end_DATE date,payer_concept_id bigint,payer_source_value varchar,payer_source_concept_id bigint,plan_concept_id bigint,plan_source_value varchar,plan_source_concept_id bigint,sponsor_concept_id bigint,sponsor_source_value varchar,sponsor_source_concept_id bigint,family_source_value varchar,stop_reason_concept_id bigint,stop_reason_source_value varchar,stop_reason_source_concept_id bigint);
-create table person(person_id bigint not null,gender_concept_id int not null,year_of_birth int not null,month_of_birth int,day_of_birth int,birth_DATETIME timestamp,race_concept_id int not null,ethnicity_concept_id int not null,location_id bigint,provider_id bigint,care_site_id bigint,person_source_value varchar,gender_source_value varchar,gender_source_concept_id int,race_source_value varchar,race_source_concept_id int,ethnicity_source_value varchar,ethnicity_source_concept_id int,trace_id varchar,unit_id varchar,load_table_id varchar);
+create table person(person_id bigint not null,gender_concept_id bigint not null,year_of_birth int not null,month_of_birth int,day_of_birth int,birth_DATETIME timestamp,race_concept_id bigint not null,ethnicity_concept_id bigint not null,location_id bigint,provider_id bigint,care_site_id bigint,person_source_value varchar,gender_source_value varchar,gender_source_concept_id bigint,race_source_value varchar,race_source_concept_id bigint,ethnicity_source_value varchar,ethnicity_source_concept_id bigint,trace_id varchar,unit_id varchar,load_table_id varchar);
-create table procedure_occurrence(procedure_occurrence_id bigint not null,person_id bigint not null,procedure_concept_id int not null,procedure_DATE date not null,procedure_DATETIME timestamp,procedure_type_concept_id int not null,modifier_concept_id int,quantity int,provider_id bigint,visit_occurrence_id bigint,visit_detail_id bigint,procedure_source_value varchar,procedure_source_concept_id int,modifier_source_value varchar,trace_id varchar,unit_id varchar,load_table_id varchar);
+create table procedure_occurrence(procedure_occurrence_id bigint not null,person_id bigint not null,procedure_concept_id bigint not null,procedure_DATE date not null,procedure_DATETIME timestamp,procedure_type_concept_id bigint not null,modifier_concept_id bigint,quantity int,provider_id bigint,visit_occurrence_id bigint,visit_detail_id bigint,procedure_source_value varchar,procedure_source_concept_id bigint,modifier_source_value varchar,trace_id varchar,unit_id varchar,load_table_id varchar);
-create table provider(provider_id bigint not null,provider_name varchar,npi varchar,dea varchar,specialty_concept_id int,care_site_id bigint,year_of_birth int,gender_concept_id int,provider_source_value varchar,specialty_source_value varchar,specialty_source_concept_id int,gender_source_value varchar,gender_source_concept_id int,trace_id varchar,unit_id varchar,load_table_id varchar);
+create table provider(provider_id bigint not null,provider_name varchar,npi varchar,dea varchar,specialty_concept_id bigint,care_site_id bigint,year_of_birth int,gender_concept_id bigint,provider_source_value varchar,specialty_source_value varchar,specialty_source_concept_id bigint,gender_source_value varchar,gender_source_concept_id bigint,trace_id varchar,unit_id varchar,load_table_id varchar);
-create table relationship(relationship_id varchar not null,relationship_name varchar,is_hierarchical varchar not null,defines_ancestry varchar not null,reverse_relationship_id varchar not null,relationship_concept_id int not null,load_table_id varchar, load_row_id bigint);
+create table relationship(relationship_id varchar not null,relationship_name varchar,is_hierarchical varchar not null,defines_ancestry varchar not null,reverse_relationship_id varchar not null,relationship_concept_id bigint not null,load_table_id varchar, load_row_id bigint);
-create table source_to_concept_map(source_code varchar,source_concept_id int,source_vocabulary_id varchar,source_code_description varchar,target_concept_id int,target_vocabulary_id varchar,valid_start_DATE date,valid_end_DATE date,invalid_reason varchar, load_table_id varchar, load_row_id bigint);
+create table source_to_concept_map(source_code varchar,source_concept_id bigint,source_vocabulary_id varchar,source_code_description varchar,target_concept_id bigint,target_vocabulary_id varchar,valid_start_DATE date,valid_end_DATE date,invalid_reason varchar, load_table_id varchar, load_row_id bigint);
-create table specimen(specimen_id bigint,person_id bigint,specimen_concept_id int,specimen_type_concept_id int,specimen_DATE date,specimen_DATETIME timestamp,quantity decimal,unit_concept_id int,anatomic_site_concept_id int,disease_status_concept_id int,specimen_source_id bigint,specimen_source_value varchar,unit_source_value varchar,anatomic_site_source_value varchar,disease_status_source_value varchar);
+create table specimen(specimen_id bigint,person_id bigint,specimen_concept_id bigint,specimen_type_concept_id bigint,specimen_DATE date,specimen_DATETIME timestamp,quantity decimal,unit_concept_id bigint,anatomic_site_concept_id bigint,disease_status_concept_id bigint,specimen_source_id bigint,specimen_source_value varchar,unit_source_value varchar,anatomic_site_source_value varchar,disease_status_source_value varchar);
-create table visit_detail(visit_detail_id bigint,person_id bigint,visit_detail_concept_id int,visit_detail_start_DATE date,visit_detail_start_DATETIME timestamp,visit_detail_end_DATE date,visit_detail_end_DATETIME timestamp,visit_detail_type_concept_id int,provider_id bigint,care_site_id bigint,admitting_source_concept_id int,discharge_to_concept_id int,preceding_visit_detail_id bigint,visit_detail_source_value varchar,visit_detail_source_concept_id int,admitting_source_value varchar,discharge_to_source_value varchar,visit_detail_parent_id bigint,visit_occurrence_id bigint);
+create table visit_detail(visit_detail_id bigint,person_id bigint,visit_detail_concept_id bigint,visit_detail_start_DATE date,visit_detail_start_DATETIME timestamp,visit_detail_end_DATE date,visit_detail_end_DATETIME timestamp,visit_detail_type_concept_id bigint,provider_id bigint,care_site_id bigint,admitting_source_concept_id bigint,discharge_to_concept_id bigint,preceding_visit_detail_id bigint,visit_detail_source_value varchar,visit_detail_source_concept_id bigint,admitting_source_value varchar,discharge_to_source_value varchar,visit_detail_parent_id bigint,visit_occurrence_id bigint,trace_id varchar, unit_id varchar, load_table_id varchar);
-create table visit_occurrence(visit_occurrence_id bigint not null,person_id bigint not null,visit_concept_id int not null,visit_start_DATE date not null,visit_start_DATETIME timestamp,visit_end_DATE date not null,visit_end_DATETIME timestamp,visit_type_concept_id int not null,provider_id bigint,care_site_id bigint,visit_source_value varchar,visit_source_concept_id int,admitting_source_concept_id int,admitting_source_value varchar,discharge_to_concept_id int,discharge_to_source_value varchar,preceding_visit_occurrence_id bigint,trace_id varchar,unit_id varchar,load_table_id varchar);
+create table visit_occurrence(visit_occurrence_id bigint not null,person_id bigint not null,visit_concept_id bigint not null,visit_start_DATE date not null,visit_start_DATETIME timestamp,visit_end_DATE date not null,visit_end_DATETIME timestamp,visit_type_concept_id bigint not null,provider_id bigint,care_site_id bigint,visit_source_value varchar,visit_source_concept_id bigint,admitting_source_concept_id bigint,admitting_source_value varchar,discharge_to_concept_id bigint,discharge_to_source_value varchar,preceding_visit_occurrence_id bigint,trace_id varchar,unit_id varchar,load_table_id varchar);
-create table vocabulary(vocabulary_id varchar not null,vocabulary_name varchar not null,vocabulary_reference varchar not null,vocabulary_version varchar,vocabulary_concept_id int not null,load_table_id varchar, load_row_id bigint);
+create table vocabulary(vocabulary_id varchar not null,vocabulary_name varchar not null,vocabulary_reference varchar not null,vocabulary_version varchar,vocabulary_concept_id bigint not null,load_table_id varchar, load_row_id bigint);
 </code>
@@ Line 233: / Line 246: @@
 COPY condition_occurrence FROM '/labs/shahlab/projects/cdm/condition_occurrence000000000014.csv' DELIMITER ',' QUOTE '"' CSV HEADER;
 COPY condition_occurrence FROM '/labs/shahlab/projects/cdm/condition_occurrence000000000015.csv' DELIMITER ',' QUOTE '"' CSV HEADER;
+COPY condition_occurrence FROM '/labs/shahlab/projects/cdm/condition_occurrence000000000016.csv' DELIMITER ',' QUOTE '"' CSV HEADER;
 COPY cost FROM '/labs/shahlab/projects/cdm/cost000000000000.csv' DELIMITER ',' QUOTE '"' CSV HEADER;
 COPY death FROM '/labs/shahlab/projects/cdm/death000000000000.csv' DELIMITER ',' QUOTE '"' CSV HEADER;
@@ Line 1328: / Line 1342: @@
 COPY visit_occurrence FROM '/labs/shahlab/projects/cdm/visit_occurrence000000000016.csv' DELIMITER ',' QUOTE '"' CSV HEADER;
 COPY vocabulary FROM '/labs/shahlab/projects/cdm/vocabulary000000000000.csv' DELIMITER ',' QUOTE '"' CSV HEADER;
 </code>
@@ Line 1508: / Line 1523: @@
 create index note_nlp_note_nlp_concept_id on note_nlp (note_nlp_concept_id);
 ANALYZE verbose;
 </code>
@@ Line 1514: / Line 1530: @@
 <code>
 psql -h ohdsi.shahlab.stanford.edu -e -f sql.txt -p 5432 -d cdm_stride8_20191011 -U achilles
 </code>

Shah Lab

User Tools

Site Tools

Differences

Page Tools