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:
Charge the SQL Context:
val sqlContext = new org.apache.spark.sql.SQLContext(sc)Read the parquet File:
val ventas=sqlContext.read.parquet("hdfs://localhost:9000/sistgestion/sql/ventas4")Register a temporal table:
ventas.registerTempTable("ventas")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) }
