全部产品
云市场

读写AnalyticDB for PostgreSQL数据

更新时间:2020-04-24 14:10:39

云数据库AnalyticDB for PostgreSQL(原HybridDB for PostgreSQL)是一种大规模并行处理(MPP)数据仓库服务,兼容PostgreSQL/Oracle数据库生态,支持行存储和列存储模式。数据湖分析DLA(Data Lata Analytics)中支持接入AnalyticDB for PostgreSQL服务,通过标准SQL语句读写AnalyticDB for PostgreSQL中的数据。

说明:目前只有深圳地区支持通过DLA读写AnalyticDB for PostgreSQL数据。

前提条件

  • AnalyticDB for PostgreSQL

    1. 根据AnalyticDB for PostgreSQL快速入门完成创建实例、设置白名单、设置网络类型以及设置账号操作,请参见快速入门

      由于您的AnalyticDB for PostgreSQL实例位于VPC内,默认情况下DLA无法访问该VPC中的资源。为使DLA能够访问AnalyticDB for PostgreSQL,需要通过VPC反向访问技术,即在AnalyticDB for PostgreSQL白名单中添加100.104.0.0/16 IP地址段。

      权限声明:当您在AnalyticDB for PostgreSQL白名单中添加了100.104.0.0/16IP地址段,即视您为同意我们利用VPC反向访问技术访问AnalyticDB for PostgreSQL。

      说明:目前仅深圳地区支持该功能,因此AnalyticDB for PostgreSQL与DLA所属地域均为深圳。

    2. 创建数据库和表,请参见创建数据库创建表

      1. create database adp_demo;
      1. CREATE TABLE products (
      2. product_no integer PRIMARY KEY,
      3. name text,
      4. price numeric)
      5. DISTRIBUTED BY (product_no);

      本文在AnalyticDB for PostgreSQL中创建adp_demo数据库和products表。

    3. 通过INSERT INTO向表中写入测试数据。

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

    1. 开通DLA服务,请参见开通数据湖分析服务

    2. 初始化数据库主账号密码,初始化数据库主账号密码

    3. 创建服务访问点,请参见创建服务访问点

步骤一:创建Schema和表

  1. 连接DLA

  2. 通过以下SQL在DLA中创建AnalyticDB for PostgreSQL Schema。

    1. CREATE SCHEMA dla_adbpg_test_db WITH DBPROPERTIES (
    2. CATALOG = 'adbpg',
    3. LOCATION = 'jdbc:postgresql://gp-bp13******.gpdb.rds.aliyuncs.com:3432/db-name',
    4. USER = 'user-name',
    5. PASSWORD = 'password',
    6. INSTANCE_ID = 'gp-bp*******',
    7. VPC_ID = 'vpc-bp********'
    8. );
    参数 说明
    CATALOG 取值为adbpg,表示创建的是AnalyticDB for PostgreSQL Schema。
    LOCATION AnalyticDB for PostgreSQL的连接信息,由jdbc:postgresql://AnalyticDB for PostgreSQL的内网连接地址:3432/db-name
    USER AnalyticDB for PostgreSQL中的数据库账号。
    PASSWORD USER对应的密码。
    VPC_ID AnalyticDB for PostgreSQL的VPC ID。
    INSTANCE_ID AnalyticDB forPostgreSQL的实例ID。
  3. 通过以下SQL在dla_adbpg_test_db Schema中创建PRODUCTS表。

    • 语法一

      1. create external table dla_tablename (
      2. column1 datatype,
      3. column2 datatype,
      4. column3 datatype
      5. ) tblproperties(
      6. table_mapping = 'schema_name.adbpg_tablename'
      7. );
    • 语法二

      1. create external table dla_tablename like mapping('schema_name.adbpg_tablename');
    • 语法三

      1. msck repair database dla_adbpg_db

      如果AnalyticDB for PostgreSQL中的表数据太大或者无法确认AnalyticDB for PostgreSQL的对应数据库中有哪些表时,您可以在DLA中执行以下SQL命令自动识别AnalyticDB for PostgreSQL对应数据库中的表,一键同步AnalyticDB for PostgreSQL中的表结构到DLA Schema中。

      说明

      由于AnalyticDB for PostgreSQL的一个数据库中有多个Schema,不同Schema中的表名可能有重复,目前msck repair database命令只能识别出public Schema中的表。

      1. create external table products (
      2. product_no int,
      3. name varchar(1023),
      4. price double
      5. ) tblproperties(
      6. table_mapping = 'public.products'
      7. );

      表创建成功后,您就可以通过SELECT在DLA中读取AnalyticDB for PostgreSQL中的数据。

写入数据

您可以通过INSERT INTO向AnalyticDB for PostgreSQL中写入数据,例如向PRODUCTS表中写入一条数据。

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