I realize there's a million ways to get a schema from a dataset.table in google big query....
is there a way to get schema data via a select statement? such like querying sql servers INFORMATION_SCHEMA table?
Thanks.
2 Answers
Mikhail's answer is still relevant if the goal is to compute information like the number of null values and non-null values per column. To answer the original question, though, BigQuery provides support for INFORMATION_SCHEMA views, which are in beta at the time of this writing. If you want to get the schema of a table, you can query the COLUMNS view, e.g.:
SELECT column_name, data_type
FROM `fh-bigquery`.reddit.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'subreddits'
ORDER BY ordinal_position
This returns:
Row column_name data_type
1 subr STRING
2 created_utc TIMESTAMP
3 score INT64
4 num_comments INT64
5 c_posts INT64
6 ups INT64
7 downs INT64
Related