数据表操作技巧分享

使用“use apachedb；”切换到apachedb 数据库，关于表的操作都在apachedb 数据库中进行。

（1）创建表

数据表包括内部表、外部表、分区表和桶表，它们有不同的特征。创建表有两种不同的方式：按照数据目录，可以创建内部表和外部表；按照数据的管理方式，可以创建分区表和桶表。创建表的语法格式为：

上述语法格式的说明如下：

①external 关键字用于指定创建外部表，若未指定，默认是创建内部表。

②可以使用comment 关键字对表的字段和表添加注释。

③partitioned by （column data_type，…）：用于创建分区表，指定分区的字段，且partitioned里的字段不能是表中声明的字段，必须是一个新字段。

④clustered by （column1，column2，…）：用于创建桶表，指定按照哪些字段进行分桶。clustered 里的字段必须要是表字段中出现的字段。

⑤row format delimited：用于指定行列的数据格式或分隔符，例如，可以指定字段间使用“\t”间隔，行间使用“\n”分隔。使用stored as 指定文件的存储格式，默认为textfile。

⑥location：创建外部表时需要使用location 来指定表的存储位置，且location 的位置必须是目录，不能是单个文件。

⑦as select_statement：查询建表法，通过复制另一张表的结构和表中所有数据来创建新表。

⑧like existing_table：通过复制另一张表的结构来建表，不复制表中的数据。

例如，分别用不同的方式创建一个员工表。

1）创建内部表

可按如下方式创建内部表“employee”，包括属性id（int 类型）、name（string 类型）、sex（string 类型）、age（int 类型）、department（string 类型）。字段之间使用“，”号进行分隔，行之间使用“\n”分隔。

2）创建外部表

创建外部表需要指定关键字“external”，一般还需要指定一个外部路径，默认的路径是“/user/hive/warehouse/apachedb.db”下的以表名命名的目录。具体命令为：

需要先创建好数据存储目录“/home/apache/data/hive/extbdata”。

3）创建分区表

创建一个分区表“employee_partition”，以city 进行分区。

4）创建桶表

在创建桶表之前，需要先设置“set hive.enforce.bucketing =true”开启分桶功能，然后才能创建桶表。例如，将employee 表按照id 字段划分为4 个桶。

（2）查看表

使用“show tables；”命令可查看数据库里面的所有表。如图7.11 所示，可以看到数据库apachedb 中有刚刚创建的4 个表。

图7.11　查看数据库中的表

使用关键字“desc”可查看某个表的字段信息。例如，查看employee 表的结构信息如图7.12所示。

图7.12　查看employee 表结构信息

再加上关键字“formatted”，可以查看更详细的表信息。如图7.13 显示的是查看“employee_external”的详细信息。

(www.xing528.com)

图7.13　查看employee_external 详情

（3）修改表

可以使用“alter table”命令来修改表的属性，该命令只是修改表的元数据，表中的数据不受影响。因此，需要保证表中的数据要与修改后的元数据模式匹配，否则原始表中的数据将变得不可用。

1）修改表的名称

语法为“alter table table_name rename to new_table_name；”。例如，将employee 表的名称改为“employee2”。

2）修改表字段的定义

①增加列

使用add columns 可以为表增加一个或多个列。例如，为employee2 表增加string 类型的province 列和bigint 类型的salary 列，如图7.14 所示。新添加的列位于该表的最后。

②修改列定义

使用change 可以修改列名称、列注释、列类型和列的位置。例如，将employee2 表的“id”字段重命名为“uid”，注释修改为“the unique id”，且将位置放在name 之后。如图7.15 所示。

③替换字段

使用replace 将表中的所有字段替换成新的字段，相当于将整个表结构进行了重置。例如，将employee2 表中的字段替换为id（int 类型）、name（string 类型）和department（string 类型）。如图7.16 所示。

图7.14　增加新列

图7.15　修改“id”字段信息

图7.16　替换字段

3）对分区表的分区进行操作

为了方便进行测试，先使用like 命令将“employee_partition”表复制一个新的分区表“employee_partition2”。

①增加分区

在“employee_partiton2”表中增加city 为beijing 和tianjin 两个分区。

可使用“show partitions”命令查看分区情况，如图7.17 所示。

图7.17　查看分区

分区对应在HDFS 上表现为数据存储目录，如图7.18 所示。“employee_partition2”数据表目录下新增“city=beijing”和“city=tianjian”两个目录。

图7.18　新增分区

②删除分区

删除“employee_partition2”表中的分区“city = 'beijing'”和“city = 'tianjin'”。如图7. 19所示。

图7.19　删除分区

删除分区并没有删除HDFS 上对应的目录，删除分区只是删除了元数据，对实际数据没有影响。

（4）删除表

使用drop 命令可以删除表，例如，“drop table employee；”命令可删除employee 表。