问将数据从mysql导出以导入到questDb

EN

#!bin/bash

# Maximum number of rows to export/total rows in table, set a bit higher if live data being written
MAX=500000000
# Size of each export batch
STEP=1000000

mkdir -p parts

for (( c=0; c<= $MAX; c = c + $STEP ))
do
   mysql --port 3306 --protocol=TCP -h <rdshostname> -u <username> -p<password> --quick --database=<db> -e "select column1, column2, column3 <table> order by <timestamp> ASC limit $STEP offset $c" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > export$c.csv

   # split down in to chunks under questdbs 65k line limit
   split -d -l 64999 --additional-suffix=.csv $FILE_NAME.csv ./parts/$FILE_NAME

done

# print out import statements to a file
for i in $(ls -v ./parts); do echo "COPY reading from '$i';" >> import.sql; done;

#!bin/bash
START_YEAR=2020
END_YEAR=2022

mkdir -p parts

for (( YEAR=$START_YEAR; YEAR<=$END_YEAR; YEAR++ ))
do
   for (( MONTH=1; MONTH<=12; MONTH++ ))
   do

      NEXT_MONTH=1
      let NEXT_YEAR=$YEAR+1
      if [ $MONTH -lt 12 ]
      then
         let NEXT_MONTH=$MONTH+1
         NEXT_YEAR=$YEAR
      fi

      FILE_NAME="export-$YEAR-$MONTH-to-$NEXT_YEAR-$NEXT_MONTH"

      mysql --port 3306 --protocol=TCP -h <rdshost> -u app -p<password> --quick --database=<database> -e "select <column1>, <column2>, round(UNIX_TIMESTAMP(<dateColumn>)) * 1000000 as date from <table> where <table>.<dateColumn> >= '$YEAR-$MONTH-01 00:00:00' and table.<dateColumn> < '$NEXT_YEAR-$NEXT_MONTH-01 00:00:00' order by <table>.<dateColumn> ASC" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > $FILE_NAME.csv

      # split down in to chunks under questdbs 65k line limit
      split -d -l 64999 --additional-suffix=.csv $FILE_NAME.csv ./parts/$FILE_NAME
   done
done

# print out import statements to a file
for i in $(ls -v ./parts); do echo "COPY reading from '$i';" >> import.sql; done;