polardb数据迁移手册
<h2>简述：</h2>
<p> 为适配信创云服务新型数据库polardb，保证项目在各个数据库之间无缝切换正常运行。polardb暂不支持传统方式迁移如dmp、sql脚本并在数据类型和语法上有区别于其他数据库，要保证开发人员及实施人员需将原数据库的表结构和数据快速迁移研发技术团队在多个迁移工具尝试中最终选用Kettle作为迁移工具能够满足现有迁移工作。</p>
<h2>二、迁移工具Kettle使用</h2>
<p> Kettle解压版：<a href="https://sourceforge.net/projects/pentaho/files/Data%20Integration/">https://sourceforge.net/projects/pentaho/files/Data%20Integration/</a>
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/5b00bada6dd84c461bbf1aba4b8eea1b" alt="" />
在lib下添加各个数据库需要用到连接包：
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/d76345a5fd6175b81d30edd2ba7070a6" alt="" />
常用数据库：达梦、polardb、mysql、oracle、人大金仓
点击下载各对应ｊｉａ包：[Dm7JdbcDriver17-7-17.jar](<a href="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/2126d01c1bef0f3022131aa3f8f66d29">http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/2126d01c1bef0f3022131aa3f8f66d29</a> "[Dm7JdbcDriver17-7-17.jar")、[polardb-jdbc18.jar](<a href="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/b4572994d54dbaa8e40043a2454e877d">http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/b4572994d54dbaa8e40043a2454e877d</a> "[polardb-jdbc18.jar")、[mysql-5.1.48.jar](<a href="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/d59e0f56ebd0f02da594fcc564e6bbc8">http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/d59e0f56ebd0f02da594fcc564e6bbc8</a> "[mysql-5.1.48.jar")、[mysql-connector-java-8.0.22.jar](<a href="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/74c4953032196a44ee14fbe7bea33ee5">http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/74c4953032196a44ee14fbe7bea33ee5</a> "[mysql-connector-java-8.0.22.jar")、[oraclejdbc-1.0.jar](<a href="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/9bace437d38d3e11d3fd32062069400e">http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/9bace437d38d3e11d3fd32062069400e</a> "[oraclejdbc-1.0.jar")、[kingbase8-8.0.jar](<a href="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/57f27665e2fd31ebc2431d8674c22478">http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/57f27665e2fd31ebc2431d8674c22478</a> "[kingbase8-8.0.jar")
<code>注意：达梦、polardb、人大金仓采用jdbc方式去连接</code>
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/0e4dedd24afd819667308e454a5aa16d" alt="" /></p>
<h2>三、迁移工具Kettle使用</h2>
<ul>
<li>
<p>双击点击 Spoon.bat（若无法启动可尝试右键编辑，指定系统JDK）
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/e69032233f5d2036dd164d0775e7151b" alt="" /></p>
</li>
<li>
<p>点击文件—>新建作业
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/62fb91c30172afe2aa7affbd2f4e33d0" alt="" />
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/90f05ceb860c82b8038064a80e9436f0" alt="" /></p>
</li>
<li>新增源数据库连接和目的数据库链接（polardb）
原数据库
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/6e5f178fb3137b6c703f5195bbb3354c" alt="" />
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/b322003b7f2714e3a4f6fb0a48780b15" alt="" />
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/5d69be5ffe262922d1d2602025bcf961" alt="" />
目的数据库
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/ba75b294e76ec046e7d9d42c9e79a11b" alt="" />
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/90e3eb6fc162ad41799c4e6e0a40d8a8" alt="" />
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/b35b3d2c041b431b0c66602869232d54" alt="" /></li>
<li>构建迁移作业
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/dab74301c64d59c1f191e8a1b5a4ec28" alt="" />
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/2eeb9333d636d962bc4f29bebe01d5b1" alt="" />
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/aeefa795369b052a4335ae69c9503d5e" alt="" />
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/aa93b5d85c7a9f0cdc3aeb263edaeba5" alt="" />
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/9c9500022817189d937afdd44ef7bb4d" alt="" />
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/6f161e24dd3d2957e3cb98e024244746" alt="" />
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/372175f4931a466d6a6a62282d6cc2bb" alt="" />
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/c1763925d562065514992e5325a99153" alt="" /></li>
</ul>
<h3>注意事项</h3>
<p><code>Kettle是一次性迁移完成工具，中途一旦有问题必须回滚，包括已经完成迁移的表必须在polardb上删除掉重新迁移！</code></p>
<h2>四、迁移过程中遇到问题和解决方案</h2>
<ul>
<li>
<p>问题一：原数据库大字段属性blob转换错误
解决方案：将UNKNOWN手工修正改为blob
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/ce02805d691a7514998c2d15ab394c41" alt="" /></p>
</li>
<li>
<p>问题二：原数据库表字段转换丢失
解决方案：将缺失的字段补上去</p>
</li>
<li>
<p>问题三：在构架转换的时候有可能出现无字段长度问题
解决方案：手动部全
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/ecb6188d7097fe05238c94abcb2a8288" alt="" /></p>
</li>
<li>问题四：初始化表结构因字段长度过小不能执行
解决方案：手动调整
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/90739c18a2cb7b4dc3758c160fffcbec" alt="" /></li>
</ul>
<h2>五、数据库主键、索引、表字段注释</h2>
<ul>
<li>
<p>2.0平台：
[2.0平台标准平台2.5.0版本以下字段注释语句.sql](<a href="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/8ad45ab5fa8e6330e525330492611489">http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/8ad45ab5fa8e6330e525330492611489</a> "[2.0平台标准平台2.5.0版本以下字段注释语句.sql")、[2.0平台标准平台2.5.0版本以下polardb主键索引语句.sql](<a href="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/f6fe46667ad64f1d5c904a64299512cb">http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/f6fe46667ad64f1d5c904a64299512cb</a> "[2.0平台标准平台2.5.0版本以下polardb主键索引语句.sql")</p>
</li>
<li>1.0平台：
[1.0平台标准平台polardb主键索引语句.sql](<a href="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/6c8ea1bf4fa1b2890629e1ba74cdcc59">http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/6c8ea1bf4fa1b2890629e1ba74cdcc59</a> "[1.0平台标准平台polardb主键索引语句.sql")
<h2>六、平台升级</h2></li>
<li>2.0平台2.5.x以下版本升级：</li>
</ul>
<pre><code class="language-shell">数据库驱动
&lt;dependency&gt;
&lt;groupId&gt;com.risen.dbdriver&lt;/groupId&gt;
&lt;artifactId&gt;polardb&lt;/artifactId&gt;
&lt;version&gt;jdbc18&lt;/version&gt;
&lt;/dependency&gt;
平台升级：risen- frame
&lt;dependency&gt;
&lt;groupId&gt;com.risen.pt&lt;/groupId&gt;
&lt;artifactId&gt;risen-frame&lt;/artifactId&gt;
&lt;version&gt;2.4.2.2_beta-SNAPSHOT&lt;/version&gt;
&lt;/ dependency&gt;
数据库连接：
dataSource.driverClassName=com.aliyun.polardb.Driver
dataSource.url=jdbc:polardb://ip:5432/数据库
dataSource.username=用户名
dataSource.password=密码</code></pre>
<ul>
<li>1.0平台升级：</li>
</ul>
<h2>七、技术层注意事项</h2>
<ol>
<li>Polardb只是高度适配Oracle,在表结构和语句上有一些区别如count(*) 不能与order by 结合使用</li>
<li>分页使用limits必须配合offset 使用</li>
<li>表结构和表字段必须为小写</li>
<li>自定义语句禁止将数据返回至strMap中</li>
</ol>
<h2>八、开发参考手册</h2>
<p>[阿里云 PolarDB-O 开发指南 20201209.pdf](<a href="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/01b7978f8957d2d77df829165d01a5b7">http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/01b7978f8957d2d77df829165d01a5b7</a> "[阿里云 PolarDB-O 开发指南 20201209.pdf")</p>