2.06 - Configuring a Non-Native Table for a Hive-to-TargetConnector

2.06 - Configuring a Non-Native Table for a Hive-to-TargetConnector - Teradata QueryGrid

Teradata® QueryGrid™ Installation and User Guide

Product

Teradata QueryGrid

Release Number

2.06

Published

September 2018

Language

English (United States)

Last Update

2018-11-26

dita:mapPath

blo1527621308305.ditamap

dita:ditavalPath

ft:empty

The Hive Storage Handlers framework is used for the Teradata QueryGrid Hive initiator connector. Storage handlers allow Hive to access data stored on other systems. A Hive table created using a storage handler is referred to as a non-native table.

Hive storage handlers operate at the table level, so one non-native table must be created for each remote table, unlike Teradata Foreign Servers and Presto Catalogs that operate at the database level.

The following steps provide an example of configuring a non-native table in order to use it with a Hive-to-TargetConnector (where TargetConnector is any type of target connector).

Set the link properties for the Hive-to-Teradata link in the QueryGrid portlet. See Hive Connector and Link Properties.
Log on to a Hive client using Beeline or Hive CLI, and add hive-qginitiator and log4j JARs to the class path:
```
add jar /opt/teradata/tdqg/connector/tdqg-hive-connector/version/lib/hive-loaderfactory-version.jar;
add jar /opt/teradata/tdqg/connector/tddqg-hive-connector/version/lib/log4j-api-2.7.jar; 
add jar /opt/teradata/tdqg/connector/tdqg-hive-connector/version/lib/log4j-core-2.7.jar; 
```
Alternatively, you can add the JAR files to the class path by putting the above commands in a file and use the -i <filename> option when logging into the client from Beeline or Hive.

There are others ways to add JARs to Hive class path, one of which is by setting HIVE_AUX_JARS_PATH.

The last approach typically requires a Hive restart. If you are using a Cloudera implementation of Hadoop and Sentry is enabled, this may be the only option available for use because Sentry does not allow use of ADD JAR commands.
Use a Hive storage handler to create the non-native table. For example:
```
CREATE TABLE cardata_remote
ROW FORMAT SERDE 'com.teradata.querygrid.qgc.hive.QGSerDe'
STORED BY'com.teradata.querygrid.qgc.hive.QGStorageHandler'
TBLPROPERTIES (
"link"="hive_to_td_link",
"version"="active",
"table"="ut1.cardata");
```
There is no column definition in the CREATE TABLE statement because the column definition is inferred dynamically by the Serializer and Deserializer (SerDe).
The table property clause contains the fully-qualified name of the remote table; for example, database.schema.table, schema.table, and table. If any part is omitted, default values (specified in connector properties) are used.