Crea un archivo de definición de tablas para una fuente de datos externa

En esta página, se describe cómo crear un archivo de definición de tablas para una fuente de datos externa. Una fuente de datos externa (también conocida como fuente de datos federada) es una fuente de datos que puedes consultar directamente, aunque los datos no estén almacenados en BigQuery.

Archivos de definición de tablas

En un archivo de definición de tablas, se incluye una definición de esquema de tabla externa y metadatos, como el formato de datos de la tabla y las propiedades relacionadas. Cuando creas un archivo de definición de tablas, puedes utilizar la detección automática de esquemas a fin de definir el esquema para una fuente de datos externa, puedes proporcionar el esquema intercalado (en la línea de comandos) o puedes proporcionar un archivo JSON en el que se incluye la definición del esquema.

Los archivos de definición de tablas se usan con la CLI de BigQuery. Las propiedades de un archivo de definición de tablas también se aplican a la creación de una ExternalDataConfiguration cuando usas la API de REST. No uses archivos de definición de tablas cuando crees una tabla externa con Cloud Console o la IU web clásica de BigQuery.

Puedes crear archivos de definición de tablas para las fuentes de datos externas siguientes:

  • Cloud Storage

    • Valores separados por comas (CSV)
    • JSON delimitado por saltos de línea
    • Archivos Avro
    • Archivos de exportación de Datastore
    • Archivos ORC (Beta)
    • Archivos Parquet (Beta)
    • Archivos de exportación de Cloud Firestore
  • Google Drive

    • Valores separados por comas (CSV)
    • JSON delimitado por saltos de línea
    • Archivos Avro
    • Hojas de cálculo de Google
  • Cloud Bigtable

Antes de comenzar

A fin de crear un archivo de definición de tablas, necesitas el URI para la fuente de datos siguiente:

Tablas externas permanentes y temporales

Puedes consultar una fuente de datos externa en BigQuery; para ello, usa una tabla permanente o una temporal. Una tabla permanente es una tabla que se crea en un conjunto de datos y está vinculada a tu fuente de datos externa. Debido a que la tabla es permanente, puedes usar controles de acceso a nivel de conjunto de datos para compartir la tabla con otras personas que también tengan acceso a la fuente de datos externa subyacente y consultarla en cualquier momento.

Cuando consultas una fuente de datos externa con una tabla temporal, envías un comando que incluye una consulta y crea una tabla no permanente vinculada a la fuente de datos externa. Cuando usas una tabla temporal, no se crea una tabla en uno de tus conjuntos de datos de BigQuery. Debido a que la tabla no se almacena de forma permanente en un conjunto de datos, no puede compartirse con otras personas. La consulta de una fuente de datos externa mediante una tabla permanente es útil para las consultas únicas ad-hoc sobre datos externos, o para procesos de extracción, transformación y carga (ETL).

Puedes utilizar un archivo de definición de tablas para describir una tabla externa permanente o temporal.

Crea una definición de tablas con la detección automática de esquemas

Si especificas un archivo CSV, JSON o de Hojas de cálculo de Google sin incluir una descripción del esquema intercalado o un archivo de esquema, puedes usar la marca --autodetect para configurar la opción "autodetect" como true en el archivo de definición de tablas. Cuando la detección automática está habilitada, en BigQuery se hace un esfuerzo para inferir automáticamente el esquema. Consulta sobre la detección automática del esquema en BigQuery para obtener más información.

Puedes utilizar la detección automática del esquema cuando creas definiciones de tablas para lo siguiente:

  • Los archivos JSON almacenados en Cloud Storage o Google Drive
  • Los archivos CSV almacenados en Cloud Storage o Google Drive
  • Archivos de Hojas de cálculo de Google almacenados en Google Drive

Cloud Storage

Si deseas usar la CLI a fin de crear una definición de tablas para una fuente de datos de Cloud Storage, haz lo siguiente:

  1. Usa el comando mkdef de la herramienta de línea de comandos con la marca --autodetect para crear una definición de tablas. Con el comando mkdef, se genera un archivo de definición de tablas en formato JSON. En el ejemplo siguiente, se crea una definición de tablas y se escribe el resultado en un archivo: /tmp/file_name.

    En el comando, realiza el reemplazo siguiente:

    • source_format por tu formato de archivo: NEWLINE_DELIMITED_JSON, CSV o GOOGLE_SHEETS
    • file_name por el nombre de tu archivo de definición de tablas
    • bucket_uri por tu URI de Cloud Storage , por ejemplo, gs://mybucket/myfile
    bq mkdef \
    --autodetect \
    --source_format=source_format \
    "bucket_uri" > /tmp/file_name
    
  2. Opcional: Abre el archivo de definición de tablas en un editor de texto. Por ejemplo, con el comando nano /tmp/file_name, se abre el archivo en nano. El archivo debe verse de la manera siguiente para una fuente de datos externa CSV. Observa que "autodetect" se configuró como true.

    {
    "autodetect": true,
    "csvOptions": {
      "allowJaggedRows": false,
      "allowQuotedNewlines": false,
      "encoding": "UTF-8",
      "fieldDelimiter": ",",
      "quote": "\"",
      "skipLeadingRows": 0
    },
    "sourceFormat": "CSV",
    "sourceUris": [
      "bucket_uri"
    ]
    }
    
  3. Opcional: Edita manualmente el archivo de definición de tablas para modificar, agregar o borrar configuraciones generales como maxBadRecords y ignoreUnknownValues. No existen opciones de configuración específicas para los archivos fuente JSON, pero hay configuraciones que se aplican a los archivos CSV y de Hojas de cálculo de Google. Para obtener más información, consulta ExternalDataConfiguration en la referencia de la API.

Datos particionados externamente

Si deseas configurar un archivo de definición de tablas para los datos particionados externamente en Cloud Storage, visita la página de consulta de datos particionados externamente.

Google Drive

Si deseas usar la CLI a fin de crear una definición de tablas para una fuente de datos de Google Drive, haz lo siguiente:

  1. Usa el comando mkdef de la herramienta de línea de comandos con la marca --autodetect para crear una definición de tablas. Con el comando mkdef, se genera un archivo de definición de tablas en formato JSON. En el ejemplo siguiente, se crea una definición de tablas y se escribe el resultado en un archivo: /tmp/file_name.

    En el comando, realiza el reemplazo siguiente:

    • source_format por tu formato de archivo: NEWLINE_DELIMITED_JSON, CSV o GOOGLE_SHEETS
    • file_name por el nombre de tu archivo de definición de tablas
    • drive_uri por tu URI de Google Drive, por ejemplo, https://drive.google.com/open?id=123ABCD123AbcD123Abcd
    bq mkdef \
    --autodetect \
    --source_format=source_format \
    "drive_uri" > /tmp/file_name
    
  2. Abre el archivo de definición de tablas en un editor de texto. Por ejemplo, con el comando nano /tmp/file_name, se abre el archivo en nano. El archivo debe verse de la manera siguiente para una fuente de datos externa de Hojas de cálculo de Google. Observa que "autodetect" se configuró como true.

    {
    "autodetect": true,
    "sourceFormat": "GOOGLE_SHEETS",
    "sourceUris": [
      "drive_uri"
    ]
    }
    
  3. Opcional: Edita manualmente el archivo de definición de tablas para modificar, agregar o borrar configuraciones generales como maxBadRecords y ignoreUnknownValues. No existen opciones de configuración específicas para los archivos fuente JSON, pero hay configuraciones que se aplican a los archivos CSV y de 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 en particular o un rango de celdas en un archivo de Hojas de cálculo de Google, agrega la propiedad range al archivo de definición de tablas. Para consultar una hoja en particular, especifica el nombre de la hoja. Para consultar un rango de celdas, especifica el rango en el formato siguiente: sheet_name!top_left_cell_id:bottom_right_cell_id, por ejemplo, "Sheet1!A1:B20". Si no se especifica el parámetro range, se usará la primera hoja del archivo.

Crea una definición de tablas con un esquema intercalado

Si no deseas utilizar la detección automática de esquemas, puedes crear un archivo de definición de tablas; para ello, proporciona una definición de esquema intercalado. Para proporcionar una definición de esquema intercalado, crea una lista de los campos y tipos de datos en la línea de comandos en el formato siguiente: field:data_type,field:data_type.

Puedes usar una definición de esquema intercalado cuando creas archivos de definición de tablas para los archivos siguientes:

  • Los archivos JSON almacenados en Cloud Storage o Google Drive
  • Los archivos CSV almacenados en Cloud Storage o Google Drive
  • Archivos de Hojas de cálculo de Google almacenados en Google Drive

Si deseas usar la CLI a fin de crear una definición de tablas para una fuente de datos de Cloud Storage mediante una definición de esquema intercalado, haz lo siguiente:

  1. Usa el comando mkdef de la herramienta de línea de comandos con la marca --noautodetect para crear una definición de tablas. Con el comando mkdef, se genera un archivo de definición de tablas en formato JSON. En el ejemplo siguiente, se crea una definición de tablas y se escribe el resultado en un archivo: /tmp/file_name.

    En el comando, realiza el reemplazo siguiente:

    • source_format por tu formato de archivo: NEWLINE_DELIMITED_JSON, CSV o GOOGLE_SHEETS
    • uri por tu URI de Cloud Storage o tu URI de Google Drive. Por ejemplo, https://drive.google.com/open?id=123ABCD123AbcD123Abcd para Google Drive o gs://mybucket/myfile para Cloud Storage
    • field:data_type,field:data_type por tu definición de esquema, por ejemplo, Name:STRING,Address:STRING, ...
    • file_name por el nombre de tu archivo de definición de tablas
    bq mkdef \
    --noautodetect \
    --source_format=source_format \
    "uri" \
    field:data_type,field:data_type > /tmp/file_name
    
  2. Opcional: Abre el archivo de definición de tablas en un editor de texto. Por ejemplo, con el comando nano /tmp/file_name, se abre el archivo en nano. El archivo debe verse de la manera siguiente. Ten en cuenta que "autodetect" no está habilitada y que la información del esquema se escribe en el archivo de definición de tablas.

    {
    "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 tablas para modificar, agregar o borrar configuraciones generales como maxBadRecords y ignoreUnknownValues. No existen opciones de configuración específicas para los archivos fuente JSON, pero hay configuraciones que se aplican a los archivos CSV y de Hojas de cálculo de Google. Para obtener más información, consulta ExternalDataConfiguration en la referencia de la API.

Crea una definición de tabla con un archivo de esquema JSON

Si no deseas usar la detección automática ni proporcionar una definición de esquema intercalado, puedes crear un archivo de esquema JSON y hacer referencia a él cuando crees tu archivo de definición de tablas. Debes crear el archivo de esquema JSON manualmente y este debe estar en tu máquina local. No se admite la referencia a un archivo de esquema JSON almacenado en Cloud Storage o en Google Drive.

Puedes utilizar un archivo de esquema JSON cuando creas definiciones de tablas para lo siguiente:

  • Los archivos JSON almacenados en Cloud Storage o Google Drive
  • Los archivos CSV almacenados en Cloud Storage o Google Drive
  • Archivos de Hojas de cálculo de Google almacenados en Google Drive

Cloud Storage

Si deseas usar la CLI a fin de crear una definición de tablas para una fuente de datos de Cloud Storage mediante un archivo de esquema JSON, haz lo siguiente:

  1. Usa el comando mkdef de la herramienta de línea de comandos con la marca --noautodetect para crear una definición de tablas. Con el comando mkdef, se genera un archivo de definición de tablas en formato JSON. En el ejemplo siguiente, se crea una definición de tablas y se escribe el resultado en un archivo: /tmp/file_name.

    En el comando, realiza el reemplazo siguiente:

    • source_format por tu formato de archivo: NEWLINE_DELIMITED_JSON, CSV o GOOGLE_SHEETS
    • file_name por el nombre de tu archivo de definición de tablas
    • bucket_uri por tu URI de Cloud Storage , por ejemplo, gs://mybucket/myfile
    • path_to_schema_file por la ubicación del archivo de esquema JSON en tu máquina local
    bq mkdef \
    --noautodetect \
    --source_format=source_format \
    "bucket_uri" \
    path_to_schema_file > /tmp/file_name
    
  2. Opcional: Abre el archivo de definición de tablas en un editor de texto. Por ejemplo, con el comando nano /tmp/file_name, se abre el archivo en
    nano. El archivo debe verse de la manera siguiente. Ten en cuenta que "autodetect" no está habilitada y que la información del esquema se escribe en el archivo de definición de tablas.

    {
    "schema": {
      "fields": [
        {
          "name": "field",
          "type": "data_type"
        },
        {
          "name": "field",
          "type": "data_type"
        }
        ...
      ]
    },
    "sourceFormat": "NEWLINE_DELIMITED_JSON",
    "sourceUris": [
      "bucket_uri"
    ]
    }
    
  3. Opcional: Edita manualmente el archivo de definición de tablas para modificar, agregar o borrar configuraciones generales como maxBadRecords y ignoreUnknownValues. No existen opciones de configuración específicas para los archivos fuente JSON, pero hay configuraciones que se aplican a los archivos CSV y de Hojas de cálculo de Google. Para obtener más información, consulta ExternalDataConfiguration en la referencia de la API.

Google Drive

Si deseas usar la CLI a fin de crear una definición de tablas para una fuente de datos de Google Drive mediante un archivo de esquema JSON, haz lo siguiente:

  1. Usa el comando mkdef de la herramienta de línea de comandos con la marca --noautodetect para crear una definición de tablas. Con el comando mkdef, se genera un archivo de definición de tablas en formato JSON. En el ejemplo siguiente, se crea una definición de tablas y se escribe el resultado en un archivo: /tmp/file_name.

    En el comando, realiza el reemplazo siguiente:

    • source_format por tu formato de archivo: NEWLINE_DELIMITED_JSON, CSV o GOOGLE_SHEETS
    • drive_uri por tu URI de Google Drive, por ejemplo, https://drive.google.com/open?id=123ABCD123AbcD123Abcd
    • path_to_schema_file por la ubicación del archivo de esquema JSON en tu máquina local
    • file_name por el nombre de tu archivo de definición de tablas
    bq mkdef \
    --noautodetect \
    --source_format=source_format \
    "drive_uri" \
    path_to_schema_file > /tmp/file_name
    
  2. Abre el archivo de definición de tablas en un editor de texto. Por ejemplo, con el comando nano /tmp/file_name, se abre el archivo en nano. El archivo debe verse de la manera siguiente. Ten en cuenta que "autodetect" no está habilitada y que la información del esquema se escribe en el archivo de definición de tablas.

    {
    "schema": {
      "fields": [
        {
          "name": "field",
          "type": "data_type"
        },
        {
          "name": "field",
          "type": "data_type"
        }
        ...
      ]
    },
    "sourceFormat": "GOOGLE_SHEETS",
    "sourceUris": [
      "drive_uri"
    ]
    }
    
  3. Opcional: Edita manualmente el archivo de definición de tablas para modificar, agregar o borrar configuraciones generales como maxBadRecords y ignoreUnknownValues. No existen opciones de configuración específicas para los archivos fuente JSON, pero hay configuraciones que se aplican a los archivos CSV y de 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 en particular o un rango de celdas en un archivo de Hojas de cálculo de Google, agrega la propiedad range al archivo de definición de tablas. Para consultar una hoja en particular, especifica el nombre de la hoja. Para consultar un rango de celdas, especifica el rango en el formato siguiente: sheet_name!top_left_cell_id:bottom_right_cell_id, por ejemplo, "Sheet1!A1:B20". Si no se especifica el parámetro range, se usará la primera hoja del archivo.

Crea definiciones de tablas Avro

Si utilizas un archivo de Avro como fuente de datos externa, BigQuery recupera automáticamente el esquema con el uso de los datos de origen. Cuando creas una definición de tablas para los archivos de Avro, no necesitas utilizar la detección automática de esquemas ni proporcionar una definición de esquema intercalado o un archivo de esquema.

Puedes crear un archivo de definición de tablas para los datos de Avro que están almacenados en Cloud Storage o Google Drive.

Si deseas usar la CLI a fin de crear un archivo de definición de tablas para los datos de Avro, haz lo siguiente:

  1. Usa el comando mkdef de la herramienta de línea de comandos para crear una definición de tablas. No necesitas usar la marca --noautodetect con los archivos Avro. La detección automática de esquemas está inhabilitada para los archivos Avro. Con el comando mkdef, se genera un archivo de definición de tablas en formato JSON. En el ejemplo siguiente, se crea una definición de tablas y se escribe el resultado en un archivo: /tmp/file_name.

    En el comando, realiza el reemplazo siguiente:

    • uri por tu URI de Cloud Storage o tu URI de Google Drive. Por ejemplo, https://drive.google.com/open?id=123ABCD123AbcD123Abcd para Google Drive o gs://mybucket/myfile para Cloud Storage
    • file_name por el nombre de tu archivo de definición de tablas
    bq mkdef \
    --source_format=AVRO \
    "uri" > /tmp/file_name
    
  2. Opcional: Abre el archivo de definición de tablas en un editor de texto. Por ejemplo, con el comando nano /tmp/file_name, se abre el archivo en nano. El archivo debe verse de la manera siguiente. Ten en cuenta que no es necesaria la configuración de "autodetect".

    {
    "sourceFormat": "AVRO",
    "sourceUris": [
      "uri"
    ]
    }
    
  3. Opcional: Edita manualmente el archivo de definición de tablas para modificar, agregar o borrar configuraciones generales como maxBadRecords y ignoreUnknownValues. No existen opciones de configuración específicas para los archivos fuente Avro, pero hay configuraciones que se aplican a los archivos CSV y de Hojas de cálculo de Google. Para obtener más información, consulta ExternalDataConfiguration en la referencia de la API.

Crea definiciones de tablas de exportación de Datastore y Cloud Firestore

Si usas una exportación de Datastore o Cloud Firestore como fuente de datos externa, se recupera automáticamente el esquema mediante los datos de origen de descripción automática en BigQuery. Cuando creas una definición de tablas para los archivos de copia de seguridad de Datastore y Cloud Firestore, no necesitas usar la detección automática de esquemas ni proporcionar una definición de esquema intercalado o un archivo de esquema.

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

Si deseas usar la CLI a fin de crear un archivo de definición de tablas para los datos de exportación de Datastore o Cloud Firestore, haz lo siguiente:

  1. Usa el comando mkdef de la herramienta de línea de comandos para crear una definición de tablas. No necesitas usar la marca --noautodetect con los archivos de copia de seguridad de Datastore o Cloud Firestore. La detección automática de esquemas está inhabilitada para estos tipos de archivos. Con el comando mkdef, se genera un archivo de definición de tablas en formato JSON. En el ejemplo siguiente, se crea una definición de tablas y se escribe el resultado en un archivo: /tmp/file_name.

    En el comando, realiza el reemplazo siguiente:

    Ten en cuenta que el formato de origen DATASTORE_BACKUP se usa tanto para Datastore como para Cloud Firestore.

    bq mkdef \
    --source_format=DATASTORE_BACKUP \
    "uri" > /tmp/file_name
    
  2. Opcional: Abre el archivo de definición de tablas en un editor de texto. Por ejemplo, con el comando nano /tmp/file_name, se abre el archivo en nano. El archivo debe verse de la manera siguiente. Ten en cuenta que no es necesaria la configuración de "autodetect".

    {
    "sourceFormat": "DATASTORE_BACKUP",
    "sourceUris": [
      "gs://bucket_uri"
    ]
    }
    
  3. Opcional: Edita manualmente el archivo de definición de tablas para modificar, agregar o borrar configuraciones como maxBadRecords y ignoreUnknownValues. No existen opciones de configuración específicas para los archivos de exportación de Datastore y Cloud Firestore, pero hay configuraciones que se aplican a los archivos CSV y de Hojas de cálculo de Google. Para obtener más información, consulta ExternalDataConfiguration en la referencia de la API.

Crea definiciones de tablas de Cloud Bigtable

Cuando creas un archivo de definición de tablas para Cloud Bigtable, genera manualmente el archivo en formato JSON. Actualmente, no se admite el uso del comando mkdef a fin de crear una definición de tablas para las fuentes de datos de Cloud Bigtable. La detección automática de esquemas tampoco es compatible con Cloud Bigtable. Para obtener una lista de las opciones de definición de tablas de Cloud Bigtable, consulta BigtableOptions en la referencia de la API de REST.

Un archivo de definición de tablas JSON para Cloud Bigtable se parece al ejemplo siguiente. Mediante este archivo de definición de tablas, en BigQuery se leen los datos de una sola familia de columnas y se interpretan los valores como números enteros codificados binarios.

{
    "sourceFormat": "BIGTABLE",
    "sourceUris": [
        "https://googleapis.com/bigtable/projects/project_id/instances/instance_id/tables/table_name"
    ],
    "bigtableOptions": {
        "columnFamilies" : [
            {
                "familyId": "family_int",
                "type": "INTEGER",
                "encoding": "BINARY"
            }
        ],
    }
}

Compatibilidad con comodines para los archivos de definición de tablas

Si los datos de Cloud Storage están separados en varios archivos que comparten un nombre base común, puedes usar un comodín en el URI en el archivo de definición de tablas. Agrega un asterisco (*) al nombre base y escribe el depósito y el nombre de archivo entre comillas. Por ejemplo, si tienes dos archivos llamados fed- sample000001.csv y fed-sample000002.csv, el URI del depósito será "gs://mybucket/fed-sample*".

Solo puedes usar un comodín para objetos (nombres de archivos) dentro de tu depósito. El comodín puede aparecer dentro del nombre del objeto o al final de este. No se admite la adición de un comodín al nombre del depósito.

Para los datos de Cloud Bigtable, solo se puede especificar un URI y este tiene que ser una URL HTTPS totalmente especificada y válida para una tabla de Cloud Bigtable. Para las copias de seguridad de Datastore, solo se puede especificar un URI, que debe terminar con .backup_info.

El carácter comodín * no se permite cuando se crean archivos de definición de tablas para los elementos siguientes:

  • Fuentes de datos de Cloud Bigtable
  • Exportaciones de Datastore almacenadas en Cloud Storage
  • Exportaciones de Cloud Firestore almacenadas en Cloud Storage
  • Datos almacenados en Google Drive