Re: Unable to load CSV

[Tushar Goel]

Thanks Peter for responding, Here are the required outputs:

CREATE TABLE `QUESTIONNAIRESUBMISSION_FACT_MIGRATION` (
`SESSIONKEY` varchar(64) NOT NULL,
`REFERENCEKEY` varchar(64) NOT NULL,
`REFERENCETYPE` varchar(64) NOT NULL,
`USERKEY` varchar(64) DEFAULT NULL,
`REPORTINGUSERKEY` varchar(128) DEFAULT NULL,
`QUESTIONNAIREKEY` varchar(64) NOT NULL,
`QUESTIONNAIRETITLE` varchar(1024) DEFAULT NULL,
`QUESTIONNAIREVERSION` varchar(64) DEFAULT NULL,
`QUESTIONNAIRETYPEKEY` varchar(32) DEFAULT NULL,
`QUESTIONNAIRESUBTYPE` varchar(32) DEFAULT NULL,
`EMAIL` varchar(128) DEFAULT NULL,
`PRODUCTKEY` varchar(16) DEFAULT NULL,
`PLATFORMKEY` varchar(64) DEFAULT NULL,
`NUMQUESTIONASKED` int(11) DEFAULT NULL,
`NUMQUESTIONANSWERED` int(11) DEFAULT NULL,
`NUMCORRECTANSWER` int(11) DEFAULT NULL,
`DBTIMESTAMP` bigint(20) DEFAULT NULL,
`TRANSACTIONTIME` bigint(20) DEFAULT NULL,
`REPORTINGUSRKEY` varchar(128) DEFAULT NULL,
`ANSWERKEY` varchar(64) DEFAULT NULL,
`ANSWERSORTORDER` int(11) DEFAULT NULL,
`ANSWERTEXT` varchar(1024) DEFAULT NULL,
`ANSWERTIME` varchar(64) DEFAULT NULL,
`ISCORRECT` varchar(64) DEFAULT NULL,
`QUESTIONTYPE` varchar(64) DEFAULT NULL,
`QUESTION ` varchar(128) DEFAULT NULL,
`QUESTIONKEY ` varchar(128) DEFAULT NULL,
`QUESTIONSORTORDER` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Stores questionnairesubmission_fact details'


===========


Same CSV input:


SESSIONKEY,REFERENCEKEY,REFERENCETYPE,USERKEY,REPORTINGUSERKEY,QUESTIONNAIREKEY,QUESTIONNAIRETITLE,QUESTIONNAIREVERSION,QUESTIONNAIRETYPEKEY,QUESTIONNAIRESUBTYPE,EMAIL,PRODUCTKEY,PLATFORMKEY,NUMQUESTIONASKED,NUMQUESTIONANSWERED,NUMCORRECTANSWER,DBTIMESTAMP,TRANSACTIONTIME,REPORTINGUSRKEY,ANSWERKEY,ANSWERSORTORDER,ANSWERTEXT,ANSWERTIME,ISCORRECT,QUESTIONTYPE,QUESTION,QUESTIONKEY,QUESTIONSORTORDER
-1,322d8ac7-4e00-4304,CONFERENCE,74698900000,webdriver.user,1879803950,\\N,\\N,REGISTRATION,PREDEFINED,[e-mail],NY,\\N,1,0,\\N,1.64067E+12,1.64067E+12,\\N,\\N,\\N,\\N,1.64067E+12,\\N,SHORT_ANSWER,city,3053931,\\N
-1,939d927a-cc8a-44b6-9189-088f065ac,CONFERENCE,3830881000,webdriver.user,8.4914E+18,\\N,\\N,REGISTRATION,CUSTOM,[e-mail],NY,\\N,2,2,\\N,1.64067E+12,1.64067E+12,\\N,\\N,\\N,black,1.64067E+12,\\N,SHORT_ANSWER,Favourite Color Name?,8377249,\\N
-1,939d927a-cc8a-44b6-9189-0065ac,CONFERENCE,3458122800,webdriver.user,8.4914E+18,\\N,\\N,REGISTRATION,CUSTOM,[e-mail],NY,\\N,2,2,\\N,1.64067E+12,1.64067E+12,\\N,\\N,\\N,cat,1.64067E+12,\\N,SHORT_ANSWER,Favourite Pet Name?,8377248,\\N
-1,939d927a-9189-088f678065ac,CONFERENCE,19448581600,webdriver.user,8.4914E+18,\\N,\\N,REGISTRATION,CUSTOM,[e-mail],NY,\\N,2,2,\\N,1.64067E+12,1.64067E+12,\\N,\\N,\\N,white,1.64067E+12,\\N,SHORT_ANSWER,Favourite Color Name?,8377249,\\N
-1,939d927a-cc8a-44b6-9189,CONFERENCE,1944857700,webdriver.user,8.4914E+18,\\N,\\N,REGISTRATION,CUSTOM,[e-mail],NY,\\N,2,2,\\N,1.64067E+12,1.64067E+12,\\N,\\N,\\N,dog,1.64067E+12,\\N,SHORT_ANSWER,Favourite Pet Name?,8377248,\\N


=======

SQL load query

LOAD DATA FROM S3 FILE 's3://xx/data_0_0_0.csv'
IGNORE
INTO TABLE test.QUESTIONNAIRESUBMISSION_FACT_MIGRATION
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES
(SESSIONKEY,REFERENCEKEY,REFERENCETYPE,USERKEY,REPORTINGUSERKEY,QUESTIONNAIREKEY,QUESTIONNAIRETITLE,QUESTIONNAIREVERSION,QUESTIONNAIRETYPEKEY,QUESTIONNAIRESUBTYPE,EMAIL,PRODUCTKEY,PLATFORMKEY,NUMQUESTIONASKED,NUMQUESTIONANSWERED,NUMCORRECTANSWER,DBTIMESTAMP,TRANSACTIONTIME,REPORTINGUSRKEY,ANSWERKEY,ANSWERSORTORDER,ANSWERTEXT,ANSWERTIME,ISCORRECT,QUESTIONTYPE,QUESTION,QUESTIONKEY,QUESTIONSORTORDER);