Specificare le colonne ObjectRef negli schemi delle tabelle

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

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

Per ulteriori informazioni sull'utilizzo dei dati multimodali, consulta Analisi dei dati multimodali. Per un tutorial che mostra come lavorare con i dati ObjectRef, consulta Analizzare i dati multimodali con SQL. Per informazioni sull'utilizzo dei dati multimodali in Python, consulta Analizzare i dati multimodali in Python con BigQuery DataFrames.

Prerequisiti

Per compilare e aggiornare i valori ObjectRef in una tabella standard, la tabella deve avere una colonna STRING contenente le informazioni sugli URI per gli oggetti Cloud Storage correlati.

Devi disporre di un bucket Cloud Storage contenente gli stessi oggetti identificati nei dati URI della tabella standard di destinazione. Se vuoi mantenere i valori ObjectRef in una tabella standard utilizzando una tabella di oggetti, devi anche avere una tabella di oggetti che rappresenti gli oggetti nel bucket.

Mantenimento dei valori ObjectRef

Puoi utilizzare una tabella di oggetti per compilare e aggiornare i valori ObjectRef in una tabella standard. Se sei nella lista consentita per l'anteprima, tutte le tabelle di oggetti che crei hanno una colonna ref che contiene un valore ObjectRef per l'oggetto specificato. Puoi utilizzare l'URI dell'oggetto per unire la tabella standard alla tabella degli oggetti al fine di compilare e aggiornare i valori ObjectRef. Consigliamo questo approccio per la scalabilità, poiché evita la necessità di recuperare i metadati degli oggetti da Cloud Storage.

Se non vuoi creare una tabella di oggetti, puoi utilizzare le funzioni OBJ.FETCH_METADATA e OBJ.MAKE_REF per compilare e aggiornare i valori ObjectRef recuperando i metadati degli oggetti direttamente da Cloud Storage. Questo approccio potrebbe essere meno scalabile, poiché richiede il recupero dei metadati degli oggetti da Cloud Storage.

Crea una colonna ObjectRef

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

Tabella di oggetti

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

  1. Nella console Trusted Cloud , 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 degli oggetti che contiene i dati degli oggetti da integrare nella tabella standard.

  3. Fai clic su Esegui.

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

Funzioni SQL

Crea e compila una colonna ObjectRef in base all'output delle funzioni OBJ.FETCH_METADATA e OBJ.MAKE_REF:

  1. Nella console Trusted Cloud , 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.FETCH_METADATA(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 alla risorsa 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 Trusted Cloud console e copiando il valore nell'ultima sezione dell'ID connessione completamente qualificato visualizzato in ID connessione. Ad esempio, projects/myproject/locations/connection_location/connections/myconnection.

      Devi concedere il ruolo Utente oggetto archiviazione (roles/storage.objectUser) all'account di servizio della connessione su 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 ulteriori informazioni su come eseguire query, consulta Eseguire una query interattiva.

Crea una colonna ARRAY<ObjectRef>

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

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

Per un esempio di come utilizzare gli array di valori ObjectRef, consulta la sezione Elaborare dati multimodali ordinati utilizzando i valori ARRAY<ObjectRef> del tutorial Analisi dei 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 degli oggetti:

  1. Nella console Trusted Cloud , 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 degli oggetti che contiene gli stessi dati degli oggetti della colonna ObjectRef della tabella standard.

  3. Fai clic su Esegui.

Per ulteriori informazioni su come eseguire 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 console Trusted Cloud , 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.FETCH_METADATA(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 alla risorsa 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 Trusted Cloud console e copiando il valore nell'ultima sezione dell'ID connessione completamente qualificato visualizzato in ID connessione. Ad esempio, projects/myproject/locations/connection_location/connections/myconnection.

      Devi concedere il ruolo Utente oggetto archiviazione (roles/storage.objectUser) all'account di servizio della connessione su 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 ulteriori informazioni su come eseguire query, consulta Eseguire una query interattiva.

Passaggi successivi