BigQuery - Possible to query for column datatype

Question

Is there a function that would allow us query the datatype of the column in BigQuery?

I'd like to find something like this:

WITH basket AS
    (SELECT "apple" AS fruit, 10 AS qty
    UNION ALL SELECT "pear" AS fruit, 7 AS qty
    UNION ALL SELECT "banana" AS fruit, 9 AS qty)

-- !!this doesn't work!!:
SELECT
    fruit,
    qty,
    DATA_TYPE(fruit) AS type1, 
    DATA_TYPE(qty) AS type2 
FROM basket

in order to return something like this:

+--------+-----+--------+-------+
| fruit  | qty | type1  | type2 |
+--------+-----+--------+-------+
| apple  |  10 | STRING | INT64 |
| pear   |   7 | STRING | INT64 |
| banana |   9 | STRING | INT64 |
+--------+-----+--------+-------+

Of course, my DATA_TYPE() function doesn't exist and I couldn't find anything comparable in the docs

Does this functionality exist somewhere that I am missing?

Answer 1

You can define basket as BQ view, then use SQL query

select column_name, data_type
from `your_project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
where table_name = 'basket'
order by ordinal_position

You obtain requested type in data_type column.

I didn't find any way how to get SQL metadata directly from BQ query (analogy to Java ResultSet.getMetaData().getColumnTypeName()) and AFAIK it is not possible.

Theoretically it is possible that BQ client who calls BQ query will be changed to create view through API call, fetch metadata, fetch data and drop view but it is error-prone in many aspects (need for stronger rights, race conditions, billing issues). I didn't try this way. However, if you manage to gather all columns whose types you need to obtain to permanent view, it could be solution for your case.