YDB与Hive SQL在写法上的对比

普通查询SQL写法对比

对比点	HIVE	YDB
统计计数	select count(*) from ydb_example_shu where ydbpartion= '2015'	/*ydb.pushdown('->')*/ select count(*) from ydb_example_shu where ydbpartion = '2015' /*('<-')pushdown.ydb*/ ;
数据预览	select * from ydb_example_shu where ydbpartion = '3000w' limit 10	/*ydb.pushdown('->')*/ select * from ydb_example_shu where ydbpartion = '3000w' limit 10  /*('<-')pushdown.ydb*/;
检索过滤筛选	select content,usernick from ydb_example_shu where ydbpartion = '3000w' and content='王老吉' limit 10	/*ydb.pushdown('->')*/ select content,usernick from ydb_example_shu where ydbpartion = '3000w' and content='王老吉' limit 10 /*('<-')pushdown.ydb*/;
多条件组合	select ydb_sex,ydb_grade,ydb_age,ydb_blood,amtlong from ydb_example_shu where ydbpartion = '3000w' and ydb_sex='女' and ydb_grade='本科' and (ydb_age='20到30岁' or ydb_blood='O') and amtlong>=3000 and amtlong<=4000  limit 10	/*ydb.pushdown('->')*/ select ydb_sex,ydb_grade,ydb_age,ydb_blood,amtlong from ydb_example_shu where ydbpartion = '3000w' and ydb_sex='女' and ydb_grade='本科' and (ydb_age='20到30岁' or ydb_blood='O') and  (amtlong like '([3000 TO 4000] )') limit 10 /*('<-')pushdown.ydb*/;
sum求和	select sum(amtdouble) from ydb_example_shu where ydbpartion = '3000w'	/*ydb.pushdown('->')*/ select sum(amtdouble) from ydb_example_shu where ydbpartion = '3000w' /*('<-')pushdown.ydb*/;
求平均数	select avg(amtdouble) as avgamt from ydb_example_shu where ydbpartion = '3000w'	/*ydb.pushdown('->')*/ select avg(amtdouble) as avgamt from ydb_example_shu where ydbpartion = '3000w' /*('<-')pushdown.ydb*/;
多条件统计	select count(*),count(amtdouble),avg(amtdouble),sum(amtdouble),min(amtdouble),max(amtdouble),min(ydb_province),max(ydb_province) from ydb_example_shu where ydbpartion = '3000w'	/*ydb.pushdown('->')*/ select count(*),count(amtdouble),avg(amtdouble),sum(amtdouble),min(amtdouble),max(amtdouble),min(ydb_province),max(ydb_province) from ydb_example_shu where ydbpartion = '3000w' /*('<-')pushdown.ydb*/;
单列group by	select ydb_sex,count(*),count(amtdouble),sum(amtdouble) from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex limit 10	/*ydb.pushdown('->')*/ select ydb_sex,count(*),count(amtdouble),sum(amtdouble) from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex limit 10 /*('<-')pushdown.ydb*/;
多列group by	select ydb_sex,ydb_province,count(*) as cnt,count(amtdouble),sum(amtdouble) from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex,ydb_province order by cnt desc limit 10	/*ydb.pushdown('->')*/ select ydb_sex,ydb_province,count(*) as cnt,count(amtdouble),sum(amtdouble) from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex,ydb_province order by cnt desc limit 10 /*('<-')pushdown.ydb*/;
order by 列	select ydb_sex, phonenum,amtlong,amtdouble from ydb_example_shu where ydbpartion='3000w'  order by amtdouble desc ,amtlong limit 10	/*ydb.pushdown('->')*/ select ydb_sex, phonenum,amtlong,amtdouble from ydb_example_shu where ydbpartion='3000w'  order by amtdouble desc ,amtlong limit 10 /*('<-')pushdown.ydb*/;
模糊检索	select content,usernick,ydb_province from ydb_example_shu where ydbpartion = '3000w' and content like '%辽宁%' limit 10	YDB也支持这样写，但是有本身更高效的写法。 /*ydb.pushdown('->')*/ select content,usernick,ydb_province from ydb_example_shu where ydbpartion = '3000w' and ydb_province like '%辽宁%' limit 100 /*('<-')pushdown.ydb*/;

 

 

 

复杂查询SQL写法对比

对比点	HIVE	YDB
导出	insert overwrite table ydb_import_importtest select    phonenum,usernick,ydb_sex,ydb_province,ydb_grade,ydb_age,ydb_blood,ydb_zhiye,ydb_earn,ydb_prefer,ydb_consume,ydb_day,amtdouble,amtlong,content from ydb_example_shu where ydbpartion = '3000w'	insert overwrite table ydb_import_importtest /*ydb.pushdown('->')*/ select      phonenum,usernick,ydb_sex,ydb_province,ydb_grade,ydb_age,ydb_blood,ydb_zhiye,ydb_earn,ydb_prefer,ydb_consume,ydb_day,amtdouble,amtlong,content  from ydb_example_shu where ydbpartion = '3000w' /*('<-')pushdown.ydb*/;
多表关联	select k21.vehiclePlate from(     select  k1.vehiclePlate as vehiclePlate from (            select vehiclePlate,tollCode from vehiclepass where ydbpartion = '3000w' and tollCode='1'     ) k1     LEFT SEMI JOIN    (        select vehiclePlate,tollCode from vehiclepass where ydbpartion = '3000w' and tollCode='2'      ) k2     on (k1.vehiclePlate=k2.vehiclePlate)  ) k21 LEFT SEMI JOIN (    select vehiclePlate,tollCode from vehiclepass  where ydbpartion = '3000w' and tollCode='3'  ) k22 on k21.vehiclePlate=k22.vehiclePlate order by k21.vehiclePlate;	select k21.vehiclePlate from(     select  k1.vehiclePlate as vehiclePlate from (             /*ydb.pushdown('->')*/ select vehiclePlate,tollCode from vehiclepass where ydbpartion = '3000w' and tollCode='1' /*('<-')pushdown.ydb*/     ) k1     LEFT SEMI JOIN    (        /*ydb.pushdown('->')*/ select vehiclePlate,tollCode from vehiclepass where ydbpartion = '3000w' and tollCode='2' /*('<-')pushdown.ydb*/      ) k2     on (k1.vehiclePlate=k2.vehiclePlate)  ) k21 LEFT SEMI JOIN (    /*ydb.pushdown('->')*/ select vehiclePlate,tollCode from vehiclepass  where ydbpartion = '3000w' and tollCode='3' /*('<-')pushdown.ydb*/  ) k22 on k21.vehiclePlate=k22.vehiclePlate order by k21.vehiclePlate;
union示例	select sum(cnt) as cnt from (   select count(*) as cnt from ydb_example_shu where ydbpartion = '3000w'  union all select count(*) as cnt from ydb_example_shu where ydbpartion = '300winsert' union all select count(*) as cnt from ydb_example_shu where ydbpartion = '300winsert2' union all select count(*) as cnt from ydb_example_shu where ydbpartion = '3000w' and content='王老吉'  union all select count(*) as cnt from ydb_example_shu where ydbpartion = '20151011' and content='工商银行' union all select count(*) as cnt from ydb_example_shu where ydbpartion = '20151011'  ) tmp limit 10;	select sum(cnt) as cnt from ( /*ydb.pushdown('->')*/ select count(*) as cnt from ydb_example_shu where ydbpartion = '3000w' /*('<-')pushdown.ydb*/ union all /*ydb.pushdown('->')*/ select count(*) as cnt from ydb_example_shu where ydbpartion = '300winsert' /*('<-')pushdown.ydb*/ union all /*ydb.pushdown('->')*/ select count(*) as cnt from ydb_example_shu where ydbpartion = '300winsert2' /*('<-')pushdown.ydb*/ union all /*ydb.pushdown('->')*/ select count(*) as cnt from ydb_example_shu where ydbpartion = '3000w' and content='王老吉' /*('<-')pushdown.ydb*/ union all /*ydb.pushdown('->')*/ select count(*) as cnt from ydb_example_shu where ydbpartion = '20151011' and content='工商银行' /*('<-')pushdown.ydb*/ union all /*ydb.pushdown('->')*/ select count(*) as cnt from ydb_example_shu where ydbpartion = '20151011'  /*('<-')pushdown.ydb*/ ) tmp limit 10;
union示例2	select * from ( select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='旺旺' order by amtlong desc limit 1 union all select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='王老吉' order by amtlong desc limit 1 union all select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='汇源' order by amtlong desc limit 1  union all select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='哇哈哈' order by amtlong desc limit 1   ) tmp  limit 1000;	select * from ( /*ydb.pushdown('->')*/ select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='旺旺' order by amtlong desc limit 1   /*('<-')pushdown.ydb*/ union all /*ydb.pushdown('->')*/ select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='王老吉' order by amtlong desc limit 1  /*('<-')pushdown.ydb*/ union all /*ydb.pushdown('->')*/ select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='汇源' order by amtlong desc limit 1   /*('<-')pushdown.ydb*/ union all /*ydb.pushdown('->')*/ select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='哇哈哈' order by amtlong desc limit 1  /*('<-')pushdown.ydb*/  ) tmp  limit 1000;
distinct示例	select size(collect_set(ydb_sex)) as dist_sex,     size(collect_set(ydb_province)) as dist_province,     count(*) as cnt,     count(amtlong) as cnt_long,     count(distinct amtlong) as dist_long  from ydb_example_shu where ydbpartion = '3000w' and content='王老吉'	select     size(collect_set(tmp.ydb_sex)) as dist_sex,     size(collect_set(tmp.ydb_province)) as dist_province,     count(*) as cnt,     count(tmp.amtlong) as cnt_long,     count(distinct tmp.amtlong) as dist_long from (     /*ydb.pushdown('->')*/         select ydb_sex,ydb_province,amtlong from ydb_example_shu where ydbpartion = '3000w' and content='王老吉'     /*('<-')pushdown.ydb*/ ) tmp limit 10;
group by +distinct	select     ydb_sex as ydb_sex,     size(collect_set(ydb_province)) as dist_province,     count(*) as cnt,     count(amtlong) as cnt_long,     count(distinct amtlong) as dist_long from ydb_example_shu where ydbpartion = '3000w' and content='王老吉' group by ydb_sex limit 10;	select     tmp.ydb_sex as ydb_sex,     size(collect_set(tmp.ydb_province)) as dist_province,     count(*) as cnt,     count(tmp.amtlong) as cnt_long,     count(distinct tmp.amtlong) as dist_long from (     /*ydb.pushdown('->')*/         select ydb_sex,ydb_province,amtlong from ydb_example_shu where ydbpartion = '3000w' and content='王老吉'     /*('<-')pushdown.ydb*/ ) tmp group by tmp.ydb_sex limit 10;
行转列示例1	select ydb_sex,concat_ws('#', sort_array(collect_set(concat_ws(',',ydb_province,cnt,cntamt,sumamt)))) from (         select ydb_sex,ydb_province,count(*) as cnt,count(amtdouble) as cntamt,sum(amtdouble) as sumamt from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex,ydb_province  )tmp  group by ydb_sex limit 10;	select ydb_sex,concat_ws('#', sort_array(collect_set(concat_ws(',',ydb_province,cnt,cntamt,sumamt)))) from (     /*ydb.pushdown('->')*/         select ydb_sex,ydb_province,count(*) as cnt,count(amtdouble) as cntamt,sum(amtdouble) as sumamt from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex,ydb_province      /*('<-')pushdown.ydb*/ )tmp  group by ydb_sex limit 10;
行转列示例2	select ydb_province,sum(cnt) as scnt,concat_ws('#', sort_array(collect_set(concat_ws(',',ydb_sex,cnt,cntamt,sumamt)))) from (         select ydb_sex,ydb_province,count(*) as cnt,count(amtdouble) as cntamt,sum(amtdouble) as sumamt from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex,ydb_province  )tmp  group by ydb_province order by scnt desc limit 10;	select ydb_province,sum(cnt) as scnt,concat_ws('#', sort_array(collect_set(concat_ws(',',ydb_sex,cnt,cntamt,sumamt)))) from (     /*ydb.pushdown('->')*/         select ydb_sex,ydb_province,count(*) as cnt,count(amtdouble) as cntamt,sum(amtdouble) as sumamt from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex,ydb_province      /*('<-')pushdown.ydb*/ )tmp  group by ydb_province order by scnt desc limit 10;