Hadoop数据仓库实战-Hive数据库及表操作课件.pptx

1、Hive数据库及表操作u熟悉Hive的数据类型u使用Hive管理雇员信息u使用JDBC操作雇员表本章任务2/29u掌握Hive的数据类型u掌握Hive数据库和表操作u理解Hive内部表和外部表u理解Hive数据分区、分桶u掌握Hive Shell命令行模式u掌握Hive Java API编程本章目标3/29u整型nTINYINT、SMALLINT、INT、BIGINTn使用整数面量时，默认是INT，其他整型需要加后缀，TINYINT、SMALLINT、BIGINT后缀为Y、S、Lu浮点型nFLOAT、DOUBLE、DECIMALu文本类型nSTRING、CHAR、VARCHARHive的基本数

2、据类型-14/29u布尔及二进制nBOOLEAN（布尔值型），值为true和falsenBINARY（二进制型）u时间类型nDATE，存储日期nTEMESTAMP，存储纳秒级别时间戳nINTERVAL，存储时间间隔Hive的基本数据类型-25/29uARRAYn具有相同类型变量的集合，如：“a”,”b”uMAPn一组键值对组合，如：“a”:”b”,”c”:”d”uSTRUCTn封装了一组有名字的字段，其类型可以是任意基本类型uUNIONTYPEn类似于Java中的泛型，任一时刻只有其中一种类型生效Hive的复杂数据类型6/29uHive DDL操作常用命令ncreate、drop、alter、

3、truncate、show、describeuHive数据库操作Hive数据库/表定义操作（DDL）-1#创建数据库CREATE(DATABASE|SCHEMA)IF NOT EXISTS database_name COMMENT database_comment LOCATION hdfs_path WITH DBPROPERTIES(property_name=property_value,.)#修改数据库ALTER(DATABASE|SCHEMA)database_nameSET DBPROPERTIES(property_name=property_value,.)#删除数据库DRO

4、P(DATABASE|SCHEMA)IF EXISTS database_name RESTRICT|CASCADE演示示例1：Hive数据库操作7/29uHive数据表操作-创建数据表Hive数据库/表定义操作（DDL）-2#创建数据表CREATE TEMPORARY EXTERNAL TABLE IF NOT EXISTS db_name.table_name(col_name data_type COMMENT col_comment,.constraint_specification)COMMENT table_comment PARTITIONED BY(col_name data_

5、type COMMENT col_comment,.)CLUSTERED BY(col_name,col_name,.)SORTED BY(col_name ASC|DESC,.)INTO num_buckets BUCKETS SKEWED BY(col_name,col_name,.)ON(col_value,col_value,.),(col_value,col_value,.),.)STORED AS DIRECTORIES ROW FORMAT row_format STORED AS file_format|STORED BY storage.handler.class.name

6、WITH SERDEPROPERTIES(.)LOCATION hdfs_path TBLPROPERTIES(property_name=property_value,.)演示示例2：Hive创建表操作8/29uHive数据表操作-修改数据表Hive数据库/表定义操作（DDL）-3#修改数据表表名ALTER TABLE table_name RENAME TO new_table_name#添加或替换列ALTER TABLE table_name ADD|REPLACE COLUMNS（col_name data_type）演示示例3：Hive修改数据表表名演示示例4：Hive表增加列9/2

7、9uHive DDL其他操作Hive数据库/表定义操作（DDL）-4命令示例说明dropdrop database database_name删除数据库drop table table_name删除表truncatetruncate table table_name清空表showshow databases显示所有数据库名show tables列出当前库中所有表show views列出当前库中所有视图show partitions table_name列出表中所有分区show create table_name显示建表语句show functions“a.*”列出所有函数describedes

8、cribe database databse_name显示数据库信息describe table_name显示表信息describe table_name.col_name显示表中某列的信息10/29uHive创建分区表语句Hive定义分区/分桶表CREATE TABLE employee_partitioned(name string,work_place ARRAY,sex_age STRUCT,skills_score MAP,depart_title MAPSTRING,ARRAY)PARTITIONED BY(year INT,month INT)CLUSTERED BY(emplo

9、yee_id)INTO 2 BUCKETSROW FORMAT DELIMITEDFIELDS TERMINATED BY|COLLECTION ITEMS TERMINATED BY,MAP KEYS TERMINATED BY:;定义按哪个字段分区定义按哪个字段分区定义按哪个字段定义按哪个字段分桶分分桶分桶桶11/29uHive分区表描述回顾n分区列的值将表划分为文件夹n查询时使用“分区”列和常规列类似n查询时Hive自动过滤掉不用于提高性能的分区uHive分桶表描述回顾n分桶是相对分区进行更细粒度的划分n根据“桶列”的哈希函数将数据进行分桶n更高的查询处理效率Hive分区表/分桶表描述1

10、2/29uHive分区n静态分区n动态分区uHive分桶n动态分桶Hive操作分区/分桶类型及设置#使用动态分区需设置set hive.exec.dynamic.partition=true;set hive.exec.dynamic.partition.mode=nonstrict;#使用动态分桶需设置set hive.enforce.bucketing=true;13/29u静态分区操作示例u动态分区操作示例Hive操作分区ALTER TABLE employee_partitioned ADD PARTITION(year=2017,month=3)PARTITION(year=2017

11、,month=4);ALTER TABLE employee_partitioned DROP PARTITION(year=2017,month=4);INSERT INTO TABLE employee_partitioned PARTITION(year,month)SELECT name,array(Toronto)AS work_place,named_struct(sex,male,age,30)AS sex_age,map(python,90)AS skills_score,map(r&d,array(developer)AS depart_title,year(start_da

12、te)AS year,month(start_date)AS monthFROM employee_hr;14/29u随机抽样基于整行数据u随机抽样基于指定列（使用分桶列更高效）u随机抽样基于block sizeHive操作分桶SELECT*FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON rand()s;SELECT*FROM table_name TABLESAMPLE(10 PERCENT)s;SELECT*FROM table_name TABLESAMPLE(1M)s;SELECT*FROM table_name TABLESAMPL

13、E(10 rows)s;SELECT*FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON id)s;15/29uHive DML操作常用命令nload、insert、update、delete、merge、explain等u数据装载nload命令实现，将数据复制或移动到Hive表对应的位置ninsert命令实现，执行MR作业将数据插入Hive表中Hive DML操作-1LOAD DATALOCAL INPATH filepathOVERWRITE INTO TABLE table_name PARTITION(partcol1=val1,part

14、col2=val2,);INSERT(INTO|OVERWRITE)TABLE table_name PARTITIONPARTITION(partcol1=val1,partcol2=val2,)select_statement1 FROM from_statement;演示示例6：使用inser t完成数据装载演示示例5：使用load完成数据装载16/29uHive 更新、删除、合并数据n执行这些操作需要开启ACID（事务）支持nHive是数据仓库解决方案，不适合做这种操作u数据导入/导出n使用import/export实现数据导入/导出Hive DML操作-2#数据导入IMPORT EX

15、TERNAL TABLE new_or_original_tablename PARTITION(partcol1=val1,)FROM source_path LOCATION import_target_path#数据导出EXPORT TABLE table_name PARTITION(partcol1=val1,)TO export_target_path17/29uHive分区表描述回顾n分区列的值将表划分为文件夹n查询时使用“分区”列和常规列类似n查询时Hive自动过滤掉不用于提高性能的分区uHive分桶表描述回顾n分桶是相对分区进行更细粒度的划分n根据“桶列”的哈希函数将数据进行

16、分桶n更高的查询处理效率Hive分区表/分桶表描述18/29uHive创建分区表语句Hive定义分区/分桶表CREATE TABLE employee_partitioned(name string,work_place ARRAY,sex_age STRUCT,skills_score MAP,depart_title MAPSTRING,ARRAY)PARTITIONED BY(year INT,month INT)CLUSTERED BY(employee_id)INTO 2 BUCKETSROW FORMAT DELIMITEDFIELDS TERMINATED BY|COLLECTI

17、ON ITEMS TERMINATED BY,MAP KEYS TERMINATED BY:;定义按哪个字段分区定义按哪个字段分区定义按哪个字段定义按哪个字段分桶分分桶分桶桶19/29演示示例7：分区表与数据装载演示示例8：分桶表与数据插入uHive分区n静态分区n动态分区uHive分桶n动态分桶Hive操作分区/分桶类型及设置#使用动态分区需设置set hive.exec.dynamic.partition=true;set hive.exec.dynamic.partition.mode=nonstrict;#使用动态分桶需设置set hive.enforce.bucketing=true

18、;20/29u静态分区操作示例u动态分区操作示例Hive操作分区ALTER TABLE employee_partitioned ADD PARTITION(year=2017,month=3)PARTITION(year=2017,month=4);ALTER TABLE employee_partitioned DROP PARTITION(year=2017,month=4);INSERT INTO TABLE employee_partitioned PARTITION(year,month)SELECT name,array(Toronto)AS work_place,named_s

19、truct(sex,male,age,30)AS sex_age,map(python,90)AS skills_score,map(r&d,array(developer)AS depart_title,year(start_date)AS year,month(start_date)AS monthFROM employee_hr;21/29u随机抽样基于整行数据u随机抽样基于指定列（使用分桶列更高效）u随机抽样基于block sizeHive操作分桶SELECT*FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON rand();SELECT

20、*FROM table_name TABLESAMPLE(10 PERCENT);SELECT*FROM table_name TABLESAMPLE(1M);SELECT*FROM table_name TABLESAMPLE(10 rows);SELECT*FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON id);22/29uHive命令行操作 n-i:从文件初始化HQLn-e:从命令行执行指定的HQLn-f:执行HQL脚本，与-i作用类似n-S：静默模式n-d:自定义变量，以便在HQL脚本中使用n-hiveconf x=y:配置Hive/

21、Hadoop参数Hive Shell操作hive-hiveconf x=y*|-S-d演示示例911：使用Hive命令行操作数据23/29u配置文件配置n配置$HIVE_HOME/conf/hive-site.xml，全局永久配置u命令行参数配置n启动Hive时，可以添加命令来设定参数uSET参数声明配置n在HQL中使用SET关键字设定参数Hive参数配置方式hive hiveconf hive.root.logger=INFO.console#参数设置只对本次session有效set mapred.reduc.tasks=100#参数设置只对本次session有效24/29u关键步骤：n创建

22、数据库empdb，并使用该数据库n新建分区表emp_hr_partitionedn包含字段pname、employee_id、sin_number、start_datep按照入职时间中的年份（year）进行分区操作n使用desc查看表的信息n通过查询语句向表中插入数据n使用HQL语句查询一共有多少条数据学员操作1使用HiveShell命令进行操作完成时间：30分钟25/29u常见问题及解决办法u代码规范问题u调试技巧共性问题集中讲解共性问题集中讲解 26/29u开发环境搭建n启动HiveServer2n在IDEA中创建maven项目hive-javaapin添加Maven依赖包hive-jdb

23、cphivephive-jdbc使用Hive JavaAPI进行操作$hive service hiveserver2&27/29u创建数据库连接nJDBC连接数据库需要提供四个必要参数p驱动类”org.apache.hive.jdbc.HiveDriver”p连接地址”jdbc:hive2:/HOSTNAME:10000”p用户名p密码使用JDBC操作Hive数据库演示示例1216：使用JDBC操作Hive28/29u关键步骤：n创建Maven项目n使用JDBC方式连接HiveServer2n使用JDBC API创建数据库n使用JDBC API创建表emp_id_bucketsn使用JDBC API对表做数据装载，查询等操作学员操作2使用JDBC API进行操作完成时间：30分钟29/29u常见问题及解决办法u代码规范问题u调试技巧共性问题集中讲解共性问题集中讲解 30/29uHive的基本数据类型有n整数类型、浮点类型、文本类型、布尔及二进制、时间类型等等uHive的复杂数据类型有nARRAY、MAP、STRUCT、UNIONuHive表的类型有n内部表、外部表、分区表、分桶表uHive表的常用操作是表创建、数据装载、查询u使用Hive JavaAPI操作前需要先启动HiveServer2服务总结31/29