目录

1、Hive命令行界面

1.1、选项列表

1.2、变量和属性

1.2.1、Hive中变量和属性命名空间

1.2.2、用户自定义变量

1.2.3、.hiverc文件

1.3、一次使用的命令

1.4、从文件中执行Hive查询

1.5、操作命令历史

1.6、Hive内部可直接使用dfs命令

2、数据类型和文件格式

2.1、基本数据类型

2.2、集合数据类型

2.3、基本数据类型的类型类型

2.4、字段分隔符

3、DDL数据定义

3.1、数据库操作

3.1.1、创建数据库

3.1.2、查看数据库信息

3.1.3、查看数据库

3.1.4、切换工作数据库

3.1.5、修改数据库键值对属性

3.1.6、删库

3.2、表操作

3.2.1、建表语法

3.2.2、内部表与外部表

3.2.3、分区

·静态分区

·​​​​​​动态分区

3.2.4、分桶

3.2.5、分区后分桶

3.2.6、修改表

4、DML数据操作

4.1、数据导入

4.1.1、外部数据加载

4.1.2、集合数据类型表的导入

4.1.3、查询语句insert导入

4.1.4、查询语句中建表as select并加载数据

4.1.5、import数据到指定表中

4.2、数据导出

4.2.1、insert导出

4.2.2、Hadoop命令导出到本地

4.2.3、Hive Shell 命令导出

4.2.4、Export导出到HDFS上

1、Hive命令行界面

1.1、选项列表

$ hive --help --service cliusage: hive-d,--define <key=value>Variable subsitution to apply to hivecommands. e.g. -d A=B or --define A=B--database <databasename>Specify the database to use-e <quoted-query-string> SQL from command line-f <filename>SQL from files-H,--help Print help information--hiveconf <property=value> Use value for given property--hivevar <key=value> Variable subsitution to apply to hivecommands. e.g. --hivevar A=B-i <filename>Initialization SQL file-S,--silent Silent mode in interactive shell-v,--verbose Verbose mode (echo executed SQL to theconsole)

1.2、变量和属性

1.2.1、Hive中变量和属性命名空间

hivevar：用户自定义变量，可读可写hiveconf：Hive相关的配置属性，可读可写system：Java定义的配置属性，可读可写env：shell环境定义的环境变量，只可读

1.2.2、用户自定义变量

在命令行外部定义用户自定义变量

$ hive --define key=value;

--define等价于-d等价于--hivevar；用户自定义的变量仅对当前hive会话有效，若退出命令行后再次进入会失效

通过set命令查看变量具体信息

system:mand=org.apache.hadoop.util.RunJar /home/jinge/apps/hive/lib/hive-cli-1.2.1.jar org.apache.hadoop.hive.cli.CliDriver --define key=value

在hive命令行中定义，

最好声明所在命名空间(不然虽然可以通过set key;查看到值，但不能使用)

hive> set hivevar:key=value;

但是在命令行下定义的变量无法通过set -v查看到

查看变量值

hive> set key;

修改变量值

hive> set key=new_value;

查看所有变量

hive> set -v;

若不加-v标记，set命令会打印出4个命名空间中的所有变量；使用-v标记还会打印出Hadoop中所定义的所有属性

使用变量

hive> create table ${key}(id int);

此时创建的表表名为value

1.2.3、.hiverc文件

Hive在启动时会自动的HOME目录下寻找名为.hiverc的文件，而且会自动执行这个文件中的命令。对于需要频繁执行的命令可以将命令写在这个文件下。

-- 显示当前所在数据库set hive.cli.print.current.db=true;-- 显示列名set hive.cli.print.header=true;-- 列名中不显示表名set hive.resultset.use.unique.column.names=false;

1.3、一次使用的命令

在命令行外部通过-e选项，可以不进入CLI运行HQL指令

$ hive -e 'desc t1';OKcol_name data_type commentid intTime taken: 1.117 seconds, Fetched: 1 row(s)

-S选项可以去掉OK和Time Taken等行信息

$ hive -S -e 'desc t1';col_name data_type commentid int

1.4、从文件中执行Hive查询

-f选项可以执行指定文件中的查询语句，通常查询文件以.q或.hql为后缀

$ echo 'desc t1' >> desc_t1.hql;$ hive -S -f ./desc_t1.hqlcol_name data_type commentid int

hive脚本使用"--"进行注释

1.5、操作命令历史

位于$HOME/.hivehistory下

1.6、Hive内部可直接使用dfs命令

hive (default)> dfs -ls /;

等价于在bash中执行hdfs dfs命令，但更加高效，因为bash中每次都会启动一个新的JVM实例，而在Hive中会在同一个进程中执行这些命令

2、数据类型和文件格式

2.1、基本数据类型

2.2、集合数据类型

2.3、基本数据类型的类型类型

Hive的基本数据类型是可以进行隐式转换的，类似于Java的类型转换

1）隐式转换：任何整数类型可以隐式地转换为一个范围更广的类型，比如tinyint可隐式转化为int

2）所有整数类型、float和string类型都可以隐式地转换成double

3）tinyint、smallint和int都可转化为float

4）boolean不可以转化为其他类型

使用cast操作显示进行数据类型转化

cast('1' as int) 将'1'字符串转化为整数cast('a' as int) 强制转化失败，表达式返回null

2.4、字段分隔符

\n：对于文本文件来说，可使用换行符分割记录^A（Ctrl+A）：用于分割字段，在建表语句中可使用八进制编码\001表示^B：用于分割ARRAY、STRUCT元素或MAP中的键值对；\002^C：用于分割MAP中的键值对；\003

hive> create table ...> ROW FORMAT DELEMITED> FIELDS TERMINATED BY '\001'> COLLECTION ITEMS TERMINATED BY '\002'> MAP KEYS TERMINATED BY '\003'> LINES TERMINATED BY '\N'

3、DDL数据定义

3.1、数据库操作

3.1.1、创建数据库

1）创建一个数据库，在hdfs上默认存储路径为/user/hive/warehouse/*.dbhive (default)> create database db1;2）避免要创建的数据库以存在导致出错，标准写法hive (default)> create database if not exists db2;3）指定数据库在hdfs上存放的位置hive (default)> create database db3 location '/mydironhdfs/db3.db';4）添加注释信息hive (default)> create database db4 comment "it's db4";5）添加数据库属性hive (default)> create database db5 with dbproperties('author'='jod','date'='-07-15');

若未显式指定数据库，将会使用默认的default数据库

默认情况在创建数据库会在hdfs设置路径(/user/hive/warehouse)下生成目录，表会在对应的数据库目录下，而default数据库的表会在与其他数据库同级目录下

​

​

3.1.2、查看数据库信息

1）显示数据库基本信息hive (default)> desc database db4;db_name comment location owner_nameowner_typeparametersdb4it's db4 hdfs://mycluster/user/hive/warehouse/db4.dbjinge USER2）显示扩展信息，数据库属性hive (default)> desc database extended db5;db_name comment location owner_nameowner_typeparametersdb5 hdfs://mycluster/user/hive/warehouse/db5.dbjinge USER {date=-07-15, author=jod}

3.1.3、查看数据库

1）显示所有数据库hive (default)> show databases;database_namedb1db2db3db4db5default2）可使用正则表达式显示hive (default)> show databases like '*1';database_namedb1

3.1.4、切换工作数据库

hive (default)> use db1;hive (db1)>

3.1.5、修改数据库键值对属性

1）增加属性，并修改原有属性值hive (default)> alter database db5 set dbproperties('edited-by'='jim','date'='-07-16');hive (default)> desc database extended db5;db_name comment location owner_nameowner_typeparametersdb5 hdfs://mycluster/user/hive/warehouse/db5.dbjinge USER {date=-07-16, author=jod, edited-by=jim}

3.1.6、删库

1）若库内有表不允许删库hive (default)> drop database if exists db5; 2）级联删除，会将该数据库内表一并删除hive (default)> drop database db4 cascade;

3.2、表操作

3.2.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]

部分字段解释

EXTERNAL：创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）PARTITIONED BY：表示创建分区表，在HDFS层面上为创建子目录CLUSTERED BY：表示创建分桶表，类似于MapReduce中map端的分区，需写到PARTITIONED BY之后ROW FORMAT：用户在建表的时候可以自定义SerDe或者使用自带的SerDe。SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。可见2.4字段分隔符STORED AS：表示指定存储文件类型。默认为TEXTFILE(文本)。

3.2.2、内部表与外部表

内部表也称管理表或托管表，内部表由Hive控制着数据的生命周期，默认情况下这些表数据存储在hive.meta.warehouse.dir下，当删除一个内部表时对应的元数据和数据会被删除。管理表不适合和其他工具共享数据。表类型为MANAGED_TABLE

外部表适合与其他工作(可能需要使用多种工具来分析处理数据)共享数据，可理解为指向数据，不需要将数据移动至仓库内，Hive对外部表是没有所有权的，所以删除外部表是不会删除数据的，仅删除元数据。表类型为EXTERNAL_TABLE

Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变

创建外部表

注意：只要使用了external关键字则为外部表；若不使用external关键字时指定内部表位置，创建的为内部表；若指定外部表位置则为外部表

1）创建内部表hive (db1)> create table student(id int,name string);2）为内部表创建外部表，location指向原表目录hive (db1)> create external table student_ex like student location '/user/hive/warehouse/db1.db/student';3）操作外部表等于操作原表hive (db1)> insert into table student_ex values(1.'lisi')

​

删除外部表

hive (db1)> drop table student_ex;删除外部表后原表的数据仍存在hive (db1)> select * from student;idname1 lisi

​

内部表与外部表的互相转换

1）查询原表的类型hive (db1)> desc formatted student;Table Type: MANAGED_TABLE2）修改内部表为外部表.注意('EXTERNAL'='TRUE')区分大小写hive (db1)> alter table student set tblproperties('EXTERNAL'='TRUE');3）查询表类型hive (db1)> desc formatted student;Table Type: EXTERNAL_TABLE4）外部表->内部表.同样区属性分大小写hive (db1)> alter table student_ex set tblproperties('EXTERNAL'='FALSE');

3.2.3、分区

Hive中select语句会扫描全表，分区将将一张大表按照分区规则拆分成多个小表，小表在对应分区目录下，这样可以缩小扫描范围。分区在hdfs层面上是创建子目录的过程。

静态分区

1）建表时指定分区，分区名任意

按照年份、国家分区将日志表；先按year分区，后按country分区hive (db1)> create table logs(id int,context string,time string,city string) > partitioned by (year string,country string) row format delimited > fields terminated by ',';

2）加载数据时手动指定分区

需手动指明分区hive (db1)> load data local inpath '/home/jinge/.csv' > into table logs partition (year='',country='china');hive (db1)> load data local inpath '/home/jinge/.csv' > into table logs partition (year='',country='china');

分区实际上是在hdfs上进行创建子目录存放对应数据

hive (db1)> dfs -lsr /user/hive/warehouse/db1.db/logs;drwxrwxrwx - jinge supergroup0 -07-15 22:34 /user/hive/warehouse/db1.db/logs/year=drwxrwxrwx - jinge supergroup0 -07-15 22:34 /user/hive/warehouse/db1.db/logs/year=/country=china-rwxrwxrwx 3 jinge supergroup 107 -07-15 22:34 /user/hive/warehouse/db1.db/logs/year=/country=china/.csvdrwxrwxrwx - jinge supergroup0 -07-15 22:34 /user/hive/warehouse/db1.db/logs/year=drwxrwxrwx - jinge supergroup0 -07-15 22:34 /user/hive/warehouse/db1.db/logs/year=/country=china-rwxrwxrwx 3 jinge supergroup 107 -07-15 22:34 /user/hive/warehouse/db1.db/logs/year=/country=china/.csv

并且表结构中存在分区列(伪列)，所以分区列可以用来查询

hive (db1)> desc logs;OKcol_name data_type commentid intcontext stringtimestringcitystringyearstringcountry string# Partition Information# col_name data_typecommentyearstringcountry string

3）可以使用分区列进行查询

hive (db1)> select * from logs where year=;idcontext time city year country1 dflksajflkshfdlksdf-07-15cc china2 asjflkafjlkajflkaf-07-15cc china3 jkldjflksfdlksfddsf-07-15cc china

4）查看分区

hive (db1)> show partitions logs;partitionyear=/country=chinayear=/country=china

5）增加分区，多个分区用空格分隔

hive (db1)> alter table logs add partition (year='',country='usa');hive (db1)> show partitions logs;partitionyear=/country=usayear=/country=chinayear=/country=china

6)删除多个分区，用逗号分隔

hive (db1)> alter table logs drop partition (year='',country='china'),(year='',country='china');hive (db1)> show partitions logs;partitionyear=/country=usayear=/country=chinayear=/country=china

​​​​​​动态分区

动态分区实现了在插入(insert into)时能够自动进行分区，在加载数据时需要指明分区规则

比如有如下类型的股票数据

​

一般需要向动态分区表中载入数据，可以使用基表插入，并按照基表中ymd字段中的年月分区

hive (db1)> dfs -put /home/jinge/stocks.csv /data/stocks;1）创建并载入基表hive (db1)> create external table if not exists stocks > (exchange1 string,symbol string,ymd string,price_open float,price_high> float,price_low float,price_close float,volume int,price_adj_close float) > row format delimited fields terminated by ',' location '/data/stocks'; 2）创建分区表按照年月分区hive (db1)> create table stocks_partitions(exchange1 string,symbol string,> ymd string,price_open float,price_high float,price_low float,price_close > float,volume int,price_adj_close float) partitioned by (year string,month string) >row format delimited fields terminated by ','; 3）年月位于ymd字段中，动态分区插入，year和month作为伪列hive (db1)> insert overwrite table stocks_partitions partition(year,month) > select exchange1,symbol,ymd,price_open,price_high,price_low,> price_close,volume,price_adj_close,> split(ymd,'/')[0] as year,split(ymd,'/')[1] as month from stocks;

注意：动态分区需要设置一些hive变量

设置动态分区模式为不严格hive> set hive.exec.dynamic.partition.mode=nonstrict;设置最大动态分区数hive> set hive.exec.max.dynamic.partitions=100000;设置每个分区最大子分区数hive> set hive.exec.max.dynamic.partitions.pernode=100000;

​

​

3.2.4、分桶

分桶实质也是将大表拆分成多个小表，而会根据某字段的hashcode÷分桶数取余数得到桶号，类似于MapReduce中map端的HashPartition分区器，实际上桶的数量决定了底层Reducer的数量。

分桶提升了查询效率，查询先计算桶号再扫描对应小表；而分桶主要是为了取样，比如有10000条数据，想要获取1000条左右采样，就可以根据某个字段(不同值多一些)分为10个桶，每个桶内是相当于是随机的1000条左右记录。

分桶在hdfs上实际上是将大文件(大表)按照桶号拆分成小文件(小表，表名中包含桶号信息)

在分桶前需要设置属性采用分桶

set hive.enforce.bucketing=true;

建立分通表 clustered by( field ) into n buckets

按照ymd字段分为5个桶hive (db1)> create table stocks_buckets(exchange1 string,symbol string,ymd string,> price_open float,price_high float,price_low float,price_close float,> volume int,price_adj_close float) > clustered by (ymd) into 5 buckets > row format delimited fields terminated by ',';

载入数据，仓库下会多出分完桶的小文件

hive (db1)> insert into stocks_buckets select * from stocks;5个bucket对应5个reducerHadoop job information for Stage-1: number of mappers: 3; number of reducers: 5

适合的分桶字段可以避免数据倾斜

从桶中取数据(采样)

一共有48761条数据，采样取1/5左右hive (db1)> select * from stocks_buckets tablesample(bucket 1 out of 5 on ymd);Time taken: 0.069 seconds, Fetched: 9286 row(s)

3.2.5、分区后分桶

在建表时需注意先分区后分桶

1)按照年月分区，并以开市价分为3个桶hive (db1)> create table stocks_parket(exchange1 string,symbol string,ymd string,> price_open float,price_high float,price_low float,price_close float,> volume int,price_adj_close float) > partitioned by (year string,month string) > clustered by (price_open) into 3 buckets > row format delimited fields terminated by ',';2)载入数据时指定分区列hive (db1)> insert overwrite table stocks_parket partition(year,month) > select exchange1,symbol,ymd,price_open,price_high,price_low,price_close,> volume,price_adj_close,split(ymd,'/')[0] as year,split(ymd,'/')[1] as month from stocks;

插入时先进行分区后计算桶号分桶

3.2.6、修改表

1)重命名表alter table tableA rename to tableB2)更新列ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]3）增加和替换列，替换即改列名，数据不变ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)4)删除表DROP TABLE table_name

4、DML数据操作

4.1、数据导入

4.1.1、外部数据加载

加载本地文件(实际上是将该文件上传至hdfs对应目录下)

当前文件如下：

1,zhangsantwo,lisi,wangwu4,zhaoliu,abc

hive (db1)> load data local inpath '/home/jinge/1.csv' overwrite into table student;hive (db1)> select * from student;idname1 zhangsanNULL lisi 不符合数据类型的为nullNULL wangwu为空的部分为null4 zhaoliu多了的不加载

加载hdfs文件(实际上是将改文件移动至对应目录下)

hive (db1)> dfs -put /home/jinge/1.csv /; 上传至根目录下hive (db1)> load data inpath '/1.csv' into table student; 不重写表hive (db1)> select * from student;idname1 zhangsanNULL lisiNULL wangwu4 zhaoliu1 zhangsanNULL lisiNULL wangwu4 zhaoliuhive (db1)> dfs -ls /;Found 4 itemsdrwxr-xr-x - jinge supergroup0 -07-14 08:59 /hbasedrwxrwxrwx - jinge supergroup0 -07-15 21:05 /mydironhdfsdrwxrwxrwx - jinge supergroup0 -07-14 14:11 /tmpdrwxrwxrwx - jinge supergroup0 -07-14 13:57 /userhive (db1)> dfs -ls /user/hive/warehouse/db1.db/student; 移动至对应表目录下Found 2 items-rwxrwxrwx 3 jinge supergroup 42 -07-15 21:38 /user/hive/warehouse/db1.db/student/1.csv-rwxrwxrwx 3 jinge supergroup 42 -07-15 21:41 /user/hive/warehouse/db1.db/student/1_copy_1.csv

4.1.2、集合数据类型表的导入

array 通过索引访问

wordcount案例

1）原文件内容

hi hellonice goodhi nicehi

2）建表并载入数据

1）设置array的分隔符为空格hive (db1)> create table wordcount(word array<string>) row format delimited collection items terminated by ' ';2）载入数据hive (db1)> load data local inpath '/home/jinge/f1.txt' into table wordcount;hive (db1)> select * from wordcount;word["hi","hello"]["nice","good"]["hi","nice"]["hi"]3）查看每一行的第二个单词hive (db1)> select word[1] from wordcount;_c0hellogoodniceNULL

3）炸裂explode，可以将array或map结构拆分成多行

hive (db1)> select w from wordcount lateral view explode(word) word as w;whihellonicegoodhinicehihive (db1)> select explode(word) from wordcount;colhihellonicegoodhinicehi

4）统计频率

hive (db1)> select w,count(1) as c from wordcount lateral view explode(word) word as w group by w order by c desc;w chi3nice 2hello 1good 1

map：通过键访问

字段间使用','分割，kv之间使用':'分割，map元素间使用'-分割'hive (db1)> create table grade(> id int,> name string,> grades map<string,int>> )> row format delimited> fields terminated by ','> collection items terminated by '-'> map keys terminated by ':'> lines terminated by '\n';

文件内容

1,mike,eng:80-math:602,jod,eng:90-math:95

1）加载数据hive (db1)> load data local inpath '/home/jinge/f2.txt' overwrite into table grade;2）查询全表hive (db1)> select * from grade;idname grades1 mike {"eng":80,"math":60}2 jod{"eng":90,"math":95}3）查询所有人英语成绩hive (db1)> select grades['eng'] from grade;_c080903）将map分行显示hive (db1)> select explode(grades) as (subject,grade) from grade;subject gradeeng80math 60eng90math 954）聚合函数显示hive (db1)> select id,name,subject,grade from grade lateral view explode(grades) grades as subject,grade;idname subject grade1 mike eng801 mike math 602 jodeng902 jodmath 95

struct：通过.属性名访问

struct中一组数据可以由不同的数据类型，是增强版的map

hive (db1)> create table city(> id int,> address struct<city:string,province:string,area:string,code:int>> )> row format delimited> fields terminated by ','> collection items terminated by '-'> lines terminated by '\n';

文件内容

1,大连-辽宁-东北部-1160002,青岛-山东-东部-2110003,大连-辽宁-东北部-116000

hive (db1)> load data local inpath '/home/jinge/f3.txt' into table city;hive (db1)> select address.city,address.code from city;city code大连 116000青岛 211000大连 116000hive (db1)> select * from city where address.code=211000;idaddress2 {"city":"青岛","province":"山东","area":"东部","code":211000}

注意：含有集合类型字段的表不允许使用insert into数据插入数据，只能load data

4.1.3、查询语句insert导入

1）创建分区表hive (db1)> create table student(id int, name string) partitioned by (month string) row format delimited fields terminated by '\t';2）插入数据hive (db1)> insert into table student partition(month='7') values(1,'zhangsan');hive (db1)> insert into table student partition(month='8') values(2,'lisi');3）基本模式插入（根据单张表查询结果进行插入）hive (db1)> insert overwrite table student partition(month='8') > select id, name from student where month='8';4）多插入模式（根据多张表查询结果进行插入）hive (db1)> from student> insert overwrite table student partition(month='7')> select id, name where month='7'> insert overwrite table student partition(month='8')> select id, name where month='8';

4.1.4、查询语句中建表as select并加载数据

create table t1 as select * from existing_t2;

4.1.5、import数据到指定表中

先由export导出数据后，在将数据导入

import table t1 from 'inpath';

4.2、数据导出

4.2.1、insert导出

1）将查询的结果导出到本地,overwrite会重写本地目录，谨慎使用hive (db1)> insert overwrite local directory '/tmp/output' select * from student;2）将查询的结果格式化导出到本地hive (db1)> insert local directory '/tmp/output'> ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'> select * from student;3)将查询的结果导出到HDFS上hive (db1)> insert directory '/output'> ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' > select * from student;

4.2.2、Hadoop命令导出到本地

hive (db1)> dfs -get /user/hive/warehouse/student /tmp/output/student1.txt;

4.2.3、Hive Shell 命令导出

hive -f/-e 执行语句或者脚本 > file

$ hive -e 'select * from db1.student;' > /tmp/output/student2.txt;

4.2.4、Export导出到HDFS上

hive (db1)> export table student to '/output/student';

Hive：命令行界面 数据类型 DDL数据定义(数据库及表操作/分区分桶) DML数据操作(数据导入导出)