hive表信息查询：查看表结构、表操作等

向右看齐 2022-06-07 08:49 294阅读 0赞

<table style="word-wrap:break-word; empty-cells:show; border-collapse:collapse; table-layout:fixed; width:757px; color:rgb(68,68,68); font-family:Tahoma,&quot;Microsoft Yahei&quot;,Simsun; font-size:12px"> 
 <tbody style="word-wrap:break-word"> 
 <tr style="word-wrap:break-word"> 
 <td style="word-wrap:break-word; font-size:14px"> 
 <div style="word-wrap:break-word; float:right; overflow:hidden; margin-left:10px; width:auto"> 
 </div> 问题导读： 1.如何查看hive表结构？ 2.如何查看表结构信息？ 3.如何查看分区信息？ 4.哪个命令可以模糊搜索表？ <img src="http://www.aboutyun.com/static/image/hrline/4.gif" alt="" style="word-wrap:break-word"> 1.hive模糊搜索表 &nbsp;&nbsp;show tables like '*name*'; 2.查看表结构信息 &nbsp;&nbsp;desc formatted table_name; &nbsp;&nbsp;desc table_name; 3.查看分区信息 &nbsp;&nbsp;show partitions table_name; 4.根据分区查询数据 &nbsp;&nbsp;select table_coulm from table_name where partition_name = '2014-02-25'; 5.查看hdfs文件信息 &nbsp;&nbsp;dfs -ls /user/hive/warehouse/table02; 6.从文件加载数据进表(OVERWRITE覆盖,追加不需要OVERWRITE关键字) &nbsp;&nbsp;LOAD DATA LOCAL INPATH 'dim_csl_rule_config.txt' OVERWRITE into table dim.dim_csl_rule_config; &nbsp;&nbsp;--从查询语句给table插入数据 &nbsp;&nbsp;INSERT OVERWRITE TABLE test_h02_click_log PARTITION(dt) select *&nbsp; &nbsp;&nbsp;from stage.s_h02_click_log where dt='2014-01-22' limit 100; 7.导出数据到文件 &nbsp;&nbsp;insert overwrite directory '/tmp/csl_rule_cfg' select a.* from dim.dim_csl_rule_config a; &nbsp;&nbsp;hive -e "select day_id,pv,uv,ip_count,click_next_count,second_bounce_rate,return_visit,pg_type from tmp.tmp_h02_click_log_baitiao_ag_sum where day_id in ('2014-03-06','2014-03-07','2014-03-08','2014-03-09','2014-03-10');"&gt; /home/jrjt/testan/baitiao.dat; 8.自定义udf函数 &nbsp;&nbsp;1.继承UDF类 &nbsp;&nbsp;2.重写evaluate方法 &nbsp;&nbsp;3.把项目打成jar包 &nbsp;&nbsp;4.hive中执行命令add jar /home/jrjt/dwetl/PUB/UDF/udf/GetProperty.jar; &nbsp;&nbsp;5.创建函数create temporary function get_pro as 'jd.Get_Property'//jd.jd.Get_Property为类路径; 9.查询显示列名 及 行转列显示&nbsp; &nbsp;&nbsp;set hive.cli.print.header=true; // 打印列名 &nbsp;&nbsp;set hive.cli.print.row.to.vertical=true; // 开启行转列功能, 前提必须开启打印列名功能 &nbsp;&nbsp;set hive.cli.print.row.to.vertical.num=1; // 设置每行显示的列数 10.查看表文件大小,下载文件到某个目录,显示多少行到某个文件 &nbsp; &nbsp;dfs -du hdfs://BJYZH3-HD-JRJT-4137.jd.com:54310/user/jrjt/warehouse/stage.db/s_h02_click_log; &nbsp; &nbsp;dfs -get /user/jrjt/warehouse/ods.db/o_h02_click_log_i_new/dt=2014-01-21/000212_0 /home/jrjt/testan/; &nbsp; &nbsp;head -n 1000 文件名 &gt; 文件名 11.杀死某个任务&nbsp;&nbsp;不在hive shell中执行 &nbsp; &nbsp;hadoop job -kill job_201403041453_58315 12.hive-wui路径 &nbsp; &nbsp;<a href="http://172.17.41.38/jobtracker.jsp" style="word-wrap:break-word; color:rgb(51,102,153)" rel="nofollow">http://172.17.41.38/jobtracker.jsp</a> 13.删除分区 &nbsp; &nbsp;alter table tmp_h02_click_log_baitiao drop partition(dt='2014-03-01'); &nbsp; &nbsp;alter table d_h02_click_log_basic_d_fact drop partition(dt='2014-01-17'); 14.hive命令行操作 &nbsp; &nbsp;执行一个查询,在终端上显示mapreduce的进度，执行完毕后，最后把查询结果输出到终端上，接着hive进程退出，不会进入交互模式。 &nbsp; &nbsp;hive -e 'select table_cloum from table' &nbsp; &nbsp;-S，终端上的输出不会有mapreduce的进度，执行完毕，只会把查询结果输出到终端上。这个静音模式很实用，,通过第三方程序调用，第三方程序通过hive的标准输出获取结果集。 &nbsp; &nbsp;hive -S -e 'select table_cloum from table' &nbsp; &nbsp;执行sql文件 &nbsp; &nbsp;hive -f hive_sql.sql 15.hive上操作hadoop文件基本命令 &nbsp; &nbsp; 查看文件大小 &nbsp; &nbsp; dfs -du /user/jrjt/warehouse/tmp.db/tmp_h02_click_log/dt=2014-02-15; &nbsp; &nbsp; 删除文件 &nbsp; &nbsp; dfs -rm /user/jrjt/warehouse/tmp.db/tmp_h02_click_log/dt=2014-02-15; 16.插入数据sql、导出数据sql &nbsp; &nbsp; 1.insert 语法格式为： &nbsp; &nbsp; 基本的插入语法： &nbsp; &nbsp; INSERT OVERWRITE TABLE tablename [PARTITON(partcol1=val1,partclo2=val2)]select_statement FROM from_statement &nbsp; &nbsp; insert overwrite table test_insert select * from test_table; &nbsp; &nbsp; 对多个表进行插入操作： &nbsp; &nbsp; FROM fromstatte &nbsp; &nbsp; INSERT OVERWRITE TABLE tablename1 [PARTITON(partcol1=val1,partclo2=val2)]select_statement1 &nbsp; &nbsp; INSERT OVERWRITE TABLE tablename2 [PARTITON(partcol1=val1,partclo2=val2)]select_statement2 &nbsp; &nbsp; from test_table&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; insert overwrite table test_insert1&nbsp; &nbsp; &nbsp; select key &nbsp; &nbsp; insert overwrite table test_insert2 &nbsp; &nbsp; select value; &nbsp; &nbsp; insert的时候，from子句即可以放在select 子句后面，也可以放在 insert子句前面。 &nbsp; &nbsp; hive不支持用insert语句一条一条的进行插入操作，也不支持update操作。数据是以load的方式加载到建立好的表中。数据一旦导入就不可以修改。 &nbsp; &nbsp; 2.通过查询将数据保存到filesystem &nbsp; &nbsp; INSERT OVERWRITE [LOCAL] DIRECTORY directory SELECT.... FROM ..... &nbsp; &nbsp; 导入数据到本地目录： &nbsp; &nbsp; insert overwrite local directory '/home/zhangxin/hive' select * from test_insert1; &nbsp; &nbsp; 产生的文件会覆盖指定目录中的其他文件，即将目录中已经存在的文件进行删除。 &nbsp; &nbsp; 导出数据到HDFS中： &nbsp; &nbsp; insert overwrite directory '/user/zhangxin/export_test' select value from test_table; &nbsp; &nbsp; 同一个查询结果可以同时插入到多个表或者多个目录中： &nbsp; &nbsp; from test_insert1 &nbsp; &nbsp; insert overwrite local directory '/home/zhangxin/hive' select *&nbsp; &nbsp; &nbsp; insert overwrite directory '/user/zhangxin/export_test' select value; 17.mapjoin的使用 应用场景：1.关联操作中有一张表非常小 2.不等值的链接操作 &nbsp; &nbsp; select /*+ mapjoin(A)*/ f.a,f.b from A t join B f&nbsp;&nbsp;on ( f.a=t.a and f.ftime=20110802)&nbsp; 18.perl启动任务 &nbsp; &nbsp;perl /home/jrjt/dwetl/APP/APP/A_H02_CLICK_LOG_CREDIT_USER/bin/a_h02_click_log_credit_user.pl&nbsp; &nbsp; &nbsp;APP_A_H02_CLICK_LOG_CREDIT_USER_20140215.dir &gt;&amp; /home/jrjt/dwetl/LOG/APP/20140306/a_h02_click_log_credit_user.pl.4.log 19.查看perl进程 &nbsp; &nbsp;ps -ef|grep perl 20.hive命令移动表数据到另外一张表目录下并添加分区 &nbsp; &nbsp;dfs -cp /user/jrjt/warehouse/tmp.db/tmp_h02_click_log/dt=2014-02-18 /user/jrjt/warehouse/ods.db/o_h02_click_log/; &nbsp; &nbsp;dfs -cp /user/jrjt/warehouse/tmp.db/tmp_h02_click_log_baitiao/* /user/jrjt/warehouse/dw.db/d_h02_click_log_baitiao_basic_d_fact/;--复制所有分区数据 &nbsp; &nbsp;alter table d_h02_click_log_baitiao_basic_d_fact add partition(dt='2014-03-11') location '/user/jrjt/warehouse/dw.db/d_h02_click_log_baitiao_basic_d_fact/dt=2014-03-11'; 21.导出白条数据 &nbsp; &nbsp; hive -e "select day_id,pv,uv,ip_count,click_next_count,second_bounce_rate,return_visit,pg_type from tmp.tmp_h02_click_log_baitiao_ag_sum where day_id like '2014-03%';"&gt; /home/jrjt/testan/baitiao.xlsx; 22.hive修改表名 &nbsp; &nbsp; ALTER TABLE o_h02_click_log_i RENAME TO o_h02_click_log_i_bk; 23.hive复制表结构 &nbsp; &nbsp;CREATE TABLE d_h02_click_log_baitiao_ag_sum LIKE tmp.tmp_h02_click_log_baitiao_ag_sum; 24.hive官网网址 &nbsp; &nbsp;<a href="https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-InstallationandConfiguration" style="word-wrap:break-word; color:rgb(51,102,153)" rel="nofollow">https://cwiki.apache.org/conflue ... ionandConfiguration</a> &nbsp; &nbsp;<a href="http://www.360doc.com/content/12/0111/11/7362_178698714.shtml" style="word-wrap:break-word; color:rgb(51,102,153)" rel="nofollow">http://www.360doc.com/content/12/0111/11/7362_178698714.shtml</a> 25.hive添加字段 &nbsp; &nbsp;alter table tmp_h02_click_log_baitiao_ag_sum add columns(current_session_timelenth_count bigint comment '页面停留总时长'); &nbsp; &nbsp;ALTER TABLE tmp_h02_click_log_baitiao CHANGE current_session_timelenth current_session_timelenth bigint comment '当前会话停留时间'; 26.hive开启简单模式不启用mr &nbsp; &nbsp;set hive.fetch.task.conversion=more; 27.以json格式输出执行语句会读取的input table和input partition信息 &nbsp; &nbsp;Explain dependency query &nbsp;</td> 
 </tr> 
 </tbody> 
</table>