Unexpected Error on BigQuery job

Question

I run a job in BigQuery that creates a new table from the result of a SQL query. The job is created through an HTTP call:

{
  "kind": "bigquery#job",
  "etag": "\"RKD_ArsVTw2JJ2le9dUIQIRuBWo/THCPayJQXAazqZRGUmb8X3v83D4\"",
  "id": "project_id:job_id",
  "selfLink": "https://www.googleapis.com/bigquery/v2/projects/project_id/jobs/job_id",
  "jobReference": {
    "projectId": "project_id",
    "jobId": "job_id"
  },
  "configuration": {
    "query": {
      "query": "SELECT * FROM dataset.Fact_Nos JOIN dataset.Dim_nos ON dataset.Fact_Nos.b_categorie = dataset.Dim_nos.b_categorie WHERE dataset.Fact_Nos.b_date < '2013-11-22T10:04:45.3' AND nostrum.Fact_Nos.b_date > '2013-11-22T09:57:21.2'",
      "destinationTable": {
        "projectId": "project_id",
        "datasetId": "dataset",
        "tableId": "MaSter"
      },
      "createDisposition": "CREATE_IF_NEEDED",
      "writeDisposition": "WRITE_APPEND",
      "defaultDataset": {
        "datasetId": "dataset",
        "projectId": "project_id"
      },
      "allowLargeResults": true
    }
  },
  "status": {
    "state": "RUNNING"
 },
 "statistics": {
   "creationTime": "1385114039746",
   "startTime": "1385114039888"
 }
}

The table is not created and the web UI displays "Unexpected. PLease try again" in the errors fields of the query.

Here is the SQL query:

SELECT * FROM dataset.Fact_Nos JOIN dataset.Dim_nos ON dataset.Fact_Nos.b_categorie = 
dataset.Dim_nos.b_categorie WHERE dataset.Fact_Nos.b_date < '2013-11-22T10:04:45.3' AND 
nostrum.Fact_Nos.b_date > '2013-11-22T09:57:21.2'

The query runs perfectly fine in the web UI. The HTTP call response is 200 and the job is running normally until it is done.

Any insight ? The errors fields don't say any useful information.

EDIT: I have also tested to add the preserveNulls field to true without success.

Jeremy Condit Jeremy Condit · Accepted Answer · 2013-11-22T17:15:28

Your WHERE clause compares a timestamp (b_date) to a string, which BigQuery doesn't allow. I'd recommend using the TIMESTAMP function to convert those strings to timestamps before comparison.

See https://developers.google.com/bigquery/query-reference#datetimefunctions for more info.

We'll investigate to see if we can figure out why the error message is not showing up for you.

Unexpected Error on BigQuery job

1 Answers