Pivotal Knowledge Base

フォローする

How To - それぞれ異なるデータベース上にある 2 つのテーブルを結合

環境

製品 バージョン
 GPDB  全てのバージョン

目的

GPDB には、まだ DBLINK 機構が存在しないため、現状異なるデータベース上にあるテーブルに対する結合はサポートしていない。

本記事では、外部テーブルと gpfdist の機能を用いた、それぞれ異なるデータベース上にある 2 つのテーブルを結合する代替手段を提案する。

手順

以下は、異なるデータベース上の 2 つのテーブルに対する結合の実行方法に関する簡単な例である。ここでは、それぞれ異なる 2 つのデータベース上に存在する 2 つのテーブルである source_table と destination_table を結合するものとする。

要注意: ここでの例では、結合元データベースと結合先データベースとが同一ホストに存在するが、この設定をベースに、異なるホスト上のデータベースに対するテーブルの結合を行うことも可能である。

設定

  • 結合元データベース上に、source_table テーブルを作成する。

    gpadmin=# \c source
    You are now connected to database "source" as user "gpadmin".
    
    source=# CREATE TABLE source_table (a char,b int,c int);
    NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
    HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
    CREATE TABLE
    
  • source_table テーブルには現状以下のデータを挿入するものとする。

    source=# INSERT INTO source_table values ('A',generate_series(1,10),1);
    INSERT 0 10
    source=# INSERT INTO source_table values ('B',generate_series(1,10),1);
    INSERT 0 10
    source=# INSERT INTO source_table values ('C',generate_series(1,10),1);
    INSERT 0 10
    
  • 結合先データベース上に、destination_table テーブルを生成する。

    source=# \c destination
    You are now connected to database "destination" as user "gpadmin".
    destination=# CREATE TABLE destination_table (a char,d int,e int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. CREATE TABLE
  • destination_table テーブルには以下のデータを挿入するものとする。

    destination=# INSERT INTO destination_table values ('A',generate_series(1,10),1);
    INSERT 0 10
    destination=# INSERT INTO destination_table values ('D',generate_series(1,10),1);
    INSERT 0 10
    destination=# INSERT INTO destination_table values ('E',generate_series(1,10),1);
    INSERT 0 10
    
  • gpfdist を起動する。

    [gpadmin@gpdbsne ~]$ gpfdist -d /tmp -p 8081 -l /tmp/log1 &
    [1] 3114
    [gpadmin@gpdbsne ~]$ 2015-10-17 03:57:57 3114 INFO Before opening listening sockets - following listening sockets are available:
    2015-10-17 03:57:57 3114 INFO IPV6 socket: [::]:8081
    2015-10-17 03:57:57 3114 INFO IPV4 socket: 0.0.0.0:8081
    2015-10-17 03:57:57 3114 INFO Trying to open listening socket:
    2015-10-17 03:57:57 3114 INFO IPV6 socket: [::]:8081
    2015-10-17 03:57:57 3114 INFO Opening listening socket succeeded
    2015-10-17 03:57:57 3114 INFO Trying to open listening socket:
    2015-10-17 03:57:57 3114 INFO IPV4 socket: 0.0.0.0:8081
    Serving HTTP on port 8081, directory /tmp
    

結合元データベース

  • source_table と同定義の書き込み可能な外部テーブル external_source_table を作成する。

    source=# CREATE WRITABLE EXTERNAL TABLE external_source_table (LIKE source_table) LOCATION ('gpfdist://gpdbsne:8081/source.out')
    source-# FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')
    source-# distributed by (a);
    CREATE EXTERNAL TABLE
    
  • 外部テーブルファイルにデータを書き込む。

    source=# INSERT INTO external_source_table  SELECT * FROM source_table ;
    INSERT 0 30
    

結合先データベース

  • 結合先データベース上に、source_table と同定義の読み込み可能な外部テーブル external_source_table を作成する。

    destination=# CREATE READABLE EXTERNAL TABLE external_source_table (a char , b int , c int) LOCATION ('gpfdist://gpdbsne:8081/source.out')
    destination-# FORMAT 'TEXT' ( DELIMITER '|' NULL ' ');
    CREATE EXTERNAL TABLE
    
  • ここで、destination_table と external_source_table の 2 つのテーブルに対して、以下のように結合を実行する。

    destination=# SELECT *
    destination-# FROM destination_table dest , external_source_table ext 
    destination-# where dest.a=ext.a 
    destination-# AND dest.d=ext.b;
     a | d  | e | a | b  | c 
    ---+----+---+---+----+---
     A |  1 | 1 | A |  1 | 1
     A |  2 | 1 | A |  2 | 1
     A |  3 | 1 | A |  3 | 1
     A |  4 | 1 | A |  4 | 1
     A |  5 | 1 | A |  5 | 1
     A |  6 | 1 | A |  6 | 1
     A |  7 | 1 | A |  7 | 1
     A |  8 | 1 | A |  8 | 1
     A |  9 | 1 | A |  9 | 1
     A | 10 | 1 | A | 10 | 1
    (10 rows)
    

追加情報

外部テーブルと gpfdist に関するより詳細な情報については、ドキュメントを参照のこと。

コメント

Powered by Zendesk