运行 Oracle 联合查询

作为数据分析师，您可以直接从 BigQuery 实时查询位于 Oracle 中的关系型数据，而无需复制或移动数据。

BigQuery Oracle 联合查询使用 BigQuery Connection API 连接到 Oracle 数据库引擎。然后，您可以使用 EXTERNAL_QUERY 函数将 SQL 查询直接发送到 Oracle 执行，结果会直接流式传输回来。

限制

Oracle 联合查询存在以下限制：

  • 您只能通过 EXTERNAL_QUERY 函数执行只读查询。不支持数据定义语言 (DDL) 和数据操纵语言 (DML) 语句。
  • 只能通过 EXTERNAL_QUERY 函数访问联合。不支持创建直接映射到 Oracle 表的 BigQuery 外部表或永久数据集。
  • 联合查询延迟时间可能会明显长于直接在 Oracle 中执行查询。
  • 底层连接服务可能会使非活跃连接进入休眠状态。在一段时间不活动后，使用 Oracle 连接的第一个查询可能需要超过一分钟的时间才能运行，因为连接器需要初始化。
  • 不支持 DNS 对等互连。您必须使用 VPC 网络中的 IP 地址或完全可解析的主机名进行连接。
  • Oracle SQL 查询必须为包含函数结果或表达式（例如 SELECT COUNT(*) AS total_count）的任何列指定显式列别名。无别名的表达式会生成包含特殊字符的列名，这些字符在 BigQuery 表架构定义中无效。
  • 当查询项目中 Compute Engine API 的使用受 VPC Service Controls 的限制时，如果连接使用网络连接，则联合查询会失败。确保配置了适当的 VPC Service Controls 入站和出站规则。

  • Oracle 不支持其他 SQL 下推。外部 BigQuery 查询中的过滤条件和聚合不会下推到 EXTERNAL_QUERY 函数内的内部查询中。您必须直接在内部 Oracle SQL 语句中指定谓词和列投影，以限制通过网络传输的行和列。

  • 仅支持 Oracle 数据库用户名和密码身份验证。不支持第三方身份提供商、Kerberos、RADIUS 和 Oracle Cloud Infrastructure (OCI) IAM 身份验证。

  • 不支持复杂或专有的 Oracle 类型，例如用户定义的类型 (UDT)、空间类型 (SDO_GEOMETRY)、集合类型（VARRAY、嵌套表）、SYS.ANYDATA、REF、BFILE 和二进制 JSON (OSON) 列。如需查询二进制 JSON 列，请在查询中将其序列化为文本（例如，使用 JSON_SERIALIZE(column_name) 或 JSON_QUERY）。

  • 无约束的 NUMBER 列和计算出的数值表达式会映射到 BigQuery STRING，以保留完整精度，而不会发生截断或溢出。受限的 NUMBER(p, s) 列会映射到 NUMERIC（当精度小于或等于 38 且比例小于或等于 9 时）或 BIGNUMERIC（当比例大于 9 或整数位数大于 29 时）。

  • 映射到 BigQuery STRING 时，固定长度的 CHAR(n) 和 NCHAR(n) 列会保留尾随空格，直到长度达到 n。

  • 由于历史上的公历转换，1582 年 10 月 15 日之前的日期和时间戳可能会出现 10 天的日历偏移。如需保留 1582 年之前的确切历史日期，请在 EXTERNAL_QUERY 语句中将其转换为 VARCHAR2。

支持的 Oracle 环境和版本

联合查询功能支持以下部署环境中的 Oracle 数据库：

  • Oracle Database@Google Cloud
  • Oracle Exadata Database Service
  • 通过 Cloud VPN、Cloud Interconnect 或互联网上的公共 IP 连接到 Google Cloud 的本地 Oracle 实例

查询联合支持以下 Oracle 版本：

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

准备工作

确保您的 BigQuery 管理员已创建 Oracle 连接并与您共享。

所需的角色

如需获得查询 Oracle 所需的权限，请让管理员为您授予项目的 BigQuery Connection User (roles/bigquery.connectionUser) IAM 角色。如需详细了解如何授予角色，请参阅管理对项目、文件夹和组织的访问权限。

您也可以通过自定义角色或其他预定义角色来获取所需的权限。

查询数据

如需将联合查询从 GoogleSQL 查询发送到 Oracle，请使用 EXTERNAL_QUERY 函数。

以下示例是一个联合查询，该查询将 BigQuery 中名为 mydataset.customers 的客户记录与 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;

查看 Oracle 表架构

您可以通过 EXTERNAL_QUERY 查询 Oracle 数据字典视图，以便在运行分析之前检查数据库元数据、发现表并查看列架构。

列出所有可访问的表

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''');

查看表中的列和数据类型

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''');

数据类型映射

执行联合查询时，BigQuery 会根据下表将 Oracle 中的数据转换为 GoogleSQL 数据类型：

Oracle 数据类型 BigQuery 数据类型
数值类型
NUMBER(p, s) (p ≤ 38, s ≤ 9) NUMERIC
NUMBER(p, s)（s > 9 或 p > 38） BIGNUMERIC
NUMBER（无约束或浮动比例） STRING
FLOAT，FLOAT(p) FLOAT64
BINARY_FLOAT FLOAT64
BINARY_DOUBLE FLOAT64
字符和字符串类型
CHAR(n) STRING
NCHAR(n) STRING
VARCHAR2(n) STRING
NVARCHAR2(n) STRING
CLOB STRING
NCLOB STRING
LONG STRING
二进制类型
RAW(n) BYTES
BLOB BYTES
LONG RAW BYTES
日期、时间和时间间隔类型
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
行标识类型
ROWID STRING
UROWID STRING
半结构化、向量和 XML 类型
JSON（文本/OSON，采用文本序列化） STRING
VECTOR STRING
XMLTYPE STRING

价格

运行联合查询的费用取决于三个因素：

  • 在 Oracle 中执行查询的计算费用（由 Oracle 数据库提供商或托管环境收取）
  • 将查询结果从 Oracle 转移到 BigQuery 的带宽费用（数据库托管服务提供商收取的出站流量网络费用）
  • 在 BigQuery 中执行查询的计算费用

任何与 Oracle 相关的费用取决于您使用的 Oracle 服务和托管环境的类型。为了限制带宽费用，我们建议您在 EXTERNAL_QUERY 语句中编写查询，使其排除所有不需要计算最终结果的列和行。

在 BigQuery 中运行联合查询不会产生额外费用。如需详细了解 BigQuery 价格，请参阅价格。

如果您的连接使用 Google Cloud 网络组件，则可能还需要支付以下费用：

后续步骤