一、原题描述
Table: Failed
+--------------+---------+ | Column Name | Type | +--------------+---------+ | fail_date | date | +--------------+---------+ Primary key for this table is fail_date. Failed table contains the days of failed tasks.
Table: Succeeded
+--------------+---------+ | Column Name | Type | +--------------+---------+ | success_date | date | +--------------+---------+ Primary key for this table is success_date. Succeeded 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、建表语句
`
CREATE TABLE IF NOT EXISTS `Failed`( `fail_date` DATE, PRIMARY KEY ( `fail_date` ) )ENGINE=INNODB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `Succeeded`( `success_date` DATE, PRIMARY KEY ( `success_date` ) )ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO Succeeded(success_date) VALUES('2018-12-30'),('2018-12-31'),('2019-01-01'),('2019-01-02'),('2019-01-03'),('2019-01-06'); INSERT INTO Failed(fail_date) VALUES('2018-12-28'),('2018-12-29'),('2019-01-04'),('2019-01-05'),('2019-01-07');
1、将两张表里面的数据按照时间是否连续进行分组。
1 #失败表 2 SELECT 3 fail_date, 4 IF( 5 @date = DATE_SUB(fail_date, INTERVAL 1 DAY), 6 @rank := @rank, 7 @rank := @rank + 1 8 ) AS rank, 9 @date := fail_date 10 FROM 11 Failed, 12 (SELECT 13 @date := NULL, 14 @rank := 0) r 15 WHERE fail_date > '2018-12-31' 16 AND fail_date < '2020-01-01' 17 ORDER BY fail_date； 18 19 #成功表 20 SELECT 21 success_date, 22 IF( 23 @date = DATE_SUB(success_date, INTERVAL 1 DAY), 24 @rank := @rank, 25 @rank := @rank + 1 26 ) AS rank, 27 @date := success_date 28 FROM 29 Succeeded, 30 (SELECT 31 @date := NULL, 32 @rank := 0) r 33 WHERE success_date > '2018-12-31' 34 AND success_date < '2020-01-01' 35 ORDER BY success_date
2、将上一步的两张表分组查询最大日期，最小日期，然后合并再排序
1 SELECT 2 * 3 FROM 4 (SELECT 5 'succeeded' AS period_state, 6 MIN(success_date) AS start_date, 7 MAX(success_date) AS end_date 8 FROM 9 (SELECT 10 success_date, 11 IF( 12 @date = DATE_SUB(success_date, INTERVAL 1 DAY), 13 @rank := @rank, 14 @rank := @rank + 1 15 ) AS rank, 16 @date := success_date 17 FROM 18 Succeeded, 19 (SELECT 20 @date := NULL, 21 @rank := 0) r 22 WHERE success_date > '2018-12-31' 23 AND success_date < '2020-01-01' 24 ORDER BY success_date) t1 25 GROUP BY t1.rank 26 UNION 27 ALL 28 SELECT 29 'failed' AS period_state, 30 MIN(fail_date) AS start_date, 31 MAX(fail_date) AS end_date 32 FROM 33 (SELECT 34 fail_date, 35 IF( 36 @date = DATE_SUB(fail_date, INTERVAL 1 DAY), 37 @rank := @rank, 38 @rank := @rank + 1 39 ) AS rank, 40 @date := fail_date 41 FROM 42 Failed, 43 (SELECT 44 @date := NULL, 45 @rank := 0) r 46 WHERE fail_date > '2018-12-31' 47 AND fail_date < '2020-01-01' 48 ORDER BY fail_date) t2 49 GROUP BY t2.rank) temp 50 ORDER BY start_date ;
`**四、HIVE版本
0.1 建表语句 略
0.2、xml依赖
**`
<properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> </properties> <dependencies> <dependency> <groupId>org.apache.hive</groupId> <artifactId>hive-exec</artifactId> </dependency> <dependency> <groupId>com.janeluo</groupId> <artifactId>ikanalyzer</artifactId> <version>2012_u6</version> </dependency> <dependency> <groupId>org.apache.lucene</groupId> <artifactId>lucene-core</artifactId> <version>4.7.2</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.junit.jupiter</groupId> <artifactId>junit-jupiter-api</artifactId> <version>RELEASE</version> <scope>compile</scope> </dependency> </dependencies> <dependencyManagement> <dependencies> <dependency> <groupId>org.apache.hive</groupId> <artifactId>hive-exec</artifactId> <version>2.1.0</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> <dependency> <groupId>jdk.tools</groupId> <artifactId>jdk.tools</artifactId> <version>1.8</version> </dependency> </dependencies> </dependencyManagement> <build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <version>3.1</version> <configuration> <source>1.8</source> <target>1.8</target> </configuration> </plugin> <plugin> <artifactId>maven-assembly-plugin</artifactId> <configuration> <descriptorRefs> <descriptorRef>jar-with-dependencies</descriptorRef> </descriptorRefs> </configuration> <executions> <execution> <id>make-assembly</id> <phase>package</phase> <goals> <goal>single</goal> </goals> </execution> </executions> </plugin> </plugins> </build>
1、用自定义的UDTF代替三里面的步骤1。
1 package com.hive.udf; 2 3 import java.text.ParseException; 4 import java.text.SimpleDateFormat; 5 import java.util.ArrayList; 6 import java.util.Calendar; 7 import java.util.Date; 8 import java.util.GregorianCalendar; 9 10 import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF; 11 import org.apache.hadoop.hive.ql.exec.UDFArgumentException; 12 import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException; 13 import org.apache.hadoop.hive.ql.metadata.HiveException; 14 import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector; 15 import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory; 16 import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector; 17 import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory; 18 import org.junit.jupiter.api.Test; 19 20 public class TestGenericUDTF extends GenericUDTF { 21 private Date startDate = null; 22 private Date lastDate = null; 23 private Date lastEmitDate = null; 24 private static SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd"); 25 26 @Override 27 public void close() throws HiveException { 28 if (startDate != null && lastDate != null && lastEmitDate != startDate) { 29 String[] result = new String[]{s.format(startDate), s.format(lastDate)}; 30 forward(result); 31 } 32 } 33 34 @Override 35 public StructObjectInspector initialize(ObjectInspector[] args) 36 throws UDFArgumentException { 37 if (args.length != 1) { 38 throw new UDFArgumentLengthException("TestGenericUDTF takes only one argument"); 39 } 40 if (args[0].getCategory() != ObjectInspector.Category.PRIMITIVE) { 41 throw new UDFArgumentException("TestGenericUDTF takes string as a parameter"); 42 } 43 44 ArrayList<String> fieldNames = new ArrayList<String>(); 45 ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>(); 46 fieldNames.add("start_date"); 47 fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector); 48 fieldNames.add("end_date"); 49 fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector); 50 51 return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs); 52 } 53 54 @Override 55 public void process(Object[] args) throws HiveException { 56 String dateStr = args[0].toString(); 57 Date date = null; 58 try { 59 date = s.parse(dateStr); 60 } catch (ParseException e) { 61 e.printStackTrace(); 62 } 63 if (date == null) { 64 if (startDate != null && lastDate != null && lastEmitDate != startDate) { 65 String[] result = new String[]{startDate.toString(), lastDate.toString()}; 66 forward(result); 67 lastEmitDate = startDate; 68 } 69 startDate = null; 70 lastDate = null; 71 return; 72 } 73 if (startDate == null) { 74 startDate = date; 75 } else if (lastDate != null) { 76 GregorianCalendar calander = new GregorianCalendar(); 77 calander.setTime(lastDate); 78 calander.add(Calendar.DATE, 1); 79 Date time = calander.getTime(); 80 if (!time.equals(date)) { 81 String[] result = new String[]{s.format(startDate), s.format(lastDate)}; 82 forward(result); 83 lastEmitDate = startDate; 84 startDate = date; 85 } 86 } 87 lastDate = date; 88 } 89 }
2、打肥jar包上传服务器，使用该udtf。
add JAR /home/hadoop/hive-dateudtf-jar-with-dependencies.jar; create temporary function dateUDTF as 'com.hive.udf.TestGenericUDTF';