APLICACIONES

Indexing is a challenge in a world of big data that is based on a distributed file system such as HDFS. HDFS’s random block placement presents difficulties in doing traditional indexes.

An index is another data file organized by one or more columns in the original raw data file. Building an index entails pointing to data in the HDFS file blocks that are randomly scattered on the distributed file system.

As newer raw data files are made available on the file system, the index somehow has to know to update itself. With traditional indexes, adding rows to a table leads to many random updates to the index files. While standard (POSIX compliant) file systems support random updates, big data storage solutions such as HDFS and S3 do not. With HDFS, existing files cannot be updated. HDFS only supports appending new data at the end of an existing file. Amazon S3 does not support random writes or file append; hence, it is impossible to implement standard indexes on HDFS or Amazon S3. When companies such as Pivotal migrated from the Greenplum database to HDFS, they eliminated index support. JethroData, a company based in Israel, has resurrected the idea of indexes to solve some of the problems noted previously. JethroData indexes have been designed to work natively on HDFS and Amazon S3 without index update overhead. JethroData has made index maintenance so cheap that, without a second thought, every column can be automatically indexed, thus accelerating many more queries. A detailed look at JethroData architecture is done in Chapter 4 .

Storage

CPU Time

Lower Storage Costs Slower Compress-Decompress

Bzip, Gzip, ZLib

Larger Storage Costs Faster Compress-Decompress

Snappy, LZO, LZ4

Hive has supported indexes since version 0.8, but these are very limited in utility and not used widely. Hive supports CREATE INDEX at the DDL level. The Hive index table contains one row for each index-value, block offset and a set of block lists for each row in the block (1 = row contains the indexed column value). Hive indexes are similar to non-clustered indexes in relational databases. Hive keeps a map of the data and HDFS blocks it resides in. This enables a MapReduce job to figure out relevant blocks to process queries. Internally, Hive indexes are nothing but another file on HDFS.

The Hive DDL for creating indexes looks like the following: CREATE INDEX <IndexName>

ON TABLE <TableName>(<ColumnName>) AS 'COMPACT'

WITH DEFERRED REBUILD; OR

CREATE INDEX <IndexName>

ON TABLE <TableName>(<ColumnName>) AS 'BITMAP'

WITH DEFERRED REBUILD;

The DEFERRED REBUILD directive instructs Hive to populate the index at a later stage. When data in the base table changes, the ALTER…REBUILD command is used to bring the index up to date.

Once the index is built, Hive will create a new index table for each index, which is added to the hive metadata and can be queried/described by as any regular table.

Partitioning

Partititioning, especially in the context of Hive, allows for division of the data in a table by one or more columns, based on the values of the partitioned columns. This segregates the input records into different files/directories, based on the column values.

A simple query in Hive scans the whole table. This is a slow process, which could be speeded up by creating Hive partitions. When the table is queried, if the partitioned clause is used in the WHERE clause, only the required partitions (directory) is scanned, thereby reducing the I/O costs, by avoiding reading of data that is known not to satisfy the query, based on the partitioned column.

Advantages

• As the data is sliced in partitions across directories, a query is faster to process the partitioned part of the data, instead of doing a full scan.

CHAPTER 2 ■ SQL-ON-BIG-DATA CHALLENGES & SOLUTIONS

Limitations

• Having too many partitions creates large numbers of files and directories in HDFS, which is an overhead to Name Node, because it must keep all metadata for the file system in memory. • Partitions optimize queries based on WHERE clauses but are less

useful for other queries not involving the partition column in the WHERE clause.

Bucketing

Bucketing is another technique used to cluster data sets into more manageable parts, to optimize query performance. It is different from partitioning in that each bucket corresponds to segments of files in HDFS. Too granular a partitioning may lead to deep and small partitions and directories that can increase the number of files and slow down Name Node performance and increase its overhead.

Bucketing can be used on tables with partitioning or without partitions. The value of the bucket column is hashed by a user-defined number into buckets. Records with the same values of the bucket column are stored in the same bucket (segment of files). Buckets help Hive to efficiently perform sampling and map side joins.

Buckets are essentially files in the leaf-level directories that correspond to records that have the same column-value hash. Hive users can specify the number of buckets per partition or per table.

When tables have been bucketed, it is important to set the number of Reduce tasks to be equal to the number of buckets. This can be done by setting the number of Reduce tasks explicitly for every job. Another way is to let Hive automatically bucket the data by setting the hive.enforce.bucketing property to true .

Recommendations

In most cases, your workloads will be bottlenecked with I/O or network. It is wise, therefore, to choose a format that reduces the amount of data that gets transferred over the wire. In most cases, it is true that CPU cores are idle while waiting to get the data and start processing. Depending on your workload pattern and the complexities of SQL analytic queries, it behooves you to choose the right data format, along with the right compression algorithm, based on whether the compression algorithm is CPU-bound or space-bound.

Always incorporate partitioning in your data ingestion and data pipelines, because that is the best way to leverage distributed systems and improve throughput. If there are too many partitions in your data, it is also advisable to consider using bucketing as a way to reduce small partitions. Partitions also help in use cases involving bad data or data quality issues or solving change-data-capture-based scenarios.

Performance tuning is a whole separate topic by itself, and performance tuning on distributed systems is an even more involved topic. A lot of performance tuning also depends on the kind of hardware, the specification of the I/O and networking, and memory sizing and bandwidth.

Summary

This chapter, we covered different types of SQL and the different SLAs associated with the different types of workloads. It also looked at the architecture of SQL engines on relational databases and how the same can be achieved with the big data–processing frameworks of today with different implementations. We discussed ways of reducing latency of SQL queries on large data sets, using different file formats, encoding, and compression techniques.

In the next chapter, we will delve deeper into the architecture of SQL on Hadoop with the Hive engine, which uses MapReduce to perform SQL queries. We will see how Hive is making architectural advances to accommodate the growing need to support low latency and reduce data-movent and shuffling issues encountered on complex MapReduce jobs.

CHAPTER 3