AMPLab 大数据基准测试

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

需要您在https://aws.amazon.com注册一个免费的账号。注册时需要您提供信用卡、邮箱、电话等信息。之后可以在https://console.aws.amazon.com/iam/home?nc2=h_m_sc#security_credential获取新的访问密钥

在控制台运行以下命令：

  1. sudo apt-get install s3cmd
  2. mkdir tiny; cd tiny;
  3. s3cmd sync s3://big-data-benchmark/pavlo/text-deflate/tiny/ .
  4. cd ..
  5. mkdir 1node; cd 1node;
  6. s3cmd sync s3://big-data-benchmark/pavlo/text-deflate/1node/ .
  7. cd ..
  8. mkdir 5nodes; cd 5nodes;
  9. s3cmd sync s3://big-data-benchmark/pavlo/text-deflate/5nodes/ .
  10. cd ..

在ClickHouse运行如下查询：

  1. CREATE TABLE rankings_tiny
  2. (
  3.     pageURL String,
  4.     pageRank UInt32,
  5.     avgDuration UInt32
  6. ) ENGINE = Log;
  8. CREATE TABLE uservisits_tiny
  9. (
  10.     sourceIP String,
  11.     destinationURL String,
  12.     visitDate Date,
  13.     adRevenue Float32,
  14.     UserAgent String,
  15.     cCode FixedString(3),
  16.     lCode FixedString(6),
  17.     searchWord String,
  18.     duration UInt32
  19. ) ENGINE = MergeTree(visitDate, visitDate, 8192);
  21. CREATE TABLE rankings_1node
  22. (
  23.     pageURL String,
  24.     pageRank UInt32,
  25.     avgDuration UInt32
  26. ) ENGINE = Log;
  28. CREATE TABLE uservisits_1node
  29. (
  30.     sourceIP String,
  31.     destinationURL String,
  32.     visitDate Date,
  33.     adRevenue Float32,
  34.     UserAgent String,
  35.     cCode FixedString(3),
  36.     lCode FixedString(6),
  37.     searchWord String,
  38.     duration UInt32
  39. ) ENGINE = MergeTree(visitDate, visitDate, 8192);
  41. CREATE TABLE rankings_5nodes_on_single
  42. (
  43.     pageURL String,
  44.     pageRank UInt32,
  45.     avgDuration UInt32
  46. ) ENGINE = Log;
  48. CREATE TABLE uservisits_5nodes_on_single
  49. (
  50.     sourceIP String,
  51.     destinationURL String,
  52.     visitDate Date,
  53.     adRevenue Float32,
  54.     UserAgent String,
  55.     cCode FixedString(3),
  56.     lCode FixedString(6),
  57.     searchWord String,
  58.     duration UInt32
  59. ) ENGINE = MergeTree(visitDate, visitDate, 8192);

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

  1. for i in tiny/rankings/*.deflate; do echo $i; zlib-flate -uncompress < $i | clickhouse-client --host=example-perftest01j --query="INSERT INTO rankings_tiny FORMAT CSV"; done
  2. for i in tiny/uservisits/*.deflate; do echo $i; zlib-flate -uncompress < $i | clickhouse-client --host=example-perftest01j --query="INSERT INTO uservisits_tiny FORMAT CSV"; done
  3. for i in 1node/rankings/*.deflate; do echo $i; zlib-flate -uncompress < $i | clickhouse-client --host=example-perftest01j --query="INSERT INTO rankings_1node FORMAT CSV"; done
  4. for i in 1node/uservisits/*.deflate; do echo $i; zlib-flate -uncompress < $i | clickhouse-client --host=example-perftest01j --query="INSERT INTO uservisits_1node FORMAT CSV"; done
  5. for i in 5nodes/rankings/*.deflate; do echo $i; zlib-flate -uncompress < $i | clickhouse-client --host=example-perftest01j --query="INSERT INTO rankings_5nodes_on_single FORMAT CSV"; done
  6. for i in 5nodes/uservisits/*.deflate; do echo $i; zlib-flate -uncompress < $i | clickhouse-client --host=example-perftest01j --query="INSERT INTO uservisits_5nodes_on_single FORMAT CSV"; done

简单的查询示例：

  1. SELECT pageURL, pageRank FROM rankings_1node WHERE pageRank > 1000
  3. SELECT substring(sourceIP, 1, 8), sum(adRevenue) FROM uservisits_1node GROUP BY substring(sourceIP, 1, 8)
  5. SELECT
  6.     sourceIP,
  7.     sum(adRevenue) AS totalRevenue,
  8.     avg(pageRank) AS pageRank
  9. FROM rankings_1node ALL INNER JOIN
  10. (
  11.     SELECT
  12.         sourceIP,
  13.         destinationURL AS pageURL,
  14.         adRevenue
  15.     FROM uservisits_1node
  16.     WHERE (visitDate > '1980-01-01') AND (visitDate < '1980-04-01')
  17. ) USING pageURL
  18. GROUP BY sourceIP
  19. ORDER BY totalRevenue DESC
  20. LIMIT 1

Original article