Hadoop学习（5）——Hive SQL(2)语法

最新推荐文章于 2023-12-23 21:07:58 发布

Hey 锡瑞

最新推荐文章于 2023-12-23 21:07:58 发布

阅读量567

点赞数

分类专栏： Hadoop

原文链接：https://blog.csdn.net/RuiKe1400360107/article/details/107106148

版权

Hadoop 专栏收录该内容

10 篇文章 3 订阅

订阅专栏

一、基础命令

1.1、数据库操作

　　show databases; # 查看某个数据库
　　use 数据库; # 进入某个数据库
　　show tables; # 展示所有表
　　desc 表名; # 显示表结构
　　show partitions 表名; # 显示表名分区
　　show create table_name; # 显示创建表的结构

1.2、表结构修改

　　use xxdb; create table xxx; # 内部表
　　create table xxx like xxx; # 创建一个表，结构与其他一样
　　use xxdb; create external table xxx; # 外部表
　　use xxdb; create external table xxx (l int) partitoned by (d string); # 分区表
　　alter table table_name set TBLPROPROTIES ('EXTERNAL'='TRUE'); # 内部表转外部表
　　alter table table_name set TBLPROPROTIES ('EXTERNAL'='FALSE');# 外部表转内部表

1.3、字段类型

　　基本类型： tinyint, smallint, int, bigint, float, decimal, boolean, string
　　复合类型：struct, array, map

二、DDL（创建表结构）

注意："[ ]"括起来的代表我们可以写也可以不写的语句

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]

IF NOT EXIST（忽略异常）：创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常，用户可以用 IF NOT EXIST 选项来忽略这个异常；
EXTERNAL：关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）

LIKE 允许用户复制现有的表结构，但是不复制数据
COMMENT可以为表与字段增加描述
ROW FORMAT 设置行数据分割格式

name string,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';

load data local inpath '/root/data/data' into table psn;

create table psn2
(
id int,
name string,
likes array<string>,
address map<string,string>
)

create table psn3
(
id int,
name string,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by '\001'
collection items terminated by '\002'
map keys terminated by '\003';

外部表
create external table psn4
(
id int,
name string,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
location '/usr/';

内部表与外部表的区别：

创建简单表：

CREATE TABLE person(name STRING,age INT);

创建外部表:

CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT 'IP Address of the User',
country STRING COMMENT 'country of origination')
COMMENT '这里写表的描述信息'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
STORED AS TEXTFILE
LOCATION '<hdfs_location>';

创建分区表：

CREATE TABLE par_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;

创建分桶表：

CREATE TABLE par_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)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED '\t'
FIELDS TERMINATED BY '\n'
STORED AS SEQUENCEFILE;

创建带索引字段的表：

CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (dindex STRING);

复制一个空表：

CREATE TABLE empty_key_value_store
LIKE key_value_store;

表中添加一个字段：

ALTER TABLE pokes ADD COLUMNS (new_col INT);

添加一个字段并为其添加注释：

ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');

删除列：

ALTER TABLE test REPLACE COLUMNS(id BIGINT, name STRING);

更改表名：

ALTER TABLE events RENAME TO new_events;

增加、删除分区：

--增加：
ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...
partition_spec:
: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)
   
--删除：
ALTER TABLE table_name DROP partition_spec, partition_spec,...

改变表的文件格式与组织：

ALTER TABLE table_name SET FILEFORMAT file_format

ALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets BUCKETS --这个命令修改了表的物理存储属性

创建和删除视图：

--创建视图：
CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...) ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, ...)] AS SELECT；
 
--删除视图：
DROP VIEW view_name；

举个例子：间普通表和分区表：

CREATE TABLE `smart_test.user_group_iop_label`( 
  `mbl_no` string,                                              
   `reponse` string,                                             
   `create_time` string,                                         
  `user_group_id` string,                                       
   `id` string)                                                  
 PARTITIONED BY (                                                
   `p_date_cd` string)                                           
 ROW FORMAT DELIMITED                                            
   FIELDS TERMINATED BY ','                                      
   LINES TERMINATED BY '\n'                                      
;
CREATE TABLE `smart_test.user_group_iop_label`( 
  `mbl_no` string,                                              
   `reponse` string,                                             
   `create_time` string,                                         
  `user_group_id` string,                                       
   `id` string,
   `p_date_cd` string);    
insert into table user_group_iop_label values('1','20','20200918','11','111','20200916');
insert into table user_group_iop_label values('2','20','20200918','22','112','20200917');
insert into table user_group_iop_label values('3','30','20200918','33','113','20200918');
insert into table user_group_iop_label values('4','30','20200918','33','114','20200918');
insert into table user_group_iop_label values('5','40','20200918','22','115','20200918');

三、DML（增删改）

数据库增删改三种操作，DML包括：INSERT插入、UPDATE新、DELETE删除

向数据表内加载文件：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

--load操作只是单纯的复制/移动操作，将数据文件移动到Hive表对应的位置。
--加载本地
LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;

--加载HDFS数据，同时给定分区信息
LOAD DATA INPATH '/user/myname/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');

将查询结果插入到Hive表：

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

--多插入模式：
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...

 
--自动分区模式
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

INSERT INTO：

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

insert overwrite和insert into的区别：

insert overwrite 会覆盖已经存在的数据，假如原始表使用overwrite 上述的数据，先现将原始表的数据remove，再插入新数据。
insert into 只是简单的插入，不考虑原始表的数据，直接追加到表中。最后表的数据是原始数据和新插入数据。

四、DQL（查询）

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]

使用ALL或者DISTINCT选项区分对重复记录的处理。默认是ALL，表示查询所有记录，DISTINCT表示去掉重复的记录
Where 条件类似我们传统SQL的where 条件
ORDER BY 全局排序，只有一个Reduce任务
SORT BY 只在本机做排序
LIMIT限制输出的个数和输出起始位置

将查询数据输出至目录：

INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHERE a.ds='<DATE>';

将查询结果输出至本地目录：

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;

将一个表的结果插入到另一个表：

FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(1) WHERE a.foo > 0 GROUP BY a.bar;

INSERT OVERWRITE TABLE events SELECT a.bar, count(1) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;

JOIN

FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar) INSERT OVERWRITE TABLE events SELECT t1.bar, t1.foo, t2.foo;

将多表数据插入到同一表中：

FROM src
INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100
INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200
INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;

join查询：

Hive 只支持等值连接（equality joins）、外连接（outer joins）和（left semi joins）。Hive 不支持所有非等值的连接（只支持等值连接），因为非等值连接非常难转化到 map/reduce 任务。

LEFT，RIGHT和FULL OUTER关键字用于处理join中空记录的情况
LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现
join 时，每次 map/reduce 任务的逻辑是这样的：reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统
实际应用过程中应尽量使用小表join大表

join查询时应注意的点：

--只支持等值连接
SELECT a.* FROM a JOIN b ON (a.id = b.id)   
SELECT a.* FROM a JOIN b
ON (a.id = b.id AND a.department = b.department)
  
--可以 join 多个表
SELECT a.val, b.val, c.val FROM a JOIN b
ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
   
--如果join中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务

LEFT，RIGHT和FULL OUTER关键字：

--左外连接
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

--右外链接
SELECT a.val, b.val FROM a RIGHT OUTER JOIN b ON (a.key=b.key)

--满外连接
SELECT a.val, b.val FROM a FULL OUTER JOIN b ON (a.key=b.key)

LEFT SEMI JOIN关键字：

LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行

SELECT a.key, a.value
FROM a
WHERE a.key in
(SELECT b.key
FROM B);
--可以被写为：
SELECT a.key, a.val
FROM a LEFT SEMI JOIN b on (a.key = b.key)

UNION 与 UNION ALL：

--用来合并多个select的查询结果，需要保证select中字段须一致
select_statement UNION ALL select_statement UNION ALL select_statement ...

--UNION 和 UNION ALL的区别
--UNION只会查询到两个表中不同的数据，相同的部分不会被查出
--UNION ALL会把两个表的所有数据都查询出