CODEWITHSUNDEEP
google-bigquerygoogle-cloud-platform
4ng3l0
4ng3l0

Reputation: 98

BigQuery: cannot save number in scientific notation

We are experiencing problems in saving data on BigQuery between 22:00 and 5:00 UTC. The problem started at midnight February 7th 2017 and is still happening today.

Everyday in this time frame we have a huge amount of failures inserting data. The number of failures decreases dramatically until it disappears in the next hours.

The data format we insert is always the same throughout the day.

This is one of the "n" errors we found in the response of the insert request:

{"debugInfo":"generic::invalid_argument: Cannot convert value to integer (bad value):8.3163161541138022e+17","location":"id","message":"Cannot convert value to integer (bad value):8.3163161541138022e+17","reason":"invalid"}

It seems the problem is related to numbers with scientific notation. As mentioned above we perform save operations with this kind of numbers all the day but we encounter the problem only in a time frame of the day since February 7th 2017

This is a print out of the row (TableDataInsertAllRequest.Rows) related to the above error.

{insertId=831631615411380224, json={in_reply_to_status_id_str=null, in_reply_to_status_id=null, created_at_timestamp=2017-02-14 22:30:02, created_a
t=Tue Feb 14 22:30:02 +0000 2017, in_reply_to_user_id_str=null, source=<a href="http://twitter.com/download/iphone" rel="nofollow">Twitter for iPhone</a>, retweeted_status={in_reply_to_status_id_str=null, in_reply_to_status_id=null, cr
eated_at=Tue Feb 14 15:56:07 +0000 2017, in_reply_to_user_id_str=null, source=<a href="http://www.keyakizaka46.com/" rel="nofollow">bot</a>, retweet_count=2.0, retweeted=false, geo=null, filter_level=low, in_reply_to_screen_name
=null, is_quote_status=false, id_str=831532483447488513, in_reply_to_user_id=null, favorite_count=25.0, id=8.3153248344748851E17, text=2017/02/15 00:48, place=null, lang=ja, favorited=fal
se, possibly_sensitive=false, coordinates=null, truncated=false, entities={urls=[{display_url=google.com/g0lSa9, indices=[31.0, 54.0], expanded_url=https://google.com/g0lSa9, url=https://twitter.com/W2Vpuwrq1F}], hashtags=[], user_mentions=[], symbol
s=[]}, contributors=null, user={utc_offset=null, friends_count=5.0, profile_image_url_https=https://pbs.twimg.com/profile_images/665843786015113217/126-R8pG_normal.jpg, listed_count=91.0, profile_background_image_url=http://abs.twimg.c
om/images/themes/theme1/bg.png, default_profile_image=false, favourites_count=0.0, description=abc, created_at=Sat Nov 14 
10:18:54 +0000 2015, is_translator=false, profile_background_image_url_https=https://abs.twimg.com/images/themes/theme1/bg.png, protected=false, screen_name=keyaki_blog, id_str=4233385392, profile_link_color=1DA1F2, id=4.233385392E9, g
eo_enabled=false, profile_background_color=C0DEED, lang=ja, profile_sidebar_border_color=C0DEED, profile_text_color=333333, verified=false, profile_image_url=http://pbs.twimg.com/profile_images/665843786015113217/126-R8pG_normal.jpg, t
ime_zone=null, url=http://www.keyakizaka46.com/mob/news/diarShw.php?site=k46o&ima=2757&cd=member, contributors_enabled=false, profile_background_tile=false, profile_banner_url=https://pbs.twimg.com/profile_banners/4233385392/1447584504
, statuses_count=22077.0, follow_request_sent=null, followers_count=11669.0, profile_use_background_image=true, default_profile=true, following=null, name=bot, location=tokyo, profile_sidebar_fill_color=DDEEF6, notifications=null}}, retweet_count=0.0, retweeted=false, geo=null, filter_level=low, in_reply_to_screen_name=null, is_quote_status=false, id_str=831631615411380224, in_reply_to_user_id=null, favorite_count=0.0, id=8.3163161541138022E+17, text=RT @keyaki_blog: 2017/02/15 00:48 https://twitter.co/W2Vpuwrq1F, place=null, lang=ja, favorited=false, possibly_sensitive=false, coordinates=null, truncated=false, timestamp_ms=1487111402739, entities={urls=[{display_url=google.com/g0lSa9, indices=[48.0, 71.0], expanded_url=https://google.com/g0lSa9, url=https://twitter.co/W2Vpuwrq1F}], hashtags=[], user_mentions=[{indices=[3.0, 15.0], screen_name=keyaki_blog, id_str=4233385392, name=bot, id=4.233385392E9}], symbols=[]}, process_instance_id=user-twitter-listener-st-1, contributors=null, user={utc_offset=-28800.0, friends_count=328.0, profile_image_url_https=https://pbs.twimg.com/profile_images/601593430666412032/PQioR336_normal.jpg, listed_count=26.0, profile_background_image_url=http://abs.twimg.com/images/themes/theme1/bg.png, default_profile_image=false, favourites_count=49710.0, description=null, created_at=Fri May 22 01:28:04 +0000 2015, is_translator=false, profile_background_image_url_https=https://abs.twimg.com/images/themes/theme1/bg.png, protected=false, screen_name=tonton_gash1, id_str=3222825788, profile_link_color=1DA1F2, id=3.222825788E9, geo_enabled=true, profile_background_color=C0DEED, lang=ja, profile_sidebar_border_color=C0DEED, profile_text_color=333333, verified=false, profile_image_url=http://pbs.twimg.com/profile_images/601593430666412032/PQioR336_normal.jpg, time_zone=Pacific Time (US & Canada), url=null, contributors_enabled=false, profile_background_tile=false, statuses_count=113901.0, follow_request_sent=null, followers_count=404.0, profile_use_background_image=true, default_profile=true, following=null, name=bb@Onlooker, location=aaa, profile_sidebar_fill_color=DDEEF6, notifications=null}}}
14/02/2017 22:30:33 [ERROR] [95] [saveBulk] [Thread: Thread-1] ERROR BigQueryDataSaver: {insertId=831631616057425921, json={quoted_status={in_reply_to_status_id_str=null, in_reply_to_status_id=null, coordinates=null, created_at=Tue Feb 14 22:17:50 +0000 2017, truncated=false, in_reply_to_user_id_str=null, source=<a href="http://twitter.com/download/iphone" rel="nofollow">Twitter for iPhone</a>, retweet_count=0.0, retweeted=false, geo=null, filter_level=low, in_reply_to_screen_name=null, is_quote_status=false, entities={urls=[], hashtags=[], user_mentions=[], symbols=[]}, id_str=831628543281025024, in_reply_to_user_id=null, favorite_count=0.0, id=8.3162854328102502E17, text=kaitlyn's baby pictures?, place=null, contributors=null, lang=en, user={utc_offset=-18000.0, friends_count=505.0, profile_image_url_https=https://pbs.twimg.com/profile_images/813191388967960577/Q6Wh4HyC_normal.jpg, listed_count=36.0, profile_background_image_url=http://pbs.twimg.com/profile_background_images/720373579259351041/mPcBv5UA.jpg, default_profile_image=false, favourites_count=48248.0, description=▽ liar, monster, snappy dresser ▽ → kaitlyn // 18 // she/her // antifa // studies history + sociology in scotland // #FreePalestine #AllBlackLivesMatter, created_at=Sun Apr 26 16:05:35 +0000 2009, is_translator=false, profile_background_image_url_https=https://pbs.twimg.com/profile_background_images/720373579259351041/mPcBv5UA.jpg, protected=false, screen_name=ghostcommie, id_str=35495052, profile_link_color=F5ABB5, id=3.5495052E7, geo_enabled=false, profile_background_color=89C9FA, lang=en, profile_sidebar_border_color=FFFFFF, profile_text_color=CE7834, verified=false, profile_image_url=http://pbs.twimg.com/profile_images/813191388967960577/Q6Wh4HyC_normal.jpg, time_zone=Quito, url=null, contributors_enabled=false, profile_background_tile=false, profile_banner_url=https://pbs.twimg.com/profile_banners/35495052/1471561647, statuses_count=59269.0, follow_request_sent=null, followers_count=836.0, profile_use_background_image=true, default_profile=false, following=null, name=a nearby galaxy, location=a few light years away, profile_sidebar_fill_color=78C0A8, notifications=null}, favorited=false}, in_reply_to_status_id_str=null, in_reply_to_status_id=null, created_at_timestamp=2017-02-14 22:30:02, created_at=Tue Feb 14 22:30:02 +0000 2017, in_reply_to_user_id_str=null, source=<a href="http://twitter.com/download/iphone" rel="nofollow">Twitter for iPhone</a>, quoted_status_id=8.3162854328102502E+17, retweet_count=0.0, retweeted=false, geo=null, filter_level=low, in_reply_to_screen_name=null, is_quote_status=true, id_str=831631616057425921, in_reply_to_user_id=null, favorite_count=0.0, id=8.3163161605742592E+17, text=The people have spoken https://twitter.co/8yPASyGyww, place=null, lang=en, favorited=false, possibly_sensitive=false, coordinates=null, truncated=false, timestamp_ms=1487111402893, entities={urls=[{display_url=twitter.com/ghostcommie/st…, indices=[23.0, 46.0], expanded_url=https://twitter.com/ghostcommie/status/831628543281025024, url=https://twitter.co/8yPASyGyww}], hashtags=[], user_mentions=[], symbols=[]}, process_instance_id=user-twitter-listener-st-1, display_text_range=[0.0, 22.0], quoted_status_id_str=831628543281025024, contributors=null, user={utc_offset=-18000.0, friends_count=505.0, profile_image_url_https=https://pbs.twimg.com/profile_images/813191388967960577/Q6Wh4HyC_normal.jpg, listed_count=36.0, profile_background_image_url=http://pbs.twimg.com/profile_background_images/720373579259351041/mPcBv5UA.jpg, default_profile_image=false, favourites_count=48248.0, description=▽ liar, monster, snappy dresser ▽ → kaitlyn // 18 // she/her // antifa // studies history + sociology in scotland // #FreePalestine #AllBlackLivesMatter, created_at=Sun Apr 26 16:05:35 +0000 2009, is_translator=false, profile_background_image_url_https=https://pbs.twimg.com/profile_background_images/720373579259351041/mPcBv5UA.jpg, protected=false, screen_name=ghostcommie, id_str=35495052, profile_link_color=F5ABB5, id=3.5495052E7, geo_enabled=false, profile_background_color=89C9FA, lang=en, profile_sidebar_border_color=FFFFFF, profile_text_color=CE7834, verified=false, profile_image_url=http://pbs.twimg.com/profile_images/813191388967960577/Q6Wh4HyC_normal.jpg, time_zone=Quito, url=null, contributors_enabled=false, profile_background_tile=false, profile_banner_url=https://pbs.twimg.com/profile_banners/35495052/1471561647, statuses_count=59270.0, follow_request_sent=null, followers_count=836.0, profile_use_background_image=true, default_profile=false, following=null, name=a nearby galaxy, location=a few light years away, profile_sidebar_fill_color=78C0A8, notifications=null}}}

Upvotes: 1

Views: 509

Answers (1)

4ng3l0
4ng3l0

Reputation: 98

I have an important update: I have a problem in another project also on Google Cloud. In this project the problem occurs 24H (not only between 22:00 and 5:00 UTC).

When we try the same software on a development machine on the same BigQuery table the problem does not occur.

So we tried the software on a GCE machine but in different zone: europe-west1-c instead of us-central1-a. On this area the problems does not occur!

Note: the previous message refer to another project. On this project the problem is still happening every day but only between 22:00 and 5:00 UTC. In this project we already have machines in europe-west1-c.

This is a very strange behaviour.

Upvotes: 1

Related Questions