Hive官网(HQL)语法手册(英文版):https://cwiki.apache.org/confluence/display/Hive/LanguageManual
Hive的数据存储
1、Hive中所有的数据都存储在 HDFS 中,没有专门的数据存储格式(可支持Text,SequenceFile,ParquetFile,RCFILE等)
2、只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符,Hive 就可以解析数据。
3、Hive 中包含以下数据模型:DB、Table,External Table,Partition,Bucket。
(1):db:在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹
(2):table:在hdfs中表现所属db目录下一个文件夹
(3):external table:外部表, 与table类似,不过其数据存放位置可以在任意指定路径
普通表: 删除表后, hdfs上的文件都删了
External外部表删除后, hdfs上的文件没有删除, 只是把文件删除了
(4): partition:在hdfs中表现为table目录下的子目录
(5):bucket:桶, 在hdfs中表现为同一个表目录下根据hash散列之后的多个文件, 会根据不同的文件把数据放到不同的文件中
hive创建数据库操作:
hive提供database的定义,database的主要作用是提供数据分割的作用,方便数据关闭,命令如下所示: #创建: create (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES] (property_name=value,name=value...) #显示描述信息: describe DATABASE|SCHEMA [extended] database_name。 #删除: DROP DATABASE|SHCEMA [IF EXISTS] database_Name [RESTRICT|CASCADE] #使用: user database_name;
1:Hive创建数据表:
(1)创建表(DDL操作)
建表语法如下所示:
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] ---数据存储的文件夹地址信息。
创建数据表解释说明:
1、 CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。hive中的表可以分为内部表(托管表)和外部表,区别在于,外部表的数据不是有hive进行管理的,也就是说当删除外部表的时候,外部表的数据不会从hdfs中删除。而内部表是由hive进行管理的,在删除表的时候,数据也会删除。一般情况下,我们在创建外部表的时候会将表数据的存储路径定义在hive的数据仓库路径之外。hive创建表主要有三种方式,第一种直接使用create table命令,第二种使用create table ... as select...(会产生数据)。第三种使用create table tablename like exist_tablename命令。
2、 EXTERNAL关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION),Hive 创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径,不对数据的位置做任何改变。在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
3、 LIKE 允许用户复制现有的表结构,但是不复制数据。
4、 ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED,将会使用自带的 SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的 SerDe,Hive通过 SerDe 确定表的具体的列的数据。
5、 STORED AS
SEQUENCEFILE | TEXTFILE | RCFILE
如果文件数据是纯文本,可以使用 STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCEFILE。
6、CLUSTERED BY
对于每一个表(table)或者分区, Hive可以进一步组织成桶,也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希,然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
把表(或者分区)组织成桶(Bucket)有两个理由:
(1)获得更高的查询处理效率。桶为表加上了额外的结构,Hive 在处理有些查询时能利用这个结构。具体而言,连接两个在(包含连接列的)相同列上划分了桶的表,可以使用 Map 端连接 (Map-side join)高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列,如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以,可以大大较少JOIN的数据量。
(2)使取样(sampling)更高效。在处理大规模数据集时,在开发和修改查询的阶段,如果能在数据集的一小部分数据上试运行查询,会带来很多方便。
7、create table命令介绍2
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name] table_name LIKE existing_table_orview_name ---指定要创建的表和已经存在的表或者视图的名称。
[LOCATION hdfs_path] ---数据文件存储的hdfs文件地址信息。
8、CREATE [EXTERNAL] TABLE [IF NOT EXISTS]
[db_Name] table_name ---指定要创建的表名称
...指定partition&bucket等信息,指定数据分割符号。
[AS select_statement] ---导入的数据
CREATE TABLE page_view(viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT 'IP Address of the User') COMMENT 'This is the page view table' PARTITIONED BY(dt STRING, country STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS SEQUENCEFILE;
创建数据表解释如下所示:
# page_view是数据表的名称,注意hive的数据类型和java的数据类型类似,和mysql和oracle等数据库的字段类型不一致。 CREATE TABLE page_view(viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT 'IP Address of the User') #COMMENT描述,可有可无的。 COMMENT 'This is the page view table' # PARTITIONED BY指定表的分区,可以先不管。 PARTITIONED BY(dt STRING, country STRING) # ROW FORMAT DELIMITED代表一行是一条记录,是自己创建的全部字段和文件的字段对应,一行对应一条记录。 ROW FORMAT DELIMITED #FIELDS TERMINATED BY ' 01'代表一行记录中的各个字段以什么隔开,方便创建的数据字段对应文件的一条记录的字段。 FIELDS TERMINATED BY ' 01' # STORED AS SEQUENCEFILE;代表对应的文件类型。最常见的是SEQUENCEFILE(以键值对类型格式存储的)类型。TEXTFILE类型。 STORED AS SEQUENCEFILE;
创建如下所示,之前创建的不符合规范,删除了,然后创建一个标准的,查看一下,最后一个指定类型的,可以不指定,默认就是普通的文本类型的:
Hive将创建的数据类型写到元数据库,可以使用本地Navicat连接虚拟机的mysql查看数据;可是呢,出现下面的情况,百度呗,解决方法一大推,我贴一下子的解决方法:
错误(贴出来,方便被搜索到,哈哈哈哈。):1130 -Host '192.168.3.132' is not allowed to connect to this MySQL server
百度方法很多,但是不是每一个都适合你,我就百度了很多没解决我的问题,所以我还是贴一下我的解决方法:
如何开启MySQL的远程帐号(Navicat远程连接自己的mysql数据库):
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
再执行下面的语句,方可立即生效(修改的权限即时生效)。
mysql> FLUSH PRIVILEGES;
上面的语句表示将 所有的 数据库的所有权限授权给 root 这个用户,允许 root 用户在 192.168.3.132 这个 IP 进行远程登陆,并设置 root 用户的密码为 123456 。
下面逐一分析所有的参数:
(1)all PRIVILEGES 表示赋予所有的权限给指定用户,这里也可以替换为赋予某一具体的权限,例如select,insert,update,delete,create,drop 等,具体权限间用“,”半角逗号分隔。
(2)*.* 表示上面的权限是针对于哪个表的,*指的是所有数据库,后面的 * 表示对于所有的表,由此可以推理出:对于全部数据库的全部表授权为“*.*”,对于某一数据库的全部表授权为“数据库名.*”,对于某一数据库的某一表授权为“数据库名.表名”。
(3)root 表示你要给哪个用户授权,这个用户可以是存在的用户,也可以是不存在的用户。
(4)192.168.3.132 表示允许远程连接的 IP 地址,如果想不限制链接的 IP 则设置为“%”即可。
(5)123456 为用户的密码。
可以使用Navicat工具查看一下自己的创建的数据表(tabs是保存了创建了那些表名):
可以看看自己创建了那些列(在COLUMNS_V2数据表里面):
可以看到有DBS里面保存了哪些数据库:
DBS数据表的DB_LOCATION_URI字段保存了路径:hdfs://ns1/user/hive/warehouse
可以去hdfs里看一眼,里面确实保存着数据库(突然发现有点意思了,只可意会,言传不了了,哈哈哈哈~~~~):
2:创建好数据表,了解了一些基本知识以后,开始插入数据,了解更多的知识:
//create & load(创建好数据表以后导入数据的操作如):
hive> create table tb_order(id int,name string,memory string,price double)
> row format delimited
> fields terminated by ' ';
//从本地导入数据到hive的表中(实质就是将文件上传到hdfs中hive管理目录下)
load data local inpath '/home/hadoop/ip.txt' into table 要导入的表名称;
//从hdfs上导入数据到hive表中(实质就是将文件从原始目录移动到hive管理的目录下)
load data inpath 'hdfs://ns1/aa/bb/data.log' into table 要导入的表名称;//使用select语句来批量插入数据
insert overwrite table tab_ip_seq select * from 要导入的表名称;
自己造一组数据,保存一下,如我的,在/home/hadoop/目录下面phoneorder.data,内容如下所示:
[root@slaver3 hadoop]# vim phoneorder.data
想了一下,由于学习hive,会有很多测试数据,自己创建一个hivetest目录,专一用于存放hive测试数据,如下所示:
10010 小米1 2G 1999 10011 小米2 4G 1999 10012 小米3 4G 1999 10013 小米4 6G 2999 10014 小米5 6G 2999 10015 小米6 8G 2999 10016 小米7 8G 3999
然后开始导入数据(或者使用hadoop的命令将正确格式数据上传到对应的目录),如下所示:
hive> load data local inpath '/home/hadoop/hivetest/phoneorder.data' into table tb_order;
或者[root@slaver3 hivetest]# hadoop fs -put phoneorder2.data /user/hive/warehouse/tb_order
可以去hdfs看到数据已经上传成功了,如下所示,可以看到一些简单信息:
下面可以使用hive的查询语句进行查询操作;
3:Hive的查询语句进行查询操作,统计多少条记录的时候发现很慢很慢,那是启动集群的时候就很慢,最后可以看到一共7条记录,用了一百多秒:
4:external外部表,优点,做数据分析的时候,有的数据是业务系统产生的,或者读或者写这个文件,如果的默认的路径,即在配置文件里面写好了,如果做分析的时候数据表导数据,如果将数据表移动了,,业务系统再读这个文件就不存在了,这个时候使用外部表,外部表不要求数据非到默认的路径下面去,数据可以摆放到任意的hdfs路径下面;
创建外部表的语法:
//external外部表 CREATE EXTERNAL TABLE tab_ip_ext(id int, name string, ip STRING, country STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE LOCATION '/external/user';
//external外部表 //使用关键字EXTERNAL CREATE EXTERNAL TABLE 数据表名称(id int, name string, ip STRING, country STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE #location指定所在的位置:切记,重点。 LOCATION '/external/user';
然后自己创建一个文件在hdfs,上传一个文件,然后创建一个扩展表和这个hdfs的文件关联起来:
[root@slaver3 hadoop]# cd /home/hadoop/hivetest/
[root@slaver3 hivetest]# cp phoneorder.data phoneorder4.data
[root@slaver3 hivetest]# hadoop fs -mkdir /hive_ext
[root@slaver3 hivetest]# hadoop fs -put phoneorder4.data /hive_ext
然后创建一个扩展表和这个hdfs的文件关联起来:
hive> create external table tb_order_ext(id int,name string,memory string,price double) > row format delimited > fields terminated by ' ' > location '/hive_ext';
具体操作如下所示:
查看一下是否存在数据:
可以查看扩展数据表的数据表结构,如下所示:
1 hive> desc extended tb_log; 2 OK 3 logid int 4 logname string 5 6 Detailed Table Information Table(tableName:tb_log, dbName:test, owner:root, createTime:1512892171, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:logid, type:int, comment:null), FieldSchema(name:logname, type:string, comment:null)], location:hdfs://master:9000/tb_log_file, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=,, field.delim=,}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{EXTERNAL=TRUE, transient_lastDdlTime=1512892171}, viewOriginalText:null, viewExpandedText:null, tableType:EXTERNAL_TABLE) 7 Time taken: 0.121 seconds, Fetched: 4 row(s) 8 hive>
格式化查看扩展表的数据表结构:
1 hive> desc formatted tb_log; 2 OK 3 # col_name data_type comment 4 5 logid int 6 logname string 7 8 # Detailed Table Information 9 Database: test 10 Owner: root 11 CreateTime: Sat Dec 09 23:49:31 PST 2017 12 LastAccessTime: UNKNOWN 13 Protect Mode: None 14 Retention: 0 15 Location: hdfs://master:9000/tb_log_file 16 Table Type: EXTERNAL_TABLE 17 Table Parameters: 18 EXTERNAL TRUE 19 transient_lastDdlTime 1512892171 20 21 # Storage Information 22 SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe 23 InputFormat: org.apache.hadoop.mapred.TextInputFormat 24 OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat 25 Compressed: No 26 Num Buckets: -1 27 Bucket Columns: [] 28 Sort Columns: [] 29 Storage Desc Params: 30 field.delim , 31 serialization.format , 32 Time taken: 0.577 seconds, Fetched: 29 row(s) 33 hive>
5:创建分区表(分区的好处是可以帮助你统计的时候少统计一些数据,加速数据统计):
hive> create table tb_part(sNo int,sName string,sAge int,sDept string) > partition //拿不准的单词,可以tab一下进行提示,并不会影响你创建表;谢谢 partition partitioned partitions > partitioned by (part string) > row format delimited > fields terminated by ',' > stored as textfile; OK Time taken: 0.351 seconds
并且将本地的数据上传到hive上面:
1 hive> load data local inpath '/home/hadoop/data_hadoop/tb_part' overwrite into table tb_part partition (part='20171210'); 2 Loading data to table test.tb_part partition (part=20171210) 3 Partition test.tb_part{part=20171210} stats: [numFiles=1, numRows=0, totalSize=43, rawDataSize=0] 4 OK 5 Time taken: 2.984 seconds 6 hive> load data local inpath '/home/hadoop/data_hadoop/tb_part' overwrite into table tb_part partition (part='20171211'); 7 Loading data to table test.tb_part partition (part=20171211) 8 Partition test.tb_part{part=20171211} stats: [numFiles=1, numRows=0, totalSize=43, rawDataSize=0] 9 OK 10 Time taken: 0.566 seconds 11 hive> show par 12 parse_url( parse_url_tuple( partition partitioned partitions 13 hive> show partition 14 partition partitioned partitions 15 hive> show partitions tb_part; 16 OK 17 part=20171210 18 part=20171211 19 Time taken: 0.119 seconds, Fetched: 2 row(s) 20 hive>
6:创建带桶的数据表,然后将本地创建好测试数据上传到hive上面:
#设置变量,设置分桶为true, 设置reduce数量是分桶的数量个数
set hive.enforce.bucketing = true;
set mapreduce.job.reduces=4;
1 hive> create table if not exists tb_stud(id int,name string,age int) 2 > partitioned by(clus string) 3 > clustered by(id) sorted by(age) into 2 buckets #分桶,根据id进行分桶,分成2个桶。 4 > row format delimited 5 > fields terminated by ','; 6 OK 7 Time taken: 0.194 seconds 8 hive> load data local inpath '/home/hadoop/data_hadoop/tb_clustered' overwrite into table tb_stud partition (clus='20171211'); 9 Loading data to table test.tb_stud partition (clus=20171211) 10 Partition test.tb_stud{clus=20171211} stats: [numFiles=1, numRows=0, totalSize=38, rawDataSize=0] 11 OK 12 Time taken: 0.594 seconds 13 hive>
7:修改表,增加/删除分区
语法结构
ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...
partition_spec:
: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)
ALTER TABLE table_name DROP partition_spec, partition_spec,...
具体实例如下所示:
alter table student_p add partition(part='a') partition(part='b');
修改分区和删除分区的操作:
hive> alter table tb_stud add partition(clus='20171215') location '/user/hive/warehouse/test.db' partition(clus='20171216'); OK Time taken: 1.289 seconds hive> alter table tb_stud add partition partition partitioned partitions hive> alter table tb_stud add partition(clus='20171217'); OK Time taken: 0.097 seconds hive> dfs -ls /user/hive/warehouse/test.db > ; Found 4 items drwxr-xr-x - root supergroup 0 2017-12-09 23:32 /user/hive/warehouse/test.db/tb_log drwxr-xr-x - root supergroup 0 2017-12-10 00:14 /user/hive/warehouse/test.db/tb_part drwxr-xr-x - root supergroup 0 2017-12-10 00:43 /user/hive/warehouse/test.db/tb_stud drwxr-xr-x - root supergroup 0 2017-12-09 21:28 /user/hive/warehouse/test.db/tb_user hive> show partitions tb_stud; OK clus=20171211 clus=20171215 clus=20171216 clus=20171217 Time taken: 0.119 seconds, Fetched: 4 row(s) hive> alter table tb_stud drop partition partition partitioned partitions hive> alter table tb_stud drop partition(clus='20171217'); Dropped the partition clus=20171217 OK Time taken: 1.433 seconds hive> show partitions tb_stud; OK clus=20171211 clus=20171215 clus=20171216 Time taken: 0.092 seconds, Fetched: 3 row(s) hive> alter table tb_stud drop partition(clus='20171215'),partition(clus='20171216'); Dropped the partition clus=20171215 Dropped the partition clus=20171216 OK Time taken: 0.271 seconds hive> show partitions tb_stud; OK clus=20171211 Time taken: 0.094 seconds, Fetched: 1 row(s) hive>
8:重命名表:
语法结构
ALTER TABLE table_name RENAME TO new_table_name
具体实例 ,如下所示:
1 hive> show tables; 2 OK 3 tb_log 4 tb_part 5 tb_stud 6 tb_user 7 Time taken: 0.026 seconds, Fetched: 4 row(s) 8 hive> alter table tb_user rename to tb_user_copy; 9 OK 10 Time taken: 0.19 seconds 11 hive> show tables; 12 OK 13 tb_log 14 tb_part 15 tb_stud 16 tb_user_copy 17 Time taken: 0.05 seconds, Fetched: 4 row(s) 18 hive>
9:增加/更新列
语法结构
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
注:ADD是代表新增一字段,字段位置在所有列后面(partition列前),REPLACE则是表示替换表中所有字段。
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
具体实例如下所示:
1 hive> desc tb_user; 2 OK 3 id int 4 name string 5 Time taken: 0.148 seconds, Fetched: 2 row(s) 6 hive> alter table tb_user add columns(age int); 7 OK 8 Time taken: 0.238 seconds 9 hive> desc tb_user; 10 OK 11 id int 12 name string 13 age int 14 Time taken: 0.088 seconds, Fetched: 3 row(s) 15 hive> alter table tb_user replace columns(id int,name string,birthday string); 16 OK 17 Time taken: 0.132 seconds 18 hive> desc tb_user; 19 OK 20 id int 21 name string 22 birthday string 23 Time taken: 0.083 seconds, Fetched: 3 row(s) 24 hive>
10:Load,操作只是单纯的复制/移动操作,DML操作
语法结构
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO
TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
说明:
1、Load 操作只是单纯的复制/移动操作,将数据文件移动到 Hive 表对应的位置。
2、filepath:
相对路径,例如:project/data1
绝对路径,例如:/user/hive/project/data1
包含模式的完整 URI,列如:
hdfs://namenode:9000/user/hive/project/data1
3、LOCAL关键字
如果指定了 LOCAL, load 命令会去查找本地文件系统中的 filepath。
如果没有指定 LOCAL 关键字,则根据inpath中的uri[如果指定了 LOCAL,那么:
load 命令会去查找本地文件系统中的 filepath。如果发现是相对路径,则路径会被解释为相对于当前用户的当前路径。
load 命令会将 filepath中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置。
如果没有指定 LOCAL 关键字,如果 filepath 指向的是一个完整的 URI,hive 会直接使用这个 URI。 否则:如果没有指定 schema 或者 authority,Hive 会使用在 hadoop 配置文件中定义的 schema 和 authority,fs.default.name 指定了 Namenode 的 URI。
如果路径不是绝对的,Hive 相对于/user/进行解释。
Hive 会将 filepath 中指定的文件内容移动到 table (或者 partition)所指定的路径中。]查找文件
4、OVERWRITE 关键字
如果使用了 OVERWRITE 关键字,则目标表(或者分区)中的内容会被删除,然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。
如果目标表(分区)已经有一个文件,并且文件名和 filepath 中的文件名冲突,那么现有的文件会被新文件所替代。
11:Hive的insert操作:
Insert
将查询结果插入Hive表
语法结构
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement
Multiple inserts:
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...
Dynamic partition inserts:
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement
1 <!--基本模式插入。--> 2 hive> load data local inpath '/home/hadoop/data_hadoop/tb_stud' overwrite into table tb_stud partition (clus='20171211'); 3 Loading data to table test.tb_stud partition (clus=20171211) 4 Partition test.tb_stud{clus=20171211} stats: [numFiles=1, numRows=0, totalSize=43, rawDataSize=0] 5 OK 6 Time taken: 4.336 seconds 7 hive> select * from tb_stud where clus='20171211'; 8 OK 9 1 张三 NULL 20171211 10 2 lisi NULL 20171211 11 3 wangwu NULL 20171211 12 4 zhaoliu NULL 20171211 13 5 libai NULL 20171211 14 Time taken: 0.258 seconds, Fetched: 5 row(s) 15 hive> insert overwrite table tb_stud partition(clus='20171218') 16 > select id,name,age from tb_stud where clus='20171211'; 17 Query ID = root_20171210012734_721f76d9-f670-42ad-bf68-bfb94baf5cda 18 Total jobs = 3 19 Launching Job 1 out of 3 20 Number of reduce tasks is set to 0 since there's no reduce operator 21 Starting Job = job_1512874725514_0005, Tracking URL = http://master:8088/proxy/application_1512874725514_0005/ 22 Kill Command = /home/hadoop/soft/hadoop-2.6.4/bin/hadoop job -kill job_1512874725514_0005 23 Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0 24 2017-12-10 01:28:00,125 Stage-1 map = 0%, reduce = 0% 25 2017-12-10 01:28:34,514 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.44 sec 26 MapReduce Total cumulative CPU time: 1 seconds 440 msec 27 Ended Job = job_1512874725514_0005 28 Stage-4 is selected by condition resolver. 29 Stage-3 is filtered out by condition resolver. 30 Stage-5 is filtered out by condition resolver. 31 Moving data to: hdfs://master:9000/user/hive/warehouse/test.db/tb_stud/clus=20171218/.hive-staging_hive_2017-12-10_01-27-34_894_112189266881641464-1/-ext-10000 32 Loading data to table test.tb_stud partition (clus=20171218) 33 Partition test.tb_stud{clus=20171218} stats: [numFiles=1, numRows=5, totalSize=58, rawDataSize=53] 34 MapReduce Jobs Launched: 35 Stage-Stage-1: Map: 1 Cumulative CPU: 1.44 sec HDFS Read: 3816 HDFS Write: 140 SUCCESS 36 Total MapReduce CPU Time Spent: 1 seconds 440 msec 37 OK 38 Time taken: 64.66 seconds 39 hive> select * from tb_stud where clus='20171218'; 40 OK 41 1 张三 NULL 20171218 42 2 lisi NULL 20171218 43 3 wangwu NULL 20171218 44 4 zhaoliu NULL 20171218 45 5 libai NULL 20171218 46 Time taken: 0.085 seconds, Fetched: 5 row(s) 47 hive> 48 49 <!--多插入模式。--> 50 hive> show partitions tb_stud; 51 OK 52 clus=20171211 53 clus=20171218 54 Time taken: 0.153 seconds, Fetched: 2 row(s) 55 hive> alter table tb_stud add partition(clus='20171212'); 56 OK 57 Time taken: 0.143 seconds 58 hive> alter table tb_stud add partition(clus='20171213'); 59 OK 60 Time taken: 0.399 seconds 61 hive> alter table tb_stud add partition(clus='20171214'); 62 OK 63 Time taken: 0.139 seconds 64 hive> from tb_stud 65 > insert overwrite table tb_stud partition(clus='20171213') 66 > select id,name,age where clus='20171211' 67 > insert overwrite table tb_stud partition(clus='20171214') 68 > select id,name,age where clus='20171211'; 69 Query ID = root_20171210013655_0c4a1d78-88e2-4de0-99ca-074c9eed81a4 70 Total jobs = 5 71 Launching Job 1 out of 5 72 Number of reduce tasks is set to 0 since there's no reduce operator 73 Starting Job = job_1512874725514_0007, Tracking URL = http://master:8088/proxy/application_1512874725514_0007/ 74 Kill Command = /home/hadoop/soft/hadoop-2.6.4/bin/hadoop job -kill job_1512874725514_0007 75 Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 0 76 2017-12-10 01:37:05,501 Stage-2 map = 0%, reduce = 0% 77 2017-12-10 01:38:06,089 Stage-2 map = 0%, reduce = 0% 78 2017-12-10 01:38:08,363 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.46 sec 79 MapReduce Total cumulative CPU time: 1 seconds 460 msec 80 Ended Job = job_1512874725514_0007 81 Stage-5 is selected by condition resolver. 82 Stage-4 is filtered out by condition resolver. 83 Stage-6 is filtered out by condition resolver. 84 Stage-11 is selected by condition resolver. 85 Stage-10 is filtered out by condition resolver. 86 Stage-12 is filtered out by condition resolver. 87 Moving data to: hdfs://master:9000/user/hive/warehouse/test.db/tb_stud/clus=20171213/.hive-staging_hive_2017-12-10_01-36-55_602_8039889333976698612-1/-ext-10000 88 Moving data to: hdfs://master:9000/user/hive/warehouse/test.db/tb_stud/clus=20171214/.hive-staging_hive_2017-12-10_01-36-55_602_8039889333976698612-1/-ext-10002 89 Loading data to table test.tb_stud partition (clus=20171213) 90 Loading data to table test.tb_stud partition (clus=20171214) 91 Partition test.tb_stud{clus=20171213} stats: [numFiles=1, numRows=0, totalSize=58, rawDataSize=0] 92 Partition test.tb_stud{clus=20171214} stats: [numFiles=1, numRows=0, totalSize=58, rawDataSize=0] 93 MapReduce Jobs Launched: 94 Stage-Stage-2: Map: 1 Cumulative CPU: 1.57 sec HDFS Read: 4798 HDFS Write: 280 SUCCESS 95 Total MapReduce CPU Time Spent: 1 seconds 570 msec 96 OK 97 Time taken: 81.536 seconds 98 hive> select * from tb_stud where clus='20171213'; 99 OK 100 1 张三 NULL 20171213 101 2 lisi NULL 20171213 102 3 wangwu NULL 20171213 103 4 zhaoliu NULL 20171213 104 5 libai NULL 20171213 105 Time taken: 0.138 seconds, Fetched: 5 row(s) 106 hive> select * from tb_stud where clus='20171214'; 107 OK 108 1 张三 NULL 20171214 109 2 lisi NULL 20171214 110 3 wangwu NULL 20171214 111 4 zhaoliu NULL 20171214 112 5 libai NULL 20171214 113 Time taken: 0.075 seconds, Fetched: 5 row(s) 114 hive> 115 116 <!--自动分区模式。-->
12:导出表数据
语法结构
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...
multiple inserts:
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...
具体实例
1 1、导出文件到本地。 2 说明: 3 数据写入到文件系统时进行文本序列化,且每列用^A来区分, 为换行符。用more命令查看时不容易看出分割符,可以使用: sed -e 's/x01/|/g' filename[]来查看。 4 5 6 hive> insert overwrite local directory '/home/hadoop/data_hadoop/get_tb_stud' 7 > select * from tb_stud; 8 Query ID = root_20171210014640_4c499323-760e-4494-946b-5ffad8fb3789 9 Total jobs = 1 10 Launching Job 1 out of 1 11 Number of reduce tasks is set to 0 since there's no reduce operator 12 Starting Job = job_1512874725514_0008, Tracking URL = http://master:8088/proxy/application_1512874725514_0008/ 13 Kill Command = /home/hadoop/soft/hadoop-2.6.4/bin/hadoop job -kill job_1512874725514_0008 14 Hadoop job information for Stage-1: number of mappers: 2; number of reducers: 0 15 2017-12-10 01:46:50,400 Stage-1 map = 0%, reduce = 0% 16 2017-12-10 01:47:25,696 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 7.13 sec 17 MapReduce Total cumulative CPU time: 7 seconds 130 msec 18 Ended Job = job_1512874725514_0008 19 Copying data to local directory /home/hadoop/data_hadoop/get_tb_stud 20 Copying data to local directory /home/hadoop/data_hadoop/get_tb_stud 21 MapReduce Jobs Launched: 22 Stage-Stage-1: Map: 2 Cumulative CPU: 7.13 sec HDFS Read: 10392 HDFS Write: 515 SUCCESS 23 Total MapReduce CPU Time Spent: 7 seconds 130 msec 24 OK 25 Time taken: 47.258 seconds 26 hive> 27 28 <!--导出数据到HDFS。--> 29 hive> insert overwrite directory 'hdfs://192.168.199.130:9000/user/hive/warehouse/tb_stud_get' 30 > select * from tb_stud; 31 Query ID = root_20171210015229_b0a323b1-b1dc-4f31-b932-cb8126bac2ff 32 Total jobs = 3 33 Launching Job 1 out of 3 34 Number of reduce tasks is set to 0 since there's no reduce operator 35 Starting Job = job_1512874725514_0009, Tracking URL = http://master:8088/proxy/application_1512874725514_0009/ 36 Kill Command = /home/hadoop/soft/hadoop-2.6.4/bin/hadoop job -kill job_1512874725514_0009 37 Hadoop job information for Stage-1: number of mappers: 2; number of reducers: 0 38 2017-12-10 01:53:52,773 Stage-1 map = 0%, reduce = 0% 39 2017-12-10 01:54:07,829 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 2.65 sec 40 MapReduce Total cumulative CPU time: 2 seconds 650 msec 41 Ended Job = job_1512874725514_0009 42 Stage-3 is filtered out by condition resolver. 43 Stage-2 is selected by condition resolver. 44 Stage-4 is filtered out by condition resolver. 45 Launching Job 3 out of 3 46 Number of reduce tasks is set to 0 since there's no reduce operator 47 Starting Job = job_1512874725514_0010, Tracking URL = http://master:8088/proxy/application_1512874725514_0010/ 48 Kill Command = /home/hadoop/soft/hadoop-2.6.4/bin/hadoop job -kill job_1512874725514_0010 49 Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 0 50 2017-12-10 01:54:55,697 Stage-2 map = 0%, reduce = 0% 51 2017-12-10 01:55:45,607 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.43 sec 52 MapReduce Total cumulative CPU time: 1 seconds 430 msec 53 Ended Job = job_1512874725514_0010 54 Moving data to: hdfs://192.168.199.130:9000/user/hive/warehouse/tb_stud_get 55 MapReduce Jobs Launched: 56 Stage-Stage-1: Map: 2 Cumulative CPU: 2.65 sec HDFS Read: 10412 HDFS Write: 515 SUCCESS 57 Stage-Stage-2: Map: 1 Cumulative CPU: 1.43 sec HDFS Read: 2313 HDFS Write: 515 SUCCESS 58 Total MapReduce CPU Time Spent: 4 seconds 80 msec 59 OK 60 Time taken: 202.508 seconds 61 hive> dfs -ls /user/hive/warehouse/tb_stud_get; 62 Found 1 items 63 -rwxr-xr-x 2 root supergroup 515 2017-12-10 01:55 /user/hive/warehouse/tb_stud_get/000000_0 64 hive>
13:SELECT,基本的Select操作
语法结构
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]
注:1、order by 会对输入做全局排序,因此只有一个reducer,会导致当输入规模较大时,需要较长的计算时间。
2、sort by不是全局排序,其在数据进入reducer前完成排序。因此,如果用sort by进行排序,并且设置mapred.reduce.tasks>1,则sort by只保证每个reducer的输出有序,不保证全局有序。
3、distribute by根据distribute by指定的内容将数据分到同一个reducer。
4、Cluster by 除了具有Distribute by的功能外,还会对该字段进行排序。因此,常常认为cluster by = distribute by + sort by5、分桶表的最大的意思:最大的作用是用来提高join操作的效率;
6、思考这个问题:
select a.id,a.name,b.addr from a join b on a.id = b.id;
如果a表和b表已经是分桶表,而且分桶的字段是id字段
做这个join操作时,还需要全表做笛卡尔积吗?答案:不需要,因为相同的id就在同一个桶里面。
14:删除hive的内部表和外部表的区别:
删除内部表是将元数据(TABL表),以及hdfs上面的文件夹以及文件一起删除;
删除外部表只是删除元数据(TABL表),hdfs上面的文件夹以及文件不删除。
15:创建一个新表根据老表(用来做一些中间结果的存储,再做后一步的处理,):
注意:用于创建一些临时表存储中间结果;
hive> create table tb_order_new > as > select id,name,memory,price > from tb_order;
16:insert from select 通过select语句批量插入数据到别的表(用于向临时表中追加中间结果数据):
//创建一个表 create table tab_ip_like like tab_ip; //批量插入数据,批量插入已经存在表 insert overwrite table tab_ip_like select * from tab_ip;
hive> create table tb_order_append(id int,name string,memory string,price double) > row format delimited > fields terminated by ' '; hive> insert overwrite table tb_order_append > select * from tb_order; hive> select * from tb_order_append;
操作如下所示:
17:PARTITION ,分区表(partition),分区统计,可以对数据操作加快速度:
查询分区:hive> show partitions part; #show partitions 数据表名称;
删除分区:hive> alter table part drop partition(date='20180512');
添加分区:hive> alter table part add partition(date='20180512');
hive> create table tb_order_part(id int,name string,memory string,salary double) > partitioned by (month string) > row format delimited > fields terminated by ' '; OK Time taken: 0.13 seconds
然后将数据导入这个新建的分区里面(所谓分区就是在文件夹下面创建一个文件夹,把数据放到这个文件夹下面),如下所示:
hive> load data local inpath '/home/hadoop/hivetest/phoneorder.data' into table tb_order_part partition(month='201401');
hive> load data local inpath '/home/hadoop/hivetest/phoneorder2.data' into table tb_order_part partition(month='201402');
可以根据分区查询一下数据:
18:write to hdfs,将结果写入到hdfs的文件中:
hive> insert overwrite local directory '/home/hadoop/hivetest/test.txt' > select * from tb_order_part > where month="201401";
19:Hive的Join使用:
语法结构 join_table: table_reference JOIN table_factor [join_condition] | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition | table_reference LEFT SEMI JOIN table_reference join_condition Hive 支持等值连接(equality joins)、外连接(outer joins)和(left/right joins)。Hive 不支持非等值的连接,因为非等值连接非常难转化到 map/reduce 任务。 另外,Hive 支持多于 2 个表的连接。 写 join 查询时,需要注意几个关键点: 1. 只支持等值join 例如: SELECT a.* FROM a JOIN b ON (a.id = b.id) SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department) 是正确的,然而: SELECT a.* FROM a JOIN b ON (a.id>b.id) 是错误的。 2. 可以 join 多于 2 个表。 例如 SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2) 如果join中多个表的 join key 是同一个,则 join 会被转化为单个 map/reduce 任务,例如: SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1) 被转化为单个 map/reduce 任务,因为 join 中只使用了 b.key1 作为 join key。 SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2) 而这一 join 被转化为 2 个 map/reduce 任务。因为 b.key1 用于第一次 join 条件,而 b.key2 用于第二次 join。 3.join 时,每次 map/reduce 任务的逻辑: reducer 会缓存 join 序列中除了最后一个表的所有表的记录,再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中,应该把最大的那个表写在最后(否则会因为缓存浪费大量内存)。例如: SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1) 所有表都使用同一个 join key(使用 1 次 map/reduce 任务计算)。Reduce 端会缓存 a 表和 b 表的记录,然后每次取得一个 c 表的记录就计算一次 join 结果,类似的还有: SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2) 这里用了 2 次 map/reduce 任务。第一次缓存 a 表,用 b 表序列化;第二次缓存第一次 map/reduce 任务的结果,然后用 c 表序列化。 4.LEFT,RIGHT 和 FULL OUTER 关键字用于处理 join 中空记录的情况 例如: SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key) 对应所有 a 表中的记录都有一条记录输出。输出的结果应该是 a.val, b.val,当 a.key=b.key 时,而当 b.key 中找不到等值的 a.key 记录时也会输出: a.val, NULL 所以 a 表中的所有记录都被保留了; “a RIGHT OUTER JOIN b”会保留所有 b 表的记录。 Join 发生在 WHERE 子句之前。如果你想限制 join 的输出,应该在 WHERE 子句中写过滤条件——或是在 join 子句中写。这里面一个容易混淆的问题是表分区的情况: SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key) WHERE a.ds='2009-07-07' AND b.ds='2009-07-07' 会 join a 表到 b 表(OUTER JOIN),列出 a.val 和 b.val 的记录。WHERE 从句中可以使用其他列作为过滤条件。但是,如前所述,如果 b 表中找不到对应 a 表的记录,b 表的所有列都会列出 NULL,包括 ds 列。也就是说,join 会过滤 b 表中不能找到匹配 a 表 join key 的所有记录。这样的话,LEFT OUTER 就使得查询结果与 WHERE 子句无关了。解决的办法是在 OUTER JOIN 时使用以下语法: SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07') 这一查询的结果是预先在 join 阶段过滤过的,所以不会存在上述问题。这一逻辑也可以应用于 RIGHT 和 FULL 类型的 join 中。 Join 是不能交换位置的。无论是 LEFT 还是 RIGHT join,都是左连接的。 SELECT a.val1, a.val2, b.val, c.val FROM a JOIN b ON (a.key = b.key) LEFT OUTER JOIN c ON (a.key = c.key) 先 join a 表到 b 表,丢弃掉所有 join key 中不匹配的记录,然后用这一中间结果和 c 表做 join。这一表述有一个不太明显的问题,就是当一个 key 在 a 表和 c 表都存在,但是 b 表中不存在的时候:整个记录在第一次 join,即 a JOIN b 的时候都被丢掉了(包括a.val1,a.val2和a.key),然后我们再和 c 表 join 的时候,如果 c.key 与 a.key 或 b.key 相等,就会得到这样的结果:NULL, NULL, NULL, c.val