Cambia manualmente esquemas de tablas

En este documento, se describe cómo cambiar de forma manual la definición de esquema para tablas de BigQuery existentes. Muchos cambios de esquema no son compatibles de manera nativa en BigQuery y requieren soluciones alternativas manuales. Las modificaciones de esquema no compatibles incluyen las siguientes opciones:

• Cambiar el nombre de una columna
• Cambiar el tipo de datos de una columna
• Cambiar el modo de una columna (además de disminuir la rigurosidad de las columnas REQUIRED a NULLABLE)
• Borrar una columna

Para obtener más información sobre los cambios de esquema compatibles en BigQuery, consulta Modifica esquemas de tablas.

Precaución: Las opciones que se describen en esta página implican reemplazar la tabla o cargar los datos en una tabla nueva. Para las tablas particionadas por tiempo de transferencia, estas operaciones destruyen la información de partición original. Después de la operación, todos los datos de la tabla residen en una sola partición.

Cambia el nombre de una columna

La consola de Google Cloud, la herramienta de línea de comandos bq o la API no admiten el cambio de nombre de una columna. Si intentas actualizar un esquema de tabla mediante una columna a la que se le cambió el nombre, se mostrará el siguiente error: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

Hay dos maneras de renombrar una columna de forma manual:

• Usa una consulta de SQL: elige esta opción si te preocupa más la simplicidad y la facilidad de uso que los costos.
• Vuelve a crear la tabla: elige esta opción si te preocupan más los costos que la simplicidad y la facilidad de uso.

Opción 1: Usa consultas

Para cambiar el nombre de una columna con una consulta de SQL, selecciona todas las columnas en la tabla y proporciona un alias a la columna que necesitas renombrar. Puedes usar el resultado de consulta para reemplazar la tabla existente o crear una tabla de destino nueva. Cuando le proporcionas un nombre nuevo a una columna, este debe cumplir con las reglas de BigQuery para los nombres de columnas.

Ventajas

• El uso de una consulta para escribir los datos en una tabla de destino nueva conserva tus datos originales.
• Si usas el trabajo de consulta a fin de reemplazar la tabla original, generas costos de almacenamiento para una tabla en vez de dos, pero pierdes los datos originales.

Desventajas

• Si cambias el nombre de una columna mediante una consulta, deberás analizar la tabla completa. Los cargos de la consulta pueden ser importantes si la tabla es muy grande.
• Si escribes los resultados de la consulta en una tabla de destino nueva, generas costos de almacenamiento para la tabla anterior y la nueva (excepto si borras la anterior).
• Las columnas requeridas a las que se les cambió el nombre se volvieron anulables.

Ejemplo de alias de columna

En el siguiente ejemplo, se muestra una consulta de SQL estándar en la que se seleccionan todos los datos de mytable, excepto 2 columnas que requieren cambio de nombre. Se usa un alias a fin de generar nombres nuevos para esas 2 columnas. column_one pasa a llamarse newcolumn_one y column_two pasa a llamarse newcolumn_two. El resultado de la consulta se usa para reemplazar la tabla existente.

Console

1. En la consola de Google Cloud, selecciona el Editor de consultas.

2. En el Editor de consultas, ingresa la siguiente consulta para seleccionar todos los datos en mydataset.mytable, excepto las dos columnas a las que se les debe cambiar el nombre. mydataset.mytable está en tu proyecto predeterminado. La consulta usa un alias para cambiar el nombre de column_one a newcolumn_one y el nombre de column_two a newcolumn_two.

   SELECT
    * EXCEPT(column_one, column_two),
    column_one AS newcolumn_one, column_two AS newcolumn_two
   FROM
    mydataset.mytable
   

3. Haz clic en Más y selecciona Configuración de consulta.

4. En la sección Destino sigue estos pasos:

   1. Seleccione Establecer una tabla de destino para los resultados de la consulta (Set a destination table for query results).

   2. En Conjunto de datos, elige myproject.mydataset.

   3. En ID de tabla, ingresa mytable.

   4. En Preferencia de escritura para la tabla de destino, selecciona Reemplazar tabla. Con esta opción, se reemplaza mytable con los resultados de la consulta.

5. Haz clic en Guardar para actualizar la configuración y, luego, en el Editor de consultas, haz clic en Ejecutar. Cuando se complete el trabajo de consulta, las columnas en mytable tendrán nombres nuevos.

bq

Ingresa el comando bq query que se muestra a continuación para seleccionar todos los datos de mydataset.mytable, excepto las dos columnas a las que se les debe cambiar el nombre. mydataset.mytable está en tu proyecto predeterminado. La consulta usa un alias para cambiar el nombre de column_one por newcolumn_one y el nombre de column_two por newcolumn_two.

Escribe los resultados de la consulta en mydataset.mytable con la marca --destination_table y especifica la marca --replace para reemplazar los datos de mytable. Especifica la marca use_legacy_sql=false para usar la sintaxis de SQL estándar.

Opcional: Proporciona la marca --location y configura el valor en tu ubicación.

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  * EXCEPT(column_one,
    column_two),
  column_one AS newcolumn_one,
  column_two AS newcolumn_two
FROM
  mydataset.mytable'

API

Para cambiar el nombre de column_one a newcolumn_one y el nombre de column_two a newcolumn_two, realiza una llamada al método jobs.insert y configura un trabajo de query. Especifica tu ubicación en la propiedad location en la sección jobReference (opcional).

La consulta de SQL que se usa en el trabajo de consulta es la siguiente: SELECT * EXCEPT(column_one, column_two), column_one AS newcolumn_one, column_two AS newcolumn_two FROM mydataset.mytable. Esta consulta selecciona todos los datos de mytable, excepto las 2 columnas a las que se les debe cambiar el nombre. Se usa un alias a fin de generar nombres nuevos para esas 2 columnas.

Para reemplazar los datos de mytable por los resultados de la consulta, incluye mydataset.mytable en la propiedad configuration.query.destinationTable y especifica WRITE_TRUNCATE en la propiedad configuration.query.writeDisposition. Para especificar una tabla de destino nueva, ingresa el nombre de la tabla en la propiedad configuration.query.destinationTable.

Opción 2: Exporta y carga datos en una tabla nueva

También puedes cambiarle el nombre a una columna si exportas los datos de tu tabla a Cloud Storage y, luego, cargas los datos en una tabla nueva con una definición de esquema que contenga el nombre de columna correcto. También puedes usar el trabajo de carga para reemplazar la tabla existente.

Ventajas

• No generas cargos por el trabajo de exportación o de carga. Por el momento, los trabajos de carga y exportación de BigQuery son gratuitos.
• Si usas el trabajo de carga a fin de reemplazar la tabla original, generas costos de almacenamiento para una tabla en vez de dos, pero pierdes los datos originales.

Desventajas

• Si cargas los datos en una tabla nueva, generas costos de almacenamiento para la tabla original y la tabla nueva (excepto si borras la anterior).
• Generas costos por almacenar los datos exportados en Cloud Storage.

Cambia el tipo de datos de una columna

No se admite el cambio de tipo de datos de una columna en la consola de Google Cloud, la herramienta de línea de comandos de bq ni la API. Si intentas actualizar una tabla mediante la aplicación de un esquema que especifique un tipo de datos nuevo para una columna, se mostrará el siguiente error: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

Hay dos maneras de cambiar el tipo de datos de una columna de forma manual:

• Usa una consulta de SQL: elige esta opción si te preocupa más la simplicidad y la facilidad de uso que los costos.
• Vuelve a crear la tabla: elige esta opción si te preocupan más los costos que la simplicidad y la facilidad de uso.

Opción 1: Usa consultas

Usa una consulta de SQL para seleccionar todos los datos de la tabla y transmitir la columna relevante como un tipo de datos diferente. Puedes usar los resultados de la consulta para reemplazar los datos de la tabla o crear una tabla de destino nueva.

Ventajas

• El uso de una consulta para escribir los datos en una tabla de destino nueva conserva tus datos originales.
• Si usas el trabajo de consulta a fin de reemplazar la tabla original, generas costos de almacenamiento para una tabla en vez de dos, pero pierdes los datos originales.

Desventajas

• Si cambias el tipo de datos de una columna mediante una consulta, deberás analizar la tabla completa. Los cargos de la consulta pueden ser importantes si la tabla es muy grande.
• Si escribes los resultados de la consulta en una tabla de destino nueva, generas costos de almacenamiento para la tabla anterior y la nueva (excepto si borras la anterior).

Ejemplo CAST

En el siguiente ejemplo, se muestra una consulta de SQL estándar en la que se seleccionan todos los datos de column_two y column_three en mydataset.mytable y se convierte column_one de DATE a STRING. El resultado de la consulta se usa para reemplazar los datos de la tabla existente. En la tabla cuyos datos se reemplazaron, se almacena column_one como un tipo de datos STRING.

Cuando usas CAST, la consulta puede fallar si BigQuery no puede realizar la transmisión. Para obtener más detalles sobre las reglas de conversión en SQL estándar, consulta la sección sobre la conversión en la documentación de referencia de operadores y funciones.

Console

1. En la consola de Google Cloud, haz clic en Redactar consulta nueva.

2. En el Editor de consultas, ingresa la siguiente consulta a fin de seleccionar todos los datos de column_two y column_three en mydataset.mytable, y para convertir column_one de DATE a STRING. La consulta usa un alias para convertir column_one de modo que conserve el mismo nombre. mydataset.mytable está en tu proyecto predeterminado.

   SELECT
    column_two, column_three, CAST(column_one AS STRING) AS column_one
   FROM
    mydataset.mytable
   

3. Haz clic en Más y selecciona Configuración de consulta.

4. En la sección Destino, marca Establecer una tabla de destino para los resultados de la consulta.

5. En los estos campos, haz lo siguiente:

   1. En Nombre del proyecto, deja el valor establecido en tu proyecto predeterminado. Este es el proyecto que contiene mydataset.mytable.

   2. En Nombre del conjunto de datos, elige mydataset.

   3. En el campo Nombre de tabla, ingresa mytable.

   4. Haz clic en Aceptar.

6. En la sección Preferencia de escritura de la tabla de destino, para Preferencia de escritura, elige Reemplazar tabla. Con esta opción, se reemplaza mytable con los resultados de la consulta.

7. En Ubicación de procesamiento, haz clic en Selección automática y elige la ubicación de tus datos (opcional).

8. Haz clic en Guardar para actualizar la configuración y, luego, en el Editor de consultas, haz clic en Ejecutar. Cuando se completa el trabajo de consulta, el tipo de datos de column_one es STRING.

bq

Ingresa el comando bq query que se muestra a continuación para seleccionar todos los datos de column_two y column_three en mydataset.mytable, y convertir column_one de DATE a STRING. La consulta usa un alias para convertir column_one de modo que conserve el mismo nombre. mydataset.mytable está en tu proyecto predeterminado.

Los resultados de la consulta se escriben en mydataset.mytable con la marca --destination_table y la marca --replace se usa para reemplazar los datos de mytable. Especifica la marca use_legacy_sql=false para usar la sintaxis de SQL estándar.

Opcional: Proporciona la marca --location y configura el valor en tu ubicación.

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  column_two,
  column_three,
  CAST(column_one AS STRING) AS column_one
FROM
  mydataset.mytable'

API

Para seleccionar todos los datos de column_two y column_three en mydataset.mytable, además de convertir column_one de DATE a STRING, realiza una llamada al método jobs.insert y configura un trabajo de query. Especifica tu ubicación en la propiedad location, en la sección jobReference (opcional).

La consulta de SQL que se usa en el trabajo de consulta es la siguiente: SELECT column_two, column_three, CAST(column_one AS STRING) AS column_one FROM mydataset.mytable. La consulta usa un alias para convertir column_one de modo que conserve el mismo nombre.

Para reemplazar los datos de mytable por los resultados de la consulta, incluye mydataset.mytable en la propiedad configuration.query.destinationTable y especifica WRITE_TRUNCATE en la propiedad configuration.query.writeDisposition.

Opción 2: Exporta y carga datos en una tabla nueva

También puedes cambiar el tipo de datos de una columna si exportas los datos de tu tabla a Cloud Storage y, luego, cargas los datos en una tabla nueva con una definición de esquema que especifique el tipo de datos de columna correcto. También puedes usar el trabajo de carga para reemplazar la tabla existente.

Ventajas

• No generas cargos por el trabajo de exportación o de carga. Por el momento, los trabajos de carga y exportación de BigQuery son gratuitos.
• Si usas el trabajo de carga a fin de reemplazar la tabla original, generas costos de almacenamiento para una tabla en vez de dos, pero pierdes los datos originales.

Desventajas

• Si cargas los datos en una tabla nueva, generas costos de almacenamiento para la tabla original y la tabla nueva (excepto si borras la anterior).
• Generas costos por almacenar los datos exportados en Cloud Storage.

Cambia el modo de una columna

Por el momento, la única modificación que puedes hacer al modo de una columna es cambiar REQUIRED por NULLABLE. Cambiar el modo de una columna de REQUIRED a NULLABLE se denomina disminución de la rigurosidad del modo de una columna. Para obtener más información sobre la disminución de la rigurosidad del modo de las columnas de REQUIRED a NULLABLE, consulta la sección Disminuye la rigurosidad del modo de una columna.

Si tratas de aplicar un cambio no admitido en un modo de columna, se muestra un error como el siguiente. En este ejemplo, se intentó cambiar el modo de una columna de NULLABLE a REPEATED: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Field field has changed mode from NULLABLE to REPEATED.

Exporta y carga datos en una tabla nueva

Puedes cambiar el modo de una columna de forma manual si exportas los datos de tu tabla a Cloud Storage y, luego, cargas los datos en una tabla nueva con una definición de esquema que especifique el modo correcto para la columna. También puedes usar el trabajo de carga para reemplazar la tabla existente.

Ventajas

• No generas cargos por el trabajo de exportación o de carga. Por el momento, los trabajos de carga y exportación de BigQuery son gratuitos.
• Si usas el trabajo de carga a fin de reemplazar la tabla original, generas costos de almacenamiento para una tabla en vez de dos, pero pierdes los datos originales.

Desventajas

• Si cargas los datos en una tabla nueva, generas costos de almacenamiento para la tabla original y la tabla nueva (excepto si borras la anterior).
• Generas costos por almacenar los datos exportados en Cloud Storage.

Borra columnas del esquema de la tabla

La consola de Google Cloud, la herramienta de línea de comandos bq o la API no admiten la eliminación de una columna del esquema de una tabla existente. Si intentas actualizar una tabla mediante la aplicación de un esquema que quite una columna, se mostrará el siguiente error: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

Hay dos maneras de borrar una columna de forma manual:

• Usa una consulta de SQL: elige esta opción si te preocupa más la simplicidad y la facilidad de uso que los costos.
• Vuelve a crear la tabla: elige esta opción si te preocupan más los costos que la simplicidad y la facilidad de uso.

Opción 1: Usa consultas

Existen dos instrucciones de SQL que puedes usar para borrar una columna:

• SELECT * EXCEPT
• ALTER TABLE DROP COLUMN

En la sección siguiente, se muestra cómo usar una consulta SELECT * EXCEPT que excluye la columna (o columnas) que deseas quitar y usar el resultado de la consulta para reemplazar los datos de la tabla o crear una tabla de destino nueva.

Para usar la declaración ALTER TABLE DROP COLUMN, consulta los ejemplos en la página Lenguaje de definición de datos.

Ventajas

• El uso de una consulta para escribir los datos en una tabla de destino nueva conserva tus datos originales.
• Si usas el trabajo de consulta a fin de reemplazar la tabla original, generas costos de almacenamiento para una tabla en vez de dos, pero pierdes los datos originales.

Desventajas

• Borrar una columna con una consulta requiere que escanees los datos en todas las columnas excepto en la que quitas. Los cargos de la consulta pueden ser importantes si la tabla es muy grande.
• Si escribes los resultados de la consulta en una tabla de destino nueva, generas costos de almacenamiento para la tabla anterior y la nueva (excepto si borras la anterior).

Ejemplo SELECT * EXCEPT

En el siguiente ejemplo, se muestra una consulta de SQL estándar en la que se seleccionan todos los datos de mydataset.mytable, excepto column_two. El resultado de la consulta se usa para reemplazar la tabla existente.

Console

1. En la consola de Google Cloud, haz clic en Redactar consulta nueva.

2. En el Editor de consultas, ingresa la siguiente consulta para seleccionar todos los datos de mydataset.mytable, excepto column_two. mydataset.mytable está en tu proyecto predeterminado.

   SELECT
    * EXCEPT(column_two)
   FROM
    mydataset.mytable
   

3. Haz clic en Más y selecciona Configuración de consulta.

4. En la sección Destino, marca Establecer una tabla de destino para los resultados de la consulta.

5. En los estos campos, haz lo siguiente:

   1. En Nombre del proyecto, deja el valor establecido en tu proyecto predeterminado. Este es el proyecto que contiene mydataset.mytable.

   2. En Nombre del conjunto de datos, elige mydataset.

   3. En el campo Nombre de tabla, ingresa mytable.

   4. Haz clic en Aceptar.

6. En la sección Preferencia de escritura de la tabla de destino, para Preferencia de escritura, elige Reemplazar tabla. Con esta opción, se reemplaza mytable con los resultados de la consulta.

7. En Ubicación de procesamiento, haz clic en Selección automática y elige la ubicación de tus datos (opcional).

8. Haz clic en Guardar para actualizar la configuración y, luego, en el Editor de consultas, haz clic en Ejecutar. Cuando se completa el trabajo de consulta, la tabla incluye todas las columnas, excepto column_two.

bq

Ingresa el siguiente comando bq query para seleccionar todos los datos de mydataset.mytable, excepto column_two. mydataset.mytable está en tu proyecto predeterminado. Los resultados de la consulta se escriben en mydataset.mytable con la marca --destination_table y la marca --replace se usa para reemplazar los datos de mytable. Especifica la marca use_legacy_sql=false para usar la sintaxis de SQL estándar.

Opcional: Proporciona la marca --location y configura el valor en tu ubicación.

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  * EXCEPT(column_two)
FROM
  mydataset.mytable'

API

Para seleccionar todos los datos de mydataset.mytable, excepto column_two, realiza una llamada al método jobs.insert y configura un trabajo de query. Especifica tu ubicación en la propiedad location en la sección jobReference (opcional).

La consulta de SQL que se usa en el trabajo de consulta es la siguiente: SELECT * EXCEPT(column_two) FROM mydataset.mytable.

Para reemplazar los datos de mytable por los resultados de la consulta, incluye mydataset.mytable en la propiedad configuration.query.destinationTable y especifica WRITE_TRUNCATE en la propiedad configuration.query.writeDisposition.

Opción 2: Exporta y carga datos en una tabla nueva

También puedes quitar una columna si exportas los datos de tu tabla a Cloud Storage, borras los datos correspondientes a la columna (o columnas) que deseas quitar y, luego, cargas los datos en una tabla nueva con una definición de esquema que no incluya las columnas que se quitaron. También puedes usar el trabajo de carga para reemplazar la tabla existente.

Ventajas

• No generas cargos por el trabajo de exportación o de carga. Por el momento, los trabajos de carga y exportación de BigQuery son gratuitos.
• Si usas el trabajo de carga a fin de reemplazar la tabla original, generas costos de almacenamiento para una tabla en vez de dos, pero pierdes los datos originales.

Desventajas

• Si cargas los datos en una tabla nueva, generas costos de almacenamiento para la tabla original y la tabla nueva (excepto si borras la anterior).
• Generas costos por almacenar los datos exportados en Cloud Storage.

Seguridad de las tablas

Para controlar el acceso a las tablas en BigQuery, consulta Introducción a los controles de acceso a tablas.