Analyze OnTime datasets with Databend on AWS EC2 and S3

Analyzing OnTime datasets on AWS EC2 and S3 with Databend step by step.


Step 1. Deploy environment​

 

EC2 size : c5a.4xlarge

EC2 region: <your S3 bucket region>

local disk 300G, local disk only used for ontime save and databend complie.

Os Type: ubuntu 20 x64

Prepare install package:


$sudo apt-get install unzip make mysql-client-core-8.0


Step 2. Deploy Databend​

2.1 Compile Databend

$git clone https://github.com/datafuselabs/databend.git
$cd databend
$make setup
$export PATH=$PATH:~/.cargo/bin
$make build-native

Finally, the databend-related binary files are at ./target/release/{databend-meta, databend-query}


2.2 Start Databend

# Please replace the s3 env config with your own. export STORAGE_TYPE=s3export S3_STORAGE_BUCKET=<your-s3-bucket>export S3_STORAGE_REGION=<your-s3-region>export S3_STORAGE_ENDPOINT_URL=<your-bucket>.s3.amazonaws.comexport 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 Test Databend

mysql -h 127.0.0.1 -P3307 -uroot

Check connect is ok .



Step 3. Load OnTime datasets​


3.1 Create OnTime table

wget --no-check-certificate https://repo.databend.rs/ontime/create_table.sqlcat create_table.sql |mysql -h 127.0.0.1 -P3307 -uroot


3.2 Load Data into OnTime table​

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


Step 4. Queries


Execute Query and set settings:

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;

 

All Queries:

NumberQuery
Q1SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q3SELECT Origin, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;
Q4SELECT IATA_CODE_Reporting_Airline AS Carrier, count() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() DESC;
Q5SELECT 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;
Q6SELECT 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;
Q7SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier;
Q8SELECT Year, avg(DepDelay) FROM ontime GROUP BY Year;
Q9SELECT Year, count(*) as c1 FROM ontime group by Year;
Q10SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11SELECT avg(c1) FROM (select Year,Month,count(*) as c1 from ontime group by Year,Month) a;
Q12SELECT OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;
Q13SELECT OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;
Q14SELECT count(*) FROM ontime;


Comments

Post a Comment

Popular posts from this blog

The Databend Community Actively Embraces The Rust Ecosystem

Image
The Databend Community Actively Embraces The Rust Ecosystem Databend aims to become an open source, flexible, and reliable serverless data warehouse, it can query fast as lightning, and organically combined with flexible, simple, and low-cost cloud services. The construction of data cloud has never been easier! What Databend Means to The Rust Community Most Databend's team members have rich experience in database/data platform development and open source collaboration. So we choose Rust to develop the next-generation data cloud services which can performance higher performance and more reliable. 1.  Create the top seed player in the Rust data warehouse field, show the charm of Rust, and promote the further application of Rust in the database field. 2.  Continue to promote Rust's efforts in data governance/database/cloud native and other domains to improve the ecosystem and tools. 3.  Provide more learning/practice/job opportunities for Rustacean, and cultivate a group of ...
Read more

This week in Databend #26

[Databend](https://github.com/datafuselabs/databend) aimed to be an open source **elastic** and **reliable** cloud warehouse, it offers blazing fast query and combines elasticity, simplicity, low cost of the cloud, built to make the Data Cloud easy. ## Big changes Below is a list of some major changes that we don't want you to miss. ### Features - **add bloom filter for single column equivalent expression** by [@junli1026](https://github.com/junli1026) ([#3887](https://github.com/datafuselabs/databend/pull/3887)) - **Jwt Auth for http handler** by [@youngsofun](https://github.com/youngsofun) ([#3993](https://github.com/datafuselabs/databend/pull/3993)) ## Improvement - **add partitions_scanned and partitions_total to planner statistics** by [@BohuTANG](https://github.com/BohuTANG). ([#3909](https://github.com/datafuselabs/databend/pull/3909)) - **clean up metasrv code and test** by [@drmingdrmer](https://github.com/drmingdrmer). ([#3925](https://github.com/datafuselabs/databend/pul...
Read more

Databend Design | Whitepapers

Image
Databend is an open source   elastic   and   scalable   cloud warehouse, it offers blazing fast query and combines elasticity, simplicity, low cost of the cloud, built to make the Data Cloud easy. Databend is intended for executing workloads with data stored in cloud storage systems, such as AWS S3 and Azure Blob Storage or others. We design Databend with the following key functionalities in mind: Elastic  In Databend storage and compute resources can dynamically scale up and down on demand. Secure  All data files and network traffic in Databend is encrypted end-to-end, and provider  Role Based Access Control  in SQL level. User-friendly  Databend is an ANSI SQL compliant cloud warehouse, it is easy for data scientist and engineers to use. Cost-efficient  Databend processes queries with high performance, and the user only pays for what is actually used. The picture above shows the high-level architecture of Databend, it consists of three...
Read more