Go to Qiita Advent Calendar Top
LoginSignup
4
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Day 22

Oracle Cloud Infrastructure Advent Calendar 2025

@fish_on_cloud

SQLだけでOCIコスト集計：Usage API → ADW取り込み手順

Posted at

OCIのコスト（Usage API）を、Oracle Autonomous Data Warehouse（ADW）に取り込み、
SQLで分析できる形にする手順をまとめます。
ポイントは DBMS_CLOUD_OCI_UA_USAGEAPI をPL/SQLから呼び出し → 取得結果をMERGEで蓄積 することです。

背景

  • コストをコンソールで見るだけでは、部門別・サービス別・時系列などの自由な分析が難しい
  • ADWに溜めれば、SQLで集計・可視化・レポート化・スケジューリングがしやすい
  • 今回は Usage API（Summarized Usages） を使って、Cost（課金額） を日次粒度で取り込みます

全体像（やること）

  1. （ADMIN）必要なパッケージ/型の GRANT EXECUTE
  2. （ADMIN）外部HTTPアクセスのためのACL設定
  3. （APPユーザー）DBMS_CLOUD.CREATE_CREDENTIAL でOCI署名認証用クレデンシャル作成
  4. 取り込み先テーブル作成（UA_USAGE_COST_ITEM）
  5. 取り込み用Procedure作成（UA_LOAD_USAGE_COST_ITEMS）
  6. 動作テスト → データ確認

前提

  • ADW上で実行（ADMIN ユーザー/ アプリユーザー(AIDEMO)）
  • Usage API を叩けるOCI認証情報（user_ocid / tenancy_ocid / private_key / fingerprint）
  • 利用するリージョン（例：ap-tokyo-1 ※Home Region推奨）

1. （ADMIN）EXECUTE権限の付与

GRANT EXECUTE ON DBMS_CLOUD_OCI_UA_USAGEAPI TO AIDEMO;
GRANT EXECUTE ON DBMS_CLOUD_OCI_UA_USAGEAPI_REQUEST_SUMMARIZED_CONFIGURATIONS_RESPONSE_T TO AIDEMO;
GRANT EXECUTE ON DBMS_CLOUD_OCI_UA_USAGEAPI_REQUEST_SUMMARIZED_USAGES_RESPONSE_T TO AIDEMO;
GRANT EXECUTE ON DBMS_CLOUD_OCI_USAGEAPI_REQUEST_SUMMARIZED_USAGES_DETAILS_T TO AIDEMO;
GRANT EXECUTE ON DBMS_CLOUD_OCI_USAGEAPI_VARCHAR2_TBL TO AIDEMO;
GRANT EXECUTE ON DBMS_CLOUD TO AIDEMO;

2. （ADMIN）ACL設定（HTTPアクセス許可）

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => '*',
    ace  => xs$ace_type(
              privilege_list => xs$name_list('http'),
              principal_name => 'AIDEMO',
              principal_type => xs_acl.ptype_db
            )
  );
END;
/

3. APPユーザー（AIDEMO）OCI署名クレデンシャル作成（DBMS_CLOUD.CREATE_CREDENTIAL）

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => '<your_credential_name>',
    user_ocid       => '<ocid1.user...>',
    tenancy_ocid    => '<ocid1.tenancy...>',
    private_key     => '-----BEGIN PRIVATE KEY-----
<your_private_key>
-----END PRIVATE KEY-----',
    fingerprint     => '<your_fingerprint>'
  );
END;
/

4. 取り込み先テーブル作成（UA_USAGE_COST_ITEM）

CREATE TABLE UA_USAGE_COST_ITEM (
  COST_ID NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  CREATED_AT TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL,

  TENANT_ID VARCHAR2(200),
  TENANT_NAME VARCHAR2(400),

  COMPARTMENT_ID VARCHAR2(200),
  COMPARTMENT_PATH VARCHAR2(1000),
  COMPARTMENT_NAME VARCHAR2(400),

  OCI_SERVICE VARCHAR2(200),
  RESOURCE_NAME VARCHAR2(1000),
  RESOURCE_ID VARCHAR2(200),

  L_REGION VARCHAR2(100),
  AD VARCHAR2(100),

  WEIGHT VARCHAR2(100),
  SHAPE VARCHAR2(200),

  SKU_PART_NUMBER VARCHAR2(200),
  SKU_NAME VARCHAR2(1000),
  UNIT VARCHAR2(200),

  DISCOUNT NUMBER,
  LIST_RATE NUMBER,
  PLATFORM VARCHAR2(200),

  TIME_USAGE_STARTED TIMESTAMP(6) WITH TIME ZONE,
  TIME_USAGE_ENDED   TIMESTAMP(6) WITH TIME ZONE,
  TIME_USAGE_STARTED_UTC TIMESTAMP(6),
  TIME_USAGE_ENDED_UTC   TIMESTAMP(6),

  COMPUTED_AMOUNT NUMBER,
  COMPUTED_QUANTITY NUMBER,

  OVERAGES_FLAG VARCHAR2(20),
  UNIT_PRICE NUMBER,
  CURRENCY VARCHAR2(10),

  SUBSCRIPTION_ID VARCHAR2(200),
  OVERAGE VARCHAR2(20),
  IS_FORECAST NUMBER(1,0),

  PRIMARY KEY (COST_ID)
);

5. 取り込みProcedure（Usage API → MERGEで蓄積）

  • Usage API から request_summarized_usages を呼び出す
  • group_by を指定して tenant/service/compartment/region 単位で集計
  • 取得結果を UA_USAGE_COST_ITEM に MERGE（再実行しても二重取り込みしない想定）
CREATE OR REPLACE PROCEDURE UA_LOAD_USAGE_COST_ITEMS (
  p_tenant_ocid     IN VARCHAR2,
  p_start_utc       IN TIMESTAMP,  -- UTC 00:00:00
  p_end_utc         IN TIMESTAMP,  -- UTC 00:00:00（排他的）
  p_region          IN VARCHAR2,
  p_credential_name IN VARCHAR2
)
IS
  l_details  C##CLOUD$SERVICE.dbms_cloud_oci_usageapi_request_summarized_usages_details_t;
  l_resp     C##CLOUD$SERVICE.dbms_cloud_oci_ua_usageapi_request_summarized_usages_response_t;
BEGIN
  l_details := C##CLOUD$SERVICE.dbms_cloud_oci_usageapi_request_summarized_usages_details_t(
    tenant_id            => p_tenant_ocid,
    time_usage_started   => FROM_TZ(p_start_utc, 'UTC'),
    time_usage_ended     => FROM_TZ(p_end_utc,   'UTC'),
    granularity          => 'DAILY',
    is_aggregate_by_time => 0,
    forecast             => NULL,
    query_type           => 'COST',
    group_by             => NULL,
    group_by_tag         => NULL,
    compartment_depth    => 7,
    filter               => NULL
  );

  -- どの次元で集計するか（ここが重要）
  l_details.group_by :=
    C##CLOUD$SERVICE.DBMS_CLOUD_OCI_USAGEAPI_VARCHAR2_TBL(
      'tenantId','service','compartmentId','region'
    );

  l_resp := C##CLOUD$SERVICE.dbms_cloud_oci_ua_usageapi.request_summarized_usages(
    request_summarized_usages_details => l_details,
    region          => p_region,
    credential_name => p_credential_name
  );

  -- 200系以外はエラーにして止める（安全）
  IF l_resp.status_code < 200 OR l_resp.status_code >= 300 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Usage API failed. HTTP status=' || l_resp.status_code);
  END IF;

  MERGE INTO UA_USAGE_COST_ITEM t
  USING (
    SELECT
      i.tenant_id         AS tenant_id,
      i.compartment_id    AS compartment_id,
      i.l_region          AS l_region,
      i.service           AS oci_service,
      i.time_usage_started AS time_usage_started,
      SYS_EXTRACT_UTC(i.time_usage_started) AS time_usage_started_utc,
      SYS_EXTRACT_UTC(i.time_usage_ended)   AS time_usage_ended_utc,

      -- 更新対象
      i.tenant_name       AS tenant_name,
      i.compartment_path  AS compartment_path,
      i.compartment_name  AS compartment_name,
      i.resource_name     AS resource_name,
      i.resource_id       AS resource_id,
      i.ad                AS ad,
      i.weight            AS weight,
      i.shape             AS shape,
      i.sku_part_number   AS sku_part_number,
      i.sku_name          AS sku_name,
      i.unit              AS unit,
      i.discount          AS discount,
      i.list_rate         AS list_rate,
      i.platform          AS platform,
      i.time_usage_ended  AS time_usage_ended,
      i.computed_amount   AS computed_amount,
      i.computed_quantity AS computed_quantity,
      i.overages_flag     AS overages_flag,
      i.unit_price        AS unit_price,
      i.currency          AS currency,
      i.subscription_id   AS subscription_id,
      i.overage           AS overage,
      i.is_forecast       AS is_forecast
    FROM TABLE(l_resp.response_body.items) i
  ) s
  ON (
    t.tenant_id = s.tenant_id
    AND t.compartment_id = s.compartment_id
    AND t.l_region = s.l_region
    AND t.oci_service = s.oci_service
    AND t.time_usage_started_utc = s.time_usage_started_utc
    AND t.time_usage_ended_utc   = s.time_usage_ended_utc
  )
  WHEN MATCHED THEN UPDATE SET
    t.tenant_name       = s.tenant_name,
    t.compartment_path  = s.compartment_path,
    t.compartment_name  = s.compartment_name,
    t.resource_name     = s.resource_name,
    t.resource_id       = s.resource_id,
    t.ad                = s.ad,
    t.weight            = s.weight,
    t.shape             = s.shape,
    t.sku_part_number   = s.sku_part_number,
    t.sku_name          = s.sku_name,
    t.unit              = s.unit,
    t.discount          = s.discount,
    t.list_rate         = s.list_rate,
    t.platform          = s.platform,
    t.time_usage_started= s.time_usage_started,
    t.time_usage_ended  = s.time_usage_ended,
    t.computed_amount   = s.computed_amount,
    t.computed_quantity = s.computed_quantity,
    t.overages_flag     = s.overages_flag,
    t.unit_price        = s.unit_price,
    t.currency          = s.currency,
    t.subscription_id   = s.subscription_id,
    t.overage           = s.overage,
    t.is_forecast       = s.is_forecast
  WHEN NOT MATCHED THEN INSERT (
    TENANT_ID, TENANT_NAME,
    COMPARTMENT_ID, COMPARTMENT_PATH, COMPARTMENT_NAME,
    L_REGION, OCI_SERVICE,
    TIME_USAGE_STARTED, TIME_USAGE_ENDED,
    TIME_USAGE_STARTED_UTC, TIME_USAGE_ENDED_UTC,
    RESOURCE_NAME, RESOURCE_ID, AD,
    WEIGHT, SHAPE, SKU_PART_NUMBER, SKU_NAME, UNIT,
    DISCOUNT, LIST_RATE, PLATFORM,
    COMPUTED_AMOUNT, COMPUTED_QUANTITY,
    OVERAGES_FLAG, UNIT_PRICE, CURRENCY,
    SUBSCRIPTION_ID, OVERAGE, IS_FORECAST
  ) VALUES (
    s.tenant_id, s.tenant_name,
    s.compartment_id, s.compartment_path, s.compartment_name,
    s.l_region, s.oci_service,
    s.time_usage_started, s.time_usage_ended,
    s.time_usage_started_utc, s.time_usage_ended_utc,
    s.resource_name, s.resource_id, s.ad,
    s.weight, s.shape, s.sku_part_number, s.sku_name, s.unit,
    s.discount, s.list_rate, s.platform,
    s.computed_amount, s.computed_quantity,
    s.overages_flag, s.unit_price, s.currency,
    s.subscription_id, s.overage, s.is_forecast
  );

  COMMIT;
END;
/

6. 動作テスト

BEGIN
  UA_LOAD_USAGE_COST_ITEMS(
    p_tenant_ocid => '<ocid1.tenancy...>',
    p_start_utc   => TIMESTAMP '2025-12-01 00:00:00',
    p_end_utc     => TIMESTAMP '2026-01-01 00:00:00',
    p_region      => 'ap-tokyo-1',
    p_credential_name => '<your_credential_name>'
  );
END;
/

7. データ確認（例）

日別×サービス×コンパートメントのコスト

SELECT
  time_usage_started_utc,
  oci_service,
  compartment_id,
  SUM(computed_amount) AS amount_jpy
FROM ua_usage_cost_item
GROUP BY time_usage_started_utc, oci_service, compartment_id
ORDER BY time_usage_started_utc, amount_jpy DESC;

まとめ

  • DBMS_CLOUD_OCI_UA_USAGEAPI で Usage API のコストをPL/SQLから取得できる
  • group_by を指定すると、Service / Compartment / Region 単位に集計した形で取り込める
  • ADWへ蓄積しておけば、FinOpsの分析がSQLで高速に回せる
4
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?