Configurar um destino do BigQuery

Nesta página, descrevemos como configurar o destino do BigQuery para fazer streaming de dados de um banco de dados de origem usando o Datastream.

Configurar os conjuntos de dados de destino

Ao configurar conjuntos de dados para o destino do BigQuery, selecione uma das seguintes opções:

• Conjunto de dados para cada esquema: o conjunto de dados é selecionado ou criado no local do BigQuery especificado, com base no nome do esquema da origem. Como resultado, para cada esquema na origem, o Datastream cria um conjunto de dados no BigQuery automaticamente.

  Se você selecionar essa opção, o Datastream criará conjuntos de dados no projeto que contém o stream.

  Por exemplo, se você tiver uma origem MySQL e ela tiver um banco de dados mydb e uma tabela employees no banco de dados, o Datastream vai criar o conjunto de dados mydb e a tabela employees no BigQuery.

• Conjunto de dados único para todos os esquemas: é possível selecionar um conjunto de dados do BigQuery para o stream. O Datastream transmite todos os dados para este conjunto. Para o conjunto de dados selecionado, o Datastream cria todas as tabelas como <schema>_<table>.

  Por exemplo, se você tiver uma origem MySQL e ela tiver um banco de dados mydb e uma tabela employees no banco de dados, o Datastream criará a tabela mydb_employees no conjunto de dados selecionado.

Comportamento de gravação

O Datastream anexa uma coluna STRUCT chamada datastream_metadata a cada tabela gravada no destino do BigQuery.

Se uma tabela tiver uma chave primária na fonte, a coluna conterá os seguintes campos:

• UUID: Este campo tem o tipo de dados string.
• SOURCE_TIMESTAMP: Este campo tem o tipo de dados integer.

Se uma tabela não tiver uma chave primária, a coluna conterá um campo adicional: IS_DELETED. Esse campo tem o tipo de dados boolean e indica se os dados que o Datastream transmite para o destino estão associados a uma operação DELETE na origem. Tabelas sem chaves primárias são somente para anexação.

O tamanho máximo do evento ao fazer streaming de dados no BigQuery é de 20 MB.

Configurar o modo de gravação

Ao configurar seu fluxo, é possível selecionar como o Datastream grava seus dados alterados no BigQuery:

• Mesclar: esse é o modo de gravação padrão. Quando selecionado, o BigQuery reflete a forma como seus dados são armazenados no banco de dados de origem. Isso significa que o Datastream grava todas as alterações dos seus dados no BigQuery, que consolida as alterações com os dados atuais, criando tabelas finais que são réplicas das tabelas de origem. Com o modo Merge, nenhum registro histórico dos eventos de alteração é mantido. Por exemplo, se você inserir e depois atualizar uma linha, o BigQuery vai manter apenas os dados atualizados. Se você excluir a linha da tabela de origem, o BigQuery não manterá mais nenhum registro dessa linha.
• Somente anexação: o modo de gravação somente anexa permite adicionar dados ao BigQuery como um fluxo de alterações (eventos INSERT, UPDATE-INSERT, UPDATE-DELETEe DELETE). Use esse modo quando precisar manter o estado histórico dos dados. Para entender melhor o modo de gravação somente de anexação, considere os seguintes cenários:
  • Preenchimento inicial: após o preenchimento inicial, todos os eventos são gravados no BigQuery como eventos do tipo INSERT, com o mesmo carimbo de data/hora, o identificador universal exclusivo (UUID) e o número de sequência de alterações.
  • Atualização da chave primária: quando uma chave primária é alterada, duas linhas são gravadas no BigQuery:
    • Uma linha UPDATE-DELETE com a chave primária original
    • Uma linha UPDATE-INSERT com a nova chave primária
  • Atualização da linha: quando você atualiza uma linha, uma única linha UPDATE-INSERT é gravada no BigQuery
  • Exclusão de linha: quando você exclui uma linha, uma única linha DELETE é gravada no BigQuery

Usar tabelas do BigQuery com a opção max_staleness

Como parte da ingestão quase em tempo real, o Datastream usa o suporte integrado do BigQuery para operações de inserção e atualização, como atualizar, inserir e excluir dados. As operações de inserção e atualização permitem atualizar dinamicamente o destino do BigQuery conforme as linhas são adicionadas, modificadas ou excluídas. O Datastream transmite essas operações de inserção para a tabela de destino usando a API Storage Write do BigQuery.

Especificar o limite de inatividade dos dados

O BigQuery aplica modificações da fonte em segundo plano de maneira contínua ou no ambiente de execução da consulta, de acordo com o limite de inatividade dos dados configurado. Quando o Datastream cria uma nova tabela no BigQuery, a opção max_staleness da tabela é definida de acordo com o valor do limite atual de inatividade dos dados do stream.

Para mais informações sobre como usar as tabelas do BigQuery com a opção max_staleness, consulte Inatividade da tabela.

Controlar os custos do BigQuery

Os custos do BigQuery são cobrados separadamente do Datastream. Para saber como controlar os custos do BigQuery, consulte Preços de CDC do BigQuery.

Tipos de dados do mapa

A tabela a seguir lista conversões de tipo de dados de bancos de dados de origem compatíveis para o destino do BigQuery.

Todas as origens MySQL Oracle PostgreSQL SQL Server

Banco de dados de origem	Tipo de dados de origem	Tipo de dados do BigQuery
MySQL	BIGINT(size)	INT64
MySQL	BIGINT (unsigned)	NUMERIC
MySQL	BINARY(size)	STRING (hex encoded)
MySQL	BIT(size)	INT64
MySQL	BLOB(size)	STRING (hex encoded)
MySQL	BOOL	INT64
MySQL	CHAR(size)	STRING
MySQL	DATE	DATE
MySQL	DATETIME(fsp)	DATETIME
MySQL	DECIMAL(precision, scale)	Se o valor de precisão for <=38 e o valor da escala for <=9, então NUMERIC. Caso contrário, BIGNUMERIC
MySQL	DOUBLE(size, d)	FLOAT64
MySQL	ENUM(val1, val2, val3, ...)	STRING
MySQL	FLOAT(precision)	FLOAT64
MySQL	FLOAT(size, d)	FLOAT64
MySQL	INTEGER(size)	INT64
MySQL	JSON	JSON Os tipos de dados BOOLEAN, NUMBER, OBJECT, STRING, ARRAY e NULL são compatíveis.
MySQL	LONGBLOB	STRING (hex encoded)
MySQL	LONGTEXT	STRING (hex encoded)
MySQL	MEDIUMBLOB	STRING (hex encoded)
MySQL	MEDIUMINT(size)	INT64
MySQL	MEDIUMTEXT	STRING
MySQL	SET(val1, val2, val3, ...)	STRING
MySQL	SMALLINT(size)	INT64
MySQL	TEXT(size)	STRING
MySQL	TIME(fsp)	INTERVAL
MySQL	TIMESTAMP(fsp)	TIMESTAMP
MySQL	TINYBLOB	STRING (hex encoded)
MySQL	TINYINT(size)	INT64
MySQL	TINYTEXT	STRING
MySQL	VARBINARY(size)	STRING (hex encoded)
MySQL	VARCHAR	STRING
MySQL	YEAR	INT64
Oracle	ANYDATA	UNSUPPORTED
Oracle	BFILE	STRING
Oracle	BINARY DOUBLE	FLOAT64
Oracle	BINARY FLOAT	FLOAT64
Oracle	BLOB	BYTES
Oracle	CHAR	STRING
Oracle	CLOB	STRING
Oracle	DATE	DATETIME Datas zero não são compatíveis e são substituídas por valores NULL.
Oracle	DOUBLE PRECISION	FLOAT64
Oracle	FLOAT(p)	FLOAT64
Oracle	INTERVAL DAY TO SECOND	UNSUPPORTED
Oracle	INTERVAL YEAR TO MONTH	UNSUPPORTED
Oracle	LONG/LONG RAW	STRING
Oracle	NCHAR	STRING
Oracle	NCLOB	STRING
Oracle	NUMBER	STRING
Oracle	NUMBER(precision=*)	STRING
Oracle	NUMBER(precision, scale<=0)	Se p<=18, então INT64. Se o valor for 18<p=<78, faça o mapeamento para tipos decimais parametrizados. Se p>=79, mapear para STRING
Oracle	NUMBER(precision, scale>0)	Se o valor for 0<p=<78, faça o mapeamento para tipos decimais parametrizados. Se p>=79, mapear para STRING
Oracle	NVARCHAR2	STRING
Oracle	RAW	STRING
Oracle	ROWID	STRING
Oracle	SDO_GEOMETRY	UNSUPPORTED
Oracle	SMALLINT	INT64
Oracle	TIMESTAMP	TIMESTAMP Datas zero não são compatíveis e são substituídas por valores NULL.
Oracle	TIMESTAMP WITH TIME ZONE	TIMESTAMP Datas zero não são compatíveis e são substituídas por valores NULL.
Oracle	UDT (user-defined type)	UNSUPPORTED
Oracle	UROWID	STRING
Oracle	VARCHAR	STRING
Oracle	VARCHAR2	STRING
Oracle	XMLTYPE	UNSUPPORTED
PostgreSQL	ARRAY	JSON Matrizes dos tipos de dados DATE, TIMESTAMP ou TIMESTAMP WITH TIME ZONE não são compatíveis.
PostgreSQL	BIGINT	INT64
PostgreSQL	BIT	BYTES
PostgreSQL	BIT_VARYING	BYTES
PostgreSQL	BOOLEAN	BOOLEAN
PostgreSQL	BOX	UNSUPPORTED
PostgreSQL	BYTEA	BYTES
PostgreSQL	CHARACTER	STRING
PostgreSQL	CHARACTER_VARYING	STRING
PostgreSQL	CIDR	STRING
PostgreSQL	CIRCLE	UNSUPPORTED
PostgreSQL	DATE	DATE
PostgreSQL	DOUBLE_PRECISION	FLOAT64
PostgreSQL	ENUM	STRING
PostgreSQL	INET	STRING
PostgreSQL	INTEGER	INT64
PostgreSQL	INTERVAL	INTERVAL
PostgreSQL	JSON	JSON
PostgreSQL	JSONB	JSON
PostgreSQL	LINE	UNSUPPORTED
PostgreSQL	LSEG	UNSUPPORTED
PostgreSQL	MACADDR	STRING
PostgreSQL	MONEY	FLOAT64
PostgreSQL	NUMERIC	Se a precisão for -1, então STRING. Os tipos de NUMERIC do BigQuery exigem uma precisão fixa. Caso contrário, BIGNUMERIC/NUMERIC. Para mais informações, consulte a seção Números de precisão arbitrária na documentação do PostgreSQL.
PostgreSQL	OID	INT64
PostgreSQL	PATH	UNSUPPORTED
PostgreSQL	POINT	UNSUPPORTED
PostgreSQL	POLYGON	UNSUPPORTED
PostgreSQL	REAL	FLOAT64
PostgreSQL	SMALLINT	INT64
PostgreSQL	SMALLSERIAL	INT64
PostgreSQL	SERIAL	INT64
PostgreSQL	TEXT	STRING
PostgreSQL	TIME	TIME
PostgreSQL	TIMESTAMP	TIMESTAMP
PostgreSQL	TIMESTAMP_WITH_TIMEZONE	TIMESTAMP
PostgreSQL	TIME_WITH_TIMEZONE	TIME
PostgreSQL	TSQUERY	STRING
PostgreSQL	TSVECTOR	STRING
PostgreSQL	TXID_SNAPSHOT	STRING
PostgreSQL	UUID	STRING
PostgreSQL	XML	STRING
SQL Server	BIGINT	INT64
SQL Server	BINARY	BYTES
SQL Server	BIT	BOOL
SQL Server	CHAR	STRING
SQL Server	DATE	DATE
SQL Server	DATETIME2	DATETIME
SQL Server	DATETIME	DATETIME
SQL Server	DATETIMEOFFSET	TIMESTAMP
SQL Server	DECIMAL	BIGNUMERIC
SQL Server	FLOAT	FLOAT64
SQL Server	IMAGE	BYTES
SQL Server	INT	INT64
SQL Server	MONEY	BIGNUMERIC
SQL Server	NCHAR	STRING
SQL Server	NTEXT	STRING
SQL Server	NUMERIC	BIGNUMERIC
SQL Server	NVARCHAR	STRING
SQL Server	NVARCHAR(MAX)	STRING
SQL Server	REAL	FLOAT64
SQL Server	SMALLDATETIME	DATETIME
SQL Server	SMALLINT	INT64
SQL Server	SMALLMONEY	NUMERIC
SQL Server	TEXT	STRING
SQL Server	TIME	TIME
SQL Server	TIMESTAMP/ROWVERSION	BYTES
SQL Server	TINYINT	INT64
SQL Server	UNIQUEIDENTIFIER	STRING
SQL Server	VARBINARY	BYTES
SQL Server	VARBINARY(MAX)	BYTES
SQL Server	VARCHAR	STRING
SQL Server	VARCHAR(MAX)	STRING
SQL Server	XML	STRING

Consultar uma matriz do PostgreSQL como um tipo de dados de matriz do BigQuery

Se você preferir consultar uma matriz do PostgreSQL como um tipo de dados ARRAY do BigQuery, converta os valores JSON em uma matriz do BigQuery usando a função JSON_VALUE_ARRAY do BigQuery:

  SELECT ARRAY(SELECT CAST(element AS TYPE) FROM UNNEST(JSON_VALUE_ARRAY(BQ_COLUMN_NAME,'$')) AS element) AS array_col
  

Substitua:

• TYPE: o tipo do BigQuery que corresponde ao tipo de elemento na matriz de origem do PostgreSQL. Por exemplo, se o tipo de origem for uma matriz de valores BIGINT, substitua TYPE por INT64.

  Para mais informações sobre como mapear os tipos de dados, consulte Tipos de dados do mapa.

• BQ_COLUMN_NAME: o nome da coluna relevante na tabela do BigQuery.

Há duas exceções na maneira como você converte os valores:

• Para matrizes de valores BIT, BIT_VARYING ou BYTEA na coluna de origem, execute a seguinte consulta:

  SELECT ARRAY(SELECT FROM_BASE64(element) FROM UNNEST(JSON_VALUE_ARRAY(BQ_COLUMN_NAME,'$')) AS element) AS array_of_bytes
  

• Para matrizes de valores JSON ou JSONB na coluna de origem, use a função JSON_QUERY_ARRAY:

  SELECT ARRAY(SELECT element FROM UNNEST(JSON_QUERY_ARRAY(BQ_COLUMN_NAME,'$')) AS element) AS array_of_jsons
  

Limitações conhecidas

Observação: esta seção descreve as limitações para o uso do Datastream com o BigQuery como destino. Além dessas limitações, você precisa se familiarizar com as limitações de captura de dados alterados do BigQuery. Para mais informações, consulte Fazer streaming de atualizações de tabelas com captura de dados alterados.

Limitações conhecidas para o uso do BigQuery como destino incluem:

• Só é possível replicar dados em um conjunto de dados do BigQuery que resida no mesmo projeto do Google Cloud que o fluxo do Datastream.

• Se uma tabela no banco de dados de origem tiver uma chave primária (por exemplo, o ID do usuário) e houver uma mudança na tabela para que a chave primária seja diferente (neste exemplo, a chave primária agora é o ID do usuário e a data), o Datastream não vai poder transmitir essa mudança para o destino.

  Para que o Datastream transmita a tabela para o destino com a chave primária modificada, siga estas etapas:

  1. Altere a chave primária da tabela do banco de dados de origem.
  2. Verifique a métrica de latência total do stream e aguarde pelo menos a latência atual para garantir que qualquer evento em andamento seja gravado no destino. Isso permite que todos os eventos com a chave primária original sejam transmitidos com sucesso.
  3. Pause o stream.
  4. Solte a tabela no destino.
  5. Retome o stream.
  6. Acione o preenchimento na tabela.
  Ainda pode haver chegadas tardias de eventos gravados no destino do BigQuery. No entanto, preencher a tabela corrige as linhas que podem estar fora de sincronia.

• As chaves primárias no BigQuery precisam ser dos seguintes tipos de dados:

  • DATE
  • BOOL
  • GEOGRAPHY
  • INT64
  • NUMERIC
  • BIGNUMERIC
  • STRING
  • TIMESTAMP
  • DATETIME

  As tabelas com chaves primárias de tipos de dados sem suporte não são replicadas pelo Datastream.

• O BigQuery não é compatível com nomes de tabelas que tenham caracteres ., $, /, @ ou +. O Datastream substitui esses caracteres por sublinhados ao criar tabelas de destino.

  Por exemplo, table.name no banco de dados de origem se torna table_name no BigQuery.

  Para mais informações sobre nomes de tabelas no BigQuery, consulte Nomenclatura de tabelas.

• O BigQuery não aceita mais de quatro agrupamentos de colunas em cluster. Ao replicar uma tabela com mais de quatro colunas de chave primária, o Datastream usa quatro colunas de chave primária como as colunas de clustering.
• O Datastream mapeia literais de data e hora fora do intervalo, como tipos de data infinita do PostgreSQL, para os seguintes valores:
  • DATE positivo em relação ao valor de 9999-12-31
  • DATE negativo ao valor de 0001-01-01
  • TIMESTAMP positivo em relação ao valor de 9999-12-31 23:59:59.999000 UTC
  • TIMESTAMP negativo ao valor de 0001-01-01 00:00:00 UTC
• O BigQuery não é compatível com tabelas de streaming que tenham chaves primárias dos tipos de dados FLOAT ou REAL. Essas tabelas não são replicadas.

Para saber mais sobre os tipos de data e períodos do BigQuery, consulte Tipos de dados.