title: Why my count,Distinct and count of Distinct count is very slow in huge cluster in spark
body: <p>I have a very huge cluster 20  m4.Xlarge instances .
  I have file size of 20GB and count of records in the file is 193944092.</p>
  
  <p>From this file I need three info.
  1. Total no of records 
  2. Total no of distinct records 
  3. Total no of distinct records based on one column (<code>FundamentalSeriesId</code>).</p>
  
  <p>When I run below code it takes very long time .For counting total no of records it has taken 7 minutes .</p>
  
  <p>But for Total no of distinct and Total no of distinct records of FundamentalSeriesId column it has taken very long time I mean I have cancelled the query because it was taking long time.</p>
  
  <p>If any one can improve my code that would be great .Can I use cache or something else to get the info faster ?</p>
  
  <p>This is what I am doing </p>
  
  <pre><code>    val rdd = sc.textFile("s3://kishore-my-bucket-trf/Fundamental.FundamentalAnalytic.FundamentalAnalytic.SelfSourcedPublic.2011.1.2018-02-18-1340.Full.txt.gz")
  println("Total count="+rdd.count())
  
  val header = rdd.filter(_.contains("FundamentalSeriesId")).map(line =&gt; line.split("\\|\\^\\|")).first()
  val schema = StructType(header.map(cols =&gt; StructField(cols.replace(".", "_"), StringType)).toSeq)
  val data = sqlContext.createDataFrame(rdd.filter(!_.contains("FundamentalSeriesId")).map(line =&gt; Row.fromSeq(line.split("\\|\\^\\|").toSeq)), schema)
  
  println("distinct count="+data.distinct.count())
  
  val data1=data.select($"FundamentalSeriesId")
  println("count of distinct FundamentalSeriesId column="+data1.distinct.count())
  </code></pre>
  
  <p>My sample records are like this ..</p>
  
  <pre><code>FundamentalSeriesId|^|FundamentalSeriesId.objectTypeId|^|FundamentalSeriesId.objectType_1|^|financialPeriodEndDate|^|financialPeriodType|^|lineItemId|^|analyticItemInstanceKey_1|^|AnalyticValue_1|^|AnalyticConceptCode_1|^|AnalyticValue.currencyId_1|^|AnalyticIsEstimated_1|^|AnalyticAuditabilityEquation_1|^|FinancialPeriodTypeId_1|^|AnalyticConceptId_1|^|sYearToDate|^|IsAnnual_1|^|TaxonomyId_1|^|InstrumentId_1|^|AuditID_1|^|PhysicalMeasureId_1|^|FFAction_1
  </code></pre>