環境概要

3台のCentOS7サーバーにClickHouseがインストールされています。

1. データベースの作成

ClickHouseでは以下のSQL構文を使用してデータベースを作成します。

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)]

基本的な使用例：

CREATE DATABASE testdb;  -- データベース作成
DROP DATABASE testdb;    -- データベース削除

2. テーブルの作成

テーブル作成の基本構文は以下の通りです。

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [compression_codec] [TTL expr2],
    ...
) ENGINE = engine

MergeTreeエンジンを使用したテーブル作成例：

CREATE TABLE sample_data( 
    region_code        String, 
    region_name        String, 
    record_date        date 
) ENGINE = MergeTree(record_date, (region_code), 8192);

複数行のSQLを実行する場合、2つの方法があります：

1. 各行の末尾にバックスラッシュを追加：

CREATE TABLE sample_data( \
    region_code        String, \
    region_name        String, \
    record_date        date \
) ENGINE = MergeTree(record_date, (region_code), 8192);

2. -mパラメータを使用してマルチラインモードでログイン：

clickhouse-client -m

エンジンの種類：MergeTreeが最も一般的ですが、Logエンジンもよく使用されます。MergeTreeは日付フィールドと主キーが必要ですが、Logはその制限がありません。

record_date：テーブルの必須日付フィールド

region_code：主キーフィールド（複数フィールド可）

8192：インデックス粒度（デフォルト値で問題ありません）

3. データのインポート

3.1 標準的なCSVファイルのインポート

サンプルCSVファイルの作成：

cat > sample_data.csv << EOF
JP,Japan,2023-01-15
US,United States,2023-02-20
CN,China,2023-03-10
EOF

データのインポート：

-- ファイルリダイレクトを使用
clickhouse-client --query "INSERT INTO testdb.sample_data FORMAT CSV" < sample_data.csv;

-- パイプを使用
cat sample_data.csv | clickhouse-client --query "INSERT INTO testdb.sample_data FORMAT CSV"

インポート結果の確認：

SELECT *
FROM sample_data
LIMIT 2;

┌─region_code─┬─region_name────┬─record_date─┐
│ JP          │ Japan          │ 2023-01-15  │
└─────────────┴────────────────┴─────────────┘
┌─region_code─┬─region_name────┬─record_date─┐
│ US          │ United States  │ 2023-02-20  │
└─────────────┴────────────────┴─────────────┘

3.2 特殊なCSVファイルのインポート（改行、エスケープ文字を含む）

対応テーブルの作成：

CREATE TABLE testdb.complex_data ( id1 UInt32, id2 Float32, name1 String, name2 String, date1 Date, date2 DateTime) ENGINE = Log;

テストデータの作成：

1,987.654,"sample text"," quoted \"text",2023-04-15,2023-04-15 12:30:45

データインポート：

clickhouse-client --query "INSERT INTO testdb.complex_data FORMAT CSV" < complex_data.csv

結果の確認：

SELECT *
FROM complex_data;

┌─id1─┬─────id2─┬─name1──────┬─name2────────────┬──────date1─┬───────────────date2─┐
│   1 │ 987.654 │ "sample text" │ " quoted \"text" │ 2023-04-15 │ 2023-04-15 12:30:45 │
└─────┴─────────┴────────────┴──────────────────┴────────────┴─────────────────────┘

3.3 大規模データセットのインポート（公式データを使用）

公式データセットのダウンロードと展開：

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

ダウンロードサイズの確認：

du -sh /data/clickhouse/tmp
9.8G	/data/clickhouse/tmp

新しいデータベースの作成：

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

インポート用テーブルの作成：

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

tutorial.visits_v1テーブルの作成も同様に行います。

データのインポート：

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

テーブルの最適化：

clickhouse-client --query "OPTIMIZE TABLE tutorial.hits_v1 FINAL"
clickhouse-client --query "OPTIMIZE TABLE tutorial.visits_v1 FINAL"

データ量の確認：

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

データのクエリ例：

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;

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