1 设置会话级别的参数

# 显示 config 配置的值 # 格式 set <config>; # 示例 set hive.cli.print.current.db # 设置config配置的值 # 格式 set <config>=<config_value>; # 设置示例 set hive.cli.print.current.db=true;

疑问：调优的时候很多参数是可以调整的，    为什么不配置到全局的里面 (hive-site.xml 文件里都是全局) 去呢？ 答:  每个场景的作业情况不同, 调优是针对具体作业的，一个调优无法适应所有场景

 

2 查看表结构详细

hive> desc formatted course_select; OK col_name data_type comment # col_name data_type comment stu_id string the id of a student select_courses array<string> selected courses of a student # Detailed Table Information Database: default OwnerType: USER Owner: hdfs CreateTime: Sat Jul 04 16:04:56 CST 2020 LastAccessTime: UNKNOWN Protect Mode: None Retention: 0 Location: hdfs://cdh01:8020/user/hive/warehouse/course_select Table Type: EXTERNAL_TABLE Table Parameters: COLUMN_STATS_ACCURATE true EXTERNAL TRUE numFiles 1 totalSize 98 transient_lastDdlTime 1593850340 # Storage Information SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe InputFormat: org.apache.hadoop.mapred.TextInputFormat OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat Compressed: No Num Buckets: -1 Bucket Columns: [] Sort Columns: [] Storage Desc Params: colelction.delim , field.delim \t serialization.format \t Time taken: 0.138 seconds, Fetched: 34 row(s)

3 shell 脚本方式执行hive命令

3.1 hive -e "<hive command>"

hive> create external table if not exists student_roll( > stu_id int comment 'the id of a student', > basic_info struct<name:string,age:int> comment 'the info of a student', > roll_year string) > row format delimited > fields terminated by '\t' > collection items terminated by ','; OK Time taken: 0.079 seconds hive> select * from student_roll; OK student_roll.stu_id student_roll.basic_info student_roll.roll_year 8102101 {"name":"zhouyu","age":23} 2012 8102121 {"name":"huanggai","age":25} 2015 8102141 {"name":"liming","age":20} 2018 8102108 {"name":"XiaoXiu","age":24} 2013 Time taken: 0.058 seconds, Fetched: 4 row(s) # 实验hive -e 命令 [hdfs@cdh04 hive-app-data]$ vim select_student_roll.sh #!/bin/bash roll_year=$1 hive -e "select * from student_roll where roll_year='$roll_year'" # 执行脚本，这种方式便于传参，更为灵活 [hdfs@cdh04 hive-app-data]$ ./select_student_roll.sh 2018 Total MapReduce CPU Time Spent: 2 seconds 770 msec OK 8102141 {"name":"liming","age":20} 2018 Time taken: 21.038 seconds, Fetched: 1 row(s)

 

3.2 hive -f <sql脚本文件>

先删除上面创建的表

hive> drop table student_roll; OK Time taken: 0.125 seconds hive> > select * from student_roll; FAILED: SemanticException [Error 10001]: Line 1:14 Table not found 'student_roll' hive>

将创建表和查询数据的动作写入脚本文件

vim hive-sql02.sql

create external table if not exists student_roll( stu_id int comment 'the id of a student', basic_info struct<name:string,age:int> comment 'the info of a student', roll_year string) row format delimited fields terminated by '\t' collection items terminated by ','; load data local inpath '/var/lib/hadoop-hdfs/hive-app-data/student_roll.dat' overwrite into table student_roll; select * from student_roll;

hive -f hive-sql02.sql

[hdfs@cdh04 hive-app-data]$ hive -f hive-sql02.sql Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0 Java HotSpot(TM) 64-Bit Server VM warning: Using incremental CMS is deprecated and will likely be removed in a future release Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0 Logging initialized using configuration in jar:file:/opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/jars/hive-common-1.1.0-cdh5.16.2.jar!/hive-log4j.properties OK Time taken: 1.359 seconds Loading data to table default.student_roll Table default.student_roll stats: [numFiles=1, numRows=0, totalSize=99, rawDataSize=0] OK Time taken: 0.778 seconds OK 8102101 {"name":"zhouyu","age":23} 2012 8102121 {"name":"huanggai","age":25} 2015 8102141 {"name":"liming","age":20} 2018 8102108 {"name":"XiaoXiu","age":24} 2013 Time taken: 0.373 seconds, Fetched: 4 row(s) WARN: The method class org.apache.commons.logging.impl.SLF4JLogFactory#release() was invoked. WARN: Please see http://www.slf4j.org/codes.html#release for an explanation.