how to import mysql dump file data into vertica 7.2?

I have mysql dump file ..

for example test.sql and how to import test.sql  data into vertica 7.2 ?

 

OS server : linux

vertica version : 7.2

 

 

script -> test.sql

insert into test_table ( 1, 'Ana');

insert into test_table ( 2, 'Budi);

 

 id data type integer

 name data type varchar

 

Thanks Before

Meliyana

 

Comments

  •  Hi ,

    1-  You can run the sql as is and wait for a long time(not a good thing).

    2 - Or you can export into a csv format from mysql.

    SELECT id,name
    FROM users
    INTO OUTFILE '/tmp/users.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';

    3 - Export form mysql using mysqldump with --fields-terminated-by and --lines-terminated-by options.

    Here is an example of a script :

    - this script  will dump and gzip 10 tables at a time.

    This script was not written by me but i have useded many times.

     

    MYSQL_USER=root
    MYSQL_PASS=rootpassword
    MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
    SQLSTMT="SELECT CONCAT(table_schema,'.',table_name)"
    SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE table_schema NOT IN "
    SQLSTMT="${SQLSTMT} ('information_schema','performance_schema','mysql')"
    mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > /tmp/DBTB.txt
    COMMIT_COUNT=0
    COMMIT_LIMIT=10
    TARGET_FOLDER=/path/to/csv/files
    for DBTB in `cat /tmp/DBTB.txt`
    do
    DB=`echo "${DBTB}" | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo "${DBTB}" | sed 's/\./ /g' | awk '{print $2}'`
    DUMPFILE=${DB}-${TB}.csv.gz
    mysqldump ${MYSQL_CONN} -T ${TARGET_FOLDER} --fields-terminated-by="," --fields-enclosed-by="\"" --lines-terminated-by="\r\n" ${DB} ${TB} | gzip > ${DUMPFILE}
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
    COMMIT_COUNT=0
    wait
    fi
    done
    if [ ${COMMIT_COUNT} -gt 0 ]
    then
    wait
    fi

     

    4 - Or you can take the dump and extract it into a csv format file and pipe it into a stdin copy command.

     

     

    cat script.sql | awk {'print $5$6$7'}|sed "s/\x27//g"|sed "s/);//g" | vsql -U dbadmin -w passwd -h ipaddres -d analytics -c "COPY schema.tbl FROM STDIN DELIMITER ','  DIRECT REJECTED DATA AS TABLE schema.tbl_rejects;"

     

     

     

    Your cat would be something like:

     

    1,Ana
    2,Budi

     

    Hope this was useful 

  • how I can running script number 3?
    whether use tools?  

  •  Hi ,

    Here is how :)

    touch script.sh
    vim script.sh
    <paste the script in>
    chmod 750 script.sh
    ./script.sh

    To test for only one table run the following:

    mysqldump  -T /tmp/  --no-create-info --skip-triggers --fields-terminated-by="," --fields-enclosed-by="\"" --lines-terminated-by="\r\n" databasename tablename

    /tmp/ is where you will the output

    database - is the db name 

    and tabename is the table you wanna export

     

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file