huyuaaaray
commented
1 month ago vocabulary:
docker exec -it omopv54 bash
psql -h ahc-hobi-gpu01.ahc.ufl.edu -p 5432 -U postgres -W -d adrd1
\copy DRUG_STRENGTH FROM '/hexing/DRUG_STRENGTH.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;
\copy CONCEPT FROM '/hexing/CONCEPT.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;
\copy CONCEPT_RELATIONSHIP FROM '/hexing/CONCEPT_RELATIONSHIP.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;
\copy CONCEPT_ANCESTOR FROM '/hexing/CONCEPT_ANCESTOR.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;
\copy CONCEPT_SYNONYM FROM '/hexing/CONCEPT_SYNONYM.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;
\copy VOCABULARY FROM '/hexing/VOCABULARY.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;
\copy RELATIONSHIP FROM '/hexing/RELATIONSHIP.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;
\copy CONCEPT_CLASS FROM '/hexing/CONCEPT_CLASS.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;
\copy DOMAIN FROM '/hexing/DOMAIN.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;or psql -h ahc-hobi-gpu01.ahc.ufl.edu -p 5432 -U postgres -W -d adrd1 -f '/new_adrd/OMOP CDM vocabulary load - PostgreSQL.sql'
return
COPY 2981807
COPY 7407686
COPY 46545874
COPY 80436514
COPY 2741949
COPY 91
COPY 696
COPY 423
COPY 50
psql -h localhost -p 5432 -U postgres -W -d adrd1 -f /omop_ddl/OMOPCDM_postgresql_5.4_ddl.sqlCOPY person ( person_id, gender_concept_id, year_of_birth, month_of_birth, day_of_birth, birth_datetime, race_concept_id, ethnicity_concept_id, location_id, provider_id, care_site_id, person_source_value, gender_source_value, gender_source_concept_id, race_source_value, race_source_concept_id, ethnicity_source_value, ethnicity_source_concept_id ) FROM '/csv_data/person.csv' WITH (DELIMITER ',', FORMAT 'csv', HEADER, NULL '');
ALTER TABLE condition_occurrence ALTER COLUMN person_id TYPE bigint;
ALTER TABLE condition_occurrence ALTER COLUMN provider_id TYPE float;
ALTER TABLE condition_occurrence ALTER COLUMN visit_occurrence_id TYPE bigint;
ALTER TABLE condition_occurrence ALTER COLUMN visit_detail_id TYPE bigint;
COPY CONDITION_OCCURRENCE( condition_occurrence_id, person_id, condition_concept_id, condition_start_date, condition_start_datetime, condition_end_date, condition_end_datetime, condition_type_concept_id, condition_status_concept_id, stop_reason, provider_id, visit_occurrence_id, visit_detail_id, condition_source_value, condition_source_concept_id, condition_status_source_value ) FROM '/csv_data/condition_occurrence.csv' WITH DELIMITER ',' CSV HEADER NULL '';
ALTER TABLE DEATH ALTER COLUMN person_id TYPE bigint;
COPY DEATH(person_id,death_date,death_datetime,death_type_concept_id,cause_concept_id,cause_source_value,cause_source_concept_id) FROM '/csv_data/death.csv' WITH DELIMITER ',' CSV HEADER;
ALTER TABLE DRUG_EXPOSURE ALTER COLUMN person_id TYPE bigint;
ALTER TABLE DRUG_EXPOSURE ALTER COLUMN provider_id TYPE float;
ALTER TABLE DRUG_EXPOSURE ALTER COLUMN visit_occurrence_id TYPE bigint;
ALTER TABLE DRUG_EXPOSURE ALTER COLUMN visit_detail_id TYPE bigint;
ALTER TABLE drug_exposure ALTER COLUMN refills TYPE float;
COPY DRUG_EXPOSURE(drug_exposure_id,person_id,drug_concept_id,drug_exposure_start_date,drug_exposure_start_datetime,drug_exposure_end_date,drug_exposure_end_datetime,verbatim_end_date,drug_type_concept_id,stop_reason,refills,quantity,days_supply,sig,route_concept_id,lot_number,provider_id,visit_occurrence_id,visit_detail_id,drug_source_value,drug_source_concept_id,route_source_value,dose_unit_source_value) FROM '/csv_data/drug_exposure.csv' WITH DELIMITER ',' CSV HEADER;
COPY LOCATION(location_id,address_1,address_2,city,state,zip,county,location_source_value,country_concept_id,country_source_value,latitude,longitude) FROM '/csv_data/location.csv' WITH DELIMITER ',' CSV HEADER;
ALTER TABLE measurement ADD COLUMN modifier_of_event_id integer;
ALTER TABLE measurement ADD COLUMN modifier_of_field_concept_id integer;
ALTER TABLE measurement ALTER COLUMN person_id TYPE bigint;
ALTER TABLE measurement ALTER COLUMN unit_concept_id TYPE float;
ALTER TABLE measurement ALTER COLUMN provider_id TYPE float;
ALTER TABLE measurement ALTER COLUMN visit_occurrence_id TYPE bigint;
ALTER TABLE measurement ALTER COLUMN visit_detail_id TYPE bigint;
ALTER TABLE measurement ALTER COLUMN value_as_concept_id TYPE float;
ALTER TABLE measurement ALTER COLUMN unit_source_concept_id TYPE float;
COPY MEASUREMENT(measurement_id,person_id,measurement_concept_id,measurement_date,measurement_datetime,measurement_time,measurement_type_concept_id,operator_concept_id,value_as_number,value_as_concept_id,unit_concept_id,range_low,range_high,provider_id,visit_occurrence_id,visit_detail_id,measurement_source_value,measurement_source_concept_id,unit_source_value,unit_source_concept_id,value_source_value,measurement_event_id,meas_event_field_concept_id,modifier_of_event_id,modifier_of_field_concept_id) FROM '/csv_data/measurement.csv' WITH DELIMITER ',' CSV HEADER;
ALTER TABLE OBSERVATION ALTER COLUMN person_id TYPE bigint;
ALTER TABLE OBSERVATION ALTER COLUMN provider_id TYPE float;
ALTER TABLE OBSERVATION ALTER COLUMN visit_occurrence_id TYPE bigint;
ALTER TABLE OBSERVATION ALTER COLUMN visit_detail_id TYPE bigint;
ALTER TABLE OBSERVATION ALTER COLUMN value_as_concept_id TYPE float;
ALTER TABLE OBSERVATION ALTER COLUMN unit_concept_id TYPE float;
COPY OBSERVATION(observation_id,person_id,observation_concept_id,observation_date,observation_datetime,observation_type_concept_id,value_as_number,value_as_string,value_as_concept_id,qualifier_concept_id,unit_concept_id,provider_id,visit_occurrence_id,visit_detail_id,observation_source_value,observation_source_concept_id,unit_source_value,qualifier_source_value,value_source_value,observation_event_id,obs_event_field_concept_id) FROM '/csv_data/observation.csv' WITH DELIMITER ',' CSV HEADER;
ALTER TABLE OBSERVATION_PERIOD ALTER COLUMN person_id TYPE bigint;
COPY OBSERVATION_PERIOD(observation_period_id,person_id,observation_period_start_date,observation_period_end_date,period_type_concept_id) FROM '/csv_data/observation_period.csv' WITH DELIMITER ',' CSV HEADER;
ALTER TABLE PROCEDURE_OCCURRENCE ALTER COLUMN person_id TYPE bigint;
ALTER TABLE PROCEDURE_OCCURRENCE ALTER COLUMN modifier_concept_id TYPE float;
ALTER TABLE PROCEDURE_OCCURRENCE ALTER COLUMN quantity TYPE float;
ALTER TABLE PROCEDURE_OCCURRENCE ALTER COLUMN provider_id TYPE float;
ALTER TABLE PROCEDURE_OCCURRENCE ALTER COLUMN visit_occurrence_id TYPE float;
ALTER TABLE PROCEDURE_OCCURRENCE ALTER COLUMN visit_detail_id TYPE float;
COPY PROCEDURE_OCCURRENCE(procedure_occurrence_id,person_id,procedure_concept_id,procedure_date,procedure_datetime,procedure_end_date,procedure_end_datetime,procedure_type_concept_id,modifier_concept_id,quantity,provider_id,visit_occurrence_id,visit_detail_id,procedure_source_value,procedure_source_concept_id,modifier_source_value) FROM '/csv_data/procedure_occurrence.csv' WITH DELIMITER ',' CSV HEADER;
ALTER TABLE visit_occurrence ADD COLUMN admitting_source_concept_id integer;
ALTER TABLE visit_occurrence ADD COLUMN admitting_source_value character varying(255);
ALTER TABLE visit_occurrence ADD COLUMN discharge_to_concept_id integer;
ALTER TABLE visit_occurrence ADD COLUMN discharge_to_source_value integer;
ALTER TABLE visit_occurrence ALTER COLUMN visit_occurrence_id TYPE bigint;
ALTER TABLE visit_occurrence ALTER COLUMN person_id TYPE bigint;
ALTER TABLE visit_occurrence ALTER COLUMN provider_id TYPE float;
ALTER TABLE visit_occurrence ALTER COLUMN care_site_id TYPE float;
ALTER TABLE visit_occurrence ALTER COLUMN discharge_to_source_value TYPE character varying(255);
ALTER TABLE visit_occurrence ALTER COLUMN admitting_source_concept_id TYPE float;
COPY VISIT_OCCURRENCE(visit_occurrence_id,person_id,visit_concept_id,visit_start_date,visit_start_datetime,visit_end_date,visit_end_datetime,visit_type_concept_id,provider_id,care_site_id,visit_source_value,visit_source_concept_id,admitting_source_concept_id,admitting_source_value,discharge_to_concept_id,discharge_to_source_value,preceding_visit_occurrence_id) FROM '/csv_data/visit_occurrence.csv' WITH DELIMITER ',' CSV HEADER;