Have you test this on a smaller set to verify that the query is correct? On Mon, Jun 22, 2015 at 2:59 PM, ayan guha <guha.a...@gmail.com> wrote:
> You may also want to change count(*) to specific column. > On 23 Jun 2015 01:29, "James Aley" <james.a...@swiftkey.com> wrote: > >> Hello, >> >> A colleague of mine ran the following Spark SQL query: >> >> select >> count(*) as uses, >> count (distinct cast(id as string)) as users >> from usage_events >> where >> from_unixtime(cast(timestamp_millis/1000 as bigint)) >> between '2015-06-09' and '2015-06-16' >> >> The table contains billions of rows, but totals only 64GB of data across >> ~30 separate files, which are stored as Parquet with LZO compression in S3. >> >> From the referenced columns: >> >> * id is Binary, which we cast to a String so that we can DISTINCT by it. >> (I was already told this will improve in a later release, in a separate >> thread.) >> * timestamp_millis is a long, containing a unix timestamp with >> millisecond resolution >> >> This took nearly 2 hours to run on a 5 node cluster of r3.xlarge EC2 >> instances, using 20 executors, each with 4GB memory. I can see from >> monitoring tools that the CPU usage is at 100% on all nodes, but incoming >> network seems a bit low at 2.5MB/s, suggesting to me that this is CPU-bound. >> >> Does that seem slow? Can anyone offer any ideas by glancing at the query >> as to why this might be slow? We'll profile it meanwhile and post back if >> we find anything ourselves. >> >> A side issue - I've found that this query, and others, sometimes >> completes but doesn't return any results. There appears to be no error that >> I can see in the logs, and Spark reports the job as successful, but the >> connected JDBC client (SQLWorkbenchJ in this case), just sits there forever >> waiting. I did a quick Google and couldn't find anyone else having similar >> issues. >> >> >> Many thanks, >> >> James. >> >
