1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313
| create external table if not exists tags_tfec_userprofile.tfec_tbl_goods( `id` bigint, `siteid` bigint, `istest` boolean comment '是否是测试网单', `hasread` boolean comment '是否已读,测试字段', `supportonedaylimit` boolean comment '是否支持24小时限时达', `orderid` bigint comment '订单ID', `cordersn` string comment 'child order sn 子订单编码 C0919293', `isbook` boolean comment '是否是预订网单', `cpaymentstatus` int comment '子订单付款状态', `cpaytime` bigint comment '子订单付款时间', `producttype` string comment '商品类型', `productid` bigint comment '抽象商品id(可能是商品规格,也可能是套装,由商品类型决定)', `productname` string comment '商品名称:可能是商品名称加颜色规格,也可能是套装名称', `sku` string comment '货号', `price` double comment '商品单价', `number` int comment '数量', `lockednumber` bigint comment '曾经锁定的库存数量', `unlockednumber` bigint comment '经解锁的库存数量', `productamount` double comment '此字段专为同步外部订单而加,商品总金额=price*number+shippingFee-优惠金额,但优惠金额没在本系统存储', `balanceamount` double comment '余额扣减', `couponamount` double comment '优惠券抵扣金额', `esamount` double comment '节能补贴金额', `giftcardnumberid` bigint comment '礼品卡号ID,关联GiftCardNumbers表的主键', `usedgiftcardamount` double comment '礼品卡抵用的金额', `couponlogid` bigint comment '使用的优惠券记录ID', `activityprice` double comment '活动价,当有活动价时price的值来源于activityPrice', `activityid` bigint comment '活动ID', `cateid` int comment '分类ID', `brandid` int comment '品牌ID', `netpointid` int comment '网点id', `shippingfee` double comment '配送费用', `settlementstatus` boolean comment '结算状态0 未结算 1已结算', `receiptorrejecttime` bigint comment '确认收货时间或拒绝收货时间', `iswmssku` boolean comment '是否淘宝小家电', `scode` string comment '库位编码', `tscode` string comment '转运库房编码', `tsshippingtime` int comment '转运时效(小时)', `status` int comment '状态', `productsn` string comment '商品条形码', `invoicenumber` string comment '运单号', `expressname` string comment '快递公司', `invoiceexpressnumber` string comment '发票快递单号', `postman` string comment '送货人', `postmanphone` string comment '送货人电话', `isnotice` int comment '是否开启预警', `noticetype` int, `noticeremark` string, `noticetime` string comment '预警时间', `shippingtime` int comment '发货时间', `lessordersn` string comment '订单号', `waitgetlesshippinginfo` boolean comment '是否等待获取LES物流配送节点信息', `getlesshippingcount` bigint comment '已获取LES配送节点信息的次数', `outping` string comment '出库凭证', `lessshiptime` int comment 'less出库时间', `closetime` bigint comment '网单完成关闭或取消关闭时间', `isreceipt` bigint comment '是否需要发票', `ismakereceipt` int comment '开票状态', `receiptnum` string comment '发票号', `receiptaddtime` string comment '开票时间', `makereceipttype` tinyint comment '开票类型 0:初始值 1:库房开票 2:共享开票', `shippingmode` string comment '物流模式,值为B2B2C或B2C', `lasttimeforshippingmode` bigint comment '最后修改物流模式的时间', `lasteditorforshippingmode` string comment '最后修改物流模式的管理员', `systemremark` string comment '系统备注,不给用户显示', `tongshuaiworkid` int comment '统帅定制作品ID', `orderpromotionid` bigint comment '下单立减活动ID', `orderpromotionamount` double comment '下单立减金额', `externalsalesettingid` bigint comment '淘宝套装设置ID', `recommendationid` bigint comment '推荐购买ID', `hassendalertnum` boolean comment '是否已发送了购买数据报警邮件(短信)', `isnolimitstockproduct` boolean comment '是否是无限制库存量的商品', `hpregisterdate` int comment 'HP注册时间', `hpfaildate` int comment 'HP派工失败时间', `hpfinishdate` int comment 'HP派工成功时间', `hpreservationdate` int comment 'HP回传预约送货时间', `shippingopporunity` tinyint comment '活动订单发货时机,0:定金发货 1:尾款发货', `istimeoutfree` tinyint comment '是否超时免单 0:未设置 1:是 2:否', `itemshareamount` double comment '订单优惠价格分摊', `lessshiptintime` int comment 'less转运入库时间', `lessshiptouttime` int comment 'less转运出库时间', `cbsseccode` string comment 'cbs库位', `points` int comment '网单使用积分', `modified` string comment '最后更新时间', `splitflag` tinyint comment '拆单标志,0:未拆单;1:拆单后旧单;2:拆单后新单', `splitrelatecordersn` string comment '拆单关联单号', `channelid` tinyint comment '区分EP和商城', `activityid2` int comment '运营活动id', `pdorderstatus` int comment '日日单状态', `omsordersn` string comment '集团OMS单号', `couponcode` string comment '优惠码编码', `couponcodevalue` double comment '优惠码优惠金额', `storeid` bigint comment '店铺ID', `storetype` tinyint comment '店铺类型', `stocktype` string, `o2otype` tinyint comment 'o2o网单类型 1:非O2O网单 2:线下用户分销商城 3:商城分销旗舰店 4:创客', `brokeragetype` string, `ogcolor` string comment '算法预留字段' ) comment '商品详情es外部表' stored by 'org.elasticsearch.hadoop.hive.EsStorageHandler' tblproperties('es.resource'='tfec_tbl_goods/_doc', 'es.nodes'='up01:9200', 'es.index.auto.create'='TRUE', 'es.index.refresh_interval' = '-1', 'es.index.number_of_replicas' = '0', 'es.batch.write.retry.count' = '6', 'es.batch.write.retry.wait' = '60s', 'es.mapping.name' = 'id:id,siteid:siteid,istest:istest,hasread:hasread,supportonedaylimit:supportonedaylimit,orderid:orderid,cordersn:cordersn,isbook:isbook,cpaymentstatus:cpaymentstatus,cpaytime:cpaytime,producttype:producttype,productid:productid,productname:productname,sku:sku,price:price,number:number,lockednumber:lockednumber,unlockednumber:unlockednumber,productamount:productamount,balanceamount:balanceamount,couponamount:couponamount,esamount:esamount,giftcardnumberid:giftcardnumberid,usedgiftcardamount:usedgiftcardamount,couponlogid:couponlogid,activityprice:activityprice,activityid:activityid,cateid:cateid,brandid:brandid,netpointid:netpointid,shippingfee:shippingfee,settlementstatus:settlementstatus,receiptorrejecttime:receiptorrejecttime,iswmssku:iswmssku,scode:scode,tscode:tscode,tsshippingtime:tsshippingtime,status:status,productsn:productsn,invoicenumber:invoicenumber,expressname:expressname,invoiceexpressnumber:invoiceexpressnumber,postman:postman,postmanphone:postmanphone,isnotice:isnotice,noticetype:noticetype,noticeremark:noticeremark,noticetime:noticetime,shippingtime:shippingtime,lessordersn:lessordersn,waitgetlesshippinginfo:waitgetlesshippinginfo,getlesshippingcount:getlesshippingcount,outping:outping,lessshiptime:lessshiptime,closetime:closetime,isreceipt:isreceipt,ismakereceipt:ismakereceipt,receiptnum:receiptnum,receiptaddtime:receiptaddtime,makereceipttype:makereceipttype,shippingmode:shippingmode,lasttimeforshippingmode:lasttimeforshippingmode,lasteditorforshippingmode:lasteditorforshippingmode,systemremark:systemremark,tongshuaiworkid:tongshuaiworkid,orderpromotionid:orderpromotionid,orderpromotionamount:orderpromotionamount,externalsalesettingid:externalsalesettingid,recommendationid:recommendationid,hassendalertnum:hassendalertnum,isnolimitstockproduct:isnolimitstockproduct,hpregisterdate:hpregisterdate,hpfaildate:hpfaildate,hpfinishdate:hpfinishdate,hpreservationdate:hpreservationdate,shippingopporunity:shippingopporunity,istimeoutfree:istimeoutfree,itemshareamount:itemshareamount,lessshiptintime:lessshiptintime,lessshiptouttime:lessshiptouttime,cbsseccode:cbsseccode,points:points,modified:modified,splitflag:splitflag,splitrelatecordersn:splitrelatecordersn,channelid:channelid,activityid2:activityid2,pdorderstatus:pdorderstatus,omsordersn:omsordersn,couponcode:couponcode,couponcodevalue:couponcodevalue,storeid:storeid,storetype:storetype,stocktype:stocktype,o2otype:o2otype,brokeragetype:brokeragetype,ogcolor:ogcolor' );
create external table if not exists tags_tfec_userprofile.tfec_tbl_orders( `id` bigint, `siteid` bigint, `istest` boolean comment '是否是测试订单', `hassync` boolean comment '是否已同步(临时添加)', `isbackend` tinyint comment '是否为后台添加的订单', `isbook` tinyint, `iscod` boolean comment '是否是货到付款订单', `notautoconfirm` boolean comment '是否是非自动确认订单', `ispackage` boolean comment '是否为套装订单', `packageid` bigint comment '套装ID', `ordersn` string comment '订单号', `relationordersn` string comment '关联订单编号', `memberid` bigint comment '会员ID', `predictid` bigint comment '会员购买预测ID', `memberemail` string comment '会员邮件', `addtime` bigint, `synctime` bigint comment '同步到此表中的时间', `orderstatus` int comment '订单状态', `paytime` bigint comment '在线付款时间', `paymentstatus` int comment '付款状态,0:买家未付款 1:买家已付款', `receiptconsignee` string comment '发票收件人', `receiptaddress` string comment '发票地址', `receiptzipcode` string comment '发票邮编', `receiptmobile` string comment '发票联系电话', `productamount` double comment '商品金额,等于订单中所有的商品的单价乘以数量之和', `orderamount` double comment '订单总金额,等于商品总金额+运费', `paidbalance` double comment '金额账户支付总金额', `giftcardamount` double comment '礼品卡抵用金额', `paidamount` double comment '已支付金额', `shippingamount` double comment '淘宝运费', `totalesamount` double comment '网单中总的节能补贴金额之和', `usedcustomerbalanceamount` double comment '使用的客户的余额支付金额', `customerid` bigint comment '用余额支付的客户ID', `bestshippingtime` string comment '最佳配送时间描述', `paymentcode` string comment '支付方式code', `paybankcode` string comment '网银代码', `paymentname` string comment '支付方式名称', `consignee` string comment '收货人', `originregionname` string comment '原淘宝收货地址信息', `originaddress` string comment '原淘宝收货人详细收货信息', `province` bigint comment '收货地址中国省份', `city` bigint comment '收货地址中的城市', `region` bigint comment '收货地址中城市中的区', `street` bigint comment '街道ID', `markbuilding` int comment '标志建筑物', `poiid` string comment '标建ID', `poiname` string comment '标建名称', `regionname` string comment '地区名称(如:北京 北京 昌平区 兴寿镇)', `address` string comment '收货地址中用户输入的地址,一般是区以下的详细地址', `zipcode` string comment '收货地址中的邮编', `mobile` string comment '收货人手机号', `phone` string comment '收货人固定电话号', `receiptinfo` string comment '发票信息,序列化数组array(title =>.., receiptType =>..,needReceipt => ..,companyName =>..,taxSpotNum =>..,regAddress=>..,regPhone=>..,bank=>..,bankAccount=>..)', `delayshiptime` bigint comment '延迟发货日期', `remark` string comment '订单备注', `bankcode` string comment '银行代码,用于银行直链支付', `agent` string comment '处理人', `confirmtime` int comment '确认时间', `firstconfirmtime` bigint comment '首次确认时间', `firstconfirmperson` string comment '第一次确认人', `finishtime` int comment '订单完成时间', `tradesn` string comment '在线支付交易流水号', `signcode` string comment '收货确认码', `source` string comment '订单来源', `sourceordersn` string comment '外部订单号', `onedaylimit` tinyint comment '是否支持24小时限时达', `logisticsmanner` int comment '物流评价', `aftersalemanner` int comment '售后评价', `personmanner` int comment '人员态度', `visitremark` string comment '回访备注', `visittime` int comment '回访时间', `visitperson` string comment '回访人', `sellpeople` string comment '销售代表', `sellpeoplemanner` int comment '销售代表服务态度', `ordertype` tinyint comment '订单类型 默认:0 团购预付款:1 团购正式单:2', `hasreadtaobaoordercomment` boolean comment '是否已读取过淘宝订单评论', `memberinvoiceid` bigint comment '订单发票ID,MemberInvoices表的主键', `taobaogroupid` bigint comment '淘宝万人团活动ID', `tradetype` string comment '交易类型,值参考淘宝', `steptradestatus` string comment '分阶段付款的订单状态,值参考淘宝', `steppaidfee` double comment '分阶段付款的已付金额', `depositamount` double comment '定金应付金额', `balanceamount` double comment '尾款应付金额', `autocanceldays` bigint comment '未付款过期的天数', `isnolimitstockorder` boolean comment '是否是无库存限制订单', `ccborderreceivedlogid` bigint comment '建行订单接收日志ID', `ip` string comment '订单来源IP,针对商城前台订单', `isgiftcardorder` boolean comment '是否为礼品卡订单', `giftcarddownloadpassword` string comment '礼品卡下载密码', `giftcardfindmobile` string comment '礼品卡密码找回手机号', `autoconfirmnum` bigint comment '已自动确认的次数', `codconfirmperson` string comment '货到付款确认人', `codconfirmtime` int comment '货到付款确认时间', `codconfirmremark` string comment '货到付款确认备注', `codconfirmstate` boolean comment '货到侍确认状态0无需未确认,1待确认,2确认通过可以发货,3确认无效,订单可以取消', `paymentnoticeurl` string comment '付款结果通知URL', `addresslon` double comment '地址经度', `addresslat` double comment '地址纬度', `smconfirmstatus` tinyint comment '标建确认状态。1 = 初始状态;2 = 已发HP,等待确认;3 = 待人工处理;4 = 待自动处理;5 = 已确认', `smconfirmtime` int comment '请求发送HP时间,格式为时间戳', `smmanualtime` int comment '转人工确认时间', `smmanualremark` string comment '转人工确认备注', `istogether` tinyint comment '货票通行', `isnotconfirm` boolean comment '是否是无需确认的订单', `tailpaytime` int comment '尾款付款时间', `points` int comment '网单使用积分', `modified` string comment '最后更新时间', `channelid` tinyint comment '区分EP和商城', `isproducedaily` int comment '是否日日单(1:是,0:否)', `couponcode` string comment '优惠码编码', `couponcodevalue` double comment '优惠码优惠金额', `ckcode` string ) comment '订单es外部表' stored by 'org.elasticsearch.hadoop.hive.EsStorageHandler' tblproperties('es.resource'='tfec_tbl_orders/_doc', 'es.nodes'='up01:9200', 'es.index.auto.create'='TRUE', 'es.index.refresh_interval' = '-1', 'es.index.number_of_replicas' = '0', 'es.batch.write.retry.count' = '6', 'es.batch.write.retry.wait' = '60s', 'es.mapping.name' = 'id:id,siteid:siteid,istest:istest,hassync:hassync,isbackend:isbackend,isbook:isbook,iscod:iscod,notautoconfirm:notautoconfirm,ispackage:ispackage,packageid:packageid,ordersn:ordersn,relationordersn:relationordersn,memberid:memberid,predictid:predictid,memberemail:memberemail,addtime:addtime,synctime:synctime,orderstatus:orderstatus,paytime:paytime,paymentstatus:paymentstatus,receiptconsignee:receiptconsignee,receiptaddress:receiptaddress,receiptzipcode:receiptzipcode,receiptmobile:receiptmobile,productamount:productamount,orderamount:orderamount,paidbalance:paidbalance,giftcardamount:giftcardamount,paidamount:paidamount,shippingamount:shippingamount,totalesamount:totalesamount,usedcustomerbalanceamount:usedcustomerbalanceamoun,customerid:customerid,bestshippingtime:bestshippingtime,paymentcode:paymentcode,paybankcode:paybankcode,paymentname:paymentname,consignee:consignee,originregionname:originregionname,originaddress:originaddress,province:province,city:city,region:region,street:street,markbuilding:markbuilding,poiid:poiid,poiname:poiname,regionname:regionname,address:address,zipcode:zipcode,mobile:mobile,phone:phone,receiptinfo:receiptinfo,delayshiptime:delayshiptime,remark:remark,bankcode:bankcode,agent:agent,confirmtime:confirmtime,firstconfirmtime:firstconfirmtime,firstconfirmperson:firstconfirmperson,finishtime:finishtime,tradesn:tradesn,signcode:signcode,source:source,sourceordersn:sourceordersn,onedaylimit:onedaylimit,logisticsmanner:logisticsmanner,aftersalemanner:aftersalemanner,personmanner:personmanner,visitremark:visitremark,visittime:visittime,visitperson:visitperson,sellpeople:sellpeople,sellpeoplemanner:sellpeoplemanner,ordertype:ordertype,hasreadtaobaoordercomment:hasreadtaobaoordercommen,memberinvoiceid:memberinvoiceid,taobaogroupid:taobaogroupid,tradetype:tradetype,steptradestatus:steptradestatus,steppaidfee:steppaidfee,depositamount:depositamount,balanceamount:balanceamount,autocanceldays:autocanceldays,isnolimitstockorder:isnolimitstockorder,ccborderreceivedlogid:ccborderreceivedlogid,ip:ip,isgiftcardorder:isgiftcardorder,giftcarddownloadpassword:giftcarddownloadpassword,giftcardfindmobile:giftcardfindmobile,autoconfirmnum:autoconfirmnum,codconfirmperson:codconfirmperson,codconfirmtime:codconfirmtime,codconfirmremark:codconfirmremark,codconfirmstate:codconfirmstate,paymentnoticeurl:paymentnoticeurl,addresslon:addresslon,addresslat:addresslat,smconfirmstatus:smconfirmstatus,smconfirmtime:smconfirmtime,smmanualtime:smmanualtime,smmanualremark:smmanualremark,istogether:istogether,isnotconfirm:isnotconfirm,tailpaytime:tailpaytime,points:points,modified:modified,channelid:channelid,isproducedaily:isproducedaily,couponcode:couponcode,couponcodevalue:couponcodevalue,ckcode:ckcode' );
create external table if not exists tags_tfec_userprofile.tfec_tbl_users( `id` bigint, `siteid` bigint, `avatarimagefileid` string, `email` string, `username` string comment '用户名', `password` string comment '密码', `salt` string comment '扰码', `registertime` bigint comment '注册时间', `lastlogintime` bigint comment '最后登录时间', `lastloginip` string comment '最后登录ip', `memberrankid` bigint comment '特殊会员等级id,0表示非特殊会员等级', `bigcustomerid` bigint comment '所属的大客户ID', `lastaddressid` bigint comment '上次使用的收货地址', `lastpaymentcode` string comment '上次使用的支付方式', `gender` tinyint comment '性别, 0:保密 1:男 2:女', `birthday` string comment '生日', `qq` string comment '', `job` string comment '职业;1学生、2公务员、3军人、4警察、5教师、6白领', `mobile` string comment '手机', `politicalface` bigint comment '政治面貌:1群众、2党员、3无党派人士', `nationality` string comment '国籍:1中国大陆、2中国香港、3中国澳门、4中国台湾、5其他', `validatecode` string comment '找回密码时的验证code', `pwderrcount` tinyint comment '密码输入错误次数', `source` string comment '会员来源', `marriage` string comment '婚姻状况:1未婚、2已婚、3离异', `money` double comment '账户余额', `moneypwd` string comment '余额支付密码', `isemailverify` boolean comment '是否验证email', `issmsverify` boolean comment '是否验证短信', `smsverifycode` string comment '短信验证码', `emailverifycode` string comment '邮件验证码', `verifysendcoupon` boolean comment '是否验证发送优惠券', `canreceiveemail` boolean comment '是否接收邮件', `modified` string comment '最后更新时间', `channelid` tinyint, `grade_id` bigint comment '等级ID', `nick_name` string comment '昵称', `is_blacklist` boolean comment '是否黑名单,0:非黑名单 1:黑名单' ) comment '用户es外部表' stored by 'org.elasticsearch.hadoop.hive.EsStorageHandler' tblproperties('es.resource'='tfec_tbl_users/_doc', 'es.nodes'='up01:9200', 'es.index.auto.create'='TRUE', 'es.index.refresh_interval' = '-1', 'es.index.number_of_replicas' = '0', 'es.batch.write.retry.count' = '6', 'es.batch.write.retry.wait' = '60s', 'es.mapping.name' = 'id:id,siteid:siteid,avatarimagefileid:avatarimagefileid,email:email,username:username,password:password,salt:salt,registertime:registertime,lastlogintime:lastlogintime,lastloginip:lastloginip,memberrankid:memberrankid,bigcustomerid:bigcustomerid,lastaddressid:lastaddressid,lastpaymentcode:lastpaymentcode,gender:gender,birthday:birthday,qq:qq,job:job,mobile:mobile,politicalface:politicalface,nationality:nationality,validatecode:validatecode,pwderrcount:pwderrcount,source:source,marriage:marriage,money:money,moneypwd:moneypwd,isemailverify:isemailverify,issmsverify:issmsverify,smsverifycode:smsverifycode,emailverifycode:emailverifycode,verifysendcoupon:verifysendcoupon,canreceiveemail:canreceiveemail,modified:modified,channelid:channelid,grade_id:grade_id,nick_name:nick_name,is_blacklist:is_blacklist' );
create external table if not exists tags_tfec_userprofile.tfec_tbl_logs( `id` bigint, `log_id` string comment '日志ID', `remote_ip` string comment '访问IP', `site_global_ticket` string comment '访问的站点入口', `site_global_session` string comment '站点会话信息', `global_user_id` string comment '用户ID', `cookie_text` string comment '客户端信息', `user_agent` string comment '用户客户端详细信息', `ref_url` string comment '访问的关联url', `loc_url` string comment '访问的本地url', `log_time` string comment '访问的时间' ) comment '日志es外部表' stored by 'org.elasticsearch.hadoop.hive.EsStorageHandler' tblproperties('es.resource'='tfec_tbl_logs/_doc', 'es.nodes'='up01:9200', 'es.index.auto.create'='TRUE', 'es.index.refresh_interval' = '-1', 'es.index.number_of_replicas' = '0', 'es.batch.write.retry.count' = '6', 'es.batch.write.retry.wait' = '60s', 'es.mapping.name' = 'id:id,log_id:log_id,remote_ip:remote_ip,site_global_ticket:site_global_ticket,site_global_session:site_global_session,global_user_id:global_user_id,cookie_text:cookie_text,user_agent:user_agent,ref_url:ref_url,loc_url:loc_url,log_time:log_time' );
|