Menu
I have a huge BQ table with a complex schema (lots of repeated and record fields). Is there a way for me to add more columns to this table and/or create a select that would copy the entire table into a new one with the addition of one (or more) columns? It appears as if copying a table requires flattening of repeated columns (not good). I need an exact copy of the original table with some new columns.
I found a way to Update Table Schema but it looks rather limited as I can only seem to add nullable or repeated columns. I can't add record columns or remove anything.
If I were to modify my import JSON data (and schema) I could import anything. But my import data is huge and conveniently already in a denormalized gzipped JSON so changing that seems like a huge effort.
875
A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement. The new table has the same column definitions. All columns or specific columns can be selected.
Syntax. The basic syntax of an ALTER TABLE command to add a New Column in an existing table is as follows. ALTER TABLE table_name ADD column_name datatype; The basic syntax of an ALTER TABLE command to DROP COLUMN in an existing table is as follows.
I think you can add fields of type RECORD.
Nullable and repeated refer to field's mode, not type. So you can add a Nullable record or a Repeated record, but cannot add a Required record.
https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
You are correct that you cannot delete anything.
153
If you want to use a query to copy the table, but don't want nested and repeated fields to be flattened, you can set the flattenResults parameter to false to preserve the structure of your output schema.
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
