Both sides previous revision
Previous revision
Next revision
|
Previous revision
Next revision
Both sides next revision
|
omop_cdm_bigquery_to_postgress_extraction [2020/06/26 09:14] podalv |
omop_cdm_bigquery_to_postgress_extraction [2020/10/11 12:55] 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> |
| |
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 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> |
| |
| |