Example of implementing a SQL query with Spark programming

1. SQL in Oracle

Select Count(1) fromA_v_pwyzl_custacct_psmis Twhere  not exists(Select1 fromTb_show_multi_question QWHEREQ.dqmp_rule_code =' compare to System only '              andq.dqmp_role_id =' 105754659 '              andq.dqmp_target_id = T.dqmp_mrid) and  not EXISTS(Select/*+Index(s) */1 fromA_v_pwyzl_custacct_gis swhereS.DQMP_CPK = T.DQMP_CPK) andT.is_repeat =' 0 ';

2. Hive/shark version

Select Count(1) from(Selectt.*,q.dqmp_question_id (column index q.0) asF1,s.gis_mrid (column index s.0) asF2 from(Select* fromA_v_pwyzl_custacct_psmis TwhereT.is_repeat (column index t.19) =' 0 ') T Left outer Join(Select* fromTb_show_multi_question QwhereQ.dqmp_rule_code (column index q.26) =' compare to System only '  andq.dqmp_role_id (column index q.31) =' 105754659 ') Q onq.dqmp_target_id (column index q.13) = T.dqmp_mrid (column index t.32) Left outer JoinA_v_pwyzl_custacct_gis s onS.DQMP_CPK (column index s.31) = T.DQMP_CPK (Carnaby column t.31)) TVwhereTv.f1 is NULL  andTv.f2 is NULL;

3, Spark programming implementation

Package Cn.com.gzkit.spark

Import Org.apache.spark.SparkContext

Import Org.apache.spark.sparkcontext._

Import org.apache.spark.rdd.PairRDDFunctions;

Object test2 {

  def main (args:array[string]) {

      var master="spark://kit-b1:7077";

      var sc=new"Hdfstest", System.getenv ("Spark_home"), Sparkcontext.jarofclass (This . GetClass));

      var file1 = sc.textfile ("Hdfs://kit-b1/demodata/utf8_a_v_pwyzl_custacct_psmis.txt"). Map (_.split (' | '));

      var file2 = sc.textfile ("Hdfs://kit-b1/demodata/utf8_a_v_pwyzl_custacct_gis.txt"). Map (_.split (' | ')). Map (m=> (m (+), m (0)));

      var file3 = sc.textfile ("Hdfs://kit-b1/demodata/utf8_tb_show_multi_question.txt"). Map (_.split (' | ')). Map (m=> (m), M (+), m (+), m (0));

      File1.cache ();

      File2.cache ();

      File3.cache ();

      File1.count ();

      File2.count ();

      File3.count ();

      System. out. println ("-----------begin-----------------");

      var T=file1.filter (_ (+) = ="0");

      var t1=t.map (t=> (t (+), T));

      var q=file3.filter (_._2=="105754659"). Filter (_._3==" only compared to System ");

      var q1=q.map (q=> (q._1,q));

      var tq=t1.leftouterjoin (Q1);

      System. out. println ("-----------end-----------------");

      System. out. println ("-----------begin2-----------------");

      var t2=tq.map (m=> (m._2._1), m._2);

      var s=file2;

      var ts=t2.leftouterjoin (s);

      var rs=ts.filter (_._2._2==none). filter (_._2._1._2==none);

      System. out. println ("Sava file");

      Rs.saveastextfile ("hdfs://kit-b1/demodata/test/02");

      System. out. println ("Sava file End");

      var v2=rs.count ();

      System. out. println ("v2="+v2);

      System. out. println ("-----------end2-----------------");

      Sc.stop ();

  }

}