DLA 分析 AnalyticDB for PostgreSQL 資料源的資料

簡介

資料湖分析（Data Lake Analytics，DLA）是無伺服器（Serverless）化的雲上互動式查詢分析服務。無需ETL，就可通過DLA在雲上通過标準JDBC直接對阿裡雲OSS，TableStore，RDS，MongoDB等不同資料源中存儲的資料進行查詢和分析。DLA無縫內建各類商業分析工具，提供便捷的資料可視化。

DLA提供了幾大核心亮點：

• 輕松分析多源資料：OSS，TableStore，RDS等，讓不同存儲源中沉睡已久的資料，具備分析能力。
• 能夠對異構資料源做關聯分析。
• 全Serverless結構，無需長期持有成本，完全按需使用，更靈活，資源伸縮友善，更新無感覺。

AnalyticDB for PostgreSQL 中文名為分析型資料庫PostgreSQL版（原HybridDB for PostgreSQL）是一種簡單、快速、經濟高效的 PB 級雲端資料倉庫解決方案。分析型資料庫PostgreSQL版 相容 Greenplum 開源資料倉庫，為一種采用 MPP 全并行架構的數倉服務，其廣泛相容 PostgreSQL/Oracle 的文法生态，新一代向量引擎性能超越傳統資料庫引擎 10 倍以上，分布式SQL優化器實作複雜查詢語句免調優。通過分析型資料庫PostgreSQL版可以實作對海量資料的即席查詢分析、ETL 處理及可視化探索，是各行業有競争力的雲上資料倉庫解決方案。

随着 DLA 使用的使用者數越來越多，通過 DLA 讀寫 AnalyticDB for PostgreSQL 并和其他資料源整合分析已經成為客戶一個很重要的需求，基于這個情況，我們為 DLA 添加了讀寫 AnalyticDB for PostgreSQL 資料源的功能。

如何使用

AnalyticDB for PostgreSQL 資料準備

我們先在已經購買好的 AnalyticDB for PostgreSQL 裡面建一張表吧，如下：

CREATE TABLE products ( 
         product_no integer PRIMARY KEY,
         name text,
         price numeric)
DISTRIBUTED BY (product_no);           

這張表預設是建立在名為 postgres 的資料庫裡面，對應的 Schema 為 public，我們往裡面插入幾條資料：

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);           

我們可以檢視下已經插入進去的資料

通過 DLA 操作 AnalyticDB for PostgreSQL

為了讓 DLA 能夠通路 AnalyticDB for PostgreSQL，需要到 AnalyticDB for PostgreSQL 對應的執行個體裡面添加 100.104.0.0/16 IP 白名單。

現在我們可以在 DLA 裡面讀寫這張表了，具體如下。通過 DLA 控制台建立一張資料庫：

CREATE SCHEMA `dla_adbpg_test_db` WITH DBPROPERTIES (
  CATALOG = 'adbpg',
  LOCATION = 'jdbc:postgresql://gp-xxxxx.gpdb.rds.aliyuncs.com:3432/postgres',
  USER = 'xxxx',
  PASSWORD = 'xxxxx',
  INSTANCE_ID = 'xxxxxx',
  VPC_ID = 'x'xxx
);            

說明：

• 這裡 CATALOG 需要寫 adbpg，代表資料源是 AnalyticDB for PostgreSQL
• LOCATION：這裡到你 AnalyticDB for PostgreSQL 控制台裡面看下對應 AnalyticDB for PostgreSQL 執行個體的内網位址，因為我這裡是預設的資料庫，是以寫 postgres。這裡需要根據你實際情況寫對應的資料庫名稱。
• USER 和 PASSWORD 對應的是你 AnalyticDB for PostgreSQL 的使用者名和密碼；
• INSTANCE_ID：對應 AnalyticDB for PostgreSQL 執行個體ID
• VPC_ID： 對應的是 VPC id。

建表文法

文法一

create external table dla_tablename (
       column1 datatype,
       column2 datatype,
       column3 datatype
) tblproperties(
  table_mapping = 'schema_name.adbpg_tablename'
);           

文法二

create external table dla_tablename like mapping('schema_name.adbpg_tablename');           

msck repair database dla_adbpg_db           

注意：因為 AnalyticDB for PostgreSQL 裡面一個 db 裡面會有多個 schema，不同 schema 裡面的表名可能會有重複，是以目前 msck repair database 隻能識别出 public schema 裡面的表。

我們使用上面的建表文法一，在 DLA 裡面建立一張表和剛剛 AnalyticDB for PostgreSQL 上面的 products 表映射：

create external table products (
       product_no int,
       name varchar(1023),
       price double
) tblproperties(
  table_mapping = 'public.products'
);           

當然，如果我們不知道 AnalyticDB for PostgreSQL 中對應庫裡面有什麼表，或者表太多，我不想一張一張到 DLA 裡面建立映射，那我們也可以使用下面指令自動識别 AnalyticDB for PostgreSQL 對應庫裡面的表：

msck repair database dla_adbpg_test_db           

現在我們可以通過 DLA 查詢 AnalyticDB for PostgreSQL 裡面 products 表中的資料：

當然，我們也可以通過 DLA 往 AnalyticDB for PostgreSQL 中的 products 表插入資料：

insert into dla_adbpg_test_db.products values(4, 'Apple', 10.1)           

為了确定這條資料的确插入到 AnalyticDB for PostgreSQL 中的 products 表裡面，我們到 DMS 中檢視這張表的資料：

可以看到，這條資料确實插進去了。

快去體驗 DLA 讀寫 AnalyticDB for PostgreSQL 裡面的表吧。