如何快速把hdfs数据动态导入到hive表

最新推荐文章于 2024-04-19 22:37:19 发布
最新推荐文章于 2024-04-19 22:37:19 发布
阅读量897 收藏 3
点赞数
文章标签： 大数据 java 数据库

1. hdfs 文件

  
{"retCode":1,"retMsg":"Success","data":[{"secID":"000001.XSHE","ticker":"000001","secShortName":"深发展A","exchangeCD":"XSHE","tradeDate":"1991-10-21","preClosePrice":24,"actPreClosePrice":24,"openPrice":24,"highestPrice":24.4,"lowestPrice":23.85,"closePrice":23.9,"turnoverVol":355700,"turnoverValue":8582250,"turnoverRate":0.0058,"accumAdjFactor":0.0117201563,"negMarketValue":1462295257.8,"marketValue":2145064267.7,"PB":2.2666,"isOpen":1},{"secID":"000002.XSHE","ticker":"000002","secShortName":"深万科A","exchangeCD":"XSHE","tradeDate":"1991-10-21","preClosePrice":8,"actPreClosePrice":8,"openPrice":8,"highestPrice":8,"lowestPrice":7.7,"closePrice":7.9,"turnoverVol":375000,"turnoverValue":2944200,"turnoverRate":0.0066,"accumAdjFactor":0.0117337592,"negMarketValue":451011000,"marketValue":615927450,"PB":1.0001,"isOpen":1},{"secID":"000004.XSHE","ticker":"000004","secShortName":"深安达A","exchangeCD":"XSHE","tradeDate":"1991-10-21","preClosePrice":7.25,"actPreClosePrice":7.25,"openPrice":7.25,"highestPrice":7.25,"lowestPrice":7.2,"closePrice":7.2,"turnoverVol":92000,"turnoverValue":665125,"turnoverRate":0.0078,"accumAdjFactor":0.2649084628,"negMarketValue":84977100,"marketValue":175500000,"PB":7.4199,"isOpen":1},{"secID":"000005.XSHE","ticker":"000005","secShortName":"深原野A","exchangeCD":"XSHE","tradeDate":"1991-10-21","preClosePrice":6.46,"actPreClosePrice":6.46,"openPrice":6.49,"highestPrice":6.49,"lowestPrice":6.49,"closePrice":6.49,"turnoverVol":94500,"turnoverValue":613305,"turnoverRate":0.0021,"accumAdjFactor":0.1016459912,"negMarketValue":287756865,"marketValue":584100000,"PB":9.1783,"isOpen":1},{"secID":"000009.XSHE","ticker":"000009","secShortName":"深宝安A","exchangeCD":"XSHE","tradeDate":"1991-10-21","preClosePrice":5.75,"actPreClosePrice":5.75,"openPrice":5.7,"highestPrice":5.8,"lowestPrice":5.65,"closePrice":5.75,"turnoverVol":767500,"turnoverValue":4382245,"turnoverRate":0.0084,"accumAdjFactor":0.1026538759,"negMarketValue":524745000,"marketValue":1293922500,"PB":2.4503,"isOpen":1},{"secID":"600601.XSHG","ticker":"600601","secShortName":"延中实业","exchangeCD":"XSHG","tradeDate":"1991-10-21","preClosePrice":65.7,"actPreClosePrice":65.7,"openPrice":66.4,"highestPrice":66.4,"lowestPrice":66.4,"closePrice":66.4,"turnoverVol":5333,"turnoverValue":354111,"dealAmount":81,"turnoverRate":0.0053,"accumAdjFactor":0.0010592167,"negMarketValue":66400000,"marketValue":66400000,"PB":40.7703,"isOpen":1},{"secID":"600602.XSHG","ticker":"600602","secShortName":"真空电子","exchangeCD":"XSHG","tradeDate":"1991-10-21","preClosePrice":640.6,"actPreClosePrice":640.6,"openPrice":647,"highestPrice":647,"lowestPrice":647,"closePrice":647,"turnoverVol":2589,"turnoverValue":1675083,"dealAmount":227,"turnoverRate":0.0051,"accumAdjFactor":0.0019640692,"negMarketValue":330552300,"marketValue":1294000000,"PB":287.6707,"isOpen":1},{"secID":"600651.XSHG","ticker":"600651","secShortName":"飞乐音响","exchangeCD":"XSHG","tradeDate":"1991-10-21","preClosePrice":119.6,"actPreClosePrice":119.6,"openPrice":120.8,"highestPrice":120.8,"lowestPrice":120.8,"closePrice":120.8,"turnoverVol":1102,"turnoverValue":133122,"dealAmount":14,"turnoverRate":0.0022,"accumAdjFactor":0.0008192464,"negMarketValue":60400000,"marketValue":60400000,"PB":39.6397,"isOpen":1},{"secID":"600652.XSHG","ticker":"600652","secShortName":"爱使电子","exchangeCD":"XSHG","tradeDate":"1991-10-21","preClosePrice":83.2,"actPreClosePrice":83.2,"openPrice":0,"highestPrice":0,"lowestPrice":0,"closePrice":83.2,"turnoverVol":0,"turnoverValue":0,"dealAmount":0,"turnoverRate":0,"accumAdjFactor":0.0006920481,"negMarketValue":22464000,"marketValue":22464000,"PB":33.8019,"isOpen":0},{"secID":"600653.XSHG","ticker":"600653","secShortName":"申华电工","exchangeCD":"XSHG","tradeDate":"1991-10-21","preClosePrice":103.4,"actPreClosePrice":103.4,"openPrice":104.4,"highestPrice":104.4,"lowestPrice":104.4,"closePrice":104.4,"turnoverVol":240,"turnoverValue":25056,"dealAmount":4,"turnoverRate":0.0005,"accumAdjFactor":0.0009289199,"negMarketValue":52200000,"marketValue":52200000,"PB":97.279,"isOpen":1},{"secID":"600654.XSHG","ticker":"600654","secShortName":"飞乐股份","exchangeCD":"XSHG","tradeDate":"1991-10-21","preClosePrice":633.2,"actPreClosePrice":633.2,"openPrice":639.5,"highestPrice":639.5,"lowestPrice":639.5,"closePrice":639.5,"turnoverVol":101,"turnoverValue":64590,"dealAmount":26,"turnoverRate":0.0048,"accumAdjFactor":0.000663586,"negMarketValue":13429500,"marketValue":134358950,"PB":282.9834,"isOpen":1},{"secID":"600656.XSHG","ticker":"600656","secShortName":"浙江凤凰","exchangeCD":"XSHG","tradeDate":"1991-10-21","preClosePrice":1242.9,"actPreClosePrice":1242.9,"openPrice":1255.3,"highestPrice":1255.3,"lowestPrice":1255.3,"closePrice":1255.3,"turnoverVol":140,"turnoverValue":175742,"dealAmount":7,"turnoverRate":0.0031,"accumAdjFactor":0.0007136096,"negMarketValue":56502308.3,"marketValue":321798665.6,"PB":-604.4303,"isOpen":1}]}
 
 

2. 创建 hive 临时表

CREATE EXTERNAL TABLE if not exists sensitop.equd_json_tmp (
  retCode string,
  retMsg string,
  data array<struct<
			  secID: string,
			  tradeDate: date,
			  ticker: string,
			  secShortName: string,
			  exchangeCD: string,
			  preClosePrice: double,
			  actPreClosePrice: double,
			  openPrice: double,
			  highestPrice: double,
			  lowestPrice: double,
			  closePrice: double,
			  turnoverVol: double,
			  turnoverValue: double,
			  dealAmount: int,
			  turnoverRate: double,
			  accumAdjFactor: double,
			  negMarketValue: double,
			  marketValue: double,
			  PE: double,
			  PE1: double,
			  PB: double,
			  isOpen: int>>)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION 'hdfs://hdfs1.wdp:8020/sensitop/finance/equd';
 

3. 创建 hive 表

需要把上面表里数组里的数据一条一条放入这个表：
CREATE TABLE if not exists sensitop.equd_h(
                        secID string,
                        ticker string,
                        secShortName string,
                        exchangeCD string,
                        tradeDate date,
                        preClosePrice double,
                        actPreClosePrice double,
                        openPrice double,
                        highestPrice double,
                        lowestPrice double,
                        closePrice double,
                        turnoverVol double,
                        turnoverValue double,
                        dealAmount int,
                        turnoverRate double,
                        accumAdjFactor double,
                        negMarketValue double,
                        marketValue double,
                        PE double,
                        PE1 double,
                        PB double,
                        isOpen int)
partitioned by (year string)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
 
然后新建一个最张表
CREATE TABLE if not exists sensitop.equd(
                        secID string,
                        ticker string,
                        secShortName string,
                        exchangeCD string,
                        tradeDate date,
                        preClosePrice double,
                        actPreClosePrice double,
                        openPrice double,
                        highestPrice double,
                        lowestPrice double,
                        closePrice double,
                        turnoverVol double,
                        turnoverValue double,
                        dealAmount int,
                        turnoverRate double,
                        accumAdjFactor double,
                        negMarketValue double,
                        marketValue double,
                        PE double,
                        PE1 double,
                        PB double,
                        isOpen int)
partitioned by (year string)
 
注意：这里的字段顺序和上面临时表的顺序要一致。
 

4. 用 Partition 更新数据

insert overwrite table sensitop.equd_tmp
partition (year='2016')
select b.dt.secID,
b.dt.ticker,
b.dt.secShortName,
b.dt.exchangeCD,
b.dt.tradeDate,
b.dt.preClosePrice,
b.dt.actPreClosePrice,
b.dt.openPrice,
b.dt.highestPrice,
b.dt.lowestPrice,
b.dt.closePrice,
b.dt.turnoverVol,
b.dt.turnoverValue,
b.dt.dealAmount,
b.dt.turnoverRate,
b.dt.accumAdjFactor,
b.dt.negMarketValue,
b.dt.marketValue,
b.dt.PE,
b.dt.PE1,
b.dt.PB,
b.dt.isOpen
from sensitop.equd_json_tmp LATERAL VIEW explode(equd_json_tmp.data) b AS dt
where dt.tradedate >= '2016-01-01' and dt.tradedate <= '2016-12-31';
 
 
insert overwrite table sensitop.equd
partition (year='2016')
select secID,
ticker,
secShortName,
exchangeCD,
tradeDate,
preClosePrice,
actPreClosePrice,
openPrice,
highestPrice,
lowestPrice,
closePrice,
turnoverVol,
turnoverValue,
dealAmount,
turnoverRate,
accumAdjFactor,
negMarketValue,
marketValue,
PE,
PE1,
PB,
isOpen
from sensitop.equd_tmp dt
where year = '2016';
 

5. 用nifi实现动态插入数据

NewImage 
 
这里有二个分支，左边一个是每天20：00更新当年的partion； 右边一个是更新1990 到 2015 年的数据，而且只需要更新一次。
 
insert overwrite table sensitop.equd_h
partition (year='${year}')
select b.dt.secID,
b.dt.ticker,
b.dt.secShortName,
b.dt.exchangeCD,
b.dt.tradeDate,
b.dt.preClosePrice,
b.dt.actPreClosePrice,
b.dt.openPrice,
b.dt.highestPrice,
b.dt.lowestPrice,
b.dt.closePrice,
b.dt.turnoverVol,
b.dt.turnoverValue,
b.dt.dealAmount,
b.dt.turnoverRate,
b.dt.accumAdjFactor,
b.dt.negMarketValue,
b.dt.marketValue,
b.dt.PE,
b.dt.PE1,
b.dt.PB,
b.dt.isOpen
from sensitop.equd_json_tmp LATERAL VIEW explode(equd_json_tmp.data) b AS dt
where dt.tradedate >= '${year}-01-01' and dt.tradedate <= '${year}-12-31'
 
 
insert overwrite table sensitop.equd
partition (year='${year}')
select secID,
ticker,
secShortName,
exchangeCD,
tradeDate,
preClosePrice,
actPreClosePrice,
openPrice,
highestPrice,
lowestPrice,
closePrice,
turnoverVol,
turnoverValue,
dealAmount,
turnoverRate,
accumAdjFactor,
negMarketValue,
marketValue,
PE,
PE1,
PB,
isOpen
from sensitop.equd_tmp dt
where year = '${year}' 
 

 

 

NIFI 中国社区 QQ群：595034369

 

weixin_34253539
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
DataX数据的迁移（MySQL、HDFS，Hive）
04-12
1.将Mysql中的数据迁移到Hdfs文件系统中，然后通过Hive加载HDFS文件系统中的数据值 2.将Hive中的数据迁移到指定Mysql数据库中 注意点： 1.数据迁移的过程中，由于hive的Null值存储为"\N"，Mysql存储为NULL值，二者转换时需要注意，再数据迁移json模板中，"nullFormat": "\\N", 该配置信息就是解决了数据迁移是存在的Null值。 2.如果hive中没有数值DataX迁移时会出现异常 执行语句：python /opt/module/datax/bin/datax.py /opt/module/datax/job/xxx.json
hdfs文件加载进hive表的两种方法
qq_42456324的博客
12-17 2039
1.当存储路径与表所在路径不一致时，可以load进去，如果表中字段类型不对应，该字段的值将会为空 load data inpath 'hdfs://hadoop01:9000/tmp/sales_info.txt' overwrite into table sales_info partition(dt = '20191215'); 2.当存储路径与表所在路径一致时，可以采用添加分区的方式 alter table ods_nshop.ods_01_sales add partition (dt=.
Doris hdfs数据导入doris动态分区表
@羲凡—只为更好的活着
01-07 3562
@羲凡——只为了更好的活着 Doris hdfs数据导入doris动态分区表 本文重点 1.动态分区表创建 2.读取路径作为分区参数 3.预聚合增加count列——set (cnt = 1) 4.broker load 的 hdfs HA 配置 前期准备 1.安装好doris——Doris 编译安装(完整版) 2.doris基本知识扫盲 Doris的数据模型主要分为3类:Aggregate、Duplicate、Unique; Doris支持单分区和复合分区两种建表方式; 单分区指的是只指定分桶; 复合分区指
Java将hive数据导入到hdfs_如何快速把hdfs数据动态导入到hive表
weixin_34247778的博客
02-26 268
{"retCode":1,"retMsg":"Success","data":[{"secID":"000001.XSHE","ticker":"000001","secShortName":"深发展A","exchangeCD":"XSHE","tradeDate":"1991-10-21","preClosePrice":24,"actPreClosePrice":24,"openPrice"...
一百一十一、Hive——从HDFS到Hive的数据导入（静态分区、动态分区）
tiantang2renjian的博客
05-11 3533
从HDFS到Hive的数据导入（静态分区、动态分区）
使用在hdfs上的文件导入到hive中
热门推荐
qq_22562651的博客
07-31 1万+
hive要打开，另一边root@hadoop-virtual-machine:/# hdfs dfs -ls /user/hive查看hdfs上都有些什么， 然后把数据文件导入到hdfs上 ，root@hadoop-virtual-machine:/# hdfs dfs -put /opt/os.txt /user/hive/ hive那边用show databases;查看当前都有什么数据库
如何把hdfs上的一个结构化数据映射为hive的一张表
狗子的逆袭之路
09-11 1012
文件的路径好像有规律 难道要把结构化数据放置在这个路径下？ 不一定 外部表指定文件路径 数据库.表 -------》 /user/hive/warehouse/数据库.db/表名 建表的字段顺序和类型一定要和文件一致。 create table t_3(id int ,name string age int ) row format delimited ...
离线分析HDFS的FsImage查找集群小文件
qq_36864672的博客
03-01 339
统计中有两个重要参数parent_id和instr(path,'/',1,2)这两个参数主要表示指定统计的HDFS目录以及目录钻取深度，instr()函数中的最后一个参数即为目录钻取深度（如果为parent_id=1为根目录“/”，钻取深度则为2，即根目录下所有的数据目录，需要钻取根深的目录则依次递增）。如下统计方式主要基于Hive库和表的统计分析，统计Hive中所有库存的数据文件数、Block数量、文件总大小(bytes)及平均文件大小(bytes)。2.使用hdfs oiv命令解析FsImage文件。
HDFS离线分析FsImage元数据
Tybyqi的博客
01-31 1187
概观 HDFS是Hadoop的一部分，它具有下载当前名称节点快照的命令。我们可以通过Spark加载图像或对其进行数据摄取，以使其进入Hive以分析数据并验证它如何使用HDFS。 HDFS文件系统元数据存储在名为“FsImage”的文件中。我们在此快照中包含： 整个文件系统命名空间。 地图，块和文件复制。 配额，ACLS等属性 我必须解决的问题如下： 运行该命令以下载映像并生成XML文件...
0464-如何离线分析HDFS的FsImage查找集群小文件
Hadoop_SC的博客
01-28 401
温馨提示：如果使用电脑查看图片不清晰，可以使用手机打开文章单击文中的图片放大查看高清原图。 Fayson的github： https://github.com/fayson/cdhproject 提示：代码块部分可以左右滑动查看噢 1 文章编写目的 随着Hadoop集群数据量的增长，集群中也同时会存在大量的小文件，即文件Size比HDFS的Block Size（默认128MB）小的多的文件。H...
使用kettle将mysql中的数据导入到hive中
01-07
先说说思路：1是直接从mysql输出至hive中，另外一个是先将数据搞到hdfs中，再load一下，当然这只是textfile存储格式而言，若是ORC等其他列式存储的话建议先搞一个textfile格式的中间表，再insert into table select ...
Kettle将数据导入导Hive2
01-07
 本来将数据导入到hive，但是不知道什么原因太慢了，一小时200条数据，优化一波kettle，还是成效不大，因此改为借用hadoop file output 到hdfs，然后再load 到hive表里 一，正文 1.在转换里拖入big data/hadoop ...
datax实战-mysql同步数据到hive
01-07
{ job: { setting: { speed: { channel: 1 }, errorLimit: { record: 0, percentage: 0.02 } }, content: [ { reader: {
datax数据从hive导入mysql数据缺失解决
01-18
datax数据从hive表导入mysql表,数据缺失解决
2.微服务技术
最新发布
qq_36352889的博客
04-19 519
微服务技术对比：
【电力大数据服务平台】项目可行性研究报告概述
【CSDN】
04-16 1012
六是数据安全，可实现不同业务、不同人员数据的逻辑隔离，确保数据的授权访问；电力大数据服务平台项目可行性研究报告写作的重点将放在政策可行性（前提：项目的建设必须满足项目建设地的相关规划政策）、市场可行性（基础：没有市场前景的产品/服务，就没有的价值）、技术可行性（先决条件：项目产品技术或工艺能够满足生产的需求）和财务可行性。电力大数据云平台定位为数据共享平台、数据分析应用平台、大数据应用开发运行平台，从数据采集、存储、预处理、计算、分析、可视化等多个层面为数据分析人员、大数据应用开发人员提供服务。
Spark集群的搭建
qq_74137843的博客
04-18 840
（2）配置Workers文件。复制Workers.template文件并重命名为Workers，打开Workers文件删除原有内容，并添加如下代码，每一行代表一个子节点的主机名，这里的workers文件名为，slaves.template。（3）进入Spark的/conf目录下，复制spark-env.sh.template文件并重命名为spark-env.sh。(7)通过命令jps查看进程，开启Spark集群后，master节点增加了Master进程，而子节点则增加了Worker进程。
Docker(七)：容器监控工具(Portainer、CAdvisor)
人不风流枉少年
04-17 560
可以通过docker安装，用于管理和监控docker，基本上的docker命令都有对应的按钮来操作。
1688官方API商品数据采集接口|阿里巴巴中国站获得1688商品详情 API 返回值说明
TinagirlAPI的博客
04-16 1088
随着全球经济一体化和电子商务的快速发展，网络购物的需求日益增加。不断涌现的电商企业使得行业的竞争情况愈演愈烈。在这种情况下，企业不仅要加大经营力度，还要在自己的基础设施和技术上持续投入，才能更好的适应市场和消费习惯。
hdfs数据导入到Hive
05-24
首先将数据文件上传到HDFS中，然后使用Hive的LOAD DATA命令将数据导入到Hive表中。LOAD DATA命令的语法如下： ``` LOAD DATA [LOCAL] INPATH 'hdfs_path' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值