hive 从入门到精通

目录

• hive入门到精通
  
  
  
  • hive部署
    
    
    
    • 启动Hadoop
      
    • 检查MySQL是否启动成功
      
    • 安装hive
      
      
      
      • hive-env.sh
        
      • hive-site.xml
        
        
        
        • 需要修改的位置提炼如下：
          
        
        
      • 上传 MySQL 连接驱动 jar 包到 hive 安装目录的lib目录下:
        
      • guava版本冲突
        
      • 配置环境变量
        
      • 初始化hive的元数据库
        
      • 远程模式
        
      • 体验
        
      
      
    
    
  • 编程
    
    
    
    • DDL
      
      
      
      • 操作数据库
        
        
        
        • 创建数据库
          
        • 查询数据库
          
        • 修改数据库
          
        • 删除数据库
          
        • 切换数据库
          
        
        
      
      
    • DML
      
      
      
      • 操作数据表
        
        
        
        • 基本数据类型
          
        • 内部表
          
        • 外部表
          
        • 部表与外部表转换
          
        • 查看表
          
        • 修改表
          
        • 删除表
          
        • 清除表
          
        
        
      
      
    • DQL
      
      
      
      • 准备数据
        
      • 单表查询
        
      • 综合练习
        
      • 高级聚合函数
        
        
        
        • 分组排序取TopN
          
        • UDTF
          
        • UDTF 案例
          
        • 分组和去重
          
        
        
      
      
    • 开窗函数
      
      
      
      • 
        
        
        
        • 聚合函数
          
        • 跨行取值
          
          
          
          • lead lag
            
          • first_value last_value
            
          
          
        
        
      • 综合练习
        
        
        
        • 准备数据
          
        • 练习题目
          
        
        
      
      
    • 分区表
      
      
      
      • 模拟数据
        
        
        
        • 身份证前六位
          
        • 身份证前六位
          
        • pom.xml
          
        • 工具类
          
        
        
      • 创建数据库
        
      • 内部分区表
        
      • 内部分区表
        
        
        
        • 导入数据
          
        
        
      • 外部分区表
        
        
        
        • 创建外部分区表关联目录
          
        • 创建外部分区表
          
        • 导入数据
          
        
        
      • 多重内部分区表
        
        
        
        • 创建内部多重内部分区表
          
        • 导入数据
          
        
        
      • 多重外部分区表
        
        
        
        • 创建多重外部分区表关联目录
          
        • 创建多重外部分区表
          
        
        
      • 动态分区
        
      
      
    • 分桶
      
      
      
      • 创建普通表并导入数据
        
      • 开启分桶
        
      • 创建桶表
        
      • 载入数据到桶表
        
      
      
    • 视图
      
    • 存储与压缩
      
      
      
      • 文件格式
        
      • text file：
        
      • sequence file
        
      • ORC
        
      • Parquet
        
      • rcfile
        
      
      
    
    
  
  

hive入门到精通

hive部署

启动Hadoop

1. # 启动hadoop
2. start-all.sh
3. # 检查hadoop进程
4. jps
5. # 检查各端口
6. netstat -aplnt | grep java

复制代码

检查MySQL是否启动成功

1. ps -aux | grep mysql
2. netstat -aplnt | grep 3306

复制代码

安装hive

1. # 将软件上传到 /opt/soft 目录
2. # 解压hive
3. tar -zxvf apache-hive-3.1.3-bin.tar.gz
4. # 目录改名
5. mv apache-hive-3.1.3-bin hive3
6. # 进入配置文件目录
7. cd /opt/soft/hive3/conf
8. # 复制配置文件
9. cp hive-env.sh.template  hive-env.sh
10. cp hive-default.xml.template  hive-site.xml
11. # 编辑环境配置文件
12. vim hive-env.sh
13. # 编辑配置文件
14. vim hive-site.xml

复制代码

hive-env.sh

1. # hadoop 安装路径
2. export HADOOP_HOME=/opt/soft/hadoop3/
3. # hive 配置文件路径
4. export HIVE_CONF_DIR=/opt/soft/hive3/conf/

复制代码

hive-site.xml

1. [/code][size=2]需要修改的位置提炼如下：[/size]
3. [code]<configuration>
4.    
5.     <property>
6.         <name>javax.jdo.option.ConnectionURL</name>
7.         <value>jdbc:mysql://spark03:3306/hive?useUnicode=true&createDatabaseIfNotExist=true&characterEncoding=UTF8&useSSL=false&serverTimeZone=Asia/Shanghai</value>
8.     </property>
10.    
11.     <property>
12.         <name>javax.jdo.option.ConnectionDriverName</name>
13.         <value>com.mysql.cj.jdbc.Driver</value>
14.     </property>
16.    
17.     <property>
18.         <name>javax.jdo.option.ConnectionUserName</name>
19.         <value>root</value>
20.     </property>
21.     <property>
22.         <name>javax.jdo.option.ConnectionPassword</name>
23.         <value>Lihaozhe!!@@1122</value>
24.     </property>
26.     <property>
27.         <name>hive.metastore.warehouse.dir</name>
28.         <value>/user/hive/warehouse</value>
29.     </property>
31.     <property>
32.         <name>hive.exec.scratchdir</name>
33.         <value>/user/hive/tmp</value>
34.     </property>
35.    
36.     </property>
38.     <property>
39.         <name>hive.exec.local.scratchdir</name>
40.         <value>/user/hive/local</value>
41.         <description>Local scratch space for Hive jobs</description>
42.     </property>
44.     <property>
45.         <name>hive.downloaded.resources.dir</name>
46.         <value>/user/hive/resources</value>
47.         <description>Temporary local directory for added resources in the remote file system.</description>
48.     </property>
50.    
51.     <property>
52.         <name>hive.querylog.location</name>
53.         <value>/user/hive/log</value>
54.     </property>
56.    
57.     <property>
58.         <name>hive.metastore.uris</name>
59.         <value>thrift://spark01:9083</value>
60.     </property>
62.    
63.     <property>
64.         <name>hive.server2.thrift.port</name>
65.         <value>10000</value>
66.     </property>
67.     <property>
68.         <name>hive.server2.thrift.bind.host</name>
69.         <value>0.0.0.0</value>
70.     </property>
71.     <property>
72.         <name>hive.server2.webui.host</name>
73.         <value>0.0.0.0</value>
74.     </property>
76.    
77.     <property>
78.         <name>hive.server2.webui.port</name>
79.         <value>10002</value>
80.     </property>
82.     <property>
83.         <name>hive.server2.long.polling.timeout</name>
84.         <value>5000</value>                              
85.     </property>
87.     <property>
88.         <name>hive.server2.enable.doAs</name>
89.         <value>true</value>
90.     </property>
91.    
92.     <property>
93.         <name>hive.execution.engine</name>
94.         <value>mr</value>
95.     </property>
96.     <property>
97.         <name>hive.metastore.schema.verification</name>
98.         <value>false</value>
99.         <description>
100.           Enforce metastore schema version consistency.
101.           True: Verify that version information stored in is compatible with one from Hive jars.  Also disable automatic
102.                 schema migration attempt. Users are required to manually migrate schema after Hive upgrade which ensures
103.                 proper metastore schema migration. (Default)
104.           False: Warn if the version information stored in metastore doesn't match with one from in Hive jars.
105.         </description>
106.       </property>
107. </configuration>

复制代码

注意：上面配置文件中的路径在 vi 编辑器下 全局替换

1. :%s@\${system:java.io.tmpdir}@/tmp/hive-logp@g

复制代码

不要使用图形化 不然每次保存后3215行都会有个 &#8 特殊字符 如果产生删除即可 具体报错信息 后面有单独的描述

上传 MySQL 连接驱动 jar 包到 hive 安装目录的lib目录下:

1. /opt/soft/hive3/lib

复制代码

jar 包有两个 分别为：

• mysql-connector-java-8.0.33.jar
  
• protobuf-java-3.22.2.jar
  

删除原有的 protobuf-java-2.5.0.jar 文件

guava版本冲突

删除 hive/lib目录中的 guava-19.0.jar
拷贝hadoop/share/hadoop/common/lib目录中的 guava-27.0-jre.jar 到 hive/lib 目录

1. rm -f /opt/soft/hive3/lib/guava-19.0.jar
2. cp -v /opt/soft/hadoop3/share/hadoop/common/lib/guava-27.0-jre.jar /opt/soft/hive3/lib

复制代码

配置环境变量

1. vim /etc/profile

复制代码

1. export HIVE_HOME=/opt/soft/hive3
2. export PATH=$PATH:$HIVE_HOME/bin

复制代码

1. source /etc/profile

复制代码

初始化hive的元数据库

注意：初始初始元素中库之前 保证 hadoop 和 mysql 正常启动

1. schematool -initSchema -dbType  mysql

复制代码

1. Exception in thread "main" java.lang.RuntimeException: com.ctc.wstx.exc.WstxParsingException: Illegal character entity: expansion character (code 0x8
2. at [row,col,system-id]: [3215,96,"file:/usr/local/hive/conf/hive-site.xml"]
3. at org.apache.hadoop.conf.Configuration.loadResource(Configuration.java:3051)
4. ...
5. at org.apache.hadoop.util.RunJar.main(RunJar.java:236)
6. Caused by: com.ctc.wstx.exc.WstxParsingException: Illegal character entity: expansion character (code 0x8
7. at [row,col,system-id]: [3215,96,"file:/usr/local/hive/conf/hive-site.xml"]
8. at com.ctc.wstx.sr.StreamScanner.constructWfcException(StreamScanner.java:621)
9. ...
10. at org.apache.hadoop.conf.Configuration.loadResource(Configuration.java:3034)
11. ... 17 more
13. 报错原因：
15. hive-site.xml配置文件中，3215行（见报错记录第二行）有特殊字符
17. 解决办法：
19. 进入hive-site.xml文件，跳转到对应行，删除里面的 &#8 特殊字符即可

复制代码

1. Caused by: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D
2.         at java.net.URI.checkPath(URI.java:1822)
3.         at java.net.URI.<init>(URI.java:745)
4.         at org.apache.hadoop.fs.Path.initialize(Path.java:260)
6. 解决方案：将hive-site.xml配置文件的
7. hive.querylog.location
8. hive.exec.local.scratchdir
9. hive.downloaded.resources.dir
10. 三个值（原始为$标识的相对路径）写成绝对值

复制代码

1. # 全局替换
2. :%s@\${system:java.io.tmpdir}@/tmp/hive-logp@g

复制代码

远程模式

1. # 启动服务端
2. hive --service metastore &
3. hive --service hiveserver2 &
5. # 后台运行
6. nohup hive --service metastore > /dev/null 2>&1 &
7. nohup hive --service hiveserver2 > /dev/null 2>&1 &
9. hiveserver2 start
10. nohup hiveserver2 >/dev/null 2>&1 &

复制代码

1. # 客户端连接
2. hive
3. beeline -u jdbc:hive2://spark01:10000 -n root
4. beeline jdbc:hive2://spark01:10000> show databases;

复制代码

体验

1. use default;
2. create table person (
3.         id int,
4.     phonenum bigint,
5.     salary double,
6.     name string
7. );
8. create table ps (
9.         id int,
10.     phonenum bigint,
11.     salary double,
12.     name string
13. );
14. show tables;
15. insert into person values (1001,13966668888,9999.99,"张三");

复制代码

1. songsong,bingbing_lili,xiao song:18_xiaoxiao song:19
2. longlong,pingping_liuliu,xiao long:8_xiaoxiao long:9

复制代码

1. drop table person;
2. create table person (
3.     name string,
4.     friends array<string>,
5.     childrens map<string,int>
6. )
7. row format delimited fields terminated by ','
8. collection items terminated by '_'
9. map keys terminated by ':'
10. lines terminated by '\n';

复制代码

1. load data local inpath  '/root/person.txt' into table person;

复制代码

1. drop table data;
2. create table data (
3.     name string,
4.            amount int
5. )
6. row format delimited fields terminated by ','
7. lines terminated by '\n';
8. load data local inpath  '/root/data.txt' into table data;

复制代码

1. select count(*) from data;
2. select count(*) from data group by name;
3. select name,max(t) from data group by name;
4. select name,max(t) from data group by name order by max(t) ;

复制代码

编程

DDL

操作数据库

创建数据库

1. -- 创建数据库不指定路径
2. create database db_hive01;
4. -- 创建数据库指定 hdfs 路径
5. create database db_hive02 location '/db_hive02';
7. -- 创建数据库附加 dbproperties
8. create database  db_hive03  with dbproperties ('create-date'='2023-04-17','create_author'='lihaozhe');

复制代码

查询数据库

1. -- 查看所有数据库
2. show databases;
4. -- 模糊查看所有数据库
5. -- * 代表所有
6. -- | 代表或
7. show databases like 'db_hive*';
9. -- 查看数据库信息
10. desc database db_hive03;
12. -- 查看数据库详尽信息
13. describe database db_hive03;
15. -- 查看数据库更详尽信息
16. describe database extended db_hive03;

复制代码

修改数据库

1. -- 修改 dbproperties
2. alter database db_hive03 SET dbproperties ('crate_data'='2023-04-18');
4. -- 修改location
5. alter database db_hive02 SET location '/db_hive002';
7. -- 修改 owner user
8. alter database database_name set owner user lhz;

复制代码

删除数据库

1. -- 删除空数据库
2. drop database db_hive02 restrict;
3. -- 删除非空数据库
4. drop database db_hive03 cascade;

复制代码

切换数据库

1. use db_hive01;

复制代码

DML

操作数据表

普通表
临时表 temporary
外部表 external

1. -- 利用 select 语句查询结果 创建一张表
2. create table as select
4. -- 复刻一张已经存在的表结构 但是 不包含数据
5. create table like

复制代码

基本数据类型

数据类型说明定义tinyint1 byte 有符号整型smallint2 byte 有符号整型int4 byte 有符号整型bigint8 byte 有符号整型float4 byte 单精度浮点数double8 byte 双精度浮点数dicimal十进制精准数据类型varchar字符序列 需要指定最大长度 范围[1~65535]string字符串 无需指定最大长度timestamp时间binary二进制数据booleantrue falsearray一组相同数据类型的集合arraymap一组相同数据类型的键值对mapstruct由多个属性组成，每个属性都有自己的属性名和数据类型structid:int,name:string内部表

简单表

1. create table person (
2.         id int,
3.     phonenum bigint,
4.     salary dicimal,
5.     name string
6. );
7. show tables;
8. insert into person values (1001,13966668888,9999.99,"张三");

复制代码

简单数据类型

1. create table data (
2.     name string,
3.            amount int
4. )
5. row format delimited fields terminated by ','
6. lines terminated by '\n'
7. location '/user/hive/warehouse/lihaozhe.db/data';

复制代码

1. # 上传文件到Hive表指定的路径
2. hdfs dfs -put /root/data.csv /user/hive/warehouse/lihaozhe.db/data

复制代码

复杂数据类型

1. vim /root/person.txtsongsong,bingbing_lili,xiao song:18_xiaoxiao song:19
2. longlong,pingping_liuliu,xiao long:8_xiaoxiao long:9

复制代码

1. drop table person;
2. create table person (
3.     name string,
4.     friends array<string>,
5.     childrens map<string,int>
6. )
7. row format delimited fields terminated by ','
8. collection items terminated by '_'
9. map keys terminated by ':'
10. lines terminated by '\n'; load data local inpath  '/root/person.txt' into table person;

复制代码

json数据类型

json函数
get_json_object
json_tuple
json serde加载数据

1.           --serialization     序列化
2.                          
3.           --deserialization 反序列化

复制代码

1. {"name":"user01","amount":"100"}

复制代码

1. {
2.     "name":"lhz",
3.     "friends":["lize","lanlan","manman"],
4.     "students":[
5.         "xiaohui":15000,"huixiaoxiao":18000
6.     ],
7.     "address":{
8.             "province":"jilin",
9.         "city":"liaoyuan",
10.         "district":"liaoyuan"
11.         }
12. }

复制代码

-- 案例一

1. create table video (info string);
2. load data local inpath '/root/video.log' into table video;
3. select * from video limit 10;
4. select count(*) from video;

复制代码

1. select
2.     get_json_object(info,'$.id') as id,
3.     get_json_object(info,'$.nickname') as nickname,
4.     get_json_object(info,'$.gold') as gold
5. from video limit 5;

复制代码

1. select
2.     json_tuple(info,'id','nickname',"gold") as (id,nickname,gold)
3. from video limit 5;

复制代码

案例二

1. create table video(
2.     id string ,
3.     uid string,
4.     nickname string,
5.     gold int,
6.     watchnumpv int,
7.     watchnumuv int,
8.     hots int,
9.     nofollower int,
10.     looktime int,
11.     smlook int ,
12.     follower int ,
13.     gifter int ,
14.     length int ,
15.     area string ,
16.     rating varchar(1),
17.     exp int ,
18.     type string
19. )
20. row format serde 'org.apache.hive.hcatalog.data.JsonSerDe';

复制代码

1. load data local inpath '/root/video.log' into table video;

复制代码

1. --把json数据，按类给格式化，
2. STORED AS TEXTFILE;--文本文档的形式

复制代码

1. --导入数据
2. load data local inpath '/root/video.log' into table video;

复制代码

1. 创建表的loaction'' 作用是 集群里边有对应的数据，则直接将数据加载到表中
2. load data loacl inpath '' into table 是使用hive将数据从本地加载到已经建好的表中
3. load data inpath '' into table  是使用hive将数据从集群里边加载到已经建好的表中

复制代码

外部表

1. create external table data (
2.     name string,
3.            amount int
4. )
5. row format delimited fields terminated by ','
6. lines terminated by '\n'
7. location '/user/hive/warehouse/lihaozhe.db/data';

复制代码

部表与外部表转换

1. -- 内部表转外部表
2. alter table tblName set tblproperties('external'='true');
4. -- 外部表转内部表
5. alter table tblName set tblproperties('external'='false');

复制代码

查看表

1. -- 查看表
2. show tables;
4. -- 查看某数据库下的某张表
5. show tables in lihaozhe;
7. -- 查看表
8. show tables;
10. -- 模糊查看数据表
11. -- * 代表所有
12. -- | 代表或
13. show tables like 'per*';
15. -- 查看基本表信息
16. describe person;
18. -- 查看基本表详细信息
19. describe extended person;
21. -- 查看基本表详细信息并格式化展示
22. describe formatted person;

复制代码

修改表

1. -- 修改表名称
2. alter table person rename to tb_user;
4. -- 添加字段 向末尾追加
5. alter table tb_user add columns (gender tinyint);
7. -- 修改字段名称及类型
8. alter table tb_user change gender age smallint;
10. -- 删除字段

复制代码

删除表

1. drop table tb_user

复制代码

清除表

1. truncate table video;

复制代码

DQL

准备数据

-- 部门表 dept.csv

1. 10,行政部,1700
2. 20,财务部,1800
3. 30,教学部,1900
4. 40,销售部,1700

复制代码

1. hdfs dfs -mkdir -p /quiz01/dept
2. hdfs dfs -put /root/dept.csv/quiz01/dept

复制代码

1. create external table dept(
2.         dept_id int comment '部门id',
3.     dept_name string comment '部门名称',
4.     location_code int comment '部门位置'
5. )
6. comment '部门表'
7. row format delimited fields terminated by ','
8. lines terminated by '\n'
9. stored as textfile
10. location '/quiz01/dept';

复制代码

1. load data local inpath '/root/dept.csv' into table dept;

复制代码

员工表

1. 7369,张三,研发,800.00,30
2. 7499,李四,财务,1600.00,20
3. 7521,王五,行政,1250.00,10
4. 7566,赵六,销售,2975.00,40
5. 7654,侯七,研发,1250.00.30
6. 7698,马八，研发,2850.00,30
7. 7782,金九,行政,2450.0,30
8. 7788,银十,行政,3000.00,10
9. 7839,小芳,销售,5000.00,40
10. 7844,小明,销告,1500.00,40
11. 7876,小李,行政,1100.00,10
12. 7900,小元,讲师,950.00,30
13. 7902,小海,行政,3000.00,10
14. 7934,小红明,讲师,1300.00,30

复制代码

1. hdfs dfs -mkdir -p /quiz01/emp
2. hdfs dfs -put /root/emp.csv /quiz01/emp

复制代码

1. create external table emp
2. (
3.     emp_id     int comment '员工ID',
4.     emp_name   string comment '员工姓名',
5.     emp_job    string comment '员工岗位',
6.     emp_salary decimal(8, 2) comment '员工薪资',
7.     dept_id    int comment '员工隶属部门ID'
8. )
9. comment '员工表'
10. row format delimited fields terminated by ','
11. lines terminated by '\n'
12. stored as textfile
13. location '/quiz01/emp' ;

复制代码

1. load data local inpath '/root/emp.csv' into table emp;

复制代码

居民表 person.csv

1. hdfs dfs -mkdir -p /quiz02/person
2. hdfs dfs -put /root/person.csv /quiz02/person

复制代码

1. CREATE external TABLE `person` (
2.   `id` int COMMENT '主键',
3.   `id_card` varchar(18) COMMENT '身份证号码',
4.   `mobile` varchar(11) COMMENT '中国手机号',
5.   `real_name` varchar(15) COMMENT '身份证姓名',
6.   `uuid` varchar(32) COMMENT '系统唯一身份标识符'
7. )
8. row format delimited fields terminated by ','
9. lines terminated by '\n'
10. stored as textfile
11. location '/quiz02/person';

复制代码

1. load data local inpath '/root/person.csv' into table person;

复制代码

地区表 region.csv

1. hdfs dfs -mkdir -p /quiz02/region
2. hdfs dfs -put /root/region.csv /quiz02/region

复制代码

1. CREATE external TABLE `region` (
2.   `parent_code` int COMMENT '当前地区的上一级地区代码',  
3.   `region_code` int COMMENT '地区代码',
4.   `region_name` varchar(10) COMMENT '地区名称'
5. )
6. row format delimited fields terminated by ','
7. lines terminated by '\n'
8. stored as textfile
9. location '/quiz02/region';

复制代码

1. load data local inpath '/root/region.csv' into table region;

复制代码

单表查询

1. -- 查询所有
2. select * from dept;
4. -- 按照指定字段查询
5. select dept_name from dept;
7. -- 列别名
8. select dept_name as name from dept;
10. -- limit 分页查询
11. select * from emp limit 5,5
13. -- where 按条件查询
14. select * from emp where dept_id = 10;
16. -- 关系运算符
17. -- = != > >= < <=
18. -- in
19. select * from emp where dept_id in (20,40);
21. -- not in
22. select * from emp where dept_id not in (20,40);
24. -- like
25. select * from emp where emp_name like '小%';
27. -- not like
28. select * from emp where emp_name not like '小%';
30. -- 逻辑运算符
31. -- and
32. select  * from emp where  dept_id = 30 and emp_salary > 1000;
34. -- between and
35. select * from emp where  dept_id = 30 and emp_salary > 1000 and emp_salary < 2000;
36. select * from emp where  dept_id = 30 and emp_salary between 1000 and 2000;
38. --not between and
39. select * from emp where  dept_id = 30 and emp_salary not between 1000 and 2000;
41. -- or
42. select * from emp where  dept_id = 10 or dept_id = 40;
44. -- not !
45. select * from emp where  dept_id != 10;
46. select * from emp where not dept_id = 10;
48. -- 聚合函数
49. -- count(*) count(1) count(column_name)
50. select count(*) from emp;
51. select count(*) as total from emp;
53. -- max
54. select max(emp_salary) from emp;
56. -- min
57. select min(emp_salary) from emp;
59. -- sum
60. select sum(emp_salary) from emp;
62. -- avg
63. select avg(emp_salary) from emp;
65. -- group by 分组查询
66. select dept_id, avg(emp_salary) as avg_salary from emp group by dept_id;
68. -- having
69. select dept_id, avg(emp_salary) as avg_salary from emp group by dept_id having avg_salary > 2000;
71. -- where having
72. select dept_id, avg(emp_salary) as avg_salary from emp where dept_id != 10 group by dept_id having avg_salary > 2000;
74. -- order by 全局排序
75. select * from emp order by dept_id desc ,emp_salary desc;
76. select dept_id, max(emp_salary) from emp group by dept_id;
77. select dept_id, max(emp_salary) as max_salary from emp group by dept_id order by max_salary desc;
79. -- sort by (每个reduce)内部排序
80. select * from emp sort by dept_id desc
81. -- 查看 reduce 数量
82. set mapreduce.job.reduces;
83. -- 设置 reduce 数量 仅在当前连接有效 连接断开失效
84. set mapreduce.job.reduces=2;
85. select * from emp sort by dept_id desc;
87. -- 将查询结果写入到文件
88. insert overwrite local directory '/root/sort-result' select * from emp sort by dept_id desc;
90. -- distribute by 分区 类似与 mapreduce 中的 partation 自定义分区
91. set mapreduce.job.reduces=2;
92. insert overwrite local directory '/root/distribute-result' select * from emp distribute by dept_id sort by emp_salary desc;
93. -- distribute by 分区规则 根据字段的hash值 与 reduce 的数量 进行相除 余数 相同的在到一个分区
94. -- hvie 要求 distribute by 语句执行 在 sort by 语句之前
95. -- 执行结束之后 将 mapreduce.job.reduces 设置为 -1 不然 会影响 分区 分桶 load
97. -- cluster by 只能升序 不能降序 cluster by = sort by + distribute by
98. select * from emp cluster by dept_id;

复制代码

多表查询

1. -- 笛卡尔积
2. select * from dept,emp;
4. -- 避免笛卡尔积
5. select * from dept,emp where dept.dept_id = emp.dept_id
7. -- 等值json 内连接
8. select * from dept join emp where dept.dept_id = emp.dept_id
10. -- left join 左外连接
11. select d.dept_id,d.dept_name,d.location_code,e.emp_id,e.emp_name,e.emp_jpb,e.emp_salary
12. from dept d left join emp e where d.dept_id = e.dept_id;
14. -- right join 右外连接
15. select d.dept_id,d.dept_name,d.location_code,e.emp_id,e.emp_name,e.emp_jpb,e.emp_salary
16. from dept d right join emp e where d.dept_id = e.dept_id;
18. -- full join 满外连接
19. select d.dept_id,d.dept_name,d.location_code,e.emp_id,e.emp_name,e.emp_jpb,e.emp_salary
20. from dept d full join emp e where d.dept_id = e.dept_id;
22. -- union 上下拼接 去重
23. select * from emp where dept_id = 10 or dept_id = 40;
24. select * from emp where dept_id in(10,40);
25. select * from emp where dept_id = 10 union select * from emp where dept_id = 40;
27. -- union all 上下拼接 不去重
28. select * from emp where dept_id = 10 or dept_id = 40;
29. select * from emp where dept_id in(10,40);
30. select * from emp where dept_id = 10 union all select * from emp where dept_id = 40;
32. -- 自关联
34. select * from  region where region_code='220422';
35. --

复制代码

函数

1. # 设施本地模式
2. set hive.exec.mode.local.auto=true;
3. set mapperd.job.tracker=local

复制代码

1. -- 算术运算符
2. --  + — * / % & | ~
3. -- 数值运算
4.         -- round 四舍五入
5. select round (3.3) as num;
6.         -- ceil 向上取整
7. select ceil(3.3) as num;
8.         -- floor 向下取整
9. select floor(3.3) as num;
11. -- 字符串
12.         -- 截取 substr(column_name,start_index,length)
13. select substr(id_card,3,3) from person;
14.         -- substring(column_name,start_index,length)
15. select substring(id_card,3,3) from person;
16.         -- spilt 字符串切割
17. select split('2023-04-19','-');
18.         -- nvl 判空 替换 null 值
19. select nvl("lhz",1);
20. select nvl(null,1);
21.         -- replace 字符串替换
22. SELECT REPLACE('aaa.mysql.com','a','w');
23.         -- concat 字符串拼接
24. select concat('slogan','-','tlbyxzcx');
25.         -- concat 字符串拼接
26. select concat_ws('-',array('2022','04','19'));
27.         -- get_json_object 解析 json 字符串
28. select get_json_object('[{"name":"lhz","age":41}]','$.name') as name;
30. select get_json_object('[
31.     {"name":"lhz","age":41},
32.     {"name":"lz","age":14}
33. ]','$.[0].name')
35.         -- json_tuple
36. select json_tuple('{"name":"lhz","age":41}','name','age') as (name,age);
38. -- 日期函数
39.         -- unix 时间戳
40.    --（1970.01.01 00：00：00 GMT  UTC  时间）
41.     -- unix_timestamp 返回 bigint 无时区
42. select unix_timestamp();-- 1681951622 时间秒数
43. select unix_timestamp('1983-11-22 20:30:00','yyyy-MM-dd HH:mm:ss'); -- 438381000
44.         -- from_unixtime
45. select from_unixtime(438381000); -- 1983-11-22 20:30:00
47.         -- current_date
48. select current_date();
50.         -- current_timestamp
51. select current_timestamp();
53.         -- year month day hours minute second
54. select year('1983-01-23');
56.         -- datediff 两个日期相差天数（结束日期减去开始日期）
57. select datediff('1983-01-23','1982-12-31')
58.         -- date_add 日期增加几天
59. select date_add('1995-01-01',15);
60.         -- date_sub 日期减少几天
61. select date_sub('1995-01-01',15);
62.         -- date_format 日期格式化
63. select date_format ('1983-11-22 20:30:00','yyyy年MM月dd日 HH时mm分ss秒');
64.        
65. -- 读取身份证获取出生日期 输出格式为 yyyy-MM-dd
66. -- 1、字符串截取 2、日期格式化
67. select substr(id_card,7,8) from person limit 3;
68. select unix_timestamp(substr(id_card,7,8),'yyyyMMdd') from person limit 3;
69. select from_unixtime(unix_timestamp(substr(id_card,7,8),'yyyyMMdd')) from person limit 3;
71. select substr(from_unixtime(unix_timestamp(substr(id_card,7,8),'yyyyMMdd')),1,10) from person limit 3;
73. -- 流程控制语句
74.         -- case when
75. -- >90 a,80~90 b, 70~80 c, 60~70 的，<60  e
76. select
77. stu_id,course_id,
78. case
79.         when score >= 90 then 'A'
80.         when score >= 80 then 'B'
81.         when score >= 70 then 'c'
82.         when score >= 60 then 'D'
83.         else'E'
84. end as grade
85. From score;
87. -- if 三目运算 if(条件表达式,条件为真的返回结果,条件为假的返回结果)
88. select if(1=2,'托尼','玛丽') as `发型师`
90. -- 结合字符串函数 时间函数 流程控制函数 计算身份证信息
91. -- 根据身份证号 判断性别 身份证号 第十七位 奇数为男性 偶数为女性
92. select id_card, if(mod(substr(id_card,17,1),2) = 1,'精神小伙儿','扒蒜老妹儿') gender from person;
94. -- 根据身份证号 找出所有男性信息
95. select *, mod(substr(id_card,17,1),2) gender from person where mod(substr(id_card,17,1),2) = 1;
97. -- 根据身份证号 计算男性人数和女性人数
98. select
99. if(mod(substr(id_card,17,1),2) = 1,'精神小伙儿','扒蒜老妹儿') gender ,
100. count(*) gender_count
101. from person group by mod(substr(id_card,17,1),2) limit 10;
103. -- 根据身份证号 计算生日排序
104. select
105. date_format(from_unixtime( unix_timestamp(substr(id_card,7,8),'yyyyMMdd')),'yyyy-MM-dd') as `birthday`
106. from person
107. order by unix_timestamp(`birthday`,'yyyy-MM-dd') desc
109. -- 根据身份证号 计算年龄
110. -- 1、当前月份-出生月份 > 0 说明 已经过完生日 及 使用 当前年份 - 出生年份 = 年龄
111. -- 2、当前月份-出生月份 < 0 说明 未过生日 及 使用 当前年份 - 出生年份 -1 = 年龄
112. -- 3、当前月份-出生月份 = 0
113. -- 3.1、当前日-出生日 > 0 说明 已经过完生日 及 使用 当前年份 - 出生年份 = 年龄
114. -- 3.2、当前日-出生日 < 0 说明 未过生日 及 使用 当前年份 - 出生年份 -1 = 年龄
115. -- 3.3、当前日-出生日 = 0 说明 生日视作过完了 及 使用 当前年份 - 出生年份  = 年龄
116. select if(month(`current_date`()) - month(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))) > 0,
117.       year(`current_date`()) - year(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))),
118.       if(month(`current_date`()) - month(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))) < 0,
119.          year(`current_date`()) - year(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))) - 1,
120.          if(day(`current_date`()) - day(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))) > 0,
121.             year(`current_date`()) - year(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))),
122.             if(day(`current_date`()) - day(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))) < 0,
123.                year(`current_date`()) - year(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))) - 1,
124.                year(`current_date`()) - year(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd')))
125.                 )
126.              )
127.           )
128.        ) as `age`
129. from person;
131.        
132. -- 集合函数
133.         -- size 集合中元素的数量
134. select size(array(0,1,2,3,4,5));
135.         -- array 声明一个集合 数组
136. select array(0,1,2,3,4,5) as nums;
137.         -- array_contains 判断array中是否包含某元素  返回值是 布尔值 tury false
138. select array_contains(array(0,1,2,3,4,5),3) as num;
139.         -- sort_array 排序 目前只能升序
140. select sort_array(array(0,1,2,3,4,5));
142. -- struct 声明结构体属性名称
143. select struct('real_name','lhz','age','41');
144. -- {"col1":"real_name","col2":"lhz","col3":"age","col4":"41"}
145.         -- named_struct 声明结构体属性和值
146. select named_struct('real_name','lhz','age','41');
147. -- {"real_name":"lhz","age":"41"}
149. -- 集合函数
151.         -- map
152. select map('xz',1000,'js',800);
153. -- {"xz":1000,"js":800}
154.         -- map_keys 返回 map 中所有的 key
155. select map_keys(map('xz',1000,'js',800));
156.         -- map_values 返回 map 中所有的 value
157. select map_values(map('xz',1000,'js',800));
159.         -- if 三目运算
160. select if(条件表达式,条件为真表达式,条件为假表达式)

复制代码

练习
数据：

学生表
讲师表
课程表
分数表

学生表 student.csv

1. hdfs dfs -mkdir -p /quiz03/student
2. hdfs dfs -put /root/student.csv /quiz03/student

复制代码

1. load data local inpath '/root/student.csv' into table student;

复制代码

课程表 course.csv

1. hdfs dfs -mkdir -p /quiz03/course
2. hdfs dfs -put /root/course.csv /quiz03/course

复制代码

1. load data local inpath '/root/course.csv' into table course;

复制代码

分数表 score.csv

1. hdfs dfs -mkdir -p /quiz03/score
2. hdfs dfs -put /root/score.csv /quiz03/score

复制代码

1. load data local inpath '/root/course.csv' into table course;

复制代码

1. -- 学生表
2. create external table student (
3. stu_id string comment '学生ID',
4. stu_name string comment '学生姓名',
5. birthday string comment '出生年月',
6. gender string comment '学生性别'
7. )
8. row format delimited fields terminated by ','
9. lines terminated by '\n'
10. stored as textfile
11. location '/quiz03/student';
13. --教师表
14. create external table teacher (
15. tea_id string comment '课程ID',
16. tea_name string comment '教师名称'
17. )
18. row format delimited fields terminated by ','
19. lines terminated by '\n'
20. stored as textfile
21. location '/quiz03/teacher';
23. --课程表
24. create external table course (
25. coures_id string comment '课程ID',
26. coures_name string comment '课程名称',
27. tea_id string comment '讲师ID'
28. )
29. row format delimited fields terminated by ','
30. lines terminated by '\n'
31. stored as textfile
32. location '/quiz03/course';
34. --成绩表
35. create external table score (
36. stu_id string comment '学生ID',
37. coures_id string comment '课程ID',
38. score string comment '成绩'
39. )
40. row format delimited fields terminated by ','
41. lines terminated by '\n'
42. stored as textfile
43. location '/quiz03/score';

复制代码

综合练习

1. -- 查询所有学生信息
2. select * from student;
4. -- 查询周姓学生信息
5. select * from student where stu_name like '周%';
7. -- 查询周姓学生数量
8. select count(*) as zhou_count from student where stu_name like '周%';
10. -- 查询 学生ID 004 的分数 超过 85 的成绩
11. select * from score where stu_id = 004 and score > 85;
13. -- 查询 学生ID 004 的分数 超过 85 的成绩
14. select * from score where stu_id = 004 and score > 85;
16. -- 查询 学生程ID 004 的成绩降序
17. select * from score where stu_id = 01 order by score desc;
20. -- 查询 数学成绩不及格学生及其对应的成绩 安装学生ID排序
21. select stu.stu_id, stu.stu_name,s.score
22. from student stu join course c join score s
23. on  stu.stu_id = s.stu_id and c.course_id = s.course_id
24. and c.course_name = '数学' and s.score < 60
25. order by stu.stu_id;
27. -- 查询男女生人数
28. select gender,count(*) as gender_count from student group by gender;
30. -- 查询编号为 02 的课程平均成绩
31. select avg(score) from score where course_id = 02;
33. -- 查询每科课程平均成绩
34. select avg(score) from score group by course_id;
36. -- 查询参加考试学生人数
37. select count(distinct stu_id) as stu_count from score;
39. -- 查询每科有多少学生参加考试
40. select course_id,count(*) as stu_count from score group by course_id;
42. -- 查询未参加考试的学生信息
43. select stu_id,stu_name from student where stu_id not in (
44. select distinct stu.stu_id  from student stu left join course c left join score s
45. on stu.stu_id = s.stu_id and c.course_id = s.course_id
46. order by stu.stu_id
47. )
49. -- 查询平均成绩及格（60分）的学生的平均成绩
50. select stu_id, avg(score) avg_score
51. from score
52. group by stu_id
53. having avg_score >= 60;
55. -- 查询选修至少 4 门 以上课程学生的学号
56. select stu_id,count(course_id) course_count from score
57. group by stu_id
58. having course_count >= 4；
60. -- 查询姓氏相同学生名单 并且同姓人数大于 2 的姓氏
61. select first_name ,count(*) first_name_count from (
62. select stu_id,stu_name,substr(stu_name,1,1) as first_name
63. from student
64. ) ts
65. group by ts.first_name
66. having first_name_count > 1;
68. -- 查询每门功课的学生的平均成绩 按照平均成绩升序 平均成绩相同按照课程编号降序
69. select course_id, avg(score) avg_score
70. from score
71. group by course_id
72. order by avg_score,course_id desc;
74. -- 统计参加考试人数大于等于 15 的学科
75. select course_id,count(*) as stu_count from score group by course_id having stu_count > 15;
77. -- 查询学生总成绩并按照总成绩降序排序
78. select stu_id, sum(score) sum_score
79. from score
80. group by stu_id
81. order by sum_score desc;
83. -- 按照指定格式显示 stu_id 语文 数学 英语 选课数 平均成绩
84. select
85. s.stu_id,
86. sum(`if`(c.course_name='语文',score,0)) as `语文`,
87. sum(`if`(c.course_name='数学',score,0)) as `数学`,
88. sum(`if`(c.course_name='英语',score,0)) as `英语`,
89. count(s.course_id) as `选课数`,
90. avg(s.score) as `平均成绩`
91. from course c left join score s
92. on c.course_id = s.course_id
93. group by s.stu_id
94. order by `平均成绩` desc;
96. -- 查询一共参加了三门功课且其中一门为语文的学生id 和 姓名
97. select s.stu_id,stu_name from
98. (select t1.stu_id ,count(t1.course_id) course_count  from
99.         (select stu_id,course_id from score
100.                 where stu_id in ( select stu_id from score where course_id = "01")
101.         ) t1 group by  t1.stu_id having course_count >=3
102. ) t2 join student s on t2.stu_id = s.stu_id;
103. -- 分解
104. -- 查询该学生的姓名
105. select s.stu_id,stu_name from
106. -- 成绩表中学习科目数量 >=3 科的学生
107. (select t1.stu_id ,count(t1.course_id) course_count  from
108.         --  报名了语文的学生还报名了那些学科
109.         (select stu_id,course_id from score
110.                 where stu_id in (
111.             -- 查询报名了语文的学生ID
112.             select stu_id from score where course_id = "01"
113.         )
114.         ) t1 group by  t1.stu_id having course_count >=3
115. ) t2 join student s on t2.stu_id = s.stu_id;
117. -- 查询两门以上的课程不及格学生的学号及其平均成绩
118. -- 1、先按照学生分组 过滤出成绩低于60的数量 大于1
119. -- 2、计算所有学生的平均成绩
120. -- 3、两个子查询相互join
121. select  t1.stu_id,t2.avg_score from
122. (select stu_id, sum(if(score < 60, 1, 0)) as result from score group by stu_id having result > 1) t1
123. left join
124. (select stu_id,avg(score) as avg_score from score group by stu_id) t2 on t1.stu_id =t2.stu_id;
126. -- 查询所有学生的学号、姓名、选课数、总成绩
127. select
128.     stu.stu_id,stu.stu_name,count(s.course_id) count_course ,nvl(sum(s.score),0) total_score
129. from student stu left join score s on stu.stu_id = s.stu_id
130. group by stu.stu_id, stu.stu_name order by stu.stu_id;
132. -- 平均成绩大于 85 的所有学生的学号、姓名、平均成绩
133. select
134.     stu.stu_id,stu.stu_name ,nvl(avg(s.score),0) as `avg_score`
135. from student stu left join score s on stu.stu_id = s.stu_id
136. group by stu.stu_id, stu.stu_name having nvl(avg(s.score),0) > 85 order by stu.stu_id
138. -- 查询学生的选课情况：学号，姓名，课程号，课程名称
139. select student.stu_id,student.stu_name,c.course_id,c.course_name from student
140. right join score s on student.stu_id = s.stu_id
141. left join course c on s.course_id = c.course_id
143. -- 查询学生的没有选课情况：学号，姓名
144. select stu_id,stu_name from
145. (
146. select student.stu_id,student.stu_name, s.course_id from student
147. left join score s on student.stu_id = s.stu_id
148. left join course c on s.course_id = c.course_id
149. ) t where course_id is null
151. -- 查询出每门课程的及格人数和不及格人数
152. select c.course_id,course_name,pass,fail
153. from course c join
154. (
155. select
156. course_id,sum(if(score >= 60,1,0)) as `pass`, sum(if(score < 60,1,0)) as `fail`
157. from score group by course_id
158. ) t on c.course_id = t.course_id
160. -- 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名及课程信息
161. select t1.stu_id,s.stu_name,t1.course_id,c.course_name,t1.score from
162. (select * from score where course_id = '03' and score > 80) t1
163. left join student s on s.stu_id = t1.stu_id
164. left join course c on t1.course_id = c.course_id
166. -- 查询语文成绩低于平均分数的学生是谁，教师是谁
167. select t3.stu_id,t3.stu_name,t3.`avg_score`,t.tea_name from
168. (select t2.stu_id,t2.`avg_score`,s.stu_name,t2.course_id,c.tea_id from
169.     (select t1.stu_id,t1.course_id,t1.`avg_score` from
170.         (select stu_id,s.course_id, avg(score) as `avg_score` from score s right join
171.          (select course_id from course where course_name = '语文') t1 on t1.course_id = s.course_id
172.          group by stu_id,s.course_id) t1
173.         where t1.`avg_score` < (select avg(score) as `avg_score` from score s right join (select course_id from course where course_name = '语文') t1 on t1.course_id = s.course_id)
174.     ) t2 left join student s on t2.stu_id = s.stu_id
175.     left join course c on t2.course_id = c.course_id
176. )t3 left join teacher t on t3.tea_id = t.tea_id;
178. -- 查询所有学生总成绩和平均成绩，
179. -- 且他们的总成绩低于平均成绩的有多少个人，
180. -- 高于平均成绩的有多少人，
181. -- 低于平均成绩的男生和女生分别有多少人，
182. -- 且他们的任课老师是谁。
184. -- 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
185. -- 方法一
186. select course_id,
187.        concat(round((sum(`if`(score >= 85, 1, 0)) / count(*)) * 100, 2), '%')                      as `a`,
188.        concat(round((sum(`if`(score < 85, `if`(score >= 70, 1, 0), 0)) / count(*)) * 100, 2), '%') as `b`,
189.        concat(round((sum(`if`(score < 70, `if`(score >= 60, 1, 0), 0)) / count(*)) * 100, 2), '%') as `c`,
190.        concat(round((sum(`if`(score < 60, 1, 0)) / count(*)) * 100, 2), '%')                       as `d`
191. from score group by course_id;
192. -- 方法二
193. select course_id,
194.        concat(round((sum(`if`(score >= 85, 1, 0)) / count(*)) * 100, 2), '%')             as `a`,
195.        concat(round((sum(`if`(score between 70 and 84, 1, 0)) / count(*)) * 100, 2), '%') as `b`,
196.        concat(round((sum(`if`(score between 60 and 74, 1, 0)) / count(*)) * 100, 2), '%') as `c`,
197.        concat(round((sum(`if`(score < 60, 1, 0)) / count(*)) * 100, 2), '%')              as `d`
198. from score group by course_id;
200. -- 查询各科成绩最高分、最低分和平均分，以如下形式显示：
201. -- 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
202. -- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
203. select c.course_id                                                                    as `课程ID`,
204.        c.course_name                                                                  as `课程name`,
205.        max(score)                                                                     as `最高分`,
206.        min(score)                                                                     as `最低分`,
207.        round(avg(score), 2)                                                           as `平均分`,
208.        concat(round(sum(`if`(score >= 60, 1, 0)) / count(*) * 100, 2), '%')           as `及格率`,
209.        concat(round(sum(if(score between 70 and 79, 1, 0)) / count(*) * 100, 2), '%') as `中等率`,
210.        concat(round(sum(if(score between 80 and 89, 1, 0)) / count(*) * 100, 2), '%') as `优良率`,
211.        concat(round(sum(`if`(score >= 90, 1, 0)) / count(*) * 100, 2), '%')           as `优秀率`
212. from course c left join score s on c.course_id = s.course_id
213. group by c.course_id, c.course_name;
215. -- 查询每门课程的教师学生有谁，男生和女生的比例是多少，
216. select t1.course_id,t1.gender,concat(round((t1.count_gender / t2.count_course_student) * 100,2),'%') as `proportion` from
217. (
218. select  c.course_id, stu.gender,count(stu.gender) as `count_gender`
219. from course c left join score s on c.course_id = s.course_id left join student stu on s.stu_id = stu.stu_id
220. group by c.course_id, stu.gender
221. ) t1
222. join
223. (
224. select  c.course_id, count(*) as `count_course_student`
225. from course c left join score s on c.course_id = s.course_id left join student stu on s.stu_id = stu.stu_id
226. group by c.course_id
227. ) t2 on t1.course_id = t2.course_id
228. join score s on t1.course_id = s.course_id
230. -- 且他们的每门学科的成绩是男生比较优一些还是女生比较优一些，并且每门课程的最高分是谁。
231. select s.course_id,max(s.score) as `max_score`,min(s.score) as `min_score` from course join score s on course.course_id = s.course_id group by s.course_id
233. -- 课程编号为"01"且课程分数小于60，按分数降序排列的学生信息
234. select s.stu_id, stu.stu_name, stu.birthday, stu.gender,s.score
235. from score s join student stu on s.stu_id = stu.stu_id
236. where s.score < 60  order by s.score desc
238. -- 查询所有课程成绩在70分以上的学生的姓名、课程名称和分数，按分数升序
239. select stu.stu_name, c.course_name, s2.score
240. from student stu join
241. (select s.stu_id, sum(`if`(s.score >= 70, 0, 1)) as `is_ok` from score s group by s.stu_id having is_ok = 0) t1
242. on stu.stu_id = t1.stu_id left join score s2 on stu.stu_id = s2.stu_id left join course c on s2.course_id = c.course_id
243. order by s2.score
245. -- 查询某学生不同课程的成绩相同的学生编号、课程编号、学生成绩
246. select s1.stu_id,collect_list(s1.course_id) as course_id,collect_set(s1.score) as score
247. from score s1 join score s2 on s1.stu_id = s2.stu_id
248. and s1.course_id != s2.course_id
249. and s1.score == s2.score
250. group by s1.stu_id

复制代码

1. -- 查询语文成绩低于平均分数的学生是谁，教师是谁
3. select stu.stu_name,tea_name from student stu left join score s left join course c left join teacher t where c.course_nam = "语文" and s.

复制代码

1. -- 结合字符串函数 时间函数 流程控制函数 计算身份证信息
2. -- 根据身份证号 判断性别 身份证号 第十七位 奇数为男性 偶数为女性
3. select id_card, if(mod(substr(id_card,17,1),2) = 1,'精神小伙儿','扒蒜老妹儿') gender from person;
5. -- 根据身份证号 找出所有男性信息
6. select *, mod(substr(id_card,17,1),2) gender from person where mod(substr(id_card,17,1),2) = 1;
8. -- 根据身份证号 计算男性人数和女性人数
9. select
10. if(mod(substr(id_card,17,1),2) = 1,'精神小伙儿','扒蒜老妹儿') gender ,
11. count(*) gender_count
12. from person group by mod(substr(id_card,17,1),2) limit 10;
14. -- 根据身份证号 计算生日排序
15. select
16. date_format(from_unixtime( unix_timestamp(substr(id_card,7,8),'yyyyMMdd')),'yyyy-MM-dd') as `birthday`
17. from person
18. order by unix_timestamp(`birthday`,'yyyy-MM-dd') desc
20. -- 根据身份证号 计算年龄
21. -- 1、当前月份-出生月份 > 0 说明 已经过完生日 及 使用 当前年份 - 出生年份 = 年龄
22. -- 2、当前月份-出生月份 < 0 说明 未过生日 及 使用 当前年份 - 出生年份 -1 = 年龄
23. -- 3、当前月份-出生月份 = 0
24. -- 3.1、当前日-出生日 > 0 说明 已经过完生日 及 使用 当前年份 - 出生年份 = 年龄
25. -- 3.2、当前日-出生日 < 0 说明 未过生日 及 使用 当前年份 - 出生年份 -1 = 年龄
26. -- 3.3、当前日-出生日 = 0 说明 生日视作过完了 及 使用 当前年份 - 出生年份  = 年龄
27. select if(month(`current_date`()) - month(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))) > 0,
28.       year(`current_date`()) - year(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))),
29.       if(month(`current_date`()) - month(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))) < 0,
30.          year(`current_date`()) - year(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))) - 1,
31.          if(day(`current_date`()) - day(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))) > 0,
32.             year(`current_date`()) - year(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))),
33.             if(day(`current_date`()) - day(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))) < 0,
34.                year(`current_date`()) - year(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd'))) - 1,
35.                year(`current_date`()) - year(from_unixtime(unix_timestamp(substr(id_card, 7, 8), 'yyyyMMdd')))
36.                 )
37.              )
38.           )
39.        ) as `age`
40. from person;

复制代码

高级聚合函数

分组排序取TopN

1. -- row_number() over () 连续序号
2. -- over()里头的分组以及排序的执行晚于 where 、group by、order by 的执行。
3. -- partition by 分区
4. select *,row_number() over () as `num` from score;
6. -- 查询各科成绩前三名的学生
7. SELECT a.stu_id,a.course_id,a.score
8. FROM score a
9. LEFT JOIN score b ON a.course_id = b.course_id
10. AND a.score <= b.score
11. GROUP BY a.stu_id,a.course_id,a.score
12. HAVING COUNT( b.stu_id ) <= 3
13. ORDER BY a.course_id,a.score DESC;
15. SELECT S1.course_id,s1.stu_id,s1.score FROM score s1
16. WHERE (
17. SELECT COUNT(*) FROM score s2
18. WHERE s2.course_id=s1.course_id AND s2.score > s1.score
19. ) < 3 ORDER BY s1.course_id,s1.score DESC;
22. select * from
23. (
24. select course_id,stu_id,score,
25. row_number() over (partition by course_id order by score desc ) as `num`
26. from score
27. ) t where t.num <= 3;
29. -- rank() over () 排名 跳跃排序 序号不是连续的
30. select * from
31. (
32. select course_id,stu_id,score,
33. rank() over (partition by course_id order by score desc ) as `ranking`
34. from score
35. ) t;
37. -- dense_rank() over () 排名 连续排序
38. select * from
39. (
40. select course_id,stu_id,score,
41. dense_rank() over (partition by course_id order by score desc ) as `ranking`
42. from score
43. ) t;

复制代码

分区表

模拟数据

身份证前六位

身份证前六位

1. -- 行转列
2. -- collect_list  行转列 有序可重复 结果是个集合
3. select collect_list(emp_job) as `job` from employee;
5. -- collect_set 行转列 过滤重复 结果是个集合
6. select collect_list(emp_job) as `job` from employee;
8. -- concat_ws 把集合转字符串
9. concat_ws('分隔符'，集合)
10. select concat_ws(',',collect_set(emp_job)) as `job` from emp;
12. -- split 把字符串转为集合
13. concat_ws(字符串，'分隔符')
14. select split(concat_ws(',',collect_set(emp_job)))as `job` from emp;

复制代码

pom.xml

1. hdfs dfs -mkdir -p /quiz04/employee
2. hdfs dfs -put /root/employee.csv /quiz04/employee

复制代码

工具类

1. create external table employee(
2.     name string comment '姓名',
3.     sex  string comment '性别',
4.     birthday string comment '出生年月',
5.     hiredate string comment '入职日期',
6.     job string comment '岗位',
7.     salary int comment '薪资',
8.     bonus int comment '奖金',
9.     friends array<string> comment '朋友',
10.     children map<string,int> comment '孩子'
11. )
12. row format delimited fields terminated by ','
13. collection items terminated by '_'
14. map keys terminated by ':'
15. lines terminated by '\n'
16. stored as textfile
17. location '/quiz04/employee';

复制代码

创建数据库

1. load data local inpath '/root/employee.csv' into table employee;

复制代码

1. -- explode
2. select explode(array('java','python','scala','go')) as course;
3. select explode(map('name','李昊哲','gender','1')) as (key,value);
5. -- posexplode
6. select posexplode(array('java','python','scala','go')) as (pos,course);
8. -- inline
9. select inline(array(named_struct('id',1,'name','李昊哲','gender','1'),
10.                    named_struct('id',2,'name','李哲','gender','0'),
11.                    named_struct('id',3,'name','李大宝','gender','1'))) as (id,name,gender);
13. -- lateral view   
14. select * from employee lateral view explode(friends) t as friend;
16. select e.name,e.friends,t1.friend from employee e lateral view explode(friends) t1 as `friend`;
18. select * from employee e lateral view explode(children) t1 as `children_name`,`children_friend_count`;
20. select e.name,e.children,t1.children_name,t1.nvl(t2.children_friend_count,0) from employee e
21. lateral view explode(children) t1 as `children_name`,`children_friend_count`;
23. select e.name,e.friends,e.children,t1.friend,t2.children_name,nvl(t2.children_friend_count,0) from employee e
24. lateral view explode(friends) t1 as `friend`
25. lateral view explode(children) t2 as `children_name`,`children_friend_count`;
27. -- lateral view outer

复制代码

内部分区表

内部分区表

1. hdfs dfs -mkdir -p /quiz04/movie
2. hdfs dfs -put /root/movie.txt /quiz04/movie

复制代码

导入数据

1. create external table movie(
2.     name string comment '电影名称',
3.     category string comment '电影分类'
4. )
5. row format delimited fields terminated by '-'
6. lines terminated by '\n'
7. stored as textfile
8. location '/quiz04/movie';

复制代码

外部分区表

创建外部分区表关联目录

1. load data local inpath '/root/employee.csv' into table employee;/partition_2

复制代码

创建外部分区表

1. -- 根据上述电影信息表，统计各分类的电影数量
2. select cate,count(name) as `quantity` from movie
3. lateral view explode(split(category,',')) tmp as cate
4. group by cate;

复制代码

导入数据

1. -- 统计岗位数量
2. select count(distinct emp_job) from emp;
3. select count(*) from (select emp_job from emp group by emp_job) t;

复制代码

多重内部分区表

创建内部多重内部分区表

1. Function(arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])
2. -- 其中Function(arg1,..., argn) 可以是下面分类中的任意一个
3.     -- 聚合函数：比如sum max min avg count等
4.     -- 排序函数：比如row_number rank dense_rank等
5.     -- 分析函数：比如lead lag first_value last_value等
6. -- OVER [PARTITION BY <...>] 类似于group by 用于指定分组  每个分组你可以把它叫做窗口
7. -- 如果没有PARTITION BY 那么整张表的所有行就是一组
8. -- [ORDER BY <....>]  用于指定每个分组内的数据排序规则 支持ASC、DESC
9. -- [<window_expression>] 用于指定每个窗口中 操作的数据范围 默认是窗口中所有行

复制代码

导入数据

1. hdfs dfs -mkdir /quiz04/order
2. hdfs dfs -put /root/order.csv /quiz04/order

复制代码

多重外部分区表

创建多重外部分区表关联目录

1. load data local inpath '/root/employee.csv' into table employee;/partition_4

复制代码

创建多重外部分区表

1. load data local inpath '/root/order.csv' into table order;

复制代码

1. -- 统计每个用户截至每次下单的累计下单总额
2. select *,
3. sum(order_amount) over
4.     (partition by user_id order by order_date rows between unbounded preceding and current row) `sum_order_amount`
5. from `order`
7. -- 统计每个用户截至每次下单的当月累积下单总额
8. select *,
9.     sum(order_amount)
10.             over(partition by user_id,substr(order_date,1,7) order by order_date
11.                     rows between unbounded preceding and current row) `sum_order_amount`
12. from `order`

复制代码

动态分区

1. -- 统计每个用户每次下单距离上次下单相隔的天数（首次下单按0天算）
2. select user_id,user_name,order_id,order_date,datediff(order_date,last_order_date) `diff_date` from
3. (select *,
4. lag(order_date,1,order_date) over (partition by user_id order by order_date) `last_order_date` from order`) t

复制代码

1. -- 查询所有下单记录以及每个用户的每个下单记录所在月份的首/末次下单日期
2. select *,
3. first_value(order_date) over(partition by user_id,substr(order_date,1,7) order by order_date) `first_date`,
4. last_value(order_date) over (partition by user_id,substr(order_date,1,7) order by order_date
5. rows between unbounded preceding and unbounded following) `last_date`
6. from `order`

复制代码

1. -- 为每个用户的所有下单记录按照订单金额进行排名

复制代码

1. hdfs dfs -mkdir -p /tmall/user
2. hdfs dfs -put /root/user.csv /tmall/user

复制代码

1. create external table `user` (
2.         `user_id`  string COMMENT '用户id',
3.     `gender`   string COMMENT '性别',
4.     `birthday` string COMMENT '生日'
5. ) COMMENT '用户信息表'
6. row format delimited fields terminated by ','
7. lines terminated by '\n'
8. stored as textfile
9. location '/tmall/user';

复制代码

1. load data local inpath '/root/user.csv' into table user;

复制代码

分桶

数据抽样 提高join查询效率

• 创建普通表并导入数据
  
• 开启分桶
  
• 查询普通表将，将查询结果插入桶
  
• 从桶中查询数据
  

创建普通表并导入数据

1. hdfs dfs -mkdir -p /tmall/sku
2. hdfs dfs -put /root/sku.csv /tmall/sku

复制代码

1. create external table sku (
2.         `sku_id`      string COMMENT '商品id',
3.     `name`        string COMMENT '商品名称',
4.     `category_id` string COMMENT '所属分类id',
5.     `from_date`   string COMMENT '上架日期',
6.     `price`       double COMMENT '商品单价'
7. ) COMMENT '商品信息表'
8. row format delimited fields terminated by ','
9. lines terminated by '\n'
10. stored as textfile
11. location '/tmall/sku';

复制代码

开启分桶

1. load data local inpath '/root/sku.csv' into table sku;

复制代码

创建桶表

1. hdfs dfs -mkdir -p /tmall/category
2. hdfs dfs -put /root/category.csv /tmall/category

复制代码

载入数据到桶表

1. load data local inpath '/root/sku.csv' into table sku;insert into table bucket_tb select id from bucket_source where id is not null;

复制代码

1. load data local inpath '/root/category.csv' into table category;

复制代码

视图

1. hdfs dfs -mkdir -p /tmall/order
2. hdfs dfs -put /root/order.csv /tmall/order

复制代码

存储与压缩

文件格式

行式存储与列式存储
hive表中的数据选择一个合适的文件格式，对于高性能查询是比较有益的
行式存储：text file，sequence file
列式存储：ORC、Parquet

text file：

hive默认采用text file 文件存储格式；

1. create external table `order` (
2.         `order_id`     string COMMENT '订单id',
3.     `user_id`      string COMMENT '用户id',
4.     `create_date`  string COMMENT '下单日期',
5.     `total_amount` decimal(16, 2) COMMENT '订单总金额'
6. ) COMMENT '订单信息表'
7. row format delimited fields terminated by ','
8. lines terminated by '\n'
9. stored as textfile
10. location '/tmall/order';

复制代码

1. load data local inpath '/root/order.csv' into table order;

复制代码

1. hdfs dfs -mkdir -p /tmall/order_detail
2. hdfs dfs -put /root/order_detail.csv /tmall/order_detail

复制代码

sequence file

sequence file 文件 是Hadoop用来存储二进制形式的的 key : value 键值对而设计的一种平面文件  flatmap

1. create external table order_detail (
2.         `order_detail_id` string COMMENT '订单明细id',
3.     `order_id`        string COMMENT '订单id',
4.     `sku_id`          string COMMENT '商品id',
5.     `create_date`     string COMMENT '下单日期',
6.     `price`           decimal(16, 2) COMMENT '下单时的商品单价',
7.     `sku_num`         int COMMENT '下单商品件数'
8. ) COMMENT '订单明细表'
9. row format delimited fields terminated by ','
10. lines terminated by '\n'
11. stored as textfile
12. location '/tmall/order_detail';

复制代码

1. load data local inpath '/root/order_detail.csv' into table order_detail;

复制代码

1. hdfs dfs -mkdir -p /tmall/user_login
2. hdfs dfs -put /root/user_login.csv /tmall/user_login

复制代码

ORC

1. create external table user_login (
2.         `user_id`    string comment '用户id',
3.     `ip_address` string comment 'ip地址',
4.     `login_ts`   string comment '登录时间',
5.     `logout_ts`  string comment '登出时间'
6. ) COMMENT '登录明细表'
7. row format delimited fields terminated by ','
8. lines terminated by '\n'
9. stored as textfile
10. location '/tmall/user_login';

复制代码

1. load data local inpath '/root/user_login.csv' into table user_login;

复制代码

1. hdfs dfs -mkdir -p /tmall/sku_price_modify_detail
2. hdfs dfs -put /root/sku_price_modify_detail.csv /tmall/sku_price_modify_detail

复制代码

1. create external table sku_price_modify_detail (
2.         `sku_id`      string comment '商品id',
3.     `new_price`   decimal(16, 2) comment '更改后的价格',
4.     `change_date` string comment '变动日期'
5. ) COMMENT '商品价格变更明细表'
6. row format delimited fields terminated by ','
7. lines terminated by '\n'
8. stored as textfile
9. location '/tmall/sku_price_modify_detail';

复制代码

Parquet

1. load data local inpath '/root/sku_price_modify_detail.csv' into table sku_price_modify_detail;

复制代码

1. create external table sku_price_modify_detail (
2.         `sku_id`      string comment '商品id',
3.     `new_price`   decimal(16, 2) comment '更改后的价格',
4.     `change_date` string comment '变动日期'
5. ) COMMENT '商品价格变更明细表'
6. row format delimited fields terminated by ','
7. lines terminated by '\n'
8. stored as textfile
9. location '/tmall/sku_price_modify_detail';

复制代码

rcfile

1. create external table delivery (
2.         `delivery_id` string comment '配送单id',
3.     `order_id`    string comment '订单id',
4.     `user_id`     string comment '用户id',
5.     `order_date`  string comment '下单日期',
6.     `custom_date` string comment '期望配送日期'
7. ) COMMENT '配送信息表'
8. row format delimited fields terminated by ','
9. lines terminated by '\n'
10. stored as textfile
11. location '/tmall/delivery';

复制代码

1. load data local inpath '/root/delivery.csv' into table delivery;

复制代码

1. hdfs dfs -mkdir -p /tmall/friendship
2. hdfs dfs -put /root/friendship.csv /tmall/friendship

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！