insert overwrite local directory ‘/path’ row format delimited fields terminated by ‘\t’ select * from t_name;
导出数据到本地
insert overwrite directory ‘/hdfs_path’ select * from t_name;
导出数据到hdfs
修改数据
update t_name set col_name = data_1;
删除数据
delete from t_name;
alter table t_name drop partition(patition_name=‘value’)
删除partition
清除数据
truncate t_name;
同左
hdfs dfs -rm -r /local_path/(shell命令)
删除外部表路径
1.6 DQL
MySQL
Hive
备注
执行顺序
FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY --> LIMIT
from -->where --> select --> group by -->聚合函数–> having --> order by -->limit
查询
select [*,distinct] col_name as new_name from t_name join t_name1 on j_condition where w_condition group by col_name having h_condition order by col_name limit 起始值,偏移值;
select [*,distinct] col_name as new_name from t_name join t_name1 on j_condition where w_condition group by col_name having h_condition order/sort/distribute/cluster by col_name limit 起始值,偏移值;
set hive.mapred.mode=nonstrict;
查询参数设置
select col_name[索引0~] from t_name;
查看数组中的某元素
select explode(array_col_name) from t_name;
查看数组中的所有元素
select explode(map_col_name) from t_name;
分两列查看map中的key和value
select map_values(map_col_name) from t_name;
查看map所有的value
查询计算
select int_col_name+100 as int_col_name1 from t_name;
同左
1.7 表关联
MySQL
Hive
备注
隐式内连接
select col_name from t_left,t_right where w_condition;
显式内连接
select col_name from t_left [inner] join t_right on j_condition;
左外连接
select col_name from t_left left join t_right on j_condition;
右外连接
select col_name from t_left right join t_right on j_condition;
select col_name from t_left full join t_right on j_condition;
full join列合并
行集合并union
select col_name from t_name1 union select col_name from t_name2 ;
union all
select col_name from t_name1 union all select col_name from t_name2 ;
select col_name from t_name1 intersect select col_name from t_name2 ;
交集
select col_name from t_name1 minus select col_name from t_name2 ;
1. 命令语句对比MySQLHiveDDL创建databasecreate database if not exists d_name character set utf8;create database if not exists d_name;查看databaseshow databases;同左查看database定义信息show create database d_name;同左删除databasedrop database