大数据（022）Hive【Hive【DML（数据操纵语言）语句】】

----
1、数据加载语句
----
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)

【

注：CREATE 后可以选跟参数EXTERNAL,带EXTERNAL我们称之为外表，不带我们称之为内表。

        外表：删表不删除数据文件

        内表：删表的同时删除数据文件

】

----
2、insert语句【】

----

上一步我们已经创建了people表。现在我要拿出其中的id STRING,name STRING,likes ARRAY<string>三个字段，将其放入一张新表people_new中。

CREATE TABLE people_new (
id STRING,
name STRING,
likes ARRAY<string>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
STORED AS TEXTFILE;
---插入people_new
hive>INSERT OVERWRITE TABLE people_new select id , name , likes FROM people ;


----
3、update语句
----

UPDATE tablename SET column = value [, column = value ...] [WHERE expression]

【注：hive默认已经不支持update和delete了（因为现实环境中几乎用不到），要使用update和delete需要进行额外配置，详情请百度一下】

 

----
4、deletee语句
----
DELETE FROM tablename [WHERE expression]

【注：hive默认已经不支持update和delete了（因为现实环境中几乎用不到），要使用update和delete需要进行额外配置，详情请百度一下】