hive insert 指定字段 select hive insert into table select

转载

ctaxnews 2023-09-04 18:10:00

文章标签 数据库 hive python java 大数据 文章分类 Hive 大数据

前言：近期在工作中往hive插入数据的过程中，发现了一些坑，它们也许是Hive提倡的比关系数据库更加自由的体现（同一时候引来一些问题）。也许是一些bug。总而言之，这些都须要使用Hive的IT人员需要额外注意，希望大家注意。

一、数据导入的六种方式

1、加载本地文件到hive表

hive insert 指定字段 select hive insert into table select_数据库

hive insert 指定字段 select hive insert into table select_大数据_02

hive insert 指定字段 select hive insert into table select_hive_03

2、加载hdfs文件到hive中

hive insert 指定字段 select hive insert into table select_数据库_04

3、加载数据覆盖表中已有的数据

hive insert 指定字段 select hive insert into table select_hive_05

4、创建表时通过select加载

create table if not exists default.dept_cats
as select * from dept;

5、创建表时通过insert加载

hive insert 指定字段 select hive insert into table select_数据库_06

6、创建表的时候通过location指定加载

create external table if not exists emp_partition(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
partitioned by (month string)
row format delimited fields terminated by '\t'

load data local inpath '/opt/datas/emp.txt' into table default.emp_partition partition (month='202012');

二、数据导入
首先我的数据是直接从原始表ods_94e8c88126d346cdbd8d34ea9c39e98f.ods_m_member_shard_321566导出csv格式的数据（我这次用的csv格式导入数据），导出数据之后我就用复制语句将原始表复制出来。

CREATE TABLE ods_94e8c88126d346cdbd8d34ea9c39e98f.ods_m_member_shard_321566_bk like ods_94e8c88126d346cdbd8d34ea9c39e98f.ods_m_member_shard_321566

注意点：导出数据之后需要将csv的表头去掉，hive导入数据时会自动识别，csv中的每一列都会当做一个字段，从左到右依次排序

这个报错提示是说我这个表现在已经分区了，导入数据的时候需要申明分区。

知识补充：

1.hive模糊搜索表

show tables like '*name*';

2.查看表结构信息

desc table_name; -- 查基础结构
desc formatted table_name; -- 查表明细结构
show create talbe table_name; -- 查建表语句

3.查看分区信息

show partitions table_name;

4.根据分区查询数据

select table_coulm from table_name where partition_name = '2014-02-25';

5.查看hdfs文件信息

dfs -ls /user/hive/warehouse/table02;

6.从文件加载数据进表(OVERWRITE覆盖,追加不需要OVERWRITE关键字)

LOAD DATA LOCAL INPATH 'dim_csl_rule_config.txt' OVERWRITE into table dim.dim_csl_rule_config;

–从查询语句给table插入数据

INSERT OVERWRITE TABLE test_h02_click_log PARTITION(dt) select *
from stage.s_h02_click_log where dt='2014-01-22' limit 100;

7.导出数据到文件

insert overwrite directory '/tmp/csl_rule_cfg' select a.* from dim.dim_csl_rule_config a;
hive -e "select day_id,pv,uv,ip_count,click_next_count,second_bounce_rate,return_visit,pg_type from tmp.tmp_h02_click_log_baitiao_ag_sum where day_id in ('2014-03-06','2014-03-07','2014-03-08','2014-03-09','2014-03-10');"> /home/jrjt/testan/baitiao.dat;

8.自定义udf函数
1.继承UDF类
2.重写evaluate方法
3.把项目打成jar包
4.hive中执行命令add jar /home/jrjt/dwetl/PUB/UDF/udf/GetProperty.jar; 5.创建函数create temporary function get_pro as 'jd.Get_Property'，jd.Get_Property为类路径;

9.查询显示列名及行转列显示

set hive.cli.print.header=true; // 打印列名
set hive.cli.print.row.to.vertical=true; // 开启行转列功能, 前提必须开启打印列名功能
set hive.cli.print.row.to.vertical.num=1; // 设置每行显示的列数

10.查看表文件大小,下载文件到某个目录,显示多少行到某个文件

dfs -du hdfs://BJYZH3-HD-JRJT-4137.jd.com:54310/user/jrjt/warehouse/stage.db/s_h02_click_log;
dfs -get /user/jrjt/warehouse/ods.db/o_h02_click_log_i_new/dt=2014-01-21/000212_0 /home/jrjt/testan/;
head -n 1000 文件名 > 文件名

11.杀死某个任务不在hive shell中执行

hadoop job -kill job_201403041453_58315

12.hive-wui路径

http://172.17.41.38/jobtracker.jsp

13.删除分区

alter table tmp_h02_click_log_baitiao drop partition(dt='2014-03-01');
alter table d_h02_click_log_basic_d_fact drop partition(dt='2014-01-17');

14.hive命令行操作
执行一个查询,在终端上显示mapreduce的进度，执行完毕后，最后把查询结果输出到终端上，接着hive进程退出，不会进入交互模式。

hive -e 'select table_cloum from table'

-S，终端上的输出不会有mapreduce的进度，执行完毕，只会把查询结果输出到终端上。这个静音模式很实用，,通过第三方程序调用，第三方程序通过hive的标准输出获取结果集。

hive -S -e 'select table_cloum from table'

执行sql文件

hive -f hive_sql.sql

15.hive上操作hadoop文件基本命令

-- 查看文件大小
dfs -du /user/jrjt/warehouse/tmp.db/tmp_h02_click_log/dt=2014-02-15;
-- 删除文件
dfs -rm /user/jrjt/warehouse/tmp.db/tmp_h02_click_log/dt=2014-02-15;

16.插入数据sql、导出数据sql

1.insert 语法格式为：
基本的插入语法：
INSERT OVERWRITE TABLE tablename [PARTITON(partcol1=val1,partclo2=val2)]select_statement FROM from_statement
insert overwrite table test_insert select * from test_table;

对多个表进行插入操作：
FROM fromstatte
INSERT OVERWRITE TABLE tablename1 [PARTITON(partcol1=val1,partclo2=val2)]select_statement1
INSERT OVERWRITE TABLE tablename2 [PARTITON(partcol1=val1,partclo2=val2)]select_statement2

from test_table                     
insert overwrite table test_insert1
select key
insert overwrite table test_insert2
select value;

insert的时候，from子句即可以放在select 子句后面，也可以放在 insert子句前面。
hive不支持用insert语句一条一条的进行插入操作，也不支持update操作。数据是以load的方式加载到建立好的表中。数据一旦导入就不可以修改。

2.通过查询将数据保存到filesystem
INSERT OVERWRITE [LOCAL] DIRECTORY directory SELECT.... FROM .....

导入数据到本地目录：
insert overwrite local directory '/home/zhangxin/hive' select * from test_insert1;
产生的文件会覆盖指定目录中的其他文件，即将目录中已经存在的文件进行删除。

导出数据到HDFS中：
insert overwrite directory '/user/zhangxin/export_test' select value from test_table;

同一个查询结果可以同时插入到多个表或者多个目录中：
from test_insert1
insert overwrite local directory '/home/zhangxin/hive' select *
insert overwrite directory '/user/zhangxin/export_test' select value;

17.mapjoin的使用应用场景：1.关联操作中有一张表非常小 2.不等值的链接操作

select /*+ mapjoin(A)*/ f.a,f.b from A t join B f  on ( f.a=t.a and f.ftime=20110802)

18.perl启动任务

perl /home/jrjt/dwetl/APP/APP/A_H02_CLICK_LOG_CREDIT_USER/bin/a_h02_click_log_credit_user.pl
APP_A_H02_CLICK_LOG_CREDIT_USER_20140215.dir >& /home/jrjt/dwetl/LOG/APP/20140306/a_h02_click_log_credit_user.pl.4.log

19.查看perl进程

ps -ef|grep perl

20.hive命令移动表数据到另外一张表目录下并添加分区

dfs -cp /user/jrjt/warehouse/tmp.db/tmp_h02_click_log/dt=2014-02-18 /user/jrjt/warehouse/ods.db/o_h02_click_log/;
dfs -cp /user/jrjt/warehouse/tmp.db/tmp_h02_click_log_baitiao/* /user/jrjt/warehouse/dw.db/d_h02_click_log_baitiao_basic_d_fact/;

--复制所有分区数据
alter table d_h02_click_log_baitiao_basic_d_fact add partition(dt='2014-03-11') location '/user/jrjt/warehouse/dw.db/d_h02_click_log_baitiao_basic_d_fact/dt=2014-03-11';

21.导出白条数据

hive -e "select day_id,pv,uv,ip_count,click_next_count,second_bounce_rate,return_visit,pg_type from tmp.tmp_h02_click_log_baitiao_ag_sum where day_id like '2014-03%';"> /home/jrjt/testan/baitiao.xlsx;

22.hive修改表名

ALTER TABLE o_h02_click_log_i RENAME TO o_h02_click_log_i_bk;

23.hive复制表结构

CREATE TABLE d_h02_click_log_baitiao_ag_sum LIKE tmp.tmp_h02_click_log_baitiao_ag_sum;

24.hive官网网址

https://cwiki.apache.org/conflue ... ionandConfiguration
http://www.360doc.com/content/12/0111/11/7362_178698714.shtml

25.hive添加字段

alter table tmp_h02_click_log_baitiao_ag_sum add columns(current_session_timelenth_count bigint comment '页面停留总时长');
ALTER TABLE tmp_h02_click_log_baitiao CHANGE current_session_timelenth current_session_timelenth bigint comment '当前会话停留时间';

26.hive开启简单模式不启用mr

set hive.fetch.task.conversion=more;

27.以json格式输出执行语句会读取的input table和input partition信息

Explain dependency query

我通过

desc formatted table_name; -- 查表明细结构

发现用了dt作为分区，此时我把分区加上结果还是报错

hive insert 指定字段 select hive insert into table select_java_07

这个提示说尝试加载的文件与目标表的文件格式不匹配。

这个时候我在想是怎么不匹配，首先查看文件格式，都是原始表导出的数据，应该格式不会有问题，再次检查了文件编码是否是utf-8，打开文件查看中文没有乱码，最后查看用show creat table table_name查看复制表发现是建表格式问题。

ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
WITH SERDEPROPERTIES ( 
  'field.delim'='\t', 
  'serialization.format'='\t')

hive insert 指定字段 select hive insert into table select_hive_08

这说明原始表是已“/t”作为分隔符，且hive的存储格式为orc，我们用csv格式导入的数据只能用“,”作为分隔符，且格式为HiveIgnoreKeyTextOutputFormat，此时我们可以转变思维，我们不在复制表，我们把之前的复制表删除，重新建立一个字段一样但是格式匹配csv的表，直接在建表语句后面加上ROW FORMAT DELIMITED FIELDS TERMINATED BY ','此时我们再看结果，结果提示导入成功。

hive insert 指定字段 select hive insert into table select_java_09

导入成功后我们用

insert into table select * from table_name

插入数据，结果伤心的是又报错了。

hive insert 指定字段 select hive insert into table select_hive_10

为什么会报错呢？我们的sql应该没有问题吧？细致查看提示信息。说是“表仅仅33列。可是查询有34列”。刚才说过，我建的两张表除了字段名称的差异。其结构全然一样。两张表都有34个字段（33个普通字段和1个分区字段），为什么说查询表仅仅有33列呢？这是因为Hive遵循读时模式且遵循相对宽松的语法，在插入或装载数据时。不会验证数据与表的模式是否匹配。仅仅有在读取数据时才会验证。因此在向表插入数据时不会验证。而查询读取表中的数据时会验证。我对sql进行了调整，把所有的字段都select 出来（分区字段除外），此时我们再看结果。

hive insert 指定字段 select hive insert into table select_python_11

这个状态说明我们数据是已经插入进去了，于是我查看表中的数据和我csv文件的数据作对比，发现一个新的问题，我插入的数据和csv中的字段不对应，发生了偏移，我仔细检查了csv文件和表中的数据并做了对比，发现是我的分区字段dt引起的，在csv文件中有dt这一列，导入的时候是不要的。

我将csv文件中的dt列删除重新导入数据，此时导入的数据准确无误。

三、总结
1、用csv导入数据需要将表头去掉
2、导入数据的时候请先用show creat table查看表结构，然后考虑导入方式（csv文件表结构需要用“,”分隔，存储格式为HiveIgnoreKeyTextOutputFormat）
3、如果hive是分区表，csv表里需要将分区字段剔除
4、如果hive是分区表，hive load数据的时候请带上分区
5、对于hive来说，inster数据请select 所有字段（分区字段除外）
6、导入之前一定要检查csv表格每一个字段是否有“,”,如果有“,”，请做特殊处理，否则会发生数据偏移
7、完整实例如下

-- 导入hive

load data local inpath 'query-hive-8053110.csv' into table ods_94e8c88126d346cdbd8d34ea9c39e98f.ods_m_member_shard_321566_bk partition (dt='2020-12-15');


-- 插入数据

```sql
insert into table ods_94e8c88126d346cdbd8d34ea9c39e98f.ods_user_member_union partition (dt='2020-12-18')
select
id,
distinct_id,
membeship_type,
paid_membership_type,
right_card_membership_type,
register_channel,
birth_year,
birth_mounth,
constellation_cycle,
tenant_id,
member_id,
domain_id,
user_id,
nickname,
last_name,
first_name,
full_name,
avatar_url,
gender,
birth_date,
age,
mobile,
email,
id_card,
platform_list,
register_source,
register_time,
update_time,
channel_name,
channel_first_name,
wx_nickname,
wx_headimgurl,
wx_province,
wx_city,
wx_country,
wx_sex,
channel_member_id,
channel_member_union_id
from ods_94e8c88126d346cdbd8d34ea9c39e98f.ods_user_member_union_bk where dt='2020-12-18'

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。