We can even use regular expressions to select the columns we want. The following query selects the symbol column and all columns from stocks whose names start with the prefix price:^[17]

hive> SELECT symbol, `price.*` FROM stocks;
AAPL    195.69  197.88  194.0   194.12  194.12
AAPL    192.63  196.0   190.85  195.46  195.46
AAPL    196.73  198.37  191.57  192.05  192.05
AAPL    195.17  200.2   194.42  199.23  199.23
AAPL    195.91  196.32  193.38  195.86  195.86
...

We’ll talk more about Hive’s use of regular expressions in the section LIKE and RLIKE.

Not only can you select columns in a table, but you can manipulate column values using function calls and arithmetic expressions.

For example, let’s select the employees’ names converted to uppercase, their salaries, federal taxes percentage, and the value that results if we subtract the federal taxes portion from their salaries and round to the nearest integer. We could call a built-in function map_values to extract all the values from the deductions map and then add them up with the built-in sum function.

The following query is long enough that we’ll split it over two lines. Note the secondary prompt that Hive uses, an indented greater-than sign (>):

hive> SELECT upper(name), salary, deductions["Federal Taxes"],
    > round(salary * (1 - deductions["Federal Taxes"])) FROM employees;
JOHN DOE    100000.0  0.2   80000
MARY SMITH   80000.0  0.2   64000
TODD JONES   70000.0  0.15  59500
BILL KING    60000.0  0.15  51000

Let’s discuss arithmetic operators and then discuss the use of functions in expressions.

All the typical arithmetic operators are supported. Table 6-1 describes the specific details.

Arithmetic operators take any numeric type. No type coercion is performed if the two operands are of the same numeric type. Otherwise, if the types differ, then the value of the smaller of the two types is promoted to wider type of the other value. (Wider in the sense that a type with more bytes can hold a wider range of values.) For example, for INT and BIGINT operands, the INT is promoted to BIGINT. For INT and FLOAT operands, the INT is promoted to FLOAT. Note that our query contained (1 - deductions[…]). Since the deductions are FLOATS, the 1 was promoted to FLOAT.

You have to be careful about data overflow or underflow when doing arithmetic. Hive follows the rules for the underlying Java types, where no attempt is made to automatically convert a result to a wider type if one exists, when overflow or underflow will occur. Multiplication and division are most likely to trigger this problem.

It pays to be aware of the ranges of your numeric data values, whether or not those values approach the upper or lower range limits of the types you are using in the corresponding schema, and what kinds of calculations people might do with the data.

If you are concerned about overflow or underflow, consider using wider types in the schema. The drawback is the extra memory each data value will occupy.

You can also convert values to wider types in specific expressions, called casting. See Table 6-2 below and Casting for details.

Finally, it is sometimes useful to scale data values, such as dividing by powers of 10, using log values, and so on. Scaling can also improve the accuracy and numerical stability of algorithms used in certain machine learning calculations, for example.

Our tax-deduction example also uses a built-in mathematical function, round(), for finding the nearest integer for a DOUBLE value.

hive> SELECT count(*), avg(salary) FROM employees;
4  77500.0

hive> SET hive.map.aggr=true;

hive> SELECT count(*), avg(salary) FROM employees;

hive> SELECT count(DISTINCT symbol) FROM stocks;
0

hive> SELECT count(DISTINCT ymd), count(DISTINCT volume) FROM stocks;
12110   26144

hive> SELECT explode(subordinates) AS sub FROM employees;
Mary Smith
Todd Jones
Bill King

SELECT parse_url_tuple(url, 'HOST', 'PATH', 'QUERY') as (host, path, query)
FROM url_table;

The results of a typical query can return a large number of rows. The LIMIT clause puts an upper limit on the number of rows returned:

hive> SELECT upper(name), salary, deductions["Federal Taxes"],
    > round(salary * (1 - deductions["Federal Taxes"])) FROM employees
    > LIMIT 2;
JOHN DOE    100000.0  0.2   80000
MARY SMITH   80000.0  0.2   64000

You can think of the previous example query as returning a new relation with new columns, some of which are anonymous results of manipulating columns in employees. It’s sometimes useful to give those anonymous columns a name, called a column alias. Here is the previous query with column aliases for the third and fourth columns returned by the query, fed_taxes and salary_minus_fed_taxes, respectively:

hive> SELECT upper(name), salary, deductions["Federal Taxes"] as fed_taxes,
    > round(salary * (1 - deductions["Federal Taxes"])) as salary_minus_fed_taxes
    > FROM employees LIMIT 2;
JOHN DOE    100000.0  0.2   80000
MARY SMITH   80000.0  0.2   64000

The column alias feature is especially useful in nested select statements. Let’s use the previous example as a nested query:

hive> FROM (
    >   SELECT upper(name), salary, deductions["Federal Taxes"] as fed_taxes,
    >   round(salary * (1 - deductions["Federal Taxes"])) as salary_minus_fed_taxes
    >   FROM employees
    > ) e
    > SELECT e.name, e.salary_minus_fed_taxes
    > WHERE e.salary_minus_fed_taxes > 70000;
JOHN DOE    100000.0  0.2   80000

The previous result set is aliased as e, from which we perform a second query to select the name and the salary_minus_fed_taxes, where the latter is greater than 70,000. (We’ll cover WHERE clauses in WHERE Clauses below.)

The CASE … WHEN … THEN clauses are like if statements for individual columns in query results. For example:

hive> SELECT name, salary,
    >   CASE
    >     WHEN salary <  50000.0 THEN 'low'
    >     WHEN salary >= 50000.0 AND salary <  70000.0 THEN 'middle'
    >     WHEN salary >= 70000.0 AND salary < 100000.0 THEN 'high'
    >     ELSE 'very high'
    >   END AS bracket FROM employees;
John Doe         100000.0   very high
Mary Smith        80000.0   high
Todd Jones        70000.0   high
Bill King         60000.0   middle
Boss Man         200000.0   very high
Fred Finance     150000.0   very high
Stacy Accountant  60000.0   middle
...

If you have been running the queries in this book so far, you have probably noticed that a MapReduce job is started in most cases. Hive implements some kinds of queries without using MapReduce, in so-called local mode, for example:

SELECT * FROM employees;

In this case, Hive can simply read the records from employees and dump the formatted output to the console.

This even works for WHERE clauses that only filter on partition keys, with or without LIMIT clauses:

SELECT * FROM employees
WHERE country = 'US' AND state = 'CA'
LIMIT 100;

Furthermore, Hive will attempt to run other operations in local mode if the hive.exec.mode.local.auto property is set to true:

set hive.exec.mode.local.auto=true;

Otherwise, Hive uses MapReduce to run all other queries.

Table 6-6 describes the predicate operators, which are also used in JOIN … ON and HAVING clauses.

We’ll discuss LIKE and RLIKE in detail below (LIKE and RLIKE). First, let’s point out an issue with comparing floating-point numbers that you should understand.

A common gotcha arises when you compare floating-point numbers of different types (i.e., FLOAT versus DOUBLE). Consider the following query of the employees table, which is designed to return the employee’s name, salary, and federal taxes deduction, but only if that tax deduction exceeds 0.2 (20%) of his or her salary:

hive> SELECT name, salary, deductions['Federal Taxes']
    > FROM employees WHERE deductions['Federal Taxes'] > 0.2;
John Doe        100000.0        0.2
Mary Smith      80000.0         0.2
Boss Man        200000.0        0.3
Fred Finance    150000.0        0.3

Wait! Why are records with deductions['Federal Taxes'] = 0.2 being returned?

Is it a Hive bug? There is a bug filed against Hive for this issue, but it actually reflects the behavior of the internal representation of floating-point numbers when they are compared and it affects almost all software written in most languages on all modern digital computers (see https://issues.apache.org/jira/browse/HIVE-2586).

When you write a floating-point literal value like 0.2, Hive uses a DOUBLE to hold the value. We defined the deductions map values to be FLOAT, which means that Hive will implicitly convert the tax deduction value to DOUBLE to do the comparison. This should work, right?

Actually, it doesn’t work. Here’s why. The number 0.2 can’t be represented exactly in a FLOAT or DOUBLE. (See http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html for an in-depth discussion of floating-point number issues.) In this particular case, the closest exact value is just slightly greater than 0.2, with a few nonzero bits at the least significant end of the number.

To simplify things a bit, let’s say that 0.2 is actually 0.2000001 for FLOAT and 0.200000000001 for DOUBLE, because an 8-byte DOUBLE has more significant digits (after the decimal point). When the FLOAT value from the table is converted to DOUBLE by Hive, it produces the DOUBLE value 0.200000100000, which is greater than 0.200000000001. That’s why the query results appear to use >= not >!

This issue is not unique to Hive nor Java, in which Hive is implemented. Rather, it’s a general problem for all systems that use the IEEE standard for encoding floating-point numbers!

However, there are two workarounds we can use in Hive.

First, if we read the data from a TEXTFILE (see Chapter 15), which is what we have been assuming so far, then Hive reads the string “0.2” from the data file and converts it to a real number. We could use DOUBLE instead of FLOAT in our schema. Then we would be comparing a DOUBLE for the deductions['Federal Taxes'] with a double for the literal 0.2. However, this change will increase the memory footprint of our queries. Also, we can’t simply change the schema like this if the data file is a binary file format like SEQUENCEFILE (discussed in Chapter 15).

The second workaround is to explicitly cast the 0.2 literal value to FLOAT. Java has a nice way of doing this: you append the letter f or F to the end of the number (e.g., 0.2f). Unfortunately, Hive doesn’t support this syntax; we have to use the cast operator.

Here is a modified query that casts the 0.2 literal value to FLOAT. With this change, the expected results are returned by the query:

hive> SELECT name, salary, deductions['Federal Taxes'] FROM employees
    > WHERE deductions['Federal Taxes'] > cast(0.2 AS FLOAT);
Boss Man        200000.0        0.3
Fred Finance    150000.0        0.3

Note the syntax inside the cast operator: number AS FLOAT.

Actually, there is also a third solution: avoid floating-point numbers for anything involving money.

Table 6-6 describes the LIKE and RLIKE predicate operators. You have probably seen LIKE before, a standard SQL operator. It lets us match on strings that begin with or end with a particular substring, or when the substring appears anywhere within the string.

For example, the following three queries select the employee names and addresses where the street ends with Ave., the city begins with O, and the street contains Chicago:

hive> SELECT name, address.street FROM employees WHERE address.street LIKE '%Ave.';
John Doe        1 Michigan Ave.
Todd Jones      200 Chicago Ave.

hive> SELECT name, address.city FROM employees WHERE address.city LIKE 'O%';
Todd Jones      Oak Park
Bill King       Obscuria

hive> SELECT name, address.street FROM employees WHERE address.street LIKE '%Chi%';
Todd Jones      200 Chicago Ave.

A Hive extension is the RLIKE clause, which lets us use Java regular expressions, a more powerful minilanguage for specifying matches. The rich details of regular expression syntax and features are beyond the scope of this book. The entry for RLIKE in Table 6-6 provides links to resources with more details on regular expressions. Here, we demonstrate their use with an example, which finds all the employees whose street contains the word Chicago or Ontario:

hive> SELECT name, address.street
    > FROM employees WHERE address.street RLIKE '.*(Chicago|Ontario).*';
Mary Smith      100 Ontario St.
Todd Jones      200 Chicago Ave.

The string after the RLIKE keyword has the following interpretation. A period (.) matches any character and a star (*) means repeat the “thing to the left” (period, in the two cases shown) zero to many times. The expression (x|y) means match either x or y.

Hence, there might be no characters before “Chicago” or “Ontario” and there might be no characters after them. Of course, we could have written this particular example with two LIKE clauses:

SELECT name, address FROM employees
WHERE address.street LIKE '%Chicago%' OR address.street LIKE '%Ontario%';

General regular expression matches will let us express much richer matching criteria that would become very unwieldy with joined LIKE clauses such as these.

For more details about regular expressions as implemented by Hive using Java, see the documentation for the Java regular expression syntax at http://docs.oracle.com/javase/6/docs/api/java/util/regex/Pattern.html or see Regular Expression Pocket Reference by Tony Stubblebine (O’Reilly), Regular Expressions Cookbook by Jan Goyvaerts and Steven Levithan (O’Reilly), or Mastering Regular Expressions, 3rd Edition, by Jeffrey E.F. Friedl (O’Reilly).

The HAVING clause lets you constrain the groups produced by GROUP BY in a way that could be expressed with a subquery, using a syntax that’s easier to express. Here’s the previous query with an additional HAVING clause that limits the results to years where the average closing price was greater than $50.0:

hive> SELECT year(ymd), avg(price_close) FROM stocks
    > WHERE exchange = 'NASDAQ' AND symbol = 'AAPL'
    > GROUP BY year(ymd)
        > HAVING avg(price_close) > 50.0;
1987    53.88968399108163
1991    52.49553383386182
1992    54.80338610251119
1999    57.77071460844979
2000    71.74892876261757
2005    52.401745992993554
...

Without the HAVING clause, this query would require a nested SELECT statement:

hive> SELECT s2.year, s2.avg FROM
    > (SELECT year(ymd) AS year, avg(price_close) AS avg FROM stocks
    > WHERE exchange = 'NASDAQ' AND symbol = 'AAPL'
    > GROUP BY year(ymd)) s2
    > WHERE s2.avg > 50.0;
1987    53.88968399108163
...

In an inner JOIN, records are discarded unless join criteria finds matching records in every table being joined. For example, the following query compares Apple (symbol AAPL) and IBM (symbol IBM). The stocks table is joined against itself, a self-join, where the dates, ymd (year-month-day) values must be equal in both tables. We say that the ymd columns are the join keys in this query:

hive> SELECT a.ymd, a.price_close, b.price_close
    > FROM stocks a JOIN stocks b ON a.ymd = b.ymd
    > WHERE a.symbol = 'AAPL' AND b.symbol = 'IBM';
2010-01-04      214.01  132.45
2010-01-05      214.38  130.85
2010-01-06      210.97  130.0
2010-01-07      210.58  129.55
2010-01-08      211.98  130.85
2010-01-11      210.11  129.48
...

The ON clause specifies the conditions for joining records between the two tables. The WHERE clause limits the lefthand table to AAPL records and the righthand table to IBM records. You can also see that using table aliases for the two occurrences of stocks is essential in this query.

As you may know, IBM is an older company than Apple. It has been a publicly traded stock for much longer than Apple. However, since this is an inner JOIN, no IBM records will be returned older than September 7, 1984, which was the first day that Apple was publicly traded!

Standard SQL allows a non-equi-join on the join keys, such as the following example that shows Apple versus IBM, but with all older records for Apple paired up with each day of IBM data. It would be a lot of data (Example 6-1)!

SELECT a.ymd, a.price_close, b.price_close
FROM stocks a JOIN stocks b
ON a.ymd <= b.ymd
WHERE a.symbol = 'AAPL' AND b.symbol = 'IBM';

This is not valid in Hive, primarily because it is difficult to implement these kinds of joins in MapReduce. It turns out that Pig offers a cross product feature that makes it possible to implement this join, even though Pig’s native join feature doesn’t support it, either.

Also, Hive does not currently support using OR between predicates in ON clauses.

To see a nonself join, let’s introduce the corresponding dividends data, also available from infochimps.org, as described in External Tables:

CREATE EXTERNAL TABLE IF NOT EXISTS dividends (
  ymd             STRING,
  dividend        FLOAT
)
PARTITIONED BY (exchange STRING, symbol STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

Here is an inner JOIN between stocks and dividends for Apple, where we use the ymd and symbol columns as join keys:

hive> 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';
1987-05-11      AAPL    77.0    0.015
1987-08-10      AAPL    48.25   0.015
1987-11-17      AAPL    35.0    0.02
...
1995-02-13      AAPL    43.75   0.03
1995-05-26      AAPL    42.69   0.03
1995-08-16      AAPL    44.5    0.03
1995-11-21      AAPL    38.63   0.03

Yes, Apple paid a dividend years ago and only recently announced it would start doing so again! Note that because we have an inner JOIN, we only see records approximately every three months, the typical schedule of dividend payments, which are announced when reporting quarterly results.

You can join more than two tables together. Let’s compare Apple, IBM, and GE side by side:

hive> SELECT a.ymd, a.price_close, b.price_close , c.price_close
    > FROM stocks a JOIN stocks b ON a.ymd = b.ymd
    >               JOIN stocks c ON a.ymd = c.ymd
    > WHERE a.symbol = 'AAPL' AND b.symbol = 'IBM' AND c.symbol = 'GE';
2010-01-04      214.01  132.45  15.45
2010-01-05      214.38  130.85  15.53
2010-01-06      210.97  130.0   15.45
2010-01-07      210.58  129.55  16.25
2010-01-08      211.98  130.85  16.6
2010-01-11      210.11  129.48  16.76
...

Most of the time, Hive will use a separate MapReduce job for each pair of things to join. In this example, it would use one job for tables a and b, then a second job to join the output of the first join with c.

However, this example actually benefits from an optimization we’ll discuss next.

In the previous example, every ON clause uses a.ymd as one of the join keys. In this case, Hive can apply an optimization where it joins all three tables in a single MapReduce job. The optimization would also be used if b.ymd were used in both ON clauses.

Hive also assumes that the last table in the query is the largest. It attempts to buffer the other tables and then stream the last table through, while performing joins on individual records. Therefore, you should structure your join queries so the largest table is last.

Recall our previous join between stocks and dividends. We actually made the mistake of using the smaller dividends table last:

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';

We should switch the positions of stocks and dividends:

SELECT s.ymd, s.symbol, s.price_close, d.dividend
FROM dividends d JOIN stocks s ON s.ymd = d.ymd AND s.symbol = d.symbol
WHERE s.symbol = 'AAPL';

It turns out that these data sets are too small to see a noticeable performance difference, but for larger data sets, you’ll want to exploit this optimization.

Fortunately, you don’t have to put the largest table last in the query. Hive also provides a “hint” mechanism to tell the query optimizer which table should be streamed:

SELECT /*+ STREAMTABLE(s) */ 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';

Now Hive will attempt to stream the stocks table, even though it’s not the last table in the query.

There is another important optimization called map-side joins that we’ll return to in Map-side Joins.

The left-outer join is indicated by adding the LEFT OUTER keywords:

hive> SELECT s.ymd, s.symbol, s.price_close, d.dividend
    > FROM stocks s LEFT OUTER JOIN dividends d ON s.ymd = d.ymd AND s.symbol = d.symbol
    > WHERE s.symbol = 'AAPL';
...
1987-05-01      AAPL    80.0    NULL
1987-05-04      AAPL    79.75   NULL
1987-05-05      AAPL    80.25   NULL
1987-05-06      AAPL    80.0    NULL
1987-05-07      AAPL    80.25   NULL
1987-05-08      AAPL    79.0    NULL
1987-05-11      AAPL    77.0    0.015
1987-05-12      AAPL    75.5    NULL
1987-05-13      AAPL    78.5    NULL
1987-05-14      AAPL    79.25   NULL
1987-05-15      AAPL    78.25   NULL
1987-05-18      AAPL    75.75   NULL
1987-05-19      AAPL    73.25   NULL
1987-05-20      AAPL    74.5    NULL
...

In this join, all the records from the lefthand table that match the WHERE clause are returned. If the righthand table doesn’t have a record that matches the ON criteria, NULL is used for each column selected from the righthand table.

Hence, in this result set, we see that the every Apple stock record is returned and the d.dividend value is usually NULL, except on days when a dividend was paid (May 11^th, 1987, in this output).

Before we discuss the other outer joins, let’s discuss a gotcha you should understand.

Recall what we said previously about speeding up queries by adding partition filters in the WHERE clause. To speed up our previous query, we might choose to add predicates that select on the exchange in both tables:

hive> SELECT s.ymd, s.symbol, s.price_close, d.dividend
    > FROM stocks s LEFT OUTER JOIN dividends d ON s.ymd = d.ymd AND s.symbol = d.symbol
    > WHERE s.symbol = 'AAPL'
    > AND s.exchange = 'NASDAQ' AND d.exchange = 'NASDAQ';
1987-05-11      AAPL    77.0    0.015
1987-08-10      AAPL    48.25   0.015
1987-11-17      AAPL    35.0    0.02
1988-02-12      AAPL    41.0    0.02
1988-05-16      AAPL    41.25   0.02
...

However, the output has changed, even though we thought we were just adding an optimization! We’re back to having approximately four stock records per year and we have non-NULL entries for all the dividend values. In other words, we are back to the original inner join!

This is actually common behavior for all outer joins in most SQL implementations. It occurs because the JOIN clause is evaluated first, then the results are passed through the WHERE clause. By the time the WHERE clause is reached, d.exchange is NULL most of the time, so the “optimization” actually filters out all records except those on the day of dividend payments.

One solution is straightforward; remove the clauses in the WHERE clause that reference the dividends table:

hive> SELECT s.ymd, s.symbol, s.price_close, d.dividend
    > FROM stocks s LEFT OUTER JOIN dividends d ON s.ymd = d.ymd AND s.symbol = d.symbol
    > WHERE s.symbol = 'AAPL' AND s.exchange = 'NASDAQ';
...
1987-05-07      AAPL    80.25   NULL
1987-05-08      AAPL    79.0    NULL
1987-05-11      AAPL    77.0    0.015
1987-05-12      AAPL    75.5    NULL
1987-05-13      AAPL    78.5    NULL
...

This isn’t very satisfactory. You might wonder if you can move the predicates from the WHERE clause into the ON clause, at least the partition filters. This does not work for outer joins, despite documentation on the Hive Wiki that claims it should work (https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins).

hive> SELECT s.ymd, s.symbol, s.price_close, d.dividend
    > FROM stocks s LEFT OUTER JOIN dividends d
    > ON s.ymd = d.ymd AND s.symbol = d.symbol
    > AND s.symbol = 'AAPL' AND s.exchange = 'NASDAQ' AND d.exchange = 'NASDAQ';
...
1962-01-02      GE      74.75   NULL
1962-01-02      IBM     572.0   NULL
1962-01-03      GE      74.0    NULL
1962-01-03      IBM     577.0   NULL
1962-01-04      GE      73.12   NULL
1962-01-04      IBM     571.25  NULL
1962-01-05      GE      71.25   NULL
1962-01-05      IBM     560.0   NULL
...

The partition filters are ignored for OUTER JOINTS. However, using such filter predicates in ON clauses for inner joins does work!

Fortunately, there is solution that works for all joins; use nested SELECT statements:

hive> SELECT s.ymd, s.symbol, s.price_close, d.dividend FROM
    > (SELECT * FROM stocks WHERE symbol = 'AAPL' AND exchange = 'NASDAQ') s
    > LEFT OUTER JOIN
    > (SELECT * FROM dividends WHERE symbol = 'AAPL' AND exchange = 'NASDAQ') d
    > ON s.ymd = d.ymd;
...
1988-02-10      AAPL    41.0    NULL
1988-02-11      AAPL    40.63   NULL
1988-02-12      AAPL    41.0    0.02
1988-02-16      AAPL    41.25   NULL
1988-02-17      AAPL    41.88   NULL
...

The nested SELECT statement performs the required “push down” to apply the partition filters before data is joined.

Right-outer joins return all records in the righthand table that match the WHERE clause. NULL is used for fields of missing records in the lefthand table.

Here we switch the places of stocks and dividends and perform a righthand join, but leave the SELECT statement unchanged:

hive> SELECT s.ymd, s.symbol, s.price_close, d.dividend
    > FROM dividends d RIGHT OUTER JOIN stocks s ON d.ymd = s.ymd AND d.symbol = s.symbol
    > WHERE s.symbol = 'AAPL';
...
1987-05-07      AAPL    80.25   NULL
1987-05-08      AAPL    79.0    NULL
1987-05-11      AAPL    77.0    0.015
1987-05-12      AAPL    75.5    NULL
1987-05-13      AAPL    78.5    NULL
...

Finally, a full-outer join returns all records from all tables that match the WHERE clause. NULL is used for fields in missing records in either table.

If we convert the previous query to a full-outer join, we’ll actually get the same results, since there is never a case where a dividend record exists without a matching stock record:

hive> SELECT s.ymd, s.symbol, s.price_close, d.dividend
    > FROM dividends d FULL OUTER JOIN stocks s ON d.ymd = s.ymd AND d.symbol = s.symbol
    > WHERE s.symbol = 'AAPL';
...
1987-05-07      AAPL    80.25   NULL
1987-05-08      AAPL    79.0    NULL
1987-05-11      AAPL    77.0    0.015
1987-05-12      AAPL    75.5    NULL
1987-05-13      AAPL    78.5    NULL
...

A left semi-join returns records from the lefthand table if records are found in the righthand table that satisfy the ON predicates. It’s a special, optimized case of the more general inner join. Most SQL dialects support an IN ... EXISTS construct to do the same thing. For instance, the following query in Example 6-2 attempts to return stock records only on the days of dividend payments, but it doesn’t work in Hive.

SELECT s.ymd, s.symbol, s.price_close FROM stocks s
WHERE s.ymd, s.symbol IN
(SELECT d.ymd, d.symbol FROM dividends d);

Instead, you use the following LEFT SEMI JOIN syntax:

hive> SELECT s.ymd, s.symbol, s.price_close
    > FROM stocks s LEFT SEMI JOIN dividends d ON s.ymd = d.ymd AND s.symbol = d.symbol;
...
1962-11-05      IBM     361.5
1962-08-07      IBM     373.25
1962-05-08      IBM     459.5
1962-02-06      IBM     551.5

Note that the SELECT and WHERE clauses can’t reference columns from the righthand table.

The reason semi-joins are more efficient than the more general inner join is as follows. For a given record in the lefthand table, Hive can stop looking for matching records in the righthand table as soon as any match is found. At that point, the selected columns from the lefthand table record can be projected.

A Cartesian product is a join where all the tuples in the left side of the join are paired with all the tuples of the right table. If the left table has 5 rows and the right table has 6 rows, 30 rows of output will be produced:

SELECTS * FROM stocks JOIN dividends;

Using the table of stocks and dividends, it is hard to find a reason for a join of this type, as the dividend of one stock is not usually paired with another. Additionally, Cartesian products create a lot of data. Unlike other join types, Cartesian products are not executed in parallel, and they are not optimized in any way using MapReduce.

It is critical to point out that using the wrong join syntax will cause a long, slow-running Cartesian product query. For example, the following query will be optimized to an inner join in many databases, but not in Hive:

hive > SELECT * FROM stocks JOIN dividends
     > WHERE stock.symbol = dividends.symbol and stock.symbol='AAPL';

In Hive, this query computes the full Cartesian product before applying the WHERE clause. It could take a very long time to finish. When the property hive.mapred.mode is set to strict, Hive prevents users from inadvertently issuing a Cartesian product query. We’ll discuss the features of strict mode more extensively in Chapter 10.

If all but one table is small, the largest table can be streamed through the mappers while the small tables are cached in memory. Hive can do all the joining map-side, since it can look up every possible match against the small tables in memory, thereby eliminating the reduce step required in the more common join scenarios. Even on smaller data sets, this optimization is noticeably faster than the normal join. Not only does it eliminate reduce steps, it sometimes reduces the number of map steps, too.

The joins between stocks and dividends can exploit this optimization, as the dividends data set is small enough to be cached.

Before Hive v0.7, it was necessary to add a hint to the query to enable this optimization. Returning to our inner join example:

SELECT /*+ MAPJOIN(d) */ 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';

Running this query versus the original on a fast MacBook Pro laptop yielded times of approximately 23 seconds versus 33 seconds for the original unoptimized query, which is roughly 30% faster using our sample stock data.

The hint still works, but it’s now deprecated as of Hive v0.7. However, you still have to set a property, hive.auto.convert.join, to true before Hive will attempt the optimization. It’s false by default:

hive> set hive.auto.convert.join=true;

hive> 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';

Note that you can also configure the threshold size for table files considered small enough to use this optimization. Here is the default definition of the property (in bytes):

hive.mapjoin.smalltable.filesize=25000000

If you always want Hive to attempt this optimization, set one or both of these properties in your $HOME/.hiverc file.

Hive does not support the optimization for right- and full-outer joins.

This optimization can also be used for larger tables under certain conditions when the data for every table is bucketed, as discussed in Bucketing Table Data Storage. Briefly, the data must be bucketed on the keys used in the ON clause and the number of buckets for one table must be a multiple of the number of buckets for the other table. When these conditions are met, Hive can join individual buckets between tables in the map phase, because it does not need to fetch the entire contents of one table to match against each bucket in the other table.

However, this optimization is not turned on by default. It must be enabled by setting the property hive.optimize.bucketmapjoin:

set hive.optimize.bucketmapjoin=true;

If the bucketed tables actually have the same number of buckets and the data is sorted by the join/bucket keys, then Hive can perform an even faster sort-merge join. Once again, properties must be set to enable the optimization:

set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;

The new BINARY type introduced in Hive v0.8.0 only supports casting BINARY to STRING. However, if you know the value is a number, you can nest cast() invocations, as in this example where column b is a BINARY column:

SELECT (2.0*cast(cast(b as string) as double)) from src;

You can also cast STRING to BINARY.

Hive offers another syntax for sampling a percentage of blocks of an input path as an alternative to sampling based on rows:

hive> SELECT * FROM numbersflat TABLESAMPLE(0.1 PERCENT) s;

Percentage-based sampling offers a variable to control the seed information for block-based tuning. Different seeds produce different samples:

<property>
  <name>hive.sample.seednumber</name>
  <value>0</value>
  <description>A number used for percentage sampling. By changing this
  number, user will change the subsets of data sampled.</description>
</property>

From a first look at the TABLESAMPLE syntax, an astute user might come to the conclusion that the following query would be equivalent to the TABLESAMPLE operation:

hive> SELECT * FROM numbersflat WHERE number % 2 = 0;
2
4
6
8
10

It is true that for most table types, sampling scans through the entire table and selects every N^th row. However, if the columns specified in the TABLESAMPLE clause match the columns in the CLUSTERED BY clause, TABLESAMPLE queries only scan the required hash partitions of the table:

hive> CREATE TABLE numbers_bucketed (number int) CLUSTERED BY (number) INTO 3 BUCKETS;

hive> SET hive.enforce.bucketing=true;

hive> INSERT OVERWRITE TABLE numbers_bucketed SELECT number FROM numbers;

hive> dfs -ls /user/hive/warehouse/mydb.db/numbers_bucketed;
/user/hive/warehouse/mydb.db/numbers_bucketed/000000_0
/user/hive/warehouse/mydb.db/numbers_bucketed/000001_0
/user/hive/warehouse/mydb.db/numbers_bucketed/000002_0

hive> dfs -cat /user/hive/warehouse/mydb.db/numbers_bucketed/000001_0;
1
7
10
4

Because this table is clustered into three buckets, the following query can be used to sample only one of the buckets efficiently:

hive> SELECT * FROM numbers_bucketed TABLESAMPLE (BUCKET 2 OUT OF 3 ON NUMBER) s;
1
7
10
4