Hive 入门教程

2020-06-22 | 阅读：次

一、Hive是什么？

Hive是基于Hadoop的数据仓库

二、DDL操作

1、创建简单表

创建简单表

CREATE TABLE new_table (a INT, b STRING);

创建外部表

CREATE EXTERNAL TABLE new_table(
  a INT, 
  b BIGINT,
  c STRING, 
  d STRING,
  e STRING COMMENT 'IP Address of the User',
  f STRING COMMENT 'country of origination'
)COMMENT 'This is the staging page view table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
STORED AS TEXTFILELOCATION '<hdfs_location>';

创建分区表

CREATE TABLE new_table(
  viewTime INT, 
  userid BIGINT,
  page_url STRING, 
  referrer_url STRING,
  ip STRING COMMENT 'IP Address of the User'
)COMMENT 'This is the page view table'
PARTITIONED BY(date STRING, pos STRING)
ROW FORMAT DELIMITED ‘\t’
FIELDS TERMINATED BY '\n'
STORED AS SEQUENCEFILE;

2、删除表

删除 our_table 表

drop table if exists our_table;

三、修改表结构

1、基本语句

更改表名

ALTER TABLE our_table RENAME TO our_newtable;

添加列（默认添加在最后）

ALTER TABLE table_name ADD COLUMNS (new_col INT); #一次添加一列
ALTER TABLE table_name ADD COLUMNS (c1 INT,c2 STRING); #一次添加多个列
ALTER TABLE table_name ADD COLUMNS (new_col INT COMMENT 'a new column'); #添加一列并增加列字段注释

改变列名、类型、位置、注释

ALTER TABLE table_name CHANGE
  [CLOUMN] col_old_name col_new_name column_type
  [CONMMENT col_conmment]
  [FIRST|AFTER column_name]
  [CASCADE|RESTRICT];

2、示例

创建表test_change并进行列操作

CREATE TABLE test_change (a int, b int, c int);
 
// 首先将列a的名称更改为a1:
ALTER TABLE test_change CHANGE a a1 INT;
 
// 将列a1的名称更改为a2，将其数据类型更改为字符串，并将其放在列b之后:
ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;
// 注意:列位置更换后数据位置还是不动的,若想让数据跟着字段一起移动,需更新表数据,
// 使用insert overwrite table 从表中将移动之前对应的数据插入到移动之后对应的字段中

// 新表的结构是: 
// b int, a2 string, c int.
 
// 然后将c列的名称改为c1，并将其作为第一列:
ALTER TABLE test_change CHANGE c c1 INT FIRST;

// 此时新表的结构是: 
// c1 int, b int, a2 string.

// 添加、更新列
ALTER TABLE table_name 
  [PARTITION partition_spec]               
  ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
  [CASCADE|RESTRICT]                       
  
// 删除列
// 原有Hive表test_change中有a,b,c,d,e这几个字段
// 从test_change中删除“d”列:
ALTER TABLE test_change REPLACE COLUMNS (a int, b int,c string,e string);
// 将d和e两列一起删除:
ALTER TABLE test_change REPLACE COLUMNS (a int, b int,c string);

3、分区表操作

添加分区表语句（表已创建，在此基础上添加分区）

ALTER TABLE table_name ADD
partition_spec [ LOCATION 'location1' ]
partition_spec [ LOCATION 'location2' ] ...

删除分区语句

ALTER TABLE table_name DROP partition_spec, partition_spec,...

4、分区表操作示例

分区表操作

// 在day_table表基础上添加分区表
ALTER TABLE day_table ADD
PARTITION (dt='2008-08-08', hour='08')
location '/path/pv1.txt'

// 删除分区
ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='09');

四、DML操作：元数据存储

向数据表内加载文件

LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE our_table;

加载本地数据，同时给定分区信息

LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE our_table PARTITION (ds='2008-08-15');

加载DFS数据，同时给定分区信息

LOAD DATA INPATH '/user/myname/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');
// The above command will load data from an HDFS file/directory to the table. 
// Note that loading data from HDFS will result in moving the file/directory. As a result, the operation is almost instantaneous. 

五、DQL操作：数据查询SQL

1、基本的SELECT操作

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
[LIMIT number]

2、基于Partition的查询

//显示表分区：
show partitions table_name;

//根据表分区查询数据：
select * from table_name where partition_date='2018-04-10' ; 

3、Join

Note：Hive中Join的关联键必须在ON ()中指定，不能在WhERE中指定，否则就会先做笛卡尔积，再过滤。

Join分类：

内关联（JOIN）：只返回能关联上的结果。
左外关联（LEFT [OUTER] JOIN）：以LEFT [OUTER] JOIN关键字前面的表作为主表，和其他表进行关联，返回记录和主表的记录数一致，关联不上的字段置为NULL。是否指定OUTER关键字，貌似对查询结果无影响。
右外关联（RIGHT [OUTER] JOIN）：和左外关联相反，以RIGTH [OUTER] JOIN关键词后面的表作为主表，和前面的表做关联，返回记录数和主表一致，关联不上的字段为NULL。是否指定OUTER关键字，貌似对查询结果无影响。
全外关联（FULL [OUTER] JOIN）：以两个表的记录为基准，返回两个表的记录去重之和，关联不上的字段为NULL。是否指定OUTER关键字，貌似对查询结果无影响。
LEFT SEMI JOIN：以LEFT SEMI JOIN关键字前面的表为主表，返回主表的KEY也在副表中的记录。
笛卡尔积关联（CROSS JOIN）：返回两个表的笛卡尔积结果，不需要指定关联键。

4、示例

创建表 u_data

CREATE TABLE u_data (
  userid INT,
  movieid INT,
  rating INT,
  unixtime STRING
 )
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

加载数据到表中

LOAD DATA LOCAL INPATH 'ml-data/u.data' OVERWRITE INTO TABLE u_data;

统计数据总量

SELECT COUNT(1) FROM u_data;

六、延伸

1、动态分区

动态分区用来解决静态分区的多次重复操作

代码块

insert into our_table partition (dt='${now.datakey}')

Liming96

齐刘海的小柴