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 里面的表吧。