Especificar colunas ObjectRef em esquemas de tabelas

As colunas ObjectRef permitem integrar dados não estruturados a uma tabela padrão do BigQuery armazenando metadados e informações de conexão para objetos no Cloud Storage. Por exemplo, em uma tabela de produtos, é possível armazenar cada imagem de produto na mesma linha que o restante das informações do produto.

Neste documento, descrevemos como definir um esquema de tabela com colunas STRUCT que usam o formato ObjectRef. Para mais informações sobre como trabalhar com dados multimodais, consulte Analisar dados multimodais e o tutorial Analisar dados multimodais com SQL.

Pré-requisitos

Para preencher e atualizar os valores de 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.

Como manter os valores de ObjectRef

Todas as tabelas de objetos criadas têm uma coluna ref que contém um valor ObjectRef para o objeto em questão. Se você tiver uma tabela de objetos, poderá mesclá-la com a tabela padrão na coluna de URI do objeto para preencher e atualizar os valores de ObjectRef. Isso é mais eficiente porque evita buscar novamente os metadados do Cloud Storage para criar um novo valor ObjectRef.

Da mesma forma, se você já tiver um conjunto de dados do Storage Insights para metadados de objeto, poderá usar a coluna ref.uri ou selfLink para mesclar a tabela padrão com o conjunto de dados do Storage Insights para preencher e atualizar os valores de ObjectRef. Os valores ObjectRef criados em conjuntos de dados do Storage Insights não têm um autorizador. Para consultar esses objetos, é necessário ter acesso direto ao objeto ou adicionar um autorizador ao ObjectRef para usar o acesso delegado.

Se você não tiver uma tabela de objetos ou um conjunto de dados do Storage Insights, use a função OBJ.MAKE_REF para preencher e atualizar os valores de ObjectRef buscando metadados de objetos diretamente do Cloud Storage. Essa abordagem pode ser menos escalonável, porque requer a recuperação de metadados de objetos do Cloud Storage.

Criar uma coluna 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 criar e preencher uma coluna ObjectRef em uma tabela padrão, selecione uma das seguintes opções:

funções SQL;

Crie e preencha uma coluna ObjectRef com base na saída da função OBJ.MAKE_REF:

  1. No console do Cloud de Confiance , 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.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 a recursos do Cloud 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 Cloud de Confiance 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.

      É necessário conceder o papel 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ê o esteja usando para acessar objetos.

      A conexão precisa estar no mesmo projeto e região que a 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.

Tabela de objetos

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

  1. No console do Cloud de Confiance , 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 objeto que você quer integrar à tabela padrão.

  3. Clique em Executar.

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

Criar uma coluna 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, em seguida, armazenar essas imagens como uma matriz de valores ObjectRef.

É possível usar a função 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, inclusive usando a cláusula WITH OFFSET para preservar a ordem dos objetos, se necessário. É possível usar metadados de objeto, como o caminho do URI e o nome do arquivo do objeto, para mapear valores ObjectRef que representam blocos de 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 de tabela de objetos:

  1. No console do Cloud de Confiance , 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 que a 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 do Cloud de Confiance , 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.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 a recursos do Cloud 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 Cloud de Confiance 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.

      É necessário conceder o papel 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ê o esteja usando para acessar objetos.

      A conexão precisa estar no mesmo projeto e região que a 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