江西建设监督网新网站,建设治安总队网站目的,昆明网站建设天锐科技,网页设计与制作论文800字1. insert into 与 insert overwrite区别
insert into 与 insert overwrite 都可以向hive表中插入数据#xff0c;但是insert into直接追加到表中数据的尾部#xff0c;而insert overwrite会重写数据#xff0c;既先进行删除#xff0c;再写入
注意#xff1a;如果存在分…1. insert into 与 insert overwrite区别
insert into 与 insert overwrite 都可以向hive表中插入数据但是insert into直接追加到表中数据的尾部而insert overwrite会重写数据既先进行删除再写入
注意如果存在分区的情况insert overwrite只重写当前分区数据不会全部重写
2. insert 与 with as 子查询一起使用
当在hive中同时使用insert into(overwrite) table xx 与with子查询时候需要将insert放在with as子查询后面区分与StarRocks不同insert放在with as子查询前面
WITH TEMP_A AS (SELECT TIME,IOT_ID,NAME FROM IOT_XX_A),TEMP_B AS (SELECT TIME,IOT_ID,NAME,COUNT(DISTINCT IOT_ID) AS TIMES FROM TEMP_AGROUP BY TIME,IOT_ID,NAME)INSERT INTO TABLE TABLE_B SELECT TIME,IOT_ID,NAME,TIMES FROM TEMP_B
注意 当使用以下语句建表时需要将create放在with as子查询前面
DROP TABLE IF EXISTS xx ;
CREATE TABLE xx AS
WITH TEMP_A AS (SELECT TIME,IOT_ID,NAME FROM IOT_XX_A),TEMP_B AS (SELECT TIME,IOT_ID,NAME,COUNT(DISTINCT IOT_ID) AS TIMES FROM TEMP_AGROUP BY TIME,IOT_ID,NAME)
SELECT TIME,IOT_ID,NAME,TIMES FROM TEMP_B
