Hive 简明教程

$ cd Downloads/
$ ls
jdk-7u71-linux-x64.gz
$ tar zxf jdk-7u71-linux-x64.gz
$ ls
jdk1.7.0_71 jdk-7u71-linux-x64.gz

$ su
password:
# mv jdk1.7.0_71 /usr/local/
# exit

export JAVA_HOME=/usr/local/jdk1.7.0_71
export PATH=PATH:$JAVA_HOME/bin

export HADOOP_HOME=/usr/local/hadoop
export HADOOP_MAPRED_HOME=$HADOOP_HOME
export HADOOP_COMMON_HOME=$HADOOP_HOME
export HADOOP_HDFS_HOME=$HADOOP_HOME
export YARN_HOME=$HADOOP_HOME
export HADOOP_COMMON_LIB_NATIVE_DIR=$HADOOP_HOME/lib/native export
PATH=$PATH:$HADOOP_HOME/sbin:$HADOOP_HOME/bin

$ source ~/.bashrc

$ cd $HADOOP_HOME/etc/hadoop

export JAVA_HOME=/usr/local/jdk1.7.0_71

<configuration>

   <property>
      <name>fs.default.name</name>
      <value>hdfs://localhost:9000</value>
   </property>

</configuration>

dfs.replication (data replication value) = 1

(In the following path /hadoop/ is the user name.
hadoopinfra/hdfs/namenode is the directory created by hdfs file system.)

namenode path = //home/hadoop/hadoopinfra/hdfs/namenode

(hadoopinfra/hdfs/datanode is the directory created by hdfs file system.)
datanode path = //home/hadoop/hadoopinfra/hdfs/datanode

<configuration>

   <property>
      <name>dfs.replication</name>
      <value>1</value>
   </property>
   <property>
      <name>dfs.name.dir</name>
      <value>file:///home/hadoop/hadoopinfra/hdfs/namenode </value>
   </property>
   <property>
      <name>dfs.data.dir</name>
      <value>file:///home/hadoop/hadoopinfra/hdfs/datanode </value >
   </property>

</configuration>

<configuration>

   <property>
      <name>yarn.nodemanager.aux-services</name>
      <value>mapreduce_shuffle</value>
   </property>

</configuration>

$ cp mapred-site.xml.template mapred-site.xml

<configuration>

   <property>
      <name>mapreduce.framework.name</name>
      <value>yarn</value>
   </property>

</configuration>

$ cd ~
$ hdfs namenode -format

10/24/14 21:30:55 INFO namenode.NameNode: STARTUP_MSG:
/************************************************************
STARTUP_MSG: Starting NameNode
STARTUP_MSG: host = localhost/192.168.1.11
STARTUP_MSG: args = [-format]
STARTUP_MSG: version = 2.4.1
...
...
10/24/14 21:30:56 INFO common.Storage: Storage directory
/home/hadoop/hadoopinfra/hdfs/namenode has been successfully formatted.
10/24/14 21:30:56 INFO namenode.NNStorageRetentionManager: Going to
retain 1 images with txid >= 0
10/24/14 21:30:56 INFO util.ExitUtil: Exiting with status 0
10/24/14 21:30:56 INFO namenode.NameNode: SHUTDOWN_MSG:
/************************************************************
SHUTDOWN_MSG: Shutting down NameNode at localhost/192.168.1.11
 ************************************************************/

$ start-dfs.sh

10/24/14 21:37:56
Starting namenodes on [localhost]
localhost: starting namenode, logging to /home/hadoop/hadoop-2.4.1/logs/hadoop-hadoop-namenode-localhost.out
localhost: starting datanode, logging to /home/hadoop/hadoop-2.4.1/logs/hadoop-hadoop-datanode-localhost.out
Starting secondary namenodes [0.0.0.0]

$ start-yarn.sh

starting yarn daemons
starting resourcemanager, logging to /home/hadoop/hadoop-2.4.1/logs/yarn-hadoop-resourcemanager-localhost.out
localhost: starting nodemanager, logging to /home/hadoop/hadoop-2.4.1/logs/yarn-hadoop-nodemanager-localhost.out

http://localhost:50070/

http://localhost:8088/

$ cd Downloads
$ ls

apache-hive-0.14.0-bin.tar.gz

$ tar zxvf apache-hive-0.14.0-bin.tar.gz
$ ls

apache-hive-0.14.0-bin apache-hive-0.14.0-bin.tar.gz

$ su -
passwd:

# cd /home/user/Download
# mv apache-hive-0.14.0-bin /usr/local/hive
# exit

export HIVE_HOME=/usr/local/hive
export PATH=$PATH:$HIVE_HOME/bin
export CLASSPATH=$CLASSPATH:/usr/local/Hadoop/lib/*:.
export CLASSPATH=$CLASSPATH:/usr/local/hive/lib/*:.

$ source ~/.bashrc

$ cd ~
$ wget http://archive.apache.org/dist/db/derby/db-derby-10.4.2.0/db-derby-10.4.2.0-bin.tar.gz

$ ls

db-derby-10.4.2.0-bin.tar.gz

$ tar zxvf db-derby-10.4.2.0-bin.tar.gz
$ ls

db-derby-10.4.2.0-bin db-derby-10.4.2.0-bin.tar.gz

$ su -
passwd:
# cd /home/user
# mv db-derby-10.4.2.0-bin /usr/local/derby
# exit

export DERBY_HOME=/usr/local/derby
export PATH=$PATH:$DERBY_HOME/bin
Apache Hive
18
export CLASSPATH=$CLASSPATH:$DERBY_HOME/lib/derby.jar:$DERBY_HOME/lib/derbytools.jar

$ source ~/.bashrc

$ mkdir $DERBY_HOME/data

DECIMAL(precision, scale)
decimal(10,0)

UNIONTYPE<int, double, array<string>, struct<a:int,b:string>>

{0:1}
{1:2.0}
{2:["three","four"]}
{3:{"a":5,"b":"five"}}
{2:["six","seven"]}
{3:{"a":8,"b":"eight"}}
{0:9}
{1:10.0}

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet; 4. CREATE DATABASE
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveCreateDb {
   private static String driverName =
   "org.apache.hadoop.hive.jdbc.HiveDriver";
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName);
      // get connection
      Connection con = DriverManager.
      getConnection("jdbc:hive://localhost:10000/default", "", "");
      Statement stmt = con.createStatement();
      stmt.executeQuery("CREATE DATABASE userdb");
      System.out.println(“Database userdb created successfully.”);
      con.close();
   }
}

$ javac HiveCreateDb.java
$ java HiveCreateDb

Database userdb created successfully.

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager; 5. DROP DATABASE

public class HiveDropDb {
   private static String driverName =
   "org.apache.hadoop.hive.jdbc.HiveDriver";
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName);
      // get connection
      Connection con = DriverManager.
      getConnection("jdbc:hive://localhost:10000/default", "", "");
      Statement stmt = con.createStatement();
      stmt.executeQuery("DROP DATABASE userdb");
      System.out.println(“Drop userdb database successful.”);
      con.close();
   }
}

$ javac HiveDropDb.java
$ java HiveDropDb

Drop userdb database successful.

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name

[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[ROW FORMAT row_format]
[STORED AS file_format]

COMMENT ‘Employee details’
FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’
STORED IN TEXT FILE

hive> CREATE TABLE IF NOT EXISTS employee ( eid int, name String,
> salary String, destination String)
> COMMENT ‘Employee details’
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY ‘\t’
> LINES TERMINATED BY ‘\n’
> STORED AS TEXTFILE;

OK
Time taken: 5.905 seconds
hive>

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveCreateTable {
   private static String driverName =
   "org.apache.hadoop.hive.jdbc.HiveDriver";
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName);
      // get connection
      Connection con = DriverManager.
      getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      // create statement
      Statement stmt = con.createStatement();
      // execute statement
      stmt.executeQuery("CREATE TABLE IF NOT EXISTS "
      +" employee ( eid int, name String, "
      +" salary String, destignation String)"
      +" COMMENT ‘Employee details’"
      +" ROW FORMAT DELIMITED"
      +" FIELDS TERMINATED BY ‘\t’"
      +" LINES TERMINATED BY ‘\n’"
      +" STORED AS TEXTFILE;");
      System.out.println(“ Table employee created.”);
      con.close();
   }
}

$ javac HiveCreateDb.java
$ java HiveCreateDb

Table employee created.

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename
[PARTITION (partcol1=val1, partcol2=val2 ...)]

1201  Gopal       45000    Technical manager
1202  Manisha     45000    Proof reader
1203  Masthanvali 40000    Technical writer
1204  Krian       40000    Hr Admin
1205  Kranthi     30000    Op Admin

hive> LOAD DATA LOCAL INPATH '/home/user/sample.txt'
> OVERWRITE INTO TABLE employee;

OK
Time taken: 15.905 seconds
hive>

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveLoadData {
   private static String driverName =
   "org.apache.hadoop.hive.jdbc.HiveDriver";
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName);
      // get connection
      Connection con = DriverManager.
      getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      // create statement
      Statement stmt = con.createStatement();
      // execute statement
      stmt.executeQuery("LOAD DATA LOCAL INPATH '/home/user/sample.txt'"
      +"OVERWRITE INTO TABLE employee;");
      System.out.println("Load Data into employee successful");
      con.close();
   }
}

$ javac HiveLoadData.java
$ java HiveLoadData

Load Data into employee successful

ALTER TABLE name RENAME TO new_name
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
ALTER TABLE name DROP [COLUMN] column_name
ALTER TABLE name CHANGE column_name new_name new_type
ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveAlterRenameTo {
   private static String driverName =
   "org.apache.hadoop.hive.jdbc.HiveDriver";
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName);
      // get connection
      Connection con = DriverManager.
      getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      // create statement
      Statement stmt = con.createStatement();
      // execute statement
      stmt.executeQuery("ALTER TABLE employee RENAME TO emp;");
      System.out.println("Table Renamed Successfully");
      con.close();
   }
}

$ javac HiveAlterRenameTo.java
$ java HiveAlterRenameTo

Table renamed successfully.

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveAlterChangeColumn {
   private static String driverName =
   "org.apache.hadoop.hive.jdbc.HiveDriver";
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName);
      // get connection
      Connection con = DriverManager.
      getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      // create statement
      Statement stmt = con.createStatement();
      // execute statement
      stmt.executeQuery("ALTER TABLE employee CHANGE name ename String;");
      stmt.executeQuery("ALTER TABLE employee CHANGE salary salary Double;");
      System.out.println("Change column successful.");
      con.close();
   }
}

$ javac HiveAlterChangeColumn.java
$ java HiveAlterChangeColumn

Change column successful.

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveAlterAddColumn {
   private static String driverName =
   "org.apache.hadoop.hive.jdbc.HiveDriver";
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName);
      // get connection
      Connection con = DriverManager.
      getConnection("jdbc:hive://localhost:10000/userdb", "", "");
     // create statement
     Statement stmt = con.createStatement();
     // execute statement
     stmt.executeQuery("ALTER TABLE employee ADD COLUMNS "
     +" (dept STRING COMMENT 'Department name');");
     System.out.prinln("Add column successful.");
     con.close();
   }
}

$ javac HiveAlterAddColumn.java
$ java HiveAlterAddColumn

Add column successful.

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveAlterReplaceColumn {
   private static String driverName =
   "org.apache.hadoop.hive.jdbc.HiveDriver";
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName);
      // get connection
      Connection con = DriverManager.
      getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      // create statement
      Statement stmt = con.createStatement();
      // execute statement
      stmt.executeQuery("ALTER TABLE employee REPLACE COLUMNS "
      +" (eid INT empid Int,"
      +" ename STRING name String);");
      System.out.println(" Replace column successful");
      con.close();
   }
}

$ javac HiveAlterReplaceColumn.java
$ java HiveAlterReplaceColumn

Replace column successful.

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveDropTable {
   private static String driverName =
   "org.apache.hadoop.hive.jdbc.HiveDriver";
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName);
      // get connection
      Connection con = DriverManager.
      getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      // create statement
      Statement stmt = con.createStatement();
      // execute statement
      stmt.executeQuery("DROP TABLE IF EXISTS employee;");
     System.out.println("Drop table successful.");
     con.close();
   }
}

$ javac HiveDropTable.java
$ java HiveDropTable

Drop table successful

hive> SHOW TABLES;
emp
ok
Time taken: 2.1 seconds
hive>

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec
[LOCATION 'location1'] partition_spec [LOCATION 'location2'] ...;

partition_spec:
: (p_column = p_col_value, p_column = p_col_value, ...)

hive> ALTER TABLE employee
> ADD PARTITION (year=’2012’)
> location '/2012/part2012';

+-----+--------------+--------+---------------------------+------+
| Id  | Name         | Salary | Designation               | Dept |
+-----+--------------+------------------------------------+------+
|1201 | Gopal        | 45000  | Technical manager         | TP   |
|1202 | Manisha      | 45000  | Proofreader               | PR   |
|1203 | Masthanvali  | 40000  | Technical writer          | TP   |
|1204 | Krian        | 40000  | Hr Admin                  | HR   |
|1205 | Kranthi      | 30000  | Op Admin                  | Admin|
+-----+--------------+--------+---------------------------+------+

hive> SELECT * FROM employee WHERE Id=1205;

+-----+-----------+-----------+----------------------------------+
| ID  | Name      | Salary    | Designation              | Dept  |
+-----+---------------+-------+----------------------------------+
|1205 | Kranthi   | 30000     | Op Admin                 | Admin |
+-----+-----------+-----------+----------------------------------+

hive> SELECT * FROM employee WHERE Salary>=40000;

+-----+------------+--------+----------------------------+------+
| ID  | Name       | Salary | Designation                | Dept |
+-----+------------+--------+----------------------------+------+
|1201 | Gopal      | 45000  | Technical manager          | TP   |
|1202 | Manisha    | 45000  | Proofreader                | PR   |
|1203 | Masthanvali| 40000  | Technical writer           | TP   |
|1204 | Krian      | 40000  | Hr Admin                   | HR   |
+-----+------------+--------+----------------------------+------+

hive> SELECT 20+30 ADD FROM temp;

+--------+
|   ADD  |
+--------+
|   50   |
+--------+

hive> SELECT * FROM employee WHERE Salary>40000 && Dept=TP;

+------+--------------+-------------+-------------------+--------+
| ID   | Name         | Salary      | Designation       | Dept   |
+------+--------------+-------------+-------------------+--------+
|1201  | Gopal        | 45000       | Technical manager | TP     |
+------+--------------+-------------+-------------------+--------+

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveQLWhere {
   private static String driverName =
   "org.apache.hadoop.hive.jdbc.HiveDriver";
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName);
      // get connection
      Connection con = DriverManager.
      getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      // create statement
      Statement stmt = con.createStatement();
      // execute statement
      Resultset res = stmt.executeQuery("SELECT * FROM employee WHERE
      salary>30000;");
      System.out.println("Result:");
      System.out.println(" ID \t Name \t Salary \t Designation \t Dept ");
      while (res.next()) {
         System.out.println(res.getInt(1)+" "+ res.getString(2)+" "+
         res.getDouble(3)+" "+ res.getString(4)+" "+ res.getString(5));
      }
      con.close();
   }
}

$ javac HiveQLWhere.java
$ java HiveQLWhere

ID       Name           Salary      Designation          Dept
1201     Gopal          45000       Technical manager    TP
1202     Manisha        45000       Proofreader          PR
1203     Masthanvali    40000       Technical writer     TP
1204     Krian          40000       Hr Admin             HR

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveQLOrderBy {
   private static String driverName =
   "org.apache.hadoop.hive.jdbc.HiveDriver";
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName);
      // get connection
      Connection con = DriverManager.
      getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      // create statement
      Statement stmt = con.createStatement();
      // execute statement
      Resultset res = stmt.executeQuery("SELECT * FROM employee ORDER BY
      DEPT;");
      System.out.println(" ID \t Name \t Salary \t Designation \t Dept ");
      while (res.next()) {
         System.out.println(res.getInt(1)+" "+ res.getString(2)+" "+
         res.getDouble(3)+" "+ res.getString(4)+" "+ res.getString(5));
      }
      con.close();
   }
}

$ javac HiveQLOrderBy.java
$ java HiveQLOrderBy

ID       Name           Salary      Designation          Dept
1205     Kranthi        30000       Op Admin             Admin
1204     Krian          40000       Hr Admin             HR
1202     Manisha        45000       Proofreader          PR
1201     Gopal          45000       Technical manager    TP
1203     Masthanvali    40000       Technical writer     TP
1204     Krian          40000       Hr Admin             HR

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveQLGroupBy {
   private static String driverName =
   "org.apache.hadoop.hive.jdbc.HiveDriver";
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName);
      // get connection
      Connection con = DriverManager.
      getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      // create statement
      Statement stmt = con.createStatement();
      // execute statement
      Resultset res = stmt.executeQuery(“SELECT Dept,count(*) ”
      +“FROM employee GROUP BY DEPT; ”);
      System.out.println(" Dept \t count(*)");
      while (res.next()) {
         System.out.println(res.getString(1)+" "+ res.getInt(2));
      }
      con.close();
   }
}

$ javac HiveQLGroupBy.java
$ java HiveQLGroupBy

 Dept     Count(*)
 Admin       1
 PR          2
 TP          3

hive> SELECT round(2.6) from temp;

3.0

hive> SELECT floor(2.6) from temp;

2.0

hive> SELECT ceil(2.6) from temp;

3.0