<commit_before>[[hive]]
== Apache Hive integration

[quote, Hive website]
____
http://hive.apache.org/[Hive] is a data warehouse system for Hadoop that facilitates easy data summarization, ad-hoc queries, and the analysis of large datasets stored in Hadoop compatible file systems. 
____

Hive abstracts Hadoop by abstracting it through SQL-like language, called HiveQL so that users can apply data defining and manipulating operations to it, just like with SQL. In Hive data set are https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-DDLOperations[defined] through 'tables' (that expose type information) in which data can be https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-DMLOperations[loaded], https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-SQLOperations[selected and transformed] through built-in operators or custom/user defined functions (or https://cwiki.apache.org/confluence/display/Hive/OperatorsAndFunctions[UDF]s).

=== Installation

Make {eh} jar available in the Hive classpath. Depending on your options, there are various https://cwiki.apache.org/confluence/display/Hive/HivePlugins#HivePlugins-DeployingjarsforUserDefinedFunctionsandUserDefinedSerDes[ways] to achieve that. Use https://cwiki.apache.org/Hive/languagemanual-cli.html#LanguageManualCli-HiveResources[ADD] command to add files, jars (what we want) or archives to the classpath:

[source]
----
ADD /path/elasticsearch-hadoop.jar;
----

NOTE: the command expects a proper URI that can be found either on the local file-system or remotely. Typically it's best to use a distributed file-system (like HDFS or Amazon S3) and use that since the script might be executed
on various machines.

As an alternative, one can use the command-line:

.CLI configuration
[source,bash]
----
$ bin/hive -hiveconf hive.aux.jars.path=/path/elasticsearch-hadoop.jar
----
or if the +hive-site.xml+ configuration can be modified, one can register additional jars through the +hive.aux.jars.path+ option (that accepts an URI as well):

.+hive-ste.xml+ configuration
[source,xml]
----
<property>
  <name>hive.aux.jars.path</name>
  <value>/path/elasticsearch-hadoop.jar</value>
  <description>A comma separated list (with no spaces) of the jar files</description>
</property>
----

[[type-conversion-hive]]
=== Type conversion

IMPORTANT: If automatic index creation is used, please review <<auto-mapping-type-loss,this>> section for more information.

Hive provides various https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types[types] for defining data and internally uses different implementations depending on the target environment (from JDK native types to binary-optimized ones). {es} integrates with all of them, including
and Serde2 http://hive.apache.org/docs/r0.11.0/api/index.html?org/apache/hadoop/hive/serde2/lazy/package-summary.html[lazy] and http://hive.apache.org/docs/r0.11.0/api/index.html?org/apache/hadoop/hive/serde2/lazybinary/package-summary.html[lazy binary]:

[cols="^,^",options="header"]

|===
| Hive type | {es} type

| +void+            | +null+
| +boolean+       	| +boolean+
| +tinyint+         | +byte+
| +smallint+		| +short+
| +int+             | +int+
| +bigint+          | +long+
| +double+          | +double+
| +float+           | +float+
| +string+          | +string+
| +binary+ 		    | +binary+
| +timestamp+       | +date+
| +struct+          | +map+
| +map+             | +map+
| +array+           | +array+
| +union+           | not supported yet

2+h| Available in Hive 0.11 or higher

| +decimal+		    | +string+

|===

NOTE: While {es} understands Hive types up to version 0.11, it is backwards compatible with Hive 0.9

=== Writing data to {es}

With {eh}, {es} becomes just an external https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTable[table] in which data can be loaded or read from:

[source,sql]
----
CREATE EXTERNAL TABLE artists (
    id      BIGINT,
    name    STRING,
    links   STRUCT<url:STRING, picture:STRING>)
STORED BY 'org.elasticsearch.hadoop.hive.ESStorageHandler'<1>
TBLPROPERTIES('es.resource' = 'radio/artists/'<2>);

-- insert data to Elasticsearch from another table called 'source'
INSERT OVERWRITE TABLE artists 
    SELECT NULL, s.name, named_struct('url', s.url, 'picture', s.picture) FROM source s;
----

<1> {es} Hive +StorageHandler+
<2> {es} resource (index and type) associated with the given storage

=== Reading data from {es}

Reading from {es} is strinkingly similar:

[source,sql]
----
CREATE EXTERNAL TABLE artists (
    id      BIGINT,
    name    STRING,
    links   STRUCT<url:STRING, picture:STRING>)
STORED BY 'org.elasticsearch.hadoop.hive.ESStorageHandler'<1>
TBLPROPERTIES('es.resource' = 'radio/artists/_search?q=me*'<2>);

-- stream data from Elasticsearch
SELECT * FROM artists;
----

<1> same {es} Hive +StorageHandler+
<2> {es} resource (in case of reading, a query) associated with the given storage
<commit_msg>add section on hive configuration + field mapping<commit_after>[[hive]]
== Apache Hive integration

[quote, Hive website]
____
http://hive.apache.org/[Hive] is a data warehouse system for Hadoop that facilitates easy data summarization, ad-hoc queries, and the analysis of large datasets stored in Hadoop compatible file systems. 
____

Hive abstracts Hadoop by abstracting it through SQL-like language, called HiveQL so that users can apply data defining and manipulating operations to it, just like with SQL. In Hive data set are https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-DDLOperations[defined] through 'tables' (that expose type information) in which data can be https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-DMLOperations[loaded], https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-SQLOperations[selected and transformed] through built-in operators or custom/user defined functions (or https://cwiki.apache.org/confluence/display/Hive/OperatorsAndFunctions[UDF]s).

=== Installation

Make {eh} jar available in the Hive classpath. Depending on your options, there are various https://cwiki.apache.org/confluence/display/Hive/HivePlugins#HivePlugins-DeployingjarsforUserDefinedFunctionsandUserDefinedSerDes[ways] to achieve that. Use https://cwiki.apache.org/Hive/languagemanual-cli.html#LanguageManualCli-HiveResources[ADD] command to add files, jars (what we want) or archives to the classpath:

[source]
----
ADD /path/elasticsearch-hadoop.jar;
----

NOTE: the command expects a proper URI that can be found either on the local file-system or remotely. Typically it's best to use a distributed file-system (like HDFS or Amazon S3) and use that since the script might be executed
on various machines.

As an alternative, one can use the command-line:

.CLI configuration
[source,bash]
----
$ bin/hive -hiveconf hive.aux.jars.path=/path/elasticsearch-hadoop.jar
----
or if the +hive-site.xml+ configuration can be modified, one can register additional jars through the +hive.aux.jars.path+ option (that accepts an URI as well):

.+hive-ste.xml+ configuration
[source,xml]
----
<property>
  <name>hive.aux.jars.path</name>
  <value>/path/elasticsearch-hadoop.jar</value>
  <description>A comma separated list (with no spaces) of the jar files</description>
</property>
----

[[hive-configuration]]
=== Configuration

When using Hive, one can use +TBLPROPERTIES+ to specify the <<configuration,configuration>> properties (as an alternative to Hadoop +Configuration+ object) when declaring the external table backed by {es}:

[source,sql]
----
CREATE EXTERNAL TABLE artists (...)
STORED BY 'org.elasticsearch.hadoop.hive.ESStorageHandler'
TBLPROPERTIES('es.resource' = 'radio/artists/',
              'es.index.auto.create' = 'false') <1>;
----

<1> {eh} settings

[[hive-alias]]
=== Mapping

By default, {eh} uses the Hive table schema to map the data in {es}, using both the field names and types in the process. There are cases however when the names in Hive cannot
be used with {es} (the field name can contain characters accepted by {es} but not by Hive). For such cases, one can use the +es.column.aliases+ setting which accepts a comma-separated list of names mapping in the following format: ++Hive field name+:++{es field name}++

To wit:

[source,sql]
----
CREATE EXTERNAL TABLE artists (...)
STORED BY 'org.elasticsearch.hadoop.hive.ESStorageHandler'
TBLPROPERTIES('es.resource' = 'radio/artists/',
            <1>'es.column.aliases' = 'date:@timestamp<2>, url:url_123<3>');
----

<1> name mapping for two fields
<2> Hive column +date+ mapped in {es} to +@timestamp+
<3> Hive column +url+ mapped in {es} to +url_123+

TIP: {es} accepts only lower-case field name and, as such, {eh} will always convert Hive column names to lower-case. This poses no issue as Hive is **case insensitive**
however it is recommended to use the default Hive style and use upper-case names only for Hive commands and avoid mixed-case names.

[[hive-type-conversion]]
=== Type conversion

IMPORTANT: If automatic index creation is used, please review <<auto-mapping-type-loss,this>> section for more information.

Hive provides various https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types[types] for defining data and internally uses different implementations depending on the target environment (from JDK native types to binary-optimized ones). {es} integrates with all of them, including
and Serde2 http://hive.apache.org/docs/r0.11.0/api/index.html?org/apache/hadoop/hive/serde2/lazy/package-summary.html[lazy] and http://hive.apache.org/docs/r0.11.0/api/index.html?org/apache/hadoop/hive/serde2/lazybinary/package-summary.html[lazy binary]:

[cols="^,^",options="header"]

|===
| Hive type | {es} type

| +void+            | +null+
| +boolean+         | +boolean+
| +tinyint+         | +byte+
| +smallint+        | +short+
| +int+             | +int+
| +bigint+          | +long+
| +double+          | +double+
| +float+           | +float+
| +string+          | +string+
| +binary+          | +binary+
| +timestamp+       | +date+
| +struct+          | +map+
| +map+             | +map+
| +array+           | +array+
| +union+           | not supported yet

2+h| Available in Hive 0.11 or higher

| +decimal+         | +string+

|===

NOTE: While {es} understands Hive types up to version 0.11, it is backwards compatible with Hive 0.9

=== Writing data to {es}

With {eh}, {es} becomes just an external https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTable[table] in which data can be loaded or read from:

[source,sql]
----
CREATE EXTERNAL TABLE artists (
    id      BIGINT,
    name    STRING,
    links   STRUCT<url:STRING, picture:STRING>)
STORED BY 'org.elasticsearch.hadoop.hive.ESStorageHandler'<1>
TBLPROPERTIES('es.resource' = 'radio/artists/'<2>);

-- insert data to Elasticsearch from another table called 'source'
INSERT OVERWRITE TABLE artists 
    SELECT NULL, s.name, named_struct('url', s.url, 'picture', s.picture) FROM source s;
----

<1> {es} Hive +StorageHandler+
<2> {es} resource (index and type) associated with the given storage

=== Reading data from {es}

Reading from {es} is strikingly similar:

[source,sql]
----
CREATE EXTERNAL TABLE artists (
    id      BIGINT,
    name    STRING,
    links   STRUCT<url:STRING, picture:STRING>)
STORED BY 'org.elasticsearch.hadoop.hive.ESStorageHandler'<1>
TBLPROPERTIES('es.resource' = 'radio/artists/_search?q=me*'<2>);

-- stream data from Elasticsearch
SELECT * FROM artists;
----

<1> same {es} Hive +StorageHandler+
<2> {es} resource (in case of reading, a query) associated with the given storage

{'fraction_non_alphanumeric': 0.1211063829787234, 'fraction_numerical': 0.006127659574468085, 'mean_word_length': 4.095836947094536, 'pattern_counts': {'":': 0, '<': 53, '<?xml version=': 0, '>': 53, 'https://': 16, 'lorem ipsum': 0, 'www.': 0, 'xml': 6}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4812109', 'n_tokens_mistral': 3708, 'n_tokens_neox': 3602, 'n_words': 1166}