CODEWITHSUNDEEP
google-bigquery
Jiekebo
Jiekebo

Reputation: 83

BigQuery can run query in console but not launch it as a job

I have a problem launching following query as a job:

SELECT COUNT(*) FROM
(
  SELECT field_name FROM [dataset.table]
  WHERE 
    time BETWEEN DATE_ADD(CURRENT_TIMESTAMP(), -30, "DAY") AND
         CURRENT_TIMESTAMP() AND
  GROUP EACH BY field_name
) AS cur_month
JOIN EACH
(
   SELECT field_name FROM [dataset.table]
   WHERE 
     time BETWEEN DATE_ADD(CURRENT_TIMESTAMP(), -60, "DAY") AND
          DATE_ADD(CURRENT_TIMESTAMP(), -30, "DAY") AND
   GROUP EACH BY field_name
) AS prev_month
ON cur_month.field_name = prev_month.field_name

Running this query in the console succeeds, but running it with the following java code fails

JobConfigurationQuery queryConfig = new JobConfigurationQuery()
    .setQuery(query)
    .setDestinationTable(new TableReference()
            .setProjectId(projectId)
            .setDatasetId(toDataset)
            .setTableId(toTableId))
    .setAllowLargeResults(true)
    .setCreateDisposition("CREATE_IF_NEEDED")
    .setWriteDisposition("WRITE_TRUNCATE")
    .setPriority("BATCH")
    .setFlattenResults(false);

The error I get is 

{
  "errorResult": {
    "location": "query",
    "message": "Ambiguous field name 'field_name' in JOIN. Please use the table qualifier before field name.",
    "reason": "invalidQuery"
  },
  "errors": [
    {
      "location": "query",
      "message": "Ambiguous field name 'field_name' in JOIN. Please use the table qualifier before field name.",
      "reason": "invalidQuery"
    }
  ],
  "state": "DONE"
}

Does anyone have an idea why?

Upvotes: 1

Views: 352

Answers (2)

Jiekebo
Jiekebo

Reputation: 83

The question is a duplicate of: BigQuery - same query works when submitted from UI and reports SQL syntax error from batch

Setting flatten results to true solved my problem.

Upvotes: 2

Pentium10
Pentium10

Reputation: 207912

You need to check with detail that you indeed execute the query you posted, as I don't see any device_id in the query, but the error message is clear:

Ambiguous field name 'device_id' in JOIN. Please use the table qualifier before field name.

You need to ensure the device_id column has the table qualifier before field name.

Upvotes: 0

Related Questions