topic Re: tgooglebigqueryinput delete data in Talend Studio

tgooglebigqueryinput delete data

Anonymous — Sat, 16 Nov 2024 05:36:47 GMT

I want to delete data from google big query using delete statement on input element. I get error, but in google bigquery data is deleted. Can you explain why this error happens and how to overcome it? i am using Talend Open studio for data integration 7.2.1.20190131_1157-M2

Exception in component tBigQueryInput_2 (criteo_data_import)
com.google.api.client.googleapis.json.GoogleJsonResponseException: 400 Bad Request
{
"code" : 400,
"errors" : [ {
"domain" : "global",
"message" : "API limit exceeded: Unable to return a row that exceeds the API limits. To retrieve the row, export the table.",
"reason" : "apiLimitExceeded"
} ],
"message" : "API limit exceeded: Unable to return a row that exceeds the API limits. To retrieve the row, export the table.",
"status" : "INVALID_ARGUMENT"
}
at com.google.api.client.googleapis.json.GoogleJsonResponseException.from(GoogleJsonResponseException.java:145)
at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:113)
at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:40)
at com.google.api.client.googleapis.services.AbstractGoogleClientRequest$1.interceptResponse(AbstractGoogleClientRequest.java:312)
at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:1049)
at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:410)
at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:343)
at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:460)
at pigu_robertas.criteo_data_import_0_1.criteo_data_import.tBigQueryInput_2Process(criteo_data_import.java:12122)
at pigu_robertas.criteo_data_import_0_1.criteo_data_import.tFileInputDelimited_6Process(criteo_data_import.java:11680)
at pigu_robertas.criteo_data_import_0_1.criteo_data_import.tFileInputDelimited_5Process(criteo_data_import.java:10045)
at pigu_robertas.criteo_data_import_0_1.criteo_data_import.tFileInputDelimited_4Process(criteo_data_import.java:8410)
at pigu_robertas.criteo_data_import_0_1.criteo_data_import.tBigQueryInput_1Process(criteo_data_import.java:5893)
at pigu_robertas.criteo_data_import_0_1.criteo_data_import.tFileInputDelimited_3Process(criteo_data_import.java:5352)
at pigu_robertas.criteo_data_import_0_1.criteo_data_import.tFileInputDelimited_2Process(criteo_data_import.java:3939)
at pigu_robertas.criteo_data_import_0_1.criteo_data_import.tFileInputDelimited_1Process(criteo_data_import.java:2526)
at pigu_robertas.criteo_data_import_0_1.criteo_data_import.tJava_1Process(criteo_data_import.java:1145)
at pigu_robertas.criteo_data_import_0_1.criteo_data_import.runJobInTOS(criteo_data_import.java:28113)
at pigu_robertas.criteo_data_import_0_1.criteo_data_import.main(criteo_data_import.java:27942)

Re: tgooglebigqueryinput delete data

nfz11 — Thu, 13 Jun 2019 00:07:22 GMT

I am not too familiar with this API, but it looks the like the data in the response is exceeding the maximum size as defined in the API. Is it possible the delete endpoint returns the row that is deleted and the data in that row is quite large?

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 06:24:52 GMT

I fink it is a bit more complicated. if i add where clause like 1 = 1 to delete full table data, where is no error, but if i select 1 day then i get this error. Also data is not big. full table is less then 10mb.

Re: tgooglebigqueryinput delete data

nfz11 — Thu, 13 Jun 2019 07:20:24 GMT

I did a Google search for the error message and found this
https://stackoverflow.com/questions/37547711/bigquery-api-limit-exceeded-error

I don't think it's related to Talend but a known issue with Google big query.

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 07:32:36 GMT

But it is 2016 information and error 403. In my case error is 400.

Also if i launch same query in GBQ UI it finishes without errors

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 08:01:06 GMT

Hi Ronertas,

Could you share the screenshot of your job ? Share us the query you use for better understanding ? Because the error says , Unable to return a row that exceeds the API limits, but you say the table is less than 10 MB. It should work ! lets find where the mistake is !

Thanks & Regards,

Prabuj

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 08:11:16 GMT

Query:

"#standardSQL \n\r delete FROM `sonorous-mix-211210.marketing_platforms_data.criteo_transactions` where cast(transactionDate as date) BETWEEN date_add(current_date('+03:00'), interval -2 day) and date_add(current_date('+03:00'), interval -2 day)"

it deletes about 2000 rows.

i have similar query but it deletes only 3 rows and it do not show this error

job.PNG

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 08:25:57 GMT

Alright, so you use the #standardsql here - check in the talend tbigqueryinput designer section whether the authentication mode & code are perfect to the query , as well as check whether you untick the use legacySQL and try one more time.

Lets c if it works out (or) change the result size

Thanks,
Prabuj.

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 08:32:58 GMT

If it doesn't work out still, You can use tRESTClient definitely for any REST API CALL, and use with OAuth authorisation token.

Thanks,

Prabuj

* Don't forget to give Kudos *

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 08:34:58 GMT

I am using authentication mode OAuth 2.0. Code is correct. And yes: use legacy SQL is unchecked.

I tried to delete #standardsql, but same error.

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 08:36:42 GMT

I wanted to try out Rest API, but i did not find in google documentation how to delete data from tabele. i only found how to delete entire table.

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 08:49:39 GMT

Try setting the result size instead of auto to Large(with allowLargeResults)

Thanks,

Prabuj

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 08:54:34 GMT

i tried that. then GBQ creates temp tables and tbigqueryinput element says not found.

Which version of talend do you use? Maybe my build is buggy.

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 09:39:22 GMT

Kindly check your quota and limit for big query.

https://cloud.google.com/bigquery/quotas
https://cloud.google.com/bigquery/docs/writing-results#large-results

Thanks,
Prabuj.

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 09:43:54 GMT

can you give an example?

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 09:45:43 GMT

As you can see on the error message:

"message" : "API limit exceeded: Unable to return a row that exceeds the API limits. To retrieve the row, export the table.",
"reason" : "apiLimitExceeded"

Kindly check your quota and limit of your api !

Refer the link

https://cloud.google.com/bigquery/quotas

https://cloud.google.com/bigquery/docs/writing-results#large-results

Thanks,

Prabuj.

/Don't forget to give kudos/

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 10:19:15 GMT

i give up. delete operation should return nothing...

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 10:25:04 GMT

Don't give up. Try Fixing it up, even it's a solved issue tbigqueryinput in JIRA from talend for DML. Definitely it should work !!!
First check your API, Did you change the settings to allow large sets and find the limits of your api ? if limit is there ! Next use your query in query section.

Thanks,
Prabuj

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 12:14:27 GMT

So i tested another thing. I tried to drop table. so now i get:

{
"code" : 404,
"errors" : [ {
"domain" : "global",
"message" : "Not found: Table sonorous-mix-211210:marketing_platforms_data.criteo_transactions20190612",
"reason" : "notFound"
} ],
"message" : "Not found: Table sonorous-mix-211210:marketing_platforms_data.criteo_transactions20190612",
"status" : "NOT_FOUND"
}

It looks like after these operations component is calling second time.

Re: tgooglebigqueryinput delete data

Anonymous — Thu, 13 Jun 2019 12:26:39 GMT

Secondly: i transfered my data from partitioned table to simple table. no errors. So it also can be something with partitioned tables.