Insert Multiple records in BigQuery using WSO2

0
votes

I am using BigQuery Connector in WSO2 to insert multiple records in BigQuery cloud.

I followed this link and was able to insert a single record successfully.

First I tried to pass multiple records by separating them using a comma.
However, in this case, only the first record gets inserted and other records are skipped.

Please note that no error is returned in this case.

My Json message with multiple records:

{"insertId":"101","json":{"NAME":"Vishal_101","ADDRESS":"UK","ID":"vbordia"}},{"insertId":"102","json":{"NAME":"Vishal_102","ADDRESS":"UK","ID":"vbordia"}}

Second I tried this link which explain how to build multiple records.
However, this time I get an invalid response from BigQuery. Since the response message is not well explanatory I am unable to understand the cause.-

Messagean :

{
"rows":
  [
    {
      "insertId":"209",
      "json": 
        {
          "NAME": "NewRow1",
          "ADDRESS": "NewAddr",
          "ID": "123"
        }
    },
    {
      "insertId":"210",
      "json": 
        {
          "NAME": "NewRow2",
          "ADDRESS": "NewAddr",
          "ID": "123"
        }
    }
  ]
}

Error Message Returned from BigQuery :

{
"insertErrors": [
    {
      "index": 0,
      "errors": [
        {
          "reason": "invalid",
          "location": "",
          "debugInfo": "",
          "message": ""
        }
      ]
    }
  ]
}

Can anyone please help me here.What is the correct way of inserting multiple records in BigQuery via Wso2. Thanks in advance.

2
google-bigquerywso2esb
Is the 2nd error is coming when running the insertAll from the 2nd link? – Tamir Klein
Yes Tamir . I have treid many other ways of inserting but nothing is working. Since there is no error message being returned from BigQuery I am unable to find what is wrong. – Vishal Bordia

2 Answers

2
votes

This was a bug in WSO2 Bigquery connector. We have raised with WSO2 and they have now released new version of connector which is capable of inserting multiple records.You can find the latest version at WSO2 market place.

0
votes

Based on your data I created a test table in my project

enter image description here

And used the API to upload your example data as follow:

curl --request POST \
  'https://www.googleapis.com/bigquery/v2/projects/myproject/datasets/dataset/tables/testInsert/insertAll' \
  --header 'Authorization: Bearer [YOUR_ACCESS_TOKEN]' \
  --header 'Accept: application/json' \
  --header 'Content-Type: application/json' \
  --data '{"rows":[{"insertId":"z1","json":{"NAME":"Vishal_z1","ADDRESS":"UK","ID":"vbordia"}},{"insertId":"z2","json":{"NAME":"Vishal_z2","ADDRESS":"UK","ID":"vbordia1"}}]}' \
  --compressed

using this SELECT:

SELECT * FROM `project.dataset.testInsert` LIMIT 1000

I confirmed the data is in the table:

enter image description here