1、hive Web接口的使用
1)在source中打出hive-hwi-1.1.0.war包
进入源码hwi/web执行打包 jar cvf hive-hwi-1.1.0.war ./*2)将hive-hwi-1.1.0.war拷贝到/home/Hadoop/hive/lib 目录下
将/home/Hadoop/java/jdk1.8.0_40/lib/tools.jar 拷贝到/home/Hadoop/hive/lib 目录下,如果不拷贝,将报错
3)修改hive-site.xml 增加如下配置
<propert> <name>hive.hwi.listen.host</name> <value>10.211.55.5</value> <description></description> </propert> <propert> <name>hive.hwi.listen.port</name> <value>7777</value> <description></description></propert><property> <name>hive.hwi.war.file</name> <value>lib/hive-hwi-1.1.0.war</value> <description>This is the WAR file with the jsp content for Hive Web Interface</description></property>4)启动并访问Web界面
cd $HIVE_HOME./bin/hive --service hwi5)访问地址:
http://10.211.55.5:7777/hwi
2、hive基本语法练习
数据来源是从之前的数据仓库项目中导出来数据,本实验有4 张表,2张表通过hive命令行创建,2张通过jdbc程序创建
hive语法不区分大小写,函数区分大小写。1)创建库
CREATE DATABASE RetailDW;
使用库
USE RetailDW;
2)创建表
CREATE EXTERNAL TABLE DemiClass (clscode STRING,cty_big STRING,cty_sml STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','LINES TERMINATED BY '\n';3)创建分区表
CREATE TABLE DemiDate (xsdate STRING,year STRING,month STRING,day STRING)PARTITIONED BY (ds STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','LINES TERMINATED BY '\n';4)删除表
DROP TABLE DemiClass;
5)显示所有表
SHOW TABLES;
SHOW TABLES '.*s';
6)删除列(用replace columns 来起到删除的作用)
ALTER TABLE DemiClass REPLACE COLUMNS (clscode STRING,cty_big STRING);
7)表添加一列
ALTER TABLE DemiClass ADD COLUMNS (cty_sml STRING);
ALTER TABLE DemiClass ADD COLUMNS (cty_sml STRING COMMENT 'small class type');
8)更改表名
ALTER TABLE DemiClass RENAME TO DemiClass1;
ALTER TABLE DemiClass1 RENAME TO DemiClass;9)清空表数据
INSERT OVERWRITE TABLE DemiClass_copy SELECT * FROM DemiClass_copy dc WHERE 1=0;
10)将本地文件中的数据加载到表中
LOAD DATA LOCAL INPATH '/home/Hadoop/Desktop/data/DemiClass.csv' OVERWRITE INTO TABLE DemiClass;
LOAD DATA LOCAL INPATH '/home/Hadoop/Desktop/data/DemiDate.csv' OVERWRITE INTO TABLE DemiDate PARTITION (ds='2014');
11)加载DFS数据 ,同时给定分区信息
LOAD DATA INPATH '/HDTest2/DemiClass.csv' OVERWRITE INTO TABLE DemiClass;
12)按先件查询
SELECT * FROM DemiClass dc WHERE dc.cty_big='化妆品' ;
13)将查询数据输出至HDFS目录
INSERT OVERWRITE DIRECTORY '/HDTest2/Hive_output' SELECT dc.* FROM DemiClass dc WHERE dc.cty_big='电器代销' ;
14)将查询结果输出至本地目录
INSERT OVERWRITE LOCAL DIRECTORY '/home/Hadoop/Desktop/Hive_output' SELECT dc.* FROM DemiClass dc WHERE dc.cty_big='电器代销' ;
OVERWRITE会将目录给清空,并替代成只有查询结果的目录(甚用OVERWRITE)
15)将一个表的查询结果插入另外一张表
INSERT OVERWRITE TABLE DemiClass_copy SELECT dc.* FROM DemiClass dc;
INSERT OVERWRITE TABLE DemiClass_copy SELECT dc.* FROM DemiClass dc WHERE dc.cty_big='钟表';INSERT OVERWRITE DIRECTORY '/HDTest2/Hive_output' SELECT dc.clscode,dc.cty_big FROM DemiClass dc WHERE dc.cty_big='钟表';INSERT OVERWRITE DIRECTORY '/HDTest2/Hive_output' SELECT count(1) FROM DemiClass dc WHERE dc.cty_big='钟表';INSERT OVERWRITE LOCAL DIRECTORY '/home/Hadoop/Desktop/Hive_output' SELECT count(1) FROM DemiClass dc WHERE dc.cty_big='钟表';
16)将一个表的统计结果插入另一个表中
CREATE TABLE DemiClass_GP_Result (CTY_BIG STRING,SUM INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','LINES TERMINATED BY '\n';FROM DemiClass dc INSERT OVERWRITE TABLE DemiClass_GP_Result SELECT dc.CTY_BIG, count(*) GROUP BY dc.CTY_BIG;
17)将一个表中的数据插入多个表中
FROM DemiClass dc
INSERT OVERWRITE TABLE DemiClass_GP_Result SELECT dc.CTY_BIG, count(*) GROUP BY dc.CTY_BIGINSERT OVERWRITE TABLE DemiClass_copy SELECT dc.* WHERE dc.cty_big='钟表';18)修改列
ALERT TABLE table_name [COLUMN]
col_old_name col_new_name column_type [comment col_comment] [first|after column_name]eg:
修改列名
ALTER TABLE DemiClass CHANGE CTY_BIG ctyBig STRING;ALTER TABLE DemiClass CHANGE ctyBig cty_big STRING;改变列的位置ALTER TABLE DemiClass CHANGE cty_big ctyBig STRING after cty_sml;ALTER TABLE DemiClass CHANGE ctyBig cty_big STRING frist ;
19)增加表信息Alter Table Properties
ALTER TABLE table_name SET TBLPROPERTIES table_properties
table_properties: : (property_name = property_value, property_name = property_value, ... )用户可以用这个命令向表中增加 metadata,目前 last_modified_user,last_modified_time 属性都是由 Hive 自动管理的。用户可以向列表中增加自己的属性。可以使用 DESCRIBE EXTENDED TABLE 来获得这些信息。
20)Add Serde Properties
ALTER TABLE table_name
SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties]ALTER TABLE table_name SET SERDEPROPERTIES serde_propertiesserde_properties: : (property_name = property_value, property_name = property_value, ... )这个命令允许用户向 SerDe 对象增加用户定义的元数据。Hive 为了序列化和反序列化数据,将会初始化 SerDe 属性,并将属性传给表的 SerDe。如此,用户可以为自定义的 SerDe 存储属性。21)Alter Table File Format and Organization
ALTER TABLE table_name SET FILEFORMAT file_format
ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)] INTO num_buckets BUCKETS这个命令修改了表的物理存储属性。22) Partitions
增加语法:
ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location1'] partition_spec [LOCATION 'location2'] ...partition_spec: (partition_col = partition_col_value, partition_col = partiton_col_value, ...)删除:用户可以用 ALTER TABLE DROP PARTITION 来删除分区。分区的元数据和数据将被一并删除。ALTER TABLE table_name DROP [IF EXISTS] partition_spec, partition_spec,...eg:ALTER TABLE page_view DROP PARTITION (dt='2008-08-08', country='us');