–,-n 使用”,”分割的多个查询 ， 仅在非交互模式下有效
–, -f 使用指定格式化输出结果
–, -E 使用垂直格式输出 ， 即每个值使用一行显示
–time, -t 打印查询时间到中
– 如果出现异常 ， 会打印堆栈跟踪信息
–-file 使用指定配置文件
– 使用服务端时区
quit,exit 表示退出客户端
Ctrl+D,Ctrl+C 表示退出客户端
登录命令：
clickhouse-client -m --host node2.itcast.cn --port 9999 --user root --password 123456
航班数据集：
2.4.3导入样例数据
操作步骤 说明
1 编写下载航班数据脚本
创建名为 --data-.sh 的脚本文件
vim clickhouse-example-data-download.shfor s in `seq 2017 2020` dofor m in `seq 1 12` dowget https://transtats.bts.gov/PREZIP/On_Time_Reporting_Carrier_On_Time_Performance_1987_present_${s}_${m}.zipdonedone
2 下载航班数据
chmod +x clickhouse-example-data-download.sh
?
./clickhouse-example-data-download.sh
3 创建表
CREATE TABLE `ontime` (`Year` UInt16,`Quarter` UInt8,`Month` UInt8,`DayofMonth` UInt8,`DayOfWeek` UInt8,`FlightDate` Date,`UniqueCarrier` FixedString(7),`AirlineID` Int32,`Carrier` FixedString(2),`TailNum` String,`FlightNum` String,`OriginAirportID` Int32,`OriginAirportSeqID` Int32,`OriginCityMarketID` Int32,`Origin` FixedString(5),`OriginCityName` String,`OriginState` FixedString(2),`OriginStateFips` String,`OriginStateName` String,`OriginWac` Int32,`DestAirportID` Int32,`DestAirportSeqID` Int32,`DestCityMarketID` Int32,`Dest` FixedString(5),`DestCityName` String,`DestState` FixedString(2),`DestStateFips` String,`DestStateName` String,`DestWac` Int32,`CRSDepTime` Int32,`DepTime` Int32,`DepDelay` Int32,`DepDelayMinutes` Int32,`DepDel15` Int32,`DepartureDelayGroups` String,`DepTimeBlk` String,`TaxiOut` Int32,`WheelsOff` Int32,`WheelsOn` Int32,`TaxiIn` Int32,`CRSArrTime` Int32,`ArrTime` Int32,`ArrDelay` Int32,`ArrDelayMinutes` Int32,`ArrDel15` Int32,`ArrivalDelayGroups` Int32,`ArrTimeBlk` String,`Cancelled` UInt8,`CancellationCode` FixedString(1),`Diverted` UInt8,`CRSElapsedTime` Int32,`ActualElapsedTime` Int32,`AirTime` Int32,`Flights` Int32,`Distance` Int32,`DistanceGroup` UInt8,`CarrierDelay` Int32,`WeatherDelay` Int32,`NASDelay` Int32,`SecurityDelay` Int32,`LateAircraftDelay` Int32,`FirstDepTime` String,`TotalAddGTime` String,`LongestAddGTime` String,`DivAirportLandings` String,`DivReachedDest` String,`DivActualElapsedTime` String,`DivArrDelay` String,`DivDistance` String,`Div1Airport` String,`Div1AirportID` Int32,`Div1AirportSeqID` Int32,`Div1WheelsOn` String,`Div1TotalGTime` String,`Div1LongestGTime` String,`Div1WheelsOff` String,`Div1TailNum` String,`Div2Airport` String,`Div2AirportID` Int32,`Div2AirportSeqID` Int32,`Div2WheelsOn` String,`Div2TotalGTime` String,`Div2LongestGTime` String,`Div2WheelsOff` String,`Div2TailNum` String,`Div3Airport` String,`Div3AirportID` Int32,`Div3AirportSeqID` Int32,`Div3WheelsOn` String,`Div3TotalGTime` String,`Div3LongestGTime` String,`Div3WheelsOff` String,`Div3TailNum` String,`Div4Airport` String,`Div4AirportID` Int32,`Div4AirportSeqID` Int32,`Div4WheelsOn` String,`Div4TotalGTime` String,`Div4LongestGTime` String,`Div4WheelsOff` String,`Div4TailNum` String,`Div5Airport` String,`Div5AirportID` Int32,`Div5AirportSeqID` Int32,`Div5WheelsOn` String,`Div5TotalGTime` String,`Div5LongestGTime` String,`Div5WheelsOff` String,`Div5TailNum` String) ENGINE = MergeTree(FlightDate, (Year, FlightDate), 8192)
4 导入数据
创建名为 .sh 的脚本文件
vim .sh
for i in *.zip; do echo $i; unzip -cq $i '*.csv' | sed 's/\.00//g' | clickhouse-client--host=node2.itcast.cn --port 9999 --user root --password 123456 --query="INSERT INTO db_ontime.ontime FORMAT CSVWithNames"; done