一 。hive简介
hive是基于hadoop文件系统的大数据分析工具，能够轻松实现数据汇总 点对点查询 大批量数据分析等 使用传统的SQL语法 提供了UDF 用户自定义函数来分析统计数据。
hive的数据组成：
数据库（Databases） 类似于 mysql的数据库 用于将不同表进行区分的命名空间；
表(Table) ddl表预先定义列名和数据的格式 dml操作带有行和列的数据集；
分区(Partitions) 根据特定的列进行分区 数据会被写到文件系统不同的分区目录；
桶 Buckets (or Clusters) 定义hash的桶的个数和列 插入数据时根据列的值%桶的个数 决定文件写入哪个文件
hive架构：#aaaa
hive启动将表结构数据存储在derby或者其他数据库中，将数据行存储在hdfs文件系统中 客户端通过jdbc或者对应客户端发起查询时 hive将对应sql结构分析后 通过metastore（表结构）优化后转换成mapreduce任务执行 返回结果（图片摘自网络）
hive适用于少量用户统计查询数据 不适用于大批量用户请求
二。 hive安装
1》hive单机安装(参考官方wiki https://cwiki.apache.org/confluence/display/Hive/Home)
hive使用hadoop来存储数据必须拥有hadoop环境 使用关系型数据库存储表结构数据 hive内置了derby数据库 默认使用derby 需要搭建hadoop集群
环境参考http://blog.csdn.net/liaomin416100569/article/details/78360734环境
主机信息
/etc/hosts
192.168.58.147 node1
192.168.58.149 node2
192.168.58.150 node3
192.168.58.151 node4 节点信息
namenode
node1 nameserviceid:node1
node2 nameserviceid:node2
secondarynode
node1
node2
DataNodes
node2
node3
node4
Resource Manager
node1
NodeManger
node2
node3
node4
我这里是fendration所以 将node1的core-site.xml 默认dfs修改成本机非viewfs
<property>
<name>fs.defaultFS</name>
<value>hdfs://node1</value>
</property>hive单机还 安装在node1节点上
安装jdk1.7以上版本（安装过hadoop所以有了）
下载hive版本 apache-hive-2.2.0-bin.tar.gz （hive.apache.org）
解压hive安装包到： /soft/hive-2.2.0
/etc/profile添加
HIVE_HOME=/soft/hive-2.2.0
export HIVE_HOME
PATH=$PATH:${HIVE_HOME}/bin
export PATH执行命令 立刻生效
source /etc/profilenode1的hdfs上创建几个目录用于存储数据
hdfs上创建关键目录
hdfs dfs -mkdir /tmp
hdfs dfs -mkdir -p /user/hive/warehouse
hdfs dfs -chmod g+w /tmp
hdfs dfs -chmod g+w /user/hive/warehouse单机方式metadata文件只能同一时间一个用户同时访问 该方式不启用任何端口 连接就直接访问元数据和文件系统（hive要读取hadoop知道hdfs默认地址所以必须配置HADOOP_HOME）
内嵌数据库 derby管理元数据
将 $HIVE_HOME/conf/metastore_db 删除或者 备份为其他名字
mv $HIVE_HOME/conf/metastore_db $HIVE_HOME/conf/metastore_db_tmp可以通过 /conf目录下的hive-default.xml.template下查看到以下四个选项 分别是jdbc的url 驱动类 用户名和密码
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:derby:;databaseName=metastore_db;create=true</value> 默认使用derby管理元数据
<description>
JDBC connect string for a JDBC metastore.
To use SSL to encrypt/authenticate the connection, provide database-specific SSL flag in the connection URL.
For example, jdbc:postgresql://myhost/db?ssl=true for postgres database.
</description>
</property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>mine</value>
<description>password to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>APP</value>
<description>Username to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>org.apache.derby.jdbc.EmbeddedDriver</value>
<description>Driver class name for a JDBC metastore</description>
</property>如果有需要将元数据存储在其他的数据库中 可以将驱动jar包 置于 ${HIVE_HOME}/lib目录下 修改这四个值为对应数据库即可
hive支持的数据库通过以下方式查看
[root@node1 upgrade]# pwd
/soft/hive-2.2.0/scripts/metastore/upgrade
[root@node1 upgrade]# ll
total 24
drwxr-xr-x 2 root root 89 Oct 27 01:39 azuredb
drwxr-xr-x 2 root root 4096 Oct 27 01:39 derby
drwxr-xr-x 2 root root 4096 Oct 27 01:39 mssql
drwxr-xr-x 2 root root 4096 Oct 27 01:39 mysql
drwxr-xr-x 2 root root 4096 Oct 27 01:39 oracle
drwxr-xr-x 2 root root 4096 Oct 27 01:39 postgres 初始化 hive 使用内置的derby管理元数据 （也就是表结构数据）
schematool -dbType derby -initSchema 初始化成功使用hive命令 进入客户端控制
hive可以使用类似mysql的语法创建数据库 操作表等
如果过程中发现无法连接等问题 可以直接查看hive-log4j2.properties.template 查看日志文件在哪里
默认位置是：property.hive.log.dir = ${sys:java.io.tmpdir}/${sys:user.name}
也就是/tmp/root目录 下面有个文件是 hive.log
2》hive服务器（hiveserver2+beeline）安装
hive服务器模式是启动一个端口 对外发布 多个客户端通过jdbcapi 登录该端口进行统计查询 该模式适用于多用户模式
顺便这里演示一下 将元数据保存在mysql中
比如本机window mysql数据库 192.168.58.1 3306 root 123456
$HIVE_HOME/conf目录下新建文件 hive-site.xml 拷贝四要素 并修改为mysql
configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://192.168.58.1:3306/metadb</value>
<description>
JDBC connect string for a JDBC metastore.
To use SSL to encrypt/authenticate the connection, provide database-specific SSL flag in the connection URL.
For example, jdbc:postgresql://myhost/db?ssl=true for postgres database.
</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
<description>password to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
<description>Username to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
</configuration>拷贝mysql驱动包 到 ${HIVE_HOME}/lib目录下
58.1 mysql数据库上创建数据库 metadb
create database metadbnode1执行初始化metadb
[root@node1 lib]# schematool -dbType mysql -initSchema
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/soft/hive-2.2.0/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/soft/hadoop-2.7.4/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Metastore connection URL: jdbc:mysql://192.168.58.1:3306/metadb
Metastore Connection Driver : com.mysql.jdbc.Driver
Metastore connection User: root
Starting metastore schema initialization to 2.1.0
Initialization script hive-schema-2.1.0.mysql.sql
Initialization script completedwindow机器上看出 metadb数据库下是否多了一堆表
mysql> use metadb
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_metadb |
+---------------------------+
| aux_table |
| bucketing_cols |
| cds |
| columns_v2 |
| compaction_queue |
| completed_compactions |
| completed_txn_components |
| database_params |
| db_privs |
| dbs |
| delegation_tokens |
| func_ru |
| funcs |
| global_privs |
| hive_locks |
| idxs |
| index_params |
| key_constraints |
| master_keys |
| next_compaction_queue_id |
| next_lock_id |
| next_txn_id |
| notification_log |
| notification_sequence |
| nucleus_tables |
| part_col_privs |
| part_col_stats |
| part_privs |
| partition_events |
| partition_key_vals |
| partition_keys |
| partition_params |
| partitions |
| role_map |
| roles |
| sd_params |
| sds |
| sequence_table |
| serde_params |
| serdes |
| skewed_col_names |
| skewed_col_value_loc_map |
| skewed_string_list |
| skewed_string_list_values |
| skewed_values |
| sort_cols |
| tab_col_stats |
| table_params |
| tbl_col_privs |
| tbl_privs |
| tbls |
| txn_components |
| txns |
| type_fields |
| types |
| version |
| write_set |
+---------------------------+
57 rows in set (0.01 sec)确保当前机器存在hadoop安装 并且所有hadoop和yarn必须启动以下配置是hadoop的一种代理机制
修改hadoop的core-site.xml
<property>
<name>hadoop.proxyuser.用户名.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.用户名.groups</name>
<value>*</value>
</property>用户名可以随便取 *表示hadoop集群中所有机器的用户 可以使用自己指定的用户名伪装代理 就相当于我有了工牌 就不用报名字了
hive默认使用的是匿名账号没有任何权限 所有连接时 配置一个代理用户 -n 代理用户 就可以访问hadoop下所有文件了 代理
用户 相关 参考（http://hadoop.apache.org/docs/r2.7.3/hadoop-project-dist/hadoop-common/Superusers.html）
我假设hive连接是 root
<property>
<name>hadoop.proxyuser.root.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.root.groups</name>
<value>*</value>
</property>node1（58.147）启动服务
hiveserver2 默认启动端口10000(用于jdbc连接) 10002是web监控界面
其他机器上也安装一个hive
beeline -u jdbc:hive2://192.168.58.147:10000 -n root 使用root用户连接
创建表 查询测试注意 yarn和hadoop一定要全部启动 （jps查看） 因为测试插入数据 调用mapreduce
客户端测试
[root@node2 bin]# beeline -u jdbc:hive2://192.168.58.147:10000 -n root
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/soft/hive-2.2.0/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/soft/hadoop-2.7.4/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Connecting to jdbc:hive2://192.168.58.147:10000
Connected to: Apache Hive (version 2.2.0)
Driver: Hive JDBC (version 2.2.0)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 2.2.0 by Apache Hive
0: jdbc:hive2://192.168.58.147:10000> show databases;
+----------------+--+
| database_name |
+----------------+--+
| default |
| test |
+----------------+--+
2 rows selected (4.615 seconds)
0: jdbc:hive2://192.168.58.147:10000> drop database test;
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database test is not empty. One or more tables exist.) (state=08S01,code=1)
0: jdbc:hive2://192.168.58.147:10000> create database hello;
No rows affected (0.96 seconds)
0: jdbc:hive2://192.168.58.147:10000> use hello;
No rows affected (0.252 seconds)
0: jdbc:hive2://192.168.58.147:10000> create table tt(id int,name string);
No rows affected (1.403 seconds)
0: jdbc:hive2://192.168.58.147:10000> insert into tt values(1,'zs');
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
No rows affected (45.633 seconds)
0: jdbc:hive2://192.168.58.147:10000> select * from tt;
+--------+----------+--+
| tt.id | tt.name |
+--------+----------+--+
| 1 | zs |
+--------+----------+--+
1 row selected (1.515 seconds)在hiveserver2控制台 看到插入数据会启动mapreduce
测试成功。。。。
使用help查看所有beeline所有帮助
Beeline version 2.2.0 by Apache Hive
beeline> help
!addlocaldriverjar Add driver jar file in the beeline client side.
!addlocaldrivername Add driver name that needs to be supported in the beeline
client side.
!all Execute the specified SQL against all the current connections
!autocommit Set autocommit mode on or off
!batch Start or execute a batch of statements
!brief Set verbose mode off
!call Execute a callable statement
!close Close the current connection to the database
!closeall Close all current open connections
!columns List all the columns for the specified table
!commit Commit the current transaction (if autocommit is off)
!connect Open a new connection to the database.
!dbinfo Give metadata information about the database
!describe Describe a table
!dropall Drop all tables in the current database
!exportedkeys List all the exported keys for the specified table
!go Select the current connection
!help Print a summary of command usage
!history Display the command history
!importedkeys List all the imported keys for the specified table
!indexes List all the indexes for the specified table
!isolation Set the transaction isolation for this connection
!list List the current connections
!manual Display the BeeLine manual
!metadata Obtain metadata information
!nativesql Show the native SQL for the specified statement
!nullemptystring Set to true to get historic behavior of printing null as
empty string. Default is false.
!outputformat Set the output format for displaying results
(table,vertical,csv2,dsv,tsv2,xmlattrs,xmlelements, and
deprecated formats(csv, tsv))
!primarykeys List all the primary keys for the specified table
!procedures List all the procedures
!properties Connect to the database specified in the properties file(s)
!quit Exits the program
!reconnect Reconnect to the database
!record Record all output to the specified file
!rehash Fetch table and column names for command completion
!rollback Roll back the current transaction (if autocommit is off)
!run Run a script from the specified file
!save Save the current variabes and aliases
!scan Scan for installed JDBC drivers
!script Start saving a script to a file
!set Set a beeline variable
!sh Execute a shell command
!sql Execute a SQL command
!tables List all the tables in the database
!typeinfo Display the type map for the current connection
!verbose Set verbose mode on
Comments, bug reports, and patches go to ???
退出命令 !quit
3》hcatalog非交互客户端
首先沿用2章节 hiveserver2的metastore
hcatalog可以直接在命令行直接执行sql 主要用执行ddl语句
创建日志目录
mkdir -p /soft/hive-2.2.0/hcatalog/var/log启动服务
[root@node1 sbin]# ./hcat_server.sh start
Started metastore server init, testing if initialized correctly...
Metastore initialized successfully on port[9083].当前机器使用 命令
[root@node1 bin]# ./hcat -e "create table ggg(id int,name string)"使用hive查看是否创建表
[root@node1 log]# hive
which: no hbase in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/root/mongodb-linux-x86_64-rhel70-3.4.9/bin:/soft/hadoop-2.7.4/bin:/soft/hadoop-2.7.4/sbin:/soft/hive-2.2.0/bin)
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/soft/hive-2.2.0/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/soft/hadoop-2.7.4/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Logging initialized using configuration in jar:file:/soft/hive-2.2.0/lib/hive-common-2.2.0.jar!/hive-log4j2.properties Async: true
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
hive> show databases;
OK
default
hello
test
Time taken: 3.325 seconds, Fetched: 3 row(s)
hive> show tables;
OK
ggg
Time taken: 0.209 seconds, Fetched: 1 row(s)
hive> desc ggg;
OK
id int
name string
Time taken: 0.926 seconds, Fetched: 2 row(s)其他用法参考（https://cwiki.apache.org/confluence/display/Hive/HCatalog+UsingHCat）
hcatalog只支持单机访问 如果需要远程 必须搭配webhcat服务
webhcat支持rest风格url操作hive
具体参考
安装 https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-ConfigurationManagementOverview
介绍https://cwiki.apache.org/confluence/display/Hive/WebHCat+UsingWebHCat