创建表 · 大数据

[TOC] ## 创建表 ### 建表语法 ~~~ 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 选项来忽略这个异常。 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）更高效**。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。 ### 具体实例 #### 文件载入表 ~~~ hive> create table student(id int,name string,age int) > row format delimited > fields terminated by ','; OK ~~~ 创建表的时候指定行分割和每个字段分割创建文本 ~~~ [root@master ~]# cat student.txt 1,jdxia,17 2,user2,20 ~~~ 然后上传上去(后面就是hadoop的路径) ~~~ hdfs dfs -put student.txt /user/hive/warehouse/db1.db/student/ ~~~ 然后查询下表 ~~~ hive> select * from student; OK 1 jdxia 17 2 user2 20 Time taken: 0.082 seconds, Fetched: 2 row(s) ~~~ 如果表不这样指定行分割和列分割,会显示null 我们再次上传下看下 ~~~ [root@master ~]# cp student.txt student1.txt [root@master ~]# hdfs dfs -put student1.txt /user/hive/warehouse/db1.db/student/ ~~~ 然后select看下发现又多了数据 #### hdfs载入表但是这样做不好,我们一般这么做 **inpath载入** 创建表 ~~~ hive> create table t_user(id int,name string,age int) > row format delimited > fields terminated by ','; OK Time taken: 0.088 seconds ~~~ 把本地的东西载入进去 ~~~ hive> load data local inpath '/root/student.txt' into table t_user; ~~~ 如果要加载hdfs上面的数据我们先把这个加载到hadoop中 ~~~ hdfs dfs -put student1.txt / ~~~ 然后我们在hive中操作 ~~~ load data inpath '/student1.txt' into table t_user; ~~~ 这样就可以用hdfs中的文件,载入进表中 #### 创建分桶表分桶表不要load,不然你去hdfs上看还是一个文件开启分桶机制,默认是关闭的 ~~~ set hive.enforce.bucketing=true; //查看 set hive.enforce.bucketing; ~~~ clustered by表示按什么分桶 ~~~ hive> create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string) > clustered by(Sno) > sorted by(Sno DESC) > into 4 buckets > row format delimited > fields terminated by ','; ~~~ ~~~ //清空表数据,可以用这个 truncate table stu_buck; ~~~ **桶表插入** ~~~ //插入数据,需要后面有这样的规则( distribute by sno sort by sno desc;)不然没有按照分桶的规则 //不要用clustered会报错 insert overwrite table stu_buck select * from student_ext distribute by sno sort by sno desc; ~~~ ~~~ //查看下 select * from student_ext; dfs -cat /user/hive/warehouse/db1.db/stu_buck/000000_0 ~~~ **桶表抽样查询** ~~~ Select * from student tablesample(bucket 1 out of 2 on id) tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例. 如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。 x表示从哪个bucket开始抽取。例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据 ~~~ ~~~ //查询其中一个桶,和直接cat查询文件是一样的,这是取1个桶的 select * from stu_buck tablesample (bucket 1 out of 4 on sno); //取2个桶的,1和3这2个桶 select * from stu_buck tablesample (bucket 1 out of 2 on sno); ~~~ ### 内部表和外部表的区别 external #### 外部表 ~~~ hive> create external table t_ext(id int,name string,age int) > row format delimited > fields terminated by ','; OK ~~~ 创建外部表可以加个local属性指定路径,他可以加载外部的东西,不像内部表 ~~~ hive> create external table t_ext(id int,name string,age int) > row format delimited > fields terminated by ',' > location "/hivedata"; OK ~~~ 然后我们把文件放到这个目录下面,发现是可以select出数据的然后我们看mysql表 TBLS(创建表单日期的一些数据)和COLUMNS_V2(表的一些字段信息)表 **如果我们把表drop掉的话,发现hdfs中还是有的,但是hive中是没有的,表示连接断开了,但是数据还是在的** ### 分区表 **创建表** ~~~ hive> create table t_patition(ip string,duration int) > partitioned by(country string) > row format delimited > fields terminated by ','; OK Time taken: 0.086 seconds ~~~ **查看表的详情** ~~~ hive> desc t_patition; ~~~ 我们来用文件来填充数据注意我们要指定分区的,因为他的分区表 ~~~ hive> load data local inpath '/root/ip.txt' into table t_patition partition(country="china"); ~~~ 然后查询的时候,后面会显示分区 ~~~ hive> select * from t_patition; OK 192.168.1.100 1 china 192.168.1.200 2 china ~~~ 也可以只查指定分区的数据 ~~~ hive> select * from t_patition where country="china"; ~~~ 还可以创建多级分区 ~~~ hive> create table t_patition(ip string,duration int) > partitioned by(country string,city string) > row format delimited > fields terminated by ','; ~~~ 数据又分成了一半,在country文件夹下在分city文件夹 ### 表存储格式 stored as **创建表** ~~~ create table t_2(id int,name string) row format delimited fields terminated by ',' stored as textfile; ~~~ 填充外部文件数据 ~~~ [root@master ~]# cat name.txt 1,jdxia 2,xiaozhan ~~~ ~~~ hive> load data local inpath '/root/name.txt' into table t_2; ~~~ ~~~ STORED AS `SEQUENCEFILE|TEXTFILE|RCFILE` 如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE ~~~ 默认是TEXTFILE 创建个压缩的 ~~~ hive> create table t_3(id int,name string) > row format delimited > fields terminated by ',' > stored as SEQUENCEFILE; ~~~ 压缩表是不能用外部文件导入的,会报错他的导入值,可以用其他表insert进去 ~~~ hive> insert overwrite table t_3 select * from t_2; ~~~