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

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? 

 

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)

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

Talend Integration with Databricks

Take a look at this video about Talend Integration with Databricks

Watch Now