kettle导数到user_用于left join_20160928
这篇博客主要是给mysql left join做铺垫,需要现在本地数据库创建一个users 数据表 然后去和 test_a03order表 left join
一、首先在local_db数据库先创建username表
1 USE local_db; 2 CREATE TABLE users 3 (username VARCHAR(255), 4 address VARCHAR(255), 5 telephone VARCHAR(255), 6 KEY username(username) 7 ) ENGINE INNODB DEFAULT CHARSET=utf8
二、写导数SQL
从服务器导数作为测试 需要先对电话 地址等信息进行修改 这里用concat() left() right() 函数进行数据打乱 电话号码为10位 地址为5位
SELECT username,CONCAT(RIGHT(address,1),"测试",LEFT(address,1)) AS address, CASE WHEN telephone IS NOT NULL THEN CONCAT(LEFT(telephone,3),58568, RIGHT(telephone,2)) ELSE NULL END AS telephone FROM Tb_username WHERE username IN (SELECT username FROM `test_a03order` GROUP BY username)
三、启动kettle 设置数据库连接
创建从服务器数据库导本地数据库的连接 建立转换 执行
执行完毕 数据表数据记录
四、其他终端向users表导入数据记录
INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('311','店测试圆','1315856818'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('463','车测试上','1865856834'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('1317','米测试天','1865856836'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('1610','楼测试望','1865856827'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('1932','街测试旧','1895856892'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('2223','口测试魏','1525856837'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('2450',',测试东','1855856896'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('2999','米测试西','1315856898'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('3631','边测试中','1355856862'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('3783','头测试天','1775856890'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('3897','虾测试朝','1835856853'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('3908','际测试天','1705856819'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('4485','部测试上','1355856827'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('5011','路测试龙','1315856818'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('5043','街测试马','1895856857'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('5184','面测试新','1365856807'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('5599','门测试回','1315856830'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('5799','边测试东','1525856806'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('5862','商测试回','1335856889'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('5983','南测试和','1585856875'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('6146','米测试苏','1865856870'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('6214','面测试清','1315856851'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('6568','场测试五','1325856898'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('6992','城测试丽','1375856861'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('8379','号测试西','1865856841'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('8534','号测试海','1355856871'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('9014','路测试四','1825856898'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('9582','口测试大','1895856824'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('9778','米测试公','1525856813'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('9821','里测试吕','1865856831'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('10335','同测试西','1395856829'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('10855','号测试闹','1355856843'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('10969','城测试索','1835856844'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('11103','米测试衙','1315856834'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('11788','城测试大','1855856868'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('12566','号测试前','1305856867'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('13009','米测试京','1825856881'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('13420','米测试回','1895856801'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('13440','侧测试海','105856852'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('13518','边测试安','1855856862'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('14466','号测试东','1305856833'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('14705','门测试回','1355856836'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('15205','下测试丰','1345856874'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('15492','号测试东','1305856821'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('15640','口测试紫','1875856823'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('16156','》测试天','1895856820'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('16271','面测试十','1525856896'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('16275','米测试宋','1885856880'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('16431','口测试西','1515856861'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('16555','城测试大','1365856858'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('16906','店测试青','1385856812'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('17141','楼测试光','1345856887'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('17437','西测试旧','1585856898'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('17951','面测试丰','1395856825'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('18246','米测试吕','1355856840'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('18619','面测试酒','1565856895'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('18763','院测试洋','1585856866'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('19456','边测试酒','1525856871'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('19476','边测试大','1365856837'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('19477','面测试酒','1865856894'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('19484','米测试定','1835856886'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('19859','寓测试丰','1365856883'); INSERT INTO `users` (`username`, `address`, `telephone`) VALUES('19915','米测试看','1585856838');