Is there a way to pretty format Big Query "value table" result sets with the bq command line tool using Standard SQL? This works as expected in the BigQuery console with Standard SQL but not in bq. I cannot find a convenient workaround. It also works as expected using Legacy SQL.
My use case: I am exploring tables with large numbers of columns in BigQuery. Anonymous queries in BigQuery require that the selected columns be uniquely named, otherwise you get the message "Duplicate column names in the result are not supported. Found duplicate(s):...". This is a very strict requirement when I am just exploring the data. Fortunately, there is a way around this in the BigQuery web console by using value tables in the select expression...
with
left_side as
(
select 1 as id, 'hello' as col1 union all
select 2 as id, 'there' as col1
)
,right_side as
(
select 1 as id, 'hello' as col1 union all
select 2 as id, 'world' as col1
)
select a, b
from left_side a
join
right_side b
on a.id = b.id
... which results in the following tabular result set with the columns automatically enumerated which is excellent...
However the exact same query when passed to bq on the command line as follows (via a here document for your convenience). Normally, I have the query in a file and redirect it into bq, for example, bq query --use_legacy_sql=false < bq_test4.sql
bq query --use_legacy_sql=false <<BQ_TEST
with
left_side as
(
select 1 as id, 'hello' as col1 union all
select 2 as id, 'there' as col1
)
,right_side as
(
select 1 as id, 'hello' as col1 union all
select 2 as id, 'world' as col1
)
select a, b
from left_side a
join
right_side b
on a.id = b.id
BQ_TEST
...results in this same essential result set regardless of any --format options passed to bq...
+---------------------------+---------------------------+
| a | b |
+---------------------------+---------------------------+
| {"id":"1","col1":"hello"} | {"id":"1","col1":"hello"} |
| {"id":"2","col1":"there"} | {"id":"2","col1":"world"} |
+---------------------------+---------------------------+
Such a result set is not helpful to me.
Ok, I can convert the json to a tabular result set with...
with
left_side as
(
select 1 as id, 'hello' as col1 union all
select 2 as id, 'there' as col1
)
,right_side as
(
select 1 as id, 'hello' as col1 union all
select 2 as id, 'world' as col1
)
select json_extract_scalar(to_json_string(a), "$['id']") as `a_id`
,json_extract_scalar(to_json_string(a), "$['col1']") as `a_col1`
,json_extract_scalar(to_json_string(b), "$['id']") as `b_id`
,json_extract_scalar(to_json_string(b), "$['col1']") as `b_col1`
from left_side a
join
right_side b
on a.id = b.id
... which results in and which is exactly what Legacy SQL would produce...
+------+--------+------+--------+
| a_id | a_col1 | b_id | b_col1 |
+------+--------+------+--------+
| 1 | hello | 1 | hello |
| 2 | there | 2 | world |
+------+--------+------+--------+
Having to enumerate the columns defeats the intention of my use case.
Is there any way, while using Standard SQL, of avoiding enumerating the columns either prior to my join or after my join?
