drop table if exists CLARITY_RT.PATIENT_NRICHED; create table if not exists CLARITY_RT.PATIENT_NRICHED ( key string ,pat_id string ,pat_name string ,add_line_1 string ,add_line_2 string ,city string ,state_c string ,state_c_name string ,state_c_abbr string ,state_c_title string ,county_c string ,county_c_name string ,county_c_abbr string ,county_c_title string ,country_c string ,country_c_name string ,country_c_abbr string ,country_c_title string ,zip string ,home_phone string ,work_phone string ,email_address string ,restricted_yn string ,pat_status string ,birth_date string ,sex string ,ethnic_group_c bigint ,ethnic_group_c_name string ,ethnic_group_c_abbr string ,ethnic_group_c_title string ,marital_status_c bigint ,marital_status_c_name string ,marital_status_c_abbr string ,marital_status_c_title string ,religion_c bigint ,religion_c_name string ,religion_c_abbr string ,religion_c_title string ,language_c string ,language_c_name string ,language_c_abbr string ,language_c_title string ,ssn string ,reg_date string ,reg_status_c string ,reg_status_c_name string ,reg_status_c_abbr string ,reg_status_c_title string ,epiccare_pat_yn string ,medicare_num string ,medicaid_num string ,power_of_attrny_yn string ,power_of_attrny_dt string ,adv_directive_yn string ,adv_directive_date string ,def_fin_class_c string ,def_fin_class_c_name string ,def_fin_class_c_abbr string ,def_fin_class_c_title string ,fin_status_c bigint ,fin_status_c_name string ,fin_status_c_abbr string ,fin_status_c_title string ,cur_pcp_prov_id string ,claim_alert_c bigint ,claim_alert_c_name string ,claim_alert_c_abbr string ,claim_alert_c_title string ,cur_prim_loc_id bigint ,legal_status_c bigint ,legal_status_c_name string ,legal_status_c_abbr string ,legal_status_c_title string ,veteran_status_c string ,veteran_status_c_name string ,veteran_status_c_abbr string ,veteran_status_c_title string ,mother_pat_id string ,father_pat_id string ,birth_loc_id bigint ,birth_status_c bigint ,birth_status_c_name string ,birth_status_c_abbr string ,birth_status_c_title string ,birth_wrist_band string ,ped_birth_len string ,ped_birth_wt string ,ped_apgar_one string ,ped_apgar_two string ,ped_apgar_ten string ,ped_comment string ,ped_mult_birth_ord bigint ,ped_mult_birth_tot bigint ,epic_pat_id string ,rec_create_date string ,create_user_id string ,prim_cvg_id bigint ,prim_epm_id bigint ,prim_epp_id bigint ,prim_fc string ,perm_chart_loc_c bigint ,perm_chart_loc_c_name string ,perm_chart_loc_c_abbr string ,perm_chart_loc_c_title string ,cur_chart_loc_c bigint ,cur_chart_loc_c_name string ,cur_chart_loc_c_abbr string ,cur_chart_loc_c_title string ,pat_mrn_id string ,death_date string ,update_date string ,eob_flag_yn string ,rec_create_pat_id string ,organ_donor_yn string ,tmp_addr_line_1 string ,tmp_addr_line_2 string ,tmp_city string ,tmp_state_c string ,tmp_state_c_name string ,tmp_state_c_abbr string ,tmp_state_c_title string ,tmp_country_c string ,tmp_country_c_name string ,tmp_country_c_abbr string ,tmp_country_c_title string ,tmp_zip string ,tmp_home_phone string ,tmp_county_c string ,tmp_county_c_name string ,tmp_county_c_abbr string ,tmp_county_c_title string ,tmp_addr_start_dt string ,tmp_addr_end_dt string ,tmp_care_of_person string ,is_mail_blocked_yn string ,is_phone_remndr_yn string ,case_spvsr_user_id string ,pat_last_name string ,pat_first_name string ,pat_middle_name string ,pat_title_c bigint ,pat_title_c_name string ,pat_title_c_abbr string ,pat_title_c_title string ,pat_name_suffix_c bigint ,pat_name_suffix_c_name string ,pat_name_suffix_c_abbr string ,pat_name_suffix_c_title string ,special_status_c bigint ,special_status_c_name string ,special_status_c_abbr string ,special_status_c_title string ,lang_care_c string ,lang_care_c_name string ,lang_care_c_abbr string ,lang_care_c_title string ,lang_writ_c string ,lang_writ_c_name string ,lang_writ_c_abbr string ,lang_writ_c_title string ,proxy_pat_yn string ,proxy_name string ,proxy_phone string ,proxy_pack_yn string ,employer_id string ,empy_status_c bigint ,empy_status_c_name string ,empy_status_c_abbr string ,empy_status_c_title string ,cm_phy_owner_id string ,cm_log_owner_id string ,alrgy_upd_date string ,alrgy_upd_user_id string ,guardian_name string ,pref_clin_zip string ,pref_pcp_sex_c string ,pref_pcp_sex_c_name string ,pref_pcp_sex_c_abbr string ,pref_pcp_sex_c_title string ,pref_pcp_spec_c string ,pref_pcp_spec_c_name string ,pref_pcp_spec_c_abbr string ,pref_pcp_spec_c_title string ,pref_pcp_lang_c string ,pref_pcp_lang_c_name string ,pref_pcp_lang_c_abbr string ,pref_pcp_lang_c_title string ,country_of_orig_c string ,country_of_orig_c_name string ,country_of_orig_c_abbr string ,country_of_orig_c_title string ,comm_pref_ltr_c bigint ,comm_pref_ltr_c_name string ,comm_pref_ltr_c_abbr string ,comm_pref_ltr_c_title string ,ped_birth_head_cir string ,ped_dischrg_wt string ,ped_cesarean_yn bigint ,ped_gest_age string ,ped_nour_meth_c bigint ,ped_nour_meth_c_name string ,ped_nour_meth_c_abbr string ,ped_nour_meth_c_title string ,ped_delivr_meth_c string ,ped_delivr_meth_c_name string ,ped_delivr_meth_c_abbr string ,ped_delivr_meth_c_title string ) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ('hbase.columns.mapping' = ':key ,v:pat_id ,v:pat_name ,v:add_line_1 ,v:add_line_2 ,v:city ,v:state_c ,v:state_c_name ,v:state_c_abbr ,v:state_c_title ,v:county_c ,v:county_c_name ,v:county_c_abbr ,v:county_c_title ,v:country_c ,v:country_c_name ,v:country_c_abbr ,v:country_c_title ,v:zip ,v:home_phone ,v:work_phone ,v:email_address ,v:restricted_yn ,v:pat_status ,v:birth_date ,v:sex ,v:ethnic_group_c ,v:ethnic_group_c_name ,v:ethnic_group_c_abbr ,v:ethnic_group_c_title ,v:marital_status_c ,v:marital_status_c_name ,v:marital_status_c_abbr ,v:marital_status_c_title ,v:religion_c ,v:religion_c_name ,v:religion_c_abbr ,v:religion_c_title ,v:language_c ,v:language_c_name ,v:language_c_abbr ,v:language_c_title ,v:ssn ,v:reg_date ,v:reg_status_c ,v:reg_status_c_name ,v:reg_status_c_abbr ,v:reg_status_c_title ,v:epiccare_pat_yn ,v:medicare_num ,v:medicaid_num ,v:power_of_attrny_yn ,v:power_of_attrny_dt ,v:adv_directive_yn ,v:adv_directive_date ,v:def_fin_class_c ,v:def_fin_class_c_name ,v:def_fin_class_c_abbr ,v:def_fin_class_c_title ,v:fin_status_c ,v:fin_status_c_name ,v:fin_status_c_abbr ,v:fin_status_c_title ,v:cur_pcp_prov_id ,v:claim_alert_c ,v:claim_alert_c_name ,v:claim_alert_c_abbr ,v:claim_alert_c_title ,v:cur_prim_loc_id ,v:legal_status_c ,v:legal_status_c_name ,v:legal_status_c_abbr ,v:legal_status_c_title ,v:veteran_status_c ,v:veteran_status_c_name ,v:veteran_status_c_abbr ,v:veteran_status_c_title ,v:mother_pat_id ,v:father_pat_id ,v:birth_loc_id ,v:birth_status_c ,v:birth_status_c_name ,v:birth_status_c_abbr ,v:birth_status_c_title ,v:birth_wrist_band ,v:ped_birth_len ,v:ped_birth_wt ,v:ped_apgar_one ,v:ped_apgar_two ,v:ped_apgar_ten ,v:ped_comment ,v:ped_mult_birth_ord ,v:ped_mult_birth_tot ,v:epic_pat_id ,v:rec_create_date ,v:create_user_id ,v:prim_cvg_id ,v:prim_epm_id ,v:prim_epp_id ,v:prim_fc ,v:perm_chart_loc_c ,v:perm_chart_loc_c_name ,v:perm_chart_loc_c_abbr ,v:perm_chart_loc_c_title ,v:cur_chart_loc_c ,v:cur_chart_loc_c_name ,v:cur_chart_loc_c_abbr ,v:cur_chart_loc_c_title ,v:pat_mrn_id ,v:death_date ,v:update_date ,v:eob_flag_yn ,v:rec_create_pat_id ,v:organ_donor_yn ,v:tmp_addr_line_1 ,v:tmp_addr_line_2 ,v:tmp_city ,v:tmp_state_c ,v:tmp_state_c_name ,v:tmp_state_c_abbr ,v:tmp_state_c_title ,v:tmp_country_c ,v:tmp_country_c_name ,v:tmp_country_c_abbr ,v:tmp_country_c_title ,v:tmp_zip ,v:tmp_home_phone ,v:tmp_county_c ,v:tmp_county_c_name ,v:tmp_county_c_abbr ,v:tmp_county_c_title ,v:tmp_addr_start_dt ,v:tmp_addr_end_dt ,v:tmp_care_of_person ,v:is_mail_blocked_yn ,v:is_phone_remndr_yn ,v:case_spvsr_user_id ,v:pat_last_name ,v:pat_first_name ,v:pat_middle_name ,v:pat_title_c ,v:pat_title_c_name ,v:pat_title_c_abbr ,v:pat_title_c_title ,v:pat_name_suffix_c ,v:pat_name_suffix_c_name ,v:pat_name_suffix_c_abbr ,v:pat_name_suffix_c_title ,v:special_status_c ,v:special_status_c_name ,v:special_status_c_abbr ,v:special_status_c_title ,v:lang_care_c ,v:lang_care_c_name ,v:lang_care_c_abbr ,v:lang_care_c_title ,v:lang_writ_c ,v:lang_writ_c_name ,v:lang_writ_c_abbr ,v:lang_writ_c_title ,v:proxy_pat_yn ,v:proxy_name ,v:proxy_phone ,v:proxy_pack_yn ,v:employer_id ,v:empy_status_c ,v:empy_status_c_name ,v:empy_status_c_abbr ,v:empy_status_c_title ,v:cm_phy_owner_id ,v:cm_log_owner_id ,v:alrgy_upd_date ,v:alrgy_upd_user_id ,v:guardian_name ,v:pref_clin_zip ,v:pref_pcp_sex_c ,v:pref_pcp_sex_c_name ,v:pref_pcp_sex_c_abbr ,v:pref_pcp_sex_c_title ,v:pref_pcp_spec_c ,v:pref_pcp_spec_c_name ,v:pref_pcp_spec_c_abbr ,v:pref_pcp_spec_c_title ,v:pref_pcp_lang_c ,v:pref_pcp_lang_c_name ,v:pref_pcp_lang_c_abbr ,v:pref_pcp_lang_c_title ,v:country_of_orig_c ,v:country_of_orig_c_name ,v:country_of_orig_c_abbr ,v:country_of_orig_c_title ,v:comm_pref_ltr_c ,v:comm_pref_ltr_c_name ,v:comm_pref_ltr_c_abbr ,v:comm_pref_ltr_c_title ,v:ped_birth_head_cir ,v:ped_dischrg_wt ,v:ped_cesarean_yn ,v:ped_gest_age ,v:ped_nour_meth_c ,v:ped_nour_meth_c_name ,v:ped_nour_meth_c_abbr ,v:ped_nour_meth_c_title ,v:ped_delivr_meth_c ,v:ped_delivr_meth_c_name ,v:ped_delivr_meth_c_abbr ,v:ped_delivr_meth_c_title ') tblproperties ('hbase.table.name' ='CLARITY_RT.PATIENT_NRICHED');