pixels-hive

This is the Pixels Hive SerDe (short for “Serializer and Deserializer.”). Hive uses SerDe and the corresponding FileFormat to read and write tables.

The flow of deserialization is:

HDFS files –> InputFileFormat –> <key, value> –> Deserializer –> Row object

While the flow of serialization is:

Row object –> Serializer –> <key, value> –> OutputFileFormat –> HDFS files

Compatibility

Pixels Serde has been tested on Hive 2.1 and 2.3. Other Hive versions that are compatible with the SerDe API in Hive 2.3 should also work well with Pixels SerDe.

Currently, Pixels Hive SerDe only supports HDFS as the underlying storage.

Build

This project can be opened as a maven project in Intellij and built using maven.

However, Pixels is the parent of this project, therefore use mvn install to install Pixels modules into your local maven repository, before building this project.

Use Pixels in Hive

Build pixels-hive using mvn package. Find the pixels-hive-*-full.jar under the target directory. This is the SerDe that can be used in Hive.

Ensure that Pixels and other prerequisites are installed following the instructions HERE. Presto is not needed as we are using Hive instead as the query engine.

Load Pixels SerDe

There are two options to load Pixels SerDe in Hive:

• In Hive cli:

hive> add jar {PATH}/pixels-hive-*-full.jar

• You can also put pixels-hive-*-full.jar in the path HIVE_HOME/auxlib/, where HIVE_HOME is the installation directory of Hive. Then Hive will load the jar when launching.

Create Table

Create tables in Hive with the syntax in Hive Language Manual. Within the CREATE TABLE statement, use the following ROW FORMAT and TABLEPROPERTY:

ROW FORMAT SERDE
  'io.pixelsdb.pixels.hive.PixelsSerDe' 
STORED AS INPUTFORMAT 
  'io.pixelsdb.pixels.hive.mapred.PixelsInputFormat' 
OUTPUTFORMAT 
  'io.pixelsdb.pixels.hive.mapred.PixelsOutputFormat'
LOCATION
  'hdfs://hostname:9000/path/to/pixels/files'
TBLPROPERTIES ('bind.pixels.table'='schema_name.table_name')

LOCATION is necessary. It should be the same as the order_path or compact_path in Pixels metadata. bind.pixels.table specifies which table in Pixels to be bind with this Hive table. Replace the schema_name and table_name with the right schema and table name in Pixels.

Note: It is a better to create an EXTERNAL table. Internal table will delete the data when the table is dropped.

Load Data

Load data by pixels-cli. Then it is ready to execute queries in Hive. Currently, we have only implemented PixelsInputFormat for Hive, therefore data can not be loaded using Hive's LOAD command. An example of pixels-cli usage shown in the TPC-H Evaluation.

Run Queries

Before executing a query, set hive.input.format in the session:

set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

The default hive.input.format is CombineHiveInputFormat, which will not invoke our dynamic splitting algorithm that generates the input splits for MapReduce jobs. Pixels SerDe currently declines to read and process splits generated by CombineHiveInputSplit.

In addition, it is better to set a smaller value for the max reducers in a job, such as:

set hive.exec.reducers.max=16

Pixels is an efficient columnar store, especially for wide tables. It is likely that only a very small portion of data is read from each split. The default reducer number in Hive is estimated by the total size of the input splits (num_reducer=min(hive.exec.reducers.max, (total_input_size/N)), which is generally much larger than the actual number of reducers we need. The default max reducers in Hive 2.x is 1099.

Note: the logs of pixels-hive are included in the Hive log file that is located at \tmp\{user_name}\hive.log by default.