5-hive.Rpres

Analysing New York taxis with RHive
========================================================
author: Andrie de Vries & Michele Usuelli
date: 2015-07-01, UseR!2015
width: 1680
height: 1050
css: css/custom.css



Introduction to Hive
====
  
- Hadoop data warehouse
- SQL-like interface
- Language: HQL (Hive Query Language)
- HQL translated into MapReduce jobs
- R interface: RHive, RevoScaleR
- Hue web interface



Using Hive with R
====

- Building queries using string manipulation tools like sprintf and paste
- Running queries using RHive


Syntax difference between Hive and RHive
====
type: alert

- Hive: semi-colon in the end

```
SELECT COUNT(*) FROM table_data;
```

- RHive: no semi-colon in the end

```
SELECT COUNT(*) FROM table_data
```





Computing the number of taxi rides by hour
====
  
Using Hive, we can replicate the rmr2 example counting the number of taxi runs by hour. The steps are

1. Importing the data
2. Querying the data




Defining an external table
====

Starting from our data, we can create a Hive table. Since the data is already in CSV format, the easiest option is creating an external table.

We need to specify

- The data location
- The data format
- The field formats




Query to create an extenal table
====

```
CREATE EXTERNAL TABLE taxi_sample(medallion STRING, hack_license STRING, vendor_id STRING, rate_code INT, store_and_fwd_flag STRING, pickup_datetime STRING, dropoff_datetime STRING, passenger_count INT, trip_time_in_secs INT, trip_distance FLOAT, pickup_longitude FLOAT, pickup_latitude FLOAT, dropoff_longitude FLOAT, dropoff_latitude FLOAT) ROW FORMAT
DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/user/share/taxi/sample';
```



Query to create an external table - defining the field format
====

The first part of the query defines a table called *taxi_sample* and the format of its fields.

```
CREATE EXTERNAL TABLE taxi_sample(medallion STRING, hack_license STRING, vendor_id STRING, rate_code INT, store_and_fwd_flag STRING, pickup_datetime STRING, dropoff_datetime STRING, passenger_count INT, trip_time_in_secs INT, trip_distance FLOAT, pickup_longitude FLOAT, pickup_latitude FLOAT, dropoff_longitude FLOAT, dropoff_latitude FLOAT) ROW FORMAT
```



Query to create an external table: defining the data format
====

This part of the query defines the data format.

```
DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
```


Query to create an external table: defining the data location
====

This part of the query defines the HDFS path to the data folder.

```
LOCATION '/user/share/taxi/sample';
```


Setting up RHive
====

```{r 2-hive-queries.R, cache=FALSE, include=FALSE}
read_chunk("hive/2-hive-queries.R")
```

```{r configure-rhive}
```




Simple query: count the number of records
====


```{r row-count}
```

```{r run-row-count, cache=TRUE}
```



Simple query: count the number of records
====


```{r build-row-count}
```

```{r run-row-count, cache=TRUE}
```




Define the hour
====

```{r define-hour}
```





Define the hour
====

```{r build-define-hour}
```



Define the hour
====

```{r run-define-hour, cache=TRUE}
```



Count by hour
====

```{r count-by-hour}
```




Count by hour
====

```{r build-count-by-hour}
```



Count by hour
====

```{r run-count-by-hour, cache=TRUE}
```



Conclusions
====

- Hive allows to define group-by queries writing SQL-like code
- The MapReduce code is not displayed
- R tools for string manipulation allow to easily build complex queries
- RHive allows to run Hive queries directly from R
- For more complex queries, we still need to use other tools like rmr2