forked from OHDSI/OHDSIonAWS
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathCMSDESynPUF23m.sql
31 lines (30 loc) · 7.78 KB
/
CMSDESynPUF23m.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 2.3 Million 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 CMSDESynPUF23m;
COPY CARE_SITE FROM 's3://ohdsi-sample-data/cmsdesynpuf2.3/care_site' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop 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) FROM 's3://ohdsi-sample-data/cmsdesynpuf2.3/condition_occurrence' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop 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/cmsdesynpuf2.3/death' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop 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/cmsdesynpuf2.3/device_exposure' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop 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,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/cmsdesynpuf2.3/drug_exposure' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop 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/cmsdesynpuf2.3/location' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop 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/cmsdesynpuf2.3/measurement_occurrence' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop 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/cmsdesynpuf2.3/observation.5.2' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop 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/cmsdesynpuf2.3/observation_period.5.3' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop QUOTE '\b' DATEFORMAT AS 'YYYYMMDD' TIMEFORMAT AS 'YYYYMMDD HH:MI:SS' 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/cmsdesynpuf2.3/payer_plan_period' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop QUOTE '\b' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY PERSON FROM 's3://ohdsi-sample-data/cmsdesynpuf2.3/person' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop 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/cmsdesynpuf2.3/procedure_occurrence' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop 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/cmsdesynpuf2.3/provider' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop QUOTE '\b' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
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) FROM 's3://ohdsi-sample-data/cmsdesynpuf2.3/visit_occurrence' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop 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/cmsdesynpuf2.3/condition_era' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop QUOTE '\b' emptyasnull blanksasnull iam_role 'RS_ROLE_ARN';
COPY DRUG_ERA(DRUG_ERA_ID,PERSON_ID,DRUG_CONCEPT_ID,DRUG_ERA_START_DATE,DRUG_ERA_END_DATE,DRUG_EXPOSURE_COUNT,GAP_DAYS) FROM 's3://ohdsi-sample-data/cmsdesynpuf2.3/drug_era' WITH DELIMITER ',' CSV REGION 'us-east-1' lzop 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';