从 mysql RDS 导出数据以导入 questDb

最终在这个帮助下算出了:Exporting a table from Amazon RDS into a CSV file

只要您有某种类型的顺序列，例如自动递增的整数主键或日期列，这种解决方案就可以很好地工作.如果您有大量数据，请确保您的日期列已被索引！

#!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;

上述脚本将输出一个import.sql，其中包含导入数据所需的所有SQL语句.请参阅:https://questdb.io/docs/guides/importing-data/