import com.alibaba.fastjson.JSON;
import com.google.common.collect.ImmutableMap;
import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaRDD;
import org.apache.spark.api.java.function.Function;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import org.elasticsearch.spark.rdd.api.java.JavaEsSpark;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
public class OrderToEs {
public static void main(String[] args) {
SparkConf conf = new SparkConf().setAppName("b2b_data_Platform")
.set("spark.debug.maxToStringFields", "200")
.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
.set("spark.kryoserializer.buffer.max", "1024")
.set("es.index.auto.create", "true")
.set("es.nodes", "11.3.25.7")
.set("es.port", "10001");
final SparkSession spark = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate();
Dataset<Row> sqlDF = spark.sql(getSql());
JavaRDD<String> javaRDD = sqlDF.toJavaRDD().map(new Function<Row, String>() {
@Override
public String call(Row row) throws Exception {
OrderInfoDTO orderInfoDTO = new OrderInfoDTO();
BigDecimal bigDecimal = new BigDecimal(100);
Long innerOrderId = Long.parseLong(row.getAs("innerOrderId").toString());
orderInfoDTO.setInnerOrderId(innerOrderId);
orderInfoDTO.setJdOrderId(Long.parseLong(row.getAs("jdOrderId").toString()));
if (row.getAs("purchaseOrderId") != null && !"".equals(row.getAs("purchaseOrderId").toString())) {
orderInfoDTO.setPurchaseOrderId(Long.parseLong(row.getAs("purchaseOrderId").toString()));
}
if (row.getAs("purchaseOriginalStatus") != null && !"".equals(row.getAs("purchaseOrderId").toString())) {
orderInfoDTO.setPurchaseOriginalStatus(row.getAs("purchaseOriginalStatus").toString());
}
if (row.getAs("cetusChannel") != null && !"".equals(row.getAs("cetusChannel").toString())) {
orderInfoDTO.setCetusChannel(Integer.parseInt(row.getAs("cetusChannel").toString()));
}
if (row.getAs("activityType") != null && !"".equals(row.getAs("activityType").toString())) {
orderInfoDTO.setActivityType(Integer.parseInt(row.getAs("activityType").toString()));
}
if (row.getAs("purchaseStatus") != null && !"".equals(row.getAs("purchaseStatus").toString())) {
orderInfoDTO.setPurchaseStatus(Integer.parseInt(row.getAs("purchaseStatus").toString()));
}
if (row.getAs("bizType") != null && !"".equals(row.getAs("bizType").toString())) {
orderInfoDTO.setBizType(Integer.parseInt(row.getAs("bizType").toString()));
}
if (row.getAs("orderPlatform") != null && !"".equals(row.getAs("orderPlatform").toString())) {
orderInfoDTO.setOrderPlatform(Integer.parseInt(row.getAs("orderPlatform").toString()));
}
if (row.getAs("orderSource") != null && !"".equals(row.getAs("orderSource").toString())) {
orderInfoDTO.setOrderSource(Integer.parseInt(row.getAs("orderSource").toString()));
}
if (row.getAs("orderIndustry") != null && !"".equals(row.getAs("orderIndustry").toString())) {
orderInfoDTO.setOrderIndustry(Integer.parseInt(row.getAs("orderIndustry").toString()));
}
if (row.getAs("splitStatus") != null && !"".equals(row.getAs("splitStatus").toString())) {
orderInfoDTO.setSplitStatus(Integer.parseInt(row.getAs("splitStatus").toString()));
}
if (row.getAs("pin") != null) {
orderInfoDTO.setPin(row.getAs("pin").toString());
}
if (row.getAs("orderAmount") != null && !"".equals(row.getAs("orderAmount").toString())) {
orderInfoDTO.setOrderAmount(new BigDecimal(row.getAs("orderAmount").toString()).multiply(bigDecimal));
}
if (row.getAs("orderNeedMoney") != null && !"".equals(row.getAs("orderNeedMoney").toString())) {
orderInfoDTO.setOrderNeedMoney(new BigDecimal(row.getAs("orderNeedMoney").toString()).multiply(bigDecimal));
}
if (row.getAs("orderType") != null && !"".equals(row.getAs("orderType").toString())) {
orderInfoDTO.setOrderType(Integer.parseInt(row.getAs("orderType").toString()));
}
if (row.getAs("orderState") != null && !"".equals(row.getAs("orderState").toString())) {
orderInfoDTO.setOrderState(Integer.parseInt(row.getAs("orderState").toString()));
}
if (row.getAs("jdOrderState") != null && !"".equals(row.getAs("jdOrderState").toString())) {
orderInfoDTO.setJdOrderState(Integer.parseInt(row.getAs("jdOrderState").toString()));
}
if (row.getAs("createOrderTime") != null && !"".equals(row.getAs("createOrderTime").toString())) {
orderInfoDTO.setCreateOrderTime(row.getAs("createOrderTime").toString().substring(0, 19));
}
if (row.getAs("outTime") != null && !"".equals(row.getAs("outTime").toString())) {
orderInfoDTO.setOutTime(row.getAs("outTime").toString().substring(0, 19));
}
if (row.getAs("payCompleteTime") != null && !"".equals(row.getAs("payCompleteTime").toString())) {
orderInfoDTO.setPayCompleteTime(row.getAs("payCompleteTime").toString().substring(0, 19));
}
if (row.getAs("remark") != null) {
orderInfoDTO.setRemark(row.getAs("remark").toString());
}
if (row.getAs("status") != null && !"".equals(row.getAs("status").toString())) {
orderInfoDTO.setStatus(Integer.parseInt(row.getAs("status").toString()));
}
if (row.getAs("activityNumber") != null && !"".equals(row.getAs("activityNumber").toString())) {
orderInfoDTO.setActivityNumber(Long.parseLong(row.getAs("activityNumber").toString()));
}
if (row.getAs("manOrderState") != null && !"".equals(row.getAs("manOrderState").toString())) {
orderInfoDTO.setManOrderState(Integer.parseInt(row.getAs("manOrderState").toString()));
}
if (row.getAs("consCompanayName") != null && !"".equals(row.getAs("consCompanayName").toString())) {
orderInfoDTO.setConsCompanayName(row.getAs("consCompanayName").toString());
}
if (row.getAs("consProvinceId") != null && !"".equals(row.getAs("consProvinceId").toString())) {
orderInfoDTO.setConsProvinceId(Integer.parseInt(row.getAs("consProvinceId").toString()));
}
if (row.getAs("consProvinceName") != null && !"".equals(row.getAs("consProvinceName").toString())) {
orderInfoDTO.setConsProvinceName(row.getAs("consProvinceName").toString());
}
if (row.getAs("consCityId") != null && !"".equals(row.getAs("consCityId").toString())) {
orderInfoDTO.setConsCityId(Integer.parseInt(row.getAs("consCityId").toString()));
}
if (row.getAs("consCityName") != null && !"".equals(row.getAs("consCityName").toString())) {
orderInfoDTO.setConsCityName(row.getAs("consCityName").toString());
}
if (row.getAs("consCountyId") != null && !"".equals(row.getAs("consCountyId").toString())) {
orderInfoDTO.setConsCountyId(Integer.parseInt(row.getAs("consCityId").toString()));
}
if (row.getAs("consCountyName") != null && !"".equals(row.getAs("consCountyName").toString())) {
orderInfoDTO.setConsCountyName(row.getAs("consCountyName").toString());
}
if (row.getAs("consTownId") != null && !"".equals(row.getAs("consTownId").toString())) {
orderInfoDTO.setConsTownId(Integer.parseInt(row.getAs("consTownId").toString()));
}
if (row.getAs("consTownName") != null && !"".equals(row.getAs("consTownName").toString())) {
orderInfoDTO.setConsTownName(row.getAs("consTownName").toString());
}
if (row.getAs("consAddress") != null && !"".equals(row.getAs("consAddress").toString())) {
orderInfoDTO.setConsAddress(row.getAs("consAddress").toString());
}
if (row.getAs("consMobile") != null && !"".equals(row.getAs("consMobile").toString())) {
orderInfoDTO.setConsMobile(row.getAs("consMobile").toString());
}
if (row.getAs("consName") != null && !"".equals(row.getAs("consName").toString())) {
orderInfoDTO.setConsName(row.getAs("consName").toString());
}
if (row.getAs("depositPaymentTime") != null && !"".equals(row.getAs("depositPaymentTime").toString())) {
orderInfoDTO.setDepositPaymentTime(row.getAs("depositPaymentTime").toString().substring(0, 19));
}
if (row.getAs("finalPaymentTime") != null && !"".equals(row.getAs("finalPaymentTime").toString())) {
orderInfoDTO.setFinalPaymentTime(row.getAs("finalPaymentTime").toString().substring(0, 19));
}
//获取sku明细
if (row.getAs("wares") != null && !"".equals(row.getAs("wares").toString())) {
String[] wareArray = row.getAs("wares").toString().split("<<@@#@#>>");
List<SkuInfoDTO> skuInfoDTOS = new ArrayList<>();
for (int i = 0; i < wareArray.length; i++) {
SkuInfoDTO skuInfoDTO = new SkuInfoDTO();
String wareStr = wareArray[i];
String[] ware = wareStr.split("<@#@#@#@>");
if (ware[0] != null && !"".equals(ware[0].trim())) {
skuInfoDTO.setBrandId(Integer.parseInt(ware[0].trim()));
}
if (ware[1] != null && !"".equals(ware[1].trim())) {
skuInfoDTO.setCategoryLevel1(Integer.parseInt(ware[1]));
}
if (ware[2] != null && !"".equals(ware[2].trim())) {
skuInfoDTO.setCategoryLevel2(Integer.parseInt(ware[2].trim()));
}
if (ware[3] != null && !"".equals(ware[3].trim())) {
skuInfoDTO.setCategoryLevel3(Integer.parseInt(ware[3].trim()));
}
if (ware[4] != null && !"".equals(ware[4].trim())) {
skuInfoDTO.setNum(Integer.parseInt(ware[4].trim()));
}
if (ware[5] != null && !"".equals(ware[5].trim())) {
skuInfoDTO.setSalesPrice(new BigDecimal(ware[5].trim()).multiply(bigDecimal));
}
if (ware[6] != null && !"".equals(ware[6].trim())) {
skuInfoDTO.setSkuId(Long.parseLong(ware[6].trim()));
}
if (ware[7] != null && !"".equals(ware[7])) {
skuInfoDTO.setPdPin(ware[7]);
}
if (ware[8] != null && !"".equals(ware[8])) {
skuInfoDTO.setName(ware[8]);
}
skuInfoDTOS.add(skuInfoDTO);
}
orderInfoDTO.setSkuInfos(skuInfoDTOS);
}
return JSON.toJSONString(orderInfoDTO);
}
});
System.out.println("Plumber data size:" + sqlDF.count());
JavaEsSpark.saveJsonToEs(javaRDD, "order_info_index/orderInfo", ImmutableMap.of("es.mapping.id", "innerOrderId"));
spark.stop();
}
public static String getSql() {
String sql = "select " +
" s1.innerOrderId, " +
" s1.purchaseOrderId, " +
" s1.purchaseOriginalStatus, " +
" s1.cetusChannel, " +
" s1.activityType, " +
" s1.purchaseStatus, " +
" s1.bizType, " +
" s2.jdOrderId, " +
" s2.orderPlatform, " +
" s2.orderSource, " +
" s2.orderIndustry, " +
" s2.orderTierType, " +
" s2.splitStatus, " +
" s2.parentId, " +
" s2.pin, " +
" s2.orderAmount, " +
" s2.orderNeedMoney, " +
" s2.orderType, " +
" s2.orderState, " +
" s2.jdOrderState, " +
" s2.createOrderTime, " +
" s2.outTime, " +
" s2.payCompleteTime, " +
" s2.status, " +
" s2.remark, " +
" s2.activityNumber, " +
" s2.manOrderState, " +
" s3.consCompanayName, " +
" s3.consProvinceId, " +
" s3.consProvinceName, " +
" s3.consCityId, " +
" s3.consCityName, " +
" s3.consCountyId, " +
" s3.consCountyName, " +
" s3.consTownId, " +
" s3.consTownName, " +
" s3.consAddress, " +
" s3.consMobile, " +
" s3.consName, " +
" s4.depositPaymentTime, " +
" s4.finalPaymentTime, " +
" s5.wares " +
"from " +
" ( " +
" select " +
" inner_order_id as innerOrderId, " +
" index05 as purchaseOrderId, " +
" index07 as purchaseOriginalStatus, " +
" index04 as cetusChannel, " +
" if(index06 <> '' and index06 is not null, cast(index06 as int), 0) as activityType, " +
" case cast(index07 as int) " +
" when 0 then 1" +
" when 12 then 2" +
" when 1 then 3" +
" when 13 then 4" +
" when 22 then 5" +
" when 31 then 6" +
" when 2 then 7" +
" when 23 then 8" +
" when 3 then 9" +
" when 33 then 10" +
" when 11 then 11" +
" when 21 then 12" +
" when 99 then 13" +
" when 121 then 14" +
" when 131 then 15" +
" when 221 then 16" +
" when 331 then 17" +
" when 990 then 18" +
" when 991 then 19" +
" when 992 then 20" +
" when 993 then 21" +
" when 994 then 22" +
" when 995 then 23" +
" when 1000 then 24" +
" when 2000 then 25" +
" else null " +
" end as purchaseStatus, " +
" case " +
" when cast(index04 as int) = 1 or cast(index04 as int) = 8 or cast(index04 as int) = 9 then 1 " +
" when cast(index04 as int) = 14 then 2 " +
" else null " +
" end as bizType " +
" from " +
" fdm.fdm_b2b_trade0_b2b_order_ext_chain " +
" where dp = 'ACTIVE' and index04 in('1', '8', '9', '14') " +
" and substr(created, 1, 10) <= '2018-12-02' " +
" ) " +
" s1 " +
"inner join " +
" ( " +
" SELECT " +
" inner_order_id as innerOrderId, " +
" jd_order_id as jdOrderId, " +
" order_platform as orderPlatform, " +
" order_source as orderSource, " +
" order_industry as orderIndustry, " +
" order_tier_type as orderTierType, " +
" case " +
" when order_tier_type = 2 " +
" and cast(parent_id as int) = 0 " +
" then 0 " +
" when order_tier_type = 1 " +
" and cast(parent_id as int) = 0 " +
" then 1 " +
" when order_tier_type = 2 " +
" and cast(parent_id as int) > 0 " +
" then 2 " +
" else null " +
" end as splitStatus, " +
" parent_id as parentId, " +
" pin as pin, " +
" order_amount as orderAmount, " +
" order_need_money as orderNeedMoney, " +
" order_type as orderType, " +
" order_state as orderState, " +
" jd_order_state as jdOrderState, " +
" create_order_time as createOrderTime, " +
" out_time as outTime, " +
" pay_complete_time as payCompleteTime, " +
" status, " +
" remark, " +
" lottery_id as activityNumber, " +
" case " +
" when order_state = 3 " +
" then 14 " +
" when jd_order_state = 1 " +
" then 1 " +
" when jd_order_state = 2 " +
" or jd_order_state = 3 " +
" or jd_order_state = 4 " +
" then 2 " +
" when jd_order_state = 5 " +
" then 3 " +
" when jd_order_state = 6 " +
" or jd_order_state = 7 " +
" or jd_order_state = 8 " +
" then 4 " +
" when jd_order_state = 9 " +
" or jd_order_state = 10 " +
" or jd_order_state = 16 " +
" then 5 " +
" when jd_order_state = 17 " +
" then 6 " +
" when jd_order_state = 11 " +
" then 7 " +
" when jd_order_state = 14 " +
" then 8 " +
" when jd_order_state = 13 " +
" then 9 " +
" when jd_order_state = 12 " +
" then 10 " +
" when jd_order_state = 18 " +
" then 11 " +
" when jd_order_state = 21 " +
" then 12 " +
" when jd_order_state = 19 " +
" then 13 " +
" else null " +
" end as manOrderState " +
" from " +
" fdm.fdm_b2b_trade0_b2b_order_main_chain " +
" where " +
" dp = 'ACTIVE' " +
" and order_platform = 2 " +
" ) " +
" s2 " +
" on " +
" s1.innerOrderId = s2.innerOrderId " +
" inner join " +
" ( " +
" select " +
" inner_order_id as innerOrderId, " +
" cons_companay_name as consCompanayName, " +
" cons_province_id as consProvinceId, " +
" cons_province_name as consProvinceName, " +
" cons_city_id as consCityId, " +
" cons_city_name as consCityName, " +
" cons_county_id as consCountyId, " +
" cons_county_name as consCountyName, " +
" cons_town_id as consTownId, " +
" cons_town_name as consTownName, " +
" cons_address as consAddress, " +
" cons_mobile as consMobile, " +
" cons_name as consName " +
" from " +
" fdm.fdm_b2b_trade0_b2b_order_detail_chain " +
" where " +
" dp = 'ACTIVE' " +
" ) " +
" s3 " +
" on " +
" s1.innerOrderId = s3.innerOrderId " +
"left join " +
" ( " +
" select " +
" inner_order_id as innerOrderId, " +
" get_json_object(snap, '$.payBargainTime') as depositPaymentTime, " +
" get_json_object(snap, '$.payBalanceTime') as finalPaymentTime " +
" from " +
" fdm.fdm_b2b_trade0_b2b_order_snap_chain " +
" where " +
" dp = 'ACTIVE' " +
" and type = 23 " +
" ) " +
" s4 " +
"on " +
" s1.innerOrderId = s4.innerOrderId " +
"left join " +
" ( " +
" select " +
" inner_order_id as innerOrderId, " +
" concat_ws('<<@@#@#>>', collect_set(concat(brand_id, '<@#@#@#@>', category_level1, '<@#@#@#@>', category_level2, '<@#@#@#@>', category_level3, '<@#@#@#@>', num, '<@#@#@#@>', sales_price, '<@#@#@#@>', sku_id,'<@#@#@#@>',pd_pin, '<@#@#@#@>',name))) as wares " +
" from " +
" ( " +
" SELECT " +
" inner_order_id, " +
" sku_id, " +
" name, " +
" num, " +
" sales_price, " +
" pd_pin, " +
" category_level1, " +
" category_level2, " +
" category_level3, " +
" brand_id " +
" from " +
" fdm.fdm_b2b_trade0_b2b_order_ware_chain " +
" where " +
" dp = 'ACTIVE' " +
" ) " +
" s1 " +
" group by " +
" s1.inner_order_id " +
" ) " +
" s5 " +
"on " +
" s1.innerOrderId = s5.innerOrderId";
return sql;
}
}