SJAyxkeb

1SET skip.header.line.count = 1;
2	
3CREATE EXTERNAL TABLE channelAccessCodes (accessCode string,channelCode string,id string,upc string,version bigint)
4STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler' 
5TBLPROPERTIES ("dynamodb.table.name" = "properties.channelAccessCode", 
6"dynamodb.column.mapping" = "accessCode:accessCode,channelCode:channelCode,id:id,upc:upc,version:version"); 
7
8CREATE TEMPORARY TABLE if not exists bdc (id STRING,name STRING,address STRING,zip STRING,city_hotel STRING,cc1 STRING,ufi STRING,class STRING,currencycode STRING,minrate STRING,maxrate STRING,preferred STRING,nr_rooms STRING,longitude STRING,latitude STRING,public_ranking STRING,hotel_url STRING,photo_url STRING,desc_en STRING,desc_fr STRING,desc_es STRING,desc_de STRING,desc_nl STRING,desc_it STRING,desc_pt STRING,desc_ja STRING,desc_zh STRING,desc_pl STRING,desc_ru STRING,desc_sv STRING,desc_ar STRING,desc_el STRING,desc_no STRING,city_unique STRING,city_preferred STRING,continent_id STRING,review_score STRING,review_nr STRING)
9ROW FORMAT DELIMITED fields terminated by 't' lines terminated by 'n'
10stored as textfile
11LOCATION 's3://properties-uat-imports/input/BDC'
12tblproperties("skip.header.line.count"="1");
13
14CREATE TEMPORARY TABLE TempTableDeletes(ChannelAccessCode STRING)
15ROW FORMAT DELIMITED fields terminated by '|' lines terminated by 'n';
16
17INSERT INTO TABLE TempTableDeletes SELECT channelAccessCodes.id FROM channelAccessCodes LEFT JOIN bdc ON channelAccessCodes.id = CONCAT('BDC',bdc.id)  WHERE CONCAT('BDC',bdc.id) IS NULL AND channelAccessCodes.id LIKE 'BDC%';
18	
19create external table s3_export_deletes(ChannelAccessCode STRING)
20row format delimited lines terminated by 'n'
21stored as textfile
22LOCATION 's3://properties-uat-imports-emr/';
23	
24INSERT OVERWRITE TABLE s3_export_deletes SELECT * from TempTableDeletes;