Saltar al contenido

Cómo obtener el tamaño de almacenamiento de BigQuery para una sola tabla

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 el creation_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 el tabledata.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.

¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)



Utiliza Nuestro Buscador

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *