When I execute SELECT COUNT(*) FROM TABLE, the output is "1," but the input file has multiple JSON records. I used org.openx.data.jsonserde.JsonSerDe to create the table. Why is the query returning the wrong value?
When records are not separated by a newline character (\n), SELECT COUNT(*) FROM TABLE returns "1."
For example:
{"ID":"1","MYTIMESTAMP":"2018-04-25 17:50:46.267","Name":"John"}{"ID":"2","MYTIMESTAMP":"2018-04-26 17:50:46.267","Name":"Joel"}{"ID":"3","MYTIMESTAMP":"2018-04-26 17:50:46.267","Name":"Patrick"}
To correct this problem, be sure that the records are delimited by \n, as shown in the following example:
{"ID":"1","MYTIMESTAMP":"2018-04-25 17:50:46.267","Name":"John"} {"ID":"2","MYTIMESTAMP":"2018-04-26 17:50:46.267","Name":"Joel"} {"ID":"3","MYTIMESTAMP":"2018-04-26 17:50:46.267","Name":"Patrick"}
Now, SELECT COUNT(*) FROM TABLE returns "3."
Did this page help you? Yes | No
Back to the AWS Support Knowledge Center
Need help? Visit the AWS Support Center
Published: 2018-08-24