clickhouse导入官方测试数据及SQL初体验

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

版权声明：原创作品，谢绝转载！否则将追究法律责任。

目录

一.yandex官方测试数据导入

1.创建表yandex测试表结构

1.1 创建数据库

	1.使用"--query"可以免交互执行SQL语句，有点类似于MySQL的"-e"选项
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --query "CREATE DATABASE IF NOT EXISTS tutorial"


	2.查看数据库列表
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --query "SHOW DATABASES"
INFORMATION_SCHEMA
default
information_schema
system
tutorial
[root@node-exporter42 ~]#

1.2 创建hits_v1表结构(教程，点击表)

CREATE TABLE tutorial.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;

1.3 创建visits_v1表结果(教程，访问表)

CREATE TABLE tutorial.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;

1.4 查看表信息

node-exporter42 :) SHOW TABLES;

SHOW TABLES

Query id: 6689011a-fe6f-4514-87ae-b69c4027d74c

   ┌─name──────┐
1. │ hits_v1   │
2. │ visits_v1 │
   └───────────┘

2 rows in set. Elapsed: 0.002 sec. 

node-exporter42 :)

2.导入yandex官方测试数据

2.1 下载官方的样例数据

https://clickhouse-datasets.s3.yandex.net/hits/tsv/hits_v1.tsv.xz
https://clickhouse-datasets.s3.yandex.net/visits/tsv/visits_v1.tsv.xz

2.2 查看文件类型

[root@node-exporter42 ~]# file visits_v1.tsv.xz 
visits_v1.tsv.xz: XZ compressed data, checksum CRC64
[root@node-exporter42 ~]# 
[root@node-exporter42 ~]# file hits_v1.tsv.xz 
hits_v1.tsv.xz: XZ compressed data, checksum CRC64
[root@node-exporter42 ~]#

2.3 基于unxz工具解压xz文件

[root@node-exporter42 ~]# unxz visits_v1.tsv.xz 
[root@node-exporter42 ~]# unxz hits_v1.tsv.xz

2.4 查看解压后的文件大小

[root@node-exporter42 ~]# ll -h hits_v1.tsv visits_v1.tsv 
-rw-r--r-- 1 root root 7.3G Jun  6 21:55 hits_v1.tsv
-rw-r--r-- 1 root root 2.5G Jun  6 21:55 visits_v1.tsv
[root@node-exporter42 ~]#

2.5 导入hits_v1表示数据

导入'hits_v1.tsv'文件的数据到tutorial数据库的hits_v1表，每次导入10w条数据。

[root@node-exporter42 ~]# time clickhouse-client --password yinzhengjie --query "INSERT INTO tutorial.hits_v1 FORMAT TSV" --max_insert_block_size=100000 < hits_v1.tsv

real	1m1.365s
user	0m48.848s
sys	0m11.385s
[root@node-exporter42 ~]#

2.6 导入visits_v1表示数据

导入'visits_v1.tsv'文件的数据到tutorial数据库的visits_v1表，每次导入10w条数据。


[root@node-exporter42 ~]# time clickhouse-client --password yinzhengjie --query "INSERT INTO tutorial.visits_v1 FORMAT TSV" --max_insert_block_size=100000 < visits_v1.tsv

real	0m25.841s
user	0m14.250s
sys	0m3.906s
[root@node-exporter42 ~]#

二.clickhouse基本SQL初体验

1 统计表的数据条数

node-exporter42 :) SHOW TABLES FROM tutorial;

SHOW TABLES FROM tutorial

Query id: 3edd96c1-33ac-4d93-8074-f1a415a19c0e

   ┌─name──────┐
1. │ hits_v1   │
2. │ visits_v1 │
   └───────────┘

2 rows in set. Elapsed: 0.007 sec. 

node-exporter42 :) 
node-exporter42 :) SELECT count() FROM tutorial.visits_v1;

SELECT count()
FROM tutorial.visits_v1

Query id: 2e6c0512-ccff-4420-a6ce-c546e56a4a45

   ┌─count()─┐
1. │ 1681439 │ -- 1.68 million
   └─────────┘

1 row in set. Elapsed: 0.014 sec. 

node-exporter42 :) 
node-exporter42 :) SELECT count() FROM tutorial.hits_v1;

SELECT count()
FROM tutorial.hits_v1

Query id: 53e7715e-7474-459a-94f8-165ee52a24dc

   ┌─count()─┐
1. │ 8873898 │ -- 8.87 million
   └─────────┘

1 row in set. Elapsed: 0.008 sec. 

node-exporter42 :)

2.查询前几条数据

	1.查询1条数据
SELECT * FROM tutorial.visits_v1 LIMIT 1;\G


	2.查看指定列的前3条数据
SELECT * FROM tutorial.hits_v1 LIMIT 3;\G

3.聚合查询

查询条件为指定时间范围内的数据，根据URL进行聚合，统计平均的流量，并根据平均流量进行排序。

SQL语句如下:
SELECT
StartURL AS URL,
AVG(Duration) AS AvgDuration
FROM tutorial.visits_v1
WHERE StartDate BETWEEN '2014-03-23' AND '2014-03-30'
GROUP BY URL
ORDER BY AvgDuration DESC
LIMIT 10;\G

4.统计指定数据的访问百分比

统计SIGN(操作动作记录)的所有记录结果为visits(访问)，接着统计Sign的Goals.ID(目标ID)是1105530的数据量为oal_visits(访问次数)，然后将goal_visits乘以100再除visits即统计goal_vistits里面的百分比。

SQL语句如下:
SELECT
  sum(Sign) AS visits,
  sumIf(Sign, has(Goals.ID, 1105530)) AS goal_visits,
  (100. * goal_visits)/ visits AS goal_percent
FROM tutorial.visits_v1
WHERE (CounterID=912887) AND (toYYYYMM(StartDate)=201403) AND (domain(StartURL)='yandex.ru');

5.主键查询

找出主键ID并通过主键ID查询，官方的测试数据的主键为： CounterID

SQL语句如下:
SELECT CounterID from tutorial.hits_v1 LIMIT 100;
SELECT * FROM tutorial.hits_v1 WHERE CounterID=61 LIMIT 10\G

6.非主键查询

SELECT * from tutorial.hits_v1 WHERE URLDomain='puhov.com.com.tv' LIMIT 5\G
posted @ 2025-06-06 23:32  尹正杰  阅读(100)  评论(0)    收藏  举报