データモデル
CData JDBC Driver for Facebook Ads は、Facebook Ads API をリレーショナルテーブル、ビュー、およびストアドプロシージャにモデル化します。 このセクションでは、API の制限および要件について説明します。既定の SupportEnhancedSQL 機能を使って、これらの制限の大半を回避できます。
Views
ビュー describes the available views. Two types of views are made available:
- Base Views are statically defined to model Ad Accounts, Campaigns, Businesses, Lead Values, and more. They are used to create your custom reports instead of being used standalone. By default. these views return data from all time aggregated into a single row.
- Predefined Report Views are a set of standard reports that mimic exactly what you see in the Facebook UI. All Predefined Report Views have "report" appended to their name. By default, these views return data from all time with a daily breakdown.
ストアドプロシージャ
ストアドプロシージャ は、データソースのファンクションライクなインターフェースです。これらを使用して、データソース内の情報を検索、更新、および変更できます。
Date Ranges and Aggregation
All tables and views support date ranges and aggregation.Date Ranges
Date ranges can be defined in the WHERE clause using the Date field using =, <,>, between delimiters.Additional predefined date fields are available, specifically:
- week
- month
- quarter
When filtering on these, you can use the = operator with the date set to the first day of the time period. If you specify a different date, an error is returned.
For example, to specify the month of May in the year 2022, use the following condition, specifying the first day of that month:
month = '2022-05-01'
Aggregation
Aggregation can be applied at multiple levels:“Date” returns daily data in the query results. For example,
SELECT ID, Target, Name, Status, "Date" FROM CData.FacebookAds.Campaigns WHERE "Date" BETWEEN '2022-01-01' and '2023-01-31' and Id = '15526475270_410830705612736_5193593'“Hour” returns data aggregated by hour across the date range selected. For example, querying two years of data and selecting “Hour” returns 24 rows of data (one for each hour) with two years of data aggregated for each hour.
“DayofWeek” returns data aggregated by week across the date range selected. For example, querying two years of data and selecting “DayOfWeek” returns seven rows of data (one for each day) with two years of data aggregated for each day.
“Week” returns data aggregated by week across the date range selected. For example, querying two years of data and selecting “Week” returns 104 rows of data (one for each week) with data aggregated for each week.
“Month” returns data aggregated by month across the date range selected. For example, querying two years of data and selecting “Month” returns 24 rows of data (one for each month) with data aggregated for each month.
“MonthofYear” returns data aggregated by month across the date range selected. For example, querying two years of data and selecting “MonthofYear” returns 12 rows of data (one for each month) with two years of data aggregated for each month.
“Quarter” returns data aggregated by quarter across the date range selected. For example. querying two years of data and selecting “Quarter” return eight rows of data (one for each quarter). Note that a Quarter is defined as starting on the 1st of the month of January, April, July, and October.
“Year” returns data aggregated by year across the date range selected. For example, querying two years of data and selecting “Year” returns two rows of data (one for each year) with data aggregated by year.
NOTE: Selecting "Date" overrides any other date metric. It always returns daily data only.