Hive配置和基本操作

1、實(shí)驗(yàn)報(bào)告（四）大數(shù)據(jù)存儲(chǔ)實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)名稱(chēng)：Hive操作姓名/學(xué)號(hào)：日期：實(shí)驗(yàn)環(huán)境：操作系統(tǒng)：LinuxHadoop版本：2.x版本HBase版本：1.1.1或以上Zookeeper版本：3.4.6或以上JDK版本：1.6或以上版本Java IDE：EclipseHIVE版本：實(shí)驗(yàn)內(nèi)容與完成情況：一：Hive的配置hive的安裝十分簡(jiǎn)單，只需要在一臺(tái)服務(wù)器上部署即可。上傳hive安裝包，解壓縮，將其配入環(huán)境變量。解壓Hive的Jar包后，進(jìn)入conf目錄，修改配置文件：cp hive-env.sh.template hive-env.shcp hive-default.xml.template

2、hive-default.xmlcp perties.template pertiescp perties.template perties在hive-env.sh文件中配置hadoop的home目錄。新建一個(gè)hive-site.xml文件并增加內(nèi)容<configuration><property><name>hive.exec.local.scratchdir</name><value>/usr/loc

3、al/hive/iotmp</value></property><property><name>hive.exec.scratchdir</name><value>/tmp/hive</value></property><property><name>hive.server2.logging.operation.log.location</name><value>/usr/local/hive/iotmp</value></prope

4、rty><property><name>hive.downloaded.resources.dir</name><value>/usr/local/hive/iotmp</value></property><property><name>hive.querylog.location</name><value>/usr/local/hive/iotmp</value></property><property><name>hi

5、ve.metastore.warehouse.dir</name><value>/user/hive/warehouse</value></property><property><name>javax.jdo.option.ConnectionDriverName</name><value>com.mysql.jdbc.Driver</value></property><property><name>javax.jdo.option.Connectio

6、nURL</name><value>jdbc:mysql:/localhost:3306/hive?characterEncoding=UTF-8</value></property><property><name>javax.jdo.option.ConnectionUserName</name><value>hive</value></property><property><name>javax.jdo.option.ConnectionPassw

7、ord</name><value>hive</value></property><property><name>hive.metastore.local</name><value>false</value></property><property><name>hive.metastore.uris</name><value>thrift:/localhost:9083</value></property>

8、</configuration>二：Hive的基本操作創(chuàng)建表：hive> CREATE TABLE pokes (foo INT, bar STRING);         Creates a table called pokes with two columns, the first being an integer and the other a string創(chuàng)建一個(gè)新表，結(jié)構(gòu)與其他一樣hive> create table new_table like records;創(chuàng)建分區(qū)表：hive>

9、 create table logs(ts bigint,line string) partitioned by (dt String,country String);加載分區(qū)表數(shù)據(jù)：hive> load data local inpath '/home/hadoop/input/hive/partitions/file1' into table logs partition (dt='2001-01-01',country='GB');展示表中有多少分區(qū)：hive> show partitions logs;展示所有表：hive&g

10、t; SHOW TABLES;        lists all the tableshive> SHOW TABLES '.*s'lists all the table that end with 's'. The pattern matching follows Java regularexpressions. Check out this link for documentation 顯示表的結(jié)構(gòu)信息hive> DESCRIBE invites;   &#

11、160;    shows the list of columns更新表的名稱(chēng)：hive> ALTER TABLE source RENAME TO target;添加新一列hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');刪除表：hive> DROP TABLE records;刪除表中數(shù)據(jù)，但要保持表的結(jié)構(gòu)定義hive> dfs -rmr /user/hive/warehouse/records;從本地文件加載數(shù)據(jù)：hive&

12、gt; LOAD DATA LOCAL INPATH '/home/hadoop/input/ncdc/micro-tab/sample.txt' OVERWRITE INTO TABLE records;顯示所有函數(shù)：hive> show functions;查看函數(shù)用法：hive> describe function substr;查看數(shù)組、map、結(jié)構(gòu)hive> select col10,col2'b',col3.c from complex;內(nèi)連接：hive> SELECT sales.*, things.* FROM sales

13、 JOIN things ON (sales.id = things.id);查看hive為某個(gè)查詢(xún)使用多少個(gè)MapReduce作業(yè)hive> Explain SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);外連接：hive> SELECT sales.*, things.* FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);hive> SELECT sales.*, things.* FROM sales

14、RIGHT OUTER JOIN things ON (sales.id = things.id);hive> SELECT sales.*, things.* FROM sales FULL OUTER JOIN things ON (sales.id = things.id);in查詢(xún)：Hive不支持，但可以使用LEFT SEMI JOINhive> SELECT * FROM things LEFT SEMI JOIN sales ON (sales.id = things.id);Map連接：Hive可以把較小的表放入每個(gè)Mapper的內(nèi)存來(lái)執(zhí)行連接操作hive> S

15、ELECT /*+ MAPJOIN(things) */ sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);INSERT OVERWRITE TABLE .SELECT：新表預(yù)先存在hive> FROM records2    > INSERT OVERWRITE TABLE stations_by_year SELECT year, COUNT(DISTINCT station) GROUP BY year     > INSERT OVERWRITE TABLE records_by_year SELECT year, COUNT(1) GROUP BY year    > INSERT OVERWRITE TABLE good_records_by_year SELECT year, COUNT(1) WHERE temperature != 9999 AND (quality = 0 OR quality = 1 OR quality = 4 OR quality = 5 OR quality = 9) GROUP BY year;  CREATE TABLE . AS