大数据(二十一)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
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,看到下图所示