CODEWITHSUNDEEP
google-bigquery
Stewart_R
Stewart_R

Reputation: 14485

BigQuery - Possible to query for column datatype

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?

Upvotes: 10

Views: 45808

Answers (2)

Suhkjin Hur
Suhkjin Hur

Reputation: 103

You can find 'JOB INFORMATION' tab within the query results, and one of job info is 'Destination table'.

(Or you can see every recent(one week?) query result you executed in the 'PERSONAL HISTORY' tab below the query results and clicking 'Job ID' in the history list will lead you to some details page in which you can find a 'Destination table'.)

The temporary destination table (click the link) will show detailed information of the result table which includes data type of each column.

Upvotes: 2

Tomáš Záluský
Tomáš Záluský

Reputation: 12000

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.

Upvotes: 16

Related Questions