René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"client.insert_rows should fail when inserting non-existing fields","articleBody":"Version\r\n```\r\ngoogle-cloud-bigquery==1.25.0\r\n```\r\n\r\nThe [`client.insert_rows()`](https://github.com/googleapis/python-bigquery/blob/v1.25.0/google/cloud/bigquery/client.py#L2471) function doesn't fail when inserting non-existing fields whereas the BigQuery API does fail with a message like\r\n```\r\n{\r\n  \"kind\": \"bigquery#tableDataInsertAllResponse\",\r\n  \"insertErrors\": [\r\n    {\r\n      \"index\": 0,\r\n      \"errors\": [\r\n        {\r\n          \"reason\": \"invalid\",\r\n          \"location\": \"zap\",\r\n          \"debugInfo\": \"\",\r\n          \"message\": \"no such field.\"\r\n        }\r\n      ]\r\n    }\r\n  ]\r\n}\r\n```\r\n`insert_rows()` silently drops the additional columns instead.\r\n\r\nThis happens because `insert_rows()` uses [`_record_field_to_json`](https://github.com/googleapis/python-bigquery/blob/3869e34f3eff6fabca21c397e3f1cbc368ec880e/google/cloud/bigquery/_helpers.py#L409) which only iterates over the list of fields that are provided ignoring all the other fields that are part of the data and `insert_rows()` passes the table's schema as list of fields to `_record_field_to_json`.\r\n\r\nThis behavior is opposite to the BigQuery API and means we cannot reliably insert data because we're not made aware of changes to the incoming data because there's no failure.\r\nIMHO this behavior is not correct, I think it would be OK if `selected_fields` was provided but it should not silently use the schema to limit which of the fields of the input data are processed and ignore the rest.\r\nI can image there might be cases where one wants to be lenient/ignore all fields that are not part of table so this behavior might have to be an option, possibly combined with `selected_fields`.\r\n\r\nP.S. By extension this also applies to the [`client.insert_rows_from_dataframe()`](https://github.com/googleapis/python-bigquery/blob/v1.25.0/google/cloud/bigquery/client.py#L2533) function which uses `client.insert_rows()`.\r\n\r\nP.P.S We initially ran into this when using `insert_rows_from_dataframe()` and it was a bit of a search to find where this was going wrong because it's a somewhat indirect chain of `insert_rows_from_dataframe -\u003e insert_rows -\u003e insert_rows_json`.\r\nWhy was this long way chosen/added instead of simply using `insert_rows_json(table, df.to_dict(orient=\"records\"))`? It seems a lot simpler and will probably be the workaround we'll implement for now.","author":{"url":"https://github.com/simonvanderveldt","@type":"Person","name":"simonvanderveldt"},"datePublished":"2020-06-26T14:16:11.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":2},"url":"https://github.com/151/python-bigquery/issues/151"}