Meta-Jdbc Driver

Introduction

The MetaJdbc driver enables reading Meta data (Facebook Graph API, Instagram Business, and Meta Ads) via JDBC with SQL.

The following conventions apply:

  • the database connection uses jdbc:meta:
  • the JDBC URL contains the Graph API base URL (jdbc:meta:https://graph.facebook.com/v25.0)
  • endpoints, fields, and metrics are configured in a spec file (JSON, YAML, or INI)
  • the schema public is used by default
  • SQL table names are predefined and bound to fixed fetch strategies (e.g. fbk_page_insights_daily, meta_ad_account)
  • metrics and API parameters come from the spec and are customizable at runtime
  • the driver supports SELECT only (read-only)
  • a single system user token (super-admin) is sufficient — page tokens and ad accounts are resolved internally

Features

  • Facebook pages and linked Instagram accounts via /me/accounts
  • Facebook page insights (daily)
  • Instagram page insights (snapshot and daily)
  • Instagram media listing and media insights
  • Meta ad accounts, campaigns, ad sets, ads
  • Async ad insights (meta_ad_stats, meta_conversion_stats)
  • Spec file customizable at runtime (metrics, limits, endpoints, filters)
  • 12 predefined tables with specialized API logic
  • System tables for tables, columns, and primary keys

JDBC URL

jdbc:meta:https://graph.facebook.com/v25.0

The part after jdbc:meta: is the Graph API base URL including API version. It takes precedence over api.baseUrl in the spec.

Example connection (Java)

Properties props = new Properties();
props.setProperty("token", "EAAx...");
props.setProperty("spec", "/opt/datasqill/config/meta-spec.json");

Connection conn = DriverManager.getConnection(
    "jdbc:meta:https://graph.facebook.com/v25.0", props);

Date range and object selection belong in SQL (FILTER), not in the connection — see FILTER.

Connection properties

Property Description Required
token System user access token (Facebook Login for Business) Yes
spec Path to spec file (.json, .yaml, .yml, .ini; filesystem or classpath:...) No (default: bundled spec)
von_dt Start date (yyyy-MM-dd) Via FILTER in SQL (fallback: connection property)
bis_dt End date (yyyy-MM-dd) Via FILTER in SQL (fallback: connection property)
load_type delta (default) or full for async ad insights Via FILTER or connection property
stdoutlog Debug output: meta, http, rows, cursor, or all (combine with +) No

Property names are case-insensitive. config is an alias for spec.

Spec path:

/opt/datasqill/config/meta-spec.json
/opt/datasqill/config/meta-spec.yaml
classpath:de/softquadrat/jdbc/meta/meta-spec.json

datasqill keyfile

Only static connection data (token, spec) belongs in the keyfile. The load date range goes in SQL:

jdbc:meta:https://graph.facebook.com/v25.0:dboptionlist={token=EAAx...,spec=/opt/datasqill/meta-spec.json}:

Authentication

A single system user token is used. The driver:

  1. calls GET /me/accounts and obtains page access tokens
  2. uses those page tokens for Facebook and Instagram insights
  3. uses the user token for all act_* ad account calls

Required permissions (excerpt):

  • pages_read_engagement, pages_show_list
  • instagram_basic, instagram_manage_insights
  • ads_read (for paid media)

Usage pattern

Open one JDBC connection per ETL job and run one SELECT per target table.

SELECT page_id, name, insta_user_id FROM fbk_page_insights;

SELECT page_id, dat, page_total_media_view_unique
FROM fbk_page_insights_daily
FILTER von_dt='2025-01-01' AND bis_dt='2025-01-31'
;

SELECT ad_account_id, name FROM meta_ad_account;

SELECT ad_account_id, ad_id, impressions, datum
FROM meta_ad_stats
FILTER von_dt='2025-01-01' AND bis_dt='2025-01-31' AND load_type=delta
;

The driver iterates internally over all pages or ad accounts. Use FILTER to narrow the date range or select individual pages. There is no WHERE and no LIMIT.

Note: datasqill SQL does not support line comments with --. meta-staging.sql contains executable statements only.

Caching within a connection

Data Behavior
Page list (/me/accounts) once per connection, then cached
Ad account list once per connection, then cached
Insights data fetched from API on every SELECT

Multiple SELECTs in the same connection avoid a second /me/accounts call. Running the same SELECT twice performs the full API run again.

Tables

Social media (Facebook / Instagram)

Table Content von_dt/bis_dt
fbk_page_insights Facebook pages + linked IG accounts No
fbk_page_insights_daily Facebook page insights per day Yes
ins_page_insights Instagram follower snapshot No
ins_page_insights_daily Instagram account insights per day Yes
ins_media Instagram media objects (posts/reels) Optional (filter by publish date)
ins_media_insights Instagram media insights (lifetime) Yes (filter by publish date)

Paid media (Meta Ads)

Table Content von_dt/bis_dt
meta_ad_account Ad accounts No
meta_campaign Campaigns (all accounts) No
meta_adset Ad sets No
meta_ad Ads No
meta_ad_stats Ad performance (async) Yes (with load_type=delta)
meta_conversion_stats Conversion stats (async) Yes (with load_type=delta)

Full SQL example: meta-staging.sql

Spec file

The spec defines API endpoints, requested fields/metrics, limits, and filters. Formats: JSON (recommended), YAML, or INI (legacy).

Full reference spec: meta-spec.json
Short YAML example: meta-spec-example.yaml

Structure (JSON)

{
  "api": {
    "version": "v25.0",
    "baseUrl": "https://graph.facebook.com"
  },
  "fbk_page_insights_daily": {
    "endpoint": "insights",
    "fields": [
      "page_total_media_view_unique",
      "page_media_view",
      "page_post_engagements"
    ],
    "tablename": "stage.fbk_page_insights_daily"
  }
}

Structure (YAML)

api:
  version: v25.0
  baseUrl: https://graph.facebook.com

fbk_page_insights_daily:
  endpoint: insights
  fields:
    - page_total_media_view_unique
    - page_post_engagements

Spec sections

Each root-level key (except api) is the name of a predefined SQL table and parameterizes its API calls (fields, endpoint, limit, …). New table names cannot be added via spec alone.

Field Description
endpoint Graph API endpoint relative to page/ad account ID
endpoints Comma-separated endpoints (only ad_account, e.g. owned + client accounts)
fields Fields or insights metrics to request (array or CSV string)
limit Page size for paginated requests
filtering Meta API filtering expression (paid media)
breakdowns Breakdowns for async insights
tablename Staging target name (documentation) JDBC ignores

Dynamic metric columns

For fbk_page_insights_daily, ins_page_insights_daily, and ins_media_insights, metric columns are read from fields in the spec. New metrics can be added in the spec without recompiling the driver.

Master data columns (page_id, media_id, ad_account_id, …) remain fixed in the driver.

What the spec controls — and what it does not

The Graph API uses different load patterns per data type (page iteration, async jobs, media pagination, pivot logic), implemented in the driver as fixed fetch strategies.

Configuration Spec Driver (Java)
SQL table name No Yes — 12 fixed names
Fetch strategy (how data is loaded) No Yes — e.g. page insights daily, async ad stats
Master data columns (page_id, ad_account_id, …) No Yes
Metric columns (fields) Yes Base columns + spec
API endpoints, limits, filters, breakdowns Yes —
tablename (staging target) Yes JDBC ignores

Practical implications:

  • Adjust metrics, remove deprecated metrics → edit spec, done
  • New table with a known load pattern → future driver extensions

Each spec section (e.g. fbk_page_insights_daily) maps to exactly one predefined SQL table. The section name must match the table name.

SQL syntax

Basic SELECT query

SELECT page_id, name, insta_user_id
FROM fbk_page_insights
;

SELECT page_id, dat, page_total_media_view_unique, page_post_engagements
FROM fbk_page_insights_daily
FILTER von_dt='2025-01-01' AND bis_dt='2025-01-31'
;

SELECT page_id, dat, page_total_media_view_unique
FROM fbk_page_insights_daily
FILTER von_dt='2025-01-01' AND bis_dt='2025-01-31' AND page_id='330419580652874'
;

FILTER (date range and object selection)

In datasqill SQL the clause is FILTER (not WHERE). It controls driver API parameters — mainly the load date range and optionally individual pages.

Syntax: param=value, multiple parameters joined with AND. Values in single quotes or unquoted.

FILTER parameter Alias Effect
von_dt from_date Start of date range (yyyy-MM-dd)
bis_dt to_date End of date range (yyyy-MM-dd)
page_id — Load only this Facebook page
insta_user_id — Load only this Instagram account
load_type — delta or full for async ad insights

Priority: FILTER values override connection properties. In datasqill ETL jobs, set the date range in SQL — only administrators maintain connection/keyfile (token, spec).

Examples:

SELECT ad_id, impressions, date_start
FROM meta_ad_stats
FILTER von_dt='2025-01-01' AND bis_dt='2025-01-31' AND load_type=delta
;

SELECT insta_user_id, dat, reach
FROM ins_page_insights_daily
FILTER from_date='2025-01-01' AND to_date='2025-01-31' AND insta_user_id='17841400000'
;

Not supported

  • INSERT, UPDATE, DELETE
  • WHERE, ORDER BY
  • SQL line comments (-- …)
  • COLUMNS clause

Column selection via SELECT works — only referenced columns are returned.

Reserved column names

Column names that are SQL keywords must be double-quoted:

SELECT insta_user_id, media_id, "timestamp", permalink FROM ins_media;

Applies to timestamp in ins_media and ins_media_insights.

Instagram follower_count (daily)

The follower_count metric in ins_page_insights_daily can only be queried for the last 30 days (excluding today). For older days in the FILTER range, the driver still loads other metrics (reach, likes, …) and sets follower_count to NULL — without an API error.

System tables

The system schema provides virtual tables:

Table Content
table_list All registered Meta tables
column_list Columns (including dynamic metrics from spec)
pk_list Primary keys 
SELECT table_name, remarks
FROM system.table_list
WHERE table_name LIKE 'meta_%'
;

SELECT column_name, type_name
FROM system.column_list
WHERE table_name = 'fbk_page_insights_daily'
;

Internal flow

Social media

token → GET /me/accounts → all pages
  ├─ fbk_page_insights        → page master data
  ├─ fbk_page_insights_daily  → GET /{page-id}/insights (per page)
  ├─ ins_page_insights        → GET /{ig-user-id}
  ├─ ins_page_insights_daily  → GET /{ig-user-id}/insights (per day)
  ├─ ins_media                → GET /{ig-user-id}/media
  └─ ins_media_insights       → GET /{media-id}/insights (per media)

Paid media

token → ad accounts (owned + client)
  ├─ meta_ad_account          → master data
  ├─ meta_campaign/adset/ad   → paginated entity calls per account
  └─ meta_ad_stats            → POST async insights → poll → GET results

Troubleshooting

stdoutlog=meta+http+rows
Level Output
meta API calls, page/account resolution, skipped metrics
http HTTP method and URL
rows Rows read per batch
cursor Opened table

Common errors:

Message Cause / fix
(#100) The value must be a valid insights metric Metric deprecated in Graph API v25 — remove from spec or driver skips individually
Missing "von_dt" Add FILTER von_dt='…' AND bis_dt='…' (required for daily/delta tables)
Sorry, I cannot understand -- … No -- comments in datasqill SQL — remove the comment line
mismatched input 'timestamp' Quote the column as "timestamp"
follower_count … last 30 days Historical days: driver skips follower_count automatically; use a new build
HTTP 429 Rate limit — driver retries automatically; reduce load
Long runtime without output Normal with many pages/media — enable stdoutlog

Notes

API version and metrics

Meta regularly deprecates insights metrics (e.g. page_impressions_unique in v25). The default spec uses current metrics (page_total_media_view_unique, page_media_view). Check the Meta Insights documentation on API errors.

Instagram media insights

ins_media_insights performs one API call per media object. Loads with many posts can take a long time.

Invalid metrics per media_type (FEED, REELS, STORY) are skipped automatically.

Examples