DROP TABLE IF EXISTS ClaimsImport;

CREATE EXTERNAL TABLE ClaimsImport
(
ClaimID varchar(50),
ClaimLineID int,
MemberID varchar(50),
ProviderID varchar(50),
Dos date,
DosTo date,
Paid int,
Icd1 varchar(9),
Icd1Type int,
Icd2 varchar(9),
Icd2Type int,
Icd3 varchar(9),
Icd3Type int,
Icd4 varchar(9),
Icd4Type int,
Icd5 varchar(9),
Icd5Type int,
Icd6 varchar(9),
Icd6Type int,
Icd7 varchar(9),
Icd7Type int,
Icd8 varchar(9),
Icd8Type int,
Icd9 varchar(9),
Icd9Type int,
Icd10 varchar(9),
Icd10Type int,
Icd11 varchar(9),
Icd11Type int,
Icd12 varchar(9),
Icd12Type int,
Icd13 varchar(9),
Icd13Type int,
Icd14 varchar(9),
Icd14Type int,
Icd15 varchar(9),
Icd15Type int,
Icd16 varchar(9),
Icd16Type int,
Icd17 varchar(9),
Icd17Type int,
Icd18 varchar(9),
Icd18Type int,
Icd19 varchar(9),
Icd19Type int,
Icd20 varchar(9),
Icd20Type int,
ProcedureCode varchar(20),
ProcedureMod1 char(2),
ProcedureMod2 char(2),
ProcedureMod3 char(2),
ProcedureMod4 char(2),
TypeOfBill varchar(4),
PlaceOfService char(2),
UbRevCode varchar(4),
DRG char(3),
Quantity int,
CvxCode char(3),
Ambulance int,
Anesthesiologist int,
ClinicalPharmacist int,
Dentist int,
DmeSupplier int,
EyeCare int,
Facility int,
Hospital int,
MentalHealth int,
Nephrologist int,
NursePractitioner int,
Obgyn int,
PhysicalTherapy int,
PhysicianAssistant int,
PrescribingPrivileges int,
PrimaryCare int,
RegisteredNurse int,
SkilledNursingFacility int,
Surgeon int,
Cardiologist int,
Endocrinologist int,
Gastroenterologist int,
ProviderTaxonomy int,
Supplemental char(1),
DataSourceName varchar(255)
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' ESCAPED BY '"'
LOCATION 'hdfs://10.0.50.1/user/spark/warehouse/claimsimport'
;