create external table student (s_id string,s_name string) row format delimited fields terminated by '\t';
复制代码
-从当地文件系统向表中加载数据
追加操作
load data local inpath '/export/servers/hivedatas/student.csv' into table student;
复制代码
覆盖操作
load data local inpath '/export/servers/hivedatas/student.csv' overwrite into table student;
复制代码
-从hdfs文件系统向表中加载数据
load data inpath '/hivedatas/techer.csv' into table techer;
复制代码
加载数据到指定分区
load data inpath '/hivedatas/techer.csv' into table techer partition(cur_date=20201210);
复制代码
-留意：
1.使用 load data local 表现从当地文件系统加载，文件会拷贝到hdfs上
2.使用 load data 表现从hdfs文件系统加载，文件会直接移动到hive相关目次下，留意不是拷贝过去，因为hive以为hdfs文件已经有3副本了，没必要再次拷贝了
3.如果表是分区表，load 时不指定分区会报错
4.如果加载类似文件名的文件，会被主动重命名
对分区表的操作
-创建分区表的语法
create table score(s_id string, s_score int) partitioned by (month string);
create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by ',' location '/myscore';
复制代码
-export导出与import 导入 hive表数据（内部表操作）
create table techer2 like techer; --依据已有表结构创建表
export table techer to '/export/techer';
import table techer2 from '/export/techer';
复制代码
hive表中数据导出
-insert导出
将查询的结果导出到当地
insert overwrite local directory '/export/servers/exporthive' select * from score;
复制代码
将查询的结果格式化导出到当地
insert overwrite local directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from student;
复制代码
将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from score;
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]
复制代码
留意：
1、order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。
2、sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by举行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
3、distribute by(字段)根据指定的字段将数据分到差别的reducer，且分发算法是hash散列。
4、Cluster by(字段) 除了具有Distribute by的功能外，还会对该字段举行排序。
因此，如果分桶和sort字段是同一个时，此时，cluster by = distribute by + sort by
WHERE语句
select * from score where s_score < 60;
复制代码
留意：
小于某个值是不包罗null的，如上查询结果是把 s_score 为 null 的行剔除的
GROUP BY 分组
select s_id ,avg(s_score) from score group by s_id;
复制代码
分组后对数据举行筛选，使用having
select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;
复制代码
留意：
如果使用 group by 分组，则 select 背面只能写分组的字段或者聚合函数
where和having区别：
1 having是在 group by 分完组之后再对数据举行筛选，所以having 要筛选的字段只能是分组字段或者聚合函数
2 where 是从数据表中的字段直接举行的筛选的，所以不能跟在gruop by背面，也不能使用聚合函数
join 毗连
INNER JOIN 内毗连：只有举行毗连的两个表中都存在与毗连条件相匹配的数据才会被保存下来
select * from techer t [inner] join course c on t.t_id = c.t_id; -- inner 可省略
复制代码
LEFT OUTER JOIN 左外毗连：左边全部数据会被返回，右边符合条件的被返回
select * from techer t left join course c on t.t_id = c.t_id; -- outer可省略
复制代码
RIGHT OUTER JOIN 右外毗连：右边全部数据会被返回，左边符合条件的被返回、
select * from techer t right join course c on t.t_id = c.t_id;
复制代码
FULL OUTER JOIN 满外(全外)毗连: 将会返回全部表中符合条件的全部记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替换。
SELECT * FROM techer t FULL JOIN course c ON t.t_id = c.t_id ;
注：1. hive2版本已经支持不等值毗连，就是 join on条件背面可以使用大于小于符号了;并且也支持 join on 条件后跟or (早前版本 on 后只支持 = 和 and，不支持 > < 和 or)
2.如hive执行引擎使用MapReduce，一个join就会启动一个job，一条sql语句中如有多个join，则会启动多个job
留意：表之间用逗号(,)毗连和 inner join 是一样的
select * from table_a,table_b where table_a.id=table_b.id;
它们的执行服从没有区别，只是书写方式差别，用逗号是sql 89标准，join 是sql 92标准。用逗号毗连背面过滤条件用 where ，用 join 毗连背面过滤条件是 on。
order by 排序
全局排序，只会有一个reduce
ASC（ascend）: 升序（默认） DESC（descend）: 降序
SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;
复制代码
留意：order by 是全局排序，所以末了只有一个reduce，也就是在一个节点执行，如果数据量太大，就会耗费较长时间
sort by 局部排序
每个MapReduce内部举行排序，对全局结果集来说不是排序。
设置reduce个数
set mapreduce.job.reduces=3;
复制代码
查看设置reduce个数
set mapreduce.job.reduces;
复制代码
查询成绩按照成绩降序分列
select * from score sort by s_score;
复制代码
将查询结果导入到文件中（按照成绩降序分列）
insert overwrite local directory '/export/servers/hivedatas/sort' select * from score sort by s_score;
