用户画像：ETL 数据流向

数据流向

• 从项目全貌来说，首先是收集日志和属性数据，从线上业务库抽取业务数据、或从日志服务器抽取日志数据，把抽取的数据放到数据仓库里。
• 然后基于收仓的数据打标签、建模和行为主题的宽表建立。
• 数据仓库层面一般分多层，比如ods做基础标签，dws层做直接推送到服务层的数据。
• 业务数据库接入的数据、爬虫外部抓取的数据都放在数仓的ods层。
• 基于ods层数据围绕应用场景进行数据建模后，把数据模型结果写入到dws层，然后推送到各服务层需要调用的数据库，以备线上OLAP分析、接口服务的调用。

数据导入

数据源表

• 订单商品表：tbl_goods

    CREATE TABLE `tbl_goods` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `siteId` int(10) unsigned NOT NULL,
      `isTest` tinyint(1) unsigned NOT NULL COMMENT '是否是测试网单',
      `hasRead` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已读,测试字段',
      `supportOneDayLimit` tinyint(1) unsigned NOT NULL COMMENT '是否支持24小时限时达',
      `orderId` int(10) unsigned NOT NULL,
      `cOrderSn` varchar(50) NOT NULL COMMENT 'child order sn 子订单编码 C0919293',
      `isBook` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否是预订网单',
      `cPaymentStatus` smallint(3) unsigned NOT NULL COMMENT '子订单付款状态',
      `cPayTime` int(10) unsigned NOT NULL COMMENT '子订单付款时间',
      `productType` varchar(50) NOT NULL COMMENT '商品类型',
      `productId` int(10) unsigned NOT NULL COMMENT '抽象商品id（可能是商品规格，也可能是套装，由商品类型决定）',
      `productName` varchar(100) NOT NULL COMMENT '商品名称：可能是商品名称加颜色规格，也可能是套装名称',
      `sku` varchar(60) NOT NULL COMMENT '货号',
      `price` decimal(10,2) unsigned NOT NULL COMMENT '商品单价',
      `number` smallint(5) unsigned NOT NULL COMMENT '数量',
      `lockedNumber` int(10) unsigned NOT NULL COMMENT '曾经锁定的库存数量',
      `unlockedNumber` int(10) unsigned NOT NULL COMMENT '曾经解锁的库存数量',
      `productAmount` decimal(10,2) NOT NULL COMMENT '此字段专为同步外部订单而加，商品总金额=price*number+shippingFee-优惠金额，但优惠金额没在本系统存储',
      `balanceAmount` decimal(10,2) unsigned NOT NULL COMMENT '余额扣减',
      `couponAmount` decimal(10,2) unsigned NOT NULL COMMENT '优惠券抵扣金额',
      `esAmount` decimal(10,2) unsigned NOT NULL COMMENT '节能补贴金额',
      `giftCardNumberId` int(10) unsigned NOT NULL COMMENT '礼品卡号ID,关联GiftCardNumbers表的主键',
      `usedGiftCardAmount` decimal(10,2) unsigned NOT NULL COMMENT '礼品卡抵用的金额',
      `couponLogId` int(10) unsigned NOT NULL COMMENT '使用的优惠券记录ID',
      `activityPrice` decimal(10,2) unsigned NOT NULL COMMENT '活动价，当有活动价时price的值来源于activityPrice',
      `activityId` int(10) unsigned NOT NULL COMMENT '活动ID',
      `cateId` int(11) NOT NULL COMMENT '分类ID',
      `brandId` int(11) NOT NULL COMMENT '品牌ID',
      `netPointId` int(10) NOT NULL COMMENT '网点id',
      `shippingFee` decimal(10,2) NOT NULL COMMENT '配送费用',
      `settlementStatus` tinyint(1) NOT NULL COMMENT '结算状态0 未结算 1已结算 ',
      `receiptOrRejectTime` int(10) unsigned NOT NULL COMMENT '确认收货时间或拒绝收货时间',
      `isWmsSku` tinyint(1) NOT NULL COMMENT '是否淘宝小家电',
      `sCode` varchar(10) NOT NULL COMMENT '库位编码',
      `tsCode` varchar(10) NOT NULL DEFAULT '' COMMENT '转运库房编码',
      `tsShippingTime` int(11) NOT NULL DEFAULT '0' COMMENT '转运时效（小时）',
      `status` smallint(3) NOT NULL COMMENT '状态',
      `productSn` varchar(60) NOT NULL COMMENT '商品条形码',
      `invoiceNumber` varchar(60) NOT NULL COMMENT '运单号',
      `expressName` varchar(255) NOT NULL COMMENT '快递公司',
      `invoiceExpressNumber` varchar(60) NOT NULL COMMENT '发票快递单号',
      `postMan` varchar(20) NOT NULL COMMENT '送货人',
      `postManPhone` varchar(15) NOT NULL COMMENT '送货人电话',
      `isNotice` smallint(5) NOT NULL COMMENT '是否开启预警',
      `noticeType` smallint(5) NOT NULL,
      `noticeRemark` varchar(255) NOT NULL,
      `noticeTime` varchar(8) NOT NULL COMMENT '预警时间',
      `shippingTime` int(10) NOT NULL COMMENT '发货时间',
      `lessOrderSn` varchar(50) NOT NULL COMMENT 'less 订单号',
      `waitGetLesShippingInfo` tinyint(1) unsigned NOT NULL COMMENT '是否等待获取LES物流配送节点信息',
      `getLesShippingCount` int(10) unsigned NOT NULL COMMENT '已获取LES配送节点信息的次数',
      `outping` varchar(20) NOT NULL COMMENT '出库凭证',
      `lessShipTime` int(10) NOT NULL COMMENT 'less出库时间',
      `closeTime` int(10) unsigned NOT NULL COMMENT '网单完成关闭或取消关闭时间',
      `isReceipt` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '是否需要发票',
      `isMakeReceipt` int(1) NOT NULL DEFAULT '1' COMMENT '开票状态',
      `receiptNum` text NOT NULL COMMENT '发票号',
      `receiptAddTime` varchar(30) NOT NULL COMMENT '开票时间',
      `makeReceiptType` tinyint(3) NOT NULL COMMENT '开票类型 0 初始值 1 库房开票  2 共享开票',
      `shippingMode` varchar(60) NOT NULL COMMENT '物流模式,值为B2B2C或B2C',
      `lastTimeForShippingMode` int(10) unsigned NOT NULL COMMENT '最后修改物流模式的时间',
      `lastEditorForShippingMode` varchar(200) NOT NULL COMMENT '最后修改物流模式的管理员',
      `systemRemark` text NOT NULL COMMENT '系统备注，不给用户显示',
      `tongshuaiWorkId` int(11) NOT NULL DEFAULT '-1' COMMENT '统帅定制作品ID',
      `orderPromotionId` int(10) unsigned NOT NULL COMMENT '下单立减活动ID',
      `orderPromotionAmount` decimal(10,2) unsigned NOT NULL COMMENT '下单立减金额',
      `externalSaleSettingId` int(10) unsigned NOT NULL COMMENT '淘宝套装设置ID',
      `recommendationId` int(10) unsigned NOT NULL COMMENT '推荐购买ID',
      `hasSendAlertNum` tinyint(1) unsigned NOT NULL COMMENT '是否已发送了购买数据报警邮件(短信)',
      `isNoLimitStockProduct` tinyint(1) unsigned NOT NULL COMMENT '是否是无限制库存量的商品',
      `hpRegisterDate` int(11) DEFAULT '0' COMMENT 'HP注册时间',
      `hpFailDate` int(11) DEFAULT '0' COMMENT 'HP派工失败时间',
      `hpFinishDate` int(11) DEFAULT '0' COMMENT 'HP派工成功时间',
      `hpReservationDate` int(11) NOT NULL DEFAULT '0' COMMENT 'HP回传预约送货时间',
      `shippingOpporunity` tinyint(4) NOT NULL DEFAULT '0' COMMENT '活动订单发货时机，0定金发货 1尾款发货',
      `isTimeoutFree` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否超时免单 0未设置 1是 2否',
      `itemShareAmount` decimal(10,2) DEFAULT '0.00' COMMENT '订单优惠价格分摊',
      `lessShipTInTime` int(10) DEFAULT '0' COMMENT 'less转运入库时间',
      `lessShipTOutTime` int(10) DEFAULT '0' COMMENT 'less转运出库时间',
      `cbsSecCode` varchar(10) DEFAULT '' COMMENT 'cbs库位',
      `points` int(11) DEFAULT '0' COMMENT '网单使用积分',
      `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
      `splitFlag` tinyint(3) unsigned NOT NULL COMMENT '拆单标志，0：未拆单；1：拆单后旧单；2：拆单后新单',
      `splitRelateCOrderSn` varchar(50) NOT NULL COMMENT '拆单关联单号',
      `channelId` tinyint(4) DEFAULT '0' COMMENT '区分EP和商城',
      `activityId2` int(11) NOT NULL DEFAULT '0' COMMENT '运营活动id',
      `pdOrderStatus` int(4) NOT NULL DEFAULT '0' COMMENT '日日单状态',
      `omsOrderSn` varchar(20) NOT NULL DEFAULT '' COMMENT '集团OMS单号',
      `couponCode` varchar(20) NOT NULL DEFAULT '' COMMENT '优惠码编码',
      `couponCodeValue` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '优惠码优惠金额',
      `storeId` int(10) unsigned NOT NULL DEFAULT '0',
      `storeType` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '店铺类型',
      `stockType` varchar(10) NOT NULL DEFAULT 'WA',
      `o2oType` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT 'o2o网单类型1非O2O网单2线下用户分销商城3商城分销旗舰店4创客',
      `brokerageType` varchar(100) DEFAULT NULL,
      `ogColor` varchar(30) DEFAULT NULL COMMENT '算法预留字段',
      PRIMARY KEY (`id`),
      KEY `orderId` (`orderId`),
      KEY `sCode` (`sCode`),
      KEY `cOrderSn` (`cOrderSn`),
      KEY `netPointId` (`netPointId`),
      KEY `isNotice` (`isNotice`),
      KEY `noticeTime` (`noticeTime`),
      KEY `closeTime` (`closeTime`),
      KEY `cPayTime` (`cPayTime`),
      KEY `productId` (`productId`),
      KEY `activityId` (`activityId`),
      KEY `idx_OrderProducts_cPaymentStatus_status` (`cPaymentStatus`,`status`),
      KEY `idx_OrderProducts_waitGetLesShippingInfo` (`waitGetLesShippingInfo`),
      KEY `idx_OrderProducts_status` (`status`),
      KEY `idx_OrderProducts_sku` (`sku`),
      KEY `ix_OrderProducts_lessShipTime` (`lessShipTime`),
      KEY `modified` (`modified`),
      KEY `ix_OrderProducts_receiptAddTime` (`receiptAddTime`),
      KEY `idx_pdOrderStatus` (`pdOrderStatus`),
      KEY `idx_lessShipTInTime` (`lessShipTInTime`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3853572 DEFAULT CHARSET=utf8;
  

• 用户表：tbl_users

    CREATE TABLE `tbl_users` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `siteId` int(10) unsigned NOT NULL,
      `avatarImageFileId` varchar(255) DEFAULT NULL,
      `email` varchar(120) DEFAULT NULL,
      `username` varchar(60) NOT NULL COMMENT '用户名',
      `password` varchar(32) DEFAULT NULL COMMENT '密码',
      `salt` varchar(10) DEFAULT NULL COMMENT '扰码',
      `registerTime` int(10) unsigned NOT NULL COMMENT '注册时间',
      `lastLoginTime` int(10) unsigned DEFAULT NULL COMMENT '最后登录时间',
      `lastLoginIp` varchar(15) DEFAULT NULL COMMENT '最后登录ip',
      `memberRankId` int(10) unsigned DEFAULT NULL COMMENT '特殊会员等级id，0表示非特殊会员等级',
      `bigCustomerId` int(10) unsigned DEFAULT NULL COMMENT '所属的大客户ID',
      `lastAddressId` int(10) unsigned DEFAULT NULL COMMENT '上次使用的收货地址',
      `lastPaymentCode` varchar(20) DEFAULT NULL COMMENT '上次使用的支付方式',
      `gender` tinyint(3) unsigned DEFAULT NULL COMMENT '性别：0保密1男2女',
      `birthday` date DEFAULT NULL COMMENT '生日',
      `qq` varchar(20) DEFAULT NULL,
      `job` varchar(60) DEFAULT NULL COMMENT '职业；1学生、2公务员、3军人、4警察、5教师、6白领',
      `mobile` varchar(15) DEFAULT NULL COMMENT '手机',
      `politicalFace` int(1) unsigned DEFAULT NULL COMMENT '政治面貌：1群众、2党员、3无党派人士',
      `nationality` varchar(20) DEFAULT NULL COMMENT '国籍：1中国大陆、2中国香港、3中国澳门、4中国台湾、5其他',
      `validateCode` varchar(32) DEFAULT NULL COMMENT '找回密码时的验证code',
      `pwdErrCount` tinyint(3) DEFAULT NULL COMMENT '密码输入错误次数',
      `source` varchar(20) DEFAULT NULL COMMENT '会员来源',
      `marriage` varchar(60) DEFAULT NULL COMMENT '婚姻状况：1未婚、2已婚、3离异',
      `money` decimal(15,2) DEFAULT NULL COMMENT '账户余额',
      `moneyPwd` varchar(32) DEFAULT NULL COMMENT '余额支付密码',
      `isEmailVerify` tinyint(1) DEFAULT NULL COMMENT '是否验证email',
      `isSmsVerify` tinyint(1) DEFAULT NULL COMMENT '是否验证短信',
      `smsVerifyCode` varchar(30) DEFAULT NULL COMMENT '邮件验证码',
      `emailVerifyCode` varchar(30) DEFAULT NULL COMMENT '短信验证码',
      `verifySendCoupon` tinyint(1) DEFAULT NULL COMMENT '是否验证发送优惠券',
      `canReceiveEmail` tinyint(1) DEFAULT NULL COMMENT '是否接收邮件',
      `modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
      `channelId` tinyint(4) DEFAULT '0' COMMENT '??EP???',
      `grade_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '等级ID',
      `nick_name` varchar(60) NOT NULL DEFAULT '' COMMENT '昵称',
      `is_blackList` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否黑名单 : 0:非黑名单  1：黑名单',
      PRIMARY KEY (`id`),
      KEY `siteId` (`siteId`,`email`),
      KEY `memberRankId` (`memberRankId`)
    ) ENGINE=InnoDB AUTO_INCREMENT=951 DEFAULT CHARSET=utf8;
  

• 行为日志表：tbl_logs

    CREATE TABLE `tbl_logs` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `log_id` varchar(50) DEFAULT NULL,
      `remote_ip` varchar(50) DEFAULT NULL,
      `site_global_ticket` varchar(250) DEFAULT NULL,
      `site_global_session` varchar(250) DEFAULT NULL,
      `global_user_id` varchar(50) DEFAULT NULL,
      `cookie_text` mediumtext,
      `user_agent` varchar(250) DEFAULT NULL,
      `ref_url` varchar(250) DEFAULT NULL,
      `loc_url` varchar(250) DEFAULT NULL,
      `log_time` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `log_time` (`log_time`)
    ) ENGINE=MyISAM AUTO_INCREMENT=1160286 DEFAULT CHARSET=utf8;
  

• 订单数据表：tbl_orders

    CREATE TABLE `tbl_orders` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `siteId` int(10) unsigned NOT NULL,
      `isTest` tinyint(1) unsigned NOT NULL COMMENT '是否是测试订单',
      `hasSync` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已同步(临时添加)',
      `isBackend` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '是否为后台添加的订单',
      `isBook` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '是否为后台添加的订单',
      `isCod` tinyint(1) unsigned NOT NULL COMMENT '是否是货到付款订单',
      `notAutoConfirm` tinyint(1) unsigned NOT NULL COMMENT '是否是非自动确认订单',
      `isPackage` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否为套装订单',
      `packageId` int(10) unsigned NOT NULL COMMENT '套装ID',
      `orderSn` varchar(50) NOT NULL COMMENT '订单号',
      `relationOrderSn` varchar(50) NOT NULL COMMENT '关联订单编号',
      `memberId` int(10) unsigned NOT NULL COMMENT '会员id',
      `predictId` int(10) unsigned NOT NULL COMMENT '会员购买预测ID',
      `memberEmail` varchar(120) NOT NULL COMMENT '会员邮件',
      `addTime` int(10) unsigned NOT NULL,
      `syncTime` int(10) unsigned NOT NULL COMMENT '同步到此表中的时间',
      `orderStatus` smallint(3) NOT NULL COMMENT '订单状态',
      `payTime` int(10) unsigned NOT NULL COMMENT '在线付款时间',
      `paymentStatus` smallint(3) unsigned NOT NULL COMMENT '付款状态：0 买家未付款 1 买家已付款 ',
      `receiptConsignee` varchar(20) NOT NULL COMMENT '发票收件人',
      `receiptAddress` varchar(255) NOT NULL COMMENT '发票地址',
      `receiptZipcode` varchar(20) NOT NULL COMMENT '发票邮编',
      `receiptMobile` varchar(30) NOT NULL COMMENT '发票联系电话',
      `productAmount` decimal(10,2) unsigned NOT NULL COMMENT '商品金额，等于订单中所有的商品的单价乘以数量之和',
      `orderAmount` decimal(10,2) unsigned NOT NULL COMMENT '订单总金额，等于商品总金额＋运费',
      `paidBalance` decimal(10,2) unsigned NOT NULL COMMENT '余额账户支付总金额',
      `giftCardAmount` decimal(10,2) unsigned NOT NULL COMMENT '礼品卡抵用金额',
      `paidAmount` decimal(10,2) unsigned NOT NULL COMMENT '已支付金额',
      `shippingAmount` decimal(10,2) NOT NULL COMMENT '淘宝运费',
      `totalEsAmount` decimal(10,2) unsigned NOT NULL COMMENT '网单中总的节能补贴金额之和',
      `usedCustomerBalanceAmount` decimal(10,2) unsigned NOT NULL COMMENT '使用的客户的余额支付金额',
      `customerId` int(10) unsigned NOT NULL COMMENT '用余额支付的客户ID',
      `bestShippingTime` varchar(100) NOT NULL COMMENT '最佳配送时间描述',
      `paymentCode` varchar(20) NOT NULL COMMENT '支付方式code',
      `payBankCode` varchar(20) NOT NULL COMMENT '网银代码',
      `paymentName` varchar(60) NOT NULL COMMENT '支付方式名称',
      `consignee` varchar(60) NOT NULL COMMENT '收货人',
      `originRegionName` varchar(255) NOT NULL COMMENT '原淘宝收货地址信息',
      `originAddress` varchar(255) NOT NULL COMMENT '原淘宝收货人详细收货信息',
      `province` int(10) unsigned NOT NULL COMMENT '收货地址中国省份',
      `city` int(10) unsigned NOT NULL COMMENT '收货地址中的城市',
      `region` int(10) unsigned NOT NULL COMMENT '收货地址中城市中的区',
      `street` int(10) unsigned NOT NULL COMMENT '街道ID',
      `markBuilding` int(10) NOT NULL COMMENT '标志建筑物',
      `poiId` varchar(64) NOT NULL DEFAULT '' COMMENT '标建ID',
      `poiName` varchar(100) DEFAULT '' COMMENT '标建名称',
      `regionName` varchar(200) NOT NULL COMMENT '地区名称（如：北京 北京 昌平区 兴寿镇）',
      `address` varchar(255) NOT NULL COMMENT '收货地址中用户输入的地址，一般是区以下的详细地址',
      `zipcode` varchar(20) NOT NULL COMMENT '收货地址中的邮编',
      `mobile` varchar(15) NOT NULL COMMENT '收货人手机号',
      `phone` varchar(20) NOT NULL COMMENT '收货人固定电话号',
      `receiptInfo` text NOT NULL COMMENT '发票信息，序列化数组array(''title'' =>.., ''receiptType'' =>..,''needReceipt'' => ..,''companyName'' =>..,''taxSpotNum'' =>..,''regAddress''=>..,''regPhone''=>..,''bank''=>..,''bankAccount''=>..)',
      `delayShipTime` int(10) unsigned NOT NULL COMMENT '延迟发货日期',
      `remark` text NOT NULL COMMENT '订单备注',
      `bankCode` varchar(255) DEFAULT NULL COMMENT '银行代码,用于银行直链支付',
      `agent` varchar(255) DEFAULT NULL COMMENT '处理人',
      `confirmTime` int(11) DEFAULT NULL COMMENT '确认时间',
      `firstConfirmTime` int(10) unsigned NOT NULL COMMENT '首次确认时间',
      `firstConfirmPerson` varchar(200) NOT NULL COMMENT '第一次确认人',
      `finishTime` int(11) DEFAULT NULL COMMENT '订单完成时间',
      `tradeSn` varchar(255) DEFAULT NULL COMMENT '在线支付交易流水号',
      `signCode` varchar(20) NOT NULL COMMENT '收货确认码',
      `source` varchar(30) NOT NULL COMMENT '订单来源',
      `sourceOrderSn` varchar(60) NOT NULL COMMENT '外部订单号',
      `onedayLimit` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否支持24小时限时达',
      `logisticsManner` int(1) NOT NULL COMMENT '物流评价',
      `afterSaleManner` int(1) NOT NULL COMMENT '售后评价',
      `personManner` int(1) NOT NULL COMMENT '人员态度',
      `visitRemark` varchar(400) NOT NULL COMMENT '回访备注',
      `visitTime` int(11) NOT NULL COMMENT '回访时间',
      `visitPerson` varchar(20) NOT NULL COMMENT '回访人',
      `sellPeople` varchar(20) NOT NULL COMMENT '销售代表',
      `sellPeopleManner` int(1) NOT NULL COMMENT '销售代表服务态度',
      `orderType` tinyint(2) NOT NULL COMMENT '订单类型 默认0 团购预付款 团购正式单 2',
      `hasReadTaobaoOrderComment` tinyint(1) unsigned NOT NULL COMMENT '是否已读取过淘宝订单评论',
      `memberInvoiceId` int(10) unsigned NOT NULL COMMENT '订单发票ID,MemberInvoices表的主键',
      `taobaoGroupId` int(10) unsigned NOT NULL COMMENT '淘宝万人团活动ID',
      `tradeType` varchar(100) NOT NULL COMMENT '交易类型,值参考淘宝',
      `stepTradeStatus` varchar(100) NOT NULL COMMENT '分阶段付款的订单状态,值参考淘宝',
      `stepPaidFee` decimal(10,2) NOT NULL COMMENT '分阶段付款的已付金额',
      `depositAmount` decimal(10,2) unsigned NOT NULL COMMENT '定金应付金额',
      `balanceAmount` decimal(10,2) unsigned NOT NULL COMMENT '尾款应付金额',
      `autoCancelDays` int(10) unsigned NOT NULL COMMENT '未付款过期的天数',
      `isNoLimitStockOrder` tinyint(1) unsigned NOT NULL COMMENT '是否是无库存限制订单',
      `ccbOrderReceivedLogId` int(10) unsigned NOT NULL COMMENT '建行订单接收日志ID',
      `ip` varchar(50) NOT NULL COMMENT '订单来源IP,针对商城前台订单',
      `isGiftCardOrder` tinyint(1) unsigned NOT NULL COMMENT '是否为礼品卡订单',
      `giftCardDownloadPassword` varchar(200) NOT NULL COMMENT '礼品卡下载密码',
      `giftCardFindMobile` varchar(20) NOT NULL COMMENT '礼品卡密码找回手机号',
      `autoConfirmNum` int(10) unsigned NOT NULL COMMENT '已自动确认的次数',
      `codConfirmPerson` varchar(100) NOT NULL COMMENT '货到付款确认人',
      `codConfirmTime` int(11) NOT NULL COMMENT '货到付款确认时间',
      `codConfirmRemark` varchar(255) NOT NULL COMMENT '货到付款确认备注',
      `codConfirmState` tinyint(1) unsigned NOT NULL COMMENT '货到侍确认状态0无需未确认,1待确认,2确认通过可以发货,3确认无效,订单可以取消',
      `paymentNoticeUrl` text NOT NULL COMMENT '付款结果通知URL',
      `addressLon` decimal(9,6) NOT NULL COMMENT '地址经度',
      `addressLat` decimal(9,6) NOT NULL COMMENT '地址纬度',
      `smConfirmStatus` tinyint(4) NOT NULL COMMENT '标建确认状态。1 = 初始状态；2 = 已发HP，等待确认；3 = 待人工处理；4 = 待自动处理；5 = 已确认',
      `smConfirmTime` int(10) NOT NULL COMMENT '请求发送HP时间，格式为时间戳',
      `smManualTime` int(10) DEFAULT '0' COMMENT '转人工确认时间',
      `smManualRemark` varchar(200) DEFAULT '' COMMENT '转人工确认备注',
      `isTogether` tinyint(3) unsigned NOT NULL COMMENT '货票通行',
      `isNotConfirm` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否是无需确认的订单',
      `tailPayTime` int(11) NOT NULL DEFAULT '0' COMMENT '尾款付款时间',
      `points` int(11) DEFAULT '0' COMMENT '网单使用积分',
      `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
      `channelId` tinyint(4) DEFAULT '0' COMMENT '区分EP和商城',
      `isProduceDaily` int(2) NOT NULL DEFAULT '0' COMMENT '是否日日单(1:是，0:否)',
      `couponCode` varchar(20) NOT NULL DEFAULT '' COMMENT '优惠码编码',
      `couponCodeValue` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '优惠码优惠金额',
      `ckCode` varchar(200) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`),
      UNIQUE KEY `ux_Orders_ordersn` (`orderSn`),
      KEY `memberId` (`memberId`),
      KEY `agent` (`agent`),
      KEY `addTime` (`addTime`),
      KEY `payTime` (`payTime`),
      KEY `orderStatus` (`orderStatus`),
      KEY `sourceOrderSn` (`sourceOrderSn`),
      KEY `smConfirmStatus` (`smConfirmStatus`),
      KEY `idx_orders_source_orderStatus_hasReadTaobaoOrderComment` (`source`,`orderStatus`,`hasReadTaobaoOrderComment`),
      KEY `idx_order_mobile` (`mobile`),
      KEY `idx_orders_codConfirmState` (`codConfirmState`),
      KEY `modified` (`modified`),
      KEY `tailPayTime` (`tailPayTime`),
      KEY `ix_Orders_syncTime` (`syncTime`),
      KEY `ix_Orders_relationOrderSn` (`relationOrderSn`),
      KEY `ix_Orders_consignee` (`consignee`),
      KEY `idx_firstConfirmTime` (`firstConfirmTime`),
      KEY `ix_Orders_paymentStatus` (`paymentStatus`)
    ) ENGINE=InnoDB AUTO_INCREMENT=120128 DEFAULT CHARSET=utf8;
  

同步源数据至 Hive 数据仓库

• 创建表

  • 行为日志表：tbl_logs

    /export/servers/sqoop/bin/sqoop create-hive-table \ --connect jdbc:mysql://bd001:3306/tags_dat \ --table tbl_logs \ --username root \ --password 123456 \ --hive-table tags_dat2.tbl_logs \ --fields-terminated-by '\t' \ --lines-terminated-by '\n'

  • 商品表：tbl_goods /export/servers/sqoop/bin/sqoop create-hive-table \ --connect jdbc:mysql://bd001:3306/tags_dat \ --table tbl_goods \ --username root \ --password 123456 \ --hive-table tags_dat2.tbl_goods \ --fields-terminated-by '\t' \ --lines-terminated-by '\n'

  • 订单数据表：tbl_orders

    /export/servers/sqoop/bin/sqoop create-hive-table \
    --connect jdbc:mysql://bd001:3306/tags_dat \
    --table tbl_orders \
    --username root \
    --password 123456 \
    --hive-table tags_dat2.tbl_orders \
    --fields-terminated-by '\t' \
    --lines-terminated-by '\n'
    

  • 用户信息表：tbl_users /export/servers/sqoop/bin/sqoop create-hive-table \ --connect jdbc:mysql://bd001:3306/tags_dat \ --table tbl_users \ --username root \ --password 123456 \ --hive-table tags_dat2.tbl_users \ --fields-terminated-by '\t' \ --lines-terminated-by '\n'

• 导入数据至 Hive 表

  • 使用 Sqoop 将 MySQL 数据库表中的数据导入到 Hive 表中（本质就是存储在HDFS上）。
  • 行为日志表：tbl_logs

    /export/servers/sqoop/bin/sqoop import \
    --connect jdbc:mysql://bd001:3306/tags_dat \
    --username root \
    --password 123456 \
    --table tbl_logs \
    --direct \
    --hive-overwrite \
    --delete-target-dir \
    --fields-terminated-by '\t' \
    --lines-terminated-by '\n' \
    --hive-table tags_dat2.tbl_logs \
    --hive-import \
    --num-mappers 20
    

  • 商品表：tbl_goods

    /export/servers/sqoop/bin/sqoop import \
    --connect jdbc:mysql://bd001:3306/tags_dat \
    --username root \
    --password 123456 \
    --table tbl_goods \
    --direct \
    --hive-overwrite \
    --delete-target-dir \
    --fields-terminated-by '\t' \
    --lines-terminated-by '\n' \
    --hive-table tags_dat2.tbl_goods \
    --hive-import \
    --num-mappers 5
    

  • 订单数据表：tbl_orders

    /export/servers/sqoop/bin/sqoop import \
    --connect jdbc:mysql://bd001:3306/tags_dat \
    --username root \
    --password 123456 \
    --table tbl_orders \
    --direct \
    --hive-overwrite \
    --delete-target-dir \
    --fields-terminated-by '\t' \
    --lines-terminated-by '\n' \
    --hive-table tags_dat2.tbl_orders \
    --hive-import \
    --num-mappers 10
    

  • 用户信息表：tbl_users

    /export/servers/sqoop/bin/sqoop import \
    --connect jdbc:mysql://bd001:3306/tags_dat \
    --username root \
    --password 123456 \
    --table tbl_users \
    --direct \
    --hive-overwrite \
    --delete-target-dir \
    --fields-terminated-by '\t' \
    --lines-terminated-by '\n' \
    --hive-table tags_dat2.tbl_users \
    --hive-import \
    --num-mappers 1
    

数据导入 HBase

• Sqoop 直接导入
  • 可以使用 SQOOP 将 MySQL 表的数据导入到 HBase 表中，指定表的名称、列簇及 RowKey；
  • 范例如下所示：

    /export/servers/sqoop/bin/sqoop import \
    -D sqoop.hbase.add.row.key=true \
    --connect jdbc:mysql://bd001:3306/tags_dat \
    --username root \
    --password 123456 \
    --table tbl_users \
    --hbase-create-table \
    --hbase-table tbl_users2 \
    --column-family detail \
    --hbase-row-key id \
    --num-mappers 2
    

  • 参数含义解释：

    -D sqoop.hbase.add.row.key=true     是否将rowkey相关字段写入列族中，默认为false，默认情况下你将在列族中看不到任何row key中的字段。注意，该参数必须放在import之后。
        
    --hbase-create-table     如果hbase中该表不存在则创建
        
    --hbase-table   对应的hbase表名
        
    --hbase-row-key   hbase表中的rowkey,注意格式
        
    --column-family   hbase表的列族
    

• HBase ImportTSV
  • ImportTSV功能描述：
    • 将tsv（也可以是csv，每行数据中各个字段使用分隔符分割）格式文本数据，加载到HBase表中。
  • 直接导入Put方式

    HADOOP_HOME=/export/servers/hadoop
    HBASE_HOME=/export/servers/hbase
    HADOOP_CLASSPATH=`${HBASE_HOME}/bin/hbase mapredcp`:${HBASE_HOME}/conf ${HADOOP_HOME}/bin/yarn jar ${HBASE_HOME}/lib/hbase-server-1.2.0-cdh5.14.0.jar \
    importtsv \
    -Dimporttsv.columns=HBASE_ROW_KEY,detail:log_id,detail:remote_ip,detail:site_global_ticket,detail:site_global_session,detail:global_user_id,detail:cookie_text,detail:user_agent,detail:ref_url,detail:loc_url,detail:log_time \
    tbl_logs2 \
    /user/hive/warehouse/tags_dat2.db/tbl_logs
    

  • 上述命令本质上运行一个 MapReduce 应用程序，将文本文件中每行数据转换封装到 Put 对象，然后插入到 HBase 表中。

  • 转换为 HFile 文件，再加载至表
    • 生成 HFILES 文件

      HADOOP_HOME=/export/servers/hadoop
      HBASE_HOME=/export/servers/hbase
      HADOOP_CLASSPATH=`${HBASE_HOME}/bin/hbase mapredcp`:${HBASE_HOME}/conf ${HADOOP_HOME}/bin/yarn jar ${HBASE_HOME}/lib/hbase-server-1.2.0-cdh5.14.0.jar \
      importtsv \
      -Dimporttsv.bulk.output=hdfs://bd001:8020/datas/output_hfile/tbl_tag_logs \
      -Dimporttsv.columns=HBASE_ROW_KEY,detail:log_id,detail:remote_ip,detail:site_global_ticket,detail:site_global_session,detail:global_user_id,detail:cookie_text,detail:user_agent,detail:ref_url,detail:loc_url,detail:log_time \
      tbl_logs2 \
      /user/hive/warehouse/tags_dat2.db/tbl_logs
      

    • 将 HFILE 文件加载到表中

      HADOOP_CLASSPATH=`${HBASE_HOME}/bin/hbase mapredcp`:${HBASE_HOME}/conf ${HADOOP_HOME}/bin/yarn jar \
      ${HBASE_HOME}/lib/hbase-server-1.2.0-cdh5.14.0.jar \
      completebulkload \
      hdfs://bd001:8020/datas/output_hfile/tbl_tag_logs \
      tbl_logs2
      

    • 这种方法的缺点是：
      • ROWKEY不能是组合主键，只能是某一个字段
      • 当表中列很多时，书写-Dimporttsv.columns值时很麻烦，容易出错
• HBase Bulkload

  • 在大量数据需要写入HBase时，通常有 put 方式和 bulkLoad 两种方式。

    • put方式为单条插入
      • 在 put 数据时会先将数据的更新操作信息和数据信息写入 WAL，在写入到 WAL 后，数据就会被放到 MemStore 中，当 MemStore 满后数据就会被 flush 到磁盘(即形成 HFile 文件)；
      • 这种写操作过程会涉及到 flush、split、compaction 等操作，容易造成节点不稳定，数据导入慢，耗费资源等问题；
      • 在海量数据的导入过程极大的消耗了系统性能，避免这些问题最好的方法就是使用 BulkLoad 的方式来加载数据到HBase中。

      val put = new Put(rowKeyByts)
      put.addColumn(cf, column, value)
      put.addColumn(cf, column, value)
      put.addColumn(cf, column, value)
      put.addColumn(cf, column, value)
            
      table.put(put)
      

    • BulkLoader
      • BulkLoader 利用 HBase 数据按照 HFile 格式存储在 HDFS 的原理，使用 MapReduce 直接批量生成 HFile 格式文件后，RegionServers 再将 HFile 文件移动到相应的 Region 目录下。
      • Extract
        • 异构数据源数据导入到 HDFS 之上。
      • Transform
        • 通过用户代码，可以是 MR 或者 Spark 任务将数据转化为 HFile。
      • Load
        • HFile 通过 loadIncrementalHFiles 调用将 HFile 放置到 Region 对应的 HDFS 目录上，该过程可能涉及到文件切分。
      • 不会触发 WAL 预写日志，当表还没有数据时进行数据导入不会产生 Flush 和 Split。
      • 减少接口调用的消耗，是一种快速写入的优化方式。

• Previous
  数据结构：布隆过滤器的工作原理
• Next
  用户画像：Hive 数仓各层表设计示例