Specifica le colonne nidificate e ripetute negli schemi delle tabelle

Questa pagina descrive come definire uno schema di tabella con colonne nidificate e ripetute in BigQuery. Per una panoramica degli schemi delle tabelle, consulta Specifica di uno schema.

Definire colonne nidificate e ripetute

Per creare una colonna con dati nidificati, imposta il tipo di dati della colonna su RECORD nello schema. È possibile accedere a RECORD come Tipo di STRUCT in GoogleSQL. Un STRUCT è un contenitore di campi ordinati.

Per creare una colonna con dati ripetuti, imposta la modalità della colonna su REPEATED nello schema. È possibile accedere a un campo ripetuto come tipo ARRAY in GoogleSQL.

Una colonna RECORD può avere la modalità REPEATED, rappresentata come un array di Tipi di STRUCT. Inoltre, un campo all'interno di un record può essere ripetuto, rappresentato come un STRUCT che contiene un valore ARRAY. Un array non può contenere direttamente un altro array. Per ulteriori informazioni, consulta Dichiarare un tipo ARRAY.

Limitazioni

Gli schemi nidificati e ripetuti sono soggetti alle seguenti limitazioni:

Uno schema non può contenere più di 15 livelli di tipi RECORD nidificati.

Le colonne di tipo RECORD possono contenere tipi RECORD nidificati, detti anche record child. Il limite massimo di profondità nidificata è 15 livelli. Questo limite è indipendentemente dal fatto che i valori RECORD siano scalari o basati su array (ripetuto).

Il tipo RECORD non è compatibile con UNION, INTERSECT, EXCEPT DISTINCT e SELECT DISTINCT.

Schema di esempio

L'esempio seguente mostra dati nidificati e ripetuti di esempio. Questa tabella contiene informazioni sulle persone. È costituito dai seguenti campi:

• id
• first_name
• last_name
• dob (data di nascita)
• addresses (un campo nidificato e ripetuto)
  • addresses.status (attuale o precedente)
  • addresses.address
  • addresses.city
  • addresses.state
  • addresses.zip
  • addresses.numberOfYears (anni all'indirizzo)

Il file di dati JSON sarà simile al seguente. Tieni presente che la colonna addresses contiene un array di valori (indicati da [ ]). I più indirizzi nell'array sono i dati ripetuti. I più campi all'interno di ogni indirizzo sono i dati nidificati.

{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]}
{"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"},{"status":"previous","address":"321 Main Street","city":"Hoboken","state":"NJ","zip":"44444","numberOfYears":"3"}]}

Lo schema di questa tabella è il seguente:

[
    {
        "name": "id",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "first_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "last_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "dob",
        "type": "DATE",
        "mode": "NULLABLE"
    },
    {
        "name": "addresses",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
            {
                "name": "status",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "address",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "city",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "state",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "zip",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "numberOfYears",
                "type": "STRING",
                "mode": "NULLABLE"
            }
        ]
    }
]

Specifica delle colonne nidificate e ripetute nell'esempio

Per creare una nuova tabella con le colonne nidificate e ripetute precedenti, seleziona una delle seguenti opzioni:

Console

Specifica la colonna addresses nidificata e ripetuta:

1. Nella console Google Cloud, apri la pagina BigQuery.

   Vai a BigQuery

2. Nel riquadro Spazio di esplorazione, espandi il progetto e seleziona un set di dati.

3. Nel riquadro dei dettagli, fai clic su Crea tabella.

4. Nella pagina Crea tabella, specifica i seguenti dettagli:

   • Per Origine, nel campo Crea tabella da, seleziona Vuota dalla tabella.

   • Nella sezione Destinazione, specifica i seguenti campi:

     • In Set di dati, seleziona il set di dati in cui creare il tabella.
     • In Tabella, inserisci il nome della tabella che vuoi creare.

   • Per Schema, fai clic su Aggiungi campo e inserisci lo schema della tabella seguente:

     • In Nome campo, inserisci addresses.
     • In Tipo, seleziona RECORD.
     • In corrispondenza di Modalità, scegli RIPETUTA.

     • Specifica i seguenti campi per un campo nidificato:

       • Nel campo Nome campo, inserisci status.
       • In Tipo, scegli STRINGA.
       • In Modalità, lascia il valore impostato su NULLABLE.

       • Fai clic su Aggiungi campo per aggiungere i seguenti campi:

         Nome campo	Tipo	Modalità
         address	STRING	NULLABLE
         city	STRING	NULLABLE
         state	STRING	NULLABLE
         zip	STRING	NULLABLE
         numberOfYears	STRING	NULLABLE

       In alternativa, fai clic su Modifica come testo e specifica lo schema come un array JSON.

SQL

Utilizza l'istruzione CREATE TABLE. Specifica lo schema utilizzando l'opzione colonna:

1. Nella console Google Cloud, vai alla pagina BigQuery.

   Vai a BigQuery

2. Nell'editor di query, inserisci la seguente istruzione:

   CREATE TABLE IF NOT EXISTS mydataset.mytable (
     id STRING,
     first_name STRING,
     last_name STRING,
     dob DATE,
     addresses
       ARRAY<
         STRUCT<
           status STRING,
           address STRING,
           city STRING,
           state STRING,
           zip STRING,
           numberOfYears STRING>>
   ) OPTIONS (
       description = 'Example name and addresses table');

3. Fai clic su Esegui.

Per ulteriori informazioni su come eseguire le query, consulta Eseguire una query interattiva.

bq

Per specificare la colonna addresses nidificata e ripetuta in un file di schema JSON: usa un editor di testo per creare un nuovo file. Incolla lo schema di esempio di cui sopra.

Dopo aver creato il file dello schema JSON, puoi fornirlo tramite lo strumento a riga di comando bq. Per ulteriori informazioni, consulta Utilizzare un file di schema JSON.

Vai

Prima di provare questo esempio, segui le istruzioni per la configurazione di Go nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API BigQuery Go.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
)

// createTableComplexSchema demonstrates creating a BigQuery table and specifying a complex schema that includes
// an array of Struct types.
func createTableComplexSchema(w io.Writer, projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	sampleSchema := bigquery.Schema{
		{Name: "id", Type: bigquery.StringFieldType},
		{Name: "first_name", Type: bigquery.StringFieldType},
		{Name: "last_name", Type: bigquery.StringFieldType},
		{Name: "dob", Type: bigquery.DateFieldType},
		{Name: "addresses",
			Type:     bigquery.RecordFieldType,
			Repeated: true,
			Schema: bigquery.Schema{
				{Name: "status", Type: bigquery.StringFieldType},
				{Name: "address", Type: bigquery.StringFieldType},
				{Name: "city", Type: bigquery.StringFieldType},
				{Name: "state", Type: bigquery.StringFieldType},
				{Name: "zip", Type: bigquery.StringFieldType},
				{Name: "numberOfYears", Type: bigquery.StringFieldType},
			}},
	}

	metaData := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, metaData); err != nil {
		return err
	}
	fmt.Fprintf(w, "created table %s\n", tableRef.FullyQualifiedName())
	return nil
}

Java

Prima di provare questo esempio, segui le istruzioni di configurazione Java riportate nella guida rapida all'utilizzo di BigQuery con le librerie client. Per ulteriori informazioni, consulta API Java BigQuery documentazione di riferimento.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per saperne di più, consulta Configurare l'autenticazione per le librerie client.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Field.Mode;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

public class NestedRepeatedSchema {

  public static void runNestedRepeatedSchema() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    createTableWithNestedRepeatedSchema(datasetName, tableName);
  }

  public static void createTableWithNestedRepeatedSchema(String datasetName, String tableName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);

      Schema schema =
          Schema.of(
              Field.of("id", StandardSQLTypeName.STRING),
              Field.of("first_name", StandardSQLTypeName.STRING),
              Field.of("last_name", StandardSQLTypeName.STRING),
              Field.of("dob", StandardSQLTypeName.DATE),
              // create the nested and repeated field
              Field.newBuilder(
                      "addresses",
                      StandardSQLTypeName.STRUCT,
                      Field.of("status", StandardSQLTypeName.STRING),
                      Field.of("address", StandardSQLTypeName.STRING),
                      Field.of("city", StandardSQLTypeName.STRING),
                      Field.of("state", StandardSQLTypeName.STRING),
                      Field.of("zip", StandardSQLTypeName.STRING),
                      Field.of("numberOfYears", StandardSQLTypeName.STRING))
                  .setMode(Mode.REPEATED)
                  .build());

      TableDefinition tableDefinition = StandardTableDefinition.of(schema);
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Table with nested and repeated schema created successfully");
    } catch (BigQueryException e) {
      System.out.println("Table was not created. \n" + e.toString());
    }
  }
}

Node.js

Prima di provare questo esempio, segui le istruzioni di configurazione Node.js riportate nella guida rapida all'utilizzo di BigQuery con le librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API BigQuery Node.js.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function nestedRepeatedSchema() {
  // Creates a new table named "my_table" in "my_dataset"
  // with nested and repeated columns in schema.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";
  // const schema = [
  //   {name: 'Name', type: 'STRING', mode: 'REQUIRED'},
  //   {
  //     name: 'Addresses',
  //     type: 'RECORD',
  //     mode: 'REPEATED',
  //     fields: [
  //       {name: 'Address', type: 'STRING'},
  //       {name: 'City', type: 'STRING'},
  //       {name: 'State', type: 'STRING'},
  //       {name: 'Zip', type: 'STRING'},
  //     ],
  //   },
  // ];

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    location: 'US',
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);
}

Python

Prima di provare questo esempio, segui le istruzioni per la configurazione di Python nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta API Python BigQuery documentazione di riferimento.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per saperne di più, consulta Configurare l'autenticazione per le librerie client.

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change table_id to the full name of the table you want to create.
table_id = "your-project.your_dataset.your_table_name"

schema = [
    bigquery.SchemaField("id", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("first_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("last_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("dob", "DATE", mode="NULLABLE"),
    bigquery.SchemaField(
        "addresses",
        "RECORD",
        mode="REPEATED",
        fields=[
            bigquery.SchemaField("status", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("address", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("city", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("state", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("zip", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("numberOfYears", "STRING", mode="NULLABLE"),
        ],
    ),
]
table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)  # API request

print(f"Created table {table.project}.{table.dataset_id}.{table.table_id}.")

Inserire i dati nelle colonne nidificate nell'esempio

Utilizza le seguenti query per inserire record di dati nidificati nelle tabelle che hanno RECORD colonne di tipo di dati.

Esempio 1

INSERT INTO mydataset.mytable (id,
first_name,
last_name,
dob,
addresses) values ("1","Johnny","Dawn","1969-01-22",
    ARRAY<
      STRUCT<
        status STRING,
        address STRING,
        city STRING,
        state STRING,
        zip STRING,
        numberOfYears STRING>>
      [("current","123 First Avenue","Seattle","WA","11111","1")])

Esempio 2

INSERT INTO mydataset.mytable (id,
first_name,
last_name,
dob,
addresses) values ("1","Johnny","Dawn","1969-01-22",[("current","123 First Avenue","Seattle","WA","11111","1")])

Eseguire query su colonne nidificate e ripetute

Per selezionare il valore di un ARRAY in una posizione specifica, utilizza un pedice di array dell'operatore. Per accedere agli elementi in un STRUCT, utilizza il operatore punto. Il seguente esempio seleziona il nome, il cognome e il primo indirizzo elencati nel campo addresses:

SELECT
  first_name,
  last_name,
  addresses[offset(0)].address
FROM
  mydataset.mytable;

Il risultato è il seguente:

+------------+-----------+------------------+
| first_name | last_name | address          |
+------------+-----------+------------------+
| John       | Doe       | 123 First Avenue |
| Jane       | Doe       | 789 Any Avenue   |
+------------+-----------+------------------+

Per estrarre tutti gli elementi di un ARRAY, utilizza la classe Operatore UNNEST con un CROSS JOIN. L'esempio seguente seleziona il nome, il cognome, l'indirizzo e lo stato per Tutti gli indirizzi che non si trovano a New York:

SELECT
  first_name,
  last_name,
  a.address,
  a.state
FROM
  mydataset.mytable CROSS JOIN UNNEST(addresses) AS a
WHERE
  a.state != 'NY';

Il risultato è il seguente:

+------------+-----------+------------------+-------+
| first_name | last_name | address          | state |
+------------+-----------+------------------+-------+
| John       | Doe       | 123 First Avenue | WA    |
| John       | Doe       | 456 Main Street  | OR    |
| Jane       | Doe       | 321 Main Street  | NJ    |
+------------+-----------+------------------+-------+

Modificare le colonne nidificate e ripetute

Dopo aver aggiunto una colonna nidificata o una colonna nidificata e ripetuta al file definizione dello schema, puoi modificare la colonna come faresti con qualsiasi altro tipo di colonna. BigQuery supporta in modo nativo diverse modifiche allo schema, ad esempio: aggiungendo un nuovo campo nidificato a un record o riducendo la modalità di un campo nidificato. Per Per saperne di più, consulta la sezione Modifica degli schemi delle tabelle.

Quando utilizzare colonne nidificate e ripetute

BigQuery offre le migliori prestazioni quando i dati sono denormalizzati. Piuttosto che preservare uno schema relazionale come uno schema a stella o a fiocco di neve, denormalizza i dati e sfrutta le colonne nidificate e ripetute. Nidificati e le colonne ripetute possono mantenere relazioni senza un impatto sul rendimento di conservare uno schema relazionale (normalizzato).

Ad esempio, un database relazionale utilizzato per monitorare i libri della biblioteca probabilmente manterrebbe tutte le informazioni sull'autore in una tabella separata. Una chiave come author_id verrebbe utilizzata per collegare il libro agli autori.

In BigQuery, puoi preservare la relazione tra author senza creare una tabella author separata. Devi, invece, creare un autore e nidificare i campi al suo interno, come il nome, il cognome dell'autore nome, data di nascita e così via. Se un libro ha più autori, puoi ripetere la colonna degli autori nidificati.

Supponiamo di avere la seguente tabella mydataset.books:

+------------------+------------+-----------+
| title            | author_ids | num_pages |
+------------------+------------+-----------+
| Example Book One | [123, 789] | 487       |
| Example Book Two | [456]      | 89        |
+------------------+------------+-----------+

Hai anche la seguente tabella, mydataset.authors, con le informazioni per ogni ID autore:

+-----------+-------------+---------------+
| author_id | author_name | date_of_birth |
+-----------+-------------+---------------+
| 123       | Alex        | 01-01-1960    |
| 456       | Rosario     | 01-01-1970    |
| 789       | Kim         | 01-01-1980    |
+-----------+-------------+---------------+

Se le tabelle sono di grandi dimensioni, l'unione regolare potrebbe richiedere molte risorse. In base alla tua situazione, potrebbe essere utile crea una singola tabella contenente tutte le informazioni:

CREATE TABLE mydataset.denormalized_books(
  title STRING,
  authors ARRAY<STRUCT<id INT64, name STRING, date_of_birth STRING>>,
  num_pages INT64)
AS (
  SELECT
    title,
    ARRAY_AGG(STRUCT(author_id, author_name, date_of_birth)) AS authors,
    ANY_VALUE(num_pages)
  FROM
    mydataset.books,
    UNNEST(author_ids) id
  JOIN
    mydataset.authors
    ON
      id = author_id
  GROUP BY
    title
);

La tabella risultante sarà simile alla seguente:

+------------------+-------------------------------+-----------+
| title            | authors                       | num_pages |
+------------------+-------------------------------+-----------+
| Example Book One | [{123, Alex, 01-01-1960},     | 487       |
|                  |  {789, Kim, 01-01-1980}]      |           |
| Example Book Two | [{456, Rosario, 01-01-1970}]  | 89        |
+------------------+-------------------------------+-----------+

BigQuery supporta il caricamento di dati nidificati e ripetuti dall'origine che supportano schemi basati su oggetti, come file JSON, file Avro File di esportazione Firestore e file di esportazione Datastore.

Deduplicare i record duplicati in una tabella

La seguente query utilizza row_number() per identificare i record duplicati che hanno gli stessi valori per last_name e first_name negli esempi utilizzati e li ordina per dob:

CREATE OR REPLACE TABLE mydataset.mytable AS (
  SELECT * except(row_num) FROM (
    SELECT *,
    row_number() over (partition by last_name, first_name order by dob) row_num
    FROM
    mydataset.mytable) temp_table
  WHERE row_num=1
)

Sicurezza dei tavoli

Per controllare l'accesso alle tabelle in BigQuery, consulta Introduzione ai controlli di accesso alle tabelle.

Passaggi successivi

• Per inserire e aggiornare righe con colonne nidificate e ripetute, consulta Sintassi del linguaggio di manipolazione dei dati.