How to Import data from MySQL into Vertica with one single line of code
We all know that we don't have linked server or db links in Vertica nor MySQL, so how can we create bridge between these two to import data from one to another ?
What do we need to do to accomplish this :
1 - Install the MySQL Client on you Vertica host and also Install the Vertica vsql client on the MySQL host(only if you plan to import data from Vertica into MySQL).
2 - Create the table definition inside Vertica(tje table that will receive the imported data)
-use this command to extract the DDL of your MySQL table
mysql:show create table alertsG
CREATE TABLE alerts (
alertid bigint(20) unsigned NOT NULL,
actionid bigint(20) unsigned NOT NULL,
eventid bigint(20) unsigned NOT NULL,
userid bigint(20) unsigned DEFAULT NULL,
clock int(11) NOT NULL DEFAULT '0',
mediatypeid bigint(20) unsigned DEFAULT NULL,
sendto varchar(100) NOT NULL DEFAULT '',
subject varchar(255) NOT NULL DEFAULT '',
message text NOT NULL,
status int(11) NOT NULL DEFAULT '0',
retries int(11) NOT NULL DEFAULT '0',
error varchar(128) NOT NULL DEFAULT '',
esc_step int(11) NOT NULL DEFAULT '0',
alerttype int(11) NOT NULL DEFAULT '0'
);
- the end result would be something like this
Create Table: CREATE TABLE alerts (
alertid int ,
actionid int,
eventid int,
userid int,
clock int ,
mediatypeid varchar(20),
sendto varchar,
subject varchar(255),
message varchar(1400),
status int,
retries int ,
error varchar(128) ,
esc_step int,
alerttype int
);
dbadmin= d alerts
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+--------+-------------+---------------+------+---------+----------+-------------+-------------
public | alerts | alertid | int | 8 | | f | f |
public | alerts | actionid | int | 8 | | f | f |
public | alerts | eventid | int | 8 | | f | f |
public | alerts | userid | int | 8 | | f | f |
public | alerts | clock | int | 8 | | f | f |
public | alerts | mediatypeid | int | 8 | | f | f |
public | alerts | sendto | varchar(80) | 80 | | f | f |
public | alerts | subject | varchar(255) | 255 | | f | f |
public | alerts | message | varchar(1400) | 1400 | | f | f |
public | alerts | status | int | 8 | | f | f |
public | alerts | retries | int | 8 | | f | f |
public | alerts | error | varchar(128) | 128 | | f | f |
public | alerts | esc_step | int | 8 | | f | f |
public | alerts | alerttype | int | 8 | | f | f |
(14 rows)- no encoding have been applied as this is just for demonstration, if you want to setup encoding , sort order, segmentation, partitioning fell free to do so at create table time.
mysql -uuser_name -ppassword -P port_number -hhost_name -Nnqsre "use db_name; select * from table_name;"tr -t 't' ','- we take the STDOUT and apply the following rules to it to transform it into comma separated values output
- follow the base syntax bellow
vsql -U user_name -w password -h host -d database_name -c "COPY public.table FROM STDIN DELIMITER ',' SKIP 1 EXCEPTIONS '/tmp/exception.log' REJECTED DATA '/tmp/rejected.log' DIRECT ;"- specify the connection details and then the COPY command options like the delimiter or the exception & rejected location and also depending on the size of the load specify the DIRECT option.
mysql -uuser_name -ppassword -P port_number -hhost_name -Nnqsre "use db_name; select * from table_name;" |tr -t 't' ','|vsql -U user_name -w password -h host -d database_name -c "COPY public.table FROM STDIN DELIMITER ',' SKIP 1 EXCEPTIONS '/tmp/exception.log' REJECTED DATA '/tmp/rejected.log' DIRECT ;"