Table Dimensions

We wish to get the number of rows and / or the number of columns of a table.

Row Count

We wish to obtain the number of rows in a table.

SELECT row_count
FROM refcon.dataset.__TABLES__
WHERE table_id = 'table_1';

Here is how this works:

  • __TABLES__ is a hidden metadata table that exists inside each BQ dataset.
  • We filter the results using the target table name to get the row_count

Column Count

We wish to obtain the number of columns in a table.

SELECT COUNT(1) AS column_count
FROM refcon.dataset.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table_1';

Here is how this works:

  • INFORMATION_SCHEMA provides metadata for each table in the dataset.
  • COLUMNS view returns the info for each column in the table.
  • We count the number of rows to get the column count.

Shape

We wish to obtain both the number of rows and the number of columns in a table. To achieve this we can implement a function shape to get the dimensions and reuse this function in the future.

CREATE OR REPLACE TABLE FUNCTION
    refcon.dataset.shape(v_table_name STRING)
AS
SELECT *
FROM (SELECT row_count
      FROM refcon.dataset.__TABLES__
      WHERE table_id = v_table_name) AS row_count
         CROSS JOIN
     (SELECT COUNT(1) AS column_count
      FROM refcon.dataset.INFORMATION_SCHEMA.COLUMNS
      WHERE table_name = v_table_name) AS column_count;

Here is how this works:

  • We pass the table name to the function shape().
  • shape() returns a two columns table with row_count and column_count.
  • The function logic is the combination of the above two sections.

To call this function we use it as a table with SELECT keyword.

SELECT *
FROM refcon.dataset.shape('table_1');
SQL
I/O