CODEWITHSUNDEEP
javagoogle-app-enginegoogle-bigquerygoogle-cloud-platformgoogle-cloud-sql
Dev Loper
Dev Loper

Reputation: 229

Google BiqQuery Unstable, Query Execution Failure on Almost all Table Queries : "Error: Error preparing subsidiary query: null"

Google BiqQuery Query Execution is failing on Almost all Table Queries. The Table Queries were working initially when we initially deployed our application. When the dataset started growing we started seeing below Errors. We have huge number of tables in each dataset . I am not sure whether it is linked with this error. However when we run this query on smaller dataset in a different biqquery account it is succeeding.

"Error: Error preparing subsidiary query: null"

Below Are some sample Queries which we are trying to Execute both from our application and Google Biq Query Web console and all our attempts were unsuccessful with above error. 

SELECT * FROM  (TABLE_DATE_RANGE([sampledataset.location], 
            TIMESTAMP('2016-07-10'), 
            TIMESTAMP('2016-07-11')))

Reference Biq Query JOB ID :bquijob_286a4921_155dd7d8868

SELECT count(*) FROM (TABLE_QUERY(sampledataset,'table_id CONTAINS "location201606"'))

Reference Biq Query JOB ID : bquijob_531d0e3_155dda8f7dc

Even When I am executing a Table Summary Query I am getting Error's . I am not sure whether this error is related. 

SELECT * FROM sampledataset.__TABLES_SUMMARY__ 
WHERE MSEC_TO_TIMESTAMP(creation_time)  < 
   DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')

Resources exceeded during query execution: Too many tables in dataset metatable :sampledataset.TABLES_SUMMARY, exceeded 500000 limit.

Because of these issues we think Gogole Biq Query is pretty Unstable and there are not much resources available to resolve our issues. Before even proceeding with smaller table partitions we did enquire in stackoverflow

Is there a limit to the number of tables allowed in bigquery ?

and we received a positive answer confirming that there are no limits from @jordan-tigani https://stackoverflow.com/users/1366527/jordan-tigani

Because of this issue we are even forced to migrate from Google Biq Query to AWS Redshift.

Update 1: As per the update from @Felipe Hoffa , it seems like TABLE_DATE_RANGE function provided from Big Query has issues when large number of tables are present in the given data set. At present there is no fix for this issue from Google Big Query Platform and Projects which were relying on TABLE_DATE_RANGE Functionality needs to migrate to "Partitioned Tables" (New Feature rolled out by Google) inorder for the application to be functional. I thought to highlight since it might help people while designing their schema for Biq Query since the limitations of TABLE_DATE_RANGE and similar functions are not documented elsewhere

Upvotes: 4

Views: 554

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59225

as Jordan says there is no limit of number of tables allowed in BigQuery - but there is a limit on how many tables can be queried in one query. How many tables are you hitting with one query here?

To work over the limit of tables permitted in one query, BigQuery rolled the new feature "partitioned tables". Here you can have a unique table - partitioned by day - that will allow you to query only the desired days, thus only incurring the proportional costs (instead of the whole table):

https://cloud.google.com/bigquery/docs/creating-partitioned-tables

Upvotes: 1

Related Questions