We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


how to import mysql dump file data into vertica 7.2? — Vertica Forum

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