1. 使用sqoop抽取mysql数据到hive mysql --> hive: sqoop import --connect jdbc:mysql://rr-wz9x0j6ud51m8e67co.mysql.rds.aliyuncs.com:3306/loan --username loan_read --password "MintqRead!1" --table t_customer_vip_flow --columns "" -m 2 --fields-terminated-by "^A" --bindir /home/hadoop/edw/etl/sqoop/classes/bdl/ --outdir /home/hadoop/edw/etl/sqoop/src/bdl/ --warehouse-dir /user/hive/warehouse/bdl.db --hive-import --hive-table bdl.bdl_loan_t_customer_vip_flow --null-string '\\N' --null-non-string '\\N' --hive-drop-import-delims 2. 使用hive映射mongodb表 从mongodb抽取数据到hive mongo --> hive: CREATE [EXTERNAL] TABLE ( ) ROW FORMAT SERDE 'com.mongodb.hadoop.hive.BSONSerDe' STORED BY 'com.mongodb.hadoop.hive.MongoStorageHandler' WITH SERDEPROPERTIES ( 'mongo.columns.mapping'='', 'serialization.format='1' ) TBLPROPERTIES ( 'mongo.uri'='' ); 2.1 映射表示例： set mongo.input.split_size=n; CREATE EXTERNAL TABLE mongo.installmentTongdun( id string COMMENT 'from deserializer', customerId string COMMENT 'from deserializer', createTime string COMMENT 'from deserializer', tongdunRegsterPlatformNum7d int COMMENT 'from deserializer', tongdunRegsterPlatformNum30d int COMMENT 'from deserializer') ROW FORMAT SERDE 'com.mongodb.hadoop.hive.BSONSerDe' STORED BY 'com.mongodb.hadoop.hive.MongoStorageHandler' WITH SERDEPROPERTIES ( 'mongo.columns.mapping'='{\"id\":\"_id\",\"customerId\":\"customerId\",\"createTime\":\"createTime\",\"tongdunRegsterPlatformNum7d\":\"tongdunRegsterPlatformNum7d\",\"tongdunRegsterPlatformNum30d\":\"tongdunRegsterPlatformNum30d\"}', 'serialization.format'='1') TBLPROPERTIES ( 'mongo.uri'='mongodb://read:riskRead@192.168.2.12:20000/risk.installmentTongdun') CREATE EXTERNAL TABLE `activeclick`( `id` string COMMENT 'from deserializer', `class` string COMMENT 'from deserializer', `device` string COMMENT 'from deserializer', `deviceid` string COMMENT 'from deserializer', `ip` string COMMENT 'from deserializer', `ipint` int COMMENT 'from deserializer', `os` string COMMENT 'from deserializer', `devicetype` string COMMENT 'from deserializer', `clicktime` timestamp COMMENT 'from deserializer', `appid` string COMMENT 'from deserializer', `appversion` string COMMENT 'from deserializer', `type` string COMMENT 'from deserializer', `channel` string COMMENT 'from deserializer', `source` string COMMENT 'from deserializer', `campaign` string COMMENT 'from deserializer', `adtime` timestamp COMMENT 'from deserializer', `advertiseid` string COMMENT 'from deserializer', `keywordid` string COMMENT 'from deserializer', `gapday` bigint COMMENT 'from deserializer') ROW FORMAT SERDE 'com.mongodb.hadoop.hive.BSONSerDe' STORED BY 'com.mongodb.hadoop.hive.MongoStorageHandler' WITH SERDEPROPERTIES ( 'mongo.columns.mapping'='{\n\"id\":\"_id\",\n\"class\":\"_class\",\n\"device\":\"device\",\n\"deviceId\":\"deviceId\",\n\"ip\":\"ip\",\n\"ipInt\":\"ipInt\",\n\"os\":\"os\",\n\"deviceType\":\"deviceType\",\n\"clickTime\":\"clickTime\",\n\"appId\":\"appId\",\n\"appVersion\":\"appVersion\",\n\"type\":\"type\",\n\"channel\":\"channel\",\n\"source\":\"source\",\n\"campaign\":\"campaign\",\n\"adTime\":\"adTime\",\n\"advertiseId\":\"advertiseId\",\n\"keywordId\":\"keywordId\",\n\"gapDay\":\"gapDay\"}', 'serialization.format'='1') LOCATION 'hdfs://nameservice1/user/hive/warehouse/mongo.db/activeclick' TBLPROPERTIES ( 'mongo.uri'='mongodb://120.77.46.159:27017/dev.activeClick', 'transient_lastDdlTime'='1519366707') CREATE EXTERNAL TABLE `apply`( `id` string COMMENT 'from deserializer', `class` string COMMENT 'from deserializer', `applyid` bigint COMMENT 'from deserializer', `orderno` string COMMENT 'from deserializer', `customerid` bigint COMMENT 'from deserializer', `quickapplyflag` boolean COMMENT 'from deserializer', `productid` string COMMENT 'from deserializer', `createtime` timestamp COMMENT 'from deserializer', `newflag` boolean COMMENT 'from deserializer', `device` string COMMENT 'from deserializer', `deviceid` string COMMENT 'from deserializer', `ip` string COMMENT 'from deserializer', `ipint` int COMMENT 'from deserializer', `os` string COMMENT 'from deserializer', `devicetype` string COMMENT 'from deserializer', `clicktime` timestamp COMMENT 'from deserializer', `appid` string COMMENT 'from deserializer', `appversion` string COMMENT 'from deserializer', `type` string COMMENT 'from deserializer', `channel` string COMMENT 'from deserializer', `source` string COMMENT 'from deserializer', `campaign` string COMMENT 'from deserializer', `adtime` timestamp COMMENT 'from deserializer', `advertiseid` string COMMENT 'from deserializer', `keywordid` string COMMENT 'from deserializer', `gapday` bigint COMMENT 'from deserializer') ROW FORMAT SERDE 'com.mongodb.hadoop.hive.BSONSerDe' STORED BY 'com.mongodb.hadoop.hive.MongoStorageHandler' WITH SERDEPROPERTIES ( 'mongo.columns.mapping'='{\n\"id\":\"_id\",\n\"class\":\"_class\",\n\"applyId\":\"applyId\",\n\"orderNo\":\"orderNo\",\n\"customerId\":\"customerId\",\n\"quickApplyFlag\":\"quickApplyFlag\",\n\"productId\":\"productId\",\n\"createTime\":\"createTime\",\n\"newFlag\":\"newFlag\",\n\"device\":\"device\",\n\"deviceId\":\"deviceId\",\n\"ip\":\"ip\",\n\"ipInt\":\"ipInt\",\n\"os\":\"os\",\n\"deviceType\":\"deviceType\",\n\"clickTime\":\"clickTime\",\n\"appId\":\"appId\",\n\"appVersion\":\"appVersion\",\n\"type\":\"type\",\n\"channel\":\"channel\",\n\"source\":\"source\",\n\"campaign\":\"campaign\",\n\"adTime\":\"adTime\",\n\"advertiseId\":\"advertiseId\",\n\"keywordId\":\"keywordId\",\n\"gapDay\":\"gapDay\"}', 'serialization.format'='1') LOCATION 'hdfs://nameservice1/user/hive/warehouse/mongo.db/apply' TBLPROPERTIES ( 'mongo.uri'='mongodb://120.77.46.159:27017/dev.apply', 'transient_lastDdlTime'='1519375799') CREATE EXTERNAL TABLE `bdl_risk_control_result`( `id` string COMMENT 'from deserializer', `class` string COMMENT 'from deserializer', `customerid` bigint COMMENT 'from deserializer', `createtime` timestamp COMMENT 'from deserializer', `risklevel` string COMMENT 'from deserializer', `antifraudrulesresult` string COMMENT 'from deserializer', `pipelinename` string COMMENT 'from deserializer', `humantrial` string COMMENT 'from deserializer', `existloanwith4fieldsxqd` string COMMENT 'from deserializer', `denied30dayswith4fieldsxqd` string COMMENT 'from deserializer', `referenceoveringoroveredhbw` string COMMENT 'from deserializer', `existloanwith6fieldshbw` string COMMENT 'from deserializer', `denied30dayswith6fieldshbw` string COMMENT 'from deserializer', `duplicatecustomerwith6fieldshbw` string COMMENT 'from deserializer', `installsimulategps` string COMMENT 'from deserializer', `installsimulations` string COMMENT 'from deserializer', `samegps` string COMMENT 'from deserializer', `blacklist` string COMMENT 'from deserializer', `duplicatecontacts` string COMMENT 'from deserializer', `messagedanger` string COMMENT 'from deserializer', `gendermatchingidcard` string COMMENT 'from deserializer', `invalidage` string COMMENT 'from deserializer', `deniedbylivescore` string COMMENT 'from deserializer', `deniedbylikescreenshotscore` string COMMENT 'from deserializer', `pinganblacklist` string COMMENT 'from deserializer', `shumeimaxoverduelevel` string COMMENT 'from deserializer', `xinyanempty` string COMMENT 'from deserializer', `xinyanadviceblack` string COMMENT 'from deserializer', `xinyandeductfaillast1month` string COMMENT 'from deserializer', `operatorphonenotbankbindphone` string COMMENT 'from deserializer', `operatorempty` string COMMENT 'from deserializer', `basecreditmodelscore` string COMMENT 'from deserializer', `juxinliempty` string COMMENT 'from deserializer', `juxinlisearchedorg` string COMMENT 'from deserializer', `juxinlitoquerycnt6` string COMMENT 'from deserializer', `juxinlicntrouter` string COMMENT 'from deserializer', `yimeiempty` string COMMENT 'from deserializer', `yimeiapplyplatformwith30days` string COMMENT 'from deserializer', `yimeioverdueplatformwith7days` string COMMENT 'from deserializer', `tongdunapplyplatformwith7days` string COMMENT 'from deserializer', `tongdunapplyplatformwith30days` string COMMENT 'from deserializer', `alipayempty` string COMMENT 'from deserializer', `alipaynocertified` string COMMENT 'from deserializer', `notownalipaynameoridcard` string COMMENT 'from deserializer', `alipaybindphonenotoperatorphone` string COMMENT 'from deserializer', `alipayregisterdate` string COMMENT 'from deserializer', `alipayreceiveaddressempty` string COMMENT 'from deserializer', `alipaybankbindphonenotoperatorphone` string COMMENT 'from deserializer', `receiveaddressphoneneiterownnorreference` string COMMENT 'from deserializer', `alipayjiebeioverdue` string COMMENT 'from deserializer', `customerrating` string COMMENT 'from deserializer') ROW FORMAT SERDE 'com.mongodb.hadoop.hive.BSONSerDe' STORED BY 'com.mongodb.hadoop.hive.MongoStorageHandler' WITH SERDEPROPERTIES ( 'mongo.columns.mapping'='{\n\"id\":\"_id\",\"class\":\"_class\",\"customerId\":\"customerId\",\"createTime\":\"createTime\",\"riskLevel\":\"riskLevel\",\"antifraudRulesResult\":\"antifraudRulesResult\",\n\"pipeLineName\":\"pipeLineName\",\"humanTrial\":\"humanTrial\",\"existLoanWith4fieldsXqd\":\"existLoanWith4fieldsXqd\",\"denied30daysWith4fieldsXqd\":\"denied30daysWith4fieldsXqd\",\n\"referenceOveringOrOveredHbw\":\"referenceOveringOrOveredHbw\",\"existLoanWith6fieldsHbw\":\"existLoanWith6fieldsHbw\",\"denied30daysWith6fieldsHbw\":\"denied30daysWith6fieldsHbw\",\n\"duplicateCustomerWith6fieldsHbw\":\"duplicateCustomerWith6fieldsHbw\",\"installSimulateGps\":\"installSimulateGps\",\"installSimulations\":\"installSimulations\",\"sameGps\":\"sameGps\",\n\"blacklist\":\"blacklist\",\"duplicateContacts\":\"duplicateContacts\",\"messageDanger\":\"messageDanger\",\"genderMatchingIdcard\":\"genderMatchingIdcard\",\"invalidAge\":\"invalidAge\",\n\"deniedByLiveScore\":\"deniedByLiveScore\",\"deniedByLikeScreenshotScore\":\"deniedByLikeScreenshotScore\",\"pinganBlacklist\":\"pinganBlacklist\",\"shumeiMaxOverdueLevel\":\"shumeiMaxOverdueLevel\",\n\"xinyanEmpty\":\"xinyanEmpty\",\"xinyanAdviceBlack\":\"xinyanAdviceBlack\",\"xinyanDeductFailLast1month\":\"xinyanDeductFailLast1month\",\"operatorPhoneNotBankBindPhone\":\"operatorPhoneNotBankBindPhone\",\n\"operatorEmpty\":\"operatorEmpty\",\"baseCreditModelScore\":\"baseCreditModelScore\",\"juxinliEmpty\":\"juxinliEmpty\",\"juxinliSearchedOrg\":\"juxinliSearchedOrg\",\"juxinliToQueryCnt6\":\"juxinliToQueryCnt6\",\n\"juxinliCntRouter\":\"juxinliCntRouter\",\"yimeiEmpty\":\"yimeiEmpty\",\"yimeiApplyPlatformWith30days\":\"yimeiApplyPlatformWith30days\",\"yimeiOverduePlatformWith7days\":\"yimeiOverduePlatformWith7days\",\n\"tongdunApplyPlatformWith7days\":\"tongdunApplyPlatformWith7days\",\"tongdunApplyPlatformWith30days\":\"tongdunApplyPlatformWith30days\",\"alipayEmpty\":\"alipayEmpty\",\n\"alipayNoCertified\":\"alipayNoCertified\",\"notOwnAlipayNameOrIdcard\":\"notOwnAlipayNameOrIdcard\",\"alipayBindPhoneNotOperatorPhone\":\"alipayBindPhoneNotOperatorPhone\",\n\"alipayRegisterDate\":\"alipayRegisterDate\",\"alipayReceiveAddressEmpty\":\"alipayReceiveAddressEmpty\",\"alipayBankBindPhoneNotOperatorPhone\":\"alipayBankBindPhoneNotOperatorPhone\",\n\"receiveAddressPhoneNeiterOwnNorReference\":\"receiveAddressPhoneNeiterOwnNorReference\",\"alipayjiebeiOverdue\":\"alipayjiebeiOverdue\",\"customerRating\":\"customerRating\"}', 'serialization.format'='1') LOCATION 'hdfs://nameservice1/user/hive/warehouse/mongo.db/bdl_risk_control_result' TBLPROPERTIES ( 'mongo.uri'='mongodb://fenqi:mintq_fenqi@dds-2ze7e127aece1d041327-pub.mongodb.rds.aliyuncs.com:3717,dds-2ze7e127aece1d042750-pub.mongodb.rds.aliyuncs.com:3717/fenqi.installmentRiskControlResult?replicaSet=mgset-5089851', 'transient_lastDdlTime'='1517208449') CREATE EXTERNAL TABLE `bdl_risk_control_result_1`( `id` string COMMENT 'from deserializer', `class` string COMMENT 'from deserializer', `customerid` bigint COMMENT 'from deserializer', `createtime` timestamp COMMENT 'from deserializer', `risklevel` string COMMENT 'from deserializer', `antifraudrulesresult` string COMMENT 'from deserializer', `pipelinename` string COMMENT 'from deserializer', `humantrial` string COMMENT 'from deserializer', `existloanwith4fieldsxqd` string COMMENT 'from deserializer', `denied30dayswith4fieldsxqd` string COMMENT 'from deserializer', `referenceoveringoroveredhbw` string COMMENT 'from deserializer', `existloanwith6fieldshbw` string COMMENT 'from deserializer', `denied30dayswith6fieldshbw` string COMMENT 'from deserializer', `duplicatecustomerwith6fieldshbw` string COMMENT 'from deserializer', `installsimulategps` string COMMENT 'from deserializer', `installsimulations` string COMMENT 'from deserializer', `samegps` string COMMENT 'from deserializer', `blacklist` string COMMENT 'from deserializer', `duplicatecontacts` string COMMENT 'from deserializer', `messagedanger` string COMMENT 'from deserializer', `gendermatchingidcard` string COMMENT 'from deserializer', `invalidage` string COMMENT 'from deserializer', `deniedbylivescore` string COMMENT 'from deserializer', `deniedbylikescreenshotscore` string COMMENT 'from deserializer', `pinganblacklist` string COMMENT 'from deserializer', `shumeimaxoverduelevel` string COMMENT 'from deserializer', `xinyanempty` string COMMENT 'from deserializer', `xinyanadviceblack` string COMMENT 'from deserializer', `xinyandeductfaillast1month` string COMMENT 'from deserializer', `operatorphonenotbankbindphone` string COMMENT 'from deserializer', `operatorempty` string COMMENT 'from deserializer', `basecreditmodelscore` string COMMENT 'from deserializer', `juxinliempty` string COMMENT 'from deserializer', `juxinlisearchedorg` string COMMENT 'from deserializer', `juxinlitoquerycnt6` string COMMENT 'from deserializer', `juxinlicntrouter` string COMMENT 'from deserializer', `yimeiempty` string COMMENT 'from deserializer', `yimeiapplyplatformwith30days` string COMMENT 'from deserializer', `yimeioverdueplatformwith7days` string COMMENT 'from deserializer', `tongdunapplyplatformwith7days` string COMMENT 'from deserializer', `tongdunapplyplatformwith30days` string COMMENT 'from deserializer', `alipayempty` string COMMENT 'from deserializer', `alipaynocertified` string COMMENT 'from deserializer', `notownalipaynameoridcard` string COMMENT 'from deserializer', `alipaybindphonenotoperatorphone` string COMMENT 'from deserializer', `alipayregisterdate` string COMMENT 'from deserializer', `alipayreceiveaddressempty` string COMMENT 'from deserializer', `alipaybankbindphonenotoperatorphone` string COMMENT 'from deserializer', `receiveaddressphoneneiterownnorreference` string COMMENT 'from deserializer', `alipayjiebeioverdue` string COMMENT 'from deserializer', `customerrating` string COMMENT 'from deserializer') ROW FORMAT SERDE 'com.mongodb.hadoop.hive.BSONSerDe' STORED BY 'com.mongodb.hadoop.hive.MongoStorageHandler' WITH SERDEPROPERTIES ( 'mongo.columns.mapping'='{\n\"id\":\"_id\",\"class\":\"_class\",\"customerId\":\"customerId\",\"createTime\":\"createTime\",\"riskLevel\":\"riskLevel\",\"antifraudRulesResult\":\"antifraudRulesResult\",\n\"pipeLineName\":\"pipeLineName\",\"humanTrial\":\"humanTrial\",\"existLoanWith4fieldsXqd\":\"existLoanWith4fieldsXqd\",\"denied30daysWith4fieldsXqd\":\"denied30daysWith4fieldsXqd\",\n\"referenceOveringOrOveredHbw\":\"referenceOveringOrOveredHbw\",\"existLoanWith6fieldsHbw\":\"existLoanWith6fieldsHbw\",\"denied30daysWith6fieldsHbw\":\"denied30daysWith6fieldsHbw\",\n\"duplicateCustomerWith6fieldsHbw\":\"duplicateCustomerWith6fieldsHbw\",\"installSimulateGps\":\"installSimulateGps\",\"installSimulations\":\"installSimulations\",\"sameGps\":\"sameGps\",\n\"blacklist\":\"blacklist\",\"duplicateContacts\":\"duplicateContacts\",\"messageDanger\":\"messageDanger\",\"genderMatchingIdcard\":\"genderMatchingIdcard\",\"invalidAge\":\"invalidAge\",\n\"deniedByLiveScore\":\"deniedByLiveScore\",\"deniedByLikeScreenshotScore\":\"deniedByLikeScreenshotScore\",\"pinganBlacklist\":\"pinganBlacklist\",\"shumeiMaxOverdueLevel\":\"shumeiMaxOverdueLevel\",\n\"xinyanEmpty\":\"xinyanEmpty\",\"xinyanAdviceBlack\":\"xinyanAdviceBlack\",\"xinyanDeductFailLast1month\":\"xinyanDeductFailLast1month\",\"operatorPhoneNotBankBindPhone\":\"operatorPhoneNotBankBindPhone\",\n\"operatorEmpty\":\"operatorEmpty\",\"baseCreditModelScore\":\"baseCreditModelScore\",\"juxinliEmpty\":\"juxinliEmpty\",\"juxinliSearchedOrg\":\"juxinliSearchedOrg\",\"juxinliToQueryCnt6\":\"juxinliToQueryCnt6\",\n\"juxinliCntRouter\":\"juxinliCntRouter\",\"yimeiEmpty\":\"yimeiEmpty\",\"yimeiApplyPlatformWith30days\":\"yimeiApplyPlatformWith30days\",\"yimeiOverduePlatformWith7days\":\"yimeiOverduePlatformWith7days\",\n\"tongdunApplyPlatformWith7days\":\"tongdunApplyPlatformWith7days\",\"tongdunApplyPlatformWith30days\":\"tongdunApplyPlatformWith30days\",\"alipayEmpty\":\"alipayEmpty\",\n\"alipayNoCertified\":\"alipayNoCertified\",\"notOwnAlipayNameOrIdcard\":\"notOwnAlipayNameOrIdcard\",\"alipayBindPhoneNotOperatorPhone\":\"alipayBindPhoneNotOperatorPhone\",\n\"alipayRegisterDate\":\"alipayRegisterDate\",\"alipayReceiveAddressEmpty\":\"alipayReceiveAddressEmpty\",\"alipayBankBindPhoneNotOperatorPhone\":\"alipayBankBindPhoneNotOperatorPhone\",\n\"receiveAddressPhoneNeiterOwnNorReference\":\"receiveAddressPhoneNeiterOwnNorReference\",\"alipayjiebeiOverdue\":\"alipayjiebeiOverdue\",\"customerRating\":\"customerRating\"}', 'serialization.format'='1') LOCATION 'hdfs://nameservice1/user/hive/warehouse/mongo.db/bdl_risk_control_result_1' TBLPROPERTIES ( 'COLUMN_STATS_ACCURATE'='false', 'mongo.uri'='mongodb://fenqi:mintq_fenqi@dds-2ze7e127aece1d041327-pub.mongodb.rds.aliyuncs.com:3717,dds-2ze7e127aece1d042750-pub.mongodb.rds.aliyuncs.com:3717/fenqi.installmentRiskControlResult?replicaSet=mgset-5089851', 'numFiles'='0', 'numRows'='-1', 'rawDataSize'='-1', 'totalSize'='0', 'transient_lastDdlTime'='1517208144') CREATE EXTERNAL TABLE `customersign`( `id` string COMMENT 'from deserializer', `class` string COMMENT 'from deserializer', `customerid` bigint COMMENT 'from deserializer', `cellphone` string COMMENT 'from deserializer', `device` string COMMENT 'from deserializer', `deviceid` string COMMENT 'from deserializer', `ip` string COMMENT 'from deserializer', `ipint` int COMMENT 'from deserializer', `os` string COMMENT 'from deserializer', `devicetype` string COMMENT 'from deserializer', `clicktime` timestamp COMMENT 'from deserializer', `appid` string COMMENT 'from deserializer', `appversion` string COMMENT 'from deserializer', `type` string COMMENT 'from deserializer', `channel` string COMMENT 'from deserializer', `source` string COMMENT 'from deserializer', `campaign` string COMMENT 'from deserializer', `adtime` timestamp COMMENT 'from deserializer', `advertiseid` string COMMENT 'from deserializer', `keywordid` string COMMENT 'from deserializer', `gapday` bigint COMMENT 'from deserializer') ROW FORMAT SERDE 'com.mongodb.hadoop.hive.BSONSerDe' STORED BY 'com.mongodb.hadoop.hive.MongoStorageHandler' WITH SERDEPROPERTIES ( 'mongo.columns.mapping'='{\n\"id\":\"_id\",\n\"class\":\"_class\",\n\"customerId\":\"customerId\",\n\"cellPhone\":\"cellPhone\",\n\"device\":\"device\",\n\"deviceId\":\"deviceId\",\n\"ip\":\"ip\",\n\"ipInt\":\"ipInt\",\n\"os\":\"os\",\n\"deviceType\":\"deviceType\",\n\"clickTime\":\"clickTime\",\n\"appId\":\"appId\",\n\"appVersion\":\"appVersion\",\n\"type\":\"type\",\n\"channel\":\"channel\",\n\"source\":\"source\",\n\"campaign\":\"campaign\",\n\"adTime\":\"adTime\",\n\"advertiseId\":\"advertiseId\",\n\"keywordId\":\"keywordId\",\n\"gapDay\":\"gapDay\"}', 'serialization.format'='1') LOCATION 'hdfs://nameservice1/user/hive/warehouse/mongo.db/customersign' TBLPROPERTIES ( 'mongo.uri'='mongodb://120.77.46.159:27017/dev.customerSign', 'transient_lastDdlTime'='1519375219') CREATE EXTERNAL TABLE `loan`( `id` string COMMENT 'from deserializer', `class` string COMMENT 'from deserializer', `applyid` bigint COMMENT 'from deserializer', `loanid` bigint COMMENT 'from deserializer', `orderno` string COMMENT 'from deserializer', `productid` string COMMENT 'from deserializer', `error` boolean COMMENT 'from deserializer', `newflag` boolean COMMENT 'from deserializer', `device` string COMMENT 'from deserializer', `deviceid` string COMMENT 'from deserializer', `ip` string COMMENT 'from deserializer', `ipint` int COMMENT 'from deserializer', `os` string COMMENT 'from deserializer', `devicetype` string COMMENT 'from deserializer', `clicktime` timestamp COMMENT 'from deserializer', `appid` string COMMENT 'from deserializer', `appversion` string COMMENT 'from deserializer', `type` string COMMENT 'from deserializer', `channel` string COMMENT 'from deserializer', `source` string COMMENT 'from deserializer', `campaign` string COMMENT 'from deserializer', `adtime` timestamp COMMENT 'from deserializer', `advertiseid` string COMMENT 'from deserializer', `keywordid` string COMMENT 'from deserializer', `gapday` bigint COMMENT 'from deserializer') ROW FORMAT SERDE 'com.mongodb.hadoop.hive.BSONSerDe' STORED BY 'com.mongodb.hadoop.hive.MongoStorageHandler' WITH SERDEPROPERTIES ( 'mongo.columns.mapping'='{\n\"id\":\"_id\",\n\"class\":\"_class\",\n\"applyId\":\"applyId\",\n\"loanId\":\"loanId\",\n\"orderNo\":\"orderNo\",\n\"productId\":\"productId\",\n\"error\":\"error\",\n\"newFlag\":\"newFlag\",\n\"device\":\"device\",\n\"deviceId\":\"deviceId\",\n\"ip\":\"ip\",\n\"ipInt\":\"ipInt\",\n\"os\":\"os\",\n\"deviceType\":\"deviceType\",\n\"clickTime\":\"clickTime\",\n\"appId\":\"appId\",\n\"appVersion\":\"appVersion\",\n\"type\":\"type\",\n\"channel\":\"channel\",\n\"source\":\"source\",\n\"campaign\":\"campaign\",\n\"adTime\":\"adTime\",\n\"advertiseId\":\"advertiseId\",\n\"keywordId\":\"keywordId\",\n\"gapDay\":\"gapDay\"}', 'serialization.format'='1') LOCATION 'hdfs://nameservice1/user/hive/warehouse/mongo.db/loan' TBLPROPERTIES ( 'mongo.uri'='mongodb://120.77.46.159:27017/dev.loan', 'transient_lastDdlTime'='1519377292') CREATE EXTERNAL TABLE `rcresultmgmodel`( `id` string COMMENT 'from deserializer', `class` string COMMENT 'from deserializer', `result` string COMMENT 'from deserializer', `createtime` timestamp COMMENT 'from deserializer', `customerid` bigint COMMENT 'from deserializer', `loanid` bigint COMMENT 'from deserializer', `pipelinegroupid` string COMMENT 'from deserializer') ROW FORMAT SERDE 'com.mongodb.hadoop.hive.BSONSerDe' STORED BY 'com.mongodb.hadoop.hive.MongoStorageHandler' WITH SERDEPROPERTIES ( 'mongo.columns.mapping'='{\n\"id\":\"_id\",\"class\":\"_class\",\"result\":\"result\",\"createTime\":\"createTime\",\"customerId\":\"customerId\",\"loanId\":\"loanId\",\"pipeLineGroupId\":\"pipeLineGroupId\"}', 'serialization.format'='1') LOCATION 'hdfs://nameservice1/user/hive/warehouse/mongo.db/rcresultmgmodel' TBLPROPERTIES ( 'COLUMN_STATS_ACCURATE'='false', 'mongo.uri'='mongodb://fenqi:mintq_fenqi@s-2zefd3ee73da15c4-pub.mongodb.rds.aliyuncs.com:3717/fenqi_test.rcResultMgModel', 'numFiles'='0', 'numRows'='-1', 'rawDataSize'='-1', 'totalSize'='0', 'transient_lastDdlTime'='1523527318') CREATE EXTERNAL TABLE `withdraw`( `id` string COMMENT 'from deserializer', `class` string COMMENT 'from deserializer', `loanid` bigint COMMENT 'from deserializer', `orderno` string COMMENT 'from deserializer', `approvedamount` double COMMENT 'from deserializer', `effectivedate` date COMMENT 'from deserializer', `loanstatus` string COMMENT 'from deserializer', `customerid` bigint COMMENT 'from deserializer', `withdrawperiod` int COMMENT 'from deserializer', `error` boolean COMMENT 'from deserializer', `appid` string COMMENT 'from deserializer', `capitalistid` int COMMENT 'from deserializer', `newflag` boolean COMMENT 'from deserializer', `activeflag` boolean COMMENT 'from deserializer', `device` string COMMENT 'from deserializer', `deviceid` string COMMENT 'from deserializer', `ip` string COMMENT 'from deserializer', `ipint` int COMMENT 'from deserializer', `os` string COMMENT 'from deserializer', `devicetype` string COMMENT 'from deserializer', `clicktime` timestamp COMMENT 'from deserializer', `appversion` string COMMENT 'from deserializer', `type` string COMMENT 'from deserializer', `channel` string COMMENT 'from deserializer', `source` string COMMENT 'from deserializer', `campaign` string COMMENT 'from deserializer', `adtime` timestamp COMMENT 'from deserializer', `advertiseid` string COMMENT 'from deserializer', `keywordid` string COMMENT 'from deserializer', `gapday` bigint COMMENT 'from deserializer') ROW FORMAT SERDE 'com.mongodb.hadoop.hive.BSONSerDe' STORED BY 'com.mongodb.hadoop.hive.MongoStorageHandler' WITH SERDEPROPERTIES ( 'mongo.columns.mapping'='{\n\"id\":\"_id\",\n\"class\":\"_class\",\n\"loanId\":\"loanId\",\n\"orderNo\":\"orderNo\",\n\"approvedAmount\":\"approvedAmount\",\n\"effectiveDate\":\"effectiveDate\",\n\"loanStatus\":\"loanStatus\",\n\"customerId\":\"customerId\",\n\"withdrawPeriod\":\"withdrawPeriod\",\n\"error\":\"error\",\n\"appId\":\"appId\",\n\"capitalistId\":\"capitalistId\",\n\"newFlag\":\"newFlag\",\n\"activeFlag\":\"activeFlag\",\n\"device\":\"device\",\n\"deviceId\":\"deviceId\",\n\"ip\":\"ip\",\n\"ipInt\":\"ipInt\",\n\"os\":\"os\",\n\"deviceType\":\"deviceType\",\n\"clickTime\":\"clickTime\",\n\"appVersion\":\"appVersion\",\n\"type\":\"type\",\n\"channel\":\"channel\",\n\"source\":\"source\",\n\"campaign\":\"campaign\",\n\"adTime\":\"adTime\",\n\"advertiseId\":\"advertiseId\",\n\"keywordId\":\"keywordId\",\n\"gapDay\":\"gapDay\"}', 'serialization.format'='1') LOCATION 'hdfs://nameservice1/user/hive/warehouse/mongo.db/withdraw' TBLPROPERTIES ( 'mongo.uri'='mongodb://120.77.46.159:27017/dev.withdraw', 'transient_lastDdlTime'='1519377795') 2.2 创建仓库表，或直接向仓库表导入外部mongodb数据 create table data_analysis.im_emaymongomodel as select * from monog.installmentTongdun; insert overwrite table data_analysis.im_emaymongomodel select * from mongo.im_emaymongomodel where xx_date='xx'; insert overwrite table fdl.fdl_api_accs_flow partition(dt) select id api_id, access_time accs_tm, customer_id cust_id, device, failure_reason fail_rsn, imei, ip_address ip, request_url url, success_flag succ_flag, '"""+v_etl_time+"""' etl_dt, DATE_FORMAT(access_time,'yyyyMMdd') dt from bdl.bdl_loan_t_api_access_flow