共计 2766 个字符,预计需要花费 7 分钟才能阅读完成。
1. 基准环境
ec2 size:c5a.4xlarge
ec2 region:< 您的 s3 存储区域 >
本地磁盘 300G,本地磁盘仅用于 ontime save 和 databend complie。
操作系统:ubuntu 20 x64
筹备安装包:
$sudo apt-get install unzip make mysql-client-core-8.0
2. 部署 Databend
2.1 编译 Databend
$git clone https://github.com/datafuselabs/databend.git
$cd databend
$make setup
$export PATH=$PATH:~/.cargo/bin
$make build-native
最初,databend 相干的二进制文件位于
./target/release/{databend-meta, databend-query}
2.2 启动 Databend
# Please replace the s3 env config with your own.
export STORAGE_TYPE=s3
export S3_STORAGE_BUCKET=<your-s3-bucket>
export S3_STORAGE_REGION=<your-s3-region>
export S3_STORAGE_ENDPOINT_URL=<your-bucket>.s3.amazonaws.com
export S3_STORAGE_ACCESS_KEY_ID=<your-s3-key-id>
export S3_STORAGE_SECRET_ACCESS_KEY=<your-s3-access-key>
echo "Starting standalone DatabendQuery(release)"
./scripts/ci/deploy/databend-query-standalone.sh release
2.3 测试 Databend
mysql -h 127.0.0.1 -P3307 -uroot
查看连贯是否失常。
3. 加载 Ontime
3.1 创立 Ontime 表
wget --no-check-certificate https://repo.databend.rs/ontime/create_table.sql
cat create_table.sql |mysql -h 127.0.0.1 -P3307 -uroot
3.2 加载数据
wget --no-check-certificate https://repo.databend.rs/t_ontime/t_ontime.csv.zip
unzip t_ontime.csv.zip
ls *.csv|xargs -I{} echo curl -H \"insert_sql:insert into ontime format CSV\" -H \"csv_header:0\" -H \"field_delimitor:'\t'\" -F \"upload=@{}\" -XPUT http://localhost:8001/v1/streaming_load |bash
4. 查问
执行查问
mysql -h 127.0.0.1 -P3307 -uroot
mysql>set parallel_read_threads=4;
mysql>select count(*) from ontime;
mysql>select Year, count(*) from ontime group by Year;
基准查问要点
Number | Query |
---|---|
Q1 | SELECT DayOfWeek, count() AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC; |
Q2 | SELECT DayOfWeek, count() *AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC; |
Q3 | SELECT Origin, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10; |
Q4 | SELECT IATACODE_Reporting_Airline AS Carrier, count() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() DESC; |
Q5 | SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(cast(DepDelay>10 as Int8))*1000 AS c3 FROM ontime WHERE Year=2007 GROUP BY Carrier ORDER BY c3 DESC; |
Q6 | SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(cast(DepDelay>10 as Int8))*1000 AS c3 FROM ontime WHERE Year>=2000 AND Year <=2008 GROUP BY Carrier ORDER BY c3 DESC; |
Q7 | SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier; |
Q8 | SELECT Year, avg(DepDelay) FROM ontime GROUP BY Year; |
Q9 | SELECT Year, count(*) as c1 FROM ontime group by Year; |
Q10 | SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a; |
Q11 | SELECT avg(c1) FROM (select Year,Month,count(*) as c1 from ontime group by Year,Month) a; |
Q12 | SELECT OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10; |
Q13 | SELECT OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10; |
Q14 | SELECT count(*) FROM ontime; |
正文完