upload2ch.sh

#!/bin/sh

for s in `seq 1987 2017`; do
    for m in `seq 1 12`; do
        FILE="On_Time_On_Time_Performance_${s}_${m}.zip"
        wget https://transtats.bts.gov/PREZIP/${FILE}
        if [ "$?" -eq "0" ]; then
            echo
            echo -n "Upload ${FILE} "
            unzip -cq ${FILE} '*.csv' | sed 's/\.00//g' | tail -n +2 | split -l 1000 --additional-suffix=".csv"
            rm ${FILE}
            for f in *.csv; do
                retry=5
                while [ ${retry} -gt 0 ]; do
                    # Test cli and restart server
                    clickhouse-client --query="select 1" > /dev/null
                    if [ "$?" -ne "0" ]; then
                        TM=`date +%T`
                        echo
                        echo "RESTART SERVER at ${TM}"
                        sleep 480 # wait 8m
                        service clickhouse-server stop
                        sleep 30
                        service clickhouse-server start
                        sleep 30
                    fi

                    # Insert data
                    echo -n "."
                    cat ${f} | clickhouse-client --query="INSERT INTO ontime FORMAT CSV"
                    if [ "$?" -ne "0" ]; then
                        TM=`date +%T`
                        echo "ERROR on ${f} at ${TM}"
                        retry=`expr ${retry} - 1`
                        if [ ${retry} -eq 0 ]; then
                            echo "STOP on ${f}"
                            exit 1
                        fi
                        sleep 540 # wait 9m
                    else
                        retry=0
                    fi
                done
                rm ${f}
                sleep 20
            done
            echo "done"
            echo
        fi
    done
done