You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@dolphinscheduler.apache.org by GitBox <gi...@apache.org> on 2022/01/25 07:14:42 UTC
[GitHub] [dolphinscheduler] zixi0825 commented on a change in pull request #6718: [Feature][DataQuality] Add Data quality Module #4283
zixi0825 commented on a change in pull request #6718:
URL: https://github.com/apache/dolphinscheduler/pull/6718#discussion_r791415535
##########
File path: dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql
##########
@@ -994,6 +994,784 @@ CREATE TABLE `t_ds_alert_plugin_instance` (
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `t_ds_dq_comparison_type`
+--
+DROP TABLE IF EXISTS `t_ds_dq_comparison_type`;
+CREATE TABLE `t_ds_dq_comparison_type` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `type` varchar(100) NOT NULL,
+ `execute_sql` text DEFAULT NULL,
+ `output_table` varchar(100) DEFAULT NULL,
+ `name` varchar(100) DEFAULT NULL,
+ `create_time` datetime DEFAULT NULL,
+ `update_time` datetime DEFAULT NULL,
+ `is_inner_source` tinyint(1) DEFAULT '0',
+ PRIMARY KEY (`id`)
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+INSERT INTO `t_ds_dq_comparison_type`
+(`id`, `type`, `execute_sql`, `output_table`, `name`, `create_time`, `update_time`, `is_inner_source`)
+VALUES(1, 'FixValue', NULL, NULL, NULL, '2021-06-30 00:00:00.000', '2021-06-30 00:00:00.000', false);
+INSERT INTO `t_ds_dq_comparison_type`
+(`id`, `type`, `execute_sql`, `output_table`, `name`, `create_time`, `update_time`, `is_inner_source`)
+VALUES(2, 'DailyFluctuation', 'select round(avg(statistics_value),2) as day_avg from t_ds_dq_task_statistics_value where data_time >=date_trunc(''DAY'', ${data_time}) and data_time < date_add(date_trunc(''day'', ${data_time}),1) and unique_code = ${unique_code} and statistics_name = ''${statistics_name}''', 'day_range', 'day_range.day_avg', '2021-06-30 00:00:00.000', '2021-06-30 00:00:00.000', true);
+INSERT INTO `t_ds_dq_comparison_type`
+(`id`, `type`, `execute_sql`, `output_table`, `name`, `create_time`, `update_time`, `is_inner_source`)
+VALUES(3, 'WeeklyFluctuation', 'select round(avg(statistics_value),2) as week_avg from t_ds_dq_task_statistics_value where data_time >= date_trunc(''WEEK'', ${data_time}) and data_time <date_trunc(''day'', ${data_time}) and unique_code = ${unique_code} and statistics_name = ''${statistics_name}''', 'week_range', 'week_range.week_avg', '2021-06-30 00:00:00.000', '2021-06-30 00:00:00.000', true);
+INSERT INTO `t_ds_dq_comparison_type`
+(`id`, `type`, `execute_sql`, `output_table`, `name`, `create_time`, `update_time`, `is_inner_source`)
+VALUES(4, 'MonthlyFluctuation', 'select round(avg(statistics_value),2) as month_avg from t_ds_dq_task_statistics_value where data_time >= date_trunc(''MONTH'', ${data_time}) and data_time <date_trunc(''day'', ${data_time}) and unique_code = ${unique_code} and statistics_name = ''${statistics_name}''', 'month_range', 'month_range.month_avg', '2021-06-30 00:00:00.000', '2021-06-30 00:00:00.000', true);
+INSERT INTO `t_ds_dq_comparison_type`
+(`id`, `type`, `execute_sql`, `output_table`, `name`, `create_time`, `update_time`, `is_inner_source`)
+VALUES(5, 'Last7DayFluctuation', 'select round(avg(statistics_value),2) as last_7_avg from t_ds_dq_task_statistics_value where data_time >= date_add(date_trunc(''day'', ${data_time}),-7) and data_time <date_trunc(''day'', ${data_time}) and unique_code = ${unique_code} and statistics_name = ''${statistics_name}''', 'last_seven_days', 'last_seven_days.last_7_avg', '2021-06-30 00:00:00.000', '2021-06-30 00:00:00.000', true);
+INSERT INTO `t_ds_dq_comparison_type`
+(`id`, `type`, `execute_sql`, `output_table`, `name`, `create_time`, `update_time`, `is_inner_source`)
+VALUES(6, 'Last30DayFluctuation', 'select round(avg(statistics_value),2) as last_30_avg from t_ds_dq_task_statistics_value where data_time >= date_add(date_trunc(''day'', ${data_time}),-30) and data_time < date_trunc(''day'', ${data_time}) and unique_code = ${unique_code} and statistics_name = ''${statistics_name}''', 'last_thirty_days', 'last_thirty_days.last_30_avg', '2021-06-30 00:00:00.000', '2021-06-30 00:00:00.000', true);
+INSERT INTO `t_ds_dq_comparison_type`
+(`id`, `type`, `execute_sql`, `output_table`, `name`, `create_time`, `update_time`, `is_inner_source`)
+VALUES(7, 'SrcTableTotalRows', 'SELECT COUNT(*) AS total FROM ${src_table} WHERE (${src_filter})', 'total_count', 'total_count.total', '2021-06-30 00:00:00.000', '2021-06-30 00:00:00.000', false);
+INSERT INTO `t_ds_dq_comparison_type`
+(`id`, `type`, `execute_sql`, `output_table`, `name`, `create_time`, `update_time`, `is_inner_source`)
+VALUES(8, 'TargetTableTotalRows', 'SELECT COUNT(*) AS total FROM ${target_table} WHERE (${target_filter})', 'total_count', 'total_count.total', '2021-06-30 00:00:00.000', '2021-06-30 00:00:00.000', false);
+
+--
+-- Table structure for table `t_ds_dq_execute_result`
+--
+DROP TABLE IF EXISTS `t_ds_dq_execute_result`;
+CREATE TABLE `t_ds_dq_execute_result` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `process_definition_id` int(11) DEFAULT NULL,
+ `process_instance_id` int(11) DEFAULT NULL,
+ `task_instance_id` int(11) DEFAULT NULL,
+ `rule_type` int(11) DEFAULT NULL,
+ `rule_name` varchar(255) DEFAULT NULL,
+ `statistics_value` double DEFAULT NULL,
+ `comparison_value` double DEFAULT NULL,
+ `check_type` int(11) DEFAULT NULL,
+ `threshold` double DEFAULT NULL,
+ `operator` int(11) DEFAULT NULL,
+ `failure_strategy` int(11) DEFAULT NULL,
+ `state` int(11) DEFAULT NULL,
+ `user_id` int(11) DEFAULT NULL,
+ `comparison_type` int(11) DEFAULT NULL,
+ `error_output_path` text DEFAULT NULL,
+ `create_time` datetime DEFAULT NULL,
+ `update_time` datetime DEFAULT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table t_ds_dq_rule
+--
+DROP TABLE IF EXISTS `t_ds_dq_rule`;
+CREATE TABLE `t_ds_dq_rule` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `name` varchar(100) DEFAULT NULL,
+ `type` int(11) DEFAULT NULL,
+ `user_id` int(11) DEFAULT NULL,
+ `create_time` datetime DEFAULT NULL,
+ `update_time` datetime DEFAULT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+INSERT INTO `t_ds_dq_rule`
+(`id`, `name`, `type`, `user_id`, `create_time`, `update_time`)
+VALUES(1, '$t(null_check)', 0, 1, '2020-01-12 00:00:00.000', '2020-01-12 00:00:00.000');
Review comment:
Yes, because this id needs to be used to associate rules_ input_ entry and rule_ execute_ sql
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org