大数据（二十一）Hive【Hive【DDL语句】】

----
1、简单的建表语句【简单类型】
----
CREATE TABLE page_view (
page_url STRING,
ip STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
--在/opt/sxt/soft/apache-hive-1.2.1-bin/hive_data/目录下新建文档people.txt。内容如下：
1    192.168.108.136
2    192.168.1.202
执行数据导入命令：
load data local inpath '/opt/sxt/soft/apache-hive-1.2.1-bin/hive_data/page_view.txt' into table page_view;
---查询
hive> select * from page_view;
OK
1    192.168.108.136
2    192.168.1.202
Time taken: 0.322 seconds, Fetched: 2 row(s)

----
2、简单的建表语句【包含复合数据类型】
----
CREATE TABLE people (
id STRING,
name STRING,
likes ARRAY<string>,
address MAP<string,string>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE;

--在/opt/sxt/soft/apache-hive-1.2.1-bin/hive_data/目录下新建文档people.txt。内容如下：
1    zs    game,girl,money    stuAddr:changsha,workAddr:beijing
1    ls    game,girl,money    stuAddr:changsha,workAddr:beijing
执行数据导入命令：
load data local inpath '/opt/sxt/soft/apache-hive-1.2.1-bin/hive_data/people.txt' into table people;
---查询
hive> select * from people ;
OK
1    zs    ["game","girl","money"]    {"stuAddr":"changsha","workAddr":"beijing"}
1    ls    ["game","girl","money"]    {"stuAddr":"changsha","workAddr":"beijing"}
Time taken: 1.454 seconds, Fetched: 2 row(s)
hive> select address['stuAddr'] from people where name = 'zs';
OK
changsha
Time taken: 0.22 seconds, Fetched: 1 row(s)


----
3、建表语句【分区】
----
CREATE TABLE partitioned_test (
id STRING,
name STRING,
likes ARRAY<string>,
address MAP<string,string>
)
PARTITIONED BY(dt STRING )
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE;
--在/opt/sxt/soft/apache-hive-1.2.1-bin/hive_data/目录下新建文档partitioned_test.txt。内容如下：
1    zs    game,girl,money    stuAddr:changsha,workAddr:beijing    2016-1-1
1    ls    game,girl,money    stuAddr:changsha,workAddr:beijing    2016-1-1
1    xm    game,girl,money    stuAddr:changsha,workAddr:beijing    2015-1-1
执行数据导入命令：
load data local inpath '/opt/sxt/soft/apache-hive-1.2.1-bin/hive_data/partitioned_test.txt' into table partitioned_test PARTITION (dt='2016-1-1');
load data local inpath '/opt/sxt/soft/apache-hive-1.2.1-bin/hive_data/partitioned_test.txt' into table partitioned_test PARTITION (dt='2015-1-1');
--查询
hive> select * from partitioned_test;
OK
1    zs    ["game","girl","money"]    {"stuAddr":"changsha","workAddr":"beijing"}    2015-1-1
1    ls    ["game","girl","money"]    {"stuAddr":"changsha","workAddr":"beijing"}    2015-1-1
1    xm    ["game","girl","money"]    {"stuAddr":"changsha","workAddr":"beijing"}    2015-1-1
1    zs    ["game","girl","money"]    {"stuAddr":"changsha","workAddr":"beijing"}    2016-1-1
1    ls    ["game","girl","money"]    {"stuAddr":"changsha","workAddr":"beijing"}    2016-1-1
1    xm    ["game","girl","money"]    {"stuAddr":"changsha","workAddr":"beijing"}    2016-1-1

此时：在浏览器http://192.168.0.202:50070中进行如下操作

可以看到如下显示：

我满接下来打开hive配置文件：vi /opt/sxt/soft/apache-hive-1.2.1-bin/conf/hive-site.xml。可以看到如下所示配置

我们在上一步浏览器中依次点击user、hive_remote、warehouse。这就是hadoop中存储我们hive中的数据，如下图

点击partitioned_test，看到下图所示