Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Parameter Validation Failure: SQL Query - Timeout.

Parameter Validation Failure: SQL Query - Timeout.

I have setup variables on table name so i can run through all my bigquery google analytics tables:
`139139762.ga_sessions_20180705`
`139139762.ga_sessions_20180704`
`139139762.ga_sessions_20180703`

I am using advanced mode for BigQuery and have the SQL syntax in there. If I hard code the table name into the SQL query it works fine, but when I try and use it as a variable it keeps failing with the timeout message.

What is causing this?
Is there a way to increase the timeout period past a minute?

10 Community Answers

Matillion Agent  

Laura Malins —

Hi Joseph

Does your variable have a valid default value? This is often the cause of a validation failure.

Thanks
Laura


Joseph Meyer —

yes it does have a valid default value


Joseph Meyer —

I have also tested it. I had it run all night with 30 different values (to test if it could get through some of the tables. It got through about 5 of the 30 and the rest timed out. So it is clearly working just not working all the time for some reason.


Joseph Meyer —

One more update. I just kicked it off again. Yesterday, the file with 20180629 failed with timeout. Changed nothing about the job and just kicked it off again today. That one now worked.

Is there something I can do to address this issue? If I can't rely on this to actually complete as expected doesn't seem like an ideal ETL tool


Matillion Agent  

Laura Malins —

Hi Joseph

Can you try setting a Connection Option in the BigQuery component? Please set QueryPassThrough to be true.

Thanks
Laura


Joseph Meyer —

I actually had that set already. I saw it as a suggestion in another post.


Matillion Agent  

Laura Malins —

Hi Joseph

I think there are 2 issues here then:

1. The component doesn’t validate.

2. Issues accessing your ga_sessions tables.

Can you please confirm if you are able to get the component to validate when the variable has a default which is for a table which you can access?

There is a timeout you can set in the Connection Options of the BigQuery component. The default is 60 seconds. You can try increasing this if you’re getting timeouts on many of the tables.

Thanks
Laura


Joseph Meyer —

So the same query I ran yesterday only had 5 out of 30 complete. Today nothing changed. It is on 12/12 currently and still running. So I don't think it is the variable itself or the tables being incorrect.

Let me try increasing the timeout connection to like 4-5 minutes and see if that stops the timeout issues


Matillion Agent  

Laura Malins —

Hi Joseph

Thanks

Please do keep us updated.

Thanks
Laura


Joseph Meyer —

seems to be working now. all weekend when I was trying kept getting that timeout error for some reason

Post Your Community Answer

To add an answer please login