hive建表、导入数据、匹配

1.建表（建立非重复表，分隔符设置为“，”）
CREATE TABLE IF NOT EXISTS imei_guid(imei string)
ROW FORMAT DELIMITED FIELDS
TERMINATED BY ‘,’;

2.导入（将本地文件夹的数据上传到hive，适用数据量较大情况）
=CONCATENATE("(’",C2,"’),")
load data inpath “hdfs://alg-hdfs/business/datacenter-platform/compass/adhoc/xxx.txt”
into table imei_guid;

3.导入（直接insert into明细，适用数据量较小情况）
insert overwrite table os_pso.imei_guid
values
(‘862267048’),
(‘862287041’)；

4.匹配（建新表）
create table if not exists os_pso.imei_guid4 as
select a.imei1,b.guid
from os_pso.imei_guid a
left join
(select imei,guid,imei_md5,guid_md5 from os_allawn_tech_dw.dws_imei_guid_mapping_all_d) b on a.imei1 = b.imei

5.匹配（不建新表）
select a.imei1,b.guid
from os_pso.imei_guid a
left join
(select imei,guid
from os_allawn_tech_dw.dws_imei_guid_mapping_all_d
where bak_dayno=20200824
group by imei,guid) b on a.imei1 = b.imei;

6.改表名
ALTER TABLE os_pso.imei_guid RENAME TO new_table_name;

7.改列名
ALTER TABLE os_pso.imei_guid CHANGE imei imei1 STRING