Shah Lab

User Tools

Site Tools

You are here: start » omop_cdm_bigquery_to_postgress_extraction
omop_cdm_bigquery_to_postgress_extraction

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
Last revision Both sides next revision
omop_cdm_bigquery_to_postgress_extraction [2020/06/26 12:33]
podalv 		omop_cdm_bigquery_to_postgress_extraction [2020/10/12 07:28]
podalv
Line 8: Line 8:
 module load google module load google
 gcloud auth application-default login gcloud auth application-default login
 +
 </code> </code>
  
Line 55: 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.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`; 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> </code>
  
Line 97: 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.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 bq extract --compression GZIP 'som-nero-phi-nigam-starr:starr_omop_cdm5_deid_20190830004605_fbbdcj.vocabulary' gs://cdm-stride8/vocabulary*.csv.gz
 +
 </code> </code>
  
Line 103: Line 106:
 <code> <code>
 gsutil -m cp gs://cdm-stride8/* /share/pi/nigam/podalv/cdm gsutil -m cp gs://cdm-stride8/* /share/pi/nigam/podalv/cdm
 +
 </code> </code>
  
Line 111: Line 115:
 <code> <code>
 sftp podalv@nero.compute.stanford.edu sftp podalv@nero.compute.stanford.edu
 +
 </code> </code>
  
Line 116: Line 121:
  
 <code> <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> </code>
  
Line 123: Line 129:
 <code> <code>
 quit quit
 +
 </code> </code>
  
Line 128: Line 135:
  
 <code> <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> </code>
  
Line 136: Line 144:
 <code> <code>
 rename '.csv' '.csv.gz' /labs/shahlab/projects/cdm/*.csv rename '.csv' '.csv.gz' /labs/shahlab/projects/cdm/*.csv
 +
 </code> </code>
  
Line 146: Line 155:
 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_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 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> </code>
  
Line 170: Line 180:
 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 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 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 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 not null,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 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 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 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 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(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);
Line 183: Line 193:
 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 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 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 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 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,unit_id varchar, unit_id varchar, load_table_id varchar);+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 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 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 bigint 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> </code>
  
Line 1331: Line 1342:
 COPY visit_occurrence FROM '/labs/shahlab/projects/cdm/visit_occurrence000000000016.csv' DELIMITER ',' QUOTE '"' CSV HEADER; 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; COPY vocabulary FROM '/labs/shahlab/projects/cdm/vocabulary000000000000.csv' DELIMITER ',' QUOTE '"' CSV HEADER;
 +
 </code> </code>
  
Line 1511: Line 1523:
 create index note_nlp_note_nlp_concept_id on note_nlp (note_nlp_concept_id); create index note_nlp_note_nlp_concept_id on note_nlp (note_nlp_concept_id);
 ANALYZE verbose; ANALYZE verbose;
 +
 </code> </code>
  
Line 1517: Line 1530:
 <code> <code>
 psql -h ohdsi.shahlab.stanford.edu -e -f sql.txt -p 5432 -d cdm_stride8_20191011 -U achilles psql -h ohdsi.shahlab.stanford.edu -e -f sql.txt -p 5432 -d cdm_stride8_20191011 -U achilles
 +
 </code> </code>
  
  
omop_cdm_bigquery_to_postgress_extraction.txt · Last modified: 2021/01/27 09:18 by podalv

Page Tools

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki