how to import mysql dump file data into vertica 7.2?

Meliyana

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

 

Adrian_Oprea_1

 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 

Meliyana

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

Adrian_Oprea_1

 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