BigQuery:SPLIT()中的错误返回 [英] BigQuery: error in SPLIT() returns

问题描述

我在BigQuery中有一个表TabA，该表的TabA列为ColA，而ColA列具有以下结构

I have a table TabA in BigQuery which has a column ColA and the column ColA has the following structure

1038627|21514184

并且表TabA拥有超过一百万条记录.我使用了此将其分成多列

and the table TabA has more than a million records. I used this to split into multiple columns

SELECT ColA,FIRST(SPLIT(ColA, '/')) part1,
       NTH(2, SPLIT(ColA, '/')) part2
FROM TabA

但是由于某些原因，在某些行之后，拆分似乎无法正常工作.

But for some reason, after certain rows the split seems to be not working properly.

我们正在获取这样的记录，

We are getting records like this,

     ColA            part1   part2
1038627|21507470    1038627 21507470     
1038627|21534857    1038627 21507470     
1038627|21546455    1038627 21507470     
1038627|21577167    1038627 21507470

这是他随机发生的事情.不知道哪里有错误.

It his happening on a random basis. Not sure where is there error.

从TabA中选择COUNT(*)-返回说170万条记录

SELECT COUNT(*) FROM TabA - returns say 1.7M records

---

SELECT ColA，FIRST(SPLIT(ColA，'|'))part1， 来自TabA的NTH(2，SPLIT(ColA，'|'))part2 -返回170万条错误记录的记录

SELECT ColA,FIRST(SPLIT(ColA, '|')) part1, NTH(2, SPLIT(ColA, '|')) part2 FROM TabA - returns 1.7M records with the wrong split

---

选择第一个(SPLIT(ColA，'|'))part1， NTH(2，SPLIT(ColA，'|'))part2 FROM TabA -仅返回1.4L记录并正确分割

SELECT FIRST(SPLIT(ColA, '|')) part1, NTH(2, SPLIT(ColA, '|')) part2 FROM TabA - returns just 1.4L records with correct split

不知道到底发生了什么...是数据问题还是拆分问题?

Don't know what exactly is happening...is it the problem with the data or the problem with the split ??

任何帮助将不胜感激.在此先感谢！

Any help would be greatly appreciated. Thanks in advance!!

推荐答案

是数据问题还是拆分问题?

is it the problem with the data or the problem with the split ??

为帮助进行故障排除-我建议在BigQuery Standard SQL中运行相同的逻辑

To help in troubleshooting - I would recommend running same logic in BigQuery Standard SQL

#standardSQL
SELECT 
  ColA,
  SPLIT(ColA, '|')[SAFE_OFFSET(0)] AS part1,
  SPLIT(ColA, '|')[SAFE_OFFSET(1)] AS part2
FROM TabA

这篇关于BigQuery:SPLIT()中的错误返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！