Especificar colunas ObjectRef em esquemas de tabelas

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

Os valores 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 de produtos na mesma linha com o restante das informações do produto adicionando uma coluna com 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 conferir um tutorial que mostra como trabalhar com dados ObjectRef, consulte Analisar dados multimodais com SQL. Para saber como trabalhar com dados multimodais em Python, consulte Analisar dados multimodais em Python com DataFrames do BigQuery.

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 do Cloud Storage relacionados.

Você precisa ter um bucket do Cloud Storage que contenha os mesmos objetos identificados nos dados do URI da tabela padrão de destino. Se você quiser manter os 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.

Como manter os 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ões para a visualização, todas as tabelas de objetos criadas terão uma coluna ref que contém um valor ObjectRef para o objeto. É possível usar o URI do objeto para unir a tabela padrão à tabela de objetos para preencher e atualizar os valores de ObjectRef. Recomendamos essa abordagem para escalonabilidade, porque ela 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 extraindo 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 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. Você pode pular esse argumento se estiver criando a tabela no seu 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 do 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.

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. Você pode pular esse argumento se estiver criando a tabela no seu 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 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 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.

      É necessário conceder o papel de usuário do objeto de armazenamento (roles/storage.objectUser) à conta de serviço da conexão em qualquer bucket do Cloud Storage em que você esteja usando-a 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.

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, você pode dividir um vídeo em imagens separadas e 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 objetos, como o caminho do URI e o nome de arquivo do objeto, para mapear valores ObjectRef que representam fragmentos de objetos para um valor ObjectRef que represente o objeto original.

Para conferir 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. Você pode pular esse argumento se estiver criando a tabela no seu 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 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. Você pode pular esse argumento se estiver criando a tabela no seu 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 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 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.

      É necessário conceder o papel de usuário do objeto de armazenamento (roles/storage.objectUser) à conta de serviço da conexão em qualquer bucket do Cloud Storage em que você esteja usando-a 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