Shulou(Shulou.com)06/01 Report--

As a data warehouse developed based on postgresql, it has attracted much attention with the rise of big data concepts in recent years.

Because GP is an open source database only in recent years, there is very little information on the Internet, unlike mysql, which is a rotten street. Basically, problems encountered can be found online. GP encountered problems can only rely on their own judgment, a lot of time can only see the official documents, and documents are all in English, for English is very poor I said really weak.

Principle of gplink:

Greenplum supports gpfdist protocol external tables, and gpfdist protocol supports custom transforms.

gplink uses jdbc to connect external data sources, defines transform, and converts data from jdbc data sources into text format for import into GP or HAWQ.

The official provides greenplum, sqlserver, hive, oracle database template, now need to connect is mysql database, a little trouble, step on a few pits, in the end of the article will be mentioned.

Required software download address

Gplink download address

https://github.com/pivotalguru/gplink

mysql JDBC download address

https://dev.mysql.com/downloads/connector/j/

This is the official documentation for installation steps

1. Download latest version from PivotalGuru.com

2. Unzip .zip

3. source gplink_path.sh and add this to your .bashrc file

4. Edit gplink.properties with correct Greenplum or Hawq connection information

5. Download 3rd party JDBC drivers and place it in $GPLINK_HOME/jar

6. Define source configurations in $GPLINK_HOME/connections/

7. Define external table names and columns in $GPLINK_HOME/tables/

8. Define SQL statements to execute in the source in $GPLINK_HOME/sql/

9. Create the External Table with gpltable

Personal translation of Chinese

1. Download the latest version from pivotalguru.com

2. Decompress the compressed package

3. source gplink_path.sh and add to.bashrc file

4. Edit Greenplum or Hawq connection information in gplink.properties.

Download the third-party JDBC driver and place it in $GPLINK_HOME/jar

6. Modify the source database configuration information in $GPLINK_HOME/connections/

Define external table names and columns in $GPLINK_HOME/tables/

In $GPLINK_HOME/sql/define sql statements to be executed in the source database

9. Create an external table with gpltable

Start installing below

Before installation, open access to GP on mysql port (172.16.104.71: 3306), close iptables, or open mysql port on iptables.

Here to facilitate testing mysql to the maximum permissions, in the actual environment can not do so

[root@s121 ~]# mysql -uroot -p123mysql> grant all on *.* to "root"@"%" identified by '123';mysql> flush privileges;

1. Download the latest version from pivotalguru.com

[root@mdw ~]# su - gpadmin[gpadmin@mdw ~]$wget https://codeload.github.com/pivotalguru/gplink/zip/master

2. Decompress the compressed package

[gpadmin@mdw ~]$unzip master

3. source gplink_path.sh and add to.bashrc file

[gpadmin@mdw ~]$source gplink-master/gplink_path.sh[gpadmin@mdw ~]$vi .bashrc source /home/gpadmin/gplink-master/gplink_path.sh

4. Edit Greenplum or Hawq connection information in gplink.properties.

[gpadmin@mdw ~]$ vi $GPLINK_HOME/gplink.properties connectionUrl=jdbc:postgresql://mdw:5432/gpdb #gpdb database for gp classForName=org.postgresql.DriverreadCommitted=trueuserName=gpadmin #gp username password=123456 #Password, note no spaces after gplinkHome=/usr/local/gplinkgplinkLog=/usr/local/gplink/log/gplinkgplinkYml=/usr/local/gplink/yml/gplink.ymlgplinkPortLower=24000gplinkPortUpper=25000

Download the third-party JDBC driver and place it in $GPLINK_HOME/jar

[gpadmin@mdw ~]$ wget https://dev.mysql.com/downloads/file/? id=470332[gpadmin@mdw ~]$ tar xvf mysql-connector-java-5.1.42.tar.gz [gpadmin@mdw ~]$ cp mysql-connector-java-5.1.42/mysql-connector-java-5.1.42-bin.jar gplink-master/jar/

6. Modify configuration at $GPLINK_HOME/connections/

[gpadmin@mdw ~]$ cp $GPLINK_HOME/connections/oracle.properties $GPLINK_HOME/connections/mysql.properties[gpadmin@mdw ~]$ vi $GPLINK_HOME/connections/mysql.properties connectionUrl=jdbc:mysql://172.16.104.71:3306/test #test database for mysql classForName=com.mysql.jdbc.DriverreadCommitted=trueuserName=root #mysql username password=123 #mysql password extraProps=defaultRowPrefetch=2000 #Amount of data read each time

Define external table names and columns in $GPLINK_HOME/tables/

[gpadmin@mdw ~]$ cp $GPLINK_HOME/tables/public.oracle_example.sql $GPLINK_HOME/tables/public.mysql.sql [gpadmin@mdw ~]$ vi $GPLINK_HOME/tables/public.mysql.sql tableName=public.mysqlcolumns=first_name text, last_name text

In $GPLINK_HOME/sql/define sql statements to be executed in the source database

[gpadmin@mdw ~]$ cp $GPLINK_HOME/sql/oracle_example.sql $GPLINK_HOME/sql/mysql_example.sql

9. Create an external table with gpltable

[gpadmin@mdw ~]$gpltable -s $GPLINK_HOME/connections/mysql.properties -t $GPLINK_HOME/gplink.properties -f $GPLINK_HOME/sql/mysql_example.sql -a $GPLINK_HOME/tables/public.mysql.sql

Log in to GP database at this time and find one more mysql table

[gpadmin@mdw ~]$ psql -d gpdatabasepsql (8.2.15)Type "help" for help.gpdatabase=# \dx List of relations Schema | Name | Type | Owner | Storage --------+--------------+-------+---------+---------- public | mysql | table | gpadmin | external(1 rows)

test

[gpadmin@mdw ~]$ gplstart -t $GPLINK_HOME/gplink.properties Started all ports needed. [gpadmin@mdw ~]$ gpldata -s $GPLINK_HOME/connections/mysql.properties -f $GPLINK_HOME/sql/mysql_example.sqljon|robertsJON|ROBERTS

OK, this status indicates that the connection is successful.

As for how to import data from mysql to greenplum, I haven't studied it yet, so I'll explore it slowly.

delete table command

[gpadmin@mdw ~]$ gpldrop -t $GPLINK_HOME/connections/gplink.properties -n public.mysql

Several pits stepped on during installation

1. ClassForName in mysql.properties is incorrect, because there is no mysql template, it is copied from oracle template to use.

[gpadmin@mdw ~]$ gpldata -s $GPLINK_HOME/connections/mysql.properties -f $GPLINK_HOME/sql/mysql_example.sqlException in thread "main" java.sql.SQLException: mysql.jdbc.driver.MysqlDriver at ExternalData.main(ExternalData.java:25)

2, jdbc version is wrong, Download the latest version, Can't use

[gpadmin@mdw ~]$ gpldata -s $GPLINK_HOME/connections/mysql.properties -f $GPLINK_HOME/sql/mysql_example.sqlException in thread "main" java.lang.UnsupportedClassVersionError: com/mysql/jdbc/Driver : Unsupported major.minor version 52.0

3. The connection failed. The firewall of the mysql host was not closed or the mysql port was not opened.

[gpadmin@mdw ~]$ gpldata -s $GPLINK_HOME/connections/mysql.properties -f $GPLINK_HOME/sql/mysql_example.sqlException in thread "main" java.sql.SQLException: Communications link failureThe last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any at ExternalData.main(ExternalData.java:25)

4. The password is wrong because there are spaces behind the password in mysql.properties file.

[gpadmin@mdw ~]$ gpldata -s $GPLINK_HOME/connections/mysql.properties -f $GPLINK_HOME/sql/mysql_example.sqlException in thread "main" java.sql.SQLException: Access denied for user 'root'@'172.16.104.21' (using password: YES) at ExternalData.main(ExternalData.java:25)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.