Load Azure Usage/Billing Data into Oracle

I need to download Azure usage/billing detail data into Oracle Table and build an APEX dashboard on top of it, here is what I did:

1. schedule a cronjob to run this script to get the Azure usage data file everyday, normally this will be on your database server:

import requests
import urllib2
import urllib
import csv
import os
import datetime

download_date=datetime.datetime.today().strftime('%Y%m')

ur='https://consumption.azure.com/v2/enrollments/your_enrollment_ID/usagedetails/download?'

mydict = {'billingPeriod': download_date}
url = ur + urllib.urlencode(mydict)

headers= {"Authorization":"Bearer XXXXXXXXX"}

billing_file='az.csv'
x = requests.get(url,headers=headers)
with open(os.path.join("/usr/users/your_dir", billing_file), 'wb') as f:
    f.write(x.content)

Replace XXXXX with your authorization key, which you can get from Azure.
Replace your_dir with your defined dir where you are going to download the csv file
Replace your_enrollment_ID with your own enrollment ID

2.  In Oracle DB, create a dir EXT_TAB_DATA pointing to the file dir you use for the dowload

3. create an Oracle External table similar to this:

CREATE TABLE  "AZURE_BILLING_EXTERNAL" 
   ( "ACCOUNTID" VARCHAR2(1000), 
 "ACCOUNTNAME" VARCHAR2(1000), 
 "ACCOUNTOWNEREMAIL" VARCHAR2(2000), 
 "ADDITIONALINFO" VARCHAR2(1000), 
 "CONSUMEDQUANTITY" VARCHAR2(1000), 
 "CONSUMEDSERVICE" VARCHAR2(2000), 
 "CONSUMEDSERVICEID" VARCHAR2(1000), 
 "COST" VARCHAR2(1000), 
 "COSTCENTER" VARCHAR2(1000), 
 "USE_DATE" VARCHAR2(200), 
 "DEPARTMENTID" VARCHAR2(1000), 
 "DEPARTMENTNAME" VARCHAR2(1000), 
 "INSTANCEID" VARCHAR2(3000), 
 "METERCATEGORY" VARCHAR2(2000), 
 "METERID" VARCHAR2(1000), 
 "METERNAME" VARCHAR2(1000), 
 "METERREGION" VARCHAR2(1000), 
 "METERSUBCATEGORY" VARCHAR2(2000), 
 "PRODUCT" VARCHAR2(3000), 
 "PRODUCTID" VARCHAR2(3000), 
 "RESOURCEGROUP" VARCHAR2(1000), 
 "RESOURCELOCATION" VARCHAR2(1000), 
 "RESOURCELOCATIONID" VARCHAR2(3000), 
 "RESOURCERATE" VARCHAR2(1000), 
 "SERVICEADMINISTRATORID" VARCHAR2(1000), 
 "SERVICEINFO1" VARCHAR2(1000), 
 "SERVICEINFO2" VARCHAR2(1000), 
 "STORESERVICEIDENTIFIER" VARCHAR2(1000), 
 "SUBSCRIPTIONGUID" VARCHAR2(1000), 
 "SUBSCRIPTIONID" VARCHAR2(1000), 
 "SUBSCRIPTIONNAME" VARCHAR2(1000), 
 "TAGS" VARCHAR2(2000), 
 "UNITOFMEASURE" VARCHAR2(1000)
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "EXT_TAB_DATA"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
    BADFILE ext_tab_data:'az.bad'
    LOGFILE ext_tab_data:'az.log'
    DISCARDFILE ext_tab_data:'az-discarded.log'
    skip 1
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    (
accountId,
accountName,
accountOwnerEmail,
additionalInfo,
consumedQuantity,
consumedService,
consumedServiceId,
cost,
costCenter,
use_date,
departmentId,
departmentName,
instanceId,
meterCategory,
meterId,
meterName,
meterRegion,
meterSubCategory,
product,
productId,
resourceGroup,
resourceLocation,
resourceLocationId,
resourceRate,
serviceAdministratorId,
serviceInfo1,
serviceInfo2,
storeServiceIdentifier,
subscriptionGuid,
subscriptionId,
subscriptionName,
Tags,
UnitOfMeasure
    )
             )
      LOCATION
       ( 'az.csv'
       )
    )
   REJECT LIMIT UNLIMITED
/




3. you now have the usage/billing data in Oracle table, so you can built your dashboard on top of it.



We are using Azure enterprise portal here, if you don't have enterprise portal for your billing, your url maybe different.