Error - “Unexpected. Please try again.” in BigQuery

Question

I am trying to move data from one table to another by using "select *" and "--destination_table" option, but I keep getting the "Unexpected" error. Trying the same query with a where clause, which limits the data to a five second interval, results in success.

Failed JobId - divine-builder-586:bqjob_r7fe3d3a191881823_0000014662d302c5_1

Successful JobId - divine-builder-586:bqjob_r7f879846473f9b08_00000146631d5857_1

Any Ideas?

The query which succeeds is as follows :

Select tstamp,uniq_id,INTEGER(ip) as ip,devtype, FLOAT(price) as price,FLOAT(altitude) as altitude,FLOAT(longitude) as longitude,FLOAT(latitude) as latitude FROM [bigquery_processed.table_rc2] WHERE tstamp >='2014-05-01 00:00:00' and tstamp <'2014-05-01 00:00:05'

Job ID: divine-builder-586:bqjob_r7f879846473f9b08_00000146631d5857_1

Start Time: 12:33am, 4 Jun 2014

End Time: 12:41am, 4 Jun 2014

Bytes Processed: 22.8 TB

Destination Table: divine-builder-586:bigquery_processed.table_rc3

Write Preference: Append to table

Allow Large Results: true

The query which fails does not have where clause, rest is same.

Please share the text of both queries (the one that fails & the one that succeeds). — IanGSY
Thanks, query looks ok. It could be that some of the data in rows where tstamp >='2014-05-01 00:00:00' and tstamp <'2014-05-01 00:00:05' is missing/corrupt and so causes the query to fail. For example a value for price may not be parsable as an integer. — IanGSY
It may sound strange, but have you tried the query using a where clause like WHERE 1=1 ? — IanGSY

Rohit Rohit · Accepted Answer · 2014-06-04T07:53:54

One workaround is to split the data into smaller parts using a WHERE clause with the mod function.

Try this?

Select tstamp,uniq_id,INTEGER(ip) as ip,devtype, FLOAT(price) as price,FLOAT(altitude) as altitude,FLOAT(longitude) as longitude,FLOAT(latitude) as latitude FROM [bigquery_processed.table_rc2] WHERE ABS(HASH(devtype) % 2) = 0

And then append the table with same query, just with WHERE ABS(HASH(devtype) % 2) = 1.

Honestly, this should only be a temp workaround. It should be able to run your original query since there are no JOINs or GROUP BYs with huge buckets.

Error - “Unexpected. Please try again.” in BigQuery

2 Answers