# Logstash - ES to BigQuery - doesn't load all rows into BigQuery
**URL:** https://discuss.elastic.co/t/logstash-es-to-bigquery-doesnt-load-all-rows-into-bigquery/190114
**Category:** Logstash
**Created:** [July 11, 2019, 9:40pm UTC](https://discuss.elastic.co/t/logstash-es-to-bigquery-doesnt-load-all-rows-into-bigquery/190114 "2019-07-11T21:40:07Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@srikrishb](https://discuss.elastic.co/u/srikrishb)
#### Post date: [July 11, 2019, 9:40pm UTC](https://discuss.elastic.co/t/logstash-es-to-bigquery-doesnt-load-all-rows-into-bigquery/190114/1 "2019-07-11T21:40:07Z")
Hello,
I am using logstash 7.1.1 to fetch data from elasticsearch cluster to BigQuery database. My logstash instance ran successfully but ran into some problems:
**Logstash config file:**
input {
elasticsearch {
hosts =\> myhost
index =\> "index-v1"
query =\> '{"query": { "type" : {"value" : "ObjectTerritory2AssignmentRuleItem"}}, "\_source" : {}}'
}
}
filter {
mutate {
remove\_field =\> ["@version","@DATE","DATE","path","host","type", "message", "version", "timestamp", "@timestamp"]
}
if ["lastmodifieddate"] != "null" { mutate { gsub =\> ["lastmodifieddate", "000+00:00", ""] }}
if ["systemmodstamp"] != "null" { mutate { gsub =\> ["systemmodstamp", "000+00:00", ""] }}
}
output {
google\_bigquery {
project\_id =\> "project"
dataset =\> "dataset"
table\_prefix =\> "ObjectTerritory2AssignmentRuleItem\_2016\_01"
table\_separator =\> ""
flush\_interval\_secs =\> 50
date\_pattern =\> ""
error\_directory =\> "/logstash/7.1.1/tmp/bigquery-errors/tmp"
json\_key\_file =\> "key.json"
json\_schema =\> {
fields =\> [
{ name =\> "CreatedById" type =\> "STRING" mode =\> "NULLABLE"},
{ name =\> "CreatedDate" type =\> "TIMESTAMP" mode =\> "NULLABLE"},
{ name =\> "Field" type =\> "STRING" mode =\> "NULLABLE"},
{ name =\> "Id" type =\> "STRING" mode =\> "NULLABLE"},
{ name =\> "IsDeleted" type =\> "BOOLEAN" mode =\> "NULLABLE"},
{ name =\> "LastModifiedById" type =\> "STRING" mode =\> "NULLABLE"},
{ name =\> "LastModifiedDate" type =\> "TIMESTAMP" mode =\> "NULLABLE"},
{ name =\> "Operation" type =\> "STRING" mode =\> "NULLABLE"},
{ name =\> "RuleId" type =\> "STRING" mode =\> "NULLABLE"},
{ name =\> "SortOrder" type =\> "FLOAT" mode =\> "NULLABLE"},
{ name =\> "SystemModstamp" type =\> "TIMESTAMP" mode =\> "NULLABLE"},
{ name =\> "Value" type =\> "STRING" mode =\> "NULLABLE"},
{ name =\> "sfdc\_type" type =\> "STRING" mode =\> "NULLABLE"}
]
}
}
}
**Logfile Content:**
_[2019-07-11T17:05:57,080][INFO][logstash.outputs.googlebigquery] Publishing 128 messages to ObjectTerritory2AssignmentRuleItem\_2016\_01_
_[2019-07-11T17:05:58,526][INFO][logstash.outputs.googlebigquery] Publishing 128 messages to ObjectTerritory2AssignmentRuleItem\_2016\_01_
_[2019-07-11T17:05:58,757][ERROR][logstash.outputs.googlebigquery] Error creating table. {:exception=\>java.lang.NullPointerException}_
...
_[2019-07-11T17:06:44,678][INFO][logstash.outputs.googlebigquery] Publishing 128 messages to ObjectTerritory2AssignmentRuleItem\_2016\_01_
_[2019-07-11T17:06:45,169][INFO][logstash.outputs.googlebigquery] Publishing 128 messages to ObjectTerritory2AssignmentRuleItem\_2016\_01_
_[2019-07-11T17:06:46,082][INFO][logstash.runner] Logstash shut down._
**Issues:**
1. Although my destination table exists, I get an error message - '[ERROR][logstash.outputs.googlebigquery] Error creating table. {:exception=\>java.lang.NullPointerException}'. Why does logstash try to create a table if it exists? Can this feature disabled? I didn't find any parameter that can do this. Please suggest.
2. The number of documents in elasticsearch is 11437 whereas total number of records that got loaded in BigQuery is 11392. As an alternative, I tried deleting the table and reloading it but I saw the same behavior.
Please help.
---
### Author:  [@srikrishb](https://discuss.elastic.co/u/srikrishb)
#### Post date: [July 26, 2019, 2:19am UTC](https://discuss.elastic.co/t/logstash-es-to-bigquery-doesnt-load-all-rows-into-bigquery/190114/2 "2019-07-26T02:19:39Z")
Update - I found that logstash didn't commit the records in buffer before shutting down. Detailed analysis is [here](https://github.com/logstash-plugins/logstash-output-google_bigquery/issues/51).
Thanks @RobBavey for quickly resolving this issue and updating the plugin code!
---
### Author:  [@srikrishb](https://discuss.elastic.co/u/srikrishb)
#### Post date: [July 26, 2019, 2:20am UTC](https://discuss.elastic.co/t/logstash-es-to-bigquery-doesnt-load-all-rows-into-bigquery/190114/3 "2019-07-26T02:20:15Z")