Spark2.3.0 通过Phoenix4.7 查询 Hbase 数据.

0. 环境

 

软件	版本
JDK	1.8
SCALA	2.11.8
Spark	2.3.0
Phoenix	4.7.0
Hbase	1.1.2

1. 目标数据

 

 

CREATE TABLE TABLE1 (ID BIGINT NOT NULL PRIMARY KEY, COL1 VARCHAR);

UPSERT INTO TABLE1 (ID, COL1) VALUES (1, 'test_row_1');

UPSERT INTO TABLE1 (ID, COL1) VALUES (2, 'test_row_2');

 

2. 代码:

package com.zl

import java.util.Properties

import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.SparkContext
import org.apache.spark.sql.{Row, SQLContext, SparkSession}

/**
 *
 *
 */
object App  {


  private val DB_PHOENIX_DRIVER = "org.apache.phoenix.jdbc.PhoenixDriver"
  private val DB_PHOENIX_URL = "jdbc:phoenix:hadoop002,hadoop004,hadoop003"
  private val DB_PHOENIX_USER = ""
  private val DB_PHOENIX_PASS = ""
  private val DB_PHOENIX_FETCHSIZE = "10000"
  /**
    * 加载数据查询SQL
    */
  private val SQL_QUERY = " ( SELECT ID ,  COL1 FROM TABLE1 limit 10 )  events  "

  def main(args: Array[String]): Unit = {

    val conf = new SparkConf()

    conf.setAppName("phoenix-test")

      .setMaster("local[*]")


    val sc = new SparkContext(conf)
    val sparkSession = SparkSession.builder().config(conf).getOrCreate()


    // JDBC连接属性
    val connProp = new Properties
    connProp.put("driver", DB_PHOENIX_DRIVER)
    connProp.put("user", DB_PHOENIX_USER)
    connProp.put("password", DB_PHOENIX_PASS)
    connProp.put("fetchsize", DB_PHOENIX_FETCHSIZE)
    val rows = sparkSession.read.jdbc(DB_PHOENIX_URL, SQL_QUERY, connProp).javaRDD



    println(rows.collect())

  }

}

 

3.项目结构与运行效果

 

 

 

4. 新建项目 pom.xml 文件:

 

<?xml version="1.0" encoding="UTF-8"?>
<!--
  ~ Licensed to the Apache Software Foundation (ASF) under one or more
  ~ contributor license agreements.  See the NOTICE file distributed with
  ~ this work for additional information regarding copyright ownership.
  ~ The ASF licenses this file to You under the Apache License, Version 2.0
  ~ (the "License"); you may not use this file except in compliance with
  ~ the License.  You may obtain a copy of the License at
  ~
  ~    http://www.apache.org/licenses/LICENSE-2.0
  ~
  ~ Unless required by applicable law or agreed to in writing, software
  ~ distributed under the License is distributed on an "AS IS" BASIS,
  ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  ~ See the License for the specific language governing permissions and
  ~ limitations under the License.
  -->

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.apache.spark</groupId>
    <artifactId>spark-parent_2.11</artifactId>
    <version>2.3.2</version>
    <relativePath>../pom.xml</relativePath>
  </parent>

  <artifactId>spark-core_2.11</artifactId>
  <properties>
    <sbt.project.name>core</sbt.project.name>
  </properties>
  <packaging>jar</packaging>
  <name>Spark Project Core</name>
  <url>http://spark.apache.org/</url>
  <dependencies>
    <dependency>
      <groupId>org.apache.avro</groupId>
      <artifactId>avro</artifactId>
    </dependency>
    <dependency>
      <groupId>org.apache.avro</groupId>
      <artifactId>avro-mapred</artifactId>
      <classifier>${avro.mapred.classifier}</classifier>
    </dependency>
    <dependency>
      <groupId>com.google.guava</groupId>
      <artifactId>guava</artifactId>
    </dependency>
    <dependency>
      <groupId>com.twitter</groupId>
      <artifactId>chill_${scala.binary.version}</artifactId>
    </dependency>
    <dependency>
      <groupId>com.twitter</groupId>
      <artifactId>chill-java</artifactId>
    </dependency>
    <dependency>
      <groupId>org.apache.xbean</groupId>
      <artifactId>xbean-asm5-shaded</artifactId>
    </dependency>
    <dependency>
      <groupId>org.apache.hadoop</groupId>
      <artifactId>hadoop-client</artifactId>
    </dependency>
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-launcher_${scala.binary.version}</artifactId>
      <version>${project.version}</version>
    </dependency>
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-kvstore_${scala.binary.version}</artifactId>
      <version>${project.version}</version>
    </dependency>
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-network-common_${scala.binary.version}</artifactId>
      <version>${project.version}</version>
    </dependency>
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-network-shuffle_${scala.binary.version}</artifactId>
      <version>${project.version}</version>
    </dependency>
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-unsafe_${scala.binary.version}</artifactId>
      <version>${project.version}</version>
    </dependency>
    <dependency>
      <groupId>net.java.dev.jets3t</groupId>
      <artifactId>jets3t</artifactId>
    </dependency>
    <dependency>
      <groupId>org.apache.curator</groupId>
      <artifactId>curator-recipes</artifactId>
    </dependency>

    <!-- Jetty dependencies promoted to compile here so they are shaded
         and inlined into spark-core jar -->
    <dependency>
      <groupId>org.eclipse.jetty</groupId>
      <artifactId>jetty-plus</artifactId>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.eclipse.jetty</groupId>
      <artifactId>jetty-security</artifactId>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.eclipse.jetty</groupId>
      <artifactId>jetty-util</artifactId>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.eclipse.jetty</groupId>
      <artifactId>jetty-server</artifactId>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.eclipse.jetty</groupId>
      <artifactId>jetty-http</artifactId>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.eclipse.jetty</groupId>
      <artifactId>jetty-continuation</artifactId>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.eclipse.jetty</groupId>
      <artifactId>jetty-servlet</artifactId>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.eclipse.jetty</groupId>
      <artifactId>jetty-proxy</artifactId>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.eclipse.jetty</groupId>
      <artifactId>jetty-client</artifactId>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.eclipse.jetty</groupId>
      <artifactId>jetty-servlets</artifactId>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>${javaxservlet.version}</version>
    </dependency>

    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-lang3</artifactId>
    </dependency>
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-math3</artifactId>
    </dependency>
    <dependency>
      <groupId>com.google.code.findbugs</groupId>
      <artifactId>jsr305</artifactId>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>jul-to-slf4j</artifactId>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>jcl-over-slf4j</artifactId>
    </dependency>
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
    </dependency>
    <dependency>
      <groupId>com.ning</groupId>
      <artifactId>compress-lzf</artifactId>
    </dependency>
    <dependency>
      <groupId>org.xerial.snappy</groupId>
      <artifactId>snappy-java</artifactId>
    </dependency>
    <dependency>
      <groupId>org.lz4</groupId>
      <artifactId>lz4-java</artifactId>
    </dependency>
    <dependency>
      <groupId>com.github.luben</groupId>
      <artifactId>zstd-jni</artifactId>
    </dependency>
    <dependency>
      <groupId>org.roaringbitmap</groupId>
      <artifactId>RoaringBitmap</artifactId>
    </dependency>
    <dependency>
      <groupId>commons-net</groupId>
      <artifactId>commons-net</artifactId>
    </dependency>
    <dependency>
      <groupId>org.scala-lang</groupId>
      <artifactId>scala-library</artifactId>
    </dependency>
    <dependency>
      <groupId>org.json4s</groupId>
      <artifactId>json4s-jackson_${scala.binary.version}</artifactId>
    </dependency>
    <dependency>
      <groupId>org.glassfish.jersey.core</groupId>
      <artifactId>jersey-client</artifactId>
    </dependency>
    <dependency>
      <groupId>org.glassfish.jersey.core</groupId>
      <artifactId>jersey-common</artifactId>
    </dependency>
    <dependency>
      <groupId>org.glassfish.jersey.core</groupId>
      <artifactId>jersey-server</artifactId>
    </dependency>
    <dependency>
      <groupId>org.glassfish.jersey.containers</groupId>
      <artifactId>jersey-container-servlet</artifactId>
    </dependency>
    <dependency>
      <groupId>org.glassfish.jersey.containers</groupId>
      <artifactId>jersey-container-servlet-core</artifactId>
    </dependency>
    <dependency>
      <groupId>io.netty</groupId>
      <artifactId>netty-all</artifactId>
    </dependency>
    <dependency>
      <groupId>io.netty</groupId>
      <artifactId>netty</artifactId>
    </dependency>
    <dependency>
      <groupId>com.clearspring.analytics</groupId>
      <artifactId>stream</artifactId>
    </dependency>
    <dependency>
      <groupId>io.dropwizard.metrics</groupId>
      <artifactId>metrics-core</artifactId>
    </dependency>
    <dependency>
      <groupId>io.dropwizard.metrics</groupId>
      <artifactId>metrics-jvm</artifactId>
    </dependency>
    <dependency>
      <groupId>io.dropwizard.metrics</groupId>
      <artifactId>metrics-json</artifactId>
    </dependency>
    <dependency>
      <groupId>io.dropwizard.metrics</groupId>
      <artifactId>metrics-graphite</artifactId>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.module</groupId>
      <artifactId>jackson-module-scala_${scala.binary.version}</artifactId>
    </dependency>
    <dependency>
      <groupId>org.apache.derby</groupId>
      <artifactId>derby</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.apache.ivy</groupId>
      <artifactId>ivy</artifactId>
    </dependency>
    <dependency>
      <groupId>oro</groupId>
      <!-- oro is needed by ivy, but only listed as an optional dependency, so we include it. -->
      <artifactId>oro</artifactId>
      <version>${oro.version}</version>
    </dependency>
    <dependency>
      <groupId>org.seleniumhq.selenium</groupId>
      <artifactId>selenium-java</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.seleniumhq.selenium</groupId>
      <artifactId>selenium-htmlunit-driver</artifactId>
      <scope>test</scope>
    </dependency>
    <!-- Added for selenium: -->
    <dependency>
      <groupId>xml-apis</groupId>
      <artifactId>xml-apis</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.hamcrest</groupId>
      <artifactId>hamcrest-core</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.hamcrest</groupId>
      <artifactId>hamcrest-library</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.mockito</groupId>
      <artifactId>mockito-core</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.scalacheck</groupId>
      <artifactId>scalacheck_${scala.binary.version}</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.apache.curator</groupId>
      <artifactId>curator-test</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>net.razorvine</groupId>
      <artifactId>pyrolite</artifactId>
      <version>4.13</version>
      <exclusions>
        <exclusion>
          <groupId>net.razorvine</groupId>
          <artifactId>serpent</artifactId>
        </exclusion>
      </exclusions>
    </dependency>
    <dependency>
      <groupId>net.sf.py4j</groupId>
      <artifactId>py4j</artifactId>
      <version>0.10.7</version>
    </dependency>
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-tags_${scala.binary.version}</artifactId>
    </dependency>

    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-launcher_${scala.binary.version}</artifactId>
      <version>${project.version}</version>
      <classifier>tests</classifier>
      <scope>test</scope>
    </dependency>


    <!--
      This spark-tags test-dep is needed even though it isn't used in this module, otherwise testing-cmds that exclude
      them will yield errors.
    -->
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-tags_${scala.binary.version}</artifactId>
      <type>test-jar</type>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-crypto</artifactId>
    </dependency>

    <!--
     The following dependencies are depended upon in HiveCredentialProvider, but are only executed if Hive is enabled in
     the user's Hadoop configuration.  So in order to prevent spark-core from depending on Hive, these deps have been
     placed in the "provided" scope, rather than the "compile" scope, and NoClassDefFoundError exceptions are handled
     when the user has not explicitly compiled with the Hive module.
    -->
    <dependency>
      <groupId>${hive.group}</groupId>
      <artifactId>hive-exec</artifactId>
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>${hive.group}</groupId>
      <artifactId>hive-metastore</artifactId>
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>org.apache.thrift</groupId>
      <artifactId>libthrift</artifactId>
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>org.apache.thrift</groupId>
      <artifactId>libfb303</artifactId>
      <scope>provided</scope>
    </dependency>


  </dependencies>
  <build>
    <outputDirectory>target/scala-${scala.binary.version}/classes</outputDirectory>
    <testOutputDirectory>target/scala-${scala.binary.version}/test-classes</testOutputDirectory>
    <resources>
      <resource>
        <directory>${project.basedir}/src/main/resources</directory>
      </resource>
      <resource>
        <!-- Include the properties file to provide the build information. -->
        <directory>${project.build.directory}/extra-resources</directory>
        <filtering>true</filtering>
      </resource>
    </resources>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-antrun-plugin</artifactId>
        <executions>
          <execution>
            <phase>generate-resources</phase>
            <configuration>
              <!-- Execute the shell script to generate the spark build information. -->
              <target>
                <exec executable="bash">
                  <arg value="${project.basedir}/../build/spark-build-info"/>
                  <arg value="${project.build.directory}/extra-resources"/>
                  <arg value="${project.version}"/>
                </exec>
              </target>
            </configuration>
            <goals>
              <goal>run</goal>
            </goals>
          </execution>
        </executions>
      </plugin>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-dependency-plugin</artifactId>
        <executions>
          <!-- When using SPARK_PREPEND_CLASSES Spark classes compiled locally don't use
               shaded deps. So here we store jars in their original form which are added
               when the classpath is computed. -->
          <!-- See similar execution in mllib/pom.xml -->
          <execution>
            <id>copy-dependencies</id>
            <phase>package</phase>
            <goals>
              <goal>copy-dependencies</goal>
            </goals>
           <configuration>
              <outputDirectory>${project.build.directory}</outputDirectory>
              <overWriteReleases>false</overWriteReleases>
              <overWriteSnapshots>false</overWriteSnapshots>
              <overWriteIfNewer>true</overWriteIfNewer>
              <useSubDirectoryPerType>true</useSubDirectoryPerType>
              <includeArtifactIds>
                guava,jetty-io,jetty-servlet,jetty-servlets,jetty-continuation,jetty-http,jetty-plus,jetty-util,jetty-server,jetty-security,jetty-proxy,jetty-client
              </includeArtifactIds>
              <silent>true</silent>
            </configuration>
          </execution>
        </executions>
      </plugin>
    </plugins>
  </build>

  <profiles>
    <profile>
      <id>Windows</id>
      <activation>
        <os>
          <family>Windows</family>
        </os>
      </activation>
      <properties>
        <script.extension>.bat</script.extension>
      </properties>
    </profile>
    <profile>
      <id>unix</id>
      <activation>
        <os>
          <family>unix</family>
        </os>
      </activation>
      <properties>
        <script.extension>.sh</script.extension>
      </properties>
    </profile>
    <profile>
      <id>sparkr</id>
      <build>
        <plugins>
          <plugin>
            <groupId>org.codehaus.mojo</groupId>
            <artifactId>exec-maven-plugin</artifactId>
            <executions>
              <execution>
                <id>sparkr-pkg</id>
                <phase>compile</phase>
                <goals>
                  <goal>exec</goal>
                </goals>
              </execution>
            </executions>
            <configuration>
              <executable>${project.basedir}${file.separator}..${file.separator}R${file.separator}install-dev${script.extension}</executable>
            </configuration>
          </plugin>
        </plugins>
      </build>
    </profile>
  </profiles>

</project>