---
title: "Data Exploitation Notebook with Spark"
canonical: "https://onesaitplatform.refined.site/space/DOCT/2220806710/Data%20Exploitation%20Notebook%20with%20Spark"
format: markdown
---
In this Notebook example, we will load a file containing data from Australian taxes, then we will convert it to a RDD Spark and lastly we will view it with Spark SQL.

To do this:

1. First you need to have the role ANALYTICS. This role allows you to access different tools, such as Notebooks and DataFlow. By default, only the Administrator user can give this role. Once you are logged in the platform with this role, you will see a menu option ANALYTICS TOOLS including a set of tools.
2. Go to ANALYTICS TOOLS>My Notebooks. From here, click on
  and give your Notebook a name. Once created, it will take you to a screen where you can write your Notebook.
3. You need the file. In this case, you will be using a DataSet from the Australian Government Open Data Portal  
[https://data.gov.au/dataset/](https://data.gov.au/dataset/)  
This will be your DataSet: [https://data.gov.au/dataset/health-expenditure-in-australia](https://data.gov.au/dataset/health-expenditure-in-australia)
4. Using the Shell interpreter, bring the file to the /tmp/ folder and remove empty fields:
  
  `%sh`
  `#remove existing copies of dataset`  
`rm /tmp/australian-expenses.csv`
  `#fetch the dataset`  
`wget https://data.gov.au/dataset/f84b9baf-c1c1-437c-8c1e-654b2829848c/resource/88399d53-d55c-466c-8f4a-6cb965d24d6d/download/healthexpenditurebyareaandsource.csv -O /tmp/australian-expenses.csv`
  `#remove header`  
`sed -i '1d' /tmp/australian-expenses.csv`  
`#remove empty fields`  
`sed -i "s/,,,,,//g" /tmp/australian-expenses.csv`  
`sed -i '/^\s*$/d' /tmp/australian-expenses.csv`
5. After this, you can create an RDD Spark and start working on the RDD:
6. To work with Spark SQL, register the RDD as a Table following the order of the file fields:
7. Finally, you can make SQL queries on the **health_table **such as:  
<span style="color: #000080">**Expenditure (in billions) by state: **</span>
  <span style="color: #000080">%sql</span>  
<span style="color: #000080">select state, sum(spending)/1000 SpendinginBillions</span><span style="color: #000080"> </span>  
<span style="color: #000080">from health_table</span><span style="color: #000080"> </span>  
<span style="color: #000080">group by state</span><span style="color: #000080"> </span>  
<span style="color: #000080">order by SpendinginBillions desc</span>
  > Macro (inline-media-image)

  
  
<span style="color: #000080">**Expenditure (In Billions) By Year**</span>
  <span style="color: #000080">%sql</span>  
<span style="color: #000080">select year,sum(spending)/1000 SpendinginBillions</span><span style="color: #000080"> </span>  
<span style="color: #000080">from health_table</span><span style="color: #000080"> </span>  
<span style="color: #000080">group by year</span><span style="color: #000080"> </span>  
<span style="color: #000080">order by SpendinginBillions</span>
  > Macro (inline-media-image)

<span style="color: #000080">o </span>
  **<span style="color: #000080">Expenditure (in billions) by area</span>**
  <span style="color: #000080">%sql</span>  
<span style="color: #000080">select category, sum(spending)/1000 SpendinginBillions</span><span style="color: #000080"> </span>  
<span style="color: #000080">from health_table</span><span style="color: #000080"> </span>  
<span style="color: #000080">group by category</span><span style="color: #000080"> </span>  
<span style="color: #000080">order by SpendinginBillions desc</span>
  
  Easy, right? You can combine the results of this Notebook with the platform's interpreter to insert these data in an ontology as we will see in this tutorial: [(Notebooks) Usando el intérprete de la plataforma dentro de los Notebooks](https://onesaitplatform.atlassian.net/wiki/pages/createpage.action?spaceKey=doct&title=Usando%20el%20int%C3%A9rprete%20de%20la%20plataforma%20en%20los%20Notebooks&linkCreation=true&fromPageId=2220806710)