CODEWITHSUNDEEP
sqlgoogle-bigquery
Shaorong Liu
Shaorong Liu

Reputation: 29

How to get table/col stats from BigQuery tables

Does BigQuery provides any sql commands for retrieving table cardinality?

For example, some RDBMS providers have sql commands like:

show table_stats schemaname tablename

for getting table cardinality.

Also, what about column stats? Like the number of distinct values in a col and MIN, MAX, etc.

I saw that the BigQuery console provides both table and col stats but I wonder whether these info are accessible through SQL statements

Thanks!

Upvotes: 1

Views: 4070

Answers (1)

Kevin Quinzel
Kevin Quinzel

Reputation: 1428

The features you would like to use are more proper for the language, instead of the tool or service itself.

  • To get stats about the table. I found the Getting table metadata which explains how to get table metadata for Tables and Columns. Some of the information you will get when running the queries found in that doc.

For Tables: the name of the dataset that contains the table, the default lifetime, in days and other Table_Options view results.

For Columns: The name of the project that contains the dataset, the the column's standard SQL data type, if the value is updatable, stored, or hidden. Find more Results for the Columns view.

  • To get stats about columns. You can use the COUNT DISTINCT function, which retrieves a statistical approximation on the unique values in certain columns.

I found this Community blog, where they show different examples and ways to get unique values. It even explains how to increase the approximation threshold.

EDIT

It seems that BigQuery does not offer a count of unique fields. However, you can always take a look at the Schema and Details tabs in your BigQuery UI where the Fields' name are shown, including the type and description.

Example from the Public Datasets:

Public Dataset "Crime"

Hope this is helpful.

Upvotes: 1

Related Questions