我下面有一个MRE，它在Big Query中产生内部错误。当运行MySQL语句作为BQ external_query函数的参数时，会出现此问题。SQL在MySQL中工作正常。这是引起问题的表达式，即case表达式返回日期时间的最大值。
max(case when t.code = 'BALANCE' then t.date else null end)
将case表达式的null强制转换为datetime并没有帮助。在MySQL中case表达式的结果是int或bigint时没有问题。
错误为：“发生内部错误，请求无法完成。错误：3144498”
并从BQ日志中：
severity: "INFO"
textPayload: "... [Note] Aborted connection 737652 to db:... (Got an error reading communication packets)"
此MRE不需要架构，仅需要与MySQL Cloud实例（v5.7）（#StandardSQL）进行BQ连接。这里有3种情况；前两个产生内部错误，最后一个是解决方法。根据需要切换注释并运行。同样，引用的MySQL SQL在MySQL中可以正常工作。
select *
FROM EXTERNAL_QUERY("your_mysql_connection_string",
'''
select t.id as transactionID
-- Aggregation 1: Generates internal error
,max(case when t.code = 'BALANCE' then t.date else null end) as maxDate
-- -- Aggregation 2: Generates internal error
-- ,max(case when t.code_id = 999 then t.date else null end) as maxDate
-- -- Aggregation 3: Workaround the internal error
-- ,from_unixtime(max(case when t.code = 'BALANCE' then unix_timestamp(t.date) else unix_timestamp(cast(null as datetime)) end)) as maxDate
from (
select 1 as id, 'BALANCE' as code, 999 as code_id, current_timestamp() as date union all
select 2, 'BALANCE' as code, 999 as code_id, current_timestamp() as date
) t
group by t.id
;''')
;
在我看来，这看起来像BigQuery / MySQL界面中的某种错误。查询在MySQL中按预期工作，并且是基本的聚合语句。
同意，听起来像是个错误-所以您可能要在这里提交
同时，以下是更简单的解决方法-只需将您的mysql查询包装select * from (...) t为以下示例
SELECT * FROM EXTERNAL_QUERY("your_mysql_connection_string", '''
select * from (
select t.id as transactionID
-- Aggregation 1: Generates internal error
,max(case when t.code = 'BALANCE' then t.date else null end) as maxDate
from (
select 1 as id, 'BALANCE' as code, 999 as code_id, current_timestamp() as date union all
select 2, 'BALANCE' as code, 999 as code_id, current_timestamp() as date
) t
group by t.id
) t
''');
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句