首页 > 代码库 > Hive:常用的一些命令

Hive:常用的一些命令

1、一般可以通过beeline,代理方式登录hive;

2、使用数据库abc_hive_db:use abc_hive_db;

3、查看数据库中有哪些表:show tables ;有哪些特定表 show tables like ‘*tb_site*‘;

4、查看某张表的表结构:desc tablename;

5、创建表:

--OID,MSISDN,StartTime,EndTime,AP_MAC,ApAliasName,HotSpotName,Longitude,Latitude,Floor0: jdbc:hive2://xxxxx/> create table tmp_wifi1109(OID string,MSISDN string,StartTime timestamp,
EndTime timestamp,AP_MAC string,ApAliasName string,HotSpotName string,Longitude string,Latitude
string,Floor string) row format delimited fields terminated by , stored as textfile;

6、从hdfs文件中导入数据到表:

0: jdbc:hive2://xxxx/> load data inpath hdfs:/user/xx_xx/dt/wifi_user_list_1109.csv into table tmp_wifi1109;

7、把表之间关联的结果存储某张新建表:

create table tmp_mr_s1_mme1109 as select distinct b.OID,b.MSISDN,b.StartTime,b.EndTime,b.AP_MAC,b.ApAliasName,b.HotSpotName,b.Longitude,b.Latitude,b.Floor,
a.ues1ap_id,a.cellid
from default.s1mme a join abc_hive_db.tmp_wifi1109 b on a.msisdn=b.MSISDN and a.hour>=20161109 and a.hour<20161110 where (
  (a.start_time<=b.StartTime and a.end_time>=b.StartTime)
  or (a.start_time<=b.EndTime and a.end_time>=b.EndTime)
  or (a.start_time>=b.StartTime and a.end_time<=b.EndTime)
)

8、导出某张表中的记录到hdfs:

set hive.merge.mapfiles;set hive.merge.mapredfields;set hive.merge.size.per.task= 1000000000;set hive.merge.smallfiles.avgsize= 1000000000;use rc_hive_db;insert overwrite directory /user/dt/dat/1109/ row format delimited fields terminated by | select * from tmp_mr_s1_mme1109;

9、查看表分区字段:

0: jdbc:hive2://xxx/> show partitions default.s1_mme;+------------------------------------+--+|             partition              |+------------------------------------+--+| hour=2016110214                  || hour=2016110215                  || hour=2016110216                  |...+------------------------------------+--+

 参考信息:

hive partitions相关处理:http://blog.sina.com.cn/s/blog_9f48885501016hn5.html

http://blog.sina.com.cn/s/blog_9f48885501016k5m.html

Hive:常用的一些命令