Skip to content

Instantly share code, notes, and snippets.

@RajenDharmendra
Forked from filimonov/export_mysql_table.sh
Created March 29, 2020 18:29
Show Gist options
  • Save RajenDharmendra/31c74f938c732ffa4eb782fb47913839 to your computer and use it in GitHub Desktop.
Save RajenDharmendra/31c74f938c732ffa4eb782fb47913839 to your computer and use it in GitHub Desktop.
mysql to clickhouse (bash)
set -o errexit
TABLE_NAME="${1:?You must pass a TABLE_NAME as first argument}"
STARTID="${2:?You must pass a STARTID as 2nd argument}"
ENDID="${3:?You must pass a ENDID as 3rd argument}"
[[ -z "$4" ]] && LIMIT="" || LIMIT="LIMIT $4"
. logins.sh
INSERT_COMMAND="INSERT INTO clickhouse_table(column1,column2,...) FORMAT TSV"
cat <<SELECTCOMMAND | mysql $MYSQL_ARGUMENTS -N | tee >(clickhouse-client $CLICKHOUSE_ARGUMENTS --query="$INSERT_COMMAND") | wc -l
SELECT
$TABLE_NAME.column1,
IFNULL($TABLE_NAME.column2,0),
...
FROM $TABLE_NAME
WHERE column1 between $STARTID and $ENDID
ORDER BY column1
$LIMIT;
SELECTCOMMAND
~
. logins.sh
LIMIT=300000
while :
do
TABLE_NAME='my_table'
STARTID=$(clickhouse-client $CLICKHOUSE_ARGUMENTS --query='select max(column1) + 1 from my_table where part_key=(select max(part_key) from my_table)')
ENDID=$(echo 'select max(column1) t from my_table' | mysql $MYSQL_ARGUMENTS -N)
echo $TABLE_NAME range $STARTID to $ENDID limit $LIMIT
export_mysql_table.sh $TABLE_NAME $STARTID $ENDID $LIMIT
echo "Press [CTRL+C] to stop.."
sleep 10
done
MYSQL_ARGUMENTS="-h mysqlhost -u mysqluser -pmysqlpassword -Dmysqldatabase"
CLICKHOUSE_ARGUMENTS="-h clickhousehost -u clickhouseuser --password clickhousepassword -d clickhousedatabase"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment