目录
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中的键值对;\003hive> 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数据操作(数据导入导出)