Athena 查询结果显示空值，尽管查询中的条件不为空

Question

我在雅典娜中运行了以下查询。我想收到"resource_tags_aws_cloudformation_stack_name"中包含标签的所有结果。但是，当我运行查询时，我的结果显示"resource_tags_aws_cloudformation_stack_name"为空的行，我不知道我做错了什么。

SELECT 
cm.line_item_usage_account_id,
pr.line_of_business,
cm.resource_tags_aws_cloudformation_stack_name, 
SUM(CASE WHEN cm.line_item_product_code = 'AmazonEC2'
THEN line_item_unblended_cost * 0.97    
ELSE cm.line_item_unblended_cost END) AS discounted_cost, 
CAST(cm.line_item_usage_start_date AS DATE) AS start_day
FROM cost_management cm
JOIN prod_cur_metadata pr ON cm.line_item_usage_account_id = pr.line_item_usage_account_id
WHERE cm.line_item_usage_account_id IN ('1234504482')
AND cm.resource_tags_aws_cloudformation_stack_name IS NOT NULL
AND cm.line_item_usage_start_date
BETWEEN date '2020-01-01'
AND date '2020-01-30'
GROUP BY  cm.line_item_usage_account_id,pr.line_of_business,  cm.resource_tags_aws_cloudformation_stack_name, CAST(cm.line_item_usage_start_date AS DATE), pr.line_of_business
HAVING sum(cm.line_item_blended_cost) > 0
ORDER BY  cm.line_item_usage_account_id 

Answer 1

我修改了我的查询以排除' '，这似乎有效：

SELECT 
cm.line_item_usage_account_id,
pr.line_of_business,
cm.resource_tags_aws_cloudformation_stack_name, 
SUM(CASE WHEN cm.line_item_product_code = 'AmazonEC2'
THEN line_item_unblended_cost * 0.97    
ELSE cm.line_item_unblended_cost END) AS discounted_cost, 
CAST(cm.line_item_usage_start_date AS DATE) AS start_day
FROM cost_management cm
JOIN prod_cur_metadata pr ON cm.line_item_usage_account_id = pr.line_item_usage_account_id
WHERE cm.line_item_usage_account_id IN ('1234504482')
AND NOT cm.resource_tags_aws_cloudformation_stack_name = ' ' 
AND cm.line_item_usage_start_date
BETWEEN date '2020-01-01'
AND date '2020-01-30'
GROUP BY  cm.line_item_usage_account_id,pr.line_of_business,  cm.resource_tags_aws_cloudformation_stack_name, CAST(cm.line_item_usage_start_date AS DATE), pr.line_of_business
HAVING sum(cm.line_item_blended_cost) > 0
ORDER BY  cm.line_item_usage_account_id 

Answer 2

您可以尝试以下空间用例

AND Coalesce(cm.resource_tags_aws_cloudformation_stack_name,' ') !=' '

或者，如果您有多个空格，请尝试。如果实际数据中需要空格，则以下查询不好

AND Regexp_replace(cm.resource_tags_aws_cloudformation_stack_name,' ') is not null

除此之外，您可能还在数据中具有特殊的字符，例如CR或LF。虽然它的情况很少见