文章目录1、简介2、通过CREATE FOREIGN TABLE加速查询3、通过IMPORT FOREIGN SCHEMA加速查询4、通过Auto Load加速查询1、简介Hologres支持通过创建外部表来加速MaxCompute数据的查询此方法允许您直接在Hologres环境中访问和分析存储在MaxCompute中的数据从 s而提高查询效率并简化数据处理流程。权限说明加速查询MaxCompute数据需要为用户授予访问MaxCompute项目和表的权限。数据类型映射MaxCompute与Hologres数据类型一一映射。方案介绍和选择方案适用场景技术特点通过CREATE FOREIGN TABLE加速查询MaxCompute数据少量表加速、部分列查询、表结构稳定。手动建表灵活定义列和注释。通过IMPORT FOREIGN SCHEMA加速查询MaxCompute数据批量映射整个Schema/DB级别表。自动同步整个Schema下的表结构。通过Auto Load加速查询MaxCompute数据表数量多、表结构频繁变更增删改列。自动检测源表变更支持按需/全量加载。2、通过CREATE FOREIGN TABLE加速查询支持使用CREATE FOREIGN TABLE方式灵活创建MaxCompute外部表可自定义表名称、自由选择列、自定义comments信息等此处以CREATE FOREIGN TABLE方式为例为您介绍通过Hologres查询MaxCompute非分区表和分区表数据的操作步骤。示例一查询MaxCompute非分区表数据在MaxCompute中创建一张非分区表并导入数据。本例直接选用MaxCompute公开数据集BIGDATA_PUBLIC_DATASET.tpcds_10t下的customer表作为示例数据。点击查看该表的DDL语句。-- MaxCompute公共数据集的表DDLCREATETABLEIFNOTEXISTSpublic_data.customer(c_customer_skBIGINT,c_customer_id STRING,c_current_cdemo_skBIGINT,c_current_hdemo_skBIGINT,c_current_addr_skBIGINT,c_first_shipto_date_skBIGINT,c_first_sales_date_skBIGINT,c_salutation STRING,c_first_name STRING,c_last_name STRING,c_preferred_cust_flag STRING,c_birth_dayBIGINT,c_birth_monthBIGINT,c_birth_yearBIGINT,c_birth_country STRING,c_login STRING,c_email_address STRING,c_last_review_date_sk STRING);运行如下命令查看示例表数据。--在MaxCompute中查询表是否有数据SETodps.namespace.schematrue;SELECT*FROMBIGDATA_PUBLIC_DATASET.tpcds_10t.customer;在Hologres中创建一张用于映射MaxCompute数据的外部表。示例语句如下。SEThg_enable_convert_type_for_foreign_tabletrue;CREATEFOREIGNTABLEcustomer(c_customer_skint8,c_customer_idtext,c_current_cdemo_skint8,c_current_hdemo_skint8,c_current_addr_skint8,c_first_shipto_date_skint8,c_first_sales_date_skint8,c_salutationtext,c_first_nametext,c_last_nametext,c_preferred_cust_flagtext,c_birth_dayint8,c_birth_monthint8,c_birth_yearint8,c_birth_countrytext,c_logintext,c_email_addresstext,c_last_review_date_sktext)SERVER odps_server OPTIONS(project_nameBIGDATA_PUBLIC_DATASET.tpcds_10t,table_namecustomer);参数说明如下表所示。参数描述SERVER外部表服务器。 您可以直接调用Hologres底层已创建的名为odps_server的外部表服务器。详细原理请参见Postgres FDW。project_name- 如果您MaxCompute的Project是三层模型模式project_name为MaxCompute的项目名称和Schema名称格式为odps_project_name.odps_schema_name。 - 如果您MaxCompute的Project是两层模型模式project_name为MaxCompute的项目名称。 三层模型详情请参见Schema操作。table_name需要查询的MaxCompute表名称。外部表创建成功后直接在Hologres中查询外部表即可查询到MaxCompute的数据。示例语句如下。SELECT*FROMcustomerLIMIT10;重要若查询报错请确保执行账号拥有MaxCompute表的Select等相关权限。详情请参见权限说明。示例二查询MaxCompute分区表数据在MaxCompute中准备一张分区表并导入数据。本例选用MaxCompute公开数据集BIGDATA_PUBLIC_DATASET.finance下的ods_enterprise_share_trade_h表作为示例数据。点击查看该表的DDL语句。--公共数据集下表的DDLCREATETABLEIFNOTEXISTSpublic_data.ods_enterprise_share_trade_h(code STRINGCOMMENT代码,name STRINGCOMMENT名称,industry STRINGCOMMENT所属行业,area STRINGCOMMENT地区,pe STRINGCOMMENT市盈率,outstanding STRINGCOMMENT流通股本,totals STRINGCOMMENT总股本(万),totalassets STRINGCOMMENT总资产(万),liquidassets STRINGCOMMENT流动资产,fixedassets STRINGCOMMENT固定资产,reserved STRINGCOMMENT公积金,reservedpershare STRINGCOMMENT每股公积金,eps STRINGCOMMENT每股收益,bvps STRINGCOMMENT每股净资,pb STRINGCOMMENT市净率,timetomarket STRINGCOMMENT上市日期,undp STRINGCOMMENT未分利润,perundp STRINGCOMMENT每股未分配,rev STRINGCOMMENT收入同比(%),profit STRINGCOMMENT利润同比(%),gpr STRINGCOMMENT毛利率(%),npr STRINGCOMMENT净利润率(%),holders_num STRINGCOMMENT股东人数)PARTITIONEDBY(ds STRING)STOREDASALIORC TBLPROPERTIES(comment数据导入日期);运行如下命令查看示例表数据。--在MaxCompute中查询某个分区的数据SETodps.namespace.schematrue;SELECT*FROMBIGDATA_PUBLIC_DATASET.finance.ods_enterprise_share_trade_hWHEREds20170113;在Hologres中创建一张用于映射MaxCompute数据的外部表。示例语句如下。CREATEFOREIGNTABLEpublic.foreign_ods_enterprise_share_trade_h(codetext,nametext,industrytext,areatext,petext,outstandingtext,totalstext,totalassetstext,liquidassetstext,fixedassetstext,reservedtext,reservedpersharetext,epstext,bvpstext,pbtext,timetomarkettext,undptext,perundptext,revtext,profittext,gprtext,nprtext,holders_numtext,dstext)SERVER odps_server OPTIONS(project_nameBIGDATA_PUBLIC_DATASET#finance,table_nameods_enterprise_share_trade_h);commentonforeigntablepublic.foreign_ods_enterprise_share_trade_his股票历史交易信息;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.codeis代码;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.nameis名称;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.industryis所属行业;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.areais地区;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.peis市盈率;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.outstandingis流通股本;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.totalsis总股本(万);commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.totalassetsis总资产(万);commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.liquidassetsis流动资产;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.fixedassetsis固定资产;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.reservedis公积金;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.reservedpershareis每股公积金;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.epsis每股收益;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.bvpsis每股净资;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.pbis市净率;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.timetomarketis上市日期;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.undpis未分利润;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.perundpis每股未分配;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.revis收入同比(%);commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.profitis利润同比(%);commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.gpris毛利率(%);commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.npris净利润率(%);commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.holders_numis股东人数;通过Hologres查询MaxCompute分区表数据。查询前10条数据SQL语句如下SELECT*FROMforeign_ods_enterprise_share_trade_hlimit10;查询分区数据示例SQL如下SELECT*FROMforeign_ods_enterprise_share_trade_hWHEREds20170113;重要若查询报错请确保执行账号拥有MaxCompute表的Select等相关权限。详情请参见权限说明。3、通过IMPORT FOREIGN SCHEMA加速查询若您需要批量创建MaxCompute外部表可通过IMPORT FOREIGN SCHEMA方式。示例1为public Schema新建一张外部表若表存在则更新表。IMPORTFOREIGNSCHEMApublic_dataLIMITTO(customer)FROMserver odps_serverINTOPUBLICoptions(if_table_existupdate);示例2为public Schema批量新建外部表。IMPORTFOREIGNSCHEMApublic_dataLIMITTO(customer,customer_address,customer_demographics,inventory,item,date_dim,warehouse)FROMserver odps_serverINTOPUBLICoptions(if_table_existupdate);示例3新建一个testdemo Schema并批量新建外部表。CREATEschematestdemo;IMPORTFOREIGNSCHEMApublic_dataLIMITTO(customer,customer_address,customer_demographics,inventory,item,date_dim,warehouse)FROMserver odps_serverINTOtestdemo options(if_table_existupdate);SETsearch_pathTOtestdemo;示例4在public Schema批量创建外部表已有外表则报错。IMPORTFOREIGNSCHEMApublic_dataLIMITto(customer,customer_address)FROMserver odps_serverINTOPUBLICoptions(if_table_existerror);示例5在public Schema中批量创建外部表已有外表则跳过该外部表。IMPORTFOREIGNSCHEMApublic_dataLIMITto(customer,customer_address)FROMserver odps_serverINTOPUBLICoptions(if_table_existignore);4、通过Auto Load加速查询当实例中需要加速的外部表较多或外部表结构变更比较频繁如在MaxCompute侧执行过删除列、修改列顺序、修改列类型等操作的表时您可以直接使用外部表自动加载Auto Load功能实现MaxCompute数据的按需自动加载以及全量自动加载而无需手动改变外部表的结构从而提高查询效率。实现MaxCompute和OSS数据的按需自动加载以及全量自动加载。应用场景Hologres与云原生大数据计算服务MaxCompute、阿里云数据湖构建Data Lake FormationDLF和阿里云对象存储Object Storage ServiceOSS深度兼容无需数据搬迁即可通过外部表加速查询存储于MaxCompute或OSS的数据。当需要加速的外部表较多时您可以通过自动加载功能自动同步MaxCompute和DLF元数据自动创建Hologres外部表降低手动创建外部表的成本。外部表按需加载主要适用于数据源表数量较少且需要加速查询的场景。当此功能开启后Hologres在查询MaxCompute或OSS中的同名表时会自动创建相应的Hologres外部表以加速数据查询。说明当Hologres自动加载相应MaxCompute或OSS的外部表时如果Hologres内部已经存在同名的Schema和Table自动加载功能将不会触发而是会查询Hologres的内部表。由于自动加载时会创建相应的外部表因此要求查询的账号必须具备在对应数据库中创建和删除Schema及Table的权限。但如果外部表已经通过自动加载创建完成那么只需要查询权限就能进行后续的操作。该功能仅在查询时触发外部自动加载不会周期性加载。外部表全量加载主要适用于数据源表数量较多或多个数据源且需要加速查询的场景。在此功能开启后查询时系统会自动创建与数据源匹配的外部表从而实现所有数据源表的全面映射。此外一旦数据全量加载完成可通过参数设置定期检查确保在查询时能自动创建新添加的外部表。这优化了对大量外部表的管理特别适用于需要提升BI查询效率的环境。
文章目录1、简介2、通过CREATE FOREIGN TABLE加速查询3、通过IMPORT FOREIGN SCHEMA加速查询4、通过Auto Load加速查询1、简介Hologres支持通过创建外部表来加速MaxCompute数据的查询此方法允许您直接在Hologres环境中访问和分析存储在MaxCompute中的数据从 s而提高查询效率并简化数据处理流程。权限说明加速查询MaxCompute数据需要为用户授予访问MaxCompute项目和表的权限。数据类型映射MaxCompute与Hologres数据类型一一映射。方案介绍和选择方案适用场景技术特点通过CREATE FOREIGN TABLE加速查询MaxCompute数据少量表加速、部分列查询、表结构稳定。手动建表灵活定义列和注释。通过IMPORT FOREIGN SCHEMA加速查询MaxCompute数据批量映射整个Schema/DB级别表。自动同步整个Schema下的表结构。通过Auto Load加速查询MaxCompute数据表数量多、表结构频繁变更增删改列。自动检测源表变更支持按需/全量加载。2、通过CREATE FOREIGN TABLE加速查询支持使用CREATE FOREIGN TABLE方式灵活创建MaxCompute外部表可自定义表名称、自由选择列、自定义comments信息等此处以CREATE FOREIGN TABLE方式为例为您介绍通过Hologres查询MaxCompute非分区表和分区表数据的操作步骤。示例一查询MaxCompute非分区表数据在MaxCompute中创建一张非分区表并导入数据。本例直接选用MaxCompute公开数据集BIGDATA_PUBLIC_DATASET.tpcds_10t下的customer表作为示例数据。点击查看该表的DDL语句。-- MaxCompute公共数据集的表DDLCREATETABLEIFNOTEXISTSpublic_data.customer(c_customer_skBIGINT,c_customer_id STRING,c_current_cdemo_skBIGINT,c_current_hdemo_skBIGINT,c_current_addr_skBIGINT,c_first_shipto_date_skBIGINT,c_first_sales_date_skBIGINT,c_salutation STRING,c_first_name STRING,c_last_name STRING,c_preferred_cust_flag STRING,c_birth_dayBIGINT,c_birth_monthBIGINT,c_birth_yearBIGINT,c_birth_country STRING,c_login STRING,c_email_address STRING,c_last_review_date_sk STRING);运行如下命令查看示例表数据。--在MaxCompute中查询表是否有数据SETodps.namespace.schematrue;SELECT*FROMBIGDATA_PUBLIC_DATASET.tpcds_10t.customer;在Hologres中创建一张用于映射MaxCompute数据的外部表。示例语句如下。SEThg_enable_convert_type_for_foreign_tabletrue;CREATEFOREIGNTABLEcustomer(c_customer_skint8,c_customer_idtext,c_current_cdemo_skint8,c_current_hdemo_skint8,c_current_addr_skint8,c_first_shipto_date_skint8,c_first_sales_date_skint8,c_salutationtext,c_first_nametext,c_last_nametext,c_preferred_cust_flagtext,c_birth_dayint8,c_birth_monthint8,c_birth_yearint8,c_birth_countrytext,c_logintext,c_email_addresstext,c_last_review_date_sktext)SERVER odps_server OPTIONS(project_nameBIGDATA_PUBLIC_DATASET.tpcds_10t,table_namecustomer);参数说明如下表所示。参数描述SERVER外部表服务器。 您可以直接调用Hologres底层已创建的名为odps_server的外部表服务器。详细原理请参见Postgres FDW。project_name- 如果您MaxCompute的Project是三层模型模式project_name为MaxCompute的项目名称和Schema名称格式为odps_project_name.odps_schema_name。 - 如果您MaxCompute的Project是两层模型模式project_name为MaxCompute的项目名称。 三层模型详情请参见Schema操作。table_name需要查询的MaxCompute表名称。外部表创建成功后直接在Hologres中查询外部表即可查询到MaxCompute的数据。示例语句如下。SELECT*FROMcustomerLIMIT10;重要若查询报错请确保执行账号拥有MaxCompute表的Select等相关权限。详情请参见权限说明。示例二查询MaxCompute分区表数据在MaxCompute中准备一张分区表并导入数据。本例选用MaxCompute公开数据集BIGDATA_PUBLIC_DATASET.finance下的ods_enterprise_share_trade_h表作为示例数据。点击查看该表的DDL语句。--公共数据集下表的DDLCREATETABLEIFNOTEXISTSpublic_data.ods_enterprise_share_trade_h(code STRINGCOMMENT代码,name STRINGCOMMENT名称,industry STRINGCOMMENT所属行业,area STRINGCOMMENT地区,pe STRINGCOMMENT市盈率,outstanding STRINGCOMMENT流通股本,totals STRINGCOMMENT总股本(万),totalassets STRINGCOMMENT总资产(万),liquidassets STRINGCOMMENT流动资产,fixedassets STRINGCOMMENT固定资产,reserved STRINGCOMMENT公积金,reservedpershare STRINGCOMMENT每股公积金,eps STRINGCOMMENT每股收益,bvps STRINGCOMMENT每股净资,pb STRINGCOMMENT市净率,timetomarket STRINGCOMMENT上市日期,undp STRINGCOMMENT未分利润,perundp STRINGCOMMENT每股未分配,rev STRINGCOMMENT收入同比(%),profit STRINGCOMMENT利润同比(%),gpr STRINGCOMMENT毛利率(%),npr STRINGCOMMENT净利润率(%),holders_num STRINGCOMMENT股东人数)PARTITIONEDBY(ds STRING)STOREDASALIORC TBLPROPERTIES(comment数据导入日期);运行如下命令查看示例表数据。--在MaxCompute中查询某个分区的数据SETodps.namespace.schematrue;SELECT*FROMBIGDATA_PUBLIC_DATASET.finance.ods_enterprise_share_trade_hWHEREds20170113;在Hologres中创建一张用于映射MaxCompute数据的外部表。示例语句如下。CREATEFOREIGNTABLEpublic.foreign_ods_enterprise_share_trade_h(codetext,nametext,industrytext,areatext,petext,outstandingtext,totalstext,totalassetstext,liquidassetstext,fixedassetstext,reservedtext,reservedpersharetext,epstext,bvpstext,pbtext,timetomarkettext,undptext,perundptext,revtext,profittext,gprtext,nprtext,holders_numtext,dstext)SERVER odps_server OPTIONS(project_nameBIGDATA_PUBLIC_DATASET#finance,table_nameods_enterprise_share_trade_h);commentonforeigntablepublic.foreign_ods_enterprise_share_trade_his股票历史交易信息;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.codeis代码;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.nameis名称;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.industryis所属行业;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.areais地区;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.peis市盈率;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.outstandingis流通股本;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.totalsis总股本(万);commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.totalassetsis总资产(万);commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.liquidassetsis流动资产;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.fixedassetsis固定资产;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.reservedis公积金;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.reservedpershareis每股公积金;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.epsis每股收益;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.bvpsis每股净资;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.pbis市净率;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.timetomarketis上市日期;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.undpis未分利润;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.perundpis每股未分配;commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.revis收入同比(%);commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.profitis利润同比(%);commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.gpris毛利率(%);commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.npris净利润率(%);commentoncolumnpublic.foreign_ods_enterprise_share_trade_h.holders_numis股东人数;通过Hologres查询MaxCompute分区表数据。查询前10条数据SQL语句如下SELECT*FROMforeign_ods_enterprise_share_trade_hlimit10;查询分区数据示例SQL如下SELECT*FROMforeign_ods_enterprise_share_trade_hWHEREds20170113;重要若查询报错请确保执行账号拥有MaxCompute表的Select等相关权限。详情请参见权限说明。3、通过IMPORT FOREIGN SCHEMA加速查询若您需要批量创建MaxCompute外部表可通过IMPORT FOREIGN SCHEMA方式。示例1为public Schema新建一张外部表若表存在则更新表。IMPORTFOREIGNSCHEMApublic_dataLIMITTO(customer)FROMserver odps_serverINTOPUBLICoptions(if_table_existupdate);示例2为public Schema批量新建外部表。IMPORTFOREIGNSCHEMApublic_dataLIMITTO(customer,customer_address,customer_demographics,inventory,item,date_dim,warehouse)FROMserver odps_serverINTOPUBLICoptions(if_table_existupdate);示例3新建一个testdemo Schema并批量新建外部表。CREATEschematestdemo;IMPORTFOREIGNSCHEMApublic_dataLIMITTO(customer,customer_address,customer_demographics,inventory,item,date_dim,warehouse)FROMserver odps_serverINTOtestdemo options(if_table_existupdate);SETsearch_pathTOtestdemo;示例4在public Schema批量创建外部表已有外表则报错。IMPORTFOREIGNSCHEMApublic_dataLIMITto(customer,customer_address)FROMserver odps_serverINTOPUBLICoptions(if_table_existerror);示例5在public Schema中批量创建外部表已有外表则跳过该外部表。IMPORTFOREIGNSCHEMApublic_dataLIMITto(customer,customer_address)FROMserver odps_serverINTOPUBLICoptions(if_table_existignore);4、通过Auto Load加速查询当实例中需要加速的外部表较多或外部表结构变更比较频繁如在MaxCompute侧执行过删除列、修改列顺序、修改列类型等操作的表时您可以直接使用外部表自动加载Auto Load功能实现MaxCompute数据的按需自动加载以及全量自动加载而无需手动改变外部表的结构从而提高查询效率。实现MaxCompute和OSS数据的按需自动加载以及全量自动加载。应用场景Hologres与云原生大数据计算服务MaxCompute、阿里云数据湖构建Data Lake FormationDLF和阿里云对象存储Object Storage ServiceOSS深度兼容无需数据搬迁即可通过外部表加速查询存储于MaxCompute或OSS的数据。当需要加速的外部表较多时您可以通过自动加载功能自动同步MaxCompute和DLF元数据自动创建Hologres外部表降低手动创建外部表的成本。外部表按需加载主要适用于数据源表数量较少且需要加速查询的场景。当此功能开启后Hologres在查询MaxCompute或OSS中的同名表时会自动创建相应的Hologres外部表以加速数据查询。说明当Hologres自动加载相应MaxCompute或OSS的外部表时如果Hologres内部已经存在同名的Schema和Table自动加载功能将不会触发而是会查询Hologres的内部表。由于自动加载时会创建相应的外部表因此要求查询的账号必须具备在对应数据库中创建和删除Schema及Table的权限。但如果外部表已经通过自动加载创建完成那么只需要查询权限就能进行后续的操作。该功能仅在查询时触发外部自动加载不会周期性加载。外部表全量加载主要适用于数据源表数量较多或多个数据源且需要加速查询的场景。在此功能开启后查询时系统会自动创建与数据源匹配的外部表从而实现所有数据源表的全面映射。此外一旦数据全量加载完成可通过参数设置定期检查确保在查询时能自动创建新添加的外部表。这优化了对大量外部表的管理特别适用于需要提升BI查询效率的环境。
