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.
Asked
Active
Viewed 4.7k times
5 Answers
60
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>'
Quentin
- 907
- 7
- 13
-
5Just a comment, Filter WHERE can be ommited and you would get the whole dataset size. – julianm Oct 24 '17 at 18:22
-
Great, thanks! I really thought you couldn't access this metadata without being an admin. Have you got a list of all properties available in __TABLES__? – Dodecaphone Aug 30 '18 at 06:56
-
Sorry, I was able to answer my own question with SELECT *. These queries appear to retrieve 0 billable bytes, so they're free. You can get: project_id, dataset_id, table_id, creation_time, last_modified_time, row_count, size_bytes, type. Timestamps are in Unix milliseconds. – Dodecaphone Aug 30 '18 at 07:27
-
its amazing that I have to rely on this answer, and searching for Google for BigQuery table size calculation returns this page and no official docs from GCP on this topic – cryanbhu Nov 06 '20 at 07:52
15
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 thecreation_timefield 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 thetabledata.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.
-
I can able to get the Total Bytes using Tables.Get method. Currently i am having 4 tables (1 Large table, 3 smaller tables). When i am getting total bytes for larger table it returns 18200091100 (16.95 GB) i did not calculate smaller tables as of now, but Google big query billing says '4.035 GB' of BigQuery storage. Why i am getting those differences? – selva kumar Jul 07 '15 at 12:22
-
Storage pricing is prorated per MB, per second. When you check the estimated costs reported for current month, the number is prorated per second (eg days elapsed so far of this month). You should check the numbers for last month, there you have full month data. Anyway at the end of the month you will see numbers closed to your gibibyte. – Pentium10 Jul 07 '15 at 12:56
-
I have created my table on 24th June 2015 (total bytes:18200091100 [16.95 GB] ), but i am getting June month Google invoice as '4.035 GB' of Big Query storage, But i don't know why those much of difference (~12GB)? Even July 1st week invoice also shows '4.035 GB' of Big Query storage. – selva kumar Jul 07 '15 at 14:07
-
1@selvakumar again, you don't pay for GB, you pay for GB/Month. If you have 16.95GB, but for a quarter of the month, then you had an average of 4.035 GB in your dataset through the month – Patrice Jul 07 '15 at 14:44
-
@Patrice i got it that point but having one doubt, if i am creating one table (~2GB) after two days i dropped the table then Google big query charge for 2 days or 1 Week? – selva kumar Jul 08 '15 at 04:58
-
1
8
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
user1692732
- 81
- 2
3
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.
- For Single Table
bq show dataset_name.table_name
- For All Tables
bq show dataset_name.*
Aniket Ghole
- 192
- 1
- 11
-
1The second command does not work if you have external tables, just FYI: "BigQuery error in show operation: External tables cannot be queried through prefix." – smoore4 Jul 25 '22 at 19:08
0
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.
pjames
- 192
- 1
- 4
- 13