Hive常用命令

常用命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
- 创建数据库
create database name;
- 创建数据库表( 需要先进入一个数据库 use name )
create table name (id Int, name String) row format delimited FIELDS TERMINATED BY '分隔符'; 
- 导入数据 ( 加上 overwrite 就会将表清空后倒入，不加上就直接导入 )
	1. 从本地导入
	load data local inpath '数据地址' overwrite into table tableName;
	2. hdfs上导入
	load data inpath '数据地址' overwrite into tableName;
	
- 查询
	group by 分组
	order by 排序 (desc倒序)
	group by 之后 where 要使用 having 
	hive 中的不等于为 <>
- 将查询的数据保存到其他位置 ( 加上 overwrite 就会将表清空后倒入，不加上就直接导入 )
	1. 保存到已存在的表中
		insert overwrite table 保存数据的表 select * form 查询数据的表
	2. 保存到未存在的表中
		create table 新表名(字段名，类型) as select * from 查询数据的表
	3. 保存到hdfs上
		
		
 - hive 中字符串处理 博客地址( https://my.oschina.net/u/2441766/blog/1648467 )
 	length(字段名) 返回字段的长度
    reverse(字段名) 返回反转之后的字段
    concat(字段A，字段B) 返回字段A+字段B的字段
    concat_ws("分隔号"，字段A，字段B) 返回字段A+分隔号+字段B
    
 - hive 连接表
 	-join 
 	1.left join
 	2.right join
 	3.
 	
 - 建表
 	1. 建立普通表
 	create table tableName() row format delimited fields termanited by ',';
 	2. 建立分区表
 	create table tableName() pratitionted by (分区字段 字段类型) row format delimited fields termanited by ',';
 - 建立静态分区
 	alter table tableName add partition(分区字段="");
 - 删除分区
 	alter table tableName drop partition(分区字段="");
 
 - 删除表之前要将所有的分区都删除

如果你的分区中有中文字符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
// 修改Mysql中Hive表的编码格式
alter database hive_meta default character set utf8;
alter table BUCKETING_COLS default character set utf8;
alter table CDS default character set utf8;
alter table COLUMNS_V2 default character set utf8;
alter table DATABASE_PARAMS default character set utf8;
alter table DBS default character set utf8;
alter table FUNCS default character set utf8;
alter table FUNC_RU default character set utf8;
alter table GLOBAL_PRIVS default character set utf8;
alter table PARTITIONS default character set utf8;
alter table PARTITION_KEYS default character set utf8;
alter table PARTITION_KEY_VALS default character set utf8;
alter table PARTITION_PARAMS default character set utf8;
alter table ROLES default character set utf8;
alter table SDS default character set utf8;
alter table SD_PARAMS default character set utf8;
alter table SEQUENCE_TABLE default character set utf8;
alter table SERDES default character set utf8;
alter table SERDE_PARAMS default character set utf8;
alter table SKEWED_COL_NAMES default character set utf8;
alter table SKEWED_COL_VALUE_LOC_MAP default character set utf8;
alter table SKEWED_STRING_LIST default character set utf8;
alter table SKEWED_STRING_LIST_VALUES default character set utf8;
alter table SKEWED_VALUES default character set utf8;
alter table SORT_COLS default character set utf8;
alter table TABLE_PARAMS default character set utf8;
alter table TAB_COL_STATS default character set utf8;
alter table TBLS default character set utf8;
alter table VERSION default character set utf8;
alter table BUCKETING_COLS convert to character set utf8;
alter table CDS convert to character set utf8;
alter table COLUMNS_V2 convert to character set utf8;
alter table DATABASE_PARAMS convert to character set utf8;
alter table DBS convert to character set utf8;
alter table FUNCS convert to character set utf8;
alter table FUNC_RU convert to character set utf8;
alter table GLOBAL_PRIVS convert to character set utf8;
alter table PARTITIONS convert to character set utf8;
alter table PARTITION_KEYS convert to character set utf8;
alter table PARTITION_KEY_VALS convert to character set utf8;
alter table PARTITION_PARAMS convert to character set utf8;
alter table ROLES convert to character set utf8;
alter table SDS convert to character set utf8;
alter table SD_PARAMS convert to character set utf8;
alter table SEQUENCE_TABLE convert to character set utf8;
alter table SERDES convert to character set utf8;
alter table SERDE_PARAMS convert to character set utf8;
alter table SKEWED_COL_NAMES convert to character set utf8;
alter table SKEWED_COL_VALUE_LOC_MAP convert to character set utf8;
alter table SKEWED_STRING_LIST convert to character set utf8;
alter table SKEWED_STRING_LIST_VALUES convert to character set utf8;
alter table SKEWED_VALUES convert to character set utf8;
alter table SORT_COLS convert to character set utf8;
alter table TABLE_PARAMS convert to character set utf8;
alter table TAB_COL_STATS convert to character set utf8;
alter table TBLS convert to character set utf8;
alter table VERSION convert to character set utf8;
SET character_set_client = utf8 ;
SET character_set_database = utf8 ;
SET character_set_results = utf8 ;
SET character_set_server = utf8 ;
SET NAMES 'utf8';
许可协议

本文采用 署名-非商业性使用-相同方式共享 4.0 国际 许可协议，转载请注明出处。

快来参与讨论吧~