大数据（021）Hive【Hive【DDL语句】】

最新推荐文章于 2025-05-08 16:34:34 发布

原创最新推荐文章于 2025-05-08 16:34:34 发布 · 469 阅读

0 ·

CC 4.0 BY-SA版权

集群、全文检索、大数据（Hadoop）专栏收录该内容

89 篇文章

订阅专栏

本文介绍如何使用Hive创建不同类型的表，包括简单类型表、复合数据类型表及分区表，并演示了数据导入过程及查询方法。

----
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