Este equipo de trabajo ha estado largas horas buscando para darle resolución a tus búsquedas, te brindamos la solución así que nuestro deseo es que sea de gran apoyo.
Solución:
O desde la GUI, puede usar la tabla interna de metadatos __TABLES__, por ejemplo, esto le dará el tamaño en GB:
select
sum(size_bytes)/pow(10,9) as size
from
.__TABLES__
where
table_id = ''
Hay un par de formas de hacer esto, pero tenga en cuenta que el tamaño de la tabla en la propiedad de bytes no está disponible para las tablas que reciben inserciones de transmisión de forma activa.
A. Uso de la herramienta de línea de comandos BQ y la biblioteca de Linux JQ para analizar JSON.
bq --format=json show publicdata:samples.gsod | jq '.numBytes | tonumber'
Este outpus:
17290009238
B. Usando la API REST para hacer tablas: obtener una llamada
GET https://www.googleapis.com/bigquery/v2/projects/projectId/datasets/datasetId/tables/tableId
esto devuelve un JSON completo, que puede analizar y obtener el 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. Hay metatablas llamadas __TABLES__
y __TABLES_SUMMARY__
Puede ejecutar una consulta como:
SELECT size_bytes FROM .__TABLES__ WHERE table_id='mytablename'
los __TABLES__
parte de esa consulta puede parecer desconocida. __TABLES_SUMMARY__
es una metatabla que contiene información sobre las tablas de un conjunto de datos. Puede utilizar esta metatabla usted mismo. Por ejemplo, la consulta SELECT * FROM publicdata:samples.__TABLES_SUMMARY__
devolverá metadatos sobre las tablas en el publicdata:samples
conjunto de datos. Tu tambien puedes hacer SELECT * FROM publicdata:samples.__TABLES__
Campos disponibles:
Los campos de la __TABLES_SUMMARY__
meta-tabla (que están todas disponibles en el TABLE_QUERY
consulta) incluyen:
table_id
: nombre de la mesa.creation_time
: tiempo, en milisegundos desde el 1/1/1970 UTC, en que se creó la tabla. Este es el mismo que elcreation_time
campo sobre la mesa.type
: si es una vista (2) o una tabla normal (1).
Los siguientes campos son no disponible en TABLE_QUERY()
ya que son miembros de __TABLES__
pero no __TABLES_SUMMARY__
. Se guardan aquí por interés histórico y para documentar parcialmente la __TABLES__
metatabla:
last_modified_time
: tiempo, en milisegundos desde el 1/1/1970 UTC, en que se actualizó la tabla (ya sea metadatos o contenido de la tabla). Tenga en cuenta que si utiliza eltabledata.insertAll()
para transmitir registros a su mesa, esto puede estar desactualizado por algunos minutos.row_count
: número de filas de la tabla.size_bytes
: tamaño total en bytes de la tabla.
Puede hacer esto con la herramienta de línea de comandos
bq show ds_name.table_name
Mostrará información sobre la tabla, incluido el “Total de bytes”. Referencia aquí https://cloud.google.com/bigquery/bq-command-line-tool
Calificaciones y comentarios
Si posees algún conflicto o forma de aumentar nuestro sección te recordamos realizar un informe y con placer lo analizaremos.