The SELECT COUNT query in Amazon Athena returns only one record even though the input JSON file has multiple records

Issue

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?

Resolution

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." 

SELECT

Querying Data in Amazon Athena Tables

CREATE TABLE 

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