Both sides previous revision
Previous revision
Next revision
|
Previous revision
|
omop_cdm_bigquery_to_postgress_extraction [2020/06/26 09:14] podalv |
omop_cdm_bigquery_to_postgress_extraction [2021/01/27 09:18] (current) podalv |
module load google | module load google |
gcloud auth application-default login | gcloud auth application-default login |
| |
</code> | </code> |
| |
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> |
| |
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> |
| |
<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> |
| |
<code> | <code> |
sftp podalv@nero.compute.stanford.edu | sftp podalv@nero.compute.stanford.edu |
| |
</code> | </code> |
| |
| |
<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> |
| |
<code> | <code> |
quit | quit |
| |
</code> | </code> |
| |
| |
<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> |
| |
<code> | <code> |
rename '.csv' '.csv.gz' /labs/shahlab/projects/cdm/*.csv | rename '.csv' '.csv.gz' /labs/shahlab/projects/cdm/*.csv |
| |
</code> | </code> |
| |
| |
<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> |
| |
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); |
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); | 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> |
| |
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> |
| |
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> |
| |
<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> |
| |
| |