PostgreSql database artifact FDW

PostgreSql database artifact FDW
Introduction to FDW of PG
Https://wiki.postgresql.org/wiki/Foreign_data_wrappers

This article briefly introduces the "transparent gateway" from PG to Mysql and PG to Hive ^_^
First, install PG
Install the PostgreSQL client.
Sudo apt-get install postgresql-client
Then: Install the PostgreSQL server.
Sudo apt-get install postgresql
Normally, after the installation is complete, the PostgreSQL server will automatically enable port 5432 on the local machine.
To install the graphic management interface, run the following command:
Sudo apt-get install pgadmin3
Next step: create a super permission user
Sudo-u postgres createuser -- superuser syk
Create Database test
Sudo-u postgres createdb-O syk test
Finally, test the connection to PG.
Psql-U syk-d test-h 127.0.0.1-p 5432
The installation of PG is complete.

Start configuring FDW
Configure
Https://github.com/EnterpriseDB/mysql_fdw
Download to local decompress
Change File Permissions
Prepare for installation: a pg extension development kit dev-9.3 needs to be installed in the middle
Sudo PATH =/usr/local/pgsql/bin/:/usr/local/mysql/bin: $ PATH make USE_PGXS = 1 install

Log on to PostgreSQL and start configuring mysql (provided that mysql users and database tables have been created ).
CREATE extension mysql_fdw;
Create server mysql_server foreign data wrapper mysql_fdw OPTIONS (HOST '2017. 0.0.1 ', PORT '2016 ');
Create user mapping FOR syk SERVER mysql_server OPTIONS (
Username 'sky ',
PASSWORD 'sky'
);
Create foreign table syk_test2 (
Goods_id INT,
Goods_type VARCHAR (10 ),
Goods_id2 BIGINT
) SERVER mysql_server OPTIONS (
Dbname 'syk ',
Table_name 'syk _ test2'
);
SELECT * FROM syk_test2;
INFO: Successfully connected to MySQL database syk at server 127.0.0.1 via TCP/IP with cipher (server version: 5.5.49-0ubuntu0. 14.04.1, protocol version: 10)
Goods_id | goods_type | goods_id2
---------- + ------------ + -----------
100 | 10010 | 0
100 | 10011 | 0
88 | 20010 | 0
86 | 20110 | 0
0 | I am special | 0
1 | I, I, and I, Cao | 0

The following content is the space occupied by ^_^
/*
ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation # note the following:
# The table structure in the mysql database has no unique constraints.
Create table 'syk _ test2 '(
'Goods _ id' INT (11) not null default '0 ',
'Goods _ type' VARCHAR (10) default null,
'Goods _ id2' BIGINT (20) not null default '0'
) ENGINE = innodb default charset = utf8;
# Add a unique constraint
Mysql> alter table syk_test2 add constraint idx_goods_id UNIQUE (goods_id );
ERROR 1142 (42000): ALTER command denied to user 'sky' @ 'localhost' for table 'syk _ test2'
The current user does not have the permission to execute alter ddl for this database. The root user has the permission to log on.
You do not have permission to grant permissions.
Mysql> grant alter on syk. * TO sky @ '% ';
Query OK, 0 rows affected (0.00 sec)
Switch back to the sky. The user adds a unique constraint again.
Songyunkui @ syk_ubuntu :~ $ Mysql-usky-p-h127.0.0.1
Mysql> use syk;
Database changed
Mysql> alter table syk_test2 add constraint idx_goods_id UNIQUE (goods_id );
ERROR 1062 (23000): Duplicate entry '000000' for key 'idx _ goods_id'
Khan: The test data is not unique. Delete it.
Mysql> delete from syk_test2 WHERE goods_id = 100 AND goods_type = 10011;
Query OK, 1 row affected (0.06 sec)
Try again
Mysql> alter table syk_test2 add constraint uni_goods_id UNIQUE (goods_id );
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
*/

Finally, operate Mysql table pai_^ on pg.
Test = # delete from syk_test2 where goods_id = 0;
Test = #
Test = # select * from syk_test2;
Goods_id | goods_type | goods_id2
---------- + ------------ + -----------
1 | I, I, and I, Cao | 0
86 | 20110 | 0
88 | 20010 | 0
100 | 10010 | 0
(4 rows)

######### FDW to HIVE
Https://github.com/youngwookim/hive-fdw-for-postgresql
$ Python setup. py sdist
$ Sudo python setup. py install

Drop extension multicorn;
Create extension multicorn;
Drop server multicorn_hive;
Create server multicorn_hive foreign data wrapper multicorn OPTIONS (
WRAPPER 'hivefdw. hiveforeigndatawrapper'
);
Create user mapping for public server multicorn_hive;

Drop foreign table hive;
Create foreign table hive (
Cat_id INT,
Cat_name VARCHAR,
Cat_name_en VARCHAR,
Type INT,
P_id INT,
Deep INT
) SERVER multicorn_hive OPTIONS (
HOST 'c3-hadoop-hive01.bj ',
PORT '18xxx ',
TABLE 'B2C _ d. xm_config_cat'
);

\! Kinit-kt h_ B2C _dc_r.keytab h_ B2C _dc_r@XXX.HADOOP here there is a kerberos Verification

SELECT * FROMhive;
Drop foreign table hive_query;

Create foreign table hive_query (cat_id INT, cat_name VARCHAR) SERVER multicorn_hive OPTIONS (
HOST 'c3-hadoop-hive01.bj ',
PORT '18xxx ',
QUERY 'select cat_id, cat_name from B2C _d.xm_config_cat'
);