hive.ddl

Create database cms;

use cms;

drop table cms.ownership;

create external table cms.ownership(
change_type string,
physician_profile_id string,
physician_first_name string,
physician_middle_name string,
physician_last_name string,
physician_name_suffix string,
recipient_primary_business_street_address_line1 string,
recipient_primary_business_street_address_line2 string,
recipient_city string,
recipient_state string,
recipient_zip_code string,
recipient_country string,
recipient_province string,
recipient_postal_code string,
physician_primary_type string,
physician_specialty string,
record_id string,
program_year string,
total_amount_invested_usdollars decimal(18,3),
value_of_interest decimal(18,3),
terms_of_interest string,
submitting_applicable_manufacturer_or_applicable_gpo_name string,
applicable_manufacturer_or_applicable_gpo_making_payment_id string,
applicable_manufacturer_or_applicable_gpo_making_payment_name string,
applicable_manufacturer_or_applicable_gpo_making_payment_state string,
applicable_manufacturer_or_applicable_gpo_making_payment_country string,
dispute_status_for_publication string,
interest_held_by_physician_or_an_immediate_family_member string,
payment_publication_date string
)
row format delimited
fields terminated by ','
stored as textfile
location '/user/frothkoetter/cms.db/ownership/'
;
select * from cms.ownership limit 3;

drop table if exists cms.researchpayments;


create external table cms.researchpayments(
change_type string,
covered_recipient_type string,
noncovered_recipient_entity_name string,
teaching_hospital_ccn string,
teaching_hospital_id string,
teaching_hospital_name string,
physician_profile_id string,
physician_first_name string,
physician_middle_name string,
physician_last_name string,
physician_name_suffix string,
recipient_primary_business_street_address_line1 string,
recipient_primary_business_street_address_line2 string,
recipient_city string,
recipient_state string,
recipient_zip_code string,
recipient_country string,
recipient_province string,
recipient_postal_code string,
physician_primary_type string,
physician_specialty string,
physician_license_state_code1 string,
physician_license_state_code2 string,
physician_license_state_code3 string,
physician_license_state_code4 string,
physician_license_state_code5 string,
principal_investigator_1_profile_id string,
principal_investigator_1_first_name string,
principal_investigator_1_middle_name string,
principal_investigator_1_last_name string,
principal_investigator_1_name_suffix string,
principal_investigator_1_business_street_address_line1 string,
principal_investigator_1_business_street_address_line2 string,
principal_investigator_1_city string,
principal_investigator_1_state string,
principal_investigator_1_zip_code string,
principal_investigator_1_country string,
principal_investigator_1_province string,
principal_investigator_1_postal_code string,
principal_investigator_1_primary_type string,
principal_investigator_1_specialty string,
principal_investigator_1_license_state_code1 string,
principal_investigator_1_license_state_code2 string,
principal_investigator_1_license_state_code3 string,
principal_investigator_1_license_state_code4 string,
principal_investigator_1_license_state_code5 string,
principal_investigator_2_profile_id string,
principal_investigator_2_first_name string,
principal_investigator_2_middle_name string,
principal_investigator_2_last_name string,
principal_investigator_2_name_suffix string,
principal_investigator_2_business_street_address_line1 string,
principal_investigator_2_business_street_address_line2 string,
principal_investigator_2_city string,
principal_investigator_2_state string,
principal_investigator_2_zip_code string,
principal_investigator_2_country string,
principal_investigator_2_province string,
principal_investigator_2_postal_code string,
principal_investigator_2_primary_type string,
principal_investigator_2_specialty string,
principal_investigator_2_license_state_code1 string,
principal_investigator_2_license_state_code2 string,
principal_investigator_2_license_state_code3 string,
principal_investigator_2_license_state_code4 string,
principal_investigator_2_license_state_code5 string,
principal_investigator_3_profile_id string,
principal_investigator_3_first_name string,
principal_investigator_3_middle_name string,
principal_investigator_3_last_name string,
principal_investigator_3_name_suffix string,
principal_investigator_3_business_street_address_line1 string,
principal_investigator_3_business_street_address_line2 string,
principal_investigator_3_city string,
principal_investigator_3_state string,
principal_investigator_3_zip_code string,
principal_investigator_3_country string,
principal_investigator_3_province string,
principal_investigator_3_postal_code string,
principal_investigator_3_primary_type string,
principal_investigator_3_specialty string,
principal_investigator_3_license_state_code1 string,
principal_investigator_3_license_state_code2 string,
principal_investigator_3_license_state_code3 string,
principal_investigator_3_license_state_code4 string,
principal_investigator_3_license_state_code5 string,
principal_investigator_4_profile_id string,
principal_investigator_4_first_name string,
principal_investigator_4_middle_name string,
principal_investigator_4_last_name string,
principal_investigator_4_name_suffix string,
principal_investigator_4_business_street_address_line1 string,
principal_investigator_4_business_street_address_line2 string,
principal_investigator_4_city string,
principal_investigator_4_state string,
principal_investigator_4_zip_code string,
principal_investigator_4_country string,
principal_investigator_4_province string,
principal_investigator_4_postal_code string,
principal_investigator_4_primary_type string,
principal_investigator_4_specialty string,
principal_investigator_4_license_state_code1 string,
principal_investigator_4_license_state_code2 string,
principal_investigator_4_license_state_code3 string,
principal_investigator_4_license_state_code4 string,
principal_investigator_4_license_state_code5 string,
principal_investigator_5_profile_id string,
principal_investigator_5_first_name string,
principal_investigator_5_middle_name string,
principal_investigator_5_last_name string,
principal_investigator_5_name_suffix string,
principal_investigator_5_business_street_address_line1 string,
principal_investigator_5_business_street_address_line2 string,
principal_investigator_5_city string,
principal_investigator_5_state string,
principal_investigator_5_zip_code string,
principal_investigator_5_country string,
principal_investigator_5_province string,
principal_investigator_5_postal_code string,
principal_investigator_5_primary_type string,
principal_investigator_5_specialty string,
principal_investigator_5_license_state_code1 string,
principal_investigator_5_license_state_code2 string,
principal_investigator_5_license_state_code3 string,
principal_investigator_5_license_state_code4 string,
principal_investigator_5_license_state_code5 string,
submitting_applicable_manufacturer_or_applicable_gpo_name string,
applicable_manufacturer_or_applicable_gpo_making_payment_id string,
applicable_manufacturer_or_applicable_gpo_making_payment_name string,
applicable_manufacturer_or_applicable_gpo_making_payment_state string,
applicable_manufacturer_or_applicable_gpo_making_payment_country string,
related_product_indicator string,
covered_or_noncovered_indicator_1 string,
indicate_drug_or_biological_or_device_or_medical_supply_1 string,
product_category_or_therapeutic_area_1 string,
name_of_drug_or_biological_or_device_or_medical_supply_1 string,
associated_drug_or_biological_ndc_1 string,
covered_or_noncovered_indicator_2 string,
indicate_drug_or_biological_or_device_or_medical_supply_2 string,
product_category_or_therapeutic_area_2 string,
name_of_drug_or_biological_or_device_or_medical_supply_2 string,
associated_drug_or_biological_ndc_2 string,
covered_or_noncovered_indicator_3 string,
indicate_drug_or_biological_or_device_or_medical_supply_3 string,
product_category_or_therapeutic_area_3 string,
name_of_drug_or_biological_or_device_or_medical_supply_3 string,
associated_drug_or_biological_ndc_3 string,
covered_or_noncovered_indicator_4 string,
indicate_drug_or_biological_or_device_or_medical_supply_4 string,
product_category_or_therapeutic_area_4 string,
name_of_drug_or_biological_or_device_or_medical_supply_4 string,
associated_drug_or_biological_ndc_4 string,
covered_or_noncovered_indicator_5 string,
indicate_drug_or_biological_or_device_or_medical_supply_5 string,
product_category_or_therapeutic_area_5 string,
name_of_drug_or_biological_or_device_or_medical_supply_5 string,
associated_drug_or_biological_ndc_5 string,
total_amount_of_payment_usdollars decimal(10,2),
date_of_payment string,
form_of_payment_or_transfer_of_value string,
expenditure_category1 string,
expenditure_category2 string,
expenditure_category3 string,
expenditure_category4 string,
expenditure_category5 string,
expenditure_category6 string,
preclinical_research_indicator string,
delay_in_publication_indicator string,
name_of_study string,
dispute_status_for_publication string,
record_id string,
program_year string,
payment_publication_date string,
clinicaltrials_gov_identifier string,
research_information_link string,
context_of_research string
)
row format delimited
fields terminated by ','
stored as textfile
location '/user/frothkoetter/cms.db/researchpayments/'
;
select * from cms.researchpayments limit 3;

use cms;
drop table cms.generalpayments;
create external  table cms.generalpayments ( 
change_type string,
covered_recipient_type string,
teaching_hospital_ccn string,
teaching_hospital_id string,
teaching_hospital_name string,
physician_profile_id string,
physician_first_name string,
physician_middle_name string,
physician_last_name string,
physician_name_suffix string,
recipient_primary_business_street_address_line1 string,
recipient_primary_business_street_address_line2 string,
recipient_city string,
recipient_state string,
recipient_zip_code string,
recipient_country string,
recipient_province string,
recipient_postal_code string,
physician_primary_type string,
physician_specialty string,
physician_license_state_code1 string,
physician_license_state_code2 string,
physician_license_state_code3 string,
physician_license_state_code4 string,
physician_license_state_code5 string,
submitting_applicable_manufacturer_or_applicable_gpo_name string,
applicable_manufacturer_or_applicable_gpo_making_payment_id string,
applicable_manufacturer_or_applicable_gpo_making_payment_name string,
applicable_manufacturer_or_applicable_gpo_making_payment_state string,
applicable_manufacturer_or_applicable_gpo_making_payment_country string,
total_amount_of_payment_usdollars decimal ( 10,2),
date_of_payment string,
number_of_payments_included_in_total_amount int,
form_of_payment_or_transfer_of_value string,
nature_of_payment_or_transfer_of_value string,
city_of_travel string,
state_of_travel string,
country_of_travel string,
physician_ownership_indicator string,
third_party_payment_recipient_indicator string,
name_of_third_party_entity_receiving_payment_or_transfer_of_value string,
charity_indicator string,
third_party_equals_covered_recipient_indicator string,
contextual_information string,
delay_in_publication_indicator string,
record_id string,
dispute_status_for_publication string,
related_product_indicator string,
covered_or_noncovered_indicator_1 string,
indicate_drug_or_biological_or_device_or_medical_supply_1 string,
product_category_or_therapeutic_area_1 string,
name_of_drug_or_biological_or_device_or_medical_supply_1 string,
associated_drug_or_biological_ndc_1 string,
covered_or_noncovered_indicator_2 string,
indicate_drug_or_biological_or_device_or_medical_supply_2 string,
product_category_or_therapeutic_area_2 string,
name_of_drug_or_biological_or_device_or_medical_supply_2 string,
associated_drug_or_biological_ndc_2 string,
covered_or_noncovered_indicator_3 string,
indicate_drug_or_biological_or_device_or_medical_supply_3 string,
product_category_or_therapeutic_area_3 string,
name_of_drug_or_biological_or_device_or_medical_supply_3 string,
associated_drug_or_biological_ndc_3 string,
covered_or_noncovered_indicator_4 string,
indicate_drug_or_biological_or_device_or_medical_supply_4 string,
product_category_or_therapeutic_area_4 string,
name_of_drug_or_biological_or_device_or_medical_supply_4 string,
associated_drug_or_biological_ndc_4 string,
covered_or_noncovered_indicator_5 string,
indicate_drug_or_biological_or_device_or_medical_supply_5 string,
product_category_or_therapeutic_area_5 string,
name_of_drug_or_biological_or_device_or_medical_supply_5 string,
associated_drug_or_biological_ndc_5 string,
program_year string,
payment_publication_date string
)
row format delimited
fields terminated by ','
stored as textfile
location '/user/frothkoetter/cms.db/generalpayments/';

select * from generalpayments limit 3;


use cms;
drop table if exists cms.abrechnungen;
create table cms.abrechnungen (arzt DOUBLE PRECISION, fachgebiet DOUBLE PRECISION, 
						krankenhaus_oder_arzt DOUBLE PRECISION, medikament DOUBLE PRECISION, strittig INT,
						bundesland DOUBLE PRECISION, leistungsempfaenger_plz DOUBLE PRECISION, 
						auszahlung_monat DOUBLE PRECISION, auszahlung_euro DOUBLE PRECISION) 
STORED AS PARQUET 
LOCATION '/tmp/cmsml/'
TBLPROPERTIES ('transactional'='false', 'external.table.purge'='true')
;

INSERT OVERWRITE TABLE cms.abrechnungen select 
cast( abs(  hash(physician_profile_id))/9999999999999999999 as DOUBLE PRECISION)                arzt,
cast( abs( hash(physician_specialty))/9999999999999999999 as DOUBLE PRECISION)                  fachgebiet,
cast( abs( hash(covered_recipient_type))/9999999999999999999 as DOUBLE PRECISION)               krankenhaus_oder_arzt, 
cast( abs( hash(associated_drug_or_biological_ndc_1))/9999999999999999999 as DOUBLE PRECISION)  medikament,
 case  when dispute_status_for_publication ='"No"' then 0 else 1 end                             strittig,
cast( abs( hash(  recipient_state ))/9999999999999999999 as DOUBLE PRECISION)                    bundesland,
cast( abs( hash(  substr( recipient_zip_code, 2, 5 )))/9999999999999999999 as DOUBLE PRECISION) leistungsempfaenger_plz,
cast( abs( hash( substr(date_of_payment,3)))/9999999999999999999 as DOUBLE PRECISION)           auszahlung_monat,
cast (total_amount_of_payment_usdollars as float )                                              auszahlung_euro
from cms.generalpayments
limit 250000
;

select * from cms.abrechnungen limit 3;