在hive/impala中运行多个sql查询以测试通过或失败

Question

我正在运行100个查询(测试用例(来检查hive/impala中的数据质量。大多数查询根据某些条件检查空值。我正在使用条件聚合来计算琐碎的测试用例，如下所示。我想为这种类型的检查添加一个更复杂的查询条件。如果有空，我也想看看计数。

我想知道如何合并更复杂的查询，并在存在null的情况下添加计数。预期输出如下。

到目前为止我所拥有的：

SELECT (CASE WHEN COUNT(*) = COUNT(car_type) THEN 'PASS' ELSE 'FAIL' END) as car_type_test,
(CASE WHEN COUNT(*) = COUNT(car_color) THEN 'PASS' ELSE 'FAIL' END) as car_color_test,
(CASE WHEN COUNT(*) = COUNT(car_sale) THEN 'PASS' ELSE 'FAIL' END) as car_sale_test       
FROM car_data;

要添加的更复杂的类型查询：

SELECT Count(*), 
car_job 
FROM   car_data 
WHERE  car_job NOT IN ( "car_type", "car_license", "car_cancellation", 
"car_color", "car_contract", "car_metal", "car_number" ) 
OR car_job IS NULL 
GROUP  BY car_job

预期输出示例：

car_type_test  car_color_test  car_sale_test  car_job_test
PASS           PASS             PASS           FAIL
102

Answer 1

我建议将其放在一行而不是两行：

SELECT (CASE WHEN COUNT(*) = COUNT(car_type) THEN 'PASS'
ELSE REPLACE('FAIL ([n])', '[n]', COUNT(*) - COUNT(car_type))
END) as car_type_test,
(CASE WHEN COUNT(*) = COUNT(car_color) THEN 'PASS'
ELSE REPLACE('FAIL ([n])', '[n]', COUNT(*) - COUNT(car_color))
END) as car_color_test,
(CASE WHEN COUNT(*) = COUNT(car_sale) THEN 'PASS'
ELSE REPLACE('FAIL ([n])', '[n]', COUNT(*) - COUNT(car_sale))
END) as car_sale_test       
FROM car_data;