iT邦幫忙

1

以Postgresql為主,再聊聊資料庫 使用MySQL_FDW 抓取MySQL 範例資料庫Sakila 並用PG13 新的pg_dump 備份

postgresql mysql rdbms
一級屠豬士 2020-11-15 16:37:02 ‧ 2411 瀏覽

  • 分享至 

  • xImage
    •   
MySQL Sakila Sample Database

https://dev.mysql.com/doc/sakila/en/sakila-installation.html

https://dev.mysql.com/doc/index-other.html

---------
download , and tar -zxvf sakila-db.tar.gz 

(root) [(none)]> source sakila-schema.sql;
(root) [sakila]> source sakila-data.sql;

(root) [sakila]> SHOW FULL TABLES;
+----------------------------+------------+
| Tables_in_sakila           | Table_type |
+----------------------------+------------+
| actor                      | BASE TABLE |
| actor_info                 | VIEW       |
| address                    | BASE TABLE |
| category                   | BASE TABLE |
| city                       | BASE TABLE |
| country                    | BASE TABLE |
| customer                   | BASE TABLE |
| customer_list              | VIEW       |
| film                       | BASE TABLE |
| film_actor                 | BASE TABLE |
| film_category              | BASE TABLE |
| film_list                  | VIEW       |
| film_text                  | BASE TABLE |
| inventory                  | BASE TABLE |
| language                   | BASE TABLE |
| nicer_but_slower_film_list | VIEW       |
| payment                    | BASE TABLE |
| rental                     | BASE TABLE |
| sales_by_film_category     | VIEW       |
| sales_by_store             | VIEW       |
| staff                      | BASE TABLE |
| staff_list                 | VIEW       |
| store                      | BASE TABLE |
+----------------------------+------------+
23 rows in set (0.01 sec)


(root) [sakila]> SELECT COUNT(*) FROM film;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+

(root) [sakila]> SELECT COUNT(*) FROM film_text;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+

------------------------

grant all privileges on sakila.* to 'miku'@'%';

flush privileges;

------
|=> mysql -u miku -p -D sakila

(miku) [sakila]> show FULL TABLES;
+----------------------------+------------+
| Tables_in_sakila           | Table_type |
+----------------------------+------------+
| actor                      | BASE TABLE |
| actor_info                 | VIEW       |
| address                    | BASE TABLE |
| category                   | BASE TABLE |
| city                       | BASE TABLE |
| country                    | BASE TABLE |
| customer                   | BASE TABLE |
| customer_list              | VIEW       |
| film                       | BASE TABLE |
| film_actor                 | BASE TABLE |
| film_category              | BASE TABLE |
| film_list                  | VIEW       |
| film_text                  | BASE TABLE |
| inventory                  | BASE TABLE |
| language                   | BASE TABLE |
| nicer_but_slower_film_list | VIEW       |
| payment                    | BASE TABLE |
| rental                     | BASE TABLE |
| sales_by_film_category     | VIEW       |
| sales_by_store             | VIEW       |
| staff                      | BASE TABLE |
| staff_list                 | VIEW       |
| store                      | BASE TABLE |
+----------------------------+------------+
23 rows in set (0.00 sec)

(miku) [sakila]> 
-------------------

https://github.com/EnterpriseDB/mysql_fdw

--------
-- create role and database for MySQL Sakila Sample Database
-- create tablespace 

cd /usr/local/var 

mkdir sakila

-- tablespace path : /usr/local/var/sakila 

-- login with superuser privilege 

create tablespace sakila location '/usr/local/var/sakila';

create role sakila with login superuser password 'sakila';

commit;

select rolsuper
     , rolpassword
  from pg_authid
 where rolname = 'sakila';

 rolsuper |             rolpassword             
----------+-------------------------------------
 t        | md50317187bad27817ed0bcc145db708d33

create database sakila owner sakila tablespace sakila encoding UTF8;

\l+ sakila
                                                List of databases
  Name  | Owner  | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace | Description 
--------+--------+----------+-------------+-------------+-------------------+---------+------------+-------------
 sakila | sakila | UTF8     | zh_TW.UTF-8 | zh_TW.UTF-8 |                   | 7849 kB | sakila     | 

-----------
-- install mysql_fdw

unzip mysql_fdw-master.zip 

cd mysql_fdw-master

export PATH=/usr/local/mysql/bin/:$PATH

make USE_PGXS=1

make USE_PGXS=1 install

---------
-- login as sakila


select current_schema;
 current_schema 
----------------
 public

show search_path;
   search_path   
-----------------
 "$user", public

create schema sakila;
create schema pakila;

commit;

alter database sakila set search_path="$user", public, pakila;

alter role sakila set search_path="$user", public, pakila;

commit;

-- quit and login again

show search_path;
       search_path       
-------------------------
 "$user", public, pakila

select current_schema;
 current_schema 
----------------
 sakila

-- create mysql_fdw extension

create extension mysql_fdw with schema public;

commit;

sakila[sakila]# \dx
                            List of installed extensions
   Name    | Version |   Schema   |                   Description                    
-----------+---------+------------+--------------------------------------------------
 mysql_fdw | 1.1     | public     | Foreign data wrapper for querying a MySQL server
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

---------------

# in PostgreSQL
# 先建立一個 連線的 server , 取名為 mysql_server

create server mysql_server
foreign data wrapper mysql_fdw
options (host '127.0.0.1', port '3306');

commit;

# 建立一個 mapping user, for 現在登入的 pg user
# 密碼以及 mysql 的 user, 要記得置換.

create user mapping for sakila
server mysql_server
options (username 'miku', password 'YourPasswd');

commit;

-----------
-- import foreign schema

import foreign schema sakila
from server mysql_server
into pakila;

-- 因為 MySQL Sakila 有自定義資料型態或者新的 空間資料型態, 我們需要將這些table先排除掉.
-- 後面再手動補上. geometry  是 SRID , 先用 bit(32) 以利傳輸, 但是實際上使用時資料會都是 0.
-- view 我們也排除.
https://dev.mysql.com/doc/refman/8.0/en/spatial-function-argument-handling.html

import foreign schema sakila
EXCEPT (address, film, film_list, nicer_but_slower_film_list)
from server mysql_server
into pakila;

commit;

\d
                    List of relations
 Schema |          Name          |     Type      | Owner  
--------+------------------------+---------------+--------
 pakila | actor                  | foreign table | sakila
 pakila | actor_info             | foreign table | sakila
 pakila | category               | foreign table | sakila
 pakila | city                   | foreign table | sakila
 pakila | country                | foreign table | sakila
 pakila | customer               | foreign table | sakila
 pakila | customer_list          | foreign table | sakila
 pakila | film_actor             | foreign table | sakila
 pakila | film_category          | foreign table | sakila
 pakila | film_text              | foreign table | sakila
 pakila | inventory              | foreign table | sakila
 pakila | language               | foreign table | sakila
 pakila | payment                | foreign table | sakila
 pakila | rental                 | foreign table | sakila
 pakila | sales_by_film_category | foreign table | sakila
 pakila | sales_by_store         | foreign table | sakila
 pakila | staff                  | foreign table | sakila
 pakila | staff_list             | foreign table | sakila
 pakila | store                  | foreign table | sakila
(19 rows)

---
CREATE TYPE rating_t AS enum('G','PG','PG-13','R','NC-17');

commit;

CREATE FOREIGN TABLE film (
  film_id smallint NOT NULL,
  title varchar(128) NOT NULL,
  description text,
  release_year smallint,
  language_id smallint NOT NULL,
  original_language_id smallint,
  rental_duration smallint NOT NULL,
  rental_rate decimal NOT NULL,
  length smallint,
  replacement_cost decimal NOT NULL,
  rating rating_t,
  special_features text,
  last_update timestamp NOT NULL
) SERVER mysql_server OPTIONS (dbname 'sakila', table_name 'film');

commit;

select * from film limit 1;
-[ RECORD 1 ]--------+-------------------------------------------------------------------------------------------------
film_id              | 1
title                | ACADEMY DINOSAUR
description          | A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies
release_year         | 2006
language_id          | 1
original_language_id | NULL
rental_duration      | 6
rental_rate          | 0.99
length               | 86
replacement_cost     | 20.99
rating               | PG
special_features     | Deleted Scenes,Behind the Scenes
last_update          | 2006-02-15 05:03:42


CREATE FOREIGN TABLE address (
  address_id smallint NOT NULL,
  address varchar(50) NOT NULL,
  address2 varchar(50),
  district varchar(20) NOT NULL,
  city_id smallint NOT NULL,
  postal_code varchar(10),
  phone varchar(20) NOT NULL,
  location bit(32) NOT NULL,
  last_update timestamp NOT NULL
) SERVER mysql_server OPTIONS (dbname 'sakila', table_name 'address');

commit;

select * from address limit 1;
-[ RECORD 1 ]--------------------
address_id  | 1
address     | 47 MySakila Drive
address2    | NULL
district    | Alberta
city_id     | 300
postal_code | 
phone       | 
location    | 0
last_update | 2014-09-25 22:30:27


\d
                    List of relations
 Schema |          Name          |     Type      | Owner  
--------+------------------------+---------------+--------
 pakila | actor                  | foreign table | sakila
 pakila | actor_info             | foreign table | sakila
 pakila | category               | foreign table | sakila
 pakila | city                   | foreign table | sakila
 pakila | country                | foreign table | sakila
 pakila | customer               | foreign table | sakila
 pakila | customer_list          | foreign table | sakila
 pakila | film_actor             | foreign table | sakila
 pakila | film_category          | foreign table | sakila
 pakila | film_text              | foreign table | sakila
 pakila | inventory              | foreign table | sakila
 pakila | language               | foreign table | sakila
 pakila | payment                | foreign table | sakila
 pakila | rental                 | foreign table | sakila
 pakila | sales_by_film_category | foreign table | sakila
 pakila | sales_by_store         | foreign table | sakila
 pakila | staff                  | foreign table | sakila
 pakila | staff_list             | foreign table | sakila
 pakila | store                  | foreign table | sakila
 sakila | address                | foreign table | sakila
 sakila | film                   | foreign table | sakila
(21 rows)

-----------
-- pg_dump --include-foreign-data

pg_dump -U sakila -W -d sakila --include-foreign-data=mysql_server -f mysakila.sql


-- 會建立 FOREIGN SERVER ,以及各 table 的 schema, data.
-- 以下為部分
---------------
--
-- Name: country; Type: FOREIGN TABLE; Schema: pakila; Owner: sakila
--

CREATE FOREIGN TABLE pakila.country (
    country_id smallint NOT NULL,
    country character varying(50) NOT NULL,
    last_update timestamp without time zone NOT NULL
)
SERVER mysql_server
OPTIONS (
    dbname 'sakila',
    table_name 'country'
);


ALTER FOREIGN TABLE pakila.country OWNER TO sakila;
---------------
-- 資料部分
--
-- Data for Name: actor; Type: TABLE DATA; Schema: pakila; Owner: sakila
--

COPY pakila.actor (actor_id, first_name, last_name, last_update) FROM stdin;
1       PENELOPE        GUINESS 2006-02-15 04:34:33
2       NICK    WAHLBERG        2006-02-15 04:34:33
3       ED      CHASE   2006-02-15 04:34:33
4       JENNIFER        DAVIS   2006-02-15 04:34:33
5       JOHNNY  LOLLOBRIGIDA    2006-02-15 04:34:33
6       BETTE   NICHOLSON       2006-02-15 04:34:33
7       GRACE   MOSTEL  2006-02-15 04:34:33
8       MATTHEW JOHANSSON       2006-02-15 04:34:33
9       JOE     SWANK   2006-02-15 04:34:33
10      CHRISTIAN       GABLE   2006-02-15 04:34:33
11      ZERO    CAGE    2006-02-15 04:34:33
12      KARL    BERRY   2006-02-15 04:34:33
13      UMA     WOOD    2006-02-15 04:34:33
14      VIVIEN  BERGEN  2006-02-15 04:34:33

---------------
PostgreSQL 13 現在的功能更完整了!

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 則留言

0
chang0206
iT邦新手 1 級 ‧ 2020-11-16 15:49:09

請問大師，有沒有postgresql 使用GPU 加速的經驗可以分享？

一級屠豬士 iT邦大師 1 級 ‧ 2020-11-16 20:34:52 檢舉

PostgreSQL 使用 GPU 加速, 主要是 PG-Strom

https://heterodb.github.io/pg-strom/

這是日本的 海外 浩平 KaiGai Kohei開發的

https://kaigai.hatenablog.com/

目前有跟 Apache Arrow 整合, 還有開發了 pg2arrow, mysql2arrow.

這有他的一系列簡報資料
https://www2.slideshare.net/kaigai/20201113pgconfjapangpupostgis

PostgreSQL 要加速,除了使用 GPU, 還有其他方式.
我會再安排適當的場合介紹.

登入發表回應

我要留言

立即登入留言
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22203 篇
完賽人數
602 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙