Five Stars

big query table_date_range is not working for more than 5 days period

I am trying to get data from big query for last 2 years. 

table_date_range fails for any range more 3 to 5 days. 

Is there a way to make it work for longer time period than 5 days? 

 

7 REPLIES
Community Manager

Re: big query table_date_range is not working for more than 5 days period

Hello
I am not sure if it is an API limitation, can you please show us the query you are using? I can escalate it to our developer for further investigation.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Five Stars

Re: big query table_date_range is not working for more than 5 days period

here is my query 

 

SELECT  

fullVisitorId,

userId,

visitNumber,

visitId,

visitStartTime,

date,

totals.visits,

totals.hits,

totals.pageviews,

totals.transactions,

totals.totalTransactionRevenue,

totals.timeOnSite,

totals.bounces,

totals.newVisits,

totals.screenviews,

totals.UniqueScreenViews,

totals.timeOnScreen,

trafficSource.adwordsClickInfo.customerId,

trafficSource.adwordsClickInfo.campaignId,

trafficSource.adwordsClickInfo.adGroupId,

trafficSource.adwordsClickInfo.creativeId,

trafficSource.adwordsClickInfo.criteriaId,

trafficSource.adwordsClickInfo.page,

trafficSource.adwordsClickInfo.slot,

trafficSource.adwordsClickInfo.criteriaParameters,

trafficSource.adwordsClickInfo.gclId,

trafficSource.adwordsClickInfo.adNetworkType,

trafficSource.adwordsClickInfo.isVideoAd,

trafficSource.referralPath,

trafficSource.campaign,

trafficSource.source,

trafficSource.medium,

trafficSource.keyword,

trafficSource.adContent,

device.browser,

device.browserVersion,

device.deviceCategory,

device.operatingSystem,

device.operatingSystemVersion,

device.mobileDeviceBranding,

device.flashVersion,

device.javaEnabled,

device.language,

device.screenColors,

device.screenResolution,

geoNetwork.continent,

geoNetwork.subContinent,

geoNetwork.country,

geoNetwork.region,

geoNetwork.metro

from

(TABLE_DATE_RANGE([myproject-bigquery:1234.ga_sessions_]

TIMESTAMP('2016-02-01'), 

TIMESTAMP('2016-02-31')))

Five Stars

Re: big query table_date_range is not working for more than 5 days period

Here is the error I get

 

Exception in component tBigQueryInput_7

com.google.api.client.googleapis.json.GoogleJsonResponseException: 404 Not Found

{

  "code" : 404,

  "errors" : [ {

    "domain" : "global",

    "message" : "Not found: table anond4db0ea245f3f56bda4e27d28fca180084f5fecd",

    "reason" : "notFound"

  } ],

  "message" : "Not found: table anond4db0ea245f3f56bda4e27d28fca180084f5fecd",

  "status" : "NOT_FOUND"

}

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 test.historical_dev_big_query_a_0_1.historical_dev_big_query_a.tBigQueryInput_7Process(historical_dev_big_query_a.java:3171)

at test.historical_dev_big_query_a_0_1.historical_dev_big_query_a.runJobInTOS(historical_dev_big_query_a.java:5517)

at test.historical_dev_big_query_a_0_1.historical_dev_big_query_a.main(historical_dev_big_query_a.java:4862)

Community Manager

Re: big query table_date_range is not working for more than 5 days period

Hi dwhdeveloper
Thanks for providing the detailed information, can you tell me which version are you using?

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Five Stars

Re: big query table_date_range is not working for more than 5 days period

Talend Open Source Version: 6.3.0

Employee

Re: big query table_date_range is not working for more than 5 days period

Can you try it in the 6.4.1 version which contains Big Query enhancements? 

Five Stars

Re: big query table_date_range is not working for more than 5 days period

I ran it 6.41. 

It fails. Error is similar 

 

---> Reason: responseTooLarge

Message: Response too large to return. Consider setting destinationTable or (for legacy SQL queries) setting allowLargeResults to true in your job configuration. For more details, see https://cloud.google.com/bigquery/querying-data#largequeryresults

Exception in component tBigQueryInput_7 (historical_dev_big_query_a)

com.google.api.client.googleapis.json.GoogleJsonResponseException: 404 Not Found

{

  "code" : 404,

  "errors" : [ {

    "domain" : "global",

    "message" : "Not found: table anon59dc4c8a5fbff088906d8e0f86a5dce5c0d31d78",

    "reason" : "notFound"

  } ],

  "message" : "Not found: table anon59dc4c8a5fbff088906d8e0f86a5dce5c0d31d78",

  "status" : "NOT_FOUND"

}

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 project.historical_dev_big_query_a_0_1.historical_dev_big_query_a.tBigQueryInput_7Process(historical_dev_big_query_a.java:3332)

at project.historical_dev_big_query_a_0_1.historical_dev_big_query_a.runJobInTOS(historical_dev_big_query_a.java:5851)

at project.historical_dev_big_query_a_0_1.historical_dev_big_query_a.main(historical_dev_big_query_a.java:5092)