Hey all,
I am new here
Recently I moved exisitng snowplow instance to a new project, It looked good for the first few days, but since yesterday:
Have been receiving on stream loader logs
[io-compute-1] INFO com.snowplowanalytics.snowplow.storage.bigquery.streamloader.Main - snowplow.streamloader.StatisticsPeriod = 1 minute, TotalEvent = 6000, GoodEvent = 0, FailedInsert = 6000, BadEvent = 0, Types = 2637
And this has increased the number of unacked size for failed inserts. Went through a number of similar logs and looks like all the Events are hitting Failed inserts.
As you can see the number of good events on stream loader has drastically decreased :
Any help will be appreciated.
Thanks
P.S: We are using BQ loader version 1.5.1 as the latest versions is increasing the backlog for good events Pub/Sub in our case.
Hi @emeritus_motet,
If the loader is failing to insert the events, it probably means the table you’re loading into does not have all the necessary columns for the entities attached to your events. To debug this further you will need to work out why the table does not match the event.
Unfortunately, if you are running BQ loader version 1.5.1 then it is difficult to find out the reason, because the logs don’t contain much helpful information. In more recent versions we added more helpful logging, so you would see log lines like:
Bigquery inserts failed. Reason: [$reason]. Location: [$location]
I would suggest take another go at running the latest version of the loader (version 1.6.4). The latest version is working very well for us, so there is no reason it should increase the backlog. Then you could take a look at the error message in the logs to understand the reason for the failed inserts.
I suppose there are two other ways you could go about resolving this, but I think they are more effort:
- Manually read some of the failed inserts from the failed inserts topic. Then manually compare the shape of the data against the schema of your BigQuery table. You might spot an inconsistency.
- Are you running the mutator and repeater? These are optional apps that you can run alongside the streamloader. Their purpose is to automatically update the BigQuery table with new columns to receive any new entities you attach to your events.
Hi @istreeter,
Thanks for response.
Manually read some of the failed inserts from the failed inserts topic. Then manually compare the shape of the data against the schema of your BigQuery table. You might spot an inconsistency.
Could this be because some fields in schema having multiple type?
Are you running the mutator and repeater? These are optional apps that you can run alongside the streamloader. Their purpose is to automatically update the BigQuery table with new columns to receive any new entities you attach to your events.
Yes we are running both mutator and repeater.
And about running the latest version of BQ loader, the docker file keeps restarting in our case preventing any data to pass through. Hence, we rolled back to a previous stable version [ in our case 1.5.1].
Hi @emeritus_motet
Yes we are running both mutator and repeater.
This is helpful – the repeater gives you another way to debug what’s going wrong. If the repeater fails to insert an event to BigQuery, then it writes out a failed event to a GCS bucket, configured like this. If you look at the failed events in that bucket, you might find an error message which describes what’s going wrong.
And about running the latest version of BQ loader, the docker file keeps restarting in our case preventing any data to pass through.
Was there an error in the logs when it crashed and restarted? If you share the logs then I might be able to suggest how to get it running properly. If we can get BQ loader 1.6.4 working from you, then I’m sure it’s the easiest way to discover why events are not getting inserted.
Hi @istreeter,
thanks for:
This is helpful – the repeater gives you another way to debug what’s going wrong. If the repeater fails to insert an event to BigQuery, then it writes out a failed event to a GCS bucket, configured like this. If you look at the failed events in that bucket, you might find an error message which describes what’s going wrong.
It helped in solving the issue. The error was with:
Could this be because some fields in schema having multiple type?
After fixing that now the failed inserts came back to a normal state and we are getting goodEvents on stream loader as well.
Thanks for the help
