Sparksql读取hive回顾

最新推荐文章于 2024-06-01 08:53:20 发布

南潇如梦

最新推荐文章于 2024-06-01 08:53:20 发布

阅读量493

点赞数 1

分类专栏：大数据那些事文章标签： hive 大数据 hadoop

本文链接：https://blog.youkuaiyun.com/weixin_45682261/article/details/126132169

版权

本文回顾了如何使用SparkSQL读取Hive数据，包括Hive中日期格式的注意事项，数据导入步骤，以及在Spark工程中配置Hive-site.xml和相关依赖设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据准备
hive这边建表

create table employee (
employee_id int,
employee_name string,
manager_id int
)
comment 'this is employee table'     
row format delimited             
fields terminated by '\t'       
lines terminated by '\n'        
stored as textfile;  

create table employeesalary (
employee_id int,
salary int,
datetime date
)
comment 'this is employeesalary table'     
row format delimited             
fields terminated by '\t'       
lines terminated by '\n'        
stored as textfile;

注意这个日期
mysql的date是 ‘2021-08-08’，就是date就是要带单引号
hive的date是2021-08-08，至于datestime是有小时分钟秒的

导入数据
就是在非hive界面，随便创建一个文档，把数据写入到文档里面

vim t1

001	Tom	null
002	Alex	001
003 Sophia	001
004	Wiliam	002
005	Amelia	null

vim t2

001	10000	2022-01-20
001	10010	2022-02-20
002	10005	2022-01-20
002	10000	2022-02-20

hive这边

load data local inpath 'data/t1' into table employee;
load data local inpath 'data/t2' into table employeesalary;

spark的开发
创建一个工程，添加scala的环境，然后把hive-site.xml添加到resource里面
hive-site.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?><!--
   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.
--><configuration>

<!--配置mysql的连接字符串-->
<property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://qianfeng03:3306/hive?createDatabaseIfNotExist=true&amp;