This repository has been archived by the owner on Jul 20, 2022. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 627
/
LOAD_OPPR_ALL_DTL_GNRL_12192014.sql
91 lines (85 loc) · 4.17 KB
/
LOAD_OPPR_ALL_DTL_GNRL_12192014.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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
--creates a structure to hold the data in the general payments file.
--Per CMS README
--File #1 - OPPR_ALL_DTL_GNRL_12192014.csv:
--This file contains the data set for identified General Payments for the 2013 program year, including the
--additional payment records added to the publication during the data refresh in December 2014. General Payments are defined --as payments or other transfers of value not made in connection with a research agreement or research protocol.
CREATE TABLE CMS.OPPR_ALL_DTL_GNRL_12192014
(
general_transaction_id integer,
program_year integer,
payment_publication_date date,
submitting_applicable_manufacturer_or_applicable_gpo_name varchar(101),
covered_recipient_type varchar(36),
teaching_hospital_id varchar(5),
teaching_hospital_name varchar(71),
physician_profile_id varchar(7),
physician_first_name varchar(21),
physician_middle_name varchar(21),
physician_last_name varchar(36),
physician_name_suffix varchar(6),
recipient_primary_business_street_address_line1 varchar(56),
recipient_primary_business_street_address_line2 varchar(56),
recipient_city varchar(41),
recipient_state char(2),
recipient_zip_code varchar(11),
recipient_country varchar(37),
recipient_province varchar(17),
recipient_postal_code varchar(11),
physician_primary_type varchar(29),
physician_specialty varchar(135),
physician_license_state_code1 char(2),
physician_license_state_code2 char(2),
physician_license_state_code3 char(2),
physician_license_state_code4 char(2),
physician_license_state_code5 char(2),
product_indicator varchar(12),
name_of_associated_covered_drug_or_biological1 varchar(77),
name_of_associated_covered_drug_or_biological2 varchar(77),
name_of_associated_covered_drug_or_biological3 varchar(77),
name_of_associated_covered_drug_or_biological4 varchar(66),
name_of_associated_covered_drug_or_biological5 varchar(90),
ndc_of_associated_covered_drug_or_biological1 varchar(13),
ndc_of_associated_covered_drug_or_biological2 varchar(13),
ndc_of_associated_covered_drug_or_biological3 varchar(13),
ndc_of_associated_covered_drug_or_biological4 varchar(13),
ndc_of_associated_covered_drug_or_biological5 varchar(13),
name_of_associated_covered_device_or_medical_supply1 varchar(101),
name_of_associated_covered_device_or_medical_supply2 varchar(101),
name_of_associated_covered_device_or_medical_supply3 varchar(101),
name_of_associated_covered_device_or_medical_supply4 varchar(101),
name_of_associated_covered_device_or_medical_supply5 varchar(101),
applicable_manufacturer_or_applicable_gpo_making_payment_name varchar(101),
applicable_manufacturer_or_applicable_gpo_making_payment_id varchar(13),
applicable_manufacturer_or_applicable_gpo_making_payment_state varchar(3),
applicable_manufacturer_or_applicable_gpo_making_payment_country varchar(19),
dispute_status_for_publication varchar(4),
total_amount_of_payment_usdollars float,
date_of_payment date,
number_of_payments_included_in_total_amount varchar(4),
form_of_payment_or_transfer_of_value varchar(53),
nature_of_payment_or_transfer_of_value varchar(147),
city_of_travel varchar(30),
state_of_travel char(2),
country_of_travel varchar(37),
physician_ownership_indicator varchar(4),
third_party_payment_recipient_indicator varchar(23),
name_of_third_party_entity_receiving_payment_or_transfer_of_value varchar(51),
charity_indicator varchar(4),
third_party_equals_covered_recipient_indicator varchar(4),
contextual_information varchar(478),
delay_in_publication_of_general_payment_indicator varchar(3)
)
distkey(physician_profile_id)
sortkey(physician_profile_id)
;
---loads 2.6+ million records. May take between 4 and 10 minutes depending on underlying cluster size
COPY CMS.OPPR_ALL_DTL_GNRL_12192014
from 's3://YOUR_S3_BUCKET/OPPR_ALL_DTL_GNRL_12192014.csv'
REGION 'us-west-2'
CREDENTIALS 'aws_access_key_id=YOUR_KEY_HERE;aws_secret_access_key=YOUR_SECRET_HERE'
IGNOREHEADER 1
DATEFORMAT AS 'MM/DD/YYYY'
csv;
--on error, use this query to find last load record that caused error:
--select * from stl_load_errors
--WHERE starttime = (select max(starttime) from stl_load_errors)