Executing OLAP functions with Spark SQL

Question

I am working with spark version 1.6. I want to execute OLAP functions include CUBE, ROLLUP, GROUPING SETS through sql queries on Spark. I understand that the cube and rollup functions are available on the dataframe api but how can I execute them through SQL queries?

Do I need to use HiveContext for this? Do I need to have Hive setup for that? How do I persist the dataframe as a table that could be accessed by the sql query executed using HiveContext?

It would be great to have a look at an example code. Thanks.

You can perform queries using the sqlContext only See here : http://spark.apache.org/docs/latest/sql-programming-guide.html#running-sql-queries-programmatically — Shivansh, Dec 14 '16 at 09:42
Those would be simple queries not including the functions I mentioned - CUBE, ROLLUP, GROUPING SETS, right? — Andy Dufresne, Dec 14 '16 at 11:25
You can implement it as it has already been implemented : https://issues.apache.org/jira/browse/SPARK-6356 — Shivansh, Dec 14 '16 at 11:28
Just read the whole data in the DF and then register it as a temp table then use spark.sql("<>"").show to print the output ! — Shivansh, Dec 14 '16 at 11:29
That doesn't work @Shivansh Srivastava. I have tried it already. I get an error on zeppelin stating the rollup function is undefined. — Andy Dufresne, Dec 15 '16 at 05:08

Antoine CHAMBILLE · Answer 1 · 2018-04-30T12:02:06.423

At ActiveViam we also needed to do interactive OLAP queries on Apache Spark. So we made an extension called Sparkube that exposes a Spark dataset as a multidimensional cube, without moving the data.

Once your dataset is exposed that way, you get access to all the OLAP MDX functions directly on top of Spark, including DRILLDOWN, DRILLUP

Here is for instance how you mount the content of a CSV file in memory and expose it as a multidimensional cube:

// Load a file into a Spark dataset.
// Here we load a CSV file, get the column names from
// the CSV headers, and use Spark automatic type inference.
var ds = spark.read
  .format("csv")
  .option("header","true")
  .option("inferSchema","true")
  .load("path/to/file.csv")

// Import the sparkube library (the sparkube jar must be in the classpath)
import com.activeviam.sparkube._

// Expose the dataset as a multidimensional cube.
// You can start visualizing the cube right away at http://localhost:9090/ui
// or connect from Excel with the XMLA protocol at http://localhost:9090/xmla
new Sparkube().fromDataset(ds)
  .withName("My first cube")
  .expose()

Executing OLAP functions with Spark SQL

1 Answers1