I am migrating from Impala to SparkSQL, using the following code to read a table:

my_data = sqlContext.read.parquet('hdfs://my_hdfs_path/my_db.db/my_table') 

How do I invoke SparkSQL above, so it can return something like:

'select col_A, col_B from my_table' 

4 Answers

After creating a Dataframe from parquet file, you have to register it as a temp table to run sql queries on it.

val sqlContext = new org.apache.spark.sql.SQLContext(sc) val df = sqlContext.read.parquet("src/main/resources/peopleTwo.parquet") df.printSchema // after registering as a table you will be able to run sql queries df.registerTempTable("people") sqlContext.sql("select * from people").collect.foreach(println) 

With plain SQL

JSON, ORC, Parquet, and CSV files can be queried without creating the table on Spark DataFrame.

//This Spark 2.x code you can do the same on sqlContext as well val spark: SparkSession = SparkSession.builder.master("set_the_master").getOrCreate spark.sql("select col_A, col_B from parquet.`hdfs://my_hdfs_path/my_db.db/my_table`") .show() 

Suppose that you have the parquet file ventas4 in HDFS:

hdfs://localhost:9000/sistgestion/sql/ventas4

In this case, the steps are:

1. Charge the SQL Context:

   val sqlContext = new org.apache.spark.sql.SQLContext(sc) 

2. Read the parquet File:

   val ventas=sqlContext.read.parquet("hdfs://localhost:9000/sistgestion/sql/ventas4") 

3. Register a temporal table:

   ventas.registerTempTable("ventas") 

4. Execute the query (in this line you can use toJSON to pass a JSON format or you can use collect()):

   sqlContext.sql("select * from ventas").toJSON.foreach(println(_)) sqlContext.sql("select * from ventas").collect().foreach(println(_)) 

Use the following code in intellij:

def groupPlaylistIds(): Unit ={ Logger.getLogger("org").setLevel(Level.ERROR) val spark = SparkSession.builder.appName("FollowCount") .master("local[*]") .getOrCreate() val sc = spark.sqlContext val d = sc.read.format("parquet").load("/Users/CCC/Downloads/pq/file1.parquet") d.printSchema() val d1 = d.select("col1").filter(x => x!='-') val d2 = d1.filter(col("col1").startsWith("searchcriteria")); d2.groupBy("col1").count().sort(col("count").desc).show(100, false) }