如何实现Google Analytics数据自动抽取至Oracle APEX数据库

一、核心认证方案：服务账号认证 #

要实现无人工交互，**服务账号（Service Account）**是唯一可行的认证方式，步骤如下：

• 登录Google Cloud控制台，创建/选择目标项目，启用「Google Analytics Data API v1beta」
• 创建服务账号：进入「IAM与管理员」→「服务账号」→「创建服务账号」，完成后进入该账号的「密钥」页面，创建并下载JSON格式的密钥文件（务必妥善保存）
• 授权服务账号访问GA资源：登录GA4后台，进入对应属性的「管理员」→「账号访问管理」，添加服务账号邮箱，分配「查看者」或「分析师」权限

二、Oracle APEX自动化流程实现 #

1. 安全存储服务账号密钥 #

• 在APEX中创建静态应用文件，上传下载的JSON密钥；或把密钥内容存储在加密的APEX应用项/数据库加密列中，绝对禁止硬编码在代码里

2. 自动获取访问令牌 #

借助APEX的工具包生成JWT断言并请求令牌，示例PL/SQL代码：

DECLARE
  l_key_content CLOB;
  l_jwt_assertion VARCHAR2(4000);
  l_response CLOB;
  l_access_token VARCHAR2(4000);
BEGIN
  -- 读取存储的服务账号密钥内容
  l_key_content := APEX_APPLICATION_FILENAMES.get_file_content(p_file_name => 'service-account-key.json');
  
  -- 用APEX_JWT生成断言（APEX 21+版本支持）
  l_jwt_assertion := APEX_JWT.generate_token(
    p_key_content => l_key_content,
    p_iss => APEX_JSON.get_varchar2(l_key_content, 'client_email'),
    p_sub => APEX_JSON.get_varchar2(l_key_content, 'client_email'),
    p_aud => 'https://oauth2.googleapis.com/token',
    p_exp => SYSTIMESTAMP + INTERVAL '1' HOUR
  );

  -- 请求访问令牌
  l_response := APEX_WEB_SERVICE.make_rest_request(
    p_url => 'https://oauth2.googleapis.com/token',
    p_http_method => 'POST',
    p_body => 'grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer&assertion=' || l_jwt_assertion
  );

  -- 解析获取令牌
  l_access_token := APEX_JSON.get_varchar2(l_response, 'access_token');
  :P1_ACCESS_TOKEN := l_access_token; -- 存储到应用项供后续使用
END;

3. 调用GA4 API抽取并写入数据库 #

用获取到的令牌调用RunReport API，解析响应后插入目标表：

DECLARE
  l_request_body CLOB;
  l_response CLOB;
BEGIN
  -- 构造GA查询请求体，替换为你的属性ID和查询维度/指标
  l_request_body := '{
    "property": "properties/123456789",
    "dateRanges": [{"startDate": "7daysAgo", "endDate": "today"}],
    "dimensions": [{"name": "city"}],
    "metrics": [{"name": "activeUsers"}]
  }';

  -- 调用API
  l_response := APEX_WEB_SERVICE.make_rest_request(
    p_url => 'https://analyticsdata.googleapis.com/v1beta/properties/123456789:runReport',
    p_http_method => 'POST',
    p_body => l_request_body,
    p_headers => apex_web_service.parse_headers('Authorization: Bearer ' || :P1_ACCESS_TOKEN)
  );

  -- 解析数据并插入表
  FOR i IN 1..APEX_JSON.get_count(l_response, 'rows') LOOP
    INSERT INTO GA_DATA (CITY, ACTIVE_USERS, REPORT_DATE)
    VALUES (
      APEX_JSON.get_varchar2(l_response, 'rows[' || i || '].dimensionValues[0].value'),
      TO_NUMBER(APEX_JSON.get_varchar2(l_response, 'rows[' || i || '].metricValues[0].value')),
      SYSDATE
    );
  END LOOP;
  COMMIT;
END;

4. 设置自动化调度 #

• 进入APEX应用的「自动化」模块，创建新自动化
• 触发条件选择「按计划」，设置执行频率（如每日凌晨1点）
• 将上述获取令牌+抽取数据的PL/SQL代码作为执行步骤
• 可选配置失败告警，确保异常情况能及时知晓

三、关键注意事项 #

• 令牌有效期为1小时，每次执行作业时需重新获取，避免过期失效
• 确保Oracle服务器能访问Google的API端点，需开放出站防火墙权限
• 测试阶段先手动执行代码验证流程，再启用自动调度

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