· 8 years ago · Mar 16, 2018, 07:34 AM
1
2CREATE TABLE IF NOT EXISTS "tradeleads"."aggregate_events" (
3 "ad_campaign_id" LONG,
4 "ad_campaign_type" STRING,
5 "ad_city_id" INTEGER,
6 "ad_clicks" DOUBLE,
7 "ad_conversions" DOUBLE,
8 "ad_cost" DOUBLE,
9 "ad_country_id" INTEGER,
10 "ad_device_type" STRING,
11 "ad_group_id" LONG,
12 "ad_id" LONG,
13 "ad_impressions" DOUBLE,
14 "ad_phrase" STRING,
15 "ad_phrase_id" LONG,
16 "ad_position" INTEGER,
17 "ad_provider_id" STRING,
18 "ad_provider_region_id" INTEGER,
19 "ad_region_id" INTEGER,
20 "ad_source" STRING,
21 "ad_source_type" STRING,
22 "advertiser_approved" INTEGER,
23 "advertiser_id" INTEGER,
24 "advertiser_pending" INTEGER,
25 "advertiser_rejected" INTEGER,
26 "aff_sub1" STRING,
27 "aff_sub2" STRING,
28 "aff_sub3" STRING,
29 "aff_sub4" STRING,
30 "aff_sub5" STRING,
31 "affiliate_approved" INTEGER,
32 "affiliate_employee_id" INTEGER,
33 "affiliate_id" INTEGER,
34 "affiliate_pending" INTEGER,
35 "affiliate_rejected" INTEGER,
36 "browser_app" STRING,
37 "browser_device" STRING,
38 "browser_format" STRING,
39 "city_id" INTEGER,
40 "click_id" STRING,
41 "clicks_total" INTEGER,
42 "clicks_unique" INTEGER,
43 "contextad_dimension_id" INTEGER,
44 "conversion_approved" INTEGER,
45 "conversion_id" LONG,
46 "conversion_pending" INTEGER,
47 "conversion_rejected" INTEGER,
48 "conversion_source" STRING,
49 "conversion_total" INTEGER,
50 "country_id" INTEGER,
51 "current_affiliate_employee_id" INTEGER,
52 "default_payout" DOUBLE,
53 "default_revenue" DOUBLE,
54 "domain" STRING,
55 "event_http_host" STRING,
56 "file_id" INTEGER,
57 "file_type" STRING,
58 "goal_id" INTEGER,
59 "has_personal_payout" INTEGER,
60 "hour" TIMESTAMP,
61 "http_host" STRING,
62 "id" LONG,
63 "ip_addr" STRING,
64 "is_test" INTEGER,
65 "is_unique_conversion" INTEGER,
66 "keyword" STRING,
67 "lead_id" LONG,
68 "lead_offer_id" INTEGER,
69 "lead_total" INTEGER,
70 "offer_employee_id" INTEGER,
71 "offer_id" INTEGER,
72 "offer_url_id" INTEGER,
73 "os" STRING,
74 "payout" DOUBLE,
75 "platform_id" INTEGER,
76 "preoffer_url_id" INTEGER,
77 "protocol_type" STRING,
78 "referrer" STRING,
79 "region_id" INTEGER,
80 "revenue" DOUBLE,
81 "rotator_id" INTEGER,
82 "row_type" STRING,
83 "search_engine" STRING,
84 "source" STRING,
85 "status" STRING,
86 "status_advertiser" STRING,
87 "status_affiliate" STRING,
88 "sys_sub1" STRING,
89 "sys_sub2" STRING,
90 "sys_sub3" STRING,
91 "sys_sub4" STRING,
92 "sys_sub5" STRING,
93 "user_agent" STRING,
94 "utm_campaign" STRING,
95 "utm_content" STRING,
96 "utm_medium" STRING,
97 "utm_referrer" STRING,
98 "utm_source" STRING,
99 "utm_term" STRING,
100 "valuta_id" INTEGER,
101 "valuta_payout" DOUBLE,
102 "valuta_rate_id" INTEGER,
103 "valuta_revenue" DOUBLE,
104 "views_total" INTEGER,
105 "views_unique" INTEGER,
106 PRIMARY KEY ("id")
107)
108CLUSTERED BY ("id") INTO 4 SHARDS
109WITH (
110 "blocks.metadata" = false,
111 "blocks.read" = false,
112 "blocks.read_only" = false,
113 "blocks.write" = false,
114 column_policy = 'strict',
115 "mapping.total_fields.limit" = 1000,
116 number_of_replicas = '0',
117 "recovery.initial_shards" = 'quorum',
118 refresh_interval = 1000,
119 "routing.allocation.enable" = 'all',
120 "routing.allocation.total_shards_per_node" = -1,
121 "translog.durability" = 'REQUEST',
122 "translog.flush_threshold_size" = 536870912,
123 "translog.sync_interval" = 5000,
124 "unassigned.node_left.delayed_timeout" = 60000,
125 "warmer.enabled" = true,
126 "write.wait_for_active_shards" = 'all'
127);
128
129INSERT INTO "tradeleads"."aggregate_events" (
130 "ad_campaign_id",
131 "ad_campaign_type",
132 "ad_city_id",
133 "ad_clicks",
134 "ad_conversions",
135 "ad_cost",
136 "ad_country_id",
137 "ad_device_type",
138 "ad_group_id",
139 "ad_id",
140 "ad_impressions",
141 "ad_phrase",
142 "ad_phrase_id",
143 "ad_position",
144 "ad_provider_id",
145 "ad_provider_region_id",
146 "ad_region_id",
147 "ad_source",
148 "ad_source_type",
149 "advertiser_approved",
150 "advertiser_id",
151 "advertiser_pending",
152 "advertiser_rejected",
153 "aff_sub1",
154 "aff_sub2",
155 "aff_sub3",
156 "aff_sub4",
157 "aff_sub5",
158 "affiliate_approved",
159 "affiliate_employee_id",
160 "affiliate_id",
161 "affiliate_pending",
162 "affiliate_rejected",
163 "browser_app",
164 "browser_device",
165 "browser_format",
166 "city_id",
167 "click_id",
168 "clicks_total",
169 "clicks_unique",
170 "contextad_dimension_id",
171 "conversion_approved",
172 "conversion_id",
173 "conversion_pending",
174 "conversion_rejected",
175 "conversion_source",
176 "conversion_total",
177 "country_id",
178 "current_affiliate_employee_id",
179 "default_payout",
180 "default_revenue",
181 "domain",
182 "event_http_host",
183 "file_id",
184 "file_type",
185 "goal_id",
186 "has_personal_payout",
187 "hour",
188 "http_host",
189 "id",
190 "ip_addr",
191 "is_test",
192 "is_unique_conversion",
193 "keyword",
194 "lead_id",
195 "lead_offer_id",
196 "lead_total",
197 "offer_employee_id",
198 "offer_id",
199 "offer_url_id",
200 "os",
201 "payout",
202 "platform_id",
203 "preoffer_url_id",
204 "protocol_type",
205 "referrer",
206 "region_id",
207 "revenue",
208 "rotator_id",
209 "row_type",
210 "search_engine",
211 "source",
212 "status",
213 "status_advertiser",
214 "status_affiliate",
215 "sys_sub1",
216 "sys_sub2",
217 "sys_sub3",
218 "sys_sub4",
219 "sys_sub5",
220 "user_agent",
221 "utm_campaign",
222 "utm_content",
223 "utm_medium",
224 "utm_referrer",
225 "utm_source",
226 "utm_term",
227 "valuta_id",
228 "valuta_payout",
229 "valuta_rate_id",
230 "valuta_revenue",
231 "views_total",
232 "views_unique")
233 SELECT
234 "ad_campaign_id",
235 "ad_campaign_type",
236 "ad_city_id",
237 "ad_clicks_fixed",
238 "ad_conversions",
239 "ad_cost",
240 "ad_country_id",
241 "ad_device_type",
242 "ad_group_id",
243 "ad_id",
244 "ad_impressions_fixed",
245 "ad_phrase",
246 "ad_phrase_id_fixed",
247 "ad_position",
248 "ad_provider_id",
249 "ad_provider_region_id",
250 "ad_region_id",
251 "ad_source",
252 "ad_source_type",
253 "advertiser_approved",
254 "advertiser_id",
255 "advertiser_pending",
256 "advertiser_rejected",
257 "aff_sub1",
258 "aff_sub2",
259 "aff_sub3",
260 "aff_sub4",
261 "aff_sub5",
262 "affiliate_approved",
263 "affiliate_employee_id",
264 "affiliate_id",
265 "affiliate_pending",
266 "affiliate_rejected",
267 "browser_app",
268 "browser_device",
269 "browser_format",
270 "city_id",
271 "click_id",
272 "clicks_total",
273 "clicks_unique",
274 "contextad_dimension_id",
275 "conversion_approved",
276 "conversion_id",
277 "conversion_pending",
278 "conversion_rejected",
279 "conversion_source",
280 "conversion_total",
281 "country_id",
282 "current_affiliate_employee_id",
283 "default_payout",
284 "default_revenue",
285 "domain",
286 "event_http_host",
287 "file_id",
288 "file_type",
289 "goal_id",
290 "has_personal_payout",
291 "hour",
292 "http_host",
293 "id",
294 "ip_addr",
295 "is_test",
296 "is_unique_conversion",
297 "keyword",
298 "lead_id",
299 "lead_offer_id",
300 "lead_total",
301 "offer_employee_id",
302 "offer_id",
303 "offer_url_id",
304 "os",
305 "payout",
306 "platform_id",
307 "preoffer_url_id",
308 "protocol_type",
309 "referrer",
310 "region_id",
311 "revenue",
312 "rotator_id",
313 "row_type",
314 "search_engine",
315 "source",
316 "status",
317 "status_advertiser",
318 "status_affiliate",
319 "sys_sub1",
320 "sys_sub2",
321 "sys_sub3",
322 "sys_sub4",
323 "sys_sub5",
324 "user_agent",
325 "utm_campaign",
326 "utm_content",
327 "utm_medium",
328 "utm_referrer",
329 "utm_source",
330 "utm_term",
331 "valuta_id",
332 "valuta_payout",
333 "valuta_rate_id",
334 "valuta_revenue",
335 "views_total",
336 "views_unique"
337
338 FROM tradeleads.aggregate_event_fact;