Especificar colunas ObjectRef em esquemas de tabelas

Neste documento, descrevemos como definir um esquema de tabela padrão do BigQuery com colunas que podem armazenar valores ObjectRef.

Os valores de ObjectRef fornecem metadados e informações de conexão para objetos no Cloud Storage. Use valores ObjectRef quando precisar integrar dados não estruturados a uma tabela padrão. Por exemplo, em uma tabela de produtos, é possível armazenar imagens na mesma linha com o restante das informações do produto adicionando uma coluna que contenha valores ObjectRef. É possível armazenar valores ObjectRef em colunas STRUCT que usam o formato ObjectRef, que é STRUCT<uri STRING, version STRING, authorizer STRING, details JSON>.

Para mais informações sobre como trabalhar com dados multimodais, consulte Analisar dados multimodais. Para um tutorial que mostra como trabalhar com dados ObjectRef, consulte Analisar dados multimodais com SQL. Para informações sobre como trabalhar com dados multimodais em Python, consulte Analisar dados multimodais em Python com DataFrames do BigQuery.

Pré-requisitos

Para preencher e atualizar valores ObjectRef em uma tabela padrão, ela precisa ter uma coluna STRING que contenha informações de URI para os objetos relacionados do Cloud Storage.

Você precisa ter um bucket do Cloud Storage que contenha os mesmos objetos identificados nos dados de URI da tabela padrão de destino. Se você quiser manter valores ObjectRef em uma tabela padrão usando uma tabela de objetos, também precisará ter uma tabela de objetos que represente os objetos nesse bucket.

Mantendo valores de ObjectRef

É possível usar uma tabela de objetos para preencher e atualizar valores ObjectRef em uma tabela padrão. Se você estiver na lista de permissão da prévia, todas as tabelas de objetos criadas terão uma coluna ref que contém um valor ObjectRef para o objeto em questão. É possível usar o URI do objeto para unir a tabela padrão à tabela de objetos e preencher e atualizar os valores de ObjectRef. Recomendamos essa abordagem para escalonabilidade, porque evita a necessidade de recuperar metadados de objetos do Cloud Storage.

Se você não quiser criar uma tabela de objetos, use as funções OBJ.FETCH_METADATA e OBJ.MAKE_REF para preencher e atualizar os valores de ObjectRef buscando os metadados de objetos diretamente do Cloud Storage. Essa abordagem pode ser menos escalonável, porque exige a recuperação de metadados de objetos do Cloud Storage.

Criar uma coluna de ObjectRef

Para criar e preencher uma coluna ObjectRef em uma tabela padrão, selecione uma das seguintes opções:

Tabela de objetos

Crie e preencha uma coluna ObjectRef com base nos dados de uma coluna ref da tabela de objetos:

  1. No console Trusted Cloud , acesse a página BigQuery.

    Acessar o BigQuery

  2. No editor de consultas, digite a seguinte instrução:

    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;

    Substitua:

    • PROJECT_ID: o ID do projeto. É possível pular esse argumento se você estiver criando a tabela no projeto atual.
    • DATASET_ID: o ID do conjunto de dados que você está criando.
    • TABLE_NAME: o nome da tabela padrão que você está recriando.
    • OBJECT_TABLE: o nome da tabela de objetos que contém os dados de objetos que você quer integrar à tabela padrão.

  3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

funções SQL;

Crie e preencha uma coluna ObjectRef com base na saída das funções OBJ.FETCH_METADATA e OBJ.MAKE_REF:

  1. No console Trusted Cloud , acesse a página BigQuery.

    Acessar o BigQuery

  2. No editor de consultas, digite a seguinte instrução:

    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;

    Substitua:

    • PROJECT_ID: o ID do projeto. É possível pular esse argumento se você estiver criando a tabela no projeto atual.
    • DATASET_ID: o ID do conjunto de dados que você está criando.
    • TABLE_NAME: o nome da tabela padrão que você está recriando.
    • CONNECTION_ID: um valor STRING que contém uma conexão de recurso de nuvem que o serviço pode usar para acessar os objetos no Cloud Storage, no formato location.connection_id. Exemplo: us-west1.myconnection. Para conseguir o ID da conexão, confira os detalhes da conexão no console do Trusted Cloud e copie o valor na última seção do ID da conexão totalmente qualificado, mostrado em ID da conexão. Por exemplo, projects/myproject/locations/connection_location/connections/myconnection.

      Conceda a função de usuário de objetos do Storage (roles/storage.objectUser) à conta de serviço da conexão em qualquer bucket do Cloud Storage em que você a esteja usando para acessar objetos.

      A conexão precisa estar no mesmo projeto e região da consulta em que você está chamando a função.

  3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

Criar uma coluna de ARRAY<ObjectRef>

É possível criar uma coluna ARRAY<STRUCT<uri STRING, version STRING, authorizer STRING, details JSON>> para conter matrizes de valores ObjectRef. Por exemplo, é possível dividir um vídeo em imagens separadas e armazenar essas imagens como uma matriz de valores ObjectRef.

É possível usar a ARRAY_AGG para agregar matrizes de valores ObjectRef, incluindo o uso da cláusula ORDER BY para preservar a ordem dos objetos, se necessário. É possível usar o operador UNNEST para analisar uma matriz de valores ObjectRef em valores ObjectRef individuais, incluindo o uso da cláusula WITH OFFSET para preservar a ordem dos objetos, se necessário. É possível usar metadados de objetos, como o caminho do URI e o nome do arquivo do objeto, para mapear valores ObjectRef que representam partes do objeto para um valor ObjectRef que representa o objeto original.

Para ver um exemplo de como trabalhar com matrizes de valores ObjectRef, consulte a seção Processar dados multimodais ordenados usando valores ARRAY<ObjectRef> do tutorial Analisar dados multimodais com SQL.

Atualizar uma coluna ObjectRef

Para atualizar uma coluna ObjectRef em uma tabela padrão, selecione uma das seguintes opções:

Tabela de objetos

Atualize uma coluna ObjectRef usando dados de uma coluna ref da tabela de objetos:

  1. No console Trusted Cloud , acesse a página BigQuery.

    Acessar o BigQuery

  2. No editor de consultas, digite a seguinte instrução:

    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 != "";

    Substitua:

    • PROJECT_ID: o ID do projeto. É possível pular esse argumento se você estiver criando a tabela no projeto atual.
    • DATASET_ID: o ID do conjunto de dados que você está criando.
    • TABLE_NAME: o nome da tabela padrão que você está recriando.
    • OBJECT_TABLE: o nome da tabela de objetos que contém os mesmos dados de objeto da coluna ObjectRef da tabela padrão.

  3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

funções SQL;

Atualize uma coluna ObjectRef usando a saída das funções OBJ.FETCH_METADATA e OBJ.MAKE_REF:

  1. No console Trusted Cloud , acesse a página BigQuery.

    Acessar o BigQuery

  2. No editor de consultas, digite a seguinte instrução:

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

    Substitua:

    • PROJECT_ID: o ID do projeto. É possível pular esse argumento se você estiver criando a tabela no projeto atual.
    • DATASET_ID: o ID do conjunto de dados que você está criando.
    • TABLE_NAME: o nome da tabela padrão que você está recriando.
    • CONNECTION_ID: um valor STRING que contém uma conexão de recurso de nuvem que o serviço pode usar para acessar os objetos no Cloud Storage, no formato location.connection_id. Exemplo: us-west1.myconnection. Para conseguir o ID da conexão, confira os detalhes da conexão no console do Trusted Cloud e copie o valor na última seção do ID da conexão totalmente qualificado, mostrado em ID da conexão. Por exemplo, projects/myproject/locations/connection_location/connections/myconnection.

      Conceda a função de usuário de objetos do Storage (roles/storage.objectUser) à conta de serviço da conexão em qualquer bucket do Cloud Storage em que você a esteja usando para acessar objetos.

      A conexão precisa estar no mesmo projeto e região da consulta em que você está chamando a função.

  3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

A seguir