hive自学代码[自学笔记]

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

1. # start
   
2. sh $HIVE_HOME/bin/hive --service hwi
   
3. 
   
4. hadoop fs -text /user/admin/daiqf/createspu_fp/input/cateinfo | head
   
5. 
   
6. # 005 means the tab
   
7. CREATE TABLE IF NOT EXISTS table_name ROW FORMAT DELIMITED FIELDS TERMINATED BY '\005' STORED AS textfile
   
8. 
   
9. hadoop fs -ls
   
10. 
    
11. # partition
    
12. # 1.build table
    
13. CREATE TABLE c02_clickstat_fatdtl
    
14. (
    
15.         yyyymmdd string,
    
16.         id int,
    
17.         ip string,
    
18.         country string,
    
19.         cookie_id string,
    
20.         page_id string,
    
21.         clickstat_url_id int,
    
22.         query_string string,
    
23.         refer string
    
24. )PARTITIONED BY(dt STRING)
    
25. row format delimited fields terminated by '\005'
    
26. # load data
    
27. LOAD DATA INPATH '/user/admin/SqlldrDat/CnClickstat/20101101/19/clickstat_gp_fatdt0/0'
    
28. OVERWRITE INTO TABLE c02_clickstat_fatdtl PARTITION(dt='20101101');
    
29. # visit a partition
    
30. SELECT count(*)
    
31. FROM c02_clickstat_fatdtl a
    
32. WHERE a.dt >= '20101101'  AND a.dt < '20101102';
    
33. select * from ods_dpi_mob_data_log a where a.dt >= '2014051814'  and a.dt < '2014051815'
    
34. # add a partition
    
35. alter table ods_dpi_mob_data_log add partition(dt='2014051814')
    
36. show partitions ods_dpi_mob_data_log;
    
37. 
    
38. ALTER TABLE table_name RENAME TO new_table_name
    
39. 
    
40. hive> desc xi;
    
41. OK
    
42. id int
    
43. cont string
    
44. dw_ins_date string
    
45. hive> create table xibak like xi;
    
46. OK
    
47. # add
    
48. hive> alter table xibak add columns (ins_date1 string);
    
49. OK
    
50. hive> desc xibak;
    
51. OK
    
52. id int
    
53. cont string
    
54. dw_ins_date string
    
55. ins_date1 string
    
56. # repalce all
    
57. hive> alter table xibak repalce columns (ins_date2 string);
    
58. OK
    
59. hive> desc xibak;
    
60. OK
    
61. ins_date2 string
    
62. 
    
63. SHOW TABLES
    
64. SHOW TABLES 'page.*'
    
65. SHOW TABLES '.*view'
    
66. SHOW PARTITIONS page_view
    
67. DESCRIBE invites
    
68. SELECT a.foo FROM invites a WHERE a.ds='2008-08-15'
    
69. SELECT a.foo FROM invites a limit 3;
    
70. DESCRIBE EXTENDED page_view PARTITION (ds='2008-08-08')
    
71. 
    
72. # add to the origin table
    
73. LOAD DATA LOCAL INPATH '/tmp/pv_2008-06-08_us.txt' INTO TABLE c02 PARTITION(date='2008-06-08', country='US')
    
74. LOAD DATA LOCAL INPATH './examples/files/kvl.txt' INTO TABLE pokes;
    
75. # overwirte
    
76. LOAD DATA INPATH '/user/admin/SqlldrDat/CnClickstat/20101101/19/clickstat_gp_fatdt0/0'
    
77. OVERWRITE INTO TABLE c02_clickstat_fatdtl PARTITION(dt='20101101');
    
78. 
    
79. hive> FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(*)
    
80. WHERE a.foo > 0 GROUP  BY a.bar;
    
81. hiev> INSERT OVERWRITE TABLE events SELECT a.bar, count(*) FROM invites a
    
82. WHERE a.foo > 0 GROUP  BY a.bar;
    
83. 
    
84. SELECT a.* FROM a JOIN b ON (a.id = b.id)
    
85. SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)
    
86. SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
    
87. 
    
88. SELECT a.val, b.val, c.val
    
89. FROM OUTER JOIN b ON (a.key = b.key)
    
90. WHERE a.ds='2009-07-07' AND b.ds = '2009-07-07'
    
91. 
    
92. SELECT a.val, b.val FROM a LEFT OUTER JOIN b
    
93. ON (a.key = b.key AND b.ds = '2009-07-07' AND a.ds = '2009-07-07')
    
94. 
    
95. # hive
    
96. # -i <filename>   initialization sql from file
    
97. # -e 'quoted query string'     sql from command line
    
98. # -f <filename>     sql from file
    
99. 
    
100. 
     
101. 
     
102. hive -e "use ku; show tables"        #查看ku库中的所有表
     
103. hive -e "show databases"        #查看所有的databases
     
104.         show tables like '*name*'        #查看名字包含name的数据库
     
105.         desc formatted table_name        #查看数据库的结构
     
106.         lbs_lifedata_         
     
107.         lbs_lifebase_s
     
108. desc formatted lbs_lifedata_lbs_batch_plan
     
109. hive -S -e "select * FROM mytable LIMIT 3"
     
110. less -SN table.desc        #可以查看table按照格式
     
111. 
     
112. hadoop fs -lsr /apps/hduser4899
     
113. hadoop fs -dus /apps/hduser4899/heater/heater_agraph
     
114. hadoop fs -get /apps/hduser4899/heater/heater_idno_name/* ./
     
115. 
     
116. table=$1
     
117. hive -e "select * from $1 limit 1000" | gzip > $1.1000.gz

复制代码

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏1 回帖

关键词：Hive HIV Department partition formatted