<commit_before>---
title: Collecting data in SparkSQL
type: default
toc: false
summary: |
    <p>If you're working with <a href="http://spark.apache.org/sql/">SQL in Apache Spark</a> in Scala, read this page.</p>
    <p><b>Author:</b> <a href="http://github.com/jpivarski"><NAME></a></p>
---

## Preliminaries

This tutorial should be regarded as a sequel to [Collecting data in Spark](../scala-spark), so I'll assume that you already have Spark installed. As before, if your Spark cluster is Spark 2.0 or later, start it with

```bash
spark-shell --packages "io.github.histogrammar:histogrammar_2.12:1.0.11,io.github.histogrammar:histogrammar-sparksql_2.12:1.0.11"
```

Otherwise, start it with

```bash
spark-shell --packages "io.github.histogrammar:histogrammar_2.12:1.0.11,io.github.histogrammar:histogrammar-sparksql_2.12:1.0.11"
```

Next, turn the [CMS public data](../scala-cmsdata) into a Spark RDD:

```scala
import org.dianahep.histogrammar.tutorial.cmsdata
val events = cmsdata.EventIterator()
val rdd = sc.parallelize(events.toList)
```

As before, you'll need to wait 10-20 seconds for the dataset to download.

## Why SparkSQL?

If you're reading these in order, you just finished a whole tutorial on plotting data in Spark. Why SparkSQL?

Plain Spark provides an "object oriented view" of your data. Events are complex, structured objects. SQL is a widely used abstraction that views data as flat tables: the columns are named and have simple types (e.g. boolean, number, string), and the rows form an unordered, unstructured collection. Business analysts call this a "relational view," and physicists call it an "ntuple."

These are soft terms, not absolutes: you can turn the object-oriented RDD into a relational SQL table. The lines below perform the conversion and print the DataFrame's schema (names and types of the columns).

```scala
val df = rdd.toDF
df.printSchema
```<commit_msg>Fix jar versions for scala-sparksql tutorial<commit_after>---
title: Collecting data in SparkSQL
type: default
toc: false
summary: |
    <p>If you're working with <a href="http://spark.apache.org/sql/">SQL in Apache Spark</a> in Scala, read this page.</p>
    <p><b>Author:</b> <a href="http://github.com/jpivarski"><NAME></a></p>
---

## Preliminaries

This tutorial should be regarded as a sequel to [Collecting data in Spark](../scala-spark), so I'll assume that you already have Spark installed. As before, if your Spark cluster is Spark 3.0 or later, start it with

```bash
spark-shell --packages "io.github.histogrammar:histogrammar_2.12:1.0.11,io.github.histogrammar:histogrammar-sparksql_2.12:1.0.11"
```

Otherwise, start it with

```bash
spark-shell --packages "io.github.histogrammar:histogrammar_2.11:1.0.11,io.github.histogrammar:histogrammar-sparksql_2.11:1.0.11"
```

Next, turn the [CMS public data](../scala-cmsdata) into a Spark RDD:

```scala
import org.dianahep.histogrammar.tutorial.cmsdata
val events = cmsdata.EventIterator()
val rdd = sc.parallelize(events.toList)
```

As before, you'll need to wait 10-20 seconds for the dataset to download.

## Why SparkSQL?

If you're reading these in order, you just finished a whole tutorial on plotting data in Spark. Why SparkSQL?

Plain Spark provides an "object oriented view" of your data. Events are complex, structured objects. SQL is a widely used abstraction that views data as flat tables: the columns are named and have simple types (e.g. boolean, number, string), and the rows form an unordered, unstructured collection. Business analysts call this a "relational view," and physicists call it an "ntuple."

These are soft terms, not absolutes: you can turn the object-oriented RDD into a relational SQL table. The lines below perform the conversion and print the DataFrame's schema (names and types of the columns).

```scala
val df = rdd.toDF
df.printSchema
```
{'fraction_non_alphanumeric': 0.11053315994798439, 'fraction_numerical': 0.017685305591677504, 'mean_word_length': 4.58200290275762, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5375614', 'n_tokens_mistral': 1281, 'n_tokens_neox': 1154, 'n_words': 463}