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

[dwhdeveloper]

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? 

[Anonymous]

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

[dwhdeveloper]

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')))

[dwhdeveloper]

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)

[Anonymous]

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

Regards
Shong

[dwhdeveloper]

Talend Open Source Version: 6.3.0

[Anonymous]

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

[dwhdeveloper]

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)