一、原题描述
Table: Failed
1+--------------+---------+ 2| Column Name | Type | 3+--------------+---------+ 4| fail_date | date | 5+--------------+---------+ 6Primary key for this table is fail_date. 7Failed table contains the days of failed tasks.
Table: Succeeded
1+--------------+---------+ 2| Column Name | Type | 3+--------------+---------+ 4| success_date | date | 5+--------------+---------+ 6Primary key for this table is success_date. 7Succeeded table contains the days of succeeded tasks.
A system is running one task every day. Every task is independent of the previous tasks. The tasks can fail or succeed.
Write an SQL query to generate a report of period_state for each continuous interval of days in the period from 2019-01-01 to 2019-12-31.
period_state is 'failed' if tasks in this interval failed or 'succeeded' if tasks in this interval succeeded. Interval of days are retrieved as start_date and end_date.
Order result by start_date.
The query result format is in the following example:
Failed table: +-------------------+ | fail_date | +-------------------+ | 2018-12-28 | | 2018-12-29 | | 2019-01-04 | | 2019-01-05 | +-------------------+
Succeeded table: +-------------------+ | success_date | +-------------------+ | 2018-12-30 | | 2018-12-31 | | 2019-01-01 | | 2019-01-02 | | 2019-01-03 | | 2019-01-06 | +-------------------+
Result table: +--------------+--------------+--------------+ | period_state | start date | end date | +--------------+--------------+--------------+ | succeeded | 2019-01-01 | 2019-01-03 | | failed | 2019-01-04 | 2019-01-05 | | succeeded | 2019-01-06 | 2019-01-06 | +--------------+--------------+--------------+
The report ignored the system state in 2018 as we care about the system in the period 2019-01-01 to 2019-12-31. From 2019-01-01 to 2019-01-03 all tasks succeeded and the system state was "succeeded". From 2019-01-04 to 2019-01-05 all tasks failed and system state was "failed". From 2019-01-06 to 2019-01-06 all tasks succeeded and system state was "succeeded".
二、简要翻译
两张表， `Failed 和 Succeeded，用来记录一个每日定时跑的系统任务的失败和成功。要求返回一张结果表，按顺序展示该任务失败和成功的连续时间段以及起止时间。
**三、SQL分析
**0、建表语句
`
1CREATE TABLE IF NOT EXISTS `Failed`( 2`fail_date` DATE, 3PRIMARY KEY ( `fail_date` ) 4)ENGINE=INNODB DEFAULT CHARSET=utf8; 5 6CREATE TABLE IF NOT EXISTS `Succeeded`( 7 `success_date` DATE, 8 PRIMARY KEY ( `success_date` ) 9)ENGINE=INNODB DEFAULT CHARSET=utf8; 10 11INSERT INTO Succeeded(success_date) VALUES('2018-12-30'),('2018-12-31'),('2019-01-01'),('2019-01-02'),('2019-01-03'),('2019-01-06'); 12 13INSERT INTO Failed(fail_date) VALUES('2018-12-28'),('2018-12-29'),('2019-01-04'),('2019-01-05'),('2019-01-07');
1、将两张表里面的数据按照时间是否连续进行分组。
1 1 #失败表 2 2 SELECT 3 3 fail_date, 4 4 IF( 5 5 @date = DATE_SUB(fail_date, INTERVAL 1 DAY), 6 6 @rank := @rank, 7 7 @rank := @rank + 1 8 8 ) AS rank, 9 9 @date := fail_date 1010 FROM 1111 Failed, 1212 (SELECT 1313 @date := NULL, 1414 @rank := 0) r 1515 WHERE fail_date > '2018-12-31' 1616 AND fail_date < '2020-01-01' 1717 ORDER BY fail_date； 1818 1919 #成功表 2020 SELECT 2121 success_date, 2222 IF( 2323 @date = DATE_SUB(success_date, INTERVAL 1 DAY), 2424 @rank := @rank, 2525 @rank := @rank + 1 2626 ) AS rank, 2727 @date := success_date 2828 FROM 2929 Succeeded, 3030 (SELECT 3131 @date := NULL, 3232 @rank := 0) r 3333 WHERE success_date > '2018-12-31' 3434 AND success_date < '2020-01-01' 3535 ORDER BY success_date
2、将上一步的两张表分组查询最大日期，最小日期，然后合并再排序
1 1 SELECT 2 2 * 3 3 FROM 4 4 (SELECT 5 5 'succeeded' AS period_state, 6 6 MIN(success_date) AS start_date, 7 7 MAX(success_date) AS end_date 8 8 FROM 9 9 (SELECT 1010 success_date, 1111 IF( 1212 @date = DATE_SUB(success_date, INTERVAL 1 DAY), 1313 @rank := @rank, 1414 @rank := @rank + 1 1515 ) AS rank, 1616 @date := success_date 1717 FROM 1818 Succeeded, 1919 (SELECT 2020 @date := NULL, 2121 @rank := 0) r 2222 WHERE success_date > '2018-12-31' 2323 AND success_date < '2020-01-01' 2424 ORDER BY success_date) t1 2525 GROUP BY t1.rank 2626 UNION 2727 ALL 2828 SELECT 2929 'failed' AS period_state, 3030 MIN(fail_date) AS start_date, 3131 MAX(fail_date) AS end_date 3232 FROM 3333 (SELECT 3434 fail_date, 3535 IF( 3636 @date = DATE_SUB(fail_date, INTERVAL 1 DAY), 3737 @rank := @rank, 3838 @rank := @rank + 1 3939 ) AS rank, 4040 @date := fail_date 4141 FROM 4242 Failed, 4343 (SELECT 4444 @date := NULL, 4545 @rank := 0) r 4646 WHERE fail_date > '2018-12-31' 4747 AND fail_date < '2020-01-01' 4848 ORDER BY fail_date) t2 4949 GROUP BY t2.rank) temp 5050 ORDER BY start_date ;
`**四、HIVE版本
0.1 建表语句 略
0.2、xml依赖
**`
1<properties> 2 <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> 3 </properties> 4 <dependencies> 5 6 <dependency> 7 <groupId>org.apache.hive</groupId> 8 <artifactId>hive-exec</artifactId> 9 </dependency> 10 11 <dependency> 12 <groupId>com.janeluo</groupId> 13 <artifactId>ikanalyzer</artifactId> 14 <version>2012_u6</version> 15 </dependency> 16 <dependency> 17 <groupId>org.apache.lucene</groupId> 18 <artifactId>lucene-core</artifactId> 19 <version>4.7.2</version> 20 </dependency> 21 22 <dependency> 23 <groupId>junit</groupId> 24 <artifactId>junit</artifactId> 25 <scope>test</scope> 26 </dependency> 27 <dependency> 28 <groupId>org.junit.jupiter</groupId> 29 <artifactId>junit-jupiter-api</artifactId> 30 <version>RELEASE</version> 31 <scope>compile</scope> 32 </dependency> 33 </dependencies> 34 <dependencyManagement> 35 <dependencies> 36 <dependency> 37 <groupId>org.apache.hive</groupId> 38 <artifactId>hive-exec</artifactId> 39 <version>2.1.0</version> 40 </dependency> 41 <dependency> 42 <groupId>junit</groupId> 43 <artifactId>junit</artifactId> 44 <version>4.12</version> 45 </dependency> 46 <dependency> 47 <groupId>jdk.tools</groupId> 48 <artifactId>jdk.tools</artifactId> 49 <version>1.8</version> 50 </dependency> 51 </dependencies> 52 </dependencyManagement> 53 <build> 54 <plugins> 55 <plugin> 56 <groupId>org.apache.maven.plugins</groupId> 57 <artifactId>maven-compiler-plugin</artifactId> 58 <version>3.1</version> 59 <configuration> 60 <source>1.8</source> 61 <target>1.8</target> 62 </configuration> 63 </plugin> 64 <plugin> 65 <artifactId>maven-assembly-plugin</artifactId> 66 <configuration> 67 <descriptorRefs> 68 <descriptorRef>jar-with-dependencies</descriptorRef> 69 </descriptorRefs> 70 </configuration> 71 <executions> 72 <execution> 73 <id>make-assembly</id> 74 <phase>package</phase> 75 <goals> 76 <goal>single</goal> 77 </goals> 78 </execution> 79 </executions> 80 </plugin> 81 </plugins> 82 </build>
1、用自定义的UDTF代替三里面的步骤1。
1 1 package com.hive.udf; 2 2 3 3 import java.text.ParseException; 4 4 import java.text.SimpleDateFormat; 5 5 import java.util.ArrayList; 6 6 import java.util.Calendar; 7 7 import java.util.Date; 8 8 import java.util.GregorianCalendar; 9 9 1010 import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF; 1111 import org.apache.hadoop.hive.ql.exec.UDFArgumentException; 1212 import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException; 1313 import org.apache.hadoop.hive.ql.metadata.HiveException; 1414 import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector; 1515 import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory; 1616 import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector; 1717 import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory; 1818 import org.junit.jupiter.api.Test; 1919 2020 public class TestGenericUDTF extends GenericUDTF { 2121 private Date startDate = null; 2222 private Date lastDate = null; 2323 private Date lastEmitDate = null; 2424 private static SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd"); 2525 2626 @Override 2727 public void close() throws HiveException { 2828 if (startDate != null && lastDate != null && lastEmitDate != startDate) { 2929 String[] result = new String[]{s.format(startDate), s.format(lastDate)}; 3030 forward(result); 3131 } 3232 } 3333 3434 @Override 3535 public StructObjectInspector initialize(ObjectInspector[] args) 3636 throws UDFArgumentException { 3737 if (args.length != 1) { 3838 throw new UDFArgumentLengthException("TestGenericUDTF takes only one argument"); 3939 } 4040 if (args[0].getCategory() != ObjectInspector.Category.PRIMITIVE) { 4141 throw new UDFArgumentException("TestGenericUDTF takes string as a parameter"); 4242 } 4343 4444 ArrayList<String> fieldNames = new ArrayList<String>(); 4545 ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>(); 4646 fieldNames.add("start_date"); 4747 fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector); 4848 fieldNames.add("end_date"); 4949 fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector); 5050 5151 return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs); 5252 } 5353 5454 @Override 5555 public void process(Object[] args) throws HiveException { 5656 String dateStr = args[0].toString(); 5757 Date date = null; 5858 try { 5959 date = s.parse(dateStr); 6060 } catch (ParseException e) { 6161 e.printStackTrace(); 6262 } 6363 if (date == null) { 6464 if (startDate != null && lastDate != null && lastEmitDate != startDate) { 6565 String[] result = new String[]{startDate.toString(), lastDate.toString()}; 6666 forward(result); 6767 lastEmitDate = startDate; 6868 } 6969 startDate = null; 7070 lastDate = null; 7171 return; 7272 } 7373 if (startDate == null) { 7474 startDate = date; 7575 } else if (lastDate != null) { 7676 GregorianCalendar calander = new GregorianCalendar(); 7777 calander.setTime(lastDate); 7878 calander.add(Calendar.DATE, 1); 7979 Date time = calander.getTime(); 8080 if (!time.equals(date)) { 8181 String[] result = new String[]{s.format(startDate), s.format(lastDate)}; 8282 forward(result); 8383 lastEmitDate = startDate; 8484 startDate = date; 8585 } 8686 } 8787 lastDate = date; 8888 } 8989 }
2、打肥jar包上传服务器，使用该udtf。
1add JAR /home/hadoop/hive-dateudtf-jar-with-dependencies.jar; 2 3create temporary function dateUDTF as 'com.hive.udf.TestGenericUDTF';