HIVE入门_1

RUNNING HIVE

$ $HADOOP_HOME/bin/hadoop fs -mkdir       /tmp
$ $HADOOP_HOME/bin/hadoop fs -mkdir       /user/hive/warehouse
$ $HADOOP_HOME/bin/hadoop fs -chmod g+w   /tmp
$ $HADOOP_HOME/bin/hadoop fs -chmod g+w   /user/hive/warehouse
$ export HIVE_HOME=<hive-install-dir>

#Running Hive CLI
$ $HIVE_HOME/bin/hive

#Running HiveServer2 and Beeline
$ $HIVE_HOME/bin/hiveserver2
$ $HIVE_HOME/bin/beeline -u jdbc:hive2://$HS2_HOST:$HS2_PORT

$ $HIVE_HOME/bin/beeline -u jdbc:hive2://

DDL

Data Definition Language

#Creating Hive Tables
hive> CREATE TABLE pokes (foo INT, bar STRING);
hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

#Browsing through Tables
hive> SHOW TABLES;
hive> SHOW TABLES '.*s';  # follows Java regular expressions.
hive> DESCRIBE invites;

#Altering and Dropping Tables
hive> ALTER TABLE events RENAME TO 3koobecaf;
hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);
hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');
hive> ALTER TABLE invites REPLACE COLUMNS (foo INT, bar STRING, baz INT COMMENT 'baz replaces new_col2');

hive> ALTER TABLE invites REPLACE COLUMNS (foo INT COMMENT 'only keep the first column');

hive> DROP TABLE pokes;

Metadata Store

Metadata is in an embedded Derby database whose disk storage location is determined by the Hive configuration variable named javax.jdo.option.ConnectionURL. By default this location is ./metastore_db (see conf/hive-default.xml).

DML

Data Manipulation Language

#loading
hive> LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;

hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');
hive> LOAD DATA LOCAL INPATH './examples/files/kv3.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-08');

#The above command will load data from an HDFS file/directory to the table.
#Note that loading data from HDFS will result in moving the file/directory. As a result, the operation is almost instantaneous.
hive> LOAD DATA INPATH '/user/myname/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');

SQL

Examples are shown: Some example queries are shown below. They are available in build/dist/examples/queries. More are available in the Hive sources at ql/src/test/queries/positive.

#SELECTS and FILTERS
hive> SELECT a.foo FROM invites a WHERE a.ds='2008-08-15';
hive> INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHERE a.ds='2008-08-15';
hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;

hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a;
hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a WHERE a.key < 100;
hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/reg_3' SELECT a.* FROM events a;
hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_4' select a.invites, a.pokes FROM profiles a;
hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT COUNT(*) FROM invites a WHERE a.ds='2008-08-15';
hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT a.foo, a.bar FROM invites a;
hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/sum' SELECT SUM(a.pc) FROM pc1 a;

#GROUP BY
hive> FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(*) WHERE a.foo > 0 GROUP BY a.bar;
hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(*) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;

#JOIN
hive> FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar) INSERT OVERWRITE TABLE events SELECT t1.bar, t1.foo, t2.foo;

#MULTITABLE INSERT
FROM src
INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100
INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200
INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;

#STREAMING
hive> FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING '/bin/cat' WHERE a.ds > '2008-08-09';

Simple Example Use Cases

参考资料

  1. get started

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏大数据学习笔记

MySQL/MariaDB SQL操作笔记

1、修改表字段类型 alter table nodes modify column info varchar(200) 示例操作入下: MariaDB [dbm...

1925
来自专栏清风

hadoop hive 与 Oracle 互相导入数据

713
来自专栏数据分析

Database、Table的所有约束

列出Database或Table的所有约束 很多时候我们想使用像 INSERT、UPDATE、DELETE 这样的DML命令。有时候因为某个表被设置约束,导致我...

2674
来自专栏源码之家

mysql部分替换

1435
来自专栏ml

C线程同步/异步

1 #include<windows.h> 2 #include<stdio.h> 3 #include<string.h> 4 //#include ...

2847
来自专栏大内老A

Audit Logging-Stored Procedure

1. T_ORDER For Insert: sp_order_i IF EXISTS (SELECT * FROM sysobjects WHERE type...

1907
来自专栏乐沙弥的世界

使用DBMS_REDEFINITION在线切换普通表到分区表

      随着数据库数据量的不断增长,有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作,诸如导出表数据,然后创建分区表再导入数据到分区...

832
来自专栏深度学习之tensorflow实战篇

SQl 语句(常见) 新建,删除,修改表,新增字段,修改默认值

SQl 语句(常见) 新建,删除,修改表结构 新建表: create table [表名] ( [自动编号字段] int IDENTITY (1,1) PRIM...

2939
来自专栏听雨堂

一次数据库的整理的sql语句

//查询以井结束的记录 SELECT f_wellnumber, SUBSTRING(f_wellnumber, 1, LEN(f_wellnumber) - ...

1759
来自专栏跟着阿笨一起玩NET

通过存储过程进行分页查询的SQL示例

791

扫码关注云+社区