Connecting Azure SQL in a Private VNet

Azure SQL (private) ──▶ Azure Function (VNet integrated) ──▶ Blob Storage ──▶ BonData
                                      ▲
                               Timer trigger (cron)

# ──────────────────────────────────────────────
# Variables - fill these in
# ──────────────────────────────────────────────
variable "resource_group"    { description = "Existing resource group name" }
variable "location"          { default = "eastus" }
variable "vnet_name"         { description = "VNet where your database lives" }
variable "vnet_rg"           { description = "Resource group of the VNet" }
variable "integration_subnet" { description = "Subnet name delegated to Microsoft.Web/serverFarms" }
variable "db_host"           { description = "Database FQDN (private endpoint)" }
variable "db_port"           { default = "5432" }
variable "db_name"           { description = "Database name" }
variable "db_user"           { description = "Database user" }
variable "db_password"       { sensitive = true }
variable "tables"            { default = "public.users,public.orders" description = "Comma-separated tables" }
variable "schedule"          { default = "0 0 * * * *" description = "NCRONTAB schedule (default: every hour)" }
variable "storage_account"   { default = "bondataexports" description = "Storage account name (globally unique)" }

provider "azurerm" {
  features {}
}

# ──────────────────────────────────────────────
# Data sources
# ──────────────────────────────────────────────
data "azurerm_resource_group" "rg" {
  name = var.resource_group
}

data "azurerm_subnet" "integration" {
  name                 = var.integration_subnet
  virtual_network_name = var.vnet_name
  resource_group_name  = var.vnet_rg
}

# ──────────────────────────────────────────────
# Storage account + container
# ──────────────────────────────────────────────
resource "azurerm_storage_account" "export" {
  name                     = var.storage_account
  resource_group_name      = data.azurerm_resource_group.rg.name
  location                 = var.location
  account_tier             = "Standard"
  account_replication_type = "LRS"
  allow_nested_items_to_be_public = false
}

resource "azurerm_storage_container" "export" {
  name                  = "bondata-exports"
  storage_account_id    = azurerm_storage_account.export.id
  container_access_type = "private"
}

# ──────────────────────────────────────────────
# App Service Plan (Consumption)
# ──────────────────────────────────────────────
resource "azurerm_service_plan" "plan" {
  name                = "bondata-export-plan"
  resource_group_name = data.azurerm_resource_group.rg.name
  location            = var.location
  os_type             = "Linux"
  sku_name            = "EP1" # Elastic Premium required for VNet integration
}

# ──────────────────────────────────────────────
# Function App
# ──────────────────────────────────────────────
resource "azurerm_linux_function_app" "export" {
  name                       = "bondata-db-export"
  resource_group_name        = data.azurerm_resource_group.rg.name
  location                   = var.location
  service_plan_id            = azurerm_service_plan.plan.id
  storage_account_name       = azurerm_storage_account.export.name
  storage_account_access_key = azurerm_storage_account.export.primary_access_key

  virtual_network_subnet_id = data.azurerm_subnet.integration.id

  site_config {
    application_stack {
      python_version = "3.12"
    }
    vnet_route_all_enabled = true
  }

  app_settings = {
    DB_HOST                        = var.db_host
    DB_PORT                        = var.db_port
    DB_NAME                        = var.db_name
    DB_USER                        = var.db_user
    DB_PASSWORD                    = var.db_password
    STORAGE_CONNECTION_STRING      = azurerm_storage_account.export.primary_connection_string
    STORAGE_CONTAINER              = azurerm_storage_container.export.name
    TABLES                         = var.tables
    EXPORT_SCHEDULE                = var.schedule
    FUNCTIONS_WORKER_RUNTIME       = "python"
    AzureWebJobsFeatureFlags       = "EnableWorkerIndexing"
  }
}

# ──────────────────────────────────────────────
# Outputs
# ──────────────────────────────────────────────
output "storage_account" { value = azurerm_storage_account.export.name }
output "function_app"    { value = azurerm_linux_function_app.export.name }

import os, io, json, logging
from datetime import datetime, timezone
import azure.functions as func
from azure.storage.blob import BlobServiceClient
import psycopg2, pyarrow as pa, pyarrow.parquet as pq

app = func.FunctionApp()

DB = dict(host=os.environ["DB_HOST"], port=int(os.environ.get("DB_PORT","5432")),
          dbname=os.environ["DB_NAME"], user=os.environ["DB_USER"], password=os.environ["DB_PASSWORD"])
CONN_STR  = os.environ["STORAGE_CONNECTION_STRING"]
CONTAINER = os.environ["STORAGE_CONTAINER"]
PREFIX    = os.environ.get("STORAGE_PREFIX", "db-exports")
TABLES    = [t.strip() for t in os.environ["TABLES"].split(",")]
CHUNK     = int(os.environ.get("CHUNK_SIZE", "50000"))

def export_table(cur, table, ts, container_client):
    safe = table.replace('"','').replace('.','__')
    cur.execute(f"SELECT * FROM {table} LIMIT 0")
    cols = [d[0] for d in cur.description]
    cur.execute(f"DECLARE _c CURSOR FOR SELECT * FROM {table}")
    part, total = 0, 0
    while True:
        cur.execute(f"FETCH {CHUNK} FROM _c")
        rows = cur.fetchall()
        if not rows: break
        tbl = pa.table({c: [r[i] for r in rows] for i,c in enumerate(cols)})
        buf = io.BytesIO()
        pq.write_table(tbl, buf); buf.seek(0)
        blob_name = f"{PREFIX}/{safe}/dt={ts}/part-{part:05d}.parquet"
        container_client.upload_blob(name=blob_name, data=buf, overwrite=True)
        total += len(rows); part += 1
    cur.execute("CLOSE _c")
    return total

@app.timer_trigger(schedule=os.environ.get("EXPORT_SCHEDULE", "0 0 * * * *"),
                   arg_name="timer", run_on_startup=False)
def bondata_export(timer: func.TimerRequest):
    ts = datetime.now(timezone.utc).strftime("%Y-%m-%dT%H%M%SZ")
    blob_svc = BlobServiceClient.from_connection_string(CONN_STR)
    container_client = blob_svc.get_container_client(CONTAINER)
    conn = psycopg2.connect(**DB)
    try:
        conn.autocommit = False; cur = conn.cursor()
        res = {}
        for t in TABLES:
            try: res[t] = export_table(cur, t, ts, container_client)
            except Exception as e: logging.error(f"{t}: {e}"); res[t] = str(e); conn.rollback()
        conn.commit()
    finally: conn.close()
    logging.info(json.dumps(res))

azure-functions==1.*
azure-storage-blob==12.*
psycopg2-binary==2.9.9
pyarrow==15.0.0

func azure functionapp publish bondata-db-export

┌──────────────────────────────────────────────────┐
│                 Your Azure VNet                  │
│                                                  │
│  ┌─────────────┐       ┌────────────────┐       │
│  │  Azure SQL  │◀──────│ BonData Tunnel │───────┼──▶ BonData Cloud (port 443 outbound)
│  │  (private)  │       │    Agent       │       │
│  └─────────────┘       └────────────────┘       │
│                                                  │
└──────────────────────────────────────────────────┘