Execute the following query:
COPY hits
TO '/home/test/clickbench/hits.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');
CREATE VIEW hits_parquet AS SELECT
r['watchid']::bigint AS watchid,
r['javaenable']::smallint AS javaenable,
r['title']::text AS title,
r['goodevent']::smallint AS goodevent,
r['eventtime']::timestamp AS eventtime,
r['eventdate']::date AS eventdate,
r['counterid']::integer AS counterid,
r['clientip']::integer AS clientip,
r['regionid']::integer AS regionid,
r['userid']::bigint AS userid,
r['counterclass']::smallint AS counterclass,
r['os']::smallint AS os,
r['useragent']::smallint AS useragent,
r['url']::text AS url,
r['referer']::text AS referer,
r['isrefresh']::smallint AS isrefresh,
r['referercategoryid']::smallint AS referercategoryid,
r['refererregionid']::integer AS refererregionid,
r['urlcategoryid']::smallint AS urlcategoryid,
r['urlregionid']::integer AS urlregionid,
r['resolutionwidth']::smallint AS resolutionwidth,
r['resolutionheight']::smallint AS resolutionheight,
r['resolutiondepth']::smallint AS resolutiondepth,
r['flashmajor']::smallint AS flashmajor,
r['flashminor']::smallint AS flashminor,
r['flashminor2']::text AS flashminor2,
r['netmajor']::smallint AS netmajor,
r['netminor']::smallint AS netminor,
r['useragentmajor']::smallint AS useragentmajor,
r['useragentminor']::text AS useragentminor,
r['cookieenable']::smallint AS cookieenable,
r['javascriptenable']::smallint AS javascriptenable,
r['ismobile']::smallint AS ismobile,
r['mobilephone']::smallint AS mobilephone,
r['mobilephonemodel']::text AS mobilephonemodel,
r['params']::text AS params,
r['ipnetworkid']::integer AS ipnetworkid,
r['traficsourceid']::smallint AS traficsourceid,
r['searchengineid']::smallint AS searchengineid,
r['searchphrase']::text AS searchphrase,
r['advengineid']::smallint AS advengineid,
r['isartifical']::smallint AS isartifical,
r['windowclientwidth']::smallint AS windowclientwidth,
r['windowclientheight']::smallint AS windowclientheight,
r['clienttimezone']::smallint AS clienttimezone,
r['clienteventtime']::timestamp AS clienteventtime,
r['silverlightversion1']::smallint AS silverlightversion1,
r['silverlightversion2']::smallint AS silverlightversion2,
r['silverlightversion3']::integer AS silverlightversion3,
r['silverlightversion4']::smallint AS silverlightversion4,
r['pagecharset']::text AS pagecharset,
r['codeversion']::integer AS codeversion,
r['islink']::smallint AS islink,
r['isdownload']::smallint AS isdownload,
r['isnotbounce']::smallint AS isnotbounce,
r['funiqid']::bigint AS funiqid,
r['originalurl']::text AS originalurl,
r['hid']::integer AS hid,
r['isoldcounter']::smallint AS isoldcounter,
r['isevent']::smallint AS isevent,
r['isparameter']::smallint AS isparameter,
r['dontcounthits']::smallint AS dontcounthits,
r['withhash']::smallint AS withhash,
r['hitcolor']::text AS hitcolor,
r['localeventtime']::timestamp AS localeventtime,
r['age']::smallint AS age,
r['sex']::smallint AS sex,
r['income']::smallint AS income,
r['interests']::smallint AS interests,
r['robotness']::smallint AS robotness,
r['remoteip']::integer AS remoteip,
r['windowname']::integer AS windowname,
r['openername']::integer AS openername,
r['historylength']::smallint AS historylength,
r['browserlanguage']::text AS browserlanguage,
r['browsercountry']::text AS browsercountry,
r['socialnetwork']::text AS socialnetwork,
r['socialaction']::text AS socialaction,
r['httperror']::smallint AS httperror,
r['sendtiming']::integer AS sendtiming,
r['dnstiming']::integer AS dnstiming,
r['connecttiming']::integer AS connecttiming,
r['responsestarttiming']::integer AS responsestarttiming,
r['responseendtiming']::integer AS responseendtiming,
r['fetchtiming']::integer AS fetchtiming,
r['socialsourcenetworkid']::smallint AS socialsourcenetworkid,
r['socialsourcepage']::text AS socialsourcepage,
r['paramprice']::bigint AS paramprice,
r['paramorderid']::text AS paramorderid,
r['paramcurrency']::text AS paramcurrency,
r['paramcurrencyid']::smallint AS paramcurrencyid,
r['openstatservicename']::text AS openstatservicename,
r['openstatcampaignid']::text AS openstatcampaignid,
r['openstatadid']::text AS openstatadid,
r['openstatsourceid']::text AS openstatsourceid,
r['utmsource']::text AS utmsource,
r['utmmedium']::text AS utmmedium,
r['utmcampaign']::text AS utmcampaign,
r['utmcontent']::text AS utmcontent,
r['utmterm']::text AS utmterm,
r['fromtag']::text AS fromtag,
r['hasgclid']::smallint AS hasgclid,
r['refererhash']::bigint AS refererhash,
r['urlhash']::bigint AS urlhash,
r['clid']::integer AS clid from
read_parquet('/home/test/clickbench/hits.parquet', binary_as_string => true) r;
Table C.10. Test Data
|
Data |
Total size |
|---|---|
|
Analytical tables in the CSV format |
71.3 GB |
|
Analytical tables in the Parquet format |
9.3 GB |
|
Database |
66 GB |
Table C.11. Table Information
|
Analytical Table |
Number of Rows |
|---|---|
|
|
99 997 497 |
Table C.12. Test Results
|
Query |
Duration (s) |
|
|
|
|
|
|
|---|---|---|---|---|---|---|---|
|
Q1 |
0.63 |
0.45 |
0.07 |
125.90 |
77.85 |
31.49 |
0.00 |
|
Q1 — second execution |
0.47 |
0.39 |
0.04 |
73.11 |
71.41 |
0.00 |
0.00 |
|
Q2 |
0.79 |
0.56 |
0.11 |
158.62 |
89.55 |
36.81 |
0.00 |
|
Q2 — second execution |
0.53 |
0.36 |
0.07 |
135.46 |
92.99 |
0.00 |
0.00 |
|
Q3 |
0.91 |
1.23 |
0.20 |
167.48 |
85.73 |
131.00 |
0.00 |
|
Q3 — second execution |
0.60 |
0.98 |
0.10 |
148.55 |
110.61 |
0.00 |
0.00 |
|
Q4 |
1.00 |
0.81 |
0.27 |
220.86 |
112.01 |
284.62 |
0.00 |
|
Q4 — second execution |
0.59 |
0.92 |
0.13 |
136.31 |
101.65 |
0.00 |
0.00 |
|
Q5 |
1.20 |
3.14 |
0.70 |
708.39 |
216.61 |
382.22 |
0.00 |
|
Q5 — second execution |
0.92 |
2.86 |
0.48 |
620.24 |
209.80 |
0.00 |
0.00 |
|
Q6 |
1.50 |
4.54 |
0.93 |
1163.14 |
486.90 |
479.83 |
0.00 |
|
Q6 — second execution |
1.16 |
4.55 |
0.75 |
954.81 |
361.70 |
0.00 |
0.00 |
|
Q7 |
0.77 |
0.77 |
0.14 |
144.44 |
88.64 |
48.86 |
0.00 |
|
Q7 — second execution |
0.57 |
0.35 |
0.08 |
137.39 |
90.48 |
0.00 |
0.00 |
|
Q8 |
0.70 |
0.43 |
0.08 |
155.98 |
84.44 |
33.83 |
0.00 |
|
Q8 — second execution |
0.57 |
0.66 |
0.08 |
143.61 |
102.05 |
0.00 |
0.00 |
|
Q9 |
1.43 |
3.44 |
0.74 |
747.97 |
321.12 |
489.98 |
0.00 |
|
Q9 — second execution |
1.02 |
3.87 |
0.49 |
605.55 |
290.13 |
0.00 |
0.00 |
|
Q10 |
1.68 |
4.81 |
0.91 |
707.90 |
302.82 |
587.95 |
0.00 |
|
Q10 — second execution |
1.20 |
4.67 |
0.54 |
712.61 |
317.29 |
0.00 |
0.00 |
|
Q11 |
1.11 |
1.32 |
0.43 |
124.87 |
33.22 |
342.63 |
0.00 |
|
Q11 — second execution |
0.68 |
0.82 |
0.13 |
163.49 |
98.62 |
0.00 |
0.00 |
|
Q12 |
1.14 |
1.55 |
0.46 |
317.14 |
144.63 |
348.34 |
0.00 |
|
Q12 — second execution |
0.69 |
0.88 |
0.15 |
92.59 |
22.58 |
0.00 |
0.00 |
|
Q13 |
1.46 |
4.85 |
1.03 |
1260.84 |
523.72 |
482.57 |
0.00 |
|
Q13 — second execution |
1.18 |
4.69 |
0.78 |
1129.12 |
486.61 |
0.00 |
0.00 |
|
Q14 |
2.04 |
7.44 |
1.51 |
1312.48 |
615.04 |
828.38 |
0.00 |
|
Q14 — second execution |
1.52 |
6.58 |
1.04 |
1338.21 |
582.66 |
0.00 |
0.00 |
|
Q15 |
1.62 |
5.31 |
1.05 |
1262.77 |
530.42 |
494.15 |
0.00 |
|
Q15 — second execution |
1.21 |
4.76 |
0.79 |
1167.94 |
527.60 |
0.00 |
0.00 |
|
Q16 |
1.26 |
2.82 |
0.78 |
745.76 |
198.17 |
384.04 |
0.00 |
|
Q16 — second execution |
0.95 |
2.69 |
0.54 |
770.43 |
240.03 |
0.00 |
0.00 |
|
Q17 |
2.14 |
7.74 |
1.90 |
2271.80 |
988.01 |
829.14 |
0.00 |
|
Q17 — second execution |
1.70 |
8.43 |
1.36 |
2292.72 |
1122.14 |
0.00 |
0.00 |
|
Q18 |
2.13 |
7.77 |
1.79 |
2191.19 |
940.75 |
831.77 |
0.00 |
|
Q18 — second execution |
1.71 |
8.45 |
1.44 |
2184.30 |
1042.97 |
0.00 |
0.00 |
|
Q19 |
3.17 |
14.12 |
3.17 |
3353.14 |
1553.31 |
1349.53 |
0.00 |
|
Q19 — second execution |
2.54 |
13.22 |
2.43 |
3812.80 |
1997.06 |
0.00 |
0.00 |
|
Q20 |
0.88 |
0.92 |
0.34 |
132.50 |
71.91 |
350.26 |
0.00 |
|
Q20 — second execution |
0.58 |
0.86 |
0.11 |
78.63 |
41.52 |
0.00 |
0.00 |
|
Q21 |
5.71 |
35.14 |
1.80 |
528.65 |
295.37 |
2665.79 |
0.00 |
|
Q21 — second execution |
4.96 |
34.58 |
0.84 |
765.54 |
507.69 |
0.00 |
0.00 |
|
Q22 |
4.12 |
17.66 |
2.49 |
1280.60 |
787.99 |
3064.32 |
0.00 |
|
Q22 — second execution |
2.77 |
16.86 |
1.14 |
1043.66 |
667.71 |
0.00 |
0.00 |
|
Q23 |
5.65 |
25.08 |
3.60 |
1052.83 |
760.07 |
4476.53 |
0.00 |
|
Q23 — second execution |
3.73 |
23.60 |
1.80 |
806.15 |
525.07 |
61.22 |
0.00 |
|
Q24 |
2.27 |
10.19 |
0.70 |
353.58 |
224.77 |
1040.53 |
0.00 |
|
Q24 — second execution |
1.90 |
10.03 |
0.32 |
253.96 |
177.31 |
0.00 |
0.00 |
|
Q25 |
0.79 |
0.88 |
0.23 |
200.04 |
83.95 |
151.16 |
0.00 |
|
Q25 — second execution |
0.65 |
0.63 |
0.12 |
176.91 |
100.47 |
0.43 |
0.00 |
|
Q26 |
1.17 |
2.49 |
0.38 |
220.63 |
108.22 |
388.89 |
0.00 |
|
Q26 — second execution |
0.86 |
2.96 |
0.17 |
220.31 |
128.92 |
0.00 |
0.00 |
|
Q27 |
0.89 |
0.89 |
0.22 |
174.16 |
53.80 |
164.66 |
0.00 |
|
Q27 — second execution |
0.65 |
0.93 |
0.11 |
140.21 |
91.47 |
0.00 |
0.00 |
|
Q28 |
3.66 |
13.22 |
2.22 |
1306.91 |
794.35 |
2635.39 |
0.00 |
|
Q28 — second execution |
2.24 |
12.38 |
1.06 |
1003.94 |
573.61 |
0.00 |
0.00 |
|
Q29 |
12.23 |
82.03 |
3.60 |
1164.64 |
638.60 |
2695.30 |
0.00 |
|
Q29 — second execution |
11.47 |
81.52 |
2.62 |
1625.54 |
1002.23 |
0.00 |
0.00 |
|
Q30 |
0.78 |
0.83 |
0.20 |
149.80 |
88.79 |
116.46 |
0.00 |
|
Q30 — second execution |
0.58 |
0.37 |
0.06 |
51.00 |
18.07 |
0.00 |
0.00 |
|
Q31 |
2.03 |
5.78 |
1.22 |
701.85 |
369.72 |
1021.30 |
0.00 |
|
Q31 — second execution |
1.26 |
4.69 |
0.53 |
603.29 |
274.75 |
0.00 |
0.00 |
|
Q32 |
3.33 |
5.87 |
2.15 |
757.51 |
366.02 |
2516.53 |
0.00 |
|
Q32 — second execution |
1.25 |
4.67 |
0.79 |
895.50 |
376.31 |
0.00 |
0.00 |
|
Q33 |
4.08 |
12.12 |
4.79 |
5902.10 |
3058.48 |
2069.20 |
0.00 |
|
Q33 — second execution |
2.58 |
12.02 |
4.27 |
5291.00 |
2890.68 |
0.00 |
0.00 |
|
Q34 |
4.17 |
17.30 |
5.64 |
5521.45 |
2674.25 |
2648.54 |
0.00 |
|
Q34 — second execution |
3.19 |
16.57 |
4.18 |
5923.27 |
2989.65 |
0.00 |
0.00 |
|
Q35 |
4.36 |
17.58 |
6.10 |
5529.96 |
2811.14 |
2648.79 |
0.00 |
|
Q35 — second execution |
3.30 |
16.83 |
4.73 |
5503.18 |
2698.39 |
0.00 |
0.00 |
|
Q36 |
1.15 |
3.17 |
0.79 |
352.95 |
99.13 |
221.50 |
0.00 |
|
Q36 — second execution |
1.07 |
3.51 |
0.67 |
838.83 |
285.11 |
0.00 |
0.00 |
|
Q37 |
0.90 |
0.98 |
0.52 |
353.64 |
116.84 |
208.02 |
0.00 |
|
Q37 — second execution |
0.64 |
0.65 |
0.26 |
186.30 |
63.08 |
0.00 |
0.00 |
|
Q38 |
0.89 |
0.91 |
0.41 |
118.58 |
6.67 |
154.77 |
0.00 |
|
Q38 — second execution |
0.61 |
0.71 |
0.23 |
119.66 |
2.78 |
0.00 |
0.00 |
|
Q39 |
0.87 |
0.65 |
0.25 |
265.94 |
42.13 |
103.84 |
0.00 |
|
Q39 — second execution |
0.62 |
0.66 |
0.23 |
245.41 |
88.15 |
0.02 |
0.00 |
|
Q40 |
1.01 |
1.04 |
0.56 |
394.09 |
110.50 |
210.13 |
0.00 |
|
Q40 — second execution |
0.74 |
1.33 |
0.61 |
793.19 |
283.12 |
0.00 |
0.00 |
|
Q41 |
0.76 |
0.59 |
0.21 |
203.92 |
89.32 |
107.98 |
0.00 |
|
Q41 — second execution |
0.57 |
0.36 |
0.07 |
117.24 |
81.53 |
0.00 |
0.00 |
|
Q42 |
0.82 |
0.58 |
0.18 |
136.13 |
61.56 |
72.99 |
0.00 |
|
Q42 — second execution |
0.58 |
0.60 |
0.12 |
99.95 |
55.43 |
0.00 |
0.00 |
|
Q43 |
0.88 |
0.64 |
0.18 |
243.94 |
110.67 |
80.21 |
0.00 |
|
Q43 — second execution |
0.63 |
0.47 |
0.08 |
135.68 |
97.21 |
0.00 |
0.00 |