Resolving error: returned “Output field used as input”

Question

I'm trying to create a BigQuery table using Python. Other operations (queries, retrieving table bodies etc.) are working fine, but when trying to create a table I'm stuck with an error:

apiclient.errors.HttpError: https://www.googleapis.com/bigquery/v2/projects/marechal-consolidation/datasets/marechal_results/tables?alt=json returned "Output field used as input">

Here's the command I'm executing:

projectId = 'xxxx'
dataSet = 'marechal_results'
with open(filePath+'tableStructure.json') as data_file:
    structure = json.load(data_file)
table_result = tables.insert(projectId=projectId, datasetId=dataSet, body=structure).execute()

JSON table:

{
  "kind": "bigquery#table",

  "tableReference": {
    "projectId": "xxxx",
    "tableId": "xxxx",
    "datasetId": "xxxx"
  },

  "type": "table",
  "schema": {
    "fields": [
      {
        "mode": "REQUIRED",
        "type": "STRING",
        "description": "Company",
        "name": "COMPANY"
      },
      {
        "mode": "REQUIRED",
        "type": "STRING",
        "description": "Currency",
        "name": "CURRENCY"
      }
// bunch of other fields follow...
    ]
  }
}

Why am I receiving this error?

EDIT: Here's the JSON object I'm passing as parameter:

{
  "kind": "bigquery#table",
  "type": "TABLE",
  "tableReference": {
    "projectId": "xxxx",
    "tableId": "xxxx",
    "datasetId": "xxxx"
  },    
  "schema": {
    "fields": [
      {
        "type": "STRING",
        "name": "COMPANY"
      },
      {
        "type": "STRING",
        "name": "YEAR"
  },
  {
    "type": "STRING",
    "name": "COUNTRY_ISO"
  },
  {
    "type": "STRING",
    "name": "COUNTRY"
  },
  {
    "type": "STRING",
    "name": "COUNTRY_GROUP"
  },
  {
    "type": "STRING",
    "name": "REGION"
  },
  {
    "type": "STRING",
    "name": "AREA"
  },
  {
    "type": "STRING",
    "name": "BU"
  },
  {
    "type": "STRING",
    "name": "REFERENCE"
  },
  {
    "type": "FLOAT",
    "name": "QUANTITY"
  },
  {
    "type": "FLOAT",
    "name": "NET_SALES"
  },
  {
    "type": "FLOAT",
    "name": "GROSS_SALES"
  },
  {
    "type": "STRING",
    "name": "FAM_GRP"
  },
  {
    "type": "STRING",
    "name": "FAMILY"
  },
  {
    "type": "STRING",
    "name": "PRESENTATION"
  },
  {
    "type": "STRING",
    "name": "ORIG_FAMILY"
      },
      {
        "type": "FLOAT",
        "name": "REF_PRICE"
      },
      {
        "type": "STRING",
        "name": "CODE1"
      },
      {
        "type": "STRING",
        "name": "CODE4"
      }
    ]
  }
}

Have you tried json.loads()? Can you log the output of 'structure' to see if the body is being mangled? — Ryan
Hi Ryan, sry I'm coming back late on this, got caught up on other stuff.... anyhow: having printed the structure object I realized the json.dump was creating a dictionary with single quotes rather than json with doubles I modified the code to produce a proper json, but still getting an error, although now the error is "Required parameter is missing" so I'm now trying to find out what the missing parameter could be — Thibault Lefevre
so.... with help from the Google support team this is where I land: still no luck using the table.insert() API method, however I was able to create a table by posting a manually constructed http request with: url: 'googleapis.com/bigquery/v2/projects{PROJECT_ID}/datasets/{DATASET_ID}/tables?key={OAUTH2_CLIENT_ID} header: {'Content-Type': 'application/json'} (passed as a dictionary) body: JSON object with kind, tableReference and schema details — Thibault Lefevre

bygrace bygrace · Accepted Answer · 2019-06-28T19:15:41

This is probably too late to help you but hopefully it helps the next poor soul like me. It took me a while figure out what "Output field used as input" meant.

Though the API specifies the same object for the request (input) and response (output), some fields are only allowed in the response. In the docs you will see their descriptions prefixed with "Output only". From looking at your table definition I see that you have "type": "TABLE" and "type" is listed as an "Output only" property. So I would gander that if you remove it then that error will go away. Here is the link to the docs: https://cloud.google.com/bigquery/docs/reference/rest/v2/tables

It would help if they told you what field the violation was on.

Resolving error: returned “Output field used as input”

1 Answers