spark sql: 操作hbase表——rdd实现sql查询

• hbase数据：（需求–按年龄分组，求出各年龄段的总人数,计算结果保存mysql）

hbase(main):006:0> scan 'p1'
ROW        COLUMN+CELL                                                                                               
 r1        column=f:age, timestamp=1556181658918, value=23                                                           
 r1        column=f:name, timestamp=1556181620870, value=lisi                                                        
 r2        column=f:age, timestamp=1556181665529, value=25                                                                              
 r2        column=f:name, timestamp=1556181630233, value=lisi2                                                       
 r3        column=f:age, timestamp=1556181671939, value=34                                                           
 r3        column=f:name, timestamp=1556181639913, value=lisi3                                                       
 r4        column=f:age, timestamp=1557247762486, value=25                                                           
 r4        column=f:name, timestamp=1557247747800, value=a1    

1，spark-sql查询hbase

I，准备sql语句

select count(*),age from person  group by age

II，使用spark-sql执行sql语句

a，maven配置

<repositories>
   <repository>
        <id>cloudera</id>
        <url>https://repository.cloudera.com/artifactory/cloudera-repos/</url>
    </repository>
</repositories>

<dependencies>
    <dependency>
        <groupId>org.apache.hbase</groupId>
        <artifactId>hbase-client</artifactId>
        <version>1.2.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hbase</groupId>
        <artifactId>hbase-server</artifactId>
        <version>1.2.0</version>
    </dependency>

    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-core_2.10</artifactId>
        <version>1.5.0-cdh5.6.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-sql_2.10</artifactId>
        <version>1.5.0-cdh5.6.1</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.17</version>
</dependency>

b，spark-sql执行sql语句

import java.util.Properties
import org.apache.hadoop.hbase.HBaseConfiguration
import org.apache.hadoop.hbase.client.Result
import org.apache.hadoop.hbase.io.ImmutableBytesWritable
import org.apache.hadoop.hbase.mapreduce.TableInputFormat
import org.apache.hadoop.hbase.util.Bytes
import org.apache.spark.sql.SQLContext
import org.apache.spark.{SparkConf, SparkContext}

object SparkSqlHbase2_write_mysql {
  def main(args: Array[String]): Unit = {
    //创建spark配置
    val conf = new SparkConf()
    conf.setMaster("local[*]").setAppName("test")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)

    //读取hbase数据
    val hbaseConf = HBaseConfiguration.create()
    hbaseConf.set(TableInputFormat.INPUT_TABLE, "p1")
    val hbaseRDD = sc.newAPIHadoopRDD(hbaseConf,
      classOf[TableInputFormat],
      classOf[ImmutableBytesWritable],
      classOf[Result]
    )

    //rdd-->df
    val tupRDD = hbaseRDD.map({
      case (_, result) => {
        val name = Bytes.toString(result.getValue(Bytes.toBytes("f"), Bytes.toBytes("name")))
        val age = Bytes.toString(result.getValue(Bytes.toBytes("f"), Bytes.toBytes("age")))

        // 转换成RDD[Row]
        (name,age)
      }
    })

    //构造schema, rdd->转成DataFrame, 注册表
    import sqlContext.implicits._
    val df = tupRDD.toDF("name","age")
    df.registerTempTable("person")

    val df2 = sqlContext.sql("select name,age from person")
    df2.show()

    //数据转存到mysql: test.person
    val prop = new Properties()
    prop.put("user", "root")
    prop.put("password", "123456")
    df2.write.mode("append").jdbc("jdbc:mysql://localhost:3306/test","person",prop)
  }

}

2, rdd实现hbase sql查询

I模拟数据源1：mysql

val dfreader=sqlContext.read.format("jdbc")
.option("url", "jdbc:mysql://localhost:3306/test")
.option("driver","com.mysql.jdbc.Driver")
.option("dbtable", "person")
.option("user", "root")
.option("password", "123456")

val df = dfreader.load()
df.show() //打印数据

II, 模拟数据源：textfile

wang@controller:~$ cat a.txt 
a,23,java
a1,23,java
a2,23,java
a3,23,java
b,23,py
c,45,go
d,34,go
###rdd编程
 sc.setLogLevel("ERROR")
 val rdd = sc.textFile("file:///home/wang/a.txt")
 val rdd2=rdd.map(str=>{
   val infos = str.split(",")
   (infos(2),(infos.mkString(","),1))
 })

 val rdd3 = rdd2.reduceByKey((tup1,tup2)=>{
   val key1=tup1._1
   val key2=tup2._1
   val extrMesg=key1+";"+key2
   val count= tup1._2+tup2._2

   (extrMesg,count)
 })
 
 rdd3.foreach(tup=>{
   val key = tup._1
   val tup_arr = tup._2
   val infostr = tup_arr._1
   val count = tup_arr._2


   println(key+"->"+count+"|"+infostr)
// py->1|b,23,py
// java->4|a,23,java;a1,23,java;a2,23,java;a3,23,java
 })

III 真实数据源:hbase

import org.apache.hadoop.hbase.HBaseConfiguration
import org.apache.hadoop.hbase.client.Result
import org.apache.hadoop.hbase.io.ImmutableBytesWritable
import org.apache.hadoop.hbase.mapreduce.TableInputFormat
import org.apache.hadoop.hbase.util.Bytes
import org.apache.spark.{SparkConf, SparkContext}

object Count3 {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf().setMaster("local[*]").setAppName("test")
    val sc = new SparkContext(conf)
    sc.setLogLevel("ERROR")

    //读取hbase数据
    val hbaseConf = HBaseConfiguration.create()
    hbaseConf.set(TableInputFormat.INPUT_TABLE, "p1")
    val hbaseRDD = sc.newAPIHadoopRDD(hbaseConf,
      classOf[TableInputFormat],
      classOf[ImmutableBytesWritable],
      classOf[Result]
    )
    val rdd2 = hbaseRDD.map({
      case (_, result) => {
        val name = Bytes.toString(result.getValue(Bytes.toBytes("f"), Bytes.toBytes("name")))
        val age = Bytes.toString(result.getValue(Bytes.toBytes("f"), Bytes.toBytes("age")))

        // 转换成RDD[Row]
        (age, (name, 1))
      }
    })

    val rdd3 = rdd2.reduceByKey((tup1, tup2) => {
      val name1 = tup1._1
      val count1 = tup1._2

      val name2 = tup2._1
      val count2 = tup2._2

      //收集详情+ group_count
      (name1 + "," + name2, count1 + count2)
    })

    rdd3.foreach(tup => {
      val key = tup._1

      val tup_arr = tup._2
      val infostr = tup_arr._1
      val count = tup_arr._2


      println(key + "->" + count + "|" + infostr)
      println("----------")
      //      34->1|lisi3
      //      ----------
      //      25->2|lisi2,a1
      //      ----------
      //      23->1|lisi
      //      ----------
    })
  }

}