Spark SQL "Limit"

hadoop apache-spark hive hortonworks-data-platform
David H picture David H · Feb 28, 2017 · Viewed 11.5k times · Source

Env : spark 1.6 using Hadoop. Hortonworks Data Platform 2.5

I have a table with 10 billion records and I would like to get 300 million records and move them to a temporary table.

sqlContext.sql("select ....from my_table limit 300000000").repartition(50)
.write.saveAsTable("temporary_table")

I saw that the Limit keyword would actually make spark use only one executor!!! This means moving 300 million records to one node and writing it back to Hadoop. How can I avoid this reduce but still get just 300 million records while having more than one executor. I would like all nodes to write into hadoop.

Can sampling help me with that? If so how?

Answer

Ashish Singh picture Ashish Singh · Feb 28, 2017

Sampling can be used in below ways :- 

select ....from my_table TABLESAMPLE(.3 PERCENT)

or

select ....from my_table TABLESAMPLE(30M ROWS)

Related questions

Save Spark dataframe as dynamic partitioned table in Hive

I have a sample application working to read from csv files into a dataframe. The dataframe can be stored to a Hive table in parquet format using the method df.saveAsTable(tablename,mode). The above code works fine, but I …

Read More
How to make shark/spark clear the cache?

when i run my shark queries, the memory gets hoarded in the main memory This is my top command result. Mem: 74237344k total, 70080492k used, 4156852k free, 399544k buffers Swap: 4194288k total, 480k used, 4193808k free, 65965904k cached this doesn't …

Read More
SparkSQL vs Hive on Spark - Difference and pros and cons?

SparkSQL CLI internally uses HiveQL and in case Hive on spark(HIVE-7292) , hive uses spark as backend engine. Can somebody throw some more light, how exactly these two scenarios are different and pros and cons of both approaches?

Read More