hive高级查询

-- 查询语句
SELECT 5,"student",88.5;
SELECT * FROM student;
SELECT name,id,id,name FROM student WHERE id>1002;
SELECT * FROM student limit 2;【注意：只能有一个参数】
SELECT * FROM student WHERE name rlike '(j|r).*';
SELECT sex,avg(mark) FROM score GROUP BY sex;
SELECT sex,avg(mark) avg_mark FROM score GROUP BY sex HAVING avg_mark>79;

SELECT id,name FROM (
    SELECT id,name FROM (
        SELECT id,name FROM student) t
    ) t;

-- CTE【Common Table Expression，公共表表达式】
WITH t1 AS(
    SELECT id,name FROM student
),t2 AS(
    SELECT id,name FROM t1
)
SELECT id,name FROM t2;

-- 准备数据
DROP TABLE IF EXISTS student;
CREATE TABLE IF NOT EXISTS student
(
    id int,
    name string,
    gid int
)row FORMAT delimited
fields terminated BY ',';

CREATE TABLE IF NOT EXISTS grade
(
    id int,
    name string
)row FORMAT delimited
fields terminated BY ',';

LOAD data local inpath '/root/data/student.txt' overwrite INTO TABLE `student`;
LOAD data local inpath '/root/data/grade.txt' overwrite INTO TABLE grade;

-- 各种连接
SELECT *
FROM student,grade;

SELECT *
FROM student CROSS join grade;

SELECT *
FROM student s CROSS join grade g
WHERE s.gid=g.id;

SELECT *
FROM student s inner join grade g
on s.gid=g.id;

SELECT *
FROM student s left join grade g
on s.gid=g.id;

SELECT *
FROM grade g left join student s
on s.gid=g.id;

SELECT *
FROM grade g full join student s
on s.gid=g.id;

-- 联合
DROP TABLE t1;
CREATE TABLE t1
(
    c11 int,
    c12 string
)row FORMAT delimited
fields terminated BY ',';
DROP TABLE t2;
CREATE TABLE t2
(
    c21 string,
    c22 string
)row FORMAT delimited
fields terminated BY ',';

LOAD data local inpath '/root/data/union2.txt' overwrite INTO TABLE `t1`;
LOAD data local inpath '/root/data/union2.txt' overwrite INTO TABLE `t2`;

-- 注意，字段个数一样，字段名称一样
SELECT c11,c12 FROM t1
UNION ALL 
SELECT c21 c11,c22 c12 FROM t2;

SELECT DISTINCT * FROM (
    SELECT c11,c12 FROM t1
    UNION ALL
    SELECT c21 c11,c22 c12 FROM t2
) t;

WITH t0 AS(
    SELECT c11,c12 FROM t1
    UNION ALL 
    SELECT c21 c11,c22 c12 FROM t2
) SELECT DISTINCT * FROM t0;

WITH t0 AS(
    SELECT c11,c12 FROM t1
    UNION ALL 
    SELECT c21 c11,c22 c12 FROM t2
) SELECT c11,c12 FROM t0 GROUP BY c11,c12;

-- 插入数据
INSERT INTO TABLE t1(c11,c12) VALUES(55,66);
INSERT INTO TABLE t1 VALUES(77,88),(99,00);
INSERT OVERWRITE TABLE t1 VALUES(77,88),(99,00);

CREATE TABLE t3 LIKE t1;
INSERT INTO TABLE t3 SELECT c11,c12 FROM t1;
INSERT OVERWRITE TABLE t3 SELECT c11,c12 FROM t1;

-- 业务数据备份，表->文件【备份数据】
INSERT overwrite directory '/test/num1/num2' SELECT c11,c12 FROM t1;【hdfs自建目录】
INSERT overwrite local directory '/root/data/num1/num2' SELECT c11,c12 FROM t1;【linux自建目录】
INSERT overwrite local directory '/root/data/num1/num2'
row FORMAT delimited fields terminated BY ','
SELECT c11,c12 FROM t1;

-- 同一表多目录
FROM t1
INSERT overwrite directory '/test/num1/num2' SELECT c11,c12 
INSERT overwrite local directory '/root/data/num1/num2'
row FORMAT delimited fields terminated BY ','
SELECT c11,c12;

-- 业务数据和元数据备份，表->文件【备份表】
export TABLE t1 TO '/test/num3/num4';【hdfs自建目录】
import TABLE t1 FROM '/test/num3/num4';【一次操作，不能覆盖】

-- 排序【升序asc(默认)，降序desc】
SELECT * FROM score ORDER BY mark;
SELECT * FROM score ORDER BY mark desc;

CREATE TABLE sort
(
    id int,
    name string
)row FORMAT delimited
fields terminated BY ',';
LOAD data local inpath '/root/data/sort.txt' overwrite INTO TABLE `sort`;
SELECT * FROM sort;

set mapreduce.job.reduces = 4;

SELECT * FROM sort distribute BY id;
SELECT * FROM sort distribute BY id sort BY id;

INSERT overwrite local directory '/root/data/sort'
SELECT * FROM sort distribute BY id sort BY id;

INSERT overwrite local directory '/root/data/sort'
SELECT * FROM sort cluster BY id;