hive3.0.0操作集锦(上)

建议配合官方文档学习。

官方文档：https://cwiki.apache.org/confluence/display/Hive/LanguageManual

作者大纲是<<hive编程指南>>，这本书有点老，很多最新的更新都没有[笑哭]，不过这些都经过测试，写出来的都能用。

注：易于操作的命令：

可以在$HIVE_HOME/conf目录下创建.hiverc文件，把需要的配置写进去，Hive在开启的时候会加载这个文件中的配置。

 

set hiveconf:hive.cli.print.current.db=true;显示当前使用的数据库

use database_name;使用数据库

show databases;查看所有的数据库。show databases like 'defa*';模糊查找->default

show tables;查看当前数据库内的所有表。show tables like 'tes*';模糊查找->test

describe database extended database_name;查看数据库详细信息

describe formatted table_name;查看表的详细信息

show partitions table_name;查看表所有分区

describe formatted table_name partition(country='US',state='IL');查看表分区的具体信息

还有2个：在hive cli界面想知道本地目录相关的细节，可以加！ ，如:! ls  ;查看进入hive cli界面目录的文件；

                可以使用hadoop相关命令，如：dfs -ls /;查看hdfs目录的文件；

 

一丶hive cli命令

     1.启动/关闭mysql服务：service mysqld(有些是mysql) start/stop

     2.启动hive shell命令：hive  [笑哭]

     3.数据定义

          (1) set+hivevar/hiveconf/env/空+值:set可以进行hivevar(值变量)，hiveconf(配置变量)，env(环境)，空

              set hivevar:bar=ttt; 创建的变量可以被其它操作使用。如：create table test(${bar} string);describe test;显示：ttt   string.

              set hiveconf:hive.cli.print.current.db=true;修改相关配置的值为true，在hive光标处显示当前数据库名。也可以自己创建新变量。如：set hiveconf:y=5;set hiveconf:y;显示y=5.

              set env:HOME;显示当前用户家目录地址。env(环境变量)无法赋值，所以无法创建和修改。

              空：set bar;显示bar=ttt。 set bar=ppp; set bar;显示bar=ppp。set hivevar:bar;显示hivebar:bar=ttt。set hiveconf:bar;显示hiveconf:bar=ppp。不加任何中间命令，优先hiveconf操作，如hiveconf无相关变量，显示hivevar变量的值。

          (2) source+HSQL文件位置（可以相对路径），没进入hive可以用 hive -f +文件位置执行

             从文件执行进入hive后的命令

          (3) 建表语句

示例(创建一个外部分区表)：

create external table if not exists employee(
name string comment "employee name",                                     --表元素和提示
salary float comment "employee salary",
subordinates array<string> comment "Names of subordinates",
deductions map<string,float>,
address struct<street:string,city:string,state:string,zip:int> comment "home address")
comment 'description of the table'                                       --表提示
partitioned by (country string comment 'this is a comment',state string comment 'this is two comment')                                                            --分区元素和提示
row format delimited                                                     --数据格式
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
location 'home/local/hive-metastore-dir/warehouse/mydb.db/employee'      --表位置
tblproperties ('creator'='sorybean','date'='2019-4-14')                  --配置属性

对应数据格式：

John Doe,100000.0,Marry Smith-Todd Jones,Federal Taxes:0.2-State Taxes:0.05-Insurance:0.1,1 Michigan Ave.-Chicago-CL-60600

删表语句：drop table table_name;

       (4) 数据库创建语句：

示例：
create database if not exists mydb
comment 'holds all mydb '                                       --数据库提示
location "home/local/hive-metastore-dir/warehouse/mydb.db"      --数据库位置
with dbproperties ('creator'='sorybean',"date"="2019-4-14")     --配置属性和值

删数据库语句：drop database database_name;

        (5) 修改表：

           表重命名：alter table table_name rename to new_name;例：alter table human rename to employee;

           增加表分区：alter table employee add if not exists partition(country='china') location '/user/data/china'  partition(country='us') location 'user/data/us'   .......;

           修改分区数据地址：alter table employee partition(country='china') set location 'user/data/beijing';不会移动和删除旧数据;

           删除分区：alter table employee drop if exists partition(country='us');外部分区表删除分区不删除数据。管理分区表使用alter table table_name add partition(country='china') location 'url';增加的分区删除分区会删除分区地址目录,删除表当然也会删除数据。

           修改列信息：

alter table employee change column 
salary(column_name) salary_employee(new_name) double 
comment "employee's salary" after name;

 即使只改字段名，字段类型也必须要再写一遍。同样改字段类型，字段名也要写，而且字段类型只可以向下兼容或类型不变，如旧是int,新的类型范围只可以比它大，如float,double,string，否则报错.最后的after+column_name将修改的列放置在column_name之后，也可以用first让修改的列放置在第一个。

           增加列：alter table employee add columns(column_name string，column_name long.......);增加的列将会被放置在最后，如需改变位置，可以用修改列信息完成。

           删除或者替换列：alter table employee replace column(name string,salary float,.......);例：create table test(s string,t int, g string);alter table test replace column(name string,salary string)，结果：test(name string,salary string)。这个更像是从第一个column_name开始修改列，第一个是string类型，替换的类型需遵从列修改向下兼容或不变的原则,为string，后面是int类型，修改为string，最后没有匹配的column_name，被删除。

           修改表属性：alter table employee set tblproperties('creator'='sorybean','date'='2019-4-22');可以增加新属性或者修改已存在的属性，但无法删除属性。可以用describe formatted table_name;显示表的详细信息，查看#Table Parameters下的表属性。

 

     4.数据操作

            (1) 向表中加载数据：load data local inpath './arraydata' [overwrite(加这个会删除分区内之前的数据文件)] into(只这个就只会追加数据文件) table employee(后面的非分区表不加) partition(country='US',state='IL');这条语句可以向管理分区表和外部分区表加载数据，文件路径一般为目录，目录中可以（不建议）存在其它目录（而不是数据文件），数据加载时会略过，加载后文件在表目录下。但需要注意的是，删除外部表并不会删除在hdfs上的数据，外部表仍然存在，如果创建和删除的外部表名相同的管理表，不会报错（因为一般创建的分区表，删除分区表不删除hdfs中的表目录），文件还是之前的分区表创建的，select查询也什么数据都没有，甚至创建之前存在的分区都不会报错，但当你创建之前存在的分区后查询，就会发现多了一些你没有加载过的数据，而这些数据属于它的上一个主人--那个外部分区表。

            (2) 通过查询语句向表中插入数据：

insert into(or overwrite) table employee 
partition (country='US',state='IL') 
select * from default.employee 
where country='US' and state='IL';

没有分区会自动创建分区，如果字段分隔符不同，也可以插入。还有一种优化的写法：

from default.employee 
insert overwrite table employee 
partition(country='US',state='IL')
select * where country='US' and state='IL'
insert overwrite table employee 
partition(country='US',state='CL')
select * where country='US' and state='CL'
......................................

   这样就只用扫描default.employee表一遍，之前那个执行几次扫描几遍。

           (3) 动态分区插入：不过要是有很多分区的话，之前上面那种方法也需要写很多次，难免不会出错，也不宜于寻找。

动态分区：

insert overwrite table employee
partition(country,state)
select *(所有行，如只需部分行，手打出那些columns),country,state(最后两个用来匹配那两个动态分区键) from default.employee;

OR

insert overwrite table employee
partition(country='US',state)
select *,state from default.employee
where country='US';

第一种都是动态分区键：

需设置两个hive.exec.dynamic.partition =true;

                  hive.exec.dynamic.partition.mode=nonstrict;

第二种一个静态键，一个动态键。只需要设置第一个配置即可。

第一种有多少个不同的country值就会在表目录下产生多少个文件，有多少个不同的state值，表目录下每个文件内就会产生多少个文件。之所以需要多一个配置，设置为非严格模式，这样很容易产生大量的分区，误操作后可能想删表吧。

第二种因为country确定为静态分区键，所以只会在表目录下产生一个名为'US'的文件，当然，有多少个不同的state值就会在这个文件下产生多少个文件。

          (4) 单个语句中创建表并加载数据：

create table ca_employee
as select name,salary,address
from employee
where state='IL';

会按照查询的字段创建表，然后把查询到的数据加载进去，但new_table数据格式为hive默认格式。

           (5) 导出数据：

第一种：找到表目录直接用hadoop命令：hadoop fs -cp source_path target_path; or  hadoop fs -get source_path target_path;复制或导出到本地。

第二种：

insert overwrite local directory '/home/hadoop/hive/test'
row format delimited                                                     --数据格式
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
select name,salary,address
from employee;

加local导出到本地相应的目录，但格式为hive默认格式（^A,^B,^C）。如需不同于hive默认的文件格式，可以创建相应格式和行的临时表，insert into table table_name导入数据进去，找到临时表目录，然后通过上面的hadoop命令直接导出。也可以用上面代码一样设置自己的格式。