Hive查询

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 hwi

5）访问地址：
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 ' ';

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 ' ';

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 ' ';

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_BIG
INSERT 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_properties
serde_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');