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
omop_cdm_bigquery_to_postgress_extraction [2020/06/26 12:35]
podalv 		omop_cdm_bigquery_to_postgress_extraction [2021/01/27 09:18] (current)
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 141: Line 150:
  
 <code> <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; create database starr_omop_cdm_20200404;
 \q \q
 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 186: Line 196:
 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.1593200104.txt.gz · Last modified: 2020/06/26 12:35 by podalv

Page Tools

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki