Executar consultas federadas do Oracle

Como analista de dados, é possível consultar dados relacionais que residem no Oracle diretamente do BigQuery em tempo real, sem copiar ou mover dados.

A federação do BigQuery Oracle usa a API BigQuery Connection para se conectar a mecanismos de banco de dados Oracle. Em seguida, use a função EXTERNAL_QUERY para enviar sua consulta SQL diretamente ao Oracle para execução, e os resultados serão transmitidos diretamente de volta.

Limitações

As consultas federadas do Oracle estão sujeitas às seguintes limitações:

  • Só é possível executar consultas somente leitura usando a função EXTERNAL_QUERY. As instruções de linguagem de definição de dados (DDL) e de linguagem de manipulação de dados (DML) não são compatíveis.
  • A federação só pode ser acessada pela função EXTERNAL_QUERY. Não é possível criar tabelas externas do BigQuery ou conjuntos de dados permanentes que sejam mapeados diretamente para tabelas do Oracle.
  • A latência da consulta federada pode ser visivelmente maior do que a execução da consulta diretamente no Oracle.
  • O serviço de conexão subjacente pode hibernar conexões inativas. A primeira consulta que usa uma conexão do Oracle após um período de inatividade pode levar mais de um minuto para ser executada enquanto o conector é inicializado.
  • O peering de DNS não é compatível. É preciso se conectar usando um endereço IP ou um nome de host totalmente resolvível na rede VPC.
  • A consulta SQL do Oracle precisa especificar aliases de coluna explícitos para todas as colunas que contêm resultados ou expressões de função (por exemplo, SELECT COUNT(*) AS total_count). Expressões sem alias produzem nomes de coluna com caracteres especiais que são inválidos nas definições de esquema de tabela do BigQuery.
  • Quando o uso da API Compute Engine no projeto de consulta é restrito pelo VPC Service Controls, a consulta federada falha se a conexão usa um anexo de rede. Verifique se as regras de entrada e saída apropriadas do VPC Service Controls estão configuradas.

  • Não há suporte para pushdowns SQL adicionais no Oracle. Os filtros e as agregações na consulta externa do BigQuery não são enviados para a consulta interna na função EXTERNAL_QUERY. É preciso especificar predicados e projeções de coluna diretamente na instrução SQL interna do Oracle para limitar as linhas e colunas transferidas pela rede.

  • Somente a autenticação de nome de usuário e senha do banco de dados Oracle é compatível. Não há suporte para provedores de identidade de terceiros, Kerberos, RADIUS e autenticação do IAM da Oracle Cloud Infrastructure (OCI).

  • Tipos complexos ou proprietários do Oracle, como tipos definidos pelo usuário (UDTs), tipos espaciais (SDO_GEOMETRY), tipos de coleção (VARRAY, tabelas aninhadas), SYS.ANYDATA, REF, BFILE e colunas binárias JSON (OSON) não são compatíveis. Para consultar uma coluna JSON binária, serialize-a em texto na consulta (por exemplo, com JSON_SERIALIZE(column_name) ou JSON_QUERY).

  • As colunas NUMBER sem restrições e as expressões numéricas calculadas são mapeadas para STRING do BigQuery para preservar a precisão total sem truncamento ou estouro. As colunas NUMBER(p, s) restritas são mapeadas para NUMERIC (quando a precisão é menor ou igual a 38 e a escala é menor ou igual a 9) ou BIGNUMERIC (quando a escala é maior que 9 ou os dígitos inteiros são maiores que 29).

  • As colunas CHAR(n) e NCHAR(n) de tamanho fixo preservam o preenchimento de espaços em branco à direita até o tamanho n quando mapeadas para o STRING do BigQuery.

  • Datas e carimbos de data/hora anteriores a 15 de outubro de 1582 podem sofrer uma mudança de 10 dias no calendário devido à transição histórica do calendário gregoriano. Para preservar datas históricas exatas anteriores a 1582, converta-as em VARCHAR2 na instrução EXTERNAL_QUERY.

Ambientes e versões compatíveis do Oracle

A federação de consultas é compatível com bancos de dados Oracle nos seguintes ambientes de implantação:

  • Oracle Database@Google Cloud
  • Oracle Exadata Database Service
  • Instâncias locais do Oracle conectadas ao Google Cloud por meio do Cloud VPN, do Cloud Interconnect ou de um IP público pela Internet

A federação de consultas é compatível com as seguintes versões do Oracle:

  • Oracle Database 23.x (23ai)
  • Oracle Database 21.x
  • Oracle Database 19.x (19c)

Antes de começar

Verifique se o administrador do BigQuery criou uma conexão do Oracle e compartilhou com você.

Funções exigidas

Para receber as permissões necessárias para consultar o Oracle, peça ao administrador para conceder a você o papel do IAM de Usuário de conexão do BigQuery (roles/bigquery.connectionUser) no projeto. Para mais informações sobre a concessão de papéis, consulte Gerenciar o acesso a projetos, pastas e organizações.

Também é possível conseguir as permissões necessárias por meio de papéis personalizados ou de outros papéis predefinidos.

Consultar dados

Para enviar uma consulta federada ao Oracle de uma consulta do GoogleSQL, use a função EXTERNAL_QUERY.

O exemplo a seguir é uma consulta federada que mescla registros de clientes no BigQuery chamados mydataset.customers com registros de pedidos em tempo real de um banco de dados Oracle:

SELECT
  bq_cust.customer_id,
  bq_cust.customer_name,
  oracle_orders.order_id,
  oracle_orders.order_status,
  oracle_orders.order_total
FROM
  mydataset.customers AS bq_cust
JOIN
  EXTERNAL_QUERY(
    'projects/my-project/locations/us-central1/connections/oracle_prod_conn',
    '''SELECT customer_id, order_id, order_status, order_total
       FROM orders
       WHERE order_date >= SYSDATE - 1''') AS oracle_orders
  ON bq_cust.customer_id = oracle_orders.customer_id;

Ver um esquema de tabela do Oracle

É possível consultar as visualizações do dicionário de dados do Oracle usando EXTERNAL_QUERY para inspecionar metadados do banco de dados, descobrir tabelas e analisar esquemas de colunas antes de executar análises.

Listar todas as tabelas acessíveis

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT owner, table_name, tablespace_name, status
     FROM all_tables
     ORDER BY owner, table_name''');

Ver colunas e tipos de dados em uma tabela

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT column_name, data_type, data_length, data_precision, data_scale, nullable
     FROM all_tab_columns
     WHERE owner = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME'
     ORDER BY column_id''');

Mapeamento de tipo de dados

Quando você executa uma consulta federada, o BigQuery converte os dados do Oracle em tipos de dados do GoogleSQL de acordo com a seguinte tabela:

Tipo de dados Oracle Tipo de dados do BigQuery
Tipos numéricos
NUMBER(p, s) (p ≤ 38, s ≤ 9) NUMERIC
NUMBER(p, s) (s > 9 ou p > 38) BIGNUMERIC
NUMBER (escala sem restrições ou flutuante) STRING
FLOAT, FLOAT(p) FLOAT64
BINARY_FLOAT FLOAT64
BINARY_DOUBLE FLOAT64
Tipos de caracteres e strings
CHAR(n) STRING
NCHAR(n) STRING
VARCHAR2(n) STRING
NVARCHAR2(n) STRING
CLOB STRING
NCLOB STRING
LONG STRING
Tipos binários
RAW(n) BYTES
BLOB BYTES
LONG RAW BYTES
Tipos de data, hora e intervalo
DATE DATETIME
TIMESTAMP, TIMESTAMP(p) DATETIME
TIMESTAMP WITH TIME ZONE TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE TIMESTAMP
INTERVAL YEAR TO MONTH STRING
INTERVAL DAY TO SECOND STRING
Tipos de identificação de linha
ROWID STRING
UROWID STRING
Tipos semiestruturados, vetoriais e XML
JSON (textual / OSON com serialização de texto) STRING
VECTOR STRING
XMLTYPE STRING

Preços

O custo de executar uma consulta federada é baseado em três fatores:

  • O custo de computação da execução da consulta no Oracle (cobrado pelo provedor de banco de dados Oracle ou pelo ambiente de hospedagem)
  • O custo da largura de banda da transferência dos resultados da consulta do Oracle para o BigQuery (por exemplo, cobranças de rede de saída do provedor de hospedagem do banco de dados)
  • O custo de computação da execução da consulta no BigQuery.

Todos os custos relacionados à Oracle dependem do tipo de serviço e do ambiente de hospedagem que você usa. Para limitar o custo da largura de banda, recomendamos que você grave a consulta na instrução EXTERNAL_QUERY para que exclua todas as colunas e linhas que não sejam necessárias para calcular o resultado final.

Não há custo adicional para executar consultas federadas no BigQuery. Para mais informações sobre os preços do BigQuery, consulte Preços.

Se a conexão usar componentes de rede do Google Cloud , os seguintes preços também poderão ser aplicados:

A seguir