;------------------------------------------------------------------------------例子select * from values (1, 2), (1, 2), (3, 4), (3, 4), (5, 6), (7, 8) t(a, b)except all select * from values (3, 4), (5, 6), (5, 6), (9, 10) t(a, b);--结果+------------+------------+| a | b |+------------+------------+| 1 | 2 || 1 | 2 || 3 | 4 || 7 | 8 |+------------+------------+select_statement1、select_statement2:必填。select语句。
distinct:可选。对取补集的结果去重。
这里先放一张示意图:
--基本格式SELECT column1, column2, ...FROM table1{left outer|right outer|full outer|inner|natural} JOIN table2 ON condition;参数说明:
column1, column2, ...:要选择的字段名称,可以为多个字段。如果不指定字段名称,则会选择所有字段。
table1:要连接的第一个表。
table2:要连接的第二个表。
condition:连接条件,用于指定连接方式。
左连接(left outer join)
可简写为left join
| 返回左表中的所有记录,即使右表中没有与之匹配的记录。
|
右连接(right outer join)
可简写为right join
| 返回右表中的所有记录,即使左表中没有与之匹配的记录。
|
全连接(full outer join)
可简写为full join
| 返回左右表中的所有记录。
|
内连接(inner join)
关键字inner可以省略
| 左右表中至少存在一个匹配行时,inner join返回数据行。
|
自然连接(natural join)
| 参与join的两张表根据字段名称自动决定连接字段。
支持outer natural join,支持使用using子句执行join,输出字段中公共字段只出现一次。
|
隐式连接
| 即不指定join关键字执行连接。
|
多路连接
| 多路join连接。支持通过括号指定join的优先级,括号内的join优先级较高。
|
SEMI JOIN | MaxCompute支持半连接操作,通过右表过滤左表的数据,使右表的数据不出现在结果集中,可以提高查询性能。
|
2. LEFT SEMI JOIN
| 当join条件成立时,返回左表中的数据。如果左表中满足指定条件的某行数据在右表中出现过,则此行保留在结果集中。
|
3. LEFT ANTI JOIN
| 当join条件不成立时,返回左表中的数据。如果左表中满足指定条件的某行数据没有在右表中出现过,则此行保留在结果集中。
|
4. MAPJOIN HINT
| 当对一个大表和一个或多个小表执行join操作时,可以在select语句中显式指定mapjoin Hint提示以提升查询性能。
|
5. 在select语句中,使用Hint提示/*+ mapjoin() */才会执行mapjoin
| 引用小表或子查询时,需要引用别名。
mapjoin支持小表为子查询。
在mapjoin中,可以使用不等值连接或or连接多个条件。您可以通过不写on语句而通过mapjoin on 1 = 1的形式,实现笛卡尔乘积的计算。
mapjoin中多个小表用英文逗号(,)分隔,例如/*+ mapjoin(a,b,c)*/。
|
--允许分区表的全表扫描SET odps.sql.allow.fullscan=true;-- 使用mapjoin查询select /*+ mapjoin(a) */ a.xxxx_name, a.xxxx_price, b.xxxx_pricefrom xxxx_detail_sj a join xxxx_detail bon a.xxxx_price< b.xxxx_price or a.xxxx_price + b.xxxx_price< 500;其原理图:
--方法1:Hint表名(注意Hint的是表的alias)。select /*+ skewjoin(a) */ * from T0 a join T1 b on a.c0 = b.c0 and a.c1 = b.c1;--方法2:Hint表名和认为可能产生倾斜的列,例如表a的c0和c1列存在数据倾斜。select /*+ skewjoin(a(c0, c1)) */ * from T0 a join T1 b on a.c0 = b.c0 and a.c1 = b.c1 and a.c2 = b.c2;--方法3:Hint表名和列,并提供发生倾斜的key值。如果是STRING类型,需要加上引号。例如(a.c0=1 and a.c1="2")和(a.c0=3 and a.c1="4")的值都存在数据倾斜。select /*+ skewjoin(a(c0, c1)((1, "2"), (3, "4"))) */ * from T0 a join T1 b on a.c0 = b.c0 and a.c1 = b.c1 and a.c2 = b.c2;当两张表Join存在热点,导致出现长尾问题时:
可以通过取出热点key,将数据分为热点数据和非热点数据两部分处理,最后合并的方式,提高Join效率。
--格式SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value--例子SELECT Customer,SUM(OrderPrice) FROM OrdersGROUP BY CustomerHAVING SUM(OrderPrice)<2000MaxCompute SQL的WHERE关键字无法与聚合函数一起使用。
此时可以使用HAVING子句来实现。
表同步▐在线表同步ODPS
这里以MySQL同步ODPS为例子,其他表同步过程也类似。
首先介绍一下步骤:
Step1:进入dataworks,选择:数据开发(DataStudio) ——>数据集成 ——>Di数据同步 节点:
- 数据去向:数据源选择“ODPS”,odps的目标表可以先建好,也可以使用“一键生成目标表” 的功能(推荐,简单高效);
Step3: 填好调度配置
调度参数,注意参数值不同,展示的时间也不同
注意调度周期,天级,小时级或其他周期
设置好调度依赖,即依赖哪些上级文件的产出,若无可填根节点:
Step4:同步信息填写完成后,在调度配置里配置好调度信息,保存——>发布——>补数据 即可:
接下来,是一些经验总结:
|
2、“数据去向”的“一键生成目标表”功能,建表DDL语句需要人工检查下:
- 填写好lifecycle,分区配置
- 列名不要与odpsSQL关键字冲突
- 可以自定义修改表名
|
3、“数据去向”的“分区信息”,当建的是分区表时,会自动出现该处的分区信息配置;若建的是非分区表,则不必配置
|
4、调度配置中,可以按需选择天、小时或其他时间粒度调度任务
|
5、非该odps项目空间的表不能在该odps项目空间做同步任务
|
6、odps个别字段内容太长,超出mysql表的该字段存储限制,也会导致写入idb失败,报脏数据(修改idb表字段类型,可将对应字段类型修改为 longtext)
|
7、idb表字段设为非null,但odps对应字段存在 null值,会导致写入idb失败,报脏数据(修改idb表定义,将对应字段改默认为null)
|
8、odps字段和idb字段不必非得一对一保持应,可以手动选择相关字段 连线 ,odps和idb字段可各有未参与同步的字段(注意:idb字段的剩余字段必须是可以自动填充或默认为null类型的)
|
▐ODPS导入Hologres
目前对于需要周期性导入ODPS分区表数据到Hologres, Holo提供了两种导入方式:
方式一:一键可视化导入并且周期性导入,详情见datastudio一键导入
方式二:使用sql导入,详情见hologres sql
新建任务,在数据开发单击一键数据同步,并填写节点信息。2. 配置信息,填写同步信息。
3. 参数说明
参数
| 配置项
| 说明
| 备注
|
MaxCompute源表选择
| 目标连接
| Hologres的实例名
| 无
|
目标库
| Hologres的DB名
| 无
|
| | - 已有外部表表示已经提前在Hologres中映射MaxCompute数据的外部表
- 新建外部表表示无相应的外部表,需要同步时新建
|
外部表表名字
| 已有的外部表表名
| 外部表用于映射MaxCompute数据,需要与同步数据的MaxCompute表对应
|
目标表设置
| 目标Schema
| 当前DB下的schema名
| 默认为public,也可以选择新建schema并使用
|
目标表名
| 要导入数据的表名
| 需要同步表数据的内表名称,如已有表,执行后原表和数据将被删除重建
|
目标表描述
| 为目标表添加comment
| 无
|
同步设置
| 同步字段
| 选择需要同步的MaxCompute表字段
| 可以选择全部字段,也可以选择部分字段
|
分区配置
| 选择需要同步的分区字段
| 当前Hologres仅支持一级分区
|
索引配置
| 为目标表构建索引
| 索引的创建可以参见文档设置表属性
|
SQL Script
| SQL Script
| 自动解析出当前运行的SQL,方便参照
| 无
|
- 保持并运行,执行同步任务。任务执行完成之后,可以使用Hologres SQL查看数据。
- 周期性调度
4. 总结
方式一优点:可视化操作,简单快捷,方便小白用户使用,能满足一般场景的使用。
方式一缺点:不支持修改SQL逻辑,若是需要修改,需将SQL Copy再新建一个Hologres SQL节点,根据业务逻辑修改SQL即可。
先介绍一下操作步骤,基本和方式一相同。
新建业务流程:选择左侧菜单栏数据开发--新建--业务流程,即可创建一个属于自己的业务流程。2. 输入业务流程名称
3. 新建Hologres SQL
4. Hologres开发:打开新建的Hologres SQL选择对应Hologres实例,既可使用标准的Postgresql语言开发。
步骤1:准备MaxCompute表数据
--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';步骤2:Hologres新建外部表
移步HoloStudio,在SQL Console中新建一张外部表,用于映射MaxCompute源头表数据。外表的字段顺序和字段类型需要和MaxCompute一一对应。示例使用import foreign schema语法新建外部表SQL如下:
import foreign schema public_data limit to (dwd_product_movie_basic_info) from server odps_server into public options(if_table_exist 'update');步骤3:Hologres新建真实存储表
在Hologres中新建一张真实的存储表,用于接收并存储数据。因为本次示例是将MaxCompute分区表导入Hologres分区表,因此需要在Hologres中创建一张分区表。
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;步骤4:新建分区子表数据开发
在hologres sql中另开一个作业,用于分区表跑调度。
--创建临时分区子表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;步骤5:调度配置
1)基础属性配置
将基础属性--参数赋值为时间节点,如示例所示:
2)时间属性设置
主要设置时间的重跑属性,其余参数可以根据业务情况自行设置。
3)调度依赖设置
调度依赖为root节点即可(也可以根据业务逻辑选择已有的父节点)请先单击自动解析为是,然后单击使用工作空间根节点,会自动解析出root节点,然后将自动解析设置为否。
步骤6:发布调度
调度参数配置完成之后,单击保存--提交,提交成功后单击运维前往运维中心。
步骤7:运维中心发布
在跳转出来的运维中心,选择已提交成功成功的节点,右键单击节点,选择补数据--当前节点。并根据业务情况设置节点配置。
补完数据之后,在补数据实例可以看到正在运行的任务,以及任务运行状态。
步骤8:Hologres查看数据
任务执行成功之后,将会在Hologres中自动创建对应分区数据的分区子表,可以返回datastudio,新开一个hologres sql节点,执行语句查询数据是否写入成功。
--查看分区子表数据select * from holo_dwd_product_movie_basic_info_20170112;--查看分区父表总数据select count (*) from holo_dwd_product_movie_basic_info;总结:
方式二优点:可按业务需求进行定制,对sql进行修改,满足复杂特定场景的需求,包括历史数据格式转换、数据清理等;通过SQL导入性能更优。
方式二缺点:有一定学习成本,初学者不太适合,可先通过方式一了解其数据同步的流程和原理,再切换到方式二。
-- 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};注意点:
- 使用临时表的原因是为了保证原子性,只有在导入完成后才绑定至分区表,为了避免导入任务失败时还需要重新删除表等操作。
- 对于更新子表分区数据场景,需要删除子表和重新绑定临时表放入一个事务过程中,保证该过程的事务性。
- MaxCompute的表数据更新之后,在Hologres存在缓存延迟(一般为10分钟内),建议在导入数据前使用IMPORT FOREIGN SCHEMA语法更新外部表以获取最新数据。
- 导入MaxCompute数据至Hologres时,建议使用SQL导入,不建议使用数据集成导入,因为使用SQL导入性能表现更优。
注意事项
▐调度配置
这里强调一下调度参数:
调度参数通常会被用于指代某些动态时间的场景,此场景下,可基于业务日期和定时时间进行调度参数的取值设置。配置调度参数前,您可先了解这两个时间概念,便于后续设置调度参数取值。
取值方式
| 参数格式
| 参数示例
| 相关参考
|
基于业务日期获取时间数据
| 通常,使用大括号${...},结合yyyy、yy、mm及dd自定义组合生成时间参数,获取业务日期前后多少年、月、天。
| 可通过${yyyymmdd}、${yyyy-mm-dd}等${...}自定义时间格式获取,例如:
- ${yyyymmdd±N}
- ${yyyymmdd±7*N}
- ${yy±N}
- ${mm}
- ${yyyy-mm-dd±N}
| 更多赋值示例,请参见自定义参数${...}
|
基于定时时间获取时间数据
| 通常,使用中括号$[...],结合yyyy、yy、mm、dd、hh24、mi及ss自定义组合生成时间参数,获取定时时间前后多少年、月、天、小时、分钟、秒。
| 可通过$[yyyymmddhh24miss]等$[...]自定义时间格式获取。例如,取前一天的前一小时,参数表达式为$[yyyymmdd-1-1/24]。
| - 更多赋值示例,请参见自定义参数$[...]
- 取多少小时、分钟,可能存在跨天问题,跨天时间的参数处理
|
内置参数:
内置参数
| 定义
|
$bizdate
| 业务日期,格式为yyyymmdd,与自定义参数${yyyymmdd}取值一致。
该参数的应用较为广泛,日常调度中默认任务预期运行时间的前一天为业务日期。
|
$cyctime
| 任务的定时时间,格式为yyyymmddhh24miss,与自定义参数$[yyyymmddhh24miss]取值一致。
|
$gmtdate
| 当前日期,格式为yyyymmdd。
该参数默认取当天日期,执行补数据操作时输入的日期为业务日期+1。
|
$bizmonth
| 业务月份,格式为yyyymm。
- 如果业务日期的月份与当前月份一致,则$bizmonth=业务日期月份-1。
- 如果业务日期的月份与当前月份不一致,则$bizmonth=业务日期月份。
|
$jobid
| 任务所属的业务流程ID。
|
$nodeid
| 节点ID。
|
$taskid
| 节点产生的实例ID。
|
调度配置的各板块:
配置基础属性
| 名称,节点ID,节点类型,责任人,描述
|
配置调度参数
| 调度参数支持的格式
配置并使用调度参数
|
配置时间属性
| 时间属性配置说明
实例生成方式:发布后即时生成实例
调度周期:分钟/小时/天/月调度
|
配置资源属性
| 默认配置为公共调度资源组。
|
配置调度依赖
| 调度依赖配置
配置同周期调度依赖
配置依赖上一周期(跨周期依赖)
复杂依赖场景调度配置原则
|
配置节点上下文
| 在节点上下文配置本节点输入参数和本节点输出参数
输出参数的取值分为常量和变量两种类型
配置输入参数,在调度依赖中添加依赖的上游节点
|