forked from ClickHouse/ClickBench
-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathcreate.sql
More file actions
124 lines (119 loc) · 3.79 KB
/
create.sql
File metadata and controls
124 lines (119 loc) · 3.79 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
CREATE EXTENSION IF NOT EXISTS pg_duckdb;
CREATE SCHEMA IF NOT EXISTS clickbench;
DROP VIEW IF EXISTS hits_parquet;
CREATE VIEW hits_parquet AS
SELECT
r['WatchID'] WatchID,
r['JavaEnable'] JavaEnable,
r['Title'] Title,
r['GoodEvent'] GoodEvent,
'epoch'::timestamp +(r['EventTime'] || 'second')::interval EventTime,
'epoch'::timestamp +(r['EventDate'] || 'day')::interval EventDate,
r['CounterID'] CounterID,
r['ClientIP'] ClientIP,
r['RegionID'] RegionID,
r['UserID'] UserID,
r['CounterClass'] CounterClass,
r['OS'] OS,
r['UserAgent'] UserAgent,
r['URL'] URL,
r['Referer'] Referer,
r['IsRefresh'] IsRefresh,
r['RefererCategoryID'] RefererCategoryID,
r['RefererRegionID'] RefererRegionID,
r['URLCategoryID'] URLCategoryID,
r['URLRegionID'] URLRegionID,
r['ResolutionWidth'] ResolutionWidth,
r['ResolutionHeight'] ResolutionHeight,
r['ResolutionDepth'] ResolutionDepth,
r['FlashMajor'] FlashMajor,
r['FlashMinor'] FlashMinor,
r['FlashMinor2'] FlashMinor2,
r['NetMajor'] NetMajor,
r['NetMinor'] NetMinor,
r['UserAgentMajor'] UserAgentMajor,
r['UserAgentMinor'] UserAgentMinor,
r['CookieEnable'] CookieEnable,
r['JavascriptEnable'] JavascriptEnable,
r['IsMobile'] IsMobile,
r['MobilePhone'] MobilePhone,
r['MobilePhoneModel'] MobilePhoneModel,
r['Params'] Params,
r['IPNetworkID'] IPNetworkID,
r['TraficSourceID'] TraficSourceID,
r['SearchEngineID'] SearchEngineID,
r['SearchPhrase'] SearchPhrase,
r['AdvEngineID'] AdvEngineID,
r['IsArtifical'] IsArtifical,
r['WindowClientWidth'] WindowClientWidth,
r['WindowClientHeight'] WindowClientHeight,
r['ClientTimeZone'] ClientTimeZone,
'epoch'::timestamp +(r['ClientEventTime'] || 'second')::interval ClientEventTime,
r['SilverlightVersion1'] SilverlightVersion1,
r['SilverlightVersion2'] SilverlightVersion2,
r['SilverlightVersion3'] SilverlightVersion3,
r['SilverlightVersion4'] SilverlightVersion4,
r['PageCharset'] PageCharset,
r['CodeVersion'] CodeVersion,
r['IsLink'] IsLink,
r['IsDownload'] IsDownload,
r['IsNotBounce'] IsNotBounce,
r['FUniqID'] FUniqID,
r['OriginalURL'] OriginalURL,
r['HID'] HID,
r['IsOldCounter'] IsOldCounter,
r['IsEvent'] IsEvent,
r['IsParameter'] IsParameter,
r['DontCountHits'] DontCountHits,
r['WithHash'] WithHash,
r['HitColor'] HitColor,
'epoch'::timestamp +(r['LocalEventTime'] || 'second')::interval LocalEventTime,
r['Age'] Age,
r['Sex'] Sex,
r['Income'] Income,
r['Interests'] Interests,
r['Robotness'] Robotness,
r['RemoteIP'] RemoteIP,
r['WindowName'] WindowName,
r['OpenerName'] OpenerName,
r['HistoryLength'] HistoryLength,
r['BrowserLanguage'] BrowserLanguage,
r['BrowserCountry'] BrowserCountry,
r['SocialNetwork'] SocialNetwork,
r['SocialAction'] SocialAction,
r['HTTPError'] HTTPError,
r['SendTiming'] SendTiming,
r['DNSTiming'] DNSTiming,
r['ConnectTiming'] ConnectTiming,
r['ResponseStartTiming'] ResponseStartTiming,
r['ResponseEndTiming'] ResponseEndTiming,
r['FetchTiming'] FetchTiming,
r['SocialSourceNetworkID'] SocialSourceNetworkID,
r['SocialSourcePage'] SocialSourcePage,
r['ParamPrice'] ParamPrice,
r['ParamOrderID'] ParamOrderID,
r['ParamCurrency'] ParamCurrency,
r['ParamCurrencyID'] ParamCurrencyID,
r['OpenstatServiceName'] OpenstatServiceName,
r['OpenstatCampaignID'] OpenstatCampaignID,
r['OpenstatAdID'] OpenstatAdID,
r['OpenstatSourceID'] OpenstatSourceID,
r['UTMSource'] UTMSource,
r['UTMMedium'] UTMMedium,
r['UTMCampaign'] UTMCampaign,
r['UTMContent'] UTMContent,
r['UTMTerm'] UTMTerm,
r['FromTag'] FromTag,
r['HasGCLID'] HasGCLID,
r['RefererHash'] RefererHash,
r['URLHash'] URLHash,
r['CLID'] CLID
FROM
read_parquet('/tmp/hits.parquet') r;
DROP TABLE IF EXISTS clickbench.hits;
CREATE TABLE clickbench.hits
USING duckdb AS
SELECT
*
FROM
hits_parquet;