· 4 years ago · Sep 06, 2021, 11:16 AM
1CREATE DATABASE IF NOT EXISTS test ON CLUSTER example_cluster;
2CREATE TABLE `test`.events_shard_wojtus_test ON CLUSTER "example_cluster"
3(
4 `server_time` DateTime,
5 `server_date` Date,
6 `app_id` UInt32,
7 `app_uuid` FixedString(16),
8 `visitor_id` UInt64,
9 `user_id` String,
10 `cookie_id` UInt64,
11 `local_time` DateTime,
12 `visitor_returning` UInt8,
13 `visitor_session_number` UInt16,
14 `visitor_days_since_last_session` Nullable(UInt16),
15 `visitor_days_since_first_session` Nullable(UInt16),
16 `visitor_days_since_order` Nullable(UInt16),
17 `session_last_event_time` DateTime,
18 `session_total_events` UInt16,
19 `session_total_time` UInt32,
20 `session_total_page_views` UInt16,
21 `session_total_outlinks` UInt16,
22 `session_total_downloads` UInt16,
23 `session_total_site_searches` UInt16,
24 `session_total_custom_events` UInt16,
25 `session_total_content_impressions` UInt16,
26 `session_total_content_interactions` UInt16,
27 `session_total_goal_conversions` UInt16,
28 `session_total_ecommerce_conversions` UInt16,
29 `session_total_abandoned_carts` UInt16,
30 `session_unique_page_views` UInt16,
31 `session_unique_outlinks` UInt16,
32 `session_unique_downloads` UInt16,
33 `session_unique_searches` UInt16,
34 `session_unique_custom_events` UInt16,
35 `session_unique_content_impressions` UInt16,
36 `session_unique_content_interactions` UInt16,
37 `session_total_revenue` Nullable(Float64),
38 `session_total_goal_revenue` Nullable(Float64),
39 `session_total_lost_revenue` Nullable(Float64),
40 `session_goals` Array(Int32),
41 `session_goal_uuids` Array(FixedString(16)),
42 `session_ecommerce_status` UInt8,
43 `source` String,
44 `medium` String,
45 `source_medium` String,
46 `keyword` String,
47 `referrer_type` UInt8,
48 `referrer_name` String,
49 `referrer_url` String,
50 `referrer_keyword` String,
51 `campaign_name` String,
52 `campaign_keyword` String,
53 `campaign_source` String,
54 `campaign_medium` String,
55 `campaign_id` String,
56 `campaign_content` String,
57 `campaign_gclid` Nullable(String),
58 `operating_system` Nullable(FixedString(3)),
59 `operating_system_version` String,
60 `browser_engine` String,
61 `browser_name` Nullable(FixedString(2)),
62 `browser_version` String,
63 `browser_language_iso639` Nullable(FixedString(2)),
64 `browser_fingerprint` UInt64,
65 `device_type` Nullable(UInt8),
66 `device_brand` Nullable(FixedString(2)),
67 `device_model` String,
68 `resolution` Nullable(String),
69 `resolution_width` Nullable(UInt16),
70 `resolution_height` Nullable(UInt16),
71 `plugin_pdf` UInt8,
72 `plugin_flash` UInt8,
73 `plugin_java` UInt8,
74 `plugin_director` UInt8,
75 `plugin_quicktime` UInt8,
76 `plugin_realplayer` UInt8,
77 `plugin_windowsmedia` UInt8,
78 `plugin_gears` UInt8,
79 `plugin_silverlight` UInt8,
80 `plugin_cookie` UInt8,
81 `location_ipv4` Nullable(UInt32),
82 `location_ipv6` Nullable(FixedString(16)),
83 `location_continent_iso_code` Nullable(FixedString(2)),
84 `location_country_iso_code` Nullable(FixedString(2)),
85 `location_country_name` String,
86 `location_subdivision_1_iso_code` String,
87 `location_subdivision_1_name` String,
88 `location_subdivision_2_iso_code` String,
89 `location_subdivision_2_name` String,
90 `location_city_geoname_id` UInt32,
91 `location_city_name` String,
92 `location_metro_code` Nullable(FixedString(3)),
93 `location_latitude` Nullable(Float64),
94 `location_longitude` Nullable(Float64),
95 `location_provider` String,
96 `location_organization` String,
97 `session_exit_url` String,
98 `session_exit_title` String,
99 `session_entry_url` String,
100 `session_entry_title` String,
101 `session_second_url` String,
102 `session_second_title` String,
103 `is_bounce` UInt8,
104 `sharepoint_display_name` Nullable(String),
105 `sharepoint_office` Nullable(String),
106 `sharepoint_department` Nullable(String),
107 `sharepoint_job_title` Nullable(String),
108 `session_total_sharepoint_shares` UInt16,
109 `session_total_sharepoint_likes` UInt16,
110 `session_total_sharepoint_comments` UInt16,
111 `session_total_sharepoint_promotions` UInt16,
112 `session_total_sharepoint_creations` UInt16,
113 `session_total_sharepoint_edits` UInt16,
114 `session_total_sharepoint_deletions` UInt16,
115 `session_total_sharepoint_opens` UInt16,
116 `session_total_sharepoint_uploads` UInt16,
117 `session_total_sharepoint_item_views` UInt16,
118 `session_total_sharepoint_item_attachment_views` UInt16,
119 `session_total_sharepoint_item_shares` UInt16,
120 `updated_at` Nullable(DateTime),
121 `event_id` UInt64,
122 `session_id` UInt64,
123 `is_exit` UInt8,
124 `is_entry` UInt8,
125 `event_type` UInt8,
126 `event_url` String,
127 `event_title` String,
128 `outlink_url` String,
129 `download_url` String,
130 `search_keyword` String,
131 `search_category` String,
132 `search_results_count` Nullable(UInt16),
133 `custom_event_category` String,
134 `custom_event_action` String,
135 `custom_event_name` String,
136 `custom_event_value` Nullable(Float64),
137 `content_name` String,
138 `content_piece` String,
139 `content_target` String,
140 `content_interaction` String,
141 `previous_event_url` String,
142 `previous_event_title` String,
143 `next_event_url` String,
144 `next_event_title` String,
145 `event_index` UInt16,
146 `page_view_index` Nullable(UInt16),
147 `time_on_page` Nullable(UInt32),
148 `page_generation_time` Nullable(Float64),
149 `goal_id` Nullable(Int32),
150 `goal_uuid` FixedString(16),
151 `goal_revenue` Nullable(Float64),
152 `lost_revenue` Nullable(Float64),
153 `order_id` String,
154 `item_count` Nullable(UInt16),
155 `revenue` Nullable(Float64),
156 `revenue_subtotal` Nullable(Float64),
157 `revenue_tax` Nullable(Float64),
158 `revenue_shipping` Nullable(Float64),
159 `revenue_discount` Nullable(Float64),
160 `timing_connect_end` Nullable(UInt16),
161 `timing_connect_start` Nullable(UInt16),
162 `timing_dom_complete` Nullable(UInt32),
163 `timing_content_end` Nullable(UInt32),
164 `timing_content_start` Nullable(UInt32),
165 `timing_dom_interactive` Nullable(UInt32),
166 `timing_dom_loading` Nullable(UInt16),
167 `timing_lookup_start` Nullable(UInt16),
168 `timing_fetch_start` Nullable(UInt16),
169 `timing_event_end` Nullable(UInt32),
170 `timing_redirect_end` Nullable(UInt16),
171 `timing_redirect_start` Nullable(UInt16),
172 `timing_request_start` Nullable(UInt16),
173 `timing_response_end` Nullable(UInt16),
174 `timing_response_start` Nullable(UInt16),
175 `timing_secure_start` Nullable(UInt16),
176 `timing_unload_end` Nullable(UInt16),
177 `timing_unload_start` Nullable(UInt16),
178 `sharepoint_action` Nullable(UInt8),
179 `sharepoint_object_type` Nullable(UInt8),
180 `sharepoint_content_type` Nullable(String),
181 `sharepoint_author` Nullable(String),
182 `sharepoint_author_display_name` Nullable(String),
183 `sharepoint_author_office` Nullable(String),
184 `sharepoint_author_department` Nullable(String),
185 `sharepoint_author_job_title` Nullable(String),
186 `sharepoint_file_url` Nullable(String),
187 `sharepoint_file_type` Nullable(String),
188 `consent_source` Nullable(UInt8),
189 `consent_form_button` Nullable(UInt8),
190 `consent_scope` Nullable(UInt8),
191 `consent_action` Nullable(UInt8),
192 `consent_type_analytics` Nullable(UInt8),
193 `consent_type_ab_testing_personalization` Nullable(UInt8),
194 `consent_type_conversion_tracking` Nullable(UInt8),
195 `consent_type_marketing_automation` Nullable(UInt8),
196 `consent_type_remarketing` Nullable(UInt8),
197 `consent_type_user_feedback` Nullable(UInt8),
198 `consent_type_custom_1` Nullable(UInt8),
199 `consent_type_custom_2` Nullable(UInt8),
200 `consent_type_custom_3` Nullable(UInt8),
201 `consent_type_custom_4` Nullable(UInt8),
202 `consent_type_custom_5` Nullable(UInt8),
203 `log_uuids` Array(FixedString(16)),
204 `items.name` Array(String),
205 `items.sku` Array(String),
206 `items.price` Array(Float32),
207 `items.quantity` Array(UInt32),
208 `items.category1` Array(String),
209 `items.category2` Array(String),
210 `items.category3` Array(String),
211 `items.category4` Array(String),
212 `items.category5` Array(String),
213 `event_custom_dimension_1` String,
214 `event_custom_dimension_2` String,
215 `event_custom_dimension_3` String,
216 `event_custom_dimension_4` String,
217 `event_custom_dimension_5` String,
218 `event_custom_dimension_6` String,
219 `event_custom_dimension_7` String,
220 `event_custom_dimension_8` String,
221 `event_custom_dimension_9` String,
222 `event_custom_dimension_10` String,
223 `event_custom_dimension_11` String,
224 `event_custom_dimension_12` String,
225 `event_custom_dimension_13` String,
226 `event_custom_dimension_14` String,
227 `event_custom_dimension_15` String,
228 `event_custom_dimension_16` String,
229 `event_custom_dimension_17` String,
230 `event_custom_dimension_18` String,
231 `event_custom_dimension_19` String,
232 `event_custom_dimension_20` String,
233 `event_custom_dimension_21` String,
234 `event_custom_dimension_22` String,
235 `event_custom_dimension_23` String,
236 `event_custom_dimension_24` String,
237 `event_custom_dimension_25` String,
238 `event_custom_dimension_26` String,
239 `event_custom_dimension_27` String,
240 `event_custom_dimension_28` String,
241 `event_custom_dimension_29` String,
242 `event_custom_dimension_30` String,
243 `event_custom_dimension_31` String,
244 `event_custom_dimension_32` String,
245 `event_custom_dimension_33` String,
246 `event_custom_dimension_34` String,
247 `event_custom_dimension_35` String,
248 `event_custom_dimension_36` String,
249 `event_custom_dimension_37` String,
250 `event_custom_dimension_38` String,
251 `event_custom_dimension_39` String,
252 `event_custom_dimension_40` String,
253 `event_custom_dimension_41` String,
254 `event_custom_dimension_42` String,
255 `event_custom_dimension_43` String,
256 `event_custom_dimension_44` String,
257 `event_custom_dimension_45` String,
258 `event_custom_dimension_46` String,
259 `event_custom_dimension_47` String,
260 `event_custom_dimension_48` String,
261 `event_custom_dimension_49` String,
262 `event_custom_dimension_50` String,
263 `event_custom_dimension_51` String,
264 `event_custom_dimension_52` String,
265 `event_custom_dimension_53` String,
266 `event_custom_dimension_54` String,
267 `event_custom_dimension_55` String,
268 `event_custom_dimension_56` String,
269 `event_custom_dimension_57` String,
270 `event_custom_dimension_58` String,
271 `event_custom_dimension_59` String,
272 `event_custom_dimension_60` String,
273 `event_custom_dimension_61` String,
274 `event_custom_dimension_62` String,
275 `event_custom_dimension_63` String,
276 `event_custom_dimension_64` String,
277 `event_custom_dimension_65` String,
278 `event_custom_dimension_66` String,
279 `event_custom_dimension_67` String,
280 `event_custom_dimension_68` String,
281 `event_custom_dimension_69` String,
282 `event_custom_dimension_70` String,
283 `event_custom_dimension_71` String,
284 `event_custom_dimension_72` String,
285 `event_custom_dimension_73` String,
286 `event_custom_dimension_74` String,
287 `event_custom_dimension_75` String,
288 `event_custom_dimension_76` String,
289 `event_custom_dimension_77` String,
290 `event_custom_dimension_78` String,
291 `event_custom_dimension_79` String,
292 `event_custom_dimension_80` String,
293 `event_custom_dimension_81` String,
294 `event_custom_dimension_82` String,
295 `event_custom_dimension_83` String,
296 `event_custom_dimension_84` String,
297 `event_custom_dimension_85` String,
298 `event_custom_dimension_86` String,
299 `event_custom_dimension_87` String,
300 `event_custom_dimension_88` String,
301 `event_custom_dimension_89` String,
302 `event_custom_dimension_90` String,
303 `event_custom_dimension_91` String,
304 `event_custom_dimension_92` String,
305 `event_custom_dimension_93` String,
306 `event_custom_dimension_94` String,
307 `event_custom_dimension_95` String,
308 `event_custom_dimension_96` String,
309 `event_custom_dimension_97` String,
310 `event_custom_dimension_98` String,
311 `event_custom_dimension_99` String,
312 `event_custom_dimension_100` String,
313 `event_custom_variable_key_1` String,
314 `event_custom_variable_value_1` String,
315 `event_custom_variable_key_2` String,
316 `event_custom_variable_value_2` String,
317 `event_custom_variable_key_3` String,
318 `event_custom_variable_value_3` String,
319 `event_custom_variable_key_4` String,
320 `event_custom_variable_value_4` String,
321 `event_custom_variable_key_5` String,
322 `event_custom_variable_value_5` String,
323 `session_custom_dimension_1` String,
324 `session_custom_dimension_2` String,
325 `session_custom_dimension_3` String,
326 `session_custom_dimension_4` String,
327 `session_custom_dimension_5` String,
328 `session_custom_dimension_6` String,
329 `session_custom_dimension_7` String,
330 `session_custom_dimension_8` String,
331 `session_custom_dimension_9` String,
332 `session_custom_dimension_10` String,
333 `session_custom_dimension_11` String,
334 `session_custom_dimension_12` String,
335 `session_custom_dimension_13` String,
336 `session_custom_dimension_14` String,
337 `session_custom_dimension_15` String,
338 `session_custom_dimension_16` String,
339 `session_custom_dimension_17` String,
340 `session_custom_dimension_18` String,
341 `session_custom_dimension_19` String,
342 `session_custom_dimension_20` String,
343 `session_custom_dimension_21` String,
344 `session_custom_dimension_22` String,
345 `session_custom_dimension_23` String,
346 `session_custom_dimension_24` String,
347 `session_custom_dimension_25` String,
348 `session_custom_dimension_26` String,
349 `session_custom_dimension_27` String,
350 `session_custom_dimension_28` String,
351 `session_custom_dimension_29` String,
352 `session_custom_dimension_30` String,
353 `session_custom_dimension_31` String,
354 `session_custom_dimension_32` String,
355 `session_custom_dimension_33` String,
356 `session_custom_dimension_34` String,
357 `session_custom_dimension_35` String,
358 `session_custom_dimension_36` String,
359 `session_custom_dimension_37` String,
360 `session_custom_dimension_38` String,
361 `session_custom_dimension_39` String,
362 `session_custom_dimension_40` String,
363 `session_custom_dimension_41` String,
364 `session_custom_dimension_42` String,
365 `session_custom_dimension_43` String,
366 `session_custom_dimension_44` String,
367 `session_custom_dimension_45` String,
368 `session_custom_dimension_46` String,
369 `session_custom_dimension_47` String,
370 `session_custom_dimension_48` String,
371 `session_custom_dimension_49` String,
372 `session_custom_dimension_50` String,
373 `session_custom_dimension_51` String,
374 `session_custom_dimension_52` String,
375 `session_custom_dimension_53` String,
376 `session_custom_dimension_54` String,
377 `session_custom_dimension_55` String,
378 `session_custom_dimension_56` String,
379 `session_custom_dimension_57` String,
380 `session_custom_dimension_58` String,
381 `session_custom_dimension_59` String,
382 `session_custom_dimension_60` String,
383 `session_custom_dimension_61` String,
384 `session_custom_dimension_62` String,
385 `session_custom_dimension_63` String,
386 `session_custom_dimension_64` String,
387 `session_custom_dimension_65` String,
388 `session_custom_dimension_66` String,
389 `session_custom_dimension_67` String,
390 `session_custom_dimension_68` String,
391 `session_custom_dimension_69` String,
392 `session_custom_dimension_70` String,
393 `session_custom_dimension_71` String,
394 `session_custom_dimension_72` String,
395 `session_custom_dimension_73` String,
396 `session_custom_dimension_74` String,
397 `session_custom_dimension_75` String,
398 `session_custom_dimension_76` String,
399 `session_custom_dimension_77` String,
400 `session_custom_dimension_78` String,
401 `session_custom_dimension_79` String,
402 `session_custom_dimension_80` String,
403 `session_custom_dimension_81` String,
404 `session_custom_dimension_82` String,
405 `session_custom_dimension_83` String,
406 `session_custom_dimension_84` String,
407 `session_custom_dimension_85` String,
408 `session_custom_dimension_86` String,
409 `session_custom_dimension_87` String,
410 `session_custom_dimension_88` String,
411 `session_custom_dimension_89` String,
412 `session_custom_dimension_90` String,
413 `session_custom_dimension_91` String,
414 `session_custom_dimension_92` String,
415 `session_custom_dimension_93` String,
416 `session_custom_dimension_94` String,
417 `session_custom_dimension_95` String,
418 `session_custom_dimension_96` String,
419 `session_custom_dimension_97` String,
420 `session_custom_dimension_98` String,
421 `session_custom_dimension_99` String,
422 `session_custom_dimension_100` String,
423 `session_custom_variable_key_1` String,
424 `session_custom_variable_value_1` String,
425 `session_custom_variable_key_2` String,
426 `session_custom_variable_value_2` String,
427 `session_custom_variable_key_3` String,
428 `session_custom_variable_value_3` String,
429 `session_custom_variable_key_4` String,
430 `session_custom_variable_value_4` String,
431 `session_custom_variable_key_5` String,
432 `session_custom_variable_value_5` String
433)
434ENGINE = ReplicatedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')
435PARTITION BY toYYYYMM(server_date)
436ORDER BY (app_uuid, server_date, event_type, visitor_id)
437SAMPLE BY visitor_id
438SETTINGS index_granularity = 8192