Specificare le colonne ObjectRef negli schemi delle tabelle

Questo documento descrive come definire uno schema di tabella standard BigQuery con colonne che possono archiviare valori ObjectRef.

I valori ObjectRef forniscono metadati e informazioni di connessione per gli oggetti in Cloud Storage. Utilizza i valori ObjectRef quando devi integrare dati non strutturati in una tabella standard. Ad esempio, in una tabella dei prodotti, puoi archiviare le immagini dei prodotti nella stessa riga con il resto delle informazioni sul prodotto aggiungendo una colonna contenente valori ObjectRef. Puoi archiviare ObjectRef valori in STRUCT colonne che utilizzano il ObjectRef formato, ovvero STRUCT<uri STRING, version STRING, authorizer STRING, details JSON>.

Per saperne di più su come lavorare con i dati multimodali, consulta Analizzare i dati multimodali. Per un tutorial che mostra come lavorare con i dati ObjectRef, consulta Analizzare i dati multimodali con SQL.

Nota: gli esempi in questo documento utilizzano l' CREATE OR REPLACE TABLE istruzione per creare e popolare una colonna ObjectRef in un'unica operazione, ma puoi anche utilizzare l' ALTER TABLE ADD COLUMN istruzione per aggiungere una colonna STRUCT a una tabella esistente e poi utilizzare l' UPDATE istruzione per popolare la colonna in un'operazione separata.

Prerequisiti

Per popolare e aggiornare i valori ObjectRef in una tabella standard, la tabella deve avere una colonna STRING che contenga informazioni sull'URI per gli oggetti Cloud Storage correlati.

Devi avere un bucket Cloud Storage che contenga gli stessi oggetti identificati nei dati URI della tabella standard di destinazione.

Gestire i valori ObjectRef

Tutte le tabelle di oggetti che crei hanno una colonna ref che contiene un valore ObjectRef per l'oggetto specificato. Se hai una tabella di oggetti esistente, puoi unirla alla tabella standard nella colonna URI dell'oggetto per popolare e aggiornare i valori ObjectRef. Questo è più efficiente perché evita di recuperare nuovamente i metadati da Cloud Storage per creare un nuovo valore ObjectRef.

Allo stesso modo, se hai già un set di dati Storage Insights per i metadati degli oggetti, puoi utilizzare la colonna ref.uri o selfLink per unire la tabella standard al set di dati Storage Insights per popolare e aggiornare i valori ObjectRef. Tutti i valori ObjectRef creati nei set di dati Storage Insights non hanno un autorizzatore. Per eseguire query su questi oggetti, devi avere accesso diretto all'oggetto o aggiungere un autorizzatore a ObjectRef per utilizzare l'accesso delegato.

Se non hai una tabella di oggetti o un set di dati Storage Insights esistente, puoi utilizzare la OBJ.MAKE_REF funzione per popolare e aggiornare ObjectRef valori recuperando i metadati degli oggetti direttamente da Cloud Storage. Questo approccio potrebbe essere meno scalabile, perché richiede il recupero dei metadati degli oggetti da Cloud Storage.

Creare una colonna ObjectRef

Per creare e popolare una colonna ObjectRef in una tabella standard, seleziona una delle seguenti opzioni:

Funzioni SQL

Crea e popola una colonna ObjectRef in base all'output della funzione OBJ.MAKE_REF:

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

   Vai a BigQuery

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

   CREATE OR REPLACE TABLE PROJECT_ID.DATASET_ID.TABLE_NAME
   AS
   SELECT TABLE_NAME.*,
     OBJ.MAKE_REF(uri, 'CONNECTION_ID') AS objectrefcolumn
   FROM DATASET_ID.TABLE_NAME;

   Sostituisci quanto segue:

   • PROJECT_ID: il tuo ID progetto. Puoi saltare questo argomento se stai creando la tabella nel progetto corrente.
   • DATASET_ID: l'ID del set di dati che stai creando.
   • TABLE_NAME: il nome della tabella standard che stai ricreando.
   • CONNECTION_ID: un valore STRING che contiene una connessione alle risorse Cloud che il servizio può utilizzare per accedere agli oggetti in Cloud Storage, nel formato location.connection_id. Ad esempio, us-west1.myconnection. Puoi ottenere l'ID connessione visualizzando i dettagli della connessione nella Google Cloud console e copiando il valore nell'ultima sezione dell'ID connessione completo visualizzato in ID connessione. Ad esempio, projects/myproject/locations/connection_location/connections/myconnection.

     Devi concedere il ruolo Utente oggetti Storage (roles/storage.objectUser) al account di servizio della connessione in qualsiasi bucket Cloud Storage in cui lo utilizzi per accedere agli oggetti.

     La connessione deve trovarsi nello stesso progetto e nella stessa regione della query in cui chiami la funzione.

3. Fai clic su Esegui.

Per saperne di più su come eseguire le query, consulta Eseguire una query interattiva.

Tabella di oggetti

Crea e popola una colonna ObjectRef in base ai dati di una colonna ref della tabella di oggetti:

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

   Vai a BigQuery

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

   CREATE OR REPLACE TABLE PROJECT_ID.DATASET_ID.TABLE_NAME
   AS
   SELECT TABLE_NAME.*, OBJECT_TABLE.ref AS objectrefcolumn
   FROM DATASET_ID.TABLE_NAME
   INNER JOIN DATASET_ID.OBJECT_TABLE
   ON OBJECT_TABLE.uri = TABLE_NAME.uri;

   Sostituisci quanto segue:

   • PROJECT_ID: il tuo ID progetto. Puoi saltare questo argomento se stai creando la tabella nel progetto corrente.
   • DATASET_ID: l'ID del set di dati che stai creando.
   • TABLE_NAME: il nome della tabella standard che stai ricreando.
   • OBJECT_TABLE: il nome della tabella di oggetti che contiene i dati degli oggetti che vuoi integrare nella tabella standard tabella.

3. Fai clic su Esegui.

Per saperne di più su come eseguire le query, consulta Eseguire una query interattiva.

Creare una colonna ARRAY<ObjectRef>

Puoi creare una colonna ARRAY<STRUCT<uri STRING, version STRING, authorizer STRING, details JSON>> per contenere array di ObjectRef valori. Ad esempio, puoi dividere un video in immagini separate e poi archiviare queste immagini come un array di valori ObjectRef.

Puoi utilizzare la ARRAY_AGG funzione per aggregare array di ObjectRef valori, inclusa la clausola ORDER BY per conservare l'ordine degli oggetti, se necessario. Puoi utilizzare l' UNNEST operatore per analizzare un array di valori ObjectRef in singoli valori ObjectRef, inclusa la clausola WITH OFFSET per conservare l'ordine degli oggetti, se necessario. Puoi utilizzare i metadati degli oggetti, come il percorso URI e il nome file dell'oggetto, per mappare i valori ObjectRef che rappresentano i blocchi di oggetti a un valore ObjectRef che rappresenta l'oggetto originale.

Per un esempio di come lavorare con gli array di valori ObjectRef, consulta la sezione Elaborare i dati multimodali ordinati utilizzando i valori ARRAY<ObjectRef> del tutorial Analizzare i dati multimodali con SQL.

Aggiornare una colonna ObjectRef

Per aggiornare una colonna ObjectRef in una tabella standard, seleziona una delle seguenti opzioni:

Tabella di oggetti

Aggiorna una colonna ObjectRef utilizzando i dati di una colonna ref della tabella di oggetti:

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

   Vai a BigQuery

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

   UPDATE PROJECT_ID.DATASET_ID.TABLE_NAME
   SET objectrefcolumn = (SELECT ref FROM DATASET_ID.OBJECT_TABLE WHERE OBJECT_TABLE.uri = TABLE_NAME.uri)
   WHERE uri != "";

   Sostituisci quanto segue:

   • PROJECT_ID: il tuo ID progetto. Puoi saltare questo argomento se stai creando la tabella nel progetto corrente.
   • DATASET_ID: l'ID del set di dati che stai creando.
   • TABLE_NAME: il nome della tabella standard che stai ricreando.
   • OBJECT_TABLE: il nome della tabella di oggetti che contiene gli stessi dati degli oggetti della colonna ObjectRef della tabella standard.

3. Fai clic su Esegui.

Per saperne di più su come eseguire le query, consulta Eseguire una query interattiva.

Funzioni SQL

Aggiorna una colonna ObjectRef utilizzando l'output delle funzioni OBJ.FETCH_METADATA e OBJ.MAKE_REF:

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

   Vai a BigQuery

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

   UPDATE PROJECT_ID.DATASET_ID.TABLE_NAME
   SET objectrefcolumn = (SELECT OBJ.MAKE_REF(uri, 'CONNECTION_ID'))
   WHERE uri != "";

   Sostituisci quanto segue:

   • PROJECT_ID: il tuo ID progetto. Puoi saltare questo argomento se stai creando la tabella nel progetto corrente.
   • DATASET_ID: l'ID del set di dati che stai creando.
   • TABLE_NAME: il nome della tabella standard che stai ricreando.
   • CONNECTION_ID: un valore STRING che contiene una connessione alle risorse Cloud che il servizio può utilizzare per accedere agli oggetti in Cloud Storage, nel formato location.connection_id. Ad esempio, us-west1.myconnection. Puoi ottenere l'ID connessione visualizzando i dettagli della connessione nella Google Cloud console e copiando il valore nell'ultima sezione dell'ID connessione completo visualizzato in ID connessione. Ad esempio, projects/myproject/locations/connection_location/connections/myconnection.

     Devi concedere il ruolo Utente oggetti Storage (roles/storage.objectUser) al account di servizio della connessione in qualsiasi bucket Cloud Storage in cui lo utilizzi per accedere agli oggetti.

     La connessione deve trovarsi nello stesso progetto e nella stessa regione della query in cui chiami la funzione.

3. Fai clic su Esegui.

Per saperne di più su come eseguire le query, consulta Eseguire una query interattiva.

Passaggi successivi

• Analizzare i dati multimodali.
• Analizzare i dati multimodali con SQL.