Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
===== Erste Schritte mit Apache Hive  =====


Vorrausetzung:\\

Hadoop installiert und gestartet, siehe [[nosql:hadoop_first_setup|Eine Hadoop 2.4 Test Umgebung installieren und konfigurieren]]

==== Überblick Hive ====

Hive verwendet das HDFS von Hadoop für die Ablage der DB Dateien und das MapReduce Framework zum Aufsetzen der Jobs. 

Für das Data Dictionary / die Metadaten wird eine separate relationale Datenbank verwendet, die NICHT im HDFS liegt (Default ist eine Apache Derby DB).  Für größere Umgebungen ist eine  MySQL oder PostgreSQL / Oracle Datenbank empfohlen.


Per HiveQL Abfragesprache werden diese MapReduce Jobs gesteuert/gestartet.

Wichtiges Grundkonzept -  **Schema-on-Write**  versus **Schema-on-Read**

 * Schema-on-Write – Traditionelles Verhalten von Relationalen Datenbanken 
    * Die Daten werden bereits mit dem Insert  in das existierende Schema Format  abgelegt 
    * Traditionelles ETL bereitet die Daten so auf (wandelt um) das die Daten auch zum DB Schema passen
    * Enthalten die Quelldaten neue Informationen muss zuvor das Schema angepasst werden (Neue Spalten etc.)
   
**versus**

 * Schema-on- Read – Hadoop / Hive 
   * Daten werden im Original Format im HDFS abgelegt ohne verändert zu werden
   * ETL on the fly -Nur die Daten die der User anfordert, werden beim Lesen  in das gewünschte Schema Format über die Parser Angaben auf der Tabelle geparst und so dem User dann zur Verfügung gestellt 
   * Neue Daten können jederzeit mit den gespeichert werden und werden dann ausgewertet, wenn der Parser angepasst wird



Übersicht:

{{ :hadoop:hive_v01.png?500 | Hive Komponenten in der Übersicht}}


Neben den bekannten Tabellen Datentypen kann Hive auch komplexes Datentypen wie Record/Array/Map unterstützen


Es gibt zwei Tabellen Typen:

  * Managed Tables - Daten und Metadaten werden von Hive verwaltet
  * External Tables - Daten verbleiben im ursprünglichen Format im HDFS - in Hive werden nur die Metadaten verwaltet


Tabellen können partitioniert werden, Partitionen müssen aber beim Laden manuell angegeben werden

Ein Index auf die Daten kann erstellt werden, dazu stehen drei Index Arten zur Verfügung ( compact, bitmap und custom).



SQL Fähigkeiten in v13:
  * Select mit inner Join und left – right - full outer Join werden unterstützt
    * Die rechts stehende Tabelle ist die Treiber Tabelle, d.h.  das sollte auch die kleinere Tabelle im Join sein!
  * Subselect mit Alias und  korrelierte Subquieres
  * Nur nach Spalten in der Ergebnis Liste kann mit "Order By" sortiert werden
     * order by wird über nur einen Reducer im Job implementiert - Performance beachten
  * Gruppenfunktionen mit "group by" und "having" möglich
  * Insert nur als Bulck Operation möglich
  * Kein eigentliches DML auf Einzel Satz Ebene 
  * Java User Defined Function (UDF) können/müssen vom Anwender selbst erstellt werden
    * siehe hier ein Beispiel => http://blog.matthewrathbone.com/2013/08/10/guide-to-writing-hive-udfs.html



==== Grundinstallation des Hive ====

Ablauf:

  * Software laden - apache-hive-0.13.1-bin.tar.gz vom => https://hive.apache.org/downloads.html
  * Archive auf dem Zielserver entpacken
  * Starten


=== Software auf dem Zielsystem bereitstellen ===

Im ersten Schritt wird Hive auf dem gleichen Server wie Hadoop installiert, in diesem Beispiel wird dazu der gleiche User und die gleiche Umgebung wie zuvor für Hadoop verwendet.

Software auspacken und damit installieren:
<code bash>
# Auspacken
tar -xf apache-hive-0.13.1-bin.tar.gz -C /opt/hadoop/product


#Link anlegen um nicht bei jeder neuen Version alles Scripte/Umgebung anpassen zu müssen

ln -s /opt/hadoop/product/apache-hive-0.13.1-bin /opt/hadoop/product/hive

</code>


Umgebung setzen:
<code bash>

vi .bashrc

..
#HIVE Settings

export HIVE_HOME=/opt/hadoop/product/hive
..

#Add Path
export PATH=$HIVE_HOME/bin:$PATH

..

. .bashrc

</code>


Verzeichnisse für HIVE auf dem HDFS anlegen und Rechte vergeben:
<code bash>

hdfs dfs -mkdir    /tmp
hdfs dfs -mkdir -p /user/hive/warehouse


#Rechte vergeben

hdfs dfs -chmod g+w /tmp
hdfs dfs -chmod g+w /user/hive/warehouse


</code>

Der erste Start der Hive "SQL*Plus" Shell:
<code bash>

hive


# To get the running version, see the path of the library

hive> set system:sun.java.command;

system:sun.java.command=org.apache.hadoop.util.RunJar /opt/hadoop/product/hive/lib/hive-cli-0.13.1.jar org.apache.hadoop.hive.cli.CliDriver

hive>quit


</code>


== Eigenständige Metadaten Store anlegen ===

siehe => https://cwiki.apache.org/confluence/display/Hive/HiveDerbyServerMode


=== An einer expliziten Datenbank anmelden===

An eine nicht "default" Datenbank anmelden:
<code sql>
hive --database gpidb
</code>



====Anlegen einer Tabelle mit Standard SQL====

Eine Tabelle in Hive ist eine Datei auf dem HDFS.

Aber ungewohnt für Oracle DBA's, beim Anlegen der Tabelle wird definiert, wie die dazu passende Daten Datei aufgebaut sein sollte (Schema on Read Konzept).

In unsere Beispiel mit ',' getrennt und NewLine als Zeilenende.

<code sql>

#Tabelle anlegen
hive> CREATE TABLE ora_audit_data(id BIGINT
                          , message string
                          , audit_date BIGINT
                          , instance_name string) 
COMMENT 'Oracle Audit Data.'
ROW FORMAT 
DELIMITED FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
;

OK
Time taken: 1.463 seconds

#Was für Tabellen gibt es
hive> show tables;
OK
ora_audit_data
Time taken: 0.14 seconds, Fetched: 1 row(s)

#Beschreibe die Tabelle
hive> describe ora_audit_data;
OK
id                      bigint
message                 string
audit_date              bigint
instance_name           string
Time taken: 0.708 seconds, Fetched: 4 row(s)


# For more informatoins:
hive> describe extended ora_audit_data;

</code>
siehe => https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL


=== Daten in die Tabelle einlesen ===


Beim Testen fällt auf das zu Zeit die SQL Syntax "INSERT INTO tablename VALUES (x,y,z)" nicht unterstützt wird!


Daten direkt aus einer Datei in die Hive Table einlesen, die zuvor auf dem HDFS abgelegt wurden:
<code bash>

#Lade Datei in dem Format anlegen, wie beim Anlegen der Tabelle definiert wurden
vi audit_data.csv
1,Message 1,89898998,GPI

#Auf HDFS kopieren
hdfs dfs -copyFromLocal audit_data.csv /tmp

#Einlesen mit overwrite überschreibt!
hive>load data inpath '/tmp/audit_data.csv' overwrite into table ora_audit_data;
</code>

**Hinweis** => Das Original Data File wird beim Laden gelöscht! Auch wenn Fehler passieren!


Oder aus einer lokalen Quelle laden, dann entfällt das Kopieren auf das HDFS, mit dem Schlüsselwort **local inpath**:

<code sql>
load data local inpath '/opt/hadoop/external_data/audit_data.csv' into table ora_audit_data;
</code>




=== Ein "insert values into" simulieren===

Über eine Hilfstabelle, wie im Oracle die DUAL Tabelle, kann ein SQL so erzeugt werden, das mit den Ergebnis wiederum in die Zieltabelle geschrieben werden kann.

<code sql>
#Textdatei mit einer 1 als Inhalt anlegen
vi ~/dual.tab
1

#Table anlegen

CREATE TABLE dual(x TINYINT);

#eine 1 einfügen

Load data local inpath '/home/hadoop/dual.tab' overwrite into table dual;

#SQL zusammenstellen für ein Insert Kommando

INSERT INTO TABLE ora_audit_data
SELECT stack(1
            ,1,"TestDATA","999999","GPI"
) AS (id, message,audit_date,instance_name)
FROM dual
;

</code>

== Einlesen mit einer External Table ==

Beim Anlegen der externen Tabelle wird das DIRECTORY angeben unter dem die Daten ausgelesen werden!

Aus einer externen Tabelle kopieren:
<code sql>

# Externe Tabelle anlegen

CREATE external TABLE ext_ora_audit_data(id            BIGINT
                                       , message       STRING
                                       , audit_date    BIGINT
                                       , instance_name STRING) 
ROW FORMAT 
DELIMITED FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
STORED AS TEXTFILE 
location '/data/external_ora_audit';

hdfs dfs -ls  /data
drwxr-xr-x   - hadoop supergroup          0 2014-07-13 18:35 /data/external_ora_audit


# Copy the data to the external table directory:

hdfs dfs -put audit_data.csv /data/external_ora_audit

# Read the data into the table:

INSERT INTO TABLE ora_audit_data 
SELECT id, message , audit_date, instance_name 
  FROM ext_ora_audit_data 
 WHERE id is not null;

</code>

==== Partitionierte Tabellen====

Hive kann die Daten hinter der Tabellendefinition in eigene Dateien pro Partition auf splitten.

Die Spalte wird dann nicht mehr in der Spaltendefinition der Tabelle angeben!

Beispiel:
<code sql>
create table emp (
 empno   INT
....
)
partitioned by (deptno INT)
;
</code>

===Laden===

**Manuell**:
<code sql>
INSERT INTO TABLE emp  PARTITION (deptno= 10)
SELECT empno,..... deptno from emp_load;
</code>



**Dynamische Partitionierung**:
<code sql>

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;


INSERT INTO TABLE emp
SELECT empno,..... deptno from emp_load;

</code>

Achtung: Letzte Spalte wird für die Partitionierung verwendet, auch wenn diese NICHTS mit der gewünschten Partitionierung zu tun hat!

===Abfragen===

Im "hive.mapred.mode=strict" (default) führt eine Abfrage auf eine partitionierte Tabelle zu einem Fehler, falls keine Partition bzw. die partitionierte Spalte nicht angegeben wurde.

<code sql>

#Partitionen anzeigen lassen

show partitions emp;


set hive.mapred.mode=nonstrict;

select * from emp;


</code>


====Indexes====

Zurzeit werden die folgenden Indextypen unterstützt:

  * Compact - pro Spaltenwert wird eine Liste mit den Offsets in der Datendatei gespeichert
  * Bitmap  - klassischer Bitmap Index
  * Custom  - ein eigener Index Typ wird definiert


Ein Index wird angelegt und muss dann neu angelegt werden. 

Ändern sich die Daten muss der Index von Hand neu angelegt werden!

Beispiel: Einen Index vom Typ "Compact" definieren:
<code sql>
CREATE INDEX idx_emp_deptno ON TABLE emp (deptno)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
WITH DEFERRED REBUILD
IN TABLE idx_emp_deptno_tab;
</code>

Index anlegen:
<code sql>
ALTER INDEX idx_emp_deptno ON emp REBUILD;
</code>


====Explain Plan===

Anzeigen lassen wie die MapReduce Jobs ausgeführt werden würden:
<code sql>
explain select .....
</code>


==== Debugging ====

Schlägt etwas fehlt kann im  Hive Client  die Log  Eigenschaften eingestellt werden:

<code bash>

hive -hiveconf hive.root.logger=INFO,console


</code>






==== Hive Remote verwenden ====

Ziel: Hive Abfragen von einem remote Rechner auf eine Hadoop Cluster durchführen

Lösung: 

  * Hive JDBC/Thrift Server auf einem Knoten im der Hadoop Struktur starten
    * Starten mit: https://cwiki.apache.org/confluence/display/Hive/HiveServer
  * Eine "Hive Server Session" starten

JDBC mit Hive verwenden:

  * https://cwiki.apache.org/confluence/display/Hive/HiveJDBCInterface


=== Hive unter Windows 7 starten ===

**Client**:

Da Hive ein reines Client Programm ist, kann auch über Windows 7 die Abfrage gestartet werden. Zuvor muss aber auf dem Client eine komplette Hadoop Umgebung konfiguriert werden. Leider wird Hive aber unter Windows nativ nur in einer cygwin Umgebung unterstützt.

Siehe => [[nosql:hadoop_connect_from_windows|HDFS Files remote lesen - Mit MS Windows Clients mit Hadoop arbeiten]]

Die Hive Software in ein Verzeichnis (D:\entwicklung\libraries\apache-hive-0.13.1-bin) entpacken.




**Hive - Thrift Hive Server auf einem Hadoop cluster Knoten starten**:
<code bash>
 hive --service hiveserver -p 10000 -v
</code>
siehe => https://cwiki.apache.org/confluence/display/Hive/HiveServer

**Client**:

Remote an den Hive Server anmelden (in Cygwin Bash Console!):
<code bash>
$ ./hive -hiveconf hive.root.logger=INFO,console -h nosqldb01 -p 10000
</code>

Alternativ siehe auch hier: => http://www.javacodegeeks.com/2012/10/apache-hive-on-windows-in-6-easy-steps.html

==== Quellen ====

  * https://cwiki.apache.org/confluence/display/Hive/Tutorial
  * https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Cli


Hive Data Loading:

  * http://jayunit100.blogspot.de/2013/12/building-hive-table-over.html
  * http://www.slideshare.net/PrashantPandey37/hive-tutoloading-data-into-hive-tables#

Netz:
  * http://www.cool-bi.com/Articles/Hive.php
  * http://www.rittmanmead.com/2014/04/simple-data-manipulation-and-reporting-using-hive-impala-and-cdh5/