PARSE_DATE:来自解析函数BigQuery/Standard SQL的无效结果 [英] PARSE_DATE : Invalid result from parsing function BigQuery/Standard SQL

问题描述

我在BigQuery(标准SQL)中具有以下代码:

I have the following code in BigQuery (standard SQL):

SAFE_CAST (PARSE_DATE('%Y-%m-%d',JSON_EXTRACT_SCALAR(g.p_dataforanalytics,'$.birthday') ) as string)

此操作由于以下原因而失败:

This fails due to:

解析函数的结果无效

Invalid result from parsing function

问题是我无法控制在g.p_dataforanalytics中获得的数据，其中有些是纯垃圾，格式不正确，有时甚至是不合理的值. 有没有一种方法可以定义如果PARSE_DATE()失败，它将返回NULL并且不会抛出查询?

The problem is that I have no control of the data I get in g.p_dataforanalytics some of it is pure garbage and not in the right format, sometimes it's not even reasonable value. Is there a way to define that if PARSE_DATE() fails it will return NULL and not throw the query out?

基本上我正在寻找任何编程语言中的try/catch之类的东西吗?

Basicly I'm looking for something like a try/catch in any programming language?

推荐答案

在 parse 上使用SAFE.前缀:

SAFE.PARSE_DATE('%Y-%m-%d', JSON_EXTRACT_SCALAR(g.p_dataforanalytics, '$.birthday') )

除非您需要这样的解决方案，否则我看不到将其转换回字符串的实用程序:

I don't see the utility of converting this back to a string, unless you wanted a solution like this:

COALESCE(FORMAT('%Y-%m-%d',
                COALESCE(SAFE.PARSE_DATE('%Y-%m-%d', JSON_EXTRACT_SCALAR(g.p_dataforanalytics, '$.birthday'), 
                         SAFE.PARSE_DATE('%m/%d/%Y', JSON_EXTRACT_SCALAR(g.p_dataforanalytics, '$.birthday')
                        ) 

                ), JSON_EXTRACT_SCALAR(g.p_dataforanalytics, '$.birthday'
        )

也就是说，如果代码为日期测试了不同的格式，请选择一种格式，然后将其转换回YYYY-MM-DD格式.而且，如果没有一种格式有效，则保留原始值.

That is, if the code tested different formats for the date, chose one, turned it back into YYYY-MM-DD format. And, if none of the formats worked, then kept the original value.

这篇关于PARSE_DATE:来自解析函数BigQuery/Standard SQL的无效结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！