ClickHouse OLAP数据库系统详解

需积分: 10 23 浏览量更新于2024-07-09 收藏 8MB PDF 举报

"clickhouse_zh.pdf - ClickHouse 20版本中文文档，列式数据库管理系统(DBMS)，适用于OLAP分析" ClickHouse是一个高效且高性能的列式数据库管理系统（DBMS），设计主要用于在线分析处理（OLAP）场景。与传统的行式数据库系统不同，ClickHouse采用列式存储方式，优化了大数据分析的性能。在行式数据库中，例如MySQL、Postgres和MSSQL Server，数据按行组织，每一行的各个字段紧密相邻，适合于大量插入和修改操作，但在读取多列进行分析时效率较低。相反，ClickHouse将数据按列存储。例如，对于上述的示例数据，同一列的数据会被物理地存储在一起，即使在不同的行中。这样，在执行分析查询时，只需读取所需列的数据，显著减少了I/O操作，提升了查询速度。列式存储特别适合于需要快速分析大量数据，但对数据修改需求不频繁的情况，如日志分析、数据仓库和报告生成等。常见的列式数据库系统还包括Vertica、Amazon Redshift、Sybase IQ等。选择行式还是列式数据库主要取决于业务需求，如查询类型、查询频率、数据读取量、更新操作比例、数据集大小、事务处理、延迟与吞吐量要求，以及数据复制和完整性策略等。在高负载环境中，ClickHouse展现出其优势，能够处理大量并发查询并提供快速响应。它支持多种数据类型，包括数值、字符串、日期和时间，以及复杂的结构化数据。此外，ClickHouse提供了丰富的SQL接口，允许用户使用熟悉的SQL语法进行查询和数据操作，同时支持分布式处理，可以通过创建分片和复制来扩展其处理能力。 ClickHouse的另一个关键特性是其压缩能力，通过压缩数据来减少存储需求，进一步提高读取效率。同时，它支持索引，尽管主要是针对列式数据的主键，但也提供了一定程度的非主键列索引功能，以增强查询性能。 ClickHouse是面向大数据分析的优秀解决方案，尤其适用于实时或近乎实时的分析场景，它的列式存储、高效查询、可扩展性和压缩能力使其在OLAP领域脱颖而出。在设计和部署数据分析系统时，理解并考虑ClickHouse的特点和适用场景，可以显著提升数据分析的效率和性能。

从TSV压缩文件获取表

从TSV压缩文件下载并导入hits:

curl https://datasets.clickhouse.tech/hits/tsv/hits_v1.tsv.xz | unxz --threads=`nproc` > hits_v1.tsv

## now create table

clickhouse-client --query "CREATE DATABASE IF NOT EXISTS datasets"

clickhouse-client --query "CREATE TABLE datasets.hits_v1 ( WatchID UInt64, JavaEnable UInt8, Title String, GoodEvent Int16, EventTime DateTime, EventDate Date,

CounterID UInt32, ClientIP UInt32, ClientIP6 FixedString(16), RegionID UInt32, UserID UInt64, CounterClass Int8, OS UInt8, UserAgent UInt8, URL String, Referer String,

URLDomain String, RefererDomain String, Refresh UInt8, IsRobot UInt8, RefererCategories Array(UInt16), URLCategories Array(UInt16), URLRegions Array(UInt32),

RefererRegions Array(UInt32), ResolutionWidth UInt16, ResolutionHeight UInt16, ResolutionDepth UInt8, FlashMajor UInt8, FlashMinor UInt8, FlashMinor2 String, NetMajor

UInt8, NetMinor UInt8, UserAgentMajor UInt16, UserAgentMinor FixedString(2), CookieEnable UInt8, JavascriptEnable UInt8, IsMobile UInt8, MobilePhone UInt8,

MobilePhoneModel String, Params String, IPNetworkID UInt32, TraficSourceID Int8, SearchEngineID UInt16, SearchPhrase String, AdvEngineID UInt8, IsArtifical UInt8,

WindowClientWidth UInt16, WindowClientHeight UInt16, ClientTimeZone Int16, ClientEventTime DateTime, SilverlightVersion1 UInt8, SilverlightVersion2 UInt8,

SilverlightVersion3 UInt32, SilverlightVersion4 UInt16, PageCharset String, CodeVersion UInt32, IsLink UInt8, IsDownload UInt8, IsNotBounce UInt8, FUniqID UInt64, HID

UInt32, IsOldCounter UInt8, IsEvent UInt8, IsParameter UInt8, DontCountHits UInt8, WithHash UInt8, HitColor FixedString(1), UTCEventTime DateTime, Age UInt8, Sex

UInt8, Income UInt8, Interests UInt16, Robotness UInt8, GeneralInterests Array(UInt16), RemoteIP UInt32, RemoteIP6 FixedString(16), WindowName Int32, OpenerName

Int32, HistoryLength Int16, BrowserLanguage FixedString(2), BrowserCountry FixedString(2), SocialNetwork String, SocialAction String, HTTPError UInt16, SendTiming

Int32, DNSTiming Int32, ConnectTiming Int32, ResponseStartTiming Int32, ResponseEndTiming Int32, FetchTiming Int32, RedirectTiming Int32, DOMInteractiveTiming

Int32, DOMContentLoadedTiming Int32, DOMCompleteTiming Int32, LoadEventStartTiming Int32, LoadEventEndTiming Int32, NSToDOMContentLoadedTiming Int32,

FirstPaintTiming Int32, RedirectCount Int8, SocialSourceNetworkID UInt8, SocialSourcePage String, ParamPrice Int64, ParamOrderID String, ParamCurrency FixedString(3),

ParamCurrencyID UInt16, GoalsReached Array(UInt32), OpenstatServiceName String, OpenstatCampaignID String, OpenstatAdID String, OpenstatSourceID String,

UTMSource String, UTMMedium String, UTMCampaign String, UTMContent String, UTMTerm String, FromTag String, HasGCLID UInt8, RefererHash UInt64, URLHash

UInt64, CLID UInt32, YCLID UInt64, ShareService String, ShareURL String, ShareTitle String, ParsedParams Nested(Key1 String, Key2 String, Key3 String, Key4 String,

Key5 String, ValueDouble Float64), IslandID FixedString(16), RequestNum UInt32, RequestTry UInt8) ENGINE = MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER

BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192"

## import data

cat hits_v1.tsv | clickhouse-client --query "INSERT INTO datasets.hits_v1 FORMAT TSV" --max_insert_block_size=100000

## optionally you can optimize table

clickhouse-client --query "OPTIMIZE TABLE datasets.hits_v1 FINAL"

clickhouse-client --query "SELECT COUNT(*) FROM datasets.hits_v1"

从压缩tsv文件下载和导入visits:

curl https://datasets.clickhouse.tech/visits/tsv/visits_v1.tsv.xz | unxz --threads=`nproc` > visits_v1.tsv

## now create table

clickhouse-client --query "CREATE DATABASE IF NOT EXISTS datasets"

clickhouse-client --query "CREATE TABLE datasets.visits_v1 ( CounterID UInt32, StartDate Date, Sign Int8, IsNew UInt8, VisitID UInt64, UserID UInt64, StartTime DateTime,

Duration UInt32, UTCStartTime DateTime, PageViews Int32, Hits Int32, IsBounce UInt8, Referer String, StartURL String, RefererDomain String, StartURLDomain String,

EndURL String, LinkURL String, IsDownload UInt8, TraficSourceID Int8, SearchEngineID UInt16, SearchPhrase String, AdvEngineID UInt8, PlaceID Int32,

RefererCategories Array(UInt16), URLCategories Array(UInt16), URLRegions Array(UInt32), RefererRegions Array(UInt32), IsYandex UInt8, GoalReachesDepth Int32,

GoalReachesURL Int32, GoalReachesAny Int32, SocialSourceNetworkID UInt8, SocialSourcePage String, MobilePhoneModel String, ClientEventTime DateTime, RegionID

UInt32, ClientIP UInt32, ClientIP6 FixedString(16), RemoteIP UInt32, RemoteIP6 FixedString(16), IPNetworkID UInt32, SilverlightVersion3 UInt32, CodeVersion UInt32,

ResolutionWidth UInt16, ResolutionHeight UInt16, UserAgentMajor UInt16, UserAgentMinor UInt16, WindowClientWidth UInt16, WindowClientHeight UInt16,

SilverlightVersion2 UInt8, SilverlightVersion4 UInt16, FlashVersion3 UInt16, FlashVersion4 UInt16, ClientTimeZone Int16, OS UInt8, UserAgent UInt8, ResolutionDepth

UInt8, FlashMajor UInt8, FlashMinor UInt8, NetMajor UInt8, NetMinor UInt8, MobilePhone UInt8, SilverlightVersion1 UInt8, Age UInt8, Sex UInt8, Income UInt8,

JavaEnable UInt8, CookieEnable UInt8, JavascriptEnable UInt8, IsMobile UInt8, BrowserLanguage UInt16, BrowserCountry UInt16, Interests UInt16, Robotness UInt8,

GeneralInterests Array(UInt16), Params Array(String), Goals Nested(ID UInt32, Serial UInt32, EventTime DateTime, Price Int64, OrderID String, CurrencyID UInt32),

WatchIDs Array(UInt64), ParamSumPrice Int64, ParamCurrency FixedString(3), ParamCurrencyID UInt16, ClickLogID UInt64, ClickEventID Int32, ClickGoodEvent Int32,

ClickEventTime DateTime, ClickPriorityID Int32, ClickPhraseID Int32, ClickPageID Int32, ClickPlaceID Int32, ClickTypeID Int32, ClickResourceID Int32, ClickCost UInt32,

ClickClientIP UInt32, ClickDomainID UInt32, ClickURL String, ClickAttempt UInt8, ClickOrderID UInt32, ClickBannerID UInt32, ClickMarketCategoryID UInt32, ClickMarketPP

UInt32, ClickMarketCategoryName String, ClickMarketPPName String, ClickAWAPSCampaignName String, ClickPageName String, ClickTargetType UInt16,

ClickTargetPhraseID UInt64, ClickContextType UInt8, ClickSelectType Int8, ClickOptions String, ClickGroupBannerID Int32, OpenstatServiceName String,

OpenstatCampaignID String, OpenstatAdID String, OpenstatSourceID String, UTMSource String, UTMMedium String, UTMCampaign String, UTMContent String, UTMTerm

String, FromTag String, HasGCLID UInt8, FirstVisit DateTime, PredLastVisit Date, LastVisit Date, TotalVisits UInt32, TraficSource Nested(ID Int8, SearchEngineID UInt16,

AdvEngineID UInt8, PlaceID UInt16, SocialSourceNetworkID UInt8, Domain String, SearchPhrase String, SocialSourcePage String), Attendance FixedString(16), CLID UInt32,

YCLID UInt64, NormalizedRefererHash UInt64, SearchPhraseHash UInt64, RefererDomainHash UInt64, NormalizedStartURLHash UInt64, StartURLDomainHash UInt64,

NormalizedEndURLHash UInt64, TopLevelDomain UInt64, URLScheme UInt64, OpenstatServiceNameHash UInt64, OpenstatCampaignIDHash UInt64, OpenstatAdIDHash

UInt64, OpenstatSourceIDHash UInt64, UTMSourceHash UInt64, UTMMediumHash UInt64, UTMCampaignHash UInt64, UTMContentHash UInt64, UTMTermHash UInt64,

FromHash UInt64, WebVisorEnabled UInt8, WebVisorActivity UInt32, ParsedParams Nested(Key1 String, Key2 String, Key3 String, Key4 String, Key5 String, ValueDouble

Float64), Market Nested(Type UInt8, GoalID UInt32, OrderID String, OrderPrice Int64, PP UInt32, DirectPlaceID UInt32, DirectOrderID UInt32, DirectBannerID UInt32,

GoodID String, GoodName String, GoodQuantity Int32, GoodPrice Int64), IslandID FixedString(16)) ENGINE = CollapsingMergeTree(Sign) PARTITION BY

toYYYYMM(StartDate) ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192"

## import data

cat visits_v1.tsv | clickhouse-client --query "INSERT INTO datasets.visits_v1 FORMAT TSV" --max_insert_block_size=100000

## optionally you can optimize table

clickhouse-client --query "OPTIMIZE TABLE datasets.visits_v1 FINAL"

clickhouse-client --query "SELECT COUNT(*) FROM datasets.visits_v1"

查询示例

使用教程是以Yandex.Metrica数据集开始教程。

可以在ClickHouse的stateful tests 中找到对这些表的查询的其他示例(它们被命名为test.hists和test.visits)。

Star Schema Benchmark

编译 dbgen:

$ git clone git@github.com:vadimtk/ssb-dbgen.git

$ cd ssb-dbgen

$ make

开始生成数据：

注意

使用-s 100dbgen将生成6亿行数据(67GB), 如果使用-s 1000它会生成60亿行数据(这需要很多时间))

$ ./dbgen -s 1000 -T c

$ ./dbgen -s 1000 -T l

$ ./dbgen -s 1000 -T p

$ ./dbgen -s 1000 -T s

$ ./dbgen -s 1000 -T d

在ClickHouse中创建数据表：

CREATE TABLE customer

(

C_CUSTKEY UInt32,

C_NAME String,

C_ADDRESS String,

C_CITY LowCardinality(String),

C_NATION LowCardinality(String),

C_REGION LowCardinality(String),

C_PHONE String,

C_MKTSEGMENT LowCardinality(String)

)

ENGINE = MergeTree ORDER BY (C_CUSTKEY);

CREATE TABLE lineorder

(

LO_ORDERKEY UInt32,

LO_LINENUMBER UInt8,

LO_CUSTKEY UInt32,

LO_PARTKEY UInt32,

LO_SUPPKEY UInt32,

LO_ORDERDATE Date,

LO_ORDERPRIORITY LowCardinality(String),

LO_SHIPPRIORITY UInt8,

LO_QUANTITY UInt8,

LO_EXTENDEDPRICE UInt32,

LO_ORDTOTALPRICE UInt32,

LO_DISCOUNT UInt8,

LO_REVENUE UInt32,

LO_SUPPLYCOST UInt32,

LO_TAX UInt8,

LO_COMMITDATE Date,

LO_SHIPMODE LowCardinality(String)

)

ENGINE = MergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);

CREATE TABLE part

(

P_PARTKEY UInt32,

P_NAME String,

P_MFGR LowCardinality(String),

P_CATEGORY LowCardinality(String),

P_BRAND LowCardinality(String),

P_COLOR LowCardinality(String),

P_TYPE LowCardinality(String),

P_SIZE UInt8,

P_CONTAINER LowCardinality(String)

)

ENGINE = MergeTree ORDER BY P_PARTKEY;

CREATE TABLE supplier

(

S_SUPPKEY UInt32,

S_NAME String,

S_ADDRESS String,

S_CITY LowCardinality(String),

S_NATION LowCardinality(String),

S_REGION LowCardinality(String),

S_PHONE String

)

ENGINE = MergeTree ORDER BY S_SUPPKEY;

写入数据：

$ clickhouse-client --query "INSERT INTO customer FORMAT CSV" < customer.tbl

$ clickhouse-client --query "INSERT INTO part FORMAT CSV" < part.tbl

$ clickhouse-client --query "INSERT INTO supplier FORMAT CSV" < supplier.tbl

$ clickhouse-client --query "INSERT INTO lineorder FORMAT CSV" < lineorder.tbl

将star schema转换为flat schema：

SET max_memory_usage = 20000000000, allow_experimental_multiple_joins_emulation = 1;

CREATE TABLE lineorder_flat

ENGINE = MergeTree

PARTITION BY toYear(LO_ORDERDATE)

ORDER BY (LO_ORDERDATE, LO_ORDERKEY) AS

SELECT l.*, c.*, s.*, p.*

FROM lineorder l

ANY INNER JOIN customer c ON (c.C_CUSTKEY = l.LO_CUSTKEY)

ANY INNER JOIN supplier s ON (s.S_SUPPKEY = l.LO_SUPPKEY)

ANY INNER JOIN part p ON (p.P_PARTKEY = l.LO_PARTKEY);

ALTER TABLE lineorder_flat DROP COLUMN C_CUSTKEY, DROP COLUMN S_SUPPKEY, DROP COLUMN P_PARTKEY;

运行查询:

Q1.1

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toYear(LO_ORDERDATE) = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND

LO_QUANTITY < 25;

Q1.2

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toYYYYMM(LO_ORDERDATE) = 199401 AND LO_DISCOUNT BETWEEN 4 AND 6

AND LO_QUANTITY BETWEEN 26 AND 35;

Q1.3

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toISOWeek(LO_ORDERDATE) = 6 AND toYear(LO_ORDERDATE) = 1994 AND

LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;

Q2.1

SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND FROM lineorder_flat WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA' GROUP BY

year, P_BRAND ORDER BY year, P_BRAND;

Q2.2

SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND FROM lineorder_flat WHERE P_BRAND BETWEEN 'MFGR#2221' AND 'MFGR#2228' AND

S_REGION = 'ASIA' GROUP BY year, P_BRAND ORDER BY year, P_BRAND;

Q2.3

SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND FROM lineorder_flat WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE' GROUP BY

year, P_BRAND ORDER BY year, P_BRAND;

Q3.1

SELECT C_NATION, S_NATION, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA'

AND year >= 1992 AND year <= 1997 GROUP BY C_NATION, S_NATION, year ORDER BY year asc, revenue desc;

Q3.2

SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE C_NATION = 'UNITED STATES' AND S_NATION =

'UNITED STATES' AND year >= 1992 AND year <= 1997 GROUP BY C_CITY, S_CITY, year ORDER BY year asc, revenue desc;

Q3.3

SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5')

AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND year >= 1992 AND year <= 1997 GROUP BY C_CITY, S_CITY, year ORDER BY year asc, revenue desc;

Q3.4

SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5')

AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND toYYYYMM(LO_ORDERDATE) = '199712' GROUP BY C_CITY, S_CITY, year ORDER BY year asc, revenue desc;

Q4.1

SELECT toYear(LO_ORDERDATE) AS year, C_NATION, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE C_REGION = 'AMERICA' AND S_REGION =

'AMERICA' AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2') GROUP BY year, C_NATION ORDER BY year, C_NATION;

Q4.2

SELECT toYear(LO_ORDERDATE) AS year, S_NATION, P_CATEGORY, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE C_REGION = 'AMERICA'

AND S_REGION = 'AMERICA' AND (year = 1997 OR year = 1998) AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2') GROUP BY year, S_NATION, P_CATEGORY ORDER

BY year, S_NATION, P_CATEGORY;

Q4.3

SELECT toYear(LO_ORDERDATE) AS year, S_CITY, P_BRAND, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE S_NATION = 'UNITED STATES'

AND (year = 1997 OR year = 1998) AND P_CATEGORY = 'MFGR#14' GROUP BY year, S_CITY, P_BRAND ORDER BY year, S_CITY, P_BRAND;

原始文章

WikiStat

参考: http://dumps.wikimedia.org/other/pagecounts-raw/

创建表结构：

CREATE TABLE wikistat

(

date Date,

time DateTime,

project String,

subproject String,

path String,

hits UInt64,

size UInt64

) ENGINE = MergeTree(date, (path, time), 8192);

加载数据：

$ for i in {2007..2016}; do for j in {01..12}; do echo $i-$j >&2; curl -sSL "http://dumps.wikimedia.org/other/pagecounts-raw/$i/$i-$j/" | grep -oE 'pagecounts-[0-9]+-[0-

9]+\.gz'; done; done | sort | uniq | tee links.txt

$ cat links.txt | while read link; do wget http://dumps.wikimedia.org/other/pagecounts-raw/$(echo $link | sed -r 's/pagecounts-([0-9]{4})([0-9]{2})[0-9]{2}-[0-

9]+\.gz/\1/')/$(echo $link | sed -r 's/pagecounts-([0-9]{4})([0-9]{2})[0-9]{2}-[0-9]+\.gz/\1-\2/')/$link; done

$ ls -1 /opt/wikistat/ | grep gz | while read i; do echo $i; gzip -cd /opt/wikistat/$i | ./wikistat-loader --time="$(echo -n $i | sed -r 's/pagecounts-([0-9]{4})([0-9]{2})([0-9]{2})-

([0-9]{2})([0-9]{2})([0-9]{2})\.gz/\1-\2-\3 \4-00-00/')" | clickhouse-client --query="INSERT INTO wikistat FORMAT TabSeparated"; done

原始文章

Terabyte of Click Logs from Criteo

可以从 http://labs.criteo.com/downloads/download-terabyte-click-logs/ 上下载数据

创建原始数据对应的表结构：

CREATE TABLE criteo_log (date Date, clicked UInt8, int1 Int32, int2 Int32, int3 Int32, int4 Int32, int5 Int32, int6 Int32, int7 Int32, int8 Int32, int9 Int32, int10 Int32, int11 Int32,

int12 Int32, int13 Int32, cat1 String, cat2 String, cat3 String, cat4 String, cat5 String, cat6 String, cat7 String, cat8 String, cat9 String, cat10 String, cat11 String, cat12 String,

cat13 String, cat14 String, cat15 String, cat16 String, cat17 String, cat18 String, cat19 String, cat20 String, cat21 String, cat22 String, cat23 String, cat24 String, cat25 String,

cat26 String) ENGINE = Log

下载数据：

$ for i in {00..23}; do echo $i; zcat datasets/criteo/day_${i#0}.gz | sed -r 's/^/2000-01-'${i/00/24}'\t/' | clickhouse-client --host=example-perftest01j --query="INSERT

INTO criteo_log FORMAT TabSeparated"; done

创建转换后的数据对应的表结构：

CREATE TABLE criteo

(

date Date,

clicked UInt8,

int1 Int32,

int2 Int32,

int3 Int32,

int4 Int32,

int5 Int32,

int6 Int32,

int7 Int32,

int8 Int32,

int9 Int32,

int10 Int32,

int11 Int32,

int12 Int32,

int13 Int32,

icat1 UInt32,

icat2 UInt32,

icat3 UInt32,

icat4 UInt32,

icat5 UInt32,

icat6 UInt32,

icat7 UInt32,

icat8 UInt32,

icat9 UInt32,

icat10 UInt32,

icat11 UInt32,

icat12 UInt32,

icat13 UInt32,

icat14 UInt32,

icat15 UInt32,

icat16 UInt32,

icat17 UInt32,

icat18 UInt32,

icat19 UInt32,

icat20 UInt32,

icat21 UInt32,

icat22 UInt32,

icat23 UInt32,

icat24 UInt32,

icat25 UInt32,

icat26 UInt32

) ENGINE = MergeTree(date, intHash32(icat1), (date, intHash32(icat1)), 8192)

将第一张表中的原始数据转化写入到第二张表中去：

INSERT INTO criteo SELECT date, clicked, int1, int2, int3, int4, int5, int6, int7, int8, int9, int10, int11, int12, int13, reinterpretAsUInt32(unhex(cat1)) AS icat1,

reinterpretAsUInt32(unhex(cat2)) AS icat2, reinterpretAsUInt32(unhex(cat3)) AS icat3, reinterpretAsUInt32(unhex(cat4)) AS icat4, reinterpretAsUInt32(unhex(cat5)) AS

icat5, reinterpretAsUInt32(unhex(cat6)) AS icat6, reinterpretAsUInt32(unhex(cat7)) AS icat7, reinterpretAsUInt32(unhex(cat8)) AS icat8, reinterpretAsUInt32(unhex(cat9))

AS icat9, reinterpretAsUInt32(unhex(cat10)) AS icat10, reinterpretAsUInt32(unhex(cat11)) AS icat11, reinterpretAsUInt32(unhex(cat12)) AS icat12,

reinterpretAsUInt32(unhex(cat13)) AS icat13, reinterpretAsUInt32(unhex(cat14)) AS icat14, reinterpretAsUInt32(unhex(cat15)) AS icat15, reinterpretAsUInt32(unhex(cat16))

AS icat16, reinterpretAsUInt32(unhex(cat17)) AS icat17, reinterpretAsUInt32(unhex(cat18)) AS icat18, reinterpretAsUInt32(unhex(cat19)) AS icat19,

reinterpretAsUInt32(unhex(cat20)) AS icat20, reinterpretAsUInt32(unhex(cat21)) AS icat21, reinterpretAsUInt32(unhex(cat22)) AS icat22, reinterpretAsUInt32(unhex(cat23))

AS icat23, reinterpretAsUInt32(unhex(cat24)) AS icat24, reinterpretAsUInt32(unhex(cat25)) AS icat25, reinterpretAsUInt32(unhex(cat26)) AS icat26 FROM criteo_log;

DROP TABLE criteo_log;

原始文章

AMPLab Big Data Benchmark

参考 https://amplab.cs.berkeley.edu/benchmark/

需要您在Amazon注册一个免费的账号。注册时需要您提供信用卡、邮箱、电话等信息。之后可以在Amazon AWS Console获取新的访问密钥

在控制台运行以下命令：

$ sudo apt-get install s3cmd

$ mkdir tiny; cd tiny;

$ s3cmd sync s3://big-data-benchmark/pavlo/text-deflate/tiny/ .

$ cd ..

$ mkdir 1node; cd 1node;

$ s3cmd sync s3://big-data-benchmark/pavlo/text-deflate/1node/ .

$ cd ..

$ mkdir 5nodes; cd 5nodes;

$ s3cmd sync s3://big-data-benchmark/pavlo/text-deflate/5nodes/ .

$ cd ..

在ClickHouse运行如下查询：

CREATE TABLE rankings_tiny

(

pageURL String,

pageRank UInt32,

avgDuration UInt32

) ENGINE = Log;

CREATE TABLE uservisits_tiny

(

sourceIP String,

destinationURL String,

visitDate Date,

adRevenue Float32,

UserAgent String,

cCode FixedString(3),

lCode FixedString(6),

searchWord String,

duration UInt32

) ENGINE = MergeTree(visitDate, visitDate, 8192);

CREATE TABLE rankings_1node

(

pageURL String,

pageRank UInt32,

avgDuration UInt32

) ENGINE = Log;

CREATE TABLE uservisits_1node

(

sourceIP String,

destinationURL String,

visitDate Date,

adRevenue Float32,

UserAgent String,

cCode FixedString(3),

lCode FixedString(6),

searchWord String,

duration UInt32

) ENGINE = MergeTree(visitDate, visitDate, 8192);

CREATE TABLE rankings_5nodes_on_single

(

pageURL String,

pageRank UInt32,

avgDuration UInt32

) ENGINE = Log;

CREATE TABLE uservisits_5nodes_on_single

(

sourceIP String,

destinationURL String,

visitDate Date,

adRevenue Float32,

UserAgent String,

cCode FixedString(3),

lCode FixedString(6),

searchWord String,

duration UInt32

) ENGINE = MergeTree(visitDate, visitDate, 8192);

回到控制台运行如下命令：

剩余456页未读，继续阅读

迈克尔·杰克糖

粉丝: 39
资源: 9

ClickHouse OLAP数据库系统详解

clickhouse_zh.pdf（clickhouse-中文文档）

ac040_zh_co62_fv_part_a4.pdf

githubdesktop_zh.zip

resources_zh_cn_intellijidea_2019.3_r1.jar

docker启动gitlab_zh.tar

中英机器翻译'data/wordmap_zh.json

lang_zh_cn.xml组件下载

ocr_zh-cn_1.6.0.117_4.zip

languagepack_1.11.0.28416-x64-zh_cn.msi

ocr_zh-cn_1.6.0.117_4

最新资源