GCP BigQuery是否有货币转换功能？迁移SAP HANA存储过程求助

1. 使用BigQuery原生CURRENCY_CONVERT函数 #

BigQuery内置的CURRENCY_CONVERT函数可直接实现基于历史日期的货币转换，默认采用Google Finance的公开汇率数据，语法与HANA的CONVERT_CURRENCY逻辑对齐：

CURRENCY_CONVERT(
  amount,
  from_currency_code,
  to_currency_code,
  date
)

示例：将2023年10月15日的100欧元转换为美元

SELECT CURRENCY_CONVERT(100, 'EUR', 'USD', DATE('2023-10-15')) AS converted_amount;

注意点：

• 货币代码必须遵循ISO 4217标准（如'EUR'、'GBP'）
• 该函数依赖公开汇率数据源，若业务需使用企业内部定制汇率，需改用自定义汇率表方案

2. 基于自定义汇率表实现（企业级场景） #

如果依赖内部维护的汇率数据，可通过以下步骤实现：

第一步：创建汇率表 #

CREATE OR REPLACE TABLE `your-project.your-dataset.currency_rates` (
  from_currency STRING,
  to_currency STRING,
  rate_date DATE,
  exchange_rate FLOAT64,
  PRIMARY KEY(from_currency, to_currency, rate_date)
);

第二步：编写历史汇率匹配逻辑 #

支持两种匹配规则：严格匹配指定日期，或指定日期无数据时取最近有效汇率：

WITH source_data AS (
  SELECT 
    transaction_amount AS amount,
    currency_code AS from_currency,
    transaction_date AS target_date
  FROM `your-project.your-dataset.transaction_records`
)
SELECT
  sd.amount,
  sd.from_currency,
  sd.target_date,
  sd.amount * cr.exchange_rate AS usd_amount
FROM source_data sd
JOIN `your-project.your-dataset.currency_rates` cr
ON cr.from_currency = sd.from_currency
AND cr.to_currency = 'USD'
AND cr.rate_date = (
  SELECT MAX(rate_date) 
  FROM `your-project.your-dataset.currency_rates`
  WHERE from_currency = sd.from_currency
    AND to_currency = 'USD'
    AND rate_date <= sd.target_date
);

3. 迁移存储过程至BigQuery #

将HANA存储过程转为BigQuery存储过程的示例：

CREATE OR REPLACE PROCEDURE `your-project.your-dataset.convert_to_usd`(
  IN source_table STRING,
  IN target_table STRING,
  IN amount_col STRING,
  IN currency_col STRING,
  IN date_col STRING
)
BEGIN
  EXECUTE IMMEDIATE FORMAT("""
    CREATE OR REPLACE TABLE `%s` AS
    SELECT
      *,
      CURRENCY_CONVERT(%s, %s, 'USD', %s) AS usd_amount
    FROM `%s`
  """, target_table, amount_col, currency_col, date_col, source_table);
END;

调用存储过程：

CALL `your-project.your-dataset.convert_to_usd`(
  'your-project.your-dataset.transaction_records',
  'your-project.your-dataset.usd_converted_records',
  'transaction_amount',
  'currency_code',
  'transaction_date'
);

关键注意事项 #

• 使用原生函数时，需确认Google Finance覆盖了你所需的货币种类和历史日期范围
• 自定义汇率表需定期同步数据，确保汇率的时效性和完整性
• 存储过程中使用动态SQL时，通过FORMAT函数处理参数可避免SQL注入风险

内容的提问来源于stack exchange，提问作者Atul Singh