AnalyticDB:外部テーブルを使用した Hadoop データソースのフェデレーテッド分析

LOCATION 句のパラメーター

サポートされている HDFS プロファイル

例：HDFS テキストファイルの読み取り

1. HDFS 上にテストファイルを作成します。

   echo 'Prague,Jan,101,4875.33
   Rome,Mar,87,1557.39
   Bangalore,May,317,8936.99
   Beijing,Jul,411,11600.67' > /tmp/pxf_hdfs_simple.txt
   
   # Create the target directory
   hdfs dfs -mkdir -p /data/pxf_examples
   
   # Upload the file
   hdfs dfs -put /tmp/pxf_hdfs_simple.txt /data/pxf_examples/
   
   # Verify the upload
   hdfs dfs -cat /data/pxf_examples/pxf_hdfs_simple.txt

2. AnalyticDB for PostgreSQL で、そのファイルを指す外部テーブルを作成します。

   CREATE EXTERNAL TABLE pxf_hdfs_textsimple (
       location    text,
       month       text,
       num_orders  int,
       total_sales float8
   )
   LOCATION ('pxf://data/pxf_examples/pxf_hdfs_simple.txt?PROFILE=hdfs:text&SERVER=hdp3')
   FORMAT 'TEXT' (delimiter=E',');

3. テーブルをクエリします。

   SELECT * FROM pxf_hdfs_textsimple;

   期待される出力：

    location  | month | num_orders |    total_sales
   -----------+-------+------------+--------------------
    Prague    | Jan   |        101 | 4875.3299999999999
    Rome      | Mar   |         87 | 1557.3900000000001
    Bangalore | May   |        317 | 8936.9899999999998
    Beijing   | Jul   |        411 |           11600.67
   (4 rows)

例：HDFS へのデータ書き込み

AnalyticDB for PostgreSQL から HDFS にデータを書き込むには、書き込み可能な外部テーブルを作成します。

1. HDFS 上にターゲットディレクトリを作成します。

   AnalyticDB for PostgreSQL から INSERT 文を実行するには、このディレクトリに対する書き込み権限が必要です。

   hdfs dfs -mkdir -p /data/pxf_examples/pxfwritable_hdfs_textsimple1

2. 書き込み可能な外部テーブルを作成し、データを挿入します。

   CREATE WRITABLE EXTERNAL TABLE pxf_hdfs_writabletbl_1 (
       location    text,
       month       text,
       num_orders  int,
       total_sales float8
   )
   LOCATION ('pxf://data/pxf_examples/pxfwritable_hdfs_textsimple1?PROFILE=hdfs:text&SERVER=hdp3')
   FORMAT 'TEXT' (delimiter=',');
   
   INSERT INTO pxf_hdfs_writabletbl_1 VALUES ('Frankfurt', 'Mar', 777, 3956.98);
   INSERT INTO pxf_hdfs_writabletbl_1 VALUES ('Cleveland', 'Oct', 3812, 96645.37);

3. データが HDFS に書き込まれたことを確認します。

   # List files in the directory
   hdfs dfs -ls /data/pxf_examples/pxfwritable_hdfs_textsimple1
   
   # Print the file contents
   hdfs dfs -cat /data/pxf_examples/pxfwritable_hdfs_textsimple1/*

   期待される出力：

   Frankfurt,Mar,777,3956.98
   Cleveland,Oct,3812,96645.37

サポートされている Hive プロファイル

Hive プロファイルは、上記のすべてのストレージフォーマットをサポートします。特定の機能が必要な場合は、特定のサブプロファイル (HiveText、HiveRC、HiveORC、HiveVectorizedORC) を使用してください。

HiveORC と HiveVectorizedORC の選択

どちらのプロファイルも ORC フォーマットの Hive テーブルを読み取ります。クエリの要件に基づいて選択してください：

例：Hive プロファイルの使用

Hive プロファイルは、すべての Hive ストレージフォーマットで動作します。

1. サンプルデータを生成し、Hive にロードします。

   echo 'Prague,Jan,101,4875.33
   Rome,Mar,87,1557.39
   Bangalore,May,317,8936.99
   Beijing,Jul,411,11600.67
   San Francisco,Sept,156,6846.34
   Paris,Nov,159,7134.56
   San Francisco,Jan,113,5397.89
   Prague,Dec,333,9894.77
   Bangalore,Jul,271,8320.55
   Beijing,Dec,100,4248.41' > /tmp/pxf_hive_datafile.txt

   -- In Hive
   CREATE TABLE sales_info (
       location         string,
       month            string,
       number_of_orders int,
       total_sales      double
   )
   ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
   STORED AS textfile;
   
   LOAD DATA LOCAL INPATH '/tmp/pxf_hive_datafile.txt' INTO TABLE sales_info;
   SELECT * FROM sales_info;

2. AnalyticDB for PostgreSQL で、PXF 拡張機能を有効にし、外部テーブルを作成します。

   CREATE EXTENSION pxf;
   
   CREATE EXTERNAL TABLE salesinfo_hiveprofile (
       location    text,
       month       text,
       num_orders  int,
       total_sales float8
   )
   LOCATION ('pxf://default.sales_info?PROFILE=Hive&SERVER=hdp3')
   FORMAT 'custom' (formatter='pxfwritable_import');

3. テーブルをクエリします。

   SELECT * FROM salesinfo_hiveprofile;

   期待される出力：

      location    | month | num_orders | total_sales
   ---------------+-------+------------+-------------
    Prague        | Jan   |        101 |     4875.33
    Rome          | Mar   |         87 |     1557.39
    Bangalore     | May   |        317 |     8936.99
    Beijing       | Jul   |        411 |    11600.67
    San Francisco | Sept  |        156 |     6846.34
    Paris         | Nov   |        159 |     7134.56
   ......

例：HiveText プロファイルの使用

HiveText は TextFile フォーマットの Hive テーブルを読み取り、pxfwritable_import フォーマッターの代わりにテキストデリミタを使用します。

CREATE EXTERNAL TABLE salesinfo_hivetextprofile (
    location    text,
    month       text,
    num_orders  int,
    total_sales float8
)
LOCATION ('pxf://default.sales_info?PROFILE=HiveText&SERVER=hdp3')
FORMAT 'TEXT' (delimiter=E',');

SELECT * FROM salesinfo_hivetextprofile;

期待される出力：

   location    | month | num_orders | total_sales
---------------+-------+------------+-------------
 Prague        | Jan   |        101 |     4875.33
 Rome          | Mar   |         87 |     1557.39
 Bangalore     | May   |        317 |     8936.99
 ......

例：HiveRC プロファイルの使用

1. RCFile フォーマットの Hive テーブルを作成します。

   -- In Hive
   CREATE TABLE sales_info_rcfile (
       location         string,
       month            string,
       number_of_orders int,
       total_sales      double
   )
   ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
   STORED AS rcfile;
   
   -- Import data from the existing table
   INSERT INTO TABLE sales_info_rcfile SELECT * FROM sales_info;
   
   -- Verify the data
   SELECT * FROM sales_info_rcfile;

2. AnalyticDB for PostgreSQL で外部テーブルを作成し、クエリを実行します。

   CREATE EXTERNAL TABLE salesinfo_hivercprofile (
       location    text,
       month       text,
       num_orders  int,
       total_sales float8
   )
   LOCATION ('pxf://default.sales_info_rcfile?PROFILE=HiveRC&SERVER=hdp3')
   FORMAT 'TEXT' (delimiter=E',');
   
   SELECT location, total_sales FROM salesinfo_hivercprofile;

   期待される出力：

      location    | total_sales
   ---------------+-------------
    Prague        |     4875.33
    Rome          |     1557.39
    Bangalore     |     8936.99
    ......

例：HiveORC プロファイルの使用

列プロジェクションが必要な場合や、複雑な型 (array、map、struct、union) を持つテーブルをクエリする必要がある場合は、HiveORC を使用します。

1. ORC フォーマットの Hive テーブルを作成します。

   -- In Hive
   CREATE TABLE sales_info_ORC (
       location         string,
       month            string,
       number_of_orders int,
       total_sales      double
   )
   STORED AS ORC;
   
   INSERT INTO TABLE sales_info_ORC SELECT * FROM sales_info;
   
   -- Verify the data
   SELECT * FROM sales_info_ORC;

2. AnalyticDB for PostgreSQL で外部テーブルを作成し、クエリを実行します。

   CREATE EXTERNAL TABLE salesinfo_hiveORCprofile (
       location    text,
       month       text,
       num_orders  int,
       total_sales float8
   )
   LOCATION ('pxf://default.sales_info_ORC?PROFILE=HiveORC&SERVER=hdp3')
   FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
   
   SELECT * FROM salesinfo_hiveORCprofile;

   期待される出力：

    ......
    Prague        | Dec   |        333 |     9894.77
    Bangalore     | Jul   |        271 |     8320.55
    Beijing       | Dec   |        100 |     4248.41
   (60 rows)
   Time: 420.920 ms

例：HiveVectorizedORC プロファイルの使用

列プロジェクション、複雑な型のサポート、または timestamp データの型が不要な大規模な ORC テーブルに対する単純なクエリには、HiveVectorizedORC を使用します。

CREATE EXTERNAL TABLE salesinfo_hiveVectORC (
    location    text,
    month       text,
    num_orders  int,
    total_sales float8
)
LOCATION ('pxf://default.sales_info_ORC?PROFILE=HiveVectorizedORC&SERVER=hdp3')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

SELECT * FROM salesinfo_hiveVectORC;

期待される出力：

   location    | month | num_orders | total_sales
---------------+-------+------------+-------------
 Prague        | Jan   |        101 |     4875.33
 Rome          | Mar   |         87 |     1557.39
 Bangalore     | May   |        317 |     8936.99
 Beijing       | Jul   |        411 |    11600.67
 San Francisco | Sept  |        156 |     6846.34
 ......

例：Parquet フォーマットの Hive テーブルのクエリ

1. Parquet フォーマットの Hive テーブルを作成します。

   -- In Hive
   CREATE TABLE hive_parquet_table (
       location         string,
       month            string,
       number_of_orders int,
       total_sales      double
   )
   STORED AS parquet;
   
   INSERT INTO TABLE hive_parquet_table SELECT * FROM sales_info;
   
   SELECT * FROM hive_parquet_table;

2. AnalyticDB for PostgreSQL で外部テーブルを作成し、クエリを実行します。

   CREATE EXTERNAL TABLE pxf_parquet_table (
       location         text,
       month            text,
       number_of_orders int,
       total_sales      double precision
   )
   LOCATION ('pxf://default.hive_parquet_table?profile=Hive&SERVER=hdp3')
   FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
   
   SELECT month, number_of_orders FROM pxf_parquet_table;

   期待される出力：

    month | number_of_orders
   -------+------------------
    Jan   |              101
    Mar   |               87
    May   |              317
    Jul   |              411
    Sept  |              156
    Nov   |              159
    ......