hive常用语法示例

2018-02-27 10:57:56来源:oschina作者:爱宝贝丶人点击

分享
1. 建表语句
create table page_view(
viewTime INT,
userId BIGINT,
pageUrl STRING,
refererUrl STRING,
ip STRING COMMENT 'ip address of user'
) COMMENT 'This is the page view table'
PARTITIONED BY (dt STRING, country STRING)
CLUSTERED BY (userId) SORTED BY (viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '/001'
LINES TERMINATED BY '/n'
COLLECTION ITEMS TERMINATED BY '/002'
MAP KEYS TERMINATED BY '/003'
STORED AS SEQUENCEFILE
LOCATION '';PATITIONED BY: 用于指定分区字段,每指定一个字段就表示基于该字段创建一个文件夹,如示例在一级目录中存在多个dt分区文件夹,在每个dt文件夹下又存在基于country分区的文件夹;
这里需要注意PARTITIONED BY后的字段不能在建表语句中出现,其中的字段会自动添加到表结构中;
CLUSTERED BY:兼具合并和排序的功能,合并指的是用于指定map端将数据处理之后传输到reduce端的数据合并方式,而排序指的是每个reduce中数据的排序方式方式,其只能为降序排序;
SORTED BY:指定在map端将数据传输到reduce端时每个reduce中数据的排序方式,默认是按照升序排序的。需要注意的是SORTED BY只保证每个reduce中的数据是按照指定字段排序的,而不保证reduce之间的数据是有序的;
ROW FORMAT DELIMITED:用于指定加载数据时数据的分隔方式。LIENS TERMINATED BY指定每一行数据以什么字符结尾;FIELDS TERMINATED BY指定了每个字段之间使用'/001'进行分隔;COLLECTION ITEMS TERMINATED BY指定字段数据为集合时其内部每个元素之间的分隔方式为'/002';MAP KEYS TERMINATED BY指定集合元素如果为MAP时,其键值对的分隔方式为'/003';
STORED AS: 用于指定加载方式,有TEXTFILE和SEQUENCEFILE两种值,TEXTFILE表示以纯文本形式加载,SEQUENCEFILE则表示以压缩文件形式加载;
LOACTION:用于指定初始数据的加载地址,该地址为HDFS文件系统中的文件。
2. 查看表
describe page_view;describe命令用于查看指定表的结构;
describe extended page_view;查看page_view的详细表结构
3. 删除表
drop table page_view;删除表page_view,该命令只会删除表的元数据和数据,如果指定的是external表(外部表),那么该命令是不会删除文件系统中的数据的。
4. 修改表
① 增加分区
alter table page_view add
partition (dt='2010-08-08', country='us') location '/path/to/us/part080808'
partition (dt='2010-08-09', country='us') location '/path/to/us/part080809';
② 删除分区
alter table page_view drop partition (dt='2018-08-08', country='us');
③ 重命名表名
alter table page_view rename to page_view_new;
④ 修改字段
alter table page_view change ip ip_address string after refererurl;修改字段格式为alter table table_name change column_name new_column_name column_type (first|after column_name);
alter table page_view add columns (name string comment 'view name');往page_view表中添加name字段,注意字段列表的括号不能省略。
5. 视图
① 创建视图
create view onion_referers (
url comment 'URL of Referring page'
) comment 'Referrers to the Onion website'
as
select distinct refererurl
from page_view
where pageurl='www.theonion.com';视图的查询语句中可以有order by和limit语句,该limit相当于一个全局的limit,如果对视图的查询中包含limit,那么其会受限于视图创建语句中的全局limit;
视图是只读的,不能用于LOAD/INSERT/ALTER;
创建视图时如果视图已经存在则会报错;
② 删除视图
drop view onion_referers;
6. 函数
① 创建函数
CREATE TEMPORARY FUNCTION function_name AS class_name;
② 删除函数
DROP TEMPORARY FUNCTION function_name
7. 展示描述语句
① 展示表
show tables "page*|view";最后的双引号中是一个描述性语句,类似于正则表达式,但只支持三种符号:*, |, [s]。 *分别表示任意数量的任意字符,|表示两者取其一即可,[s]表示可以包含该字符也可以不包含该字符。
show partitions page_view;显示page_view表中的所有分区信息。
show table extended like '*';查看符合指定表达式的表的信息;
show functions like '*'查看符合指定表达式的所有函数信息;
8. 加载数据
① load加载数据
hive> load data inpath '/user/eg2/list_male.txt' overwrite into table list partition (gender='male');
hive> load data inpath '/user/eg2/list_female.txt' overwrite into table list partition (gender='female');在load data后如果没有local关键字,那么默认后面的路径是hdfs中的路径,如果有local关键字,则指定的是本地机器路径;
overwrite关键字用于指定是否覆盖hive中同目录的文件,因为如果hive中已经有该路径的数据,那么加载数据时是会报错的,这是为了防止数据丢失,如果有overwrite,则会覆盖已有的数据;
在加载数据时需要注意,不同分区的数据需要分别加载,load命令其实只是简单的将数据文件复制到相应的分区目录下。
② insert加载数据
hive> insert into table list1 partition (gender='male') select name, birthday from list where gender='male';
hive> insert into table list1 partition (gender='female') select name, birthday from list where gender='female';在insert的时候需要注意,如果目标表有分区,那么需要指定当前插入的是哪个分区的数据;
在进行插入的时候,分区字段是不需要在后续的select中的,其会自动存储为partition后的字段指定的值,而后续的select语句也只需要包含其余的字段即可,如这里的select语句只包含了name和birthday字段。
③ insert导出数据
insert overwrite directory '/user/eg2/' select * from list;这里overwrite不能省略;
overwrite后可接local关键字,如果有该关键字,那么后面的路径则表示本地路径,否则表示HDFS文件系统上的路径。
9. SQL查询
SELECT [ALL|DISTINCT] select_expr, select_expr,...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[CLUSTER BY col_list|[DISTRIBUTE BY col_list][SORT BY col_list]]
[LIMIT number];这里需要注意的是CLUSTER BY col_list和DISTRIBUTE BY col_list SORT BY col_list两个是可选的,CLUSTER BY col_list其实相当于DISTRIBUTE BY col_list SORT BY col_list DESC的一个组合;
在进行查询时,如果表是已分区的,那么在查询字段中添加分区字段的话将会大大提升查询效率,因为需要扫描的数据量减少了。

微信扫一扫

第七城市微信公众平台