Getting Started with Hive in DataStax Enterprise¶

Moving the Job Tracker Node for DSE¶

If your primary Job Tracker node fails, DataStax Enterprise provides a utility to allow you to fail-over to an alternate job tracker node. The dsetool movejt utility can be used to move the job tracker to another Analytics node in the cluster.

1. Log in to a DSE Analytics node.
2. Run the dsetool movejt command and specify the IP address of the new job tracker node in your DSE cluster. For example:

dsetool movejt 110.82.155.4

or in a binary distribution:

$DSE_HOME/bin/dsetool movejt 110.82.155.4

3. Allow 20 seconds for all of the Analytics nodes to detect the change and restart their task tracker processes.
4. In a browser, see if you can connect to the new job tracker and confirm that it is up and running. For example (change the IP to reflect your job tracker node IP):

http://110.82.155.4:50030

5. If you are running Hive or Pig MapReduce clients, you will need to restart them so that they pick up the new job tracker node information.

Mapping a Hive Database to a Cassandra Keyspace¶

To access data stored in Cassandra, you would first define a database in Hive that maps to a keyspace in Cassandra. One way you can map them is by making sure that the name is the same in both Hive and Cassandra. For example:

hive> CREATE DATABASE PortfolioDemo;

Optionally, if your Hive database and Cassandra keyspace use different names (or the Cassandra keyspace does not exist), you can declare keyspace properties in your external table definition using the TBLPROPERTIES clause. If the keyspace does not yet exist in Cassandra, Hive will create it.

For example, in the case where the keyspace exists in Cassandra but under a different name:

hive> CREATE DATABASE MyHiveDB;

hive> CREATE EXTERNAL TABLE MyHiveTable(row_key string, col1 string, col2 string)
      STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler'
      TBLPROPERTIES ( "cassandra.ks.name" = "MyCassandraKS" )

Or if the keyspace does not exist in Cassandra yet and you want to create it:

hive> CREATE EXTERNAL TABLE MyHiveTable(row_key string, col1 string, col2 string)
      STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler'
      TBLPROPERTIES ( "cassandra.ks.name" = "MyCassandraKS",
                      "cassandra.ks.repfactor" = "2",
                      "cassandra.ks.strategy" = "org.apache.cassandra.locator.NetworkTopologyStrategy");

Mapping Hive External Tables to Cassandra Column Families¶

An external table in Hive maps to a column family in Cassandra. The STORED BY clause specifies the storage handler to use, which for Cassandra is org.apache.hadoop.hive.cassandra.CassandraStorageHandler. The WITH SERDEPROPERTIES clause specifies the properties used when serializing/deserializing data passed between the Hive table and Cassandra. The TBLPROPERTIES clause specifies CassandraFS and MapReduce properties for the table. For example:

hive> CREATE EXTERNAL TABLE Users(userid string, name string,
      email string, phone string)
      STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler'
      WITH SERDEPROPERTIES ( "cassandra.columns.mapping" = ":key,user_name,primary_email,home_phone")
      TBLPROPERTIES ( "cassandra.range.size" = "100",
                      "cassandra.slice.predicate.size" = "100" );

For static Cassandra column families that model objects (such as users), mapping them to a relational structure is pretty straightforward. In the example above, the first column of the Hive table (userid) maps to the row key in Cassandra. The row key in Cassandra is similar to a PRIMARY KEY in a relational table, and should be the first column in your Hive table. If you know what the column names are in Cassandra, you can map the Hive column names to the Cassandra column names as shown above.

However, for dynamic column families (such as time series data), all rows likely have a different set of columns, and in most cases you do not know what the column names are. To convert this type of column family to a Hive table, you would convert a wide row in Cassandra to a collection of short rows in Hive using a special set of column names (row_key, column_name, value). For example:

hive> CREATE EXTERNAL TABLE PortfolioDemo.Stocks
      (row_key string, column_name string,  value string)
      STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler';

Optionally, you can add a WITH SERDEPROPERTIES clause to map meaningful column names in Hive to the Cassandra row key, column names and column values. For example:

hive> CREATE EXTERNAL TABLE PortfolioDemo.PortfolioStocks
     (portfolio string, ticker string, number_shares string)
     STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler'
      WITH SERDEPROPERTIES ("cassandra.columns.mapping" = ":key,:column,:value" );

Using cassandra.columns.mapping, you can use a mapping of meaningful column names you assign in the Hive table to Cassandra row key, column/subcolumn names and column/subcolumn values. In the mapping, :key is a special name reserved for the column family row key, :column for column names, :subcolumn for subcolumn names (in super column families), and :value for column (or subcolumn) values. If you do not provide a mapping, then the first column of the Hive table is assumed to be the row key of the corresponding Cassandra column family.

Once you have defined your external tables in Hive, you should be able to do a SELECT to see the data stored in them. For example:

hive> SELECT * FROM PortfolioDemo.Stocks;

Any other query besides a SELECT * in Hive will run as a MapReduce job.