I am constructing a table using given values like this
SELECT * FROM UNNEST
([ STRUCT
(1, 3.07766, 6.31371, 12.7062, 63.65600),
(2, 1.88562, 2.91999, 4.30265, 9.92482),
(3, 1.63774, 2.35336, 3.18243, 5.84089)
])
which creates columns with names fn_
as this
Row | f0_ | f1_ | f2_ | f3_ | f4_ |
---|
I would like to name columns without putting AS statements into STRUCT, so not like
SELECT * FROM UNNEST
([ STRUCT
(1 as a, 3.07766 as b, 6.31371 as c, 12.7062 as d, 63.65600 as e),
(2, 1.88562, 2.91999, 4.30265, 9.92482),
(3, 1.63774, 2.35336, 3.18243, 5.84089)
])
I understand that it works, but the question is can we do something like
SELECT * FROM UNNEST
([ STRUCT
(1, 3.07766, 6.31371, 12.7062, 63.65600),
(2, 1.88562, 2.91999, 4.30265, 9.92482),
(3, 1.63774, 2.35336, 3.18243, 5.84089)
]) as (a, b, c, d, e)
and that is because this SELECT is constructed by Python code to be used in MERGE statement that updates existing values in a table with a constructed table.
If there is any other way you think it's better to construct temporary table with given values in BigQuery, please share your ideas!
Use below option
SELECT * FROM UNNEST
([ STRUCT<a int64, b float64, c float64, d float64, e float64>
(1, 3.07766, 6.31371, 12.7062, 63.65600),
(2, 1.88562, 2.91999, 4.30265, 9.92482),
(3, 1.63774, 2.35336, 3.18243, 5.84089)
])
from your Python code point of view - it should be equivalent to what you want. So that line with column names - moved to STRUCT definition line and does not touch the values rows