Hive中常见Sql有哪些

这篇文章主要为大家展示了"Hive中常见Sql有哪些"，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下"Hive中常见Sql有哪些"这篇文章吧。

•数据库
show databases;
CREATE DATABASE IF NOT EXISTS test;
drop database test;
use test;
•建表
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]
•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常
•EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）
•LIKE 允许用户复制现有的表结构，但是不复制数据
•COMMENT可以为表与字段增加描述

•ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。
•STORED AS
SEQUENCEFILE
| TEXTFILE
| RCFILE
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。
•hive支持的字段类型
TINYINT
SMALLINT
INT
BIGINT
BOOLEAN
FLOAT
DOUBLE
STRING

•创建简单表
CREATE TABLE IF NOT EXISTS pokes (foo STRING, bar STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
•创建外部表
CREATE EXTERNAL TABLE pokes (foo STRING, bar STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/test/pokes';
•建分区表
CREATE TABLE IF NOT EXISTS invites (foo STRING, bar STRING)
PARTITIONED BY(d STRING,s STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
•建Bucket表
CREATE TABLE IF NOT EXISTS buckets (foo STRING, bar STRING)
CLUSTERED BY (foo) into 4 buckets
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
•复制一个空表
CREATE TABLE invites_copy LIKE invites;
•创建表并从其他表导入数据(mapreduce)
CREATE TABLE parts AS SELECT * FROM invites;
•hbase表
CREATE EXTERNAL TABLE workStatisticsNone (
id string,
num int
) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,f:c")
TBLPROPERTIES ("hbase.table.name" = "workStatisticsNone","hbase.mapred.output.outputtable" = "workStatisticsNone");

•删除表
drop table pokes;
drop table invites;
•修改表结构
•增加/替换/修改列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type[COMMENT col_comment], ...)
ALTER TABLE pokes ADD COLUMNS (d STRING COMMENT 'd comment');
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENTcol_comment] [FIRST|(AFTER column_name)]
alter table pokes change d s string comment 'change column name' first;

•更改表名：
ALTER TABLE pokes RENAME TO poke;
•修复表分区：
MSCK REPAIR TABLE invites;
ALTER TABLE invites RECOVER PARTITIONS;
•创建／删除视图
CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...) ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, ...)] AS SELECT
create view v_invites(foo,bar) as select foo,bar from invites;
DROP VIEW v_invites;
•显示命令
SHOW TABLES;
SHOW TABLES '.*s';（正则表达式）
desc pokes;
SHOW FUNCTIONS;
DESCRIBE FUNCTION ;
DESCRIBE FUNCTION EXTENDED ;
•加载数据
•Load data到指定的表
LOAD DATA LOCAL INPATH 'kv.txt' OVERWRITE INTO TABLE pokes;
LOAD DATA LOCAL INPATH 'kv1.txt' INTO TABLE pokes;
LOAD DATA INPATH '/test/kv.txt' INTO TABLE pokes;
LOAD DATA INPATH '/test/kv.txt' INTO TABLE pokes;
关键字[OVERWRITE]意思是是覆盖原表里的数据，不写则不会覆盖。
关键字[LOCAL]是指你加载文件的来源为本地文件，不写则为hdfs的文件。
•load到指定表的分区
LOAD DATA LOCAL INPATH 'kv.txt' OVERWRITE INTO TABLE invites PARTITION(d='1',s='1');
LOAD DATA LOCAL INPATH 'kv1.txt' INTO TABLE invites PARTITION(d='1',s='1');
LOAD DATA LOCAL INPATH 'kv.txt' OVERWRITE INTO TABLE invites PARTITION(d='1',s='2');
•查询结果导入hive
INSERT overwrite TABLE pokes SELECT foo,bar FROM invites; 覆盖相应目录下的文件
INSERT INTO TABLE pokes SELECT foo,bar FROM invites;
INSERT INTO TABLE invites_copy PARTITION(d='1',s='1') SELECT * FROM invites;
动态分区插入，默认关闭
set hive.exec.dynamic.partition.mode=nonstrict
INSERT INTO TABLE invites_copy PARTITION(d,s) SELECT * FROM invites;
•多插入模式
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...
•查询结果写入文件系统
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
insert overwrite local DIRECTORY 'test.txt' select * from invites_copy
•数据查询
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]

select * from invites limit 2,5;
ORDER BY与SORT BY的不同
•ORDER BY 全局排序，只有一个Reduce任务
•SORT BY 只在本机做排序
hive会根据distribute by后面列，根据reduce的个数进行数据分发，默认是采用hash算法
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能，但是排序只能是倒序排序
select * from invites where foo=1 or bar=2;
where 条件支持 AND,OR ,between,IN, NOT IN,EXIST,NOT EXIST

•JOIN
Hive 只支持等值连接（equality joins）、外连接（outer joins）和（left semi joins）。Hive 不支持所有非等值的连接，因为非等值连接非常难转化到 map/reduce 任务
•join on 属于 common join
最为普通的join策略，不受数据量的大小影响，也可以叫做reduce side join
•left semi joins
left semi join 则属于 map join（broadcast join）的一种变体,left semi join 是只传递表的 join key 给 map 阶段 , 如果 key 足够小还是执行 map join, 如果不是则还是 common join,代替in条件
select a.* from invites a left semi join invites_copy b on (a.bar=b.bar)
•Map Join
SELECT /*+ MAPJOIN(smalltable)*/ .key,value
FROM smalltable JOIN bigtable ON smalltable.key = bigtable.key
0.7之后，不需要/*+ MAPJOIN(smalltable)*/，这个计算是自动化的，自动判断哪个是小表，哪个是大表
set hive.auto.convert.join=true; # 是否自动转换为mapjoin
set hive.mapjoin.smalltable.filesize=300000000; # 小表的最大文件大小，默认为25000000，即25M
set hive.auto.convert.join.noconditionaltask=true; #是否将多个mapjoin合并为一个
set hive.auto.convert.join.noconditionaltask.size=300000000;
#多个mapjoin转换为1个时，所有小表的文件大小总和的最大值，例如，一个大表顺序关联3个小表a(10M), b(8M),c(12M)
FULL [OUTER] JOIN不会使用MapJoin优化
•Bucket Map Join
当连接的两个表的join key 就是bucket column 的时候
hive.optimize.bucketmapjoin= true

以上是"Hive中常见Sql有哪些"这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！