BigQuery: Get size of each row in table

user_10819889 0
Viewed 3311

In BigQuery, for a query, I'm getting the following error message:

Cannot query rows larger than 100MB limit.

I understand the limit, however, I'd like to debug this a bit more and search for the row(s) which are larger than 100MB.

Does someone know if a function within BigQuery exists - or is there another way - to get the size of each row in a table?

google-bigquery
2 Answers

I don't think there is an out of the box (e.g. a function in BQ) solution to this.

What I often do and helps is to user the __TABLES__ metadata table of a dataset, in order to get an estimation of the average size per row for a table

So, my query would look like this

SELECT 
  table_id, 
  (size_bytes/row_count) / 1000000 as row_size -- avg row size in MB
FROM `my_dataset.my_table.__TABLES__`
user_59395910

The size of a row is defined by the data types of the respective values. You can add up your static columns (e.g. Numeric) and add the size of dynamically sized data (e.g. Strings) with a function.

Perform this on a subset of columns (I assume that the error appears when the row gets to big because of a JOIN) and then you can find the rows that are unusually large and treat them accordingly.

Data Type Sizes

Data type             Size
INT64/INTEGER         8 bytes
FLOAT64/FLOAT         8 bytes
NUMERIC               16 bytes
BIGNUMERIC (Preview)  32 bytes
BOOL/BOOLEAN          1 byte
STRING                2 bytes + the UTF-8 encoded string size
BYTES                 2 bytes + the number of bytes in the value
DATE                  8 bytes
DATETIME              8 bytes
TIME                  8 bytes
TIMESTAMP             8 bytes
STRUCT/RECORD         0 bytes + the size of the contained fields
GEOGRAPHY             16 bytes + 24 bytes * the number of vertices in the geography type (you can verify the number of vertices using the ST_NumPoints function)

Null values for any data type are calculated as 0 bytes.

A repeated column is stored as an array, and the size is calculated based on the number of values. For example, an integer column (INT64) that is repeated (ARRAY) and contains 4 entries is calculated as 32 bytes (4 entries x 8 bytes).

Source: Data size calculation - BigQuery Documentation

Similar Question: How many bytes in BigQuery types (StackOverflow)

Example

Example when you have 2 String Columns, 1 Numeric column and 1 Datetime column:

SELECT 2 + BYTE_LENGTH(string_column1) 
       + 2 + BYTE_LENGTH(string_column2) 
       + 16 -- NUMERIC -> 16 Bytes
       + 8 -- DATETIME -> 8 Bytes
       AS ROW_SIZE
       FROM `project-name.dataset-name.table-name`

Source: String Byte Length Calculation

user_62314400
Related