2021 10 15

补充：
1）load本地（local）数据到数据库
load data local inpath ‘/opt/sxt/temp/test.txt’ into table people PARTITION (dt=’2016-1-1’);（直接复制会出错）
2）举例建几个表（DDL）

1.CREATE TABLE page_view(
page_url STRING,
ip STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
数据如下：（-->表示制表符，即’\t’）
Node1-->192.168.13.1
Node2-->192.168.13.2

2.CREATE TABLE people(
id STRING,
name STRING,
likes Array<String>,
addr 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;
数据如下：
1-->zs-->game,girl,money-->stuAddr:nantong,workAddr:tongzhou-->2017-1-1
2-->ls-->game,girl,money-->stuAddr:nantong,workAddr:tongzhou-->2017-1-1

select addr[‘stuAddr’] from people where name=’zs’;

3.从一个表中查询数据放到另一表中
内表与外表的区别：内表drop后数据销毁，外表drop后数据还在hdfs上。
内表数据由hive管理，外表数据存放在别处。
CREATE [EXTERNAL] TABLE people_test(
id STRING,
name STRING,
likes Array<String>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ‘,’
STORED AS TEXTFILE;

INSERT OVERWRITE TABLE people_test select id,name,likes FROM people where name=’zs’;

//把表清空
INSERT OVERWRITE TABLE people_test select id,name,likes FROM people where 1=2;

UPDATE people_test SET name = ‘ls’ where name=’zs’;

//删除分区
ALTER TABLE people DROP IF EXISTS PARTITION (dt=’2016-1-1’);