forked from OHDSI/OHDSIonAWS
-
Notifications
You must be signed in to change notification settings - Fork 0
/
CMSDESynPUF100k.sql
31 lines (30 loc) · 7.81 KB
/
CMSDESynPUF100k.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
--CMS DE-SynPUF 100,000 person dataset in OMOP v5.3.1 schema
--SET search_path assigns the SCHEMA for this data, which must be the same at the filename of this file.
--The end of every copy statement must contain iam_role 'RS_ROLE_ARN'; This will be replaced with the actual IAM Role used by Redshift.
--Patient Level Tables
SET search_path to CMSDESynPUF100k;
COPY CARE_SITE FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/care_site' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
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,stop_reason,provider_id,visit_occurrence_id,condition_source_value,condition_source_concept_id,condition_status_source_value,condition_status_concept_id) FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/condition_occurrence' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' DATEFORMAT AS 'YYYY-MM-DD' TIMEFORMAT AS 'YYYY-MM-DD HH:MI:SS' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY DEATH FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/death' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' DATEFORMAT AS 'YYYY-MM-DD' TIMEFORMAT AS 'YYYY-MM-DD HH:MI:SS' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY DEVICE_EXPOSURE(DEVICE_EXPOSURE_ID,PERSON_ID,DEVICE_CONCEPT_ID,DEVICE_EXPOSURE_START_DATE,DEVICE_EXPOSURE_START_DATETIME,DEVICE_EXPOSURE_END_DATE,DEVICE_EXPOSURE_END_DATETIME,DEVICE_TYPE_CONCEPT_ID,UNIQUE_DEVICE_ID,QUANTITY,PROVIDER_ID,VISIT_OCCURRENCE_ID,DEVICE_SOURCE_VALUE,DEVICE_SOURCE_CONCEPT_ID) FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/device_exposure' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' DATEFORMAT AS 'YYYY-MM-DD' TIMEFORMAT AS 'YYYY-MM-DD HH:MI:SS' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
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,drug_source_value,drug_source_concept_id,route_source_value,dose_unit_source_value) FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/drug_exposure' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' DATEFORMAT AS 'YYYY-MM-DD' TIMEFORMAT AS 'YYYY-MM-DD HH:MI:SS' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY LOCATION FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/location' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY MEASUREMENT(measurement_id,person_id,measurement_concept_id,measurement_date,measurement_datetime,measurement_type_concept_id,operator_concept_id,value_as_number,value_as_concept_id,unit_concept_id,range_low,range_high,measurement_source_value,measurement_source_concept_id,unit_source_value,value_source_value) FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/measurement' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' DATEFORMAT AS 'YYYY-MM-DD' TIMEFORMAT AS 'YYYY-MM-DD HH:MI:SS' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
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,OBSERVATION_SOURCE_VALUE,OBSERVATION_SOURCE_CONCEPT_ID,UNIT_SOURCE_VALUE,QUALIFIER_SOURCE_VALUE) FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/observation.' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' DATEFORMAT AS 'YYYY-MM-DD' TIMEFORMAT AS 'YYYY-MM-DD HH:MI:SS' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY OBSERVATION_PERIOD FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/observation_period' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' DATEFORMAT AS 'YYYY-MM-DD' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY PAYER_PLAN_PERIOD(PAYER_PLAN_PERIOD_ID,PERSON_ID,PAYER_PLAN_PERIOD_START_DATE,PAYER_PLAN_PERIOD_END_DATE,PAYER_SOURCE_VALUE,PLAN_SOURCE_VALUE,FAMILY_SOURCE_VALUE) FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/payer_plan_period' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY PERSON FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/person' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY PROCEDURE_OCCURRENCE(PROCEDURE_OCCURRENCE_ID,PERSON_ID,PROCEDURE_CONCEPT_ID,PROCEDURE_DATE,PROCEDURE_DATETIME,PROCEDURE_TYPE_CONCEPT_ID,MODIFIER_CONCEPT_ID,QUANTITY,PROVIDER_ID,VISIT_OCCURRENCE_ID,PROCEDURE_SOURCE_VALUE,PROCEDURE_SOURCE_CONCEPT_ID,MODIFIER_SOURCE_VALUE) FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/procedure_occurrence' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' DATEFORMAT AS 'YYYY-MM-DD' TIMEFORMAT AS 'YYYY-MM-DD HH:MI:SS' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY PROVIDER FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/provider' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY VISIT_OCCURRENCE FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/visit_occurrence' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' DATEFORMAT AS 'YYYY-MM-DD' TIMEFORMAT AS 'YYYY-MM-DD HH:MI:SS' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY CONDITION_ERA(CONDITION_ERA_ID,PERSON_ID,CONDITION_CONCEPT_ID,CONDITION_ERA_START_DATE,CONDITION_ERA_END_DATE,CONDITION_OCCURRENCE_COUNT) FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/condition_era' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY DRUG_ERA(person_id,drug_concept_id,drug_era_start_date,drug_era_end_date,drug_exposure_count,gap_days,drug_era_id) FROM 's3://ohdsi-sample-data/cmsdesynpuf100k/drug_era' WITH DELIMITER ',' CSV REGION 'us-east-1' gzip IGNOREHEADER 1 QUOTE '\b' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
--Vocabulary Tables
COPY CONCEPT_ANCESTOR FROM 's3://ohdsi-sample-data/vocab/CONCEPT_ANCESTOR.csv.bz2' WITH DELIMITER '\t' CSV REGION 'us-east-1' bzip2 IGNOREHEADER 1 QUOTE '\b' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY CONCEPT_CLASS FROM 's3://ohdsi-sample-data/vocab/CONCEPT_CLASS.csv.bz2' WITH DELIMITER '\t' CSV REGION 'us-east-1' bzip2 IGNOREHEADER 1 QUOTE '\b' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY CONCEPT FROM 's3://ohdsi-sample-data/vocab/CONCEPT.csv.bz2' WITH DELIMITER '\t' CSV REGION 'us-east-1' bzip2 IGNOREHEADER 1 QUOTE '\b' DATEFORMAT AS 'YYYYMMDD' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY CONCEPT_RELATIONSHIP FROM 's3://ohdsi-sample-data/vocab/CONCEPT_RELATIONSHIP.csv.bz2' WITH DELIMITER '\t' CSV REGION 'us-east-1' bzip2 IGNOREHEADER 1 QUOTE '\b' emptyasnull blanksasnull DATEFORMAT AS 'YYYYMMDD' iam_role 'RS_ROLE_ARN';
COPY CONCEPT_SYNONYM FROM 's3://ohdsi-sample-data/vocab/CONCEPT_SYNONYM.csv.bz2' WITH DELIMITER '\t' CSV REGION 'us-east-1' bzip2 IGNOREHEADER 1 QUOTE '\b' iam_role 'RS_ROLE_ARN';
COPY DOMAIN FROM 's3://ohdsi-sample-data/vocab/DOMAIN.csv.bz2' WITH DELIMITER '\t' CSV REGION 'us-east-1' bzip2 IGNOREHEADER 1 QUOTE '\b' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY RELATIONSHIP FROM 's3://ohdsi-sample-data/vocab/RELATIONSHIP.csv.bz2' WITH DELIMITER '\t' CSV REGION 'us-east-1' bzip2 IGNOREHEADER 1 QUOTE '\b' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY VOCABULARY FROM 's3://ohdsi-sample-data/vocab/VOCABULARY.csv.bz2' WITH DELIMITER '\t' CSV REGION 'us-east-1' bzip2 IGNOREHEADER 1 QUOTE '\b' DATEFORMAT AS 'YYYYMMDD' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';