碉堡了！假装内讧？史上最牛的任意球配合
分享这个视频的人喜欢
热门视频推荐
热门日志推荐
同类视频推荐
北京千橡网景科技发展有限公司：
文网文[号··京公网安备号·甲测资字
文化部监督电子邮箱：wlwh@··
文明办网文明上网举报电话： 举报邮箱：&&&&&&&&&&&&
请输入手机号，完成注册
请输入验证码
密码必须由6-20个字符组成
下载人人客户端
品评校花校草，体验校园广场& Hive安装及使用攻略
Hive安装及使用攻略
，介绍了如何整合虚拟化和Hadoop，让Hadoop集群跑在VPS虚拟主机上，通过云向用户提供存储和计算的服务。
现在硬件越来越便宜，一台非品牌服务器，2颗24核CPU，配48G内存，2T的硬盘，已经降到2万块人民币以下了。这种配置如果简单地放几个web应用，显然是奢侈的浪费。就算是用来实现单节点的hadoop，对计算资源浪费也是非常高的。对于这么高性能的计算机，如何有效利用计算资源，就成为成本控制的一项重要议题了。
通过虚拟化技术，我们可以将一台服务器，拆分成12台VPS，每台2核CPU，4G内存，40G硬盘，并且支持资源重新分配。多么伟大的技术啊！现在我们有了12个节点的hadoop集群， 让Hadoop跑在云端，让世界加速。
关于作者：
张丹(Conan), 程序员Java,R,PHP,Javascript
weibo：@Conan_Z
blog: http://blog.fens.me
转载请注明出处：
Hive是Hadoop一个程序接口，Hive让数据分析人员快速上手，Hive使用了类SQL的语法，Hive让JAVA的世界变得简单而轻巧，Hive让Hadoop普及到了程序员以外的人。
从Hive开始，让分析师们也能玩转大数据。
Hive的安装
Hive的基本使用:CRUD
Hive交互式模式
Hive查询HiveQL
Hive分区表
1. Hive的安装
装好hadoop的环境后，我们可以把Hive装在namenode机器上(c1)。
hadoop的环境，请参考：，
下载: hive-0.9.0.tar.gz
解压到： /home/cos/toolkit/hive-0.9.0
~ cd /home/cos/toolkit/hive-0.9.0
~ cp hive-default.xml.template hive-site.xml
~ cp hive-log4j.properties.template hive-log4j.properties
修改hive-site.xml配置文件
把Hive的元数据存储到MySQL中
~ vi conf/hive-site.xml
&property&
&name&javax.jdo.option.ConnectionURL&/name&
&value&jdbc:mysql://c1:3306/hive_metadata?createDatabaseIfNotExist=true&/value&
&description&JDBC connect string for a JDBC metastore&/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&
&property&
&name&javax.jdo.option.ConnectionUserName&/name&
&value&hive&/value&
&description&username to use against metastore database&/description&
&/property&
&property&
&name&javax.jdo.option.ConnectionPassword&/name&
&value&hive&/value&
&description&password to use against metastore database&/description&
&/property&
&property&
&name&hive.metastore.warehouse.dir&/name&
&value&/user/hive/warehouse&/value&
&description&location of default database for the warehouse&/description&
&/property&
修改hive-log4j.properties
#log4j.appender.EventCounter=org.apache.hadoop.metrics.jvm.EventCounter
log4j.appender.EventCounter=org.apache.hadoop.log.metrics.EventCounter
设置环境变量
~ sudo vi /etc/environment
PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/home/cos/toolkit/ant184/bin:/home/cos/toolkit/jdk16/bin:/home/cos/toolkit/maven3/bin:/home/cos/toolkit/hadoop-1.0.3/bin:/home/cos/toolkit/hive-0.9.0/bin"
JAVA_HOME=/home/cos/toolkit/jdk16
ANT_HOME=/home/cos/toolkit/ant184
MAVEN_HOME=/home/cos/toolkit/maven3
HADOOP_HOME=/home/cos/toolkit/hadoop-1.0.3
HIVE_HOME=/home/cos/toolkit/hive-0.9.0
CLASSPATH=/home/cos/toolkit/jdk16/lib/dt.jar:/home/cos/toolkit/jdk16/lib/tools.jar
在hdfs上面，创建目录
$HADOOP_HOME/bin/hadoop fs -mkidr /tmp
$HADOOP_HOME/bin/hadoop fs -mkidr /user/hive/warehouse
$HADOOP_HOME/bin/hadoop fs -chmod g+w /tmp
$HADOOP_HOME/bin/hadoop fs -chmod g+w /user/hive/warehouse
在MySQL中创建数据库
create database hive_
grant all on hive_metadata.* to hive@'%' identified by 'hive';
grant all on hive_metadata.* to hive@localhost identified by 'hive';
ALTER DATABASE hive_metadata CHARACTER SET latin1;
手动上传mysql的jdbc库到hive/lib
~ ls /home/cos/toolkit/hive-0.9.0/lib
mysql-connector-java-5.1.22-bin.jar
#启动metastore服务
~ bin/hive --service metastore &
Starting Hive Metastore Server
#启动hiveserver服务
~ bin/hive --service hiveserver &
Starting Hive Thrift Server
#启动hive客户端
~ bin/hive shell
Logging initialized using configuration in file:/root/hive-0.9.0/conf/hive-log4j.properties
Hive history file=/tmp/root/hive_job_log_root__.txt
hive> show tables
查询MySQL数据库中的元数据
~ mysql -uroot -p
mysql> use hive_
Database changed
+-------------------------+
| Tables_in_hive_metadata |
+-------------------------+
| BUCKETING_COLS
| COLUMNS_V2
| DATABASE_PARAMS
| INDEX_PARAMS
| PARTITIONS
| PARTITION_KEYS
| PARTITION_KEY_VALS
| PARTITION_PARAMS
| PART_COL_PRIVS
| PART_PRIVS
| SD_PARAMS
| SEQUENCE_TABLE
| SERDE_PARAMS
| SORT_COLS
| TABLE_PARAMS
| TBL_COL_PRIVS
| TBL_PRIVS
+-------------------------+
23 rows in set (0.00 sec)
Hive已经成功安装，下面是hive的使用攻略。
2. Hive的基本使用
1. 进入hive控制台
~ cd /home/cos/toolkit/hive-0.9.0
~ bin/hive shell
Logging initialized using configuration in file:/home/cos/toolkit/hive-0.9.0/conf/hive-log4j.properties
Hive history file=/tmp/cos/hive_job_log_cos__.txt
#创建数据(文本以tab分隔)
~ vi /home/cos/demo/t_hive.txt
hive& CREATE TABLE t_hive (a int, b int, c int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
Time taken: 0.489 seconds
#导入数据t_hive.txt到t_hive表
hive& LOAD DATA LOCAL INPATH '/home/cos/demo/t_hive.txt' OVERWRITE INTO TABLE t_
Copying data from file:/home/cos/demo/t_hive.txt
Copying file: file:/home/cos/demo/t_hive.txt
Loading data to table default.t_hive
Deleted hdfs://:9000/user/hive/warehouse/t_hive
Time taken: 0.397 seconds
查看表和数据
Time taken: 0.099 seconds
#正则匹配表名
hive&show tables '*t*';
Time taken: 0.065 seconds
#查看表数据
hive& select * from t_
Time taken: 0.264 seconds
#查看表结构
hive& desc t_
Time taken: 0.1 seconds
#增加一个字段
hive& ALTER TABLE t_hive ADD COLUMNS (new_col String);
Time taken: 0.186 seconds
hive& desc t_
new_col string
Time taken: 0.086 seconds
#重命令表名
~ ALTER TABLE t_hive RENAME TO t_
Time taken: 0.45 seconds
Time taken: 0.07 seconds
hive& DROP TABLE t_
Time taken: 0.767 seconds
Time taken: 0.064 seconds
3. Hive交互式模式
quit,exit:
退出交互式shell
reset: 重置配置为默认值
set &key&=&value& : 修改特定变量的值(如果变量名拼写错误，不会报错)
输出用户覆盖的hive配置变量
set -v : 输出所有Hadoop和Hive的配置变量
add FILE[S] *, add JAR[S] *, add ARCHIVE[S] * : 添加 一个或多个 file, jar, archives到分布式缓存
list FILE[S], list JAR[S], list ARCHIVE[S] : 输出已经添加到分布式缓存的资源。
list FILE[S] *, list JAR[S] *,list ARCHIVE[S] * : 检查给定的资源是否添加到分布式缓存
delete FILE[S] *,delete JAR[S] *,delete ARCHIVE[S] * : 从分布式缓存删除指定的资源
! &command& :
从Hive shell执行一个shell命令
dfs &dfs command& :
从Hive shell执行一个dfs命令
&query string& : 执行一个Hive 查询，然后输出结果到标准输出
source FILE &filepath&:
在CLI里执行一个hive脚本文件
4. 数据导入
还以刚才的t_hive为例。
#创建表结构
hive& CREATE TABLE t_hive (a int, b int, c int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
从操作本地文件系统加载数据(LOCAL)
hive& LOAD DATA LOCAL INPATH '/home/cos/demo/t_hive.txt' OVERWRITE INTO TABLE t_
Copying data from file:/home/cos/demo/t_hive.txt
Copying file: file:/home/cos/demo/t_hive.txt
Loading data to table default.t_hive
Deleted hdfs://:9000/user/hive/warehouse/t_hive
Time taken: 0.612 seconds
#在HDFS中查找刚刚导入的数据
~ hadoop fs -cat /user/hive/warehouse/t_hive/t_hive.txt
从HDFS加载数据
创建表t_hive2
hive& CREATE TABLE t_hive2 (a int, b int, c int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
#从HDFS加载数据
hive& LOAD DATA INPATH '/user/hive/warehouse/t_hive/t_hive.txt' OVERWRITE INTO TABLE t_hive2;
Loading data to table default.t_hive2
Deleted hdfs://:9000/user/hive/warehouse/t_hive2
Time taken: 0.325 seconds
hive& select * from t_hive2;
Time taken: 0.287 seconds
从其他表导入数据
hive& INSERT OVERWRITE TABLE t_hive2 SELECT * FROM t_
Total MapReduce jobs = 2
Launching Job 1 out of 2
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job__0002, Tracking URL = :50030/jobdetails.jsp?jobid=job__0002
Kill Command = /home/cos/toolkit/hadoop-1.0.3/libexec/../bin/hadoop job
-Dmapred.job.tracker=hdfs://:9001 -kill job__0002
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
10:32:41,979 Stage-1 map = 0%,
reduce = 0%
10:32:48,034 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 1.03 sec
10:32:49,050 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 1.03 sec
10:32:50,068 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 1.03 sec
10:32:51,082 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 1.03 sec
10:32:52,093 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 1.03 sec
10:32:53,102 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 1.03 sec
10:32:54,112 Stage-1 map = 100%,
reduce = 100%, Cumulative CPU 1.03 sec
MapReduce Total cumulative CPU time: 1 seconds 30 msec
Ended Job = job__0002
Ended Job = -, job is filtered out (removed at runtime).
Moving data to: hdfs://:9000/tmp/hive-cos/hive__10-32-31_323_4014154/-ext-10000
Loading data to table default.t_hive2
Deleted hdfs://:9000/user/hive/warehouse/t_hive2
Table default.t_hive2 stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 56, raw_data_size: 0]
7 Rows loaded to t_hive2
MapReduce Jobs Launched:
Job 0: Map: 1
Cumulative CPU: 1.03 sec
HDFS Read: 273 HDFS Write: 56 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 30 msec
Time taken: 23.227 seconds
hive& select * from t_hive2;
Time taken: 0.134 seconds
创建表并从其他表导入数据
hive& DROP TABLE t_
#创建表并从其他表导入数据
hive& CREATE TABLE t_hive AS SELECT * FROM t_hive2 ;
Total MapReduce jobs = 2
Launching Job 1 out of 2
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job__0003, Tracking URL = :50030/jobdetails.jsp?jobid=job__0003
Kill Command = /home/cos/toolkit/hadoop-1.0.3/libexec/../bin/hadoop job
-Dmapred.job.tracker=hdfs://:9001 -kill job__0003
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
10:36:48,612 Stage-1 map = 0%,
reduce = 0%
10:36:54,648 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 1.13 sec
10:36:55,657 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 1.13 sec
10:36:56,666 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 1.13 sec
10:36:57,673 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 1.13 sec
10:36:58,683 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 1.13 sec
10:36:59,691 Stage-1 map = 100%,
reduce = 100%, Cumulative CPU 1.13 sec
MapReduce Total cumulative CPU time: 1 seconds 130 msec
Ended Job = job__0003
Ended Job = -, job is filtered out (removed at runtime).
Moving data to: hdfs://:9000/tmp/hive-cos/hive__10-36-39_986_2540343/-ext-10001
Moving data to: hdfs://:9000/user/hive/warehouse/t_hive
Table default.t_hive stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 56, raw_data_size: 0]
7 Rows loaded to hdfs://:9000/tmp/hive-cos/hive__10-36-39_986_2540343/-ext-10000
MapReduce Jobs Launched:
Job 0: Map: 1
Cumulative CPU: 1.13 sec
HDFS Read: 272 HDFS Write: 56 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 130 msec
Time taken: 20.13 seconds
hive& select * from t_
Time taken: 0.109 seconds
仅复制表结构不导数据
hive& CREATE TABLE t_hive3 LIKE t_
hive& select * from t_hive3;
Time taken: 0.077 seconds
从MySQL数据库导入数据
我们将在介绍Sqoop时讲。
5. 数据导出
从HDFS复制到HDFS其他位置
~ hadoop fs -cp /user/hive/warehouse/t_hive /
~ hadoop fs -ls /t_hive
Found 1 items
-rw-r--r--
1 cos supergroup
10:41 /t_hive/
~ hadoop fs -cat /t_hive/
通过Hive导出到本地文件系统
hive& INSERT OVERWRITE LOCAL DIRECTORY '/tmp/t_hive' SELECT * FROM t_
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job__0005, Tracking URL = :50030/jobdetails.jsp?jobid=job__0005
Kill Command = /home/cos/toolkit/hadoop-1.0.3/libexec/../bin/hadoop job
-Dmapred.job.tracker=hdfs://:9001 -kill job__0005
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
10:46:24,774 Stage-1 map = 0%,
reduce = 0%
10:46:30,823 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 0.87 sec
10:46:31,833 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 0.87 sec
10:46:32,844 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 0.87 sec
10:46:33,856 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 0.87 sec
10:46:34,865 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 0.87 sec
10:46:35,873 Stage-1 map = 100%,
reduce = 0%, Cumulative CPU 0.87 sec
10:46:36,884 Stage-1 map = 100%,
reduce = 100%, Cumulative CPU 0.87 sec
MapReduce Total cumulative CPU time: 870 msec
Ended Job = job__0005
Copying data to local directory /tmp/t_hive
Copying data to local directory /tmp/t_hive
7 Rows loaded to /tmp/t_hive
MapReduce Jobs Launched:
Job 0: Map: 1
Cumulative CPU: 0.87 sec
HDFS Read: 271 HDFS Write: 56 SUCCESS
Total MapReduce CPU Time Spent: 870 msec
Time taken: 23.369 seconds
#查看本地操作系统
hive& ! cat /tmp/t_hive/;
hive& 1623
6. Hive查询HiveQL
注：以下代码将去掉map,reduce的日志输出部分。
普通查询：排序，列别名，嵌套子查询
hive& FROM (
SELECT b,c as c2 FROM t_hive
& SELECT t.b, t.c2
& WHERE b&2
& LIMIT 2;
连接查询：JOIN
hive& SELECT t1.a,t1.b,t2.a,t2.b
& FROM t_hive t1 JOIN t_hive2 t2 on t1.a=t2.a
& WHERE t1.c&10;
聚合查询1：count, avg
hive& SELECT count(*), avg(a) FROM t_
聚合查询2：count, distinct
hive& SELECT count(DISTINCT b) FROM t_
聚合查询3：GROUP BY, HAVING
hive& SELECT avg(a),b,sum(c) FROM t_hive GROUP BY b,c
hive& SELECT avg(a),b,sum(c) FROM t_hive GROUP BY b,c HAVING sum(c)&30
7. Hive视图
Hive视图和数据库视图的概念是一样的，我们还以t_hive为例。
hive& CREATE VIEW v_hive AS SELECT a,b FROM t_hive where c&30;
hive& select * from v_
hive& DROP VIEW IF EXISTS v_
Time taken: 0.495 seconds
8. Hive分区表
分区表是数据库的基本概念，但很多时候数据量不大，我们完全用不到分区表。Hive是一种OLAP数据仓库软件，涉及的数据量是非常大的，所以分区表在这个场景就显得非常重要！！
下面我们重新定义一个数据表结构：t_hft
~ vi /home/cos/demo/t_hft_.csv
~ vi /home/cos/demo/t_hft_.csv
创建数据表
DROP TABLE IF EXISTS t_
CREATE TABLE t_hft(
SecurityID STRING,
tradeTime STRING,
PreClosePx DOUBLE
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
创建分区数据表
根据业务：按天和股票ID进行分区设计
DROP TABLE IF EXISTS t_
CREATE TABLE t_hft(
SecurityID STRING,
tradeTime STRING,
PreClosePx DOUBLE
) PARTITIONED BY (tradeDate INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
hive& LOAD DATA LOCAL INPATH '/home/cos/demo/t_hft_.csv' OVERWRITE INTO TABLE t_hft PARTITION (tradeDate=);
Copying data from file:/home/cos/demo/t_hft_.csv
Copying file: file:/home/cos/demo/t_hft_.csv
Loading data to table default.t_hft partition (tradedate=)
hive& LOAD DATA LOCAL INPATH '/home/cos/demo/t_hft_.csv' OVERWRITE INTO TABLE t_hft PARTITION (tradeDate=);
Copying data from file:/home/cos/demo/t_hft_.csv
Copying file: file:/home/cos/demo/t_hft_.csv
Loading data to table default.t_hft partition (tradedate=)
查看分区表
hive& SHOW PARTITIONS t_
tradedate=
tradedate=
Time taken: 0.082 seconds
hive& select * from t_hft where securityid='000001';
hive& select * from t_hft where tradedate= and PreClosePx&9;
Hive基于使用完成，这些都是日常的操作。后面我会继续讲一下，HiveQL优化及Hive的运维。
转载请注明出处：
This entry was posted in ,
Pingback: ()
Pingback: ()
Pingback: ()
Designed by长河落月。的喜欢 | LOFTER（乐乎） - 每个人的理想国
LOFTER for ipad —— 记录生活，发现同好
长河落月。 的喜欢
&nbsp&nbsp被喜欢
&nbsp&nbsp被喜欢
{list posts as post}
{if post.type==1 || post.type == 5}
{if !!post.title}${post.title|escape}{/if}
{if !!post.digest}${post.digest}{/if}
{if post.type==2}
{if post.type == 3}
{if !!post.image}
{if post.type == 4}
{if !!post.image}
{list photos as photo}
{if photo_index==0}{break}{/if}
品牌${make||'-'}
型号${model||'-'}
焦距${focalLength||'-'}
光圈${apertureValue||'-'}
快门速度${exposureTime||'-'}
ISO${isoSpeedRatings||'-'}
曝光补偿${exposureBiasValue||'-'}
镜头${lens||'-'}
{if data.msgRank == 1}{/if}
{if data.askSetting == 1}{/if}
{if defined('posts')&&posts.length>0}
{list posts as post}
{if post_index < 3}
{if post.type == 1 || post.type == 5}
{if !!post.title}${post.title|escape}{/if}
{if !!post.digest}${post.digest}{/if}
{if post.type == 2}
{if post.type == 3}
{if post.type == 4}
{if drlist.length>0}
更多相似达人：
{list drlist as dr}{if drlist.length === 3 && dr_index === 0}、{/if}{if drlist.length === 3 && dr_index === 1}、{/if}{if drlist.length === 2 && dr_index === 0}、{/if}{/list}
暂无相似达人，
{if defined('posts')&&posts.length>0}
{list posts as post}
{if post.type == 2}
{if post.type == 3}
{if post.type == 4}
this.p={ dwrMethod:'queryLikePosts',fpost:'45d542_8998bff',userId:2676865,blogListLength:30};