ClickHouse数据导入

痛定思痛。 2022-12-11 07:45 327阅读 0赞

### 文章目录 ###

*  ClickHouse数据导入
 *   *  下载样本数据
     *  创建数据库
     *  创建数据表
     *  数据表字段的数据类型
     *  数据表的表引擎
     *  导入数据
     *  检查数据
     *  查询数据
     *  数据压缩

# ClickHouse数据导入 #

参照ClickHouse官方教程来导入样本数据：

*  [https://clickhouse.tech/docs/en/getting-started/tutorial/][https_clickhouse.tech_docs_en_getting-started_tutorial]

## 下载样本数据 ##

curl https://clickhouse-datasets.s3.yandex.net/hits/tsv/hits_v1.tsv.xz | unxz --threads=`nproc` > hits_v1.tsv
    
    curl https://clickhouse-datasets.s3.yandex.net/visits/tsv/visits_v1.tsv.xz | unxz --threads=`nproc` > visits_v1.tsv

其中，hits\_v1.tsv约7.3G，visits\_v1.tsv约2.5G。

## 创建数据库 ##

-- 创建数据库
    create database if not exists tutorial
    
    -- 查看数据库
    show databases
    
    -- 切换数据库
    use tutorial

## 创建数据表 ##

> 因为官方文档中的建表语句有换行，因此需要在文本编辑器中先在每行末尾添加`\`，再复制到clickhouse-client执行，。
> 
> VS Code在每行末尾添加`\`的做法：
> 
> 1.  打开VS Code，复制粘贴内容到一个新的文件中。
> 2.  按下`Alt`\+ `Command` \+ `F`，选中Use Regular Expression，将`\n`替换为`\ \n`。

创建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;

创建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;

## 数据表字段的数据类型 ##

-- 查看表
    show tables
    
    -- 查看表详情
    desc hits_v1
    desc visits_v1

查看上述表详情，可以看到该表的字段数据类型包括：

*  无符号整数：UInt
 *  有符号整数：Int
 *  浮点数：Float
 *  可变字符串：String
 *  固定长度字符串：FixedString
 *  日期和时间：DateTime、Date
 *  数组：Array
 *  嵌套类型：Nested，在`desc <table>` 时，可以看到嵌套内的字段被描述为一个数组类型，比如
    
     *  ParsedParams.Key1为Array(String)
     *  ParsedParams.ValueDouble为Array(Float64)

关于ClickHouse的数据类型参见：

*  [ClickHouse数据类型][ClickHouse]

## 数据表的表引擎 ##

-- 查看数据表建表语句
    show create table hits_v1
    show create table visits_v1

hits\_v1表的表引擎定义：

ENGINE = MergeTree()
    PARTITION BY toYYYYMM(EventDate)
    ORDER BY (CounterID, EventDate, intHash32(UserID))
    SAMPLE BY intHash32(UserID)
    SETTINGS index_granularity = 8192

**说明：**

*  `ENGINE = MergeTree()` : 表引擎为MergeTree表引擎，且没有参数。MergeTree表引擎是MergeTree系列表引擎的基础表引擎。MergeTree系列表引擎是ClickHouse的核心表引擎，性能最强，使用最广泛。
 *  `PARTITION BY toYYYYMM(EventDate)` : 表根据EventDate的YYYYMM格式进行分区（partion）。ClickHouse的表分区和MySQL的表分区类似。MergeTree表引擎支持表分区。
 *  `ORDER BY (CounterID, EventDate, intHash32(UserID))`: 使用MergeTree表引擎时，数据在各个分区内，按照指定规则进行排序，是一种分区内的局部排序。在没有`PRIMARY KEY` 语句时，MergeTree表引擎使用`ORDER BY` 语句中定义的排序键（sorting key）作为表的主键，因此该表的主键为：CounterID, EventDate, intHash32(UserID) 组成的联合主键。
 *  `SAMPLE BY intHash32(UserID)`：使用MergeTree表引擎时，指定的数据取样（sampling）规则，取样Key必须为Int类型。这里根据`intHash32` (将int数转成32位的Hash值）后的分布结果进行采样。
 *  `SETTINGS index_granularity = 8192`：设置索引粒度（index granularity)为8192，默认值为8192，表示每8192行为一个索引标记。

参见：

*  [https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/][https_clickhouse.tech_docs_en_engines_table-engines_mergetree-family_mergetree]

visits\_v1表的表引擎定义：

ENGINE = CollapsingMergeTree(Sign)
    PARTITION BY toYYYYMM(StartDate)
    ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
    SAMPLE BY intHash32(UserID)
    SETTINGS index_granularity = 8192

下面只说明一些特殊的地方：

*  `ENGINE = CollapsingMergeTree(Sign)`：表引擎为CollapsingMergeTree(Sign)，折叠合并树。CollapsingMergeTree表引擎是MergeTree系列表引擎的一种。CollapsingMergeTree表引擎，根据Sign标记字段（`Sign Int8`）来记录数据行的状态，Sign为1时表示有效数据，Sign为-1时表示删除，在分区合并时，通过Sign的相互抵消（ 1 + -1 = 0 ）合并数据，像是折叠纸一样。

参见：

*  [https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/collapsingmergetree/][https_clickhouse.tech_docs_en_engines_table-engines_mergetree-family_collapsingmergetree]

## 导入数据 ##

在存放样本数据的当前目录，使用非交互式的`clickhouse-client --query`命令来导入数据到指定表中：

clickhouse-client --query "INSERT INTO tutorial.hits_v1 FORMAT TSV" --max_insert_block_size=100000 < hits_v1.tsv
    
    clickhouse-client --query "INSERT INTO tutorial.visits_v1 FORMAT TSV" --max_insert_block_size=100000 < visits_v1.tsv

说明：

*  在非交互模式时，需要使用`<database>.<table>` 的表的全名，比如`tutorial.hits_v1`
 *  `FORMAT TSV` 表示数据文件格式为TSV格式(和CSV格式类似，但是使用Tab制表符作为字段分隔符)
 *  导入数据时，可以设置一些参数，比如`--max_insert_block_size=100000`

根据机器的性能不同，数据导入需要时间也不同，在我的2vCPU/8G机器上，只需要1分钟左右，这个速度是非常快的。

在数据导入时，机器的CPU使用率会飙到100%，而内存使用率却不会很高，这也说明了ClickHouse在CPU层面的优化。

## 检查数据 ##

-- 8873898，超过887万条记录
    select count(1) from hits_v1
    
    -- 168107，超过168万条记录
    select count(1) from visits_v1

可以看到在作COUNT时，ClickHouse的速度是惊人的。

## 查询数据 ##

一个典型的AVG聚合查询：

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;

一个典型的SUM聚合查询：

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');

可以看到在数据分析里面常用到的筛选、分组、排序和聚合运算，在ClickHouse中的速度都非常快，远超MySQL数据库。

## 数据压缩 ##

查看数据表文件：

# 在ClickHouse Server的数据存储目录下
    cd data/tutorial/
    
    du -sh hits_v1/
    du -sh visits_v1/

根据 *数据表文件大小 / 数据文件大小* 来粗略计算压缩率：

<table> 
 <thead> 
  <tr> 
   <th>数据文件</th> 
   <th>数据文件大小</th> 
   <th>数据表</th> 
   <th>数据表文件大小</th> 
   <th>压缩率</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>hits_v1.tsv</td> 
   <td>7.3G</td> 
   <td>hits_v1</td> 
   <td>1.3G</td> 
   <td>17.8%</td> 
  </tr> 
  <tr> 
   <td>visits_v1.tsv</td> 
   <td>2.5G</td> 
   <td>visits_v1</td> 
   <td>543M</td> 
   <td>21.2%</td> 
  </tr> 
 </tbody> 
</table>

可以看到ClickHouse的数据压缩方面可以节约大约80%的存储空间。

[https_clickhouse.tech_docs_en_getting-started_tutorial]: https://clickhouse.tech/docs/en/getting-started/tutorial/
[ClickHouse]: https://blog.csdn.net/nklinsirui/article/details/108827707
[https_clickhouse.tech_docs_en_engines_table-engines_mergetree-family_mergetree]: https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/
[https_clickhouse.tech_docs_en_engines_table-engines_mergetree-family_collapsingmergetree]: https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/collapsingmergetree/