Hive 修改表添加分区和 加载数据时添加分区 的区别

1、 数据量表比于2GB面2GB文件于32位os限另外备份间
2、 包括历史数据表比新数据放入新区典型例：历史表前月份数据修改其月份能read-only
ORACLE支持区：tables, indexes on tables, materialized views, and indexes on materialized views
区SQLDML透明(应用程序必知道已经作区)DDL同区进行管理
同区间必须相同逻辑属性比共同表名列名数据类型约束;
同物理属性比pctfree, pctused, and tablespaces
区独立性：即使某些区用其区仍用
64000区具LONG or LONG RAW列表CLOB or BLOB列表
用to_date函数比：
alter session set nls_date_format='mm/dd/yyyy';
CREATE TABLE sales_range
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2000 VALUES LESS THAN('02/01/2000'),
PARTITION sales_feb2000 VALUES LESS THAN('03/01/2000'),
PARTITION sales_mar2000 VALUES LESS THAN('04/01/2000'),
PARTITION sales_apr2000 VALUES LESS THAN('05/01/2000')
);
Partition Key：16columns,nullable
非区表区或者非区索引;
区表区或者非区索引;

1数据存储及压缩优化

针对hive中表的存储格式通常有textfile和orc，压缩格式一般使用snappy。相比于 textfile格式存储，orc占有更少的存储。因为hive底层使用MR计算架构，数据流是hdfs到磁盘再到hdfs，而且会有很多次IO读写 *** 作，所以使用orc数据格式和snappy压缩策略可以降低IO读写，还能降低网络传输量，这样在一定程度上可以节省存储空间，还能提升hql的执行效率；

2 Hive Job优化

①　调节Jvm参数，重用Jvm；

②　合理设置Map个数；

③　合理设置Reduce个数；

3 Sql语法优化

① 建表优化 ：

1) Hive创建表的时候，可以建分区表，分桶表；

2) Hive创建表的时候，可以指定数据存储格式：TextFile、SequenceFile、RCfile 、ORCfile；

② 查询时优化 ：

1) 列裁剪，在查询时只读取需要的列，避免全列扫描，不要使用select from table；

2) 分区裁剪：在查询时只读取需要分区的数据，避免全表扫描；

3) 开启谓词下推：set hiveoptimizeppd = true，默认是true：

a 将Sql语句中的where谓词逻辑都尽可能提前执行，减少下游处理的数据量；

4) 大表join小表：

a 开启MapJoin：set hiveautoconvertjoin=true：

b MapJoin是将Join双方比较小的那个表直接分发到各个Map进程的内存中，在 Map进程中进行Join *** 作， 这样就不用进行Reduce步骤 ，从而提高了速度( 大表left join小表才有效 ，小表left join大表会失效)；

5) 大表join大表：

a SMB Join ：Sort Merge Bucket Join(数据不仅分桶了，而且每个桶数据是排好序了)；

b 开启SMB Join之后，底层是根据两个表join字段进行分桶存储，这样的话，两张表就变为了基于桶之间join关联查询，而不是基于整张表的join，减少了笛卡尔积；

6) 少用in，用left semi join替代in：

a 原始写法：select aid, aname from a where aid in (select bid from b)；

b 用join改写：select aid, aname from a join b on aid = bid；

c left semi join改写：select aid, aname from a left semi join b on aid = bid；

7) 用union all代替union，因为union all不需要去重，也不需要排序，效率高于union；

(每天1小题，进步1点点)

1创建表的语句：Create [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], )] [COMMENT table_comment] [PARTITIONED BY (col_name data_type [COMMENT col_comment], )] [CLUSTERED BY (col_name, col_name, ) [SORTED BY (col_name [ASC|DESC], )] INTO num_buckets BUCKETS] [ROW FORMAT row_format] [STORED AS file_format] [LOCATION hdfs_path]
稍微解释下
CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常。EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数 据会被一起删除，而外部表只删除元数据，不删除数据。如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。有 分区的表可以在创建的时候使用 PARTITIONED BY 语句。一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。而且，表和分区都可以对某个列进行 CLUSTERED BY *** 作，将若干个列放入一个桶（bucket）中。也可以利用SORT BY 对数据进行排序。这样可以为特定应用提高性能。
创建普通的表：create table test_table (id int,name string,no int) row format delimited fields terminated by ',' stored as textfile；
//指定了字段的分隔符为逗号，所以load数据的时候，load的文本也要为逗号，否则加载后为NULL。hive只支持单个字符的分隔符，hive默认的分隔符是\001
4
创建带有partition的表：create table test_part (id int,name string,no int) partitioned by (dt string) row format delimited fields terminated by '\t' stored as textfile ;
用创建用\t作分隔符的表，PT为分区字段，
加载如下：
load data local inpath '/home/zhangxin/hive/test_hivetxt' overwrite into table test_part partition (dt='2012-03-05');
//local是本地文件，注意不是你电脑上的文件，是hadoop所在的本地文件
//如果是在hdfs里的文件，则不需要local。 overwrite into是覆盖表分区，仅仅是这个分区的数据内容，如果是追加，则不需要overwrite
5
创建external表：(外部表)create external table test_external (id int,name string,no int) row format delimited fields terminated by ',' location '/home/zhangxin/hive/test_hivetxt';
//用逗号分隔的表，且无分区， location后是外部表数据的存放路径
6
创建与已知表相同结构的表 Like：只复制表的结构，而不复制表的内容。create table test_like_table like test_bucket;

本文说明如何通过把Hive中的数据备份到磁盘中，并从磁盘中恢复到Hive中。

1，把Hive中的表数据备份到磁盘中。 

备份示例：
以上语句说明，把src_companyinfo表中的数据以‘|’为分隔符号，并备份到“/root/grc_bigdata/backup/src_companyinfo”目录中。 

备份之后的目录结构如下：
在Hue中浏览的src_xtbillmx2013_st的目录结构如下： 

从以上结果可以看出，数据文件输出的个数与表在Hive中存储的文件个数不一定一致。

2，把磁盘中的文件恢复到Hive中。 

先在hive中执行建表脚本：
然后在Hive中执行如下导入命令：

3，在Hive中备份46个表、一共552GB的数据到Linux文件系统，一共耗时55386 秒，大概154个小时。 

从Linux文件系统中恢复以上数据，耗时41217秒，大概114个小时。

原文

1 hive如何通过insert语句将数据插入表中
从文件加载数据进表（OVERWRITE覆盖，追加不需要OVERWRITE关键字）

LOAD DATA LOCAL INPATH 'dim_csl_rule_configtxt' OVERWRITE into table dimdim_csl_rule_config;

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

INSERT OVERWRITE TABLE test_h02_click_log PARTITION(dt) select
from stages_h02_click_log where dt='2014-01-22' limit 100;
2 如何将excel中的数据导入hive仓库中
将存放在excel表中的数据如何导入到hive中，首先前提是excel表中数据类型以及长度要和hive仓库中表的字段属性一致，否则会报异常。其次因为建表的时候，已经定义了表的分割符号（row format delimited fields terminated by '，'），所以不能直接将excel表格中数据导入的hive仓库中。处理方式如下：

先将excel表中数据另存转化为datacsv格式，转化为csv格式的文件默认就是用“，”进行分割的，可以用notepad++打开datacsv格式查看。然后再讲数据导入到hive仓库中即可。但是执行如下导入语句时，发现报错，显示Wrong file format。

检查以后发现，因为在创建表格时，使用的是RCFILE存储格式

STORED AS INPUTFORMAT 'orgapachehadoophiveqlioRCFileInputFormat'

OUTPUTFORMAT 'orgapachehadoophiveqlioRCFileOutputFormat'

而导入到是TextFile文件格式，所以报错，解决办法建表的格式默认使用TextFile格式即可。

如何将文件转化为rcfile文件格式：

(1)hive 中直接 通过textfile表进行insert转换，比如通过如下将textfile数据导入到rcfile中。

insert overwrite table _RCTable partition(dt='2013-09-30') select p_id,tm,idate,phone from tmp_testp where dt='2013-09-30';

(2)使用mapreduce将普通的文件压缩成RCFile，并且再读取RCFile文件。
3 如何把hive导出到本地的表导入mysql中
MySQL命令行导出数据库：

1，进入MySQL目录下的bin文件夹：cd MySQL中到bin文件夹的目录

如我输入的命令行：cd C:\Program Files\MySQL\MySQL Server 41\bin

（或者直接将windows的环境变量path中添加该目录）

2，导出数据库：mysqldump -u 用户名 -p 数据库名 >； 导出的文件名

如我输入的命令行：mysqldump -u root -p news > newssql （输入后会让你输入进入MySQL的密码）

（如果导出单张表的话在数据库名后面输入表名即可）

3、会看到文件newssql自动生成到bin文件下

命令行导入数据库：

1，将要导入的sql文件移至bin文件下，这样的路径比较方便

2，同上面导出的第1步

3，进入MySQL:mysql -u 用户名 -p

如我输入的命令行：mysql -u root -p （输入同样后会让你输入MySQL的密码）

4，在MySQL-Front中新建你要建的数据库，这时是空数据库，如新建一个名为news的目标数据库

5，输入：mysql>use 目标数据库名

如我输入的命令行：mysql>use news;

6，导入文件：mysql>source 导入的文件名；

如我输入的命令行：mysql>source newssql;
4 Hive几种数据导入方式和动态分区,多表插入
常用的的有三种：1从本地文件系统中导入数据到Hive表；2从HDFS上导入数据到Hive表；3在创建表的时候通过从别的表中查询出相应的记录并插入到所创建的表中。

Hive配置：HDFS中Hive数据文件存放目录（启动hive后HDFS自动创建）：HDFS: /usr/hive/warehousehadoop fs -mkdir /usr/hive/warehouse 命令创立本地数据存放目录：本地：/home/santiago/data/hive一从本地文件系统中导入数据到Hive表1在hive中建表hive> show databases;OKdefaultTime taken: 1706 seconds, Fetched: 1 row(s)hive> create table guo_test(Name string,String string)> row format delimited> fields terminated by ','> stored as textfile;hive> show tables;OKguo_testTime taken: 0024 seconds, Fetched: 1 row(s)1234567891011122在本地文件建立同类型数据表santi@hdp:~/data/hive$ lshive_testtxtsanti@hdp:~/data/hive$ cat hive_testtxtsanti,you are a zhazha12343导入数据并测试hive>load data local inpath '/home/santi/data/hive/hive_testtxt' into table guo_test;hive> select from guo_test;hive>dfs -ls /usr/hive/warehouse/guo_test;#hadoop fs -ls /usr/hive/warehouseFound 1 itemsdrwxrwxr-x - santiago supergroup 0 2017-01-14 21:13/usr/hive/warehouse/guo_test12345678发现hive-site,xml设置的HDFS文件存储位置中多了guo_test这个文件夹#hadoop fs -ls /usr/hive/warehouse/guo_testFound 1 items-rwxrwxr-x 1 santiago supergroup 24 2017-01-14 21:13/usr/hive/warehouse/guo_test/hive_testtxthive> select from guo_test;OKsanti you are a zhazha12345678在该文件夹中找到了所写入hive数据仓库的文件。[注]本地数据写入成功，但是从本地将数据导入到Hive表的过程中，其实是先将数据临时复制到HDFS的一个目录下（典型的情况是复制到上传用户的HDFS home目录下，比如/home/santi/），然后再将数据从临时目录下移动到对应的Hive表的数据目录里面（临时目录不保留数据）。

二从HDFS文件系统中导入数据到Hive表1在HDFS文件系统上建立数据文件hdfs上没有vim命令，则需要将本地数据文件手动传入到HDFS上/data/hive# vim data_HDtoHive/data/hive# cat data_HDtoHivedata from, HDFS to Hive #hadoop fs -put /home/santi/data/hive/data_HDtoHive /usr/data/input数据传入# hadoop fs -ls /usr/data/input123452导入数据hive> load data inpath '/usr/data/input/data_HDtoHive' into table guo_test;hive> select from guo_test;OKdata from HDFS to Hivesanti you are a zhazhaTime taken: 0172 seconds, Fetched: 2 row(s)123456数据写入成功数据存hive配置的数据存储位置中。[注]从本地导入数据语句为hive>load data local inpath '/home/santi/data/hive/hive_testtxt' into table guo_test；从HDFS中导入数据的语句为hive> load data inpath '/usr/data/input/data_HDtoHive' into table guo_test；差距在local这个命令这里。

而从HDFS系统上导入到Hive表的时候，数据转移。HDFS系统上查找不到相关文件。

三从HIVE表选取数据插入新的HIVE表命令为create table 表名 as selecr xxx from 表名。hive> create table hivedata_test1> as> select name> from guo_test;hive> select from hivedata_test1;OKdata fromsantiTime taken: 0116 seconds, Fetched: 2 row(s)123456789[注]hive是分区表有稍微区别在Hive中，表的每一个分区对应表下的相应目录，所有分区的数据都是存储在对应的目录中。

比表有a和b两个分区，则对应a=xxx,b=xx对应表的目录为/user/hive/warehouse/a=xxxuser/hive/warehouse/b=xx，所有属于这个分区的数据都存放在这个目录中。hive> create table hivedata_test2(> Name string)> partitioned by> (String string)> ROW FORMAT DELIMITED> FIELDS TERMINATED BY ','> STORED AS TEXTFILE;hive> insert into table hivedata_test2> partition(String='best')> select Name> from guo_test;hive> select from hivedata_test2;OKdata from bestsanti bestTime taken: 1549 seconds, Fetched: 2 row(s)# hadoop fs -ls /usr/hive/warehouse/hivedata_test2Found 1 itemsdrwxrwxr-x -santiago supergroup 0 2017-02-14 17:40/usr/hive/warehouse/hivedata_test2/string=best。
5 hive 怎么添加 表注释语法
要添加注释，只需要用单引号'作为注释文字的开头。注释符告诉Visual Basic，忽略这个符号后面的内容，这些内容就是代码段中的注释部分，在代码编辑器中以绿色字符显示。

注释可以和语句在同一行，写在语句的后面，也可占据一整行。

例如：

'在文本框中放欢迎词。

Private Sub mand1_Click()

Text1 Text="Hello" '把文本框Text1的属性设置为Hello。

End Sub

注意，不能在同一行上把注释接在续行符后面。

HDFS存元数据地址
/home/softwares/hadoop-261/dfs/data/current/BP-1499552349-19216831160-1574884211184/current/finalized/subdir0/subdir0

导入本地数据：load data local inpath '/path/' into table XX; = hadoop fs -put filepath /hadooppath/XX

HDFS上的数据交互：load data inpath 'hadooppath' into table XX; 修改namendoe元数据信息

分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。
Hive 中的分区就是分目录，把一个大的数据集根据业务需要（时间）分割成小的数据集。
在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。
谓词下推：先走个数据过滤（WHERE）

语法

分区 将month作为一个单独的列
加载数据到分区表

hdfs直接上传
hadoop fs -put /home/data/depttxt /user/hive/warehouse/dept_partition/month=2019-08

增加分区：分区与分区之间是空格
alter table XX add partition(month='2019-08');

alter table dept_partition add partition(month='2019-08') partition(month='2018-06'); //多个也ok

查询，过滤数据使用

1上传数据后修复

2上传数据后添加分区(常用)

3load

replace会替换表中所有列,需写全字段

load data:表示加载数据
local:表示从本地加载数据到 hive 表；否则从HDFS 加载数据到 hive 表
inpath:表示加载数据的路径
overwrite:表示覆盖表中已有数据，否则表示追加
into table:表示加载到哪张表
student:表示具体的表
partition:表示上传到指定分区

insert：将表中数据导出到本地或者hdfs
local为本地路径，不加local为hdfs

hadoop命令导出到本地

export 与 import

清除表中数据truncate
Truncate 只能删除管理表，不能删除外部表中数据

---