使用SQL导入MaxCompute的数据至Hologres

文章目录一、简介二、导入MaxCompute的非分区表数据至Hologres并查询1、准备MaxCompute的非分区表数据。2、在Hologres中创建外部表。3、在Hologres中创建存储表。4、导入数据至Hologres。5、Hologres查询MaxCompute表数据。一、简介当MaxCompute业务数据规模超过200 GB且查询复杂度较高、对响应时间要求达到秒级时Hologres支持将这些数据直接导入内部表进行查询相较于通过外部表查询方式该方式可以设置索引且数据查询效率更高。本文为您介绍不同场景的数据导入操作以及常见问题。注意事项通过SQL导入MaxCompute数据至Hologres时您需要注意如下内容MaxCompute的分区与Hologres无强映射关系MaxCompute的分区字段映射至Hologres为普通字段因此支持MaxCompute分区数据导入Hologres非分区或者分区。Hologres仅支持一级分区MaxCompute多级分区导入Hologres分区表时只需要映射一个分区其余分区映射成Hologres的普通字段。如果导入数据时需要更新覆盖原有数据您需要使用INSERT ON CONFLICT(UPSERT)语法。MaxCompute与Hologres的数据类型映射请参见数据类型汇总。MaxCompute的表数据更新之后在Hologres存在缓存延迟一般为10分钟内建议在导入数据前使用IMPORT FOREIGN SCHEMA语法更新外部表以获取最新数据。导入MaxCompute数据至Hologres时建议使用SQL导入不建议使用数据集成导入因为使用SQL导入性能表现更优。二、导入MaxCompute的非分区表数据至Hologres并查询1、准备MaxCompute的非分区表数据。在MaxCompute中创建一张非分区源数据表或直接选用已创建的非分区表。示例选取MaxCompute公共数据集public_data的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 STRING,useless STRING);-- 在MaxCompute中查询表是否有数据SELECT*FROMpublic_data.customer;部分数据内容显示如下图所示。2、在Hologres中创建外部表。在Hologres中创建一张用于映射MaxCompute源数据表的外部表。CREATEFOREIGNTABLEforeign_customer(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_datetext,uselesstext)SERVER odps_server OPTIONS(project_namepublic_data,table_namecustomer);参数描述Server您可以直接调用Hologres底层已创建的名为odps_server的外部表服务器。详细原理请参见Postgres FDW。Project_NameMaxCompute表所在的项目名称。Table_Name需要查询的MaxCompute表名称。外部表字段的数据类型与MaxCompute表字段的数据类型保持一致。3、在Hologres中创建存储表。在Hologres中创建一张用于接收MaxCompute源表数据的存储表。-- 示例建一张列存表BEGIN;CREATETABLEpublic.holo_customer(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_datetext,uselesstext);CALLSET_TABLE_PROPERTY(public.holo_customer,orientation,column);CALLSET_TABLE_PROPERTY(public.holo_customer,bitmap_columns,c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag,c_birth_country,c_login,c_email_address,c_last_review_date,useless);CALLSET_TABLE_PROPERTY(public.holo_customer,dictionary_encoding_columns,c_customer_id:auto,c_salutation:auto,c_first_name:auto,c_last_name:auto,c_preferred_cust_flag:auto,c_birth_country:auto,c_login:auto,c_email_address:auto,c_last_review_date:auto,useless:auto);CALLSET_TABLE_PROPERTY(public.holo_customer,time_to_live_in_seconds,3153600000);CALLSET_TABLE_PROPERTY(public.holo_customer,storage_format,segment);COMMIT;4、导入数据至Hologres。使用INSERT语句将MaxCompute源头表中的数据导入至Hologres您可以选择部分字段导入字段顺序需要一一对应也可以选择全部字段导入示例DDL如下。-- 可选推荐使用Serverless Computing执行大数据量离线导入和ETL作业SEThg_computing_resourceserverless;--部分字段数据导入INSERTINTOholo_customer(c_customer_sk,c_customer_id,c_email_address,c_last_review_date,useless)SELECTc_customer_sk,c_customer_id,c_email_address,c_last_review_date,uselessFROMforeign_customer;--全部字段数据导入INSERTINTOholo_customerSELECT*FROMforeign_customer;-- 重置配置保证非必要的SQL不会使用serverless资源。RESET hg_computing_resource;5、Hologres查询MaxCompute表数据。在Hologres中查询导入的MaxCompute表数据。示例SQL语句如下。SELECT*FROMholo_customer;