Crear un archivo de definición de tabla para una fuente de datos externa

En esta página se describe cómo crear un archivo de definición de tabla para una fuente de datos externa. Las fuentes de datos externas se pueden consultar directamente, aunque los datos no estén almacenados en BigQuery.

Un archivo de definición de tabla contiene la definición del esquema de una tabla externa y metadatos, como el formato de datos de la tabla y las propiedades relacionadas. Cuando creas un archivo de definición de tabla, puedes usar la detección automática de esquemas para definir el esquema de una fuente de datos externa. Puedes proporcionar el esquema insertado o en un archivo JSON que contenga la definición del esquema.

Los archivos de definición de tabla se usan con la herramienta de línea de comandos bq. Las propiedades de un archivo de definición de tabla también se aplican a la creación de un ExternalDataConfiguration cuando se usa la API REST. No se usan archivos de definición de tabla cuando se crea una tabla externa con la consola Google Cloud .

Puede crear archivos de definición de tabla para describir una tabla externa permanente o temporal de las siguientes fuentes de datos externas:

  • Cloud Storage

    • Valores separados por comas (CSV)
    • JSON delimitado por líneas nuevas
    • Archivos Avro
    • Archivos de exportación de Datastore
    • Archivos ORC
    • Archivos Parquet
    • Archivos de exportación de Firestore

  • Google Drive

    • Valores separados por comas (CSV)
    • JSON delimitado por líneas nuevas
    • Archivos Avro
    • Hojas de cálculo de Google

  • Bigtable

Antes de empezar

Para crear un archivo de definición de tabla, necesita el URI de su fuente de datos:

  • En el caso de las fuentes de datos de Drive, necesitas el URI de Drive.
  • En el caso de una fuente de datos de Cloud Storage, necesitas el URI de Cloud Storage.
  • En el caso de las fuentes de datos de Bigtable, necesitas el URI de Bigtable.

Crear un archivo de definición para archivos CSV, JSON u Hojas de cálculo de Google

Utiliza uno de los siguientes métodos para crear un archivo de definición de tabla para archivos CSV, JSON o de Hojas de cálculo de Google en Cloud Storage o Drive:

Usar la marca autodetect

Si especifica un archivo CSV, JSON o de Hojas de cálculo de Google sin incluir una descripción de esquema insertada o un archivo de esquema, puede usar la marca --autodetect para definir la opción "autodetect" como true en el archivo de definición de tabla. Cuando la detección automática está habilitada, BigQuery hace todo lo posible para inferir automáticamente el esquema. Para obtener más información, consulta Detección automática de esquemas de fuentes de datos externas.

Usar la detección automática con una fuente de datos de Cloud Storage

Crea un archivo de definición de tabla para una fuente de datos de Cloud Storage:

  1. Usa el comando bq mkdef con la marca --autodetect para crear un archivo de definición de tabla. El comando mkdef genera un archivo de definición de tabla en formato JSON. En el siguiente ejemplo se crea una definición de tabla y se escribe el resultado en un archivo: /tmp/file_name.

    bq mkdef \
  --autodetect \
  --source_format=SOURCE_FORMAT \
  "URI" > /tmp/FILE_NAME

    Haz los cambios siguientes:

    • SOURCE_FORMAT: el formato de tu archivo
    • FILE_NAME: el nombre del archivo de definición de tabla

    • URI: el URI de Cloud Storage

      Por ejemplo, gs://mybucket/myfile.

  2. (Opcional) Abre el archivo de definición de la tabla en un editor de texto. Por ejemplo, el comando nano /tmp/file_name abre el archivo en nano. El archivo debería tener un aspecto similar al siguiente en el caso de una fuente de datos externa CSV. La notificación "autodetect" se ha configurado para las true.

    {
"autodetect": true,
"csvOptions": {
  "allowJaggedRows": false,
  "allowQuotedNewlines": false,
  "encoding": "UTF-8",
  "fieldDelimiter": ",",
  "quote": "\"",
  "skipLeadingRows": 0
},
"sourceFormat": "CSV",
"sourceUris": [
  "URI"
]
}

  3. (Opcional) Edita manualmente el archivo de definición de la tabla para modificar, añadir o eliminar ajustes generales, como maxBadRecords y ignoreUnknownValues. No hay ajustes de configuración específicos para archivos de origen JSON, pero sí hay ajustes que se aplican a los archivos CSV y Hojas de cálculo de Google. Para obtener más información, consulta ExternalDataConfiguration en la referencia de la API.

Usar la detección automática con una fuente de datos de Drive

Para crear un archivo de definición de tabla de una fuente de datos de Drive, siga estos pasos:

  1. Usa el comando bq mkdef con la marca --autodetect para crear una definición de tabla. El comando mkdef genera un archivo de definición de tabla en formato JSON. En el siguiente ejemplo se crea una definición de tabla y se escribe el resultado en un archivo: /tmp/file_name.

    bq mkdef \
   --autodetect \
   --source_format=SOURCE_FORMAT \
   "URI" > /tmp/FILE_NAME

    Haz los cambios siguientes:

    • SOURCE_FORMAT: el formato de tu archivo
    • FILE_NAME: el nombre del archivo de definición de tabla

    • URI: el URI de Drive

      Por ejemplo, https://drive.google.com/open?id=123ABCD123AbcD123Abcd.

  2. Abre el archivo de definición de la tabla en un editor de texto. Por ejemplo, el comando nano /tmp/file_name abre el archivo en nano. El archivo debe tener el siguiente aspecto en el caso de una fuente de datos externa de Hojas de cálculo de Google. Aviso "autodetect" se ha configurado para las true.

    {
"autodetect": true,
"sourceFormat": "GOOGLE_SHEETS",
"sourceUris": [
  "URI"
]
}

  3. (Opcional) Edita manualmente el archivo de definición de la tabla para modificar, añadir o eliminar ajustes generales, como maxBadRecords y ignoreUnknownValues. No hay ajustes de configuración específicos para archivos de origen JSON, pero sí hay ajustes que se aplican a los archivos CSV y Hojas de cálculo de Google. Para obtener más información, consulta ExternalDataConfiguration en la referencia de la API.

  4. Para especificar una hoja o un intervalo de celdas concretos en un archivo de Hojas de cálculo de Google, añade la propiedad range al objeto GoogleSheetsOptions en el archivo de definición de la tabla. Para consultar una hoja concreta, especifica su nombre. Para consultar un intervalo de celdas, especifica el intervalo con el siguiente formato: sheet_name!top_left_cell_id:bottom_right_cell_id, por ejemplo, "Sheet1!A1:B20". Si no se especifica el parámetro range, se usa la primera hoja del archivo.

Usar un esquema insertado

Si no quiere usar la detección automática de esquemas, puede crear un archivo de definición de tabla proporcionando una definición de esquema insertada. Para proporcionar una definición de esquema insertada, enumera los campos y los tipos de datos en la línea de comandos con el siguiente formato: FIELD:DATA_TYPE,FIELD:DATA_TYPE.

Usar un esquema insertado con una fuente de datos de Cloud Storage o Drive

Para crear una definición de tabla para una fuente de datos de Cloud Storage o Drive mediante una definición de esquema insertada, sigue estos pasos:

  1. Usa el comando bq mkdef con la marca --noautodetect para crear una definición de tabla. El comando mkdef genera un archivo de definición de tabla en formato JSON. En el siguiente ejemplo se crea una definición de tabla y se escribe el resultado en un archivo: /tmp/file_name.

    bq mkdef \
  --noautodetect \
  --source_format=SOURCE_FORMAT \
  "URI" \
  FIELD:DATA_TYPE,FIELD:DATA_TYPE > /tmp/FILE_NAME

    Sustituye lo siguiente:

    • SOURCE_FORMAT: el formato del archivo de origen

    • URI: el URI de Cloud Storage o el URI de Drive

      Por ejemplo, gs://mybucket/myfile para Cloud Storage o https://drive.google.com/open?id=123ABCD123AbcD123Abcd para Drive.

    • FIELD:DATA_TYPE,FIELD:DATA_TYPE: la definición del esquema

      Por ejemplo, Name:STRING,Address:STRING, ....

    • FILE_NAME: el nombre del archivo de definición de tabla

  2. (Opcional) Abre el archivo de definición de la tabla en un editor de texto. Por ejemplo, el comando nano /tmp/file_name abre el archivo en nano. El archivo debería tener este aspecto: Observa que "autodetect" no está habilitado y que la información del esquema se escribe en el archivo de definición de la tabla.

    {
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "NEWLINE_DELIMITED_JSON",
"sourceUris": [
  "URI"
]
}

  3. (Opcional) Edita manualmente el archivo de definición de la tabla para modificar, añadir o eliminar ajustes generales, como maxBadRecords y ignoreUnknownValues. No hay ajustes de configuración específicos para archivos de origen JSON, pero sí hay ajustes que se aplican a los archivos CSV y Hojas de cálculo de Google. Para obtener más información, consulta ExternalDataConfiguration en la referencia de la API.

Usar un archivo de esquema JSON

Si no quieres usar la detección automática ni proporcionar una definición de esquema insertada, puedes crear un archivo de esquema JSON y hacer referencia a él al crear el archivo de definición de la tabla. Crea el archivo de esquema JSON manualmente en tu máquina local. No se puede hacer referencia a un archivo de esquema JSON almacenado en Cloud Storage o en Drive.

Usar un archivo de esquema con una fuente de datos de Cloud Storage

Crea una definición de tabla para una fuente de datos de Cloud Storage con un archivo de esquema JSON:

  1. Usa el comando bq mkdef con la marca --noautodetect para crear una definición de tabla. El comando mkdef genera un archivo de definición de tabla en formato JSON. En el siguiente ejemplo se crea una definición de tabla y se escribe el resultado en un archivo: /tmp/file_name.

    bq mkdef \
   --noautodetect \
   --source_format=SOURCE_FORMAT \
   "URI" \
  PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME

    Haz los cambios siguientes:

    • SOURCE_FORMAT: el formato de tu archivo
    • FILE_NAME: el nombre del archivo de definición de tabla

    • URI: el URI de Cloud Storage

      Por ejemplo, gs://mybucket/myfile.

    • PATH_TO_SCHEMA_FILE: la ubicación del archivo de esquema JSON en tu máquina local

  2. (Opcional) Abre el archivo de definición de la tabla en un editor de texto. Por ejemplo, el comando nano /tmp/file_name abre el archivo en
    nano. El archivo debería tener este aspecto: Observa que "autodetect" no está habilitado y que la información del esquema se escribe en el archivo de definición de la tabla.

    {
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "NEWLINE_DELIMITED_JSON",
"sourceUris": [
  "URI"
]
}

  3. (Opcional) Edita manualmente el archivo de definición de la tabla para modificar, añadir o eliminar ajustes generales, como maxBadRecords y ignoreUnknownValues. No hay ajustes de configuración específicos para archivos de origen JSON, pero sí hay ajustes que se aplican a los archivos CSV y Hojas de cálculo de Google. Para obtener más información, consulta ExternalDataConfiguration en la referencia de la API.

Usar un archivo de esquema con una fuente de datos de Drive

Crea una definición de tabla para una fuente de datos de Drive con un archivo de esquema JSON:

  1. Usa el comando bq mkdef con la marca --noautodetect para crear una definición de tabla. El comando mkdef genera un archivo de definición de tabla en formato JSON. En el siguiente ejemplo se crea una definición de tabla y se escribe el resultado en un archivo: /tmp/file_name.

    bq mkdef \
   --noautodetect \
   --source_format=source_format \
   "URI" \
   PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME

    Haz los cambios siguientes:

    • SOURCE_FORMAT: el formato del archivo de origen

    • URI: el URI de Drive

      Por ejemplo, https://drive.google.com/open?id=123ABCD123AbcD123Abcd.

    • PATH_TO_SCHEMA_FILE: la ubicación del archivo de esquema JSON en tu máquina local

    • FILE_NAME: el nombre del archivo de definición de tabla

  2. Abre el archivo de definición de la tabla en un editor de texto. Por ejemplo, el comando nano /tmp/file_name abre el archivo en nano. El archivo debería tener este aspecto. La opción "autodetect" no está habilitada y la información del esquema se escribe en el archivo de definición de la tabla.

    {
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "GOOGLE_SHEETS",
"sourceUris": [
  "URI"
]
}

  3. (Opcional) Edita manualmente el archivo de definición de la tabla para modificar, añadir o eliminar ajustes generales, como maxBadRecords y ignoreUnknownValues. No hay ajustes de configuración específicos para archivos de origen JSON, pero sí hay ajustes que se aplican a los archivos CSV y Hojas de cálculo de Google. Para obtener más información, consulta ExternalDataConfiguration en la referencia de la API.

  4. Para especificar una hoja o un intervalo de celdas concretos en un archivo de Hojas de cálculo de Google, añade la propiedad range al objeto GoogleSheetsOptions en el archivo de definición de la tabla. Para consultar una hoja concreta, especifica su nombre. Para consultar un intervalo de celdas, especifica el intervalo con el siguiente formato: sheet_name!top_left_cell_id:bottom_right_cell_id, por ejemplo, "Sheet1!A1:B20". Si no se especifica el parámetro range, se usa la primera hoja del archivo.

Crear un archivo de definición para formatos autoexplicativos

Avro, Parquet y ORC son formatos autoexplicativos. Los archivos de datos de estos formatos contienen su propia información de esquema. Si usas uno de estos formatos como fuente de datos externa, BigQuery recuperará automáticamente el esquema mediante los datos de origen. Cuando creas una definición de tabla, no es necesario que uses la detección automática de esquemas ni que proporciones una definición de esquema insertada o un archivo de esquema.

Puedes crear un archivo de definición de tabla para los datos de Avro, Parquet u ORC almacenados en Cloud Storage o Drive:

  1. Usa el comando bq mkdef para crear una definición de tabla.

    bq mkdef \
    --source_format=FORMAT \
    "URI" > FILE_NAME

    Haz los cambios siguientes:

    • FORMAT: el formato de origen

    • URI: el URI de Cloud Storage o el URI de Drive

      Por ejemplo, gs://mybucket/myfile para Cloud Storage o https://drive.google.com/open?id=123ABCD123AbcD123Abcd para Drive.

    • FILE_NAME: el nombre del archivo de definición de tabla

  2. Opcional: Abre el archivo de definición de la tabla en un editor de texto. El archivo tendrá un aspecto similar al siguiente:

    {
   "sourceFormat": "AVRO",
   "sourceUris": [
   "URI"
    ]
}

  3. Opcional: Edita manualmente el archivo de definición de la tabla para modificar, añadir o eliminar ajustes generales, como maxBadRecords y ignoreUnknownValues. Para obtener más información, consulta ExternalDataConfiguration en la referencia de la API.

Crear un archivo de definición para datos particionados por Hive

Usa el comando bq mkdef con las marcas hive_partitioning_mode y hive_partitioning_source_uri_prefix para crear un archivo de definición de datos particionados por Hive que estén almacenados en Cloud Storage, Amazon Simple Storage Service (Amazon S3) o Azure Blob Storage.

Crear un archivo de definición para Datastore y Firestore

Si usa una exportación de Datastore o Firestore como fuente de datos externa, BigQuery recupera automáticamente el esquema mediante los datos de origen autodescriptivos. Cuando creas una definición de tabla, no es necesario que proporciones una definición de esquema insertada ni un archivo de esquema.

Puedes crear un archivo de definición de tabla para los datos de exportación de Datastore y Firestore almacenados en Cloud Storage:

  1. Usa el comando bq mkdef para crear una definición de tabla. No es necesario usar la marca --noautodetect con los archivos de copia de seguridad de Datastore o Firestore. La detección automática de esquemas está inhabilitada para estos tipos de archivo. El comando mkdef genera un archivo de definición de tabla en formato JSON. En el siguiente ejemplo se crea una definición de tabla y se escribe el resultado en un archivo: /tmp/file_name.

    bq mkdef \
--source_format=DATASTORE_BACKUP \
"URI" > /tmp/FILE_NAME

    Haz los cambios siguientes:

    El formato de origen DATASTORE_BACKUP se usa tanto en Datastore como en Firestore.

  2. (Opcional) Abre el archivo de definición de la tabla en un editor de texto. Por ejemplo, el comando nano /tmp/file_name abre el archivo en nano. El archivo debería tener este aspecto: No es necesario usar el ajuste "autodetect".

    {
"sourceFormat": "DATASTORE_BACKUP",
"sourceUris": [
  "gs://URI"
]
}

  3. (Opcional) Edita manualmente el archivo de definición de la tabla para modificar, añadir o eliminar ajustes como maxBadRecords y ignoreUnknownValues. No hay ajustes de configuración específicos para los archivos de exportación de Datastore y Firestore. Para obtener más información, consulta ExternalDataConfiguration en la referencia de la API.

Crear un archivo de definición para Bigtable

Cuando creas un archivo de definición de tabla para Bigtable, generas manualmente el archivo en formato JSON. Actualmente, no se puede usar el comando mkdef para crear una definición de tabla en fuentes de datos de Bigtable. La detección automática de esquemas tampoco se admite en Bigtable. Para ver una lista de las opciones de definición de tablas de Bigtable, consulta BigtableOptions en la referencia de la API REST.

Un archivo de definición de tabla JSON para Bigtable tiene el siguiente aspecto. Con este archivo de definición de tabla, BigQuery lee los datos de una sola familia de columnas e interpreta los valores como números enteros codificados en formato binario.

{
    "sourceFormat": "BIGTABLE",
    "sourceUris": [
        "https://googleapis.com/bigtable/projects/PROJECT_ID/instances/INSTANCE_ID[/appProfiles/APP_PROFILE_ID]/tables/TABLE_NAME"
    ],
    "bigtableOptions": {
        "columnFamilies" : [
            {
                "familyId": "FAMILY_ID",
                "type": "INTEGER",
                "encoding": "BINARY"
            }
        ]
    }
}

Haz los cambios siguientes:

  • PROJECT_ID: el proyecto que contiene tu clúster de Bigtable
  • INSTANCE_ID: el ID de la instancia de Bigtable
  • APP_PROFILE_ID (opcional): el ID del perfil de aplicación que quieras usar para leer tus datos de Bigtable. La configuración del perfil de aplicación indica si la tabla externa usa Data Boost o nodos aprovisionados para el cálculo.
  • TABLE_NAME: el nombre de la tabla que estás consultando
  • FAMILY_ID: el identificador de la familia de columnas

Para obtener más información, consulta Recuperar el URI de Bigtable.

Compatibilidad con comodines en archivos de definición de tabla

Si los datos están separados en varios archivos, puede usar un asterisco (*) como comodín para seleccionar varios archivos. El uso del carácter comodín asterisco debe seguir estas reglas:

  • El asterisco puede aparecer dentro del nombre del objeto o al final del nombre del objeto.
  • No se admite el uso de varios asteriscos. Por ejemplo, la ruta gs://mybucket/fed-*/temp/*.csv no es válida.
  • No se admite el uso de un asterisco con el nombre del segmento.

Ejemplos:

  • En el siguiente ejemplo se muestra cómo seleccionar todos los archivos de todas las carpetas que empiezan por el prefijo gs://mybucket/fed-samples/fed-sample:

    gs://mybucket/fed-samples/fed-sample*

  • En el siguiente ejemplo se muestra cómo seleccionar solo los archivos con la extensión .csv de la carpeta fed-samples y de sus subcarpetas:fed-samples

    gs://mybucket/fed-samples/*.csv

  • En el siguiente ejemplo se muestra cómo seleccionar archivos con un patrón de nomenclatura de fed-sample*.csv en la carpeta llamada fed-samples. En este ejemplo no se seleccionan archivos de las subcarpetas de fed-samples.

    gs://mybucket/fed-samples/fed-sample*.csv

Cuando uses la herramienta de línea de comandos bq, es posible que tengas que usar la barra inversa para el asterisco en algunas plataformas.

Si usas un comodín de asterisco, incluye el nombre del archivo y del contenedor entre comillas. Por ejemplo, si tiene dos archivos llamados fed-sample000001.csv y fed-sample000002.csv, y quiere usar un asterisco para seleccionar ambos, el URI del contenedor sería "gs://mybucket/fed-sample*".

No se permite el carácter comodín * al crear archivos de definición de tabla para las siguientes fuentes de datos:

  • Bigtable. En el caso de los datos de Bigtable, solo se puede especificar una fuente de datos. El valor de URI debe ser una URL de HTTPS válida para una tabla de Bigtable.
  • Datastore o Firestore. Exportaciones de Datastore o Firestore almacenadas en Cloud Storage. En el caso de las copias de seguridad de Datastore, solo se puede especificar una fuente de datos. El valor del URI debe terminar en .backup_info o .export_metadata.
  • Drive. Datos almacenados en Drive.

Siguientes pasos