SparkSQL通过Hive创建DataFrame

ゝ一纸荒年。 2023-07-06 06:19 4阅读 0赞

### SparkSQL通过Hive创建DataFrame问题分析 ###

问题一
    Caused by: org.apache.spark.sql.catalyst.analysis.NoSuchTableException:
     Table or view 'stu' not found in database 'default';
    
    分析：确实没有临时表View，并且没有开启Hive支持
    解决：开启Hive支持
        val spark: SparkSession = SparkSession.builder()
          .appName("SparkUtils")
          .master("local[*]")
          .enableHiveSupport()  // Hive支持
          .getOrCreate()

问题二
    hive> show databases;
    FAILED: SemanticException org.apache.hadoop.hive.ql.metadata.HiveException:
    java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
    
    分析和解决
    1.2.0  SparkSql自带的Hive版本为1.2.0，每次连接之后版本会变成自带版本1.2.0，而本机版本为2.3.1，所以报错
    2.3.1  将版本改为2.3.1

问题三：
    Exception in thread "main" java.lang.IllegalArgumentException:
    Unable to instantiate SparkSession with Hive support because Hive classes are not found.
    
    分析： 因为没有spark连接hive的jar
    解决：
            <dependency>
                <groupId>org.apache.spark</groupId>
                <artifactId>spark-hive_2.11</artifactId>
                <version>2.3.3</version>
            </dependency>

问题四：
    19/12/31 11:00:53 INFO HiveMetaStore: 0: get_table : db=default tbl=stu
    19/12/31 11:00:53 INFO audit: ugi=yuhui	ip=unknown-ip-addr	cmd=get_table : db=default tbl=stu
    Exception in thread "main" org.apache.spark.sql.AnalysisException: Table or view not found: stu; line 2 pos 14
    
    分析：
    缺少 core-site.xml ， hdfs-site.xml ，hive-site.xml
    
    解决：
    /usr/app/hadoop-2.8.5/etc/hadoop/core-site.xml
    /usr/app/hadoop-2.8.5/etc/hadoop/hdfs-site.xml
    /usr/app/apache-hive-2.3.1-bin/conf/hive-site.xml
    放在resources中

问题五：
    Caused by: org.datanucleus.store.rdbms.connectionpool.DatastoreDriverNotFoundException:
    The specified datastore driver ("com.mysql.jdbc.Driver") was not found in the CLASSPATH.
    Please check your CLASSPATH specification, and the name of the driver.
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.39</version>
            </dependency>

问题六：
    Exception in thread "main" org.apache.hadoop.security.AccessControlException:
    Permission denied: user=yuhui, access=READ_EXECUTE, inode="/user/hive/warehouse/stu":root:supergroup:drwx-wx-wx
    	at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.check(FSPermissionChecker.java:318)
    	at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkPermission(FSPermissionChecker.java:225)
    	at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkPermission(FSPermissionChecker.java:189)
    	at org.apache.hadoop.hdfs.server.namenode.FSDirectory.checkPermission(FSDirectory.java:1663)
    
    
    方法一、在代码中加入这句话: System.setProperty("HADOOP_USER_NAME","ROOT")
    方法二、hadoop fs  -chmod 777 /user/hive/warehouse/stu.txt

### 二、SparkSQL通过Hive创建DataFrame代码 ###

package bolg
    
    import org.apache.spark.sql.{DataFrame, SparkSession}
    
    /**
     * @author: 余辉  
     * @blog: https://blog.csdn.net/silentwolfyh
     * @create: 2019-12-31 10:31
     * @description:
     **/
    object DF03_Create_Hive {
    
      def main(args: Array[String]): Unit = {
    
        System.setProperty("HADOOP_USER_NAME", "ROOT")
    
        val spark: SparkSession = SparkSession.builder()
          .appName("SparkUtils")
          .master("local[*]")
          .enableHiveSupport()
          .getOrCreate()
    
        spark.sql(
          """
            |select * from stu
            |
            |""".stripMargin).show()
      }
    }

### 三、数据及结果展示 ###

create table userinfo(id int,name string)
    row format delimited fields terminated by ',';
    
    load data local inpath '/root/data/userinfo.txt' overwrite into table userinfo;
    
    1,xiaohui
    2,xiaowang
    3,xiaoyu

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly95dWh1aS5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70]![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly95dWh1aS5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly95dWh1aS5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200104192552320.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly95dWh1aS5ibG9nLmNzZG4ubmV0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly95dWh1aS5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200104192634475.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly95dWh1aS5ibG9nLmNzZG4ubmV0,size_16,color_FFFFFF,t_70