hive sql 基本语法

1、创建库
hive> CREATE DATABASE test_hive;
OK
Time taken: 0.197 seconds
hive> show databases;
OK
default
test_hive
Time taken: 0.009 seconds, Fetched: 2 row(s)
2、创建表
hive> use test_hive;
OK
Time taken: 0.01 seconds
hive> create table x (a INT);
OK

对应hdfs上生成两个目录
/usr/hive/warehouse 这个目录存储hive中的表和数据库(默认情况下)，从这里也可以看出hive本身是不存储数据的， 数据存储在HDFS上
hadoop fs -ls /user/hive/warehouse/test_hive.db/x

创建外部表，语法完整些
create EXTERNAL TABLE w_a
(
usrid STRING,
age STRING,
sex STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’;

external：表示创建一个外部表
ROW FORMAT DELIMITED FIELDS TERMINATED BY 表示行分隔符使用tab制表符分割
LINES TERMINATED BY 行结尾符是回车。
查看表的结构
hive> desc test_external_table;
OK
userid string
age string
sex string
Time taken: 0.065 seconds, Fetched: 3 row(s)

3、像表中插入数据。注意hive中没有insert into 这类语句，一般向表中插入数据都是加载一个文件
hive> LOAD DATA LOCAL INPATH ‘/data/project/data/a.txt’ OVERWRITE INTO TABLE test_external_table;
Loading data to table test_hive.test_external_table
Table test_hive.test_external_table stats: [numFiles=1, numRows=0, totalSize=88, rawDataSize=0]
OK
Time taken: 0.405 seconds
a.txt 的内容，3个字段，以制表符tab键分割
[root@master data]# cat /data/project/data/a.txt
user1 27 1
user2 28 1
user3 29 0
user4 30 1
user5 31 0
user6 32 1
user7 33 1
user8 34 0
检验数据是否已经加载到表中
hive> SELECT * FROM test_external_table;
OK
user1 27 1
user2 28 1
user3 29 0
user4 30 1
user5 31 0
user6 32 1
user7 33 1
user8 34 0
Time taken: 0.17 seconds, Fetched: 8 row(s)
现在看下hdfs上的数据是什么样的
[root@master data]# hadoop fs -text /user/hive/warehouse/test_hive.db/test_external_table/a.txt
user1 27 1
user2 28 1
user3 29 0
user4 30 1
user5 31 0
user6 32 1
user7 33 1
user8 34 0

可以上述向hive中加载数据，其实和将本地文件上传至hdfs上指定的目录效果一样

4、删除外部表
Time taken: 0.17 seconds, Fetched: 8 row(s)
hive> DROP TABLE test_external_table;
OK
Time taken: 0.507 seconds
hive> SHOW TABLES;
OK
x
Time taken: 0.01 seconds, Fetched: 1 row(s)
在hive中已经成功删除了一个外部表，查看下hdfs上对应的文件是否还存在
[root@master data]# hadoop fs -ls /user/hive/warehouse/test_hive.db/test_external_table/a.txt
-rwxr-xr-x 3 root supergroup 88 2018-01-25 06:46 /user/hive/warehouse/test_hive.db/test_external_table/a.txt
[root@master data]# hadoop fs -text /user/hive/warehouse/test_hive.db/test_external_table/a.txt
user1 27 1
user2 28 1
user3 29 0
user4 30 1
user5 31 0
user6 32 1
user7 33 1
user8 34 0
可以看到文件还存在而且内容没有发生任何变化，这就是外部表的功效，从hive中删除的表只删除了hive到hdfs上文件的映射关系，而没有删除真实的数据
如果想在hive中恢复数据，实际上是恢复hive到hdfs上a.txt 文件到hive中test_external_table 表的映射关系，只需要安装之前的方式创建同名字的表即可,字段不必一样，但是字段类型必须和原数据保存一样，否则查询到的数据类型不匹配，会变成NULL
CREATE EXTERNAL TABLE test_external_table
(
uid STRING,
uage INT,
usex STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’;
OK
Time taken: 0.349 seconds
hive> select * from test_external_table ;
OK
user1 27 1
user2 28 1
user3 29 0
user4 30 1
user5 31 0
user6 32 1
user7 33 1
user8 34 0
Time taken: 0.072 seconds, Fetched: 8 row(s)
hive> DESC test_external_table;
OK
usrid string
age string
sex string
Time taken: 0.031 seconds, Fetched: 3 row(s)
可以看数据和表结构都和之前的一样
创建一个内部表，默认情况下就内部表
CREATE TABLE userinfo
(
userid STRING,
userage STRING,
usersex STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’;
导入数据
LOAD DATA LOCAL INPATH ‘/data/project/data/b.txt’ OVERWRITE INTO TABLE userinfo;
检查是否导入成功
查看hdfs上的文件
[root@master data]# hadoop fs -ls /user/hive/warehouse/test_hive.db/userinfo/b.txt
-rwxr-xr-x 3 root supergroup 114 2018-01-25 07:56 /user/hive/warehouse/test_hive.db/userinfo/b.txt
[root@master data]# hadoop fs -text /user/hive/warehouse/test_hive.db/userinfo/b.txt
user1 100 20170301
user3 101 20170302
user4 102 20170303
user5 103 20170304
user7 104 20170305
user8 105 20170306
可以看到完全一样，直到这里无法看出内部表和外部表的区别
如果在hive中将内部表删除会发生什么？让我们测试下
hive> drop table userinfo;
OK
Time taken: 0.404 seconds
hive> show tables;
OK
test_external_table
x
Time taken: 0.009 seconds, Fetched: 2 row(s)
可以看的userinfo 表已经删除，查看下hdfs对应的目录下是否有文件
[root@master data]# hadoop fs -ls /user/hive/warehouse/test_hive.db/
Found 3 items
drwxr-xr-x - root supergroup 0 2018-01-25 06:46 /user/hive/warehouse/test_hive.db/test_external_table
drwxr-xr-x - root supergroup 0 2018-01-25 07:00 /user/hive/warehouse/test_hive.db/w_a
drwxr-xr-x - root supergroup 0 2018-01-25 05:46 /user/hive/warehouse/test_hive.db/x
可以看到文件已经删除了

内部表和外部表的区别：
内部表在hive中被删除后，对应的目录也被删除(以表名创建的目录被删除)
外部表在hive中被删除后，对应的目录和文件都存在，只是删除了hive表到hdfs对应文件的映射，重新创建相同名字的表就可以恢复数据，外部表的优势是防止误删除。