ODPS开发大全：入门篇（3）

--MaxCompute分区表DDLCREATE TABLE IF NOT EXISTS public_data.dwd_product_movie_basic_info(  movie_name STRING COMMENT '电影名称',  dirctor STRING COMMENT '导演',  scriptwriter STRING COMMENT '编剧',  area STRING COMMENT '制片地区/国家',  actors STRING COMMENT '主演',  `type` STRING COMMENT '类型',  movie_length STRING COMMENT '电影长度',  movie_date STRING COMMENT '上映日期',  movie_language STRING COMMENT '语言',  imdb_url STRING COMMENT 'imdb号') PARTITIONED BY (ds STRING) STORED AS ALIORC;
--查看分区表的某个分区数据SELECT * FROM public_data.xxxx_movie_basic_info WHERE ds = '20170112';

import foreign schema public_data limit to (dwd_product_movie_basic_info) from server odps_server into public options(if_table_exist 'update');

BEGIN;CREATE TABLE "public"."holo_dwd_product_movie_basic_info" (  "movie_name" text,  "dirctor" text,  "scriptwriter" text,  "area" text,  "actors" text,  "type" text,  "movie_length" text,  "movie_date" text,  "movie_language" text,  "imdb_url" text,  "ds" text)PARTITION BY LIST (ds);CALL SET_TABLE_PROPERTY('"public"."holo_dwd_product_movie_basic_info"', 'orientation', 'column');CALL SET_TABLE_PROPERTY('"public"."holo_dwd_product_movie_basic_info"', 'bitmap_columns', '"movie_name","dirctor","scriptwriter","area","actors","type","movie_length","movie_date","movie_language","imdb_url","ds"');CALL SET_TABLE_PROPERTY('"public"."holo_dwd_product_movie_basic_info"', 'dictionary_encoding_columns', '"movie_name:auto","dirctor:auto","scriptwriter:auto","area:auto","actors:auto","type:auto","movie_length:auto","movie_date:auto","movie_language:auto","imdb_url:auto","ds:auto"');CALL SET_TABLE_PROPERTY('"public"."holo_dwd_product_movie_basic_info"', 'time_to_live_in_seconds', '3153600000');comment on column "public"."holo_dwd_product_movie_basic_info"."movie_name" is '电影名称';comment on column "public"."holo_dwd_product_movie_basic_info"."dirctor" is '导演';comment on column "public"."holo_dwd_product_movie_basic_info"."scriptwriter" is '编剧';comment on column "public"."holo_dwd_product_movie_basic_info"."area" is '制片地区/国家';comment on column "public"."holo_dwd_product_movie_basic_info"."actors" is '主演';comment on column "public"."holo_dwd_product_movie_basic_info"."type" is '类型';comment on column "public"."holo_dwd_product_movie_basic_info"."movie_length" is '电影长度';comment on column "public"."holo_dwd_product_movie_basic_info"."movie_date" is '上映日期';comment on column "public"."holo_dwd_product_movie_basic_info"."movie_language" is '语言';comment on column "public"."holo_dwd_product_movie_basic_info"."imdb_url" is 'imdb号';COMMIT;

--创建临时分区子表BEGIN;CREATE TABLE IF NOT EXISTS "public".tmp_holo_dwd_product_movie_basic_info_${bizdate}  (  "movie_name" text,  "dirctor" text,  "scriptwriter" text,  "area" text,  "actors" text,  "type" text,  "movie_length" text,  "movie_date" text,  "movie_language" text,  "imdb_url" text,  "ds" text);COMMIT;
--更新外表数据import foreign schema public_data limit to (dwd_product_movie_basic_info) from server odps_server into public options(if_table_exist 'update');
--等待30s再导入Hologres，以防Hologres meta信息更新缓存慢导致的数据不一致而同步不成功select pg_sleep(30); 
--将Maxcompute数据导入临时分区子表INSERT INTO "public".tmp_holo_dwd_product_movie_basic_info_${bizdate} SELECT     "movie_name",    "dirctor",    "scriptwriter",    "area",    "actors",    "type",    "movie_length",    "movie_date",    "movie_language",    "imdb_url",    "ds"FROM "public".dwd_product_movie_basic_infoWHERE ds='${bizdate}';
--导入的场景逻辑比较多，下面有两个场景供参考，可以根据业务逻辑二选一即可
--场景1:导入新的分区数据可以参考以下逻辑，BEGIN;
ALTER TABLE "public".tmp_holo_dwd_product_movie_basic_info_${bizdate} RENAME TO holo_dwd_product_movie_basic_info_${bizdate};
--将临时分区子表绑定在分区父表上ALTER TABLE "public".holo_dwd_product_movie_basic_info ATTACH PARTITION "public".holo_dwd_product_movie_basic_info_${bizdate} FOR VALUES in ('${bizdate}');
COMMIT;
--场景2:重新对历史分区数据刷新可以参考该逻辑BEGIN;
ALTER TABLE IF EXISTS "public".holo_dwd_product_movie_basic_info DETACH PARTITION "public".holo_dwd_product_movie_basic_info_${bizdate};
DROP TABLE IF EXISTS "public".holo_dwd_product_movie_basic_info_${bizdate};
ALTER TABLE "public".tmp_holo_dwd_product_movie_basic_info_${bizdate} RENAME TO holo_dwd_product_movie_basic_info_${bizdate};
--将分区子表绑定在分区父表上ALTER TABLE "public".holo_dwd_product_movie_basic_info ATTACH PARTITION "public".holo_dwd_product_movie_basic_info_${bizdate} FOR VALUES in ('${bizdate}');
COMMIT;

--查看分区子表数据select * from holo_dwd_product_movie_basic_info_20170112;
--查看分区父表总数据select count (*) from holo_dwd_product_movie_basic_info;

-- 1.创建外表CREATE FOREIGN TABLE IF NOT EXISTS ${odps_table_name} (  "user_id" bigint,  "user_name" text,  "ds" text)SERVER odps_serverOPTIONS (project_name 'onetag', table_name '${odps_table_name}');COMMIT;
-- 2.刷新外表的SchemaIMPORT FOREIGN SCHEMA ${odps_project} LIMIT to(  ${odps_table_name}) FROM SERVER odps_server INTO public OPTIONS(if_table_exist 'update',if_unsupported_type 'error');
-- 3.清理潜在的临时表BEGIN ;DROP TABLE IF EXISTS ${holo_table_name}_tmp_${bizhour};COMMIT ;
-- 4.创建临时表BEGIN ;CREATE TABLE IF NOT EXISTS "public".${holo_table_name}_tmp_${bizhour} (  "user_id" bigint,  "user_name" text,  "ds" text,  PRIMARY KEY (user_id,ds));  COMMIT;
-- 5.通过查询外表，向临时表插入数据INSERT INTO ${holo_table_name}_tmp_${bizhour}SELECT *FROM public.${odps_table_name}WHERE ds='${bizhour}';
-- 6.替换子表BEGIN ;
-- 6.1删除已经存在的子表DROP TABLE IF EXISTS ${holo_table_name}_${bizhour};
-- 6.2将临时表改名ALTER TABLE ${holo_table_name}_tmp_${bizhour} RENAME TO ${holo_table_name}_${bizhour};
-- 6.3将临时表绑定至指定分区表ALTER TABLE ${holo_table_name} ATTACH PARTITION ${holo_table_name}_${bizhour}FOR VALUES IN ('${bizhour}');COMMIT ;
-- 7. 大量数据导入后执行ANALYZE分区表父表操作ANALYZE ${holo_table_name};