Eseguire query sui dati protobuf

Questo documento fornisce esempi di pattern di query comuni per la lettura e l'esecuzione di query sui dati del buffer del protocollo (protobuf) archiviati in Bigtable.

Prima di leggere questa pagina, acquisisci familiarità con quanto segue:

Dati di esempio

Gli esempi seguenti utilizzano una tabella Music che memorizza informazioni su album e artisti. I dati vengono archiviati in una famiglia di colonne denominata album_details, con un qualificatore di colonna album. Il qualificatore di colonna contiene messaggi protobuf.

Lo schema protobuf è definito nei seguenti file proto:

  • artist.proto:

    syntax = "proto3";

package package_name;

message Artist {
 string name = 1;
}

  • album.proto:

    syntax = "proto3";

package package_name;

import "artist.proto";

message Album {
 string title = 1;
 Artist artist = 2;
 int32 release_year = 3;
}

Di conseguenza, Bigtable crea un bundle di schema per questa tabella che contiene il seguente set di descrittori per queste definizioni protobuf:

file {
  name: "artist.proto"
  package: "package_name"
  message_type {
    name: "Artist"
    field {
      name: "name"
      number: 1
      label: LABEL_OPTIONAL
      type: TYPE_STRING
      json_name: "name"
    }
  }
  syntax: "proto3"
}
file {
  name: "album.proto"
  package: "package_name"
  dependency: "artist.proto"
  message_type {
    name: "Album"
    field {
      name: "title"
      number: 1
      label: LABEL_OPTIONAL
      type: TYPE_STRING
      json_name: "title"
    }
    field {
      name: "artist"
      number: 2
      label: LABEL_OPTIONAL
      type: TYPE_MESSAGE
      type_name: ".package_name.Artist"
      json_name: "artist"
    }
    field {
      name: "release_year"
      number: 3
      label: LABEL_OPTIONAL
      type: TYPE_INT32
      json_name: "releaseYear"
    }
  }
  syntax: "proto3"
}

Esempi di query

Gli esempi seguenti mostrano come eseguire query sui dati protobuf utilizzando GoogleSQL per le tabelle esterne Bigtable e BigQuery.

Eseguire il cast di una colonna in un messaggio protobuf

Puoi utilizzare l'operatore CAST per interpretare un valore BYTES come messaggio protobuf. Per farlo, devi fornire il nome completo del messaggio protobuf nel seguente formato: SCHEMA_BUNDLE_ID.FULLY_QUALIFIED_MESSAGE_NAME.

Sostituisci quanto segue:

  • SCHEMA_BUNDLE_ID: l'ID univoco che hai assegnato al bundle di schemi quando l'hai creato.
  • FULLY_QUALIFIED_MESSAGE_NAME: il nome completo del messaggio, che deve includere il nome del pacchetto definito nel file proto, ad esempio package_name.message_name.

La seguente query di esempio esegue il cast della colonna album nel messaggio protobuf Album. Questo messaggio è definito nel pacchetto package_name e fa parte di un bundle di schemi denominato bundle_name:

SELECT
  CAST(album_details['album'] AS bundle_name.package_name.Album).title
FROM
  Music;

Accedere ai campi nidificati

Puoi accedere ai campi nidificati all'interno di un messaggio protobuf utilizzando la notazione con il punto.

La seguente query recupera il nome dell'artista dal messaggio Artist nidificato all'interno del messaggio Album:

SELECT
  CAST(album_details['album'] AS bundle_name.package_name.Album).artist.name
FROM
  Music;

Filtra in base ai campi protobuf

Puoi utilizzare la clausola WHERE per filtrare le righe in base ai valori dei campi all'interno di un messaggio protobuf.

La seguente query seleziona tutti gli album dell'artista Dana A.:

SELECT
  *
FROM
  Music
WHERE
  CAST(album_details['album'] AS bundle_name.package_name.Album).artist.name = 'Dana A.';

Campi protobuf aggregati

Puoi utilizzare funzioni di aggregazione come SUM, AVG, MIN, MAX e COUNT sui campi numerici all'interno dei messaggi protobuf.

La seguente query calcola l'anno di uscita medio di tutti gli album nella tabella:

SELECT
  AVG(CAST(album_details['album'] AS bundle_name.package_name.Album).release_year)
FROM
  Music;

Ordina in base ai campi protobuf

Puoi utilizzare la clausola ORDER BY per ordinare il set di risultati in base a un campo nel messaggio protobuf.

La seguente query recupera tutti gli album e li ordina in base all'anno di uscita in ordine decrescente:

SELECT
  *
FROM
  Music
ORDER BY
  CAST(album_details['album'] AS bundle_name.package_name.Album).release_year DESC;

Utilizzo con tabelle esterne BigQuery

Puoi eseguire query sui dati protobuf archiviati in Bigtable da BigQuery creando una tabella esterna. Quando crei la tabella esterna, specifichi il tipo di colonna come JSON, la codifica come PROTO_BINARY e la associ a un bundle di schema. Questo processo converte i byte del messaggio protobuf nei dati JSON equivalenti, consentendoti di eseguire query direttamente sui relativi campi.

Di seguito è riportato un esempio di file di definizione della tabella per creare una tabella esterna BigQuery nella tabella Music:

{
    "sourceFormat": "BIGTABLE",
    "sourceUris": [
        "https://googleapis.com/bigtable/projects/PROJECT_ID/instances/INSTANCE_ID/tables/Music"
    ],
    "bigtableOptions": {
        "columnFamilies" : [
            {
                "familyId": "album_details",
                "columns": [
                    {
                        "qualifierString": "album",
                        "type": "JSON",
                        "encoding": "PROTO_BINARY",
                        "protoConfig": {
                            "schemaBundleId": "bundle_name",
                            "protoMessageName": "package_name.Album"
                        }
                    }
                ]
            }
        ]
    }
}

Una volta creata la tabella esterna, puoi eseguire query sui dati protobuf come colonna JSON in BigQuery.

La seguente query recupera il titolo di tutti gli album pubblicati nel 2022 dalla tabella esterna BigQuery:

SELECT JSON_VALUE(value, '$.title') AS title
FROM
`PROJECT_ID.DATASET.TABLE_NAME` AS t,
UNNEST(t.album_details.album.cell)
WHERE INT64(JSON_EXTRACT(value, '$.releaseYear')) = 2022;

Passaggi successivi