CODEWITHSUNDEEP
google-bigquery
selva kumar
selva kumar

Reputation: 1156

How to get BigQuery storage size for a single table

I want to calculate table wise cost for Google Big Query Storage, But i don't know how to view size of storage for each table individually.

Upvotes: 42

Views: 62884

Answers (5)

pjames
pjames

Reputation: 302

A more modern way to do this across an entire dataset or org would be to use the TABLE_STORAGE or TABLE_STORAGE_BY_ORGANIZATION views.

SELECT * FROM `{project-id}.{region}.INFORMATION_SCHEMA.TABLE_STORAGE`;

The docs provide how to do things like Forecast storage billing.

Upvotes: 1

Aniket Ghole
Aniket Ghole

Reputation: 192

Just use the command-line tool. You will see the table with columns such as Last-Modified, Schema, Total Rows, Total Bytes and so forth.

  1. For Single Table

bq show dataset_name.table_name

  1. For All Tables

bq show dataset_name.*

Upvotes: 3

Pentium10
Pentium10

Reputation: 207893

There are a couple of ways to do this, but be aware the size of the table in bytes property is unavailable for tables that are actively receiving streaming inserts.

A. Using the BQ command line tool, and the JQ linux library to parse JSON.

bq --format=json show publicdata:samples.gsod | jq '.numBytes | tonumber'

This outpus:

17290009238

B. Using the REST api to do a Tables:get call

GET https://www.googleapis.com/bigquery/v2/projects/projectId/datasets/datasetId/tables/tableId

this returns a full JSON, that you can parse and get the numBytes.

{
    "kind": "bigquery#table",
    "description": "This dataset contains weather information collected by NOAA, such a…",
    "creationTime": "1335916040125",
    "tableReference": {
        "projectId": "publicdata",
        "tableId": "gsod",
        "datasetId": "samples"
    },
    "numRows": "114420316",
    "numBytes": "17290009238",
    "etag": "\"Gn3Hpo5WaKnpFuT457VBDNMgZBw/MTQxMzkzNzk4Nzg0Ng\"",
    "location": "US",
    "lastModifiedTime": "1413937987846",
    "type": "TABLE",
    "id": "publicdata:samples.gsod",
    "selfLink": "https://www.googleapis.com/bigquery/v2/projects/publicdata/datasets…",
    "schema": {
        "fields": [
            {
                "description": "The World Meteorological Organization (WMO) / DATSAV3 station numbe…",
                "type": "INTEGER",
                "name": "station_number",
                "mode": "REQUIRED"
            },
            {
                "description": "The Weather-Bureau-Army-Navy (WBAN) station number where the data w…",
                "type": "INTEGER",
                "name": "wban_number",
                "mode": "NULLABLE"
            },
            {
                "description": "The year the data was collected in",
                "type": "INTEGER",
                "name": "year",
                "mode": "REQUIRED"
            },
            {
                "description": "The month the data was collected in",
                "type": "INTEGER",
                "name": "month",
                "mode": "REQUIRED"
            },
            {
                "description": "The day the data was collected in.",
                "type": "INTEGER",
                "name": "day",
                "mode": "REQUIRED"
            },
            {
                "description": "The mean temperature of the day in degrees Fahrenheit, accurate to …",
                "type": "FLOAT",
                "name": "mean_temp",
                "mode": "NULLABLE"
            },
            {
                "description": "The number of observations used to calculate mean_temp.",
                "type": "INTEGER",
                "name": "num_mean_temp_samples",
                "mode": "NULLABLE"
            },
            {
                "description": "The mean dew point of the day in degrees Fahrenheit, accurate to on…",
                "type": "FLOAT",
                "name": "mean_dew_point",
                "mode": "NULLABLE"
            },
            {
                "description": "The number of observations used to calculate mean_dew_point.",
                "type": "INTEGER",
                "name": "num_mean_dew_point_samples",
                "mode": "NULLABLE"
            },
            {
                "description": "The mean sea level pressure of the day in millibars, accurate to on…",
                "type": "FLOAT",
                "name": "mean_sealevel_pressure",
                "mode": "NULLABLE"
            },
            {
                "description": "The number of observations used to calculate mean_sealevel_pressure…",
                "type": "INTEGER",
                "name": "num_mean_sealevel_pressure_samples",
                "mode": "NULLABLE"
            },
            {
                "description": "The mean station pressure of the day in millibars, accurate to one …",
                "type": "FLOAT",
                "name": "mean_station_pressure",
                "mode": "NULLABLE"
            },
            {
                "description": "The number of observations used to calculate mean_station_pressure.…",
                "type": "INTEGER",
                "name": "num_mean_station_pressure_samples",
                "mode": "NULLABLE"
            },
            {
                "description": "The mean visibility of the day in miles, accurate to one tenth of a…",
                "type": "FLOAT",
                "name": "mean_visibility",
                "mode": "NULLABLE"
            },
            {
                "description": "The number of observations used to calculate mean_visibility.",
                "type": "INTEGER",
                "name": "num_mean_visibility_samples",
                "mode": "NULLABLE"
            },
            {
                "description": "The mean wind speed of the day in knots, accurate to one tenth of a…",
                "type": "FLOAT",
                "name": "mean_wind_speed",
                "mode": "NULLABLE"
            },
            {
                "description": "The number of observations used to calculate mean_wind_speed.",
                "type": "INTEGER",
                "name": "num_mean_wind_speed_samples",
                "mode": "NULLABLE"
            },
            {
                "description": "The maximum sustained wind speed reported on the day in knots, accu…",
                "type": "FLOAT",
                "name": "max_sustained_wind_speed",
                "mode": "NULLABLE"
            },
            {
                "description": "The maximum wind gust speed reported on the day in knots, accurate …",
                "type": "FLOAT",
                "name": "max_gust_wind_speed",
                "mode": "NULLABLE"
            },
            {
                "description": "The maximum temperature of the day in degrees Fahrenheit, accurate …",
                "type": "FLOAT",
                "name": "max_temperature",
                "mode": "NULLABLE"
            },
            {
                "description": "Indicates the source of max_temperature.",
                "type": "BOOLEAN",
                "name": "max_temperature_explicit",
                "mode": "NULLABLE"
            },
            {
                "description": "The minimum temperature of the day in degrees Fahrenheit, accurate …",
                "type": "FLOAT",
                "name": "min_temperature",
                "mode": "NULLABLE"
            },
            {
                "description": "Indicates the source of min_temperature.",
                "type": "BOOLEAN",
                "name": "min_temperature_explicit",
                "mode": "NULLABLE"
            },
            {
                "description": "The total precipitation of the day in inches, accurate to one hundr…",
                "type": "FLOAT",
                "name": "total_precipitation",
                "mode": "NULLABLE"
            },
            {
                "description": "The snow depth of the day in inches, accurate to one tenth of an in…",
                "type": "FLOAT",
                "name": "snow_depth",
                "mode": "NULLABLE"
            },
            {
                "description": "Indicates if fog was reported on this day.",
                "type": "BOOLEAN",
                "name": "fog",
                "mode": "NULLABLE"
            },
            {
                "description": "Indicates if rain was reported on this day.",
                "type": "BOOLEAN",
                "name": "rain",
                "mode": "NULLABLE"
            },
            {
                "description": "Indicates if snow was reported on this day.",
                "type": "BOOLEAN",
                "name": "snow",
                "mode": "NULLABLE"
            },
            {
                "description": "Indicates if hail was reported on this day.",
                "type": "BOOLEAN",
                "name": "hail",
                "mode": "NULLABLE"
            },
            {
                "description": "Indicates if thunder was reported on this day.",
                "type": "BOOLEAN",
                "name": "thunder",
                "mode": "NULLABLE"
            },
            {
                "description": "Indicates if a tornado was reported on this day.",
                "type": "BOOLEAN",
                "name": "tornado",
                "mode": "NULLABLE"
            }
        ]
    }
}

C. There are metatables called __TABLES__ and __TABLES_SUMMARY__

You can run a query like:

SELECT size_bytes FROM <dataset>.__TABLES__ WHERE table_id='mytablename'

The __TABLES__ portion of that query may look unfamiliar. __TABLES_SUMMARY__ is a meta-table containing information about tables in a dataset. You can use this meta-table yourself. For example, the query SELECT * FROM publicdata:samples.__TABLES_SUMMARY__ will return metadata about the tables in the publicdata:samples dataset. You can also do SELECT * FROM publicdata:samples.__TABLES__

Available Fields:

The fields of the __TABLES_SUMMARY__ meta-table (that are all available in the TABLE_QUERY query) include:

  • table_id: name of the table.
  • creation_time: time, in milliseconds since 1/1/1970 UTC, that the table was created. This is the same as the creation_time field on the table.
  • type: whether it is a view (2) or regular table (1).

The following fields are not available in TABLE_QUERY() since they are members of __TABLES__ but not __TABLES_SUMMARY__. They're kept here for historical interest and to partially document the __TABLES__ metatable:

  • last_modified_time: time, in milliseconds since 1/1/1970 UTC, that the table was updated (either metadata or table contents). Note that if you use the tabledata.insertAll() to stream records to your table, this might be a few minutes out of date.
  • row_count: number of rows in the table.
  • size_bytes: total size in bytes of the table.

Upvotes: 16

Quentin
Quentin

Reputation: 1037

Or from the GUI, you can use the metadata internal table __TABLES__ , for example this will give you the size in GB:

select 
  sum(size_bytes)/pow(10,9) as size
from
  <your_dataset>.__TABLES__
where 
  table_id = '<your_table>'

Upvotes: 73

user1692732
user1692732

Reputation: 81

You can do this with command line tool

bq show ds_name.table_name

It will show some info aboout the table, including "Total Bytes". Reference here https://cloud.google.com/bigquery/bq-command-line-tool

Upvotes: 8

Related Questions