Week 7: HIVE and Apache SparkSQL

Question 1

HIVE

Answer 1

It’s an open-source data warehousing solution built on top of Hadoop. It can support queries written in HiveQL, which is similar to SQL, as well as queries that are compiled into MapReduce jobs that are executed using Hadoop. HIVE is like a wrapper for people familiar with SQL to help them do big data processing in the context of data warehousing.

Pros:
1. An easy way to process large scale data.
2. Supports SQL-based queries.
3. Provides more user defined interfaces to extend programmability.
4. Efficient execution of plans for performance interoperability with other databases.

Cons:
1. No easy way to append data.
2. Files in HDFS are immutable.

Question 2

HIVE: Applications

Answer 2

Common applications include: log processing, text mining, document indexing, customer-facing business intelligence (Google Analytics), predictive modelling, hypothesis testing

Question 3

HIVE Components: Client Components

Answer 3

Command Line Interface (CLI), the web UI, JDBC/ODBC driver.

Question 4

HIVE Components: Driver

Answer 4

The driver manages the lifecycle of a HiveQL statement as it moves through HIVE, maintains a session handle, and maintains session statistics.

Question 5

HIVE Components: Compiler

Answer 5

It compiles HiveQL into MapReduce tasks.

Question 6

HIVE Components: Optimiser

Answer 6

Optimises the tasks (improves HiveQL)

Question 7

HIVE Components: Executor

Answer 7

It executes the tasks in the proper order and interacts with Hadoop.

Question 8

HIVE Components: Metastore

Answer 8

It serves as the system catalogue and stores information about tables, partitions, locations in the HDFS and etc. It runs on an RDBMS, not on a HDFS, as it needs to have very low latency.

Question 9

HIVE Components: Thrift Server

Answer 9

It provides an interface between the clients and the Metastore, allowing the clients to query or modify the information in the Metastore.

Question 10

Data Units of the Data Model: Table

Answer 10

Each table consists of a number of rows, and each row has a specified number of columns.

Example code:
CREATE TABLE t1(st string, fl float, li list<map<string,struct<p1:int,p2:int»);

Question 11

Data Units of the Data Model: Partition

Answer 11

Partitions come from decomposing a table into partitions, based on values. Making partitions speeds up querying, as only relevant data in Hadoop are scanned.

Example code:
CREATE TABLE test_part(c1 string, c2 int)
PARTITIONED BY (ds string, hr int);

ALTER TABLE test_part
ADD PARTITION(ds=’2009-02-02’,hr=11);

Question 12

Data Units of the Data Model: Bucket

Answer 12

Buckets come from decomposing a table into buckets. These are useful for sampling, especially when using partitions will result in them being too numerous and too small.

Example code:
CREATE TABLE weblog (user_id INT, url STRING, source_ip STRING)
PARTITIONED BY (dt STRING)
CLUSTERED BY (user_id) INTO 96 BUCKETS;

Example code:
SELECT product_id, sum(price)
FROM sales TABLESAMPLE (BUCKET 1 OUT OF 32)
GROUP BY product_id

Question 13

HIVE: Mapping Data Units

Answer 13

The following workflow is used to map data units into the HDFS name space:

1. A table is stored in a directory in HDFS.
2. A partition of the table is stored in a subdirectory within a table’s HDFS directory.
3. A bucket is stored in a file within the partition’s or table’s directory depending on whether the table is a partitioned table or not.

Hive prunes the data by scanning only the required sub-directories tied to relevant partitions.

Hive uses the file corresponding to a bucket, making bucketing useful for sampling.

Question 14

HIVE Query Limitations

Answer 14

1. HIVE doesn’t support inserting into an existing table or data partition and all inserts overwrite existing data.
2. Only equality predicates are supported in JOIN.

Otherwise, there are plenty of SQL commands supported.

Question 15

HiveQL: Selecting a Database

Answer 15

USE database;

Question 16

HiveQL: Listing Databases

Answer 16

SHOW DATABASES;

Question 17

HiveQL: Listing Tables in a Database

Answer 17

SHOW TABLES;

Question 18

HiveQL: Describing the Format of a Table

Answer 18

DESCRIBE (FORMATTED|EXTENDED) table;

Question 19

HiveQL: Creating a Database

Answer 19

CREATE DATABASE db_name;

Question 20

HiveQL: Dropping a Database

Answer 20

DROP DATABASE db_name (CASCADE);

Question 21

HiveQL: Retrieving Information

Answer 21

SELECT from_columns FROM table WHERE conditions;

Question 22

HiveQL: All Values

Answer 22

SELECT * FROM table;

Question 23

HiveQL: Some Values

Answer 23

SELECT * FROM table WHERE rec_name = “Value”;

Question 24

HiveQL: Multiple Criteria

Answer 24

SELECT * from TABLE WHERE rec1 = “value” AND rec2 = “value2”;

Question 25

HiveQL: Selecting Specific Columns

Answer 25

SELECT column-name FROM table;

Question 26

HiveQL: Retrieving Unique Output Records

Answer 26

SELECT DISTINCT column_name FROM table;

Question 27

HiveQL: Sorting

Answer 27

SELECT col1, col2 FROM table ORDER BY col2;

Question 28

HiveQL: Sorting Backwards

Answer 28

SELECT col1, col2 FROM table ORDER BY col2 DESC;

Question 29

HiveQL: Counting Rows

Answer 29

SELECT COUNT(*) FROM table;

Question 30

HiveQL: Grouping with Counting

Answer 30

SELECT owner, COUNT(*) FROM table GROUP BY owner;

Question 31

HiveQL: Maximum Value

Answer 31

SELECT MAX(col_name) AS label FROM table;

Question 32

HiveQL: Selecting From Multiple Tables

Answer 32

SELECT pet.name, comment FROM pet JOIN event ON (pet.name = event.name)

Question 33

HIVE: JOIN Efficiency

Answer 33

The mapper sends all rows with the same key to a single reducer, and the reducer does the join.

If many rows have the same key, then the efficiency drops.

To make JOIN operations more efficient, keep the smaller table data in memory first, join with a chunk of the larger table data each time. As such, everything is done in memory, without the reduce step required in the more common join scenarios.

Example code:
set hive.auto.convert.join=true;
SELECT s.ymd, s.symbol, s.price_close, d.dividend
FROM stocks s JOIN dividends d ON s.ymd = d.ymd AND s.symbol = d.symbol
WHERE s.symbol = ‘AAPL’;

Question 34

Serializer/Deserializer (SerDe)

Answer 34

It describes how to load the data from the file into a representation that makes it look like a table. It’s implemented using JAVA. There are several built-in serializers/deserializers.

Question 35

Lazy SerDe

Answer 35

Doesn’t fully materialise an object, until individual attributes are necessary. Reduces the overhead to create unnecessary objects in Hive. This increases performance.

Question 36

SparkSQL

Answer 36

It’s a new module in Apache Spark that integrates relational processing with Spark’s functional programming API. It allows Spark programmers to leverage the benefits of relational processing, and it lets SQL users call complex analytics libraries in Spark.

SparkSQL runs as a library on top of Spark, exposes interfaces accessible through JDBC and command-line, and exposes the DataFrame API which is accessible through different programming languages.

It tries to bridge the relational processing model with the native RDD’s in Spark by using a DataFrame API that can perform relational operations on both external data sources and Spark’s built-in RDD’s, and a highly extensible optimiser, Catalyst.

Pros:
1. Has access to MapReduce, a low-level programming language.
2. Integrated with SQL, a declarative language.
3. Great for ETL (Extract, Transform, and Load) to and from various semi or unstructured data sources, and advanced analytics that are hard to express in relational systems.

Cons:
1. MapReduce isn’t the best fit for data warehousing operations for Big Data.

Question 37

DataFrame

Answer 37

It’s a distributed collection of rows with the same schema, similar to a table in RDBMS. It can built from external data sources or RDD’s, support relational operators (e.g. where, grouby) as well as Spark operations. It’s evaluated lazily, with each DataFrame object representing a logical plan to compute a dataset but no execution occurs until the user calls an output operation. This enables optimisation.

DataFrames use operators (i.e. ===), and can also be registered as temporary SQL tables and queried using SQL.

Example code:
ctx = new HiveContext()
users = ctx.table(“users”)
young = users.where(users(“age” < 21)
println(young.count())

Question 38

SparkSQL: Data Model

Answer 38

SparkSQL uses a nested data model based on HIVE. It supports different data types, which allows for modelling data from HIVE, RDBMS, JSON, and native objects in JAVA/Python/Scala.

Question 39

Advantages of DataFrames over Relational Query Languages

Answer 39

1. Code can be rewritten in different languages and benefit from optimisations across the whole logical plan.
2. Programmers can use control structures (if, loops)
3. Logical plan is analysed eagerly although query results are computed lazily. SparkSQL reports an error as soon as the user types an invalid line of code.

Question 40

SparkSQL: Querying Native Datasets

Answer 40

SparkSQL infers the schema of the native objects of a programming language automatically. This allows running relational operations on existing Spark programmes, and combines RDD’s with external structured data.

Question 41

SparkSQL: Catalyst

Answer 41

It’s a query optimiser built on Scala, uses Scala’s pattern matching features, and represents Abstract Syntax Trees (AST’s) and applies rules to manipulate them.

Example code:
tree.transform{
case Add(Literal(c1), Literal(c2)) -> Literal(c1+c2)
}