《hive编程指南》之hive的数据类型，创建表，外部表，分区，文件类型，字段分割格式。
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]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
LIKE existing_table_name
[LOCATION hdfs_path]
data_type
: primitive_type
| array_type
| map_type
| struct_type
primitive_type
: TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| STRING
array_type
: ARRAY < data_type >
map_type
: MAP < primitive_type, data_type >
struct_type
: STRUCT < col_name : data_type [COMMENT col_comment], ...>
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, ...)]
file_format:
: SEQUENCEFILE
| TEXTFILE
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
这个是我从hive的docs里面复制出来的，因为我感觉我怎么写也没人家写得好。我最多无非就是在下面举几个例子，说明一下使用的方法。
在任意的地方写比如hive -e ‘show databases’
会执行单引号里面的命令，然后执完以后会退出，
hive -e ‘show databases’ -S
加了-S会不会有额外的输出。
首先先看一下数据类型:
TINYINT SMALLINT INT BIGINT当然了都是int类型的，只是范围不一样。
BOOLEAN
FLOAT
DOUBLE
STRING
BINARY
TIMESTAMP
后两种都是在hive0.8版本后才有的。当然了还有一些其他的类型，我们后面用到再说。
hadoop fs -ls /user/hive/warehouse这个下面保存的是我们表的实际的数据，这个目录是我们默认的目录。然后表的结构之类的是保存在metadata当中。而metadata又保存在我们的MySQL当中。
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [LOCATION ‘hdfs_path’]
create external table tab_name location ‘/tmp/ext/tab_name’;
EXTERNAL：作用是可以指定其他目录保存在hdfs中，不然的话就只能保存在默认的位置了。
hdfs_path:是一个已经存在的目录。不能直接是一个目录，还得有上级的这么一个目录。
然后我们只要往tab_name里put我们的文件，我们执行
select * from tab_name就会看到数据了.
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[(col_name data_type [COMMENT col_comment], ...)] //列名：数据类型：列的注释
[COMMENT table_comment]//整个表的注释
[PARTITIONED BY//接着解释一下
举个例子
drop table tab_name;
create table tab_name (id int , name string) partitioned by (aa string) row format delimited fields terminated by ‘,’location ‘/tmp/ext/tab_name’;
load data local inpath ’/a.txt’ into table tab_name partition(aa=”1”);
load data local inpath '/b.txt' into table user partition (aa="2");
然后注意观察dfs -ls /tmp/ext/tab_name下有什么？
我们发现有一个叫aa=1的目录，整个目录下存着刚刚我们load的文件a.txt;
然后我们写select * from tab_name where aa=’1’;
就会查出来该目录下的数据
如果是select * from tab_name where aa=’2’;
那么查出来的就是对应的目录的下的数据
那么如果是select * from tab_name不写的话
会把这个表的所有的数据都会查出来。
CLUSTERED BY
这个会是什么作用呢？
我们数据还是使用我们话单业务产生的数据，
drop table tab_cdr;
create table tab_cdr(oaddr string,oareacode string,daddr string,dareacode string,ts string,type string) row format delimited fields teminated by ‘,’;
load data local inpath ‘/var/www/hadoop/test/output/xx.txt’ into table tab_cdr;(加进去了十万条数据)
接着我们创建
set hive.enforce.bucketing=true;//强制使用
create table tab_cdr_buc (oaddr string,oareacode string,daddr string,dareacode string,ts string,type string) clustered by (oaddr ) into 10 buckets;
然后我做一个操作
insert into table tab_cdr_buc select * from tab_cdr;
然后我们观察
/tmp/ext/tab_cdr_buc
它下面会有十个文件，规则是根据oaddr的。
这样做的目的就是为了提高mapreduce的效率。
总之我们大体的思路是：把描述信息存到mysql中，这样我们随时随地在不同主机上也可以访问hive表，然后我们把文件要存到hdfs上，这样就利用到了集群的威力。这些才是我们的目的。
Hive其实跟hibernate很像的，一个表最主要的就是表的描述信息，表的描述信息存在metadata里面，而我们的metadata存储在MySQL的数据库里面，这个数据库的名字会在配置的时候配置。
比如我此次的数据库的名字叫lixiyuanhive,然后路径就是（/var/lib/mysql/lixiyuanhive/）我们表的描述信息室在这儿的。
前面说的我们把文件存储在HDFS上，其有默认的路径是/user/hive/warehouse；当然了我们在建表的时候也可以自己指定路径。
data_type :
primitive_type //基本数据类型
array_type
map_type
struct_type primitive_type ://基本数据类型有
TINYINT
SMALLINT
INT
BIGINT
BOOLEAN
FLOAT
DOUBLE
STRING
BINARY
TIMESTAMP
array_type : ARRAY < data_type > //它的意思是array里面可以放data_type类型
那么data_type的类型有哪些呢，上面有。
map_type : MAP < primitive_type, data_type >//看出来这个需要我们定义key value
struct_type : STRUCT < col_name : data_type [COMMENT col_comment], ...>
union_type:UNIONTYPE<data_type,data_type,....>
那么接着我们看array怎么使用：
drop table tab_array;
create table tab_array(a array<string> , b array<int>)
row format delimited fields terminated by ‘\t’
collection items terminated by ‘,’;
然后看看我们准备的数据array.txt：
Rob,bob,stever 1,2,3
Amy,ady 11,22
Jac 11,22,33,44,55
load data local inpath ‘/home/robby/array.txt’ into table tab_array;
当然了我们可以select * from tab_array一下
存储的方式跟我们在文本里面的时候差不多
如果我们要想访问集合那么
select a[0] from tab_array;
Rob,bob,stever 1,2,3//有0 1 2
Amy,ady 11,22 //有0 1
Jac 11,22,33,44,55//有0
我们select a[0] from tab_array;的结果就是查到0的
Rob
Amy
Jac
那么hive也提供了一些函数来给我们使用
select * from tab_array where array_contains(b,22);
//我们查找b列中包含22字段的行。
那么结果应该是后两行
insert into table tab_array select array(oaddr,daddr), array(0) from tab_cdr;
//这句话主要是从其他表中获取数据然后插到我们的表中，array(oaddr,daddr)那么我们的a字段是string，然后我们选用了tab_cdr的两个字段，然后tab_cdr里面没有int类型的，所以我们就填0。
我们查看一下：
select * from tab_cdr limit 10;
接下来我们看看map是这样使用的
drop table tab_map;
create table tab_map(name string,info map<string,string>)
row format delimited fields terminated by ‘\t’
collection items terminated by ‘,’
map keys terminated by ‘:’;
那么我们有一个map.txt的文件为：
Rob age:30,tel:4234323
Amy age:22,tel:43243
Bob age:33,tel:432434,addr:shanghai
接着：
load data local inpath ‘/home/robby/map.txt’ into table tab_map;
那么我们怎么访问map的字段呢？
select info[‘age’] from tab_map;
我们如何从其它表中读取数据到map表中？
insert into table tab_map select oaddr,map(‘oareacode’,oareacode,’daddr’,daddr) from tab_cdr;
结论：map里面是可以填任意多个的，不管你当时定义的几个
接下来我们看看struct是怎么使用？(个人感觉用的多)
drop table tab_st;
create table tab_st(name string,info struct<age:int,tel:string,addr:string>)
row format delimited fields terminated by ‘\t’
collection items terminated by ‘,’;
那么我们有准备好了的struts.txt数据为：
Rob 30,432423
Amy 17,432423,beijing
Bob 28,213243,shanghai
然后加载数据：
load data local inpath ‘/home/robby/struts.txt’ into table tab_st;
那么我们怎么访问struct里面的数据：
select info.age from tab_st;
insert into table tab_st select oaddr,name_struct(‘age’,0,’tel’,daddr,’addr’,dareacode) from tab_cdr;
结论：跟map不一样，当时strust内定义了几个就得插入几个，当然了没有的可以为null；
四中文件类型：
SEQUENCEFILE ：压缩后可以进行mapreduce任务计算的
TEXTFILE：普通的文件
RCFILE：facebook弄出来的，针对hive的，性能比SEQUENCEFILE 还要好
INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname：自定义的类型。
举个例子：
我们有一个a.txt的文件，里面存了内容，然后我们用gzip命令压缩一下，然后产生一个叫
a.txt.gz的文件.
然后我们在hive里面创建表
drop table tab_name;
create table tab_name(id int,name string) row format delimited fields terminated by ‘,’;
创建好后：
load data local inpath ‘/home/robby/a.txt.gz’ into table tab_name;
接着我们观察一下：
在hive下输入：
dfs -ls /user/hive/warehouse/tab_name;
我们发现，他直接存储了我们的压缩文件a.txt.gz;
那么我们直接可以用sql语句访问我们的表(因为hive会做解压的工作)
select * from tab_name;
我们普通文件的压缩后不能直接进行mapreduce任务。这个不好的地方。
所以我们不能用压缩的文本文件做为hive的存储。同时呢我们最好也不要用文本文件作为文件的存储，因为它占的地方大。
因此呢，我们就引出了上面的两种格式
（blog.cdsn.net/wh62592855/atricle/details/6409680）
一种是：SEQUENCEFILE ，SEQUENCEFILE跟普通的文本文件的压缩的区别是，普通的文本文件的压缩是针对整个文件的压缩，SEQUENCEFILE针对的是每一部分的压缩。那么是按行来分的。
一种是：RCFILE，那么RCFILE也是针对每一部分的压缩，那么是按行分和按列分的混合体，先水平，再垂直。(一般我们用的压缩算法是LOZO)
LOZO的安装
1, sudo apt-get install liblzo2-dev
2, sudo apt-get install lzop
3, https://github.com/kevinweil/hadoop-lzo
a, git clone https://github.com/kevinweil/hadoop-lzo （不会可以不 用这种）
b, download *.tar.gz
4, 修改 build.xml
搜索javah
加上<classpath refid="classpath"/>
<javah classpath="${build.classes}"
destdir="${build.native}/src/com/hadoop/compression/lzo"
force="yes"
verbose="yes">
<class name="com.hadoop.compression.lzo.LzoCompressor" />
<class name="com.hadoop.compression.lzo.LzoDecompressor" />
<classpath refid="classpath"/>
</javah>
5, export CFLAGS=-m32
export CXXFLAGS=-m32
ant compile-native tar
6, 拷贝 build/hadoop-lzo-0.4.15.jar 到 $HADOOP_HOME/lib
7, 修改core-site.xm
<property>
<name>io.compression.codecs</name>
<value>
org.apache.hadoop.io.compress.DefaultCodec,org.apache.hadoop.io.compress.GzipCodec,org. apache.hadoop.io.compress.BZip2Codec,com.hadoop.compression.lzo.LzopCodec
</value>
</property>
<property>
<name>io.compression.codec.lzo.class</name>
<value>com.hadoop.compression.lzo.LzoCodec</value>
</property>
8, cp build/native/Linux-i386-32/lib/libgplcompression.so $HADOOP_HOME/lib/native/Linux-i386-32/
然后重启hadoop就可以了。
接着，我们举例看上面讲的几个文件类型是怎么使用的，首先要准备数据
那么数据我们还是用话单例子的数据。
1：首先我们创建一个普通文本文件类型存储的表
drop table tab_cdr;
create table tab_cdr(oaddr string,oareacode string,daddr string,dareacode string,ts string,type string) row format delimited fields teminated by ‘,’;
load data local inpath ‘/var/www/hadoop/test/output/b.txt’ into table tab_cdr;(加进去了二十万条数据)
当然了，我们可以观察一下：
hive> dfs -ls /user/hive/warehouse/tab_cdr/
那么我们看到大小为9800000
2:我们用SEQUENCEFILE来存储
首先我们设置一下参数：
set hive.exec.compress.output=true;
set mapred.output.compress=true;
set mapred.output.copression.type=BLOCK://压缩的方式有三种，按份压缩，按行压缩，按block压缩
set mapred.output.compression.codec=org.apache.hadoop.io.compress.LzoCodec;
drop table tab_cdr_seq;
create table tab_cdr_seq(oaddr string,oareacode string,daddr string,dareacode string,ts string,type string) row format delimited fields teminated by ‘,’ stored as sequencefile;
insert overwrite table tab_cdr_seq select * from tab_cdr;(会启mapreduce任务的)
用了overwrite会把原来的数据线清空。
然后我们观察：
hive> dfs -ls /user/hive/warehouse/tab_cdr_seq/
我们看到这个文件的大小变为：4379300
当然了这样的压缩比没有我们直接压缩文本好，但是好处就是我们能直接对压缩后的文件进行mapreduce任务。
3：怎样使用RCFILE：
set hive.exec.compress.output=true;
set mapred.output.compress=true;
set mapred.output.copression.type=BLOCK:
set mapred.output.compression.codec=org.apache.hadoop.io.compress.LzoCodec;
drop table tab_cdr_rc;
create table tab_cdr_rc(oaddr string,oareacode string,daddr string,dareacode string,ts string,type string) row format delimited fields teminated by ‘,’ stored as rcfile;
insert overwrite table tab_cdr_rc select * from tab_cdr;(会启mapreduce任务的)
那么这个跟我们的上面那个是非常像的。
观察：
hive> dfs -ls /user/hive/warehouse/tab_cdr_rc/
我们看到文件的大小为：3605100
如果从压缩比上看的话，sequencefile的差别不会太大，但是执行mapreduce的话，从总体上看，rcfile的要好一些，所以如果项目开发的话，我们应该可以优先使用rcfile。
此外：比如我们写这样的语句
Select oaddr from tab_cdr where ofd=’xxxx’时
就是说我们有这样的语句只是查到部分字段的，那么我们如果用的是rcfile存储的话，我们这个语句的mapreduce会跳过不相关的列，这样的话我们的效率很高。
join
举例：当然了这个例子跟我们话单的业务表示联合起来的，我们当时话单业务的表里面，也有归属地这一栏，但是当时我们只是显示了010等之类的数字，并没有对应起来。
drop table tab_areaname;
create table tab_areaname(areacode string,cityname string)
row format delimited fields terminated by ',';
准备文件/usr/local/areaname.txt内容为：
010,beijing
021,shanghai
020,guangzhou
加载数据：
load data local inpath '/usr/local/areaname.txt' overwrite into table tab_areaname;
然后我们创建一个表，这个表的目的是用来存查询得到的结果的。
drop table tab_res1;
create table tab_res1(oaddr string,cityname string);
那么我们开始来做这个操作：
insert overwrite table tab_res1
select b.oaddr , a.cityname from tab_areaname a
join tab_cdr b
on a.areacode=b.oarecode;
//上面一共是一条语句,注意的是，我们做连表查询的时候，我们把数据量小的那张表写到join的左边，这样是为了效率好（原因是在做mapreduce的时候，在reduce阶段hive会把join左边的表加载到我们的内存中）
接下来咱们可以查找一下：
select * from tab_res1 limit 100;
当然了也可以看每个城市有多少个话单
select cityname,count(*) from tab_res1 group by cityname;
map join
insert overwrite table tab_res1
select /*+ MAPJOIN*/ b.oaddr , a.cityname from tab_areaname a
join tab_cdr b
on a.areacode=b.oarecode;
那么顾名思义，我们做这个连表是在map任务的时候就连了，没有经过reduce的计算；
这样的话，效率会高一些，当然了会有使用的限制的，得某一张表的数据量要小。
多余两张表的联合
drop table tab_user;
create table tab_user(addr string);
insert overwrite table tab_user select distinct(oaddr) from tab_cdr limit 100;
//插入的数据是从tab_cdr里面选择的不重复的100个主机号码
insert overwrite table tab_res1
select b.oaddr , a.cityname from tab_areaname a
join tab_cdr b
on a.areacode=b.oarecode
join tab_user c
on b.oaddr=c.addr;
比较这两句的话，后面这句的效率更高一些
我们做怎样的操作？比如我们想查询tab_cdr里面的一些字段，但是我不想操作所有的
我只是想查找部分用户的，而这部分用户保存在tab_user里面，那么我们要像后面那样写。
select oaddr,oareacode from tab_cdr a join tab_user b on a.oaddr=b.addr;
select oaddr,oareacode from tab_cdr a left semi join tab_user b on a.oaddr=b.addr;
5132
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
