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

Question

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?

Community · Accepted Answer · 2017-05-23 12:29:51Z

2

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.

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Jul 6, 2015 at 10:02

Jiekebo

837 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pentium10 · Accepted Answer · 2015-07-06 09:11:35Z

0

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.

answered Jul 6, 2015 at 9:11

Pentium10

209k124 gold badges435 silver badges513 bronze badges

1 Comment

Jiekebo Over a year ago

Sorry... posted non-anonymized error detail, device_id is the real field name in the query.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related