mongodb bi-connector 使用
<p>mongodb bi-connector 是一个方便的mongodb 2 sql 工具，我们可以使用sql 的强大查询能力，分析mongo的数据，以下是一些 <br>简单操作，以及一些部署上的细节说明，后边会介绍spring boot 集成的说明</p>
<h2 id="安装">安装</h2>
<blockquote>
<p>为了方便测试使用本地docker 运行mongo+ 使用本地mongodb bi-connector </p>
</blockquote>
<p>参考以下地址安装 https://www.mongodb.com/download-center/bi-connector</p>
<h2 id="mongo环境准备">mongo 环境准备</h2>
<p>添加了一个pgspider:mongo pg 的fdw</p>
<ul>
<li>docker-compose 文件</li>
</ul>
<div id="wiz_cm_1581763734350_5663" class="wiz-code-container" data-mode="JavaScript" data-theme="default">
<div class="CodeMirror cm-s-default CodeMirror-wrap" data-id="wiz_cm_1581763734350_5663">
<div class="CodeMirror-scroll">
<div class="CodeMirror-sizer">
<div>
<div class="CodeMirror-lines">
<div>
<div class="CodeMirror-cursors"> </div>
<div class="CodeMirror-code">
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">version: <span class="cm-string">"3"</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">services:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">pg<span class="cm-operator">-<span class="cm-variable">mysql:</span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">image: <span class="cm-variable">dalongrong<span class="cm-operator">/<span class="cm-variable">pgspider:<span class="cm-variable">mongo</span></span></span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">ports:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-operator">- <span class="cm-string">"5432:5432"</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">environment:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-operator">- <span class="cm-string">"POSTGRES_PASSWORD=dalong"</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">volumes:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-operator">- <span class="cm-string">"./certs:/opt/certs"</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">mongo:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">image: <span class="cm-variable">mongo</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">ports:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-operator">- <span class="cm-string">"27017:27017"</span></span></span></pre>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<ul>
<li>初始化数据</li>
</ul>
<div id="wiz_cm_1581763734329_1814" class="wiz-code-container" data-mode="JavaScript" data-theme="default">
<div class="CodeMirror cm-s-default CodeMirror-wrap" data-id="wiz_cm_1581763734329_1814">
<div class="CodeMirror-scroll">
<div class="CodeMirror-sizer">
<div>
<div class="CodeMirror-lines">
<div>
<div class="CodeMirror-code">
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">docker<span class="cm-operator">-<span class="cm-variable">compose <span class="cm-variable">up <span class="cm-operator">-<span class="cm-variable">d</span></span></span></span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">创建数据库以及用户</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">use <span class="cm-variable">appdemos</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">db.<span class="cm-property">createCollection(<span class="cm-string">"appdemos")</span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">db.<span class="cm-property">createUser( </span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span>{ </span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-property">user: <span class="cm-string">"dalong", </span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-property">pwd: <span class="cm-string">"dalong", </span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-property">roles: [{<span class="cm-property">role: <span class="cm-string">"dbAdmin", <span class="cm-property">db: <span class="cm-string">"appdemos"}],</span></span></span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-property">mechanisms : [<span class="cm-string">"SCRAM-SHA-1"] </span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span>} </span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span>)</span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">导入部分这个，这个可以根据实际数据导入，可以使用命令</span></span></pre>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<h2 id="配置说明">配置说明</h2>
<p>下载好的软件包里边有三个文件（可能是两个）一个生成schema 的一个是sql 运行服务，一个是sql 到mongo <br>
聚合查询生成的，我们使用静态配置方式，同时因为添加了用户密码，所以需要tls 证书（使用了宽松模式）</p>
<ul>
<li>
<h2 id="配置">配置</h2>
</li>
<li>生成证书<br>
<blockquote>
<p>实际最好调整下信息</p>
</blockquote>
</li>
</ul>
<div id="wiz_cm_1581763734318_4869" class="wiz-code-container" data-mode="JavaScript" data-theme="default">
<div class="CodeMirror cm-s-default CodeMirror-wrap" data-id="wiz_cm_1581763734318_4869">
<div class="CodeMirror-scroll">
<div class="CodeMirror-sizer">
<div>
<div class="CodeMirror-lines">
<div>
<div class="CodeMirror-cursors"> </div>
<div class="CodeMirror-code">
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">openssl <span class="cm-variable">req <span class="cm-operator">-<span class="cm-variable">nodes <span class="cm-operator">-<span class="cm-variable">newkey <span class="cm-variable">rsa:<span class="cm-number">2048 <span class="cm-operator">-<span class="cm-variable">keyout <span class="cm-variable">test.<span class="cm-property">key <span class="cm-operator">-<span class="cm-variable">out <span class="cm-variable">test.<span class="cm-property">crt <span class="cm-operator">-<span class="cm-variable">x509 <span class="cm-operator">-<span class="cm-variable">days <span class="cm-number">365 <span class="cm-operator">-<span class="cm-variable">subj <span class="cm-string">"/C=US/ST=test/L=test/O=test Security/OU=IT Department/CN=test.com"</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">cat <span class="cm-variable">test.<span class="cm-property">crt <span class="cm-variable">test.<span class="cm-property">key <span class="cm-operator">> <span class="cm-variable">test.<span class="cm-property">pem</span></span></span></span></span></span></span></span></span></pre>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<ul>
<li>mongosqld配置 <br>
通过yaml 格式配置</li>
</ul>
<div id="wiz_cm_1581763734279_6885" class="wiz-code-container" data-mode="JavaScript" data-theme="default">
<div class="CodeMirror cm-s-default CodeMirror-wrap" data-id="wiz_cm_1581763734279_6885">
<div class="CodeMirror-scroll">
<div class="CodeMirror-sizer">
<div>
<div class="CodeMirror-lines">
<div>
<div class="CodeMirror-cursors"> </div>
<div class="CodeMirror-code">
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">net:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">bindIp: <span class="cm-string">"0.0.0.0"</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">port: <span class="cm-number">3307</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-error"># 宽松模式的ssl</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">ssl:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">mode: <span class="cm-variable">allowSSL</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">PEMKeyFile: <span class="cm-string">'./certs/test.pem'</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">allowInvalidCertificates: <span class="cm-atom">true</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">minimumTLSVersion: <span class="cm-variable">TLS1_0</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">mongodb:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">net:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">uri: <span class="cm-string">"mongodb://127.0.0.1:27017"</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">ssl:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">enabled: <span class="cm-atom">false</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">auth:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">username: <span class="cm-variable">dalong</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">password: <span class="cm-variable">dalong</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">source: <span class="cm-variable">appdemos</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">mechanism: <span class="cm-variable">SCRAM<span class="cm-operator">-<span class="cm-variable">SHA<span class="cm-operator">-<span class="cm-number">1</span></span></span></span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">security:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">enabled: <span class="cm-atom">true</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">defaultMechanism: <span class="cm-string">"SCRAM-SHA-1"</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">defaultSource: <span class="cm-string">"appdemos"</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">systemLog:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">path: .<span class="cm-operator">/<span class="cm-variable">mongosqld.<span class="cm-property">log</span></span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">verbosity: <span class="cm-number">2</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">logAppend: <span class="cm-atom">true</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">schema:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">path: .<span class="cm-operator">/<span class="cm-variable">schemas</span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">maxVarcharLength: <span class="cm-number">65535</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">processManagement:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">service:</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">name: <span class="cm-string">"mongosql"</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">displayName: <span class="cm-string">"MongoSQL Service"</span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span> <span class="cm-variable">description: <span class="cm-string">"MongoSQL accesses MongoDB data with SQL"</span></span></span></pre>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<ul>
<li>schema 生成</li>
</ul>
<div id="wiz_cm_1581763734268_1658" class="wiz-code-container" data-mode="JavaScript" data-theme="default">
<div class="CodeMirror cm-s-default CodeMirror-wrap" data-id="wiz_cm_1581763734268_1658">
<div class="CodeMirror-scroll">
<div class="CodeMirror-sizer">
<div>
<div class="CodeMirror-lines">
<div>
<div class="CodeMirror-code">
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">bin<span class="cm-operator">/<span class="cm-variable">mongodrdl <span class="cm-operator">--<span class="cm-variable">host <span class="cm-number">127.0.0.1:<span class="cm-number">27017 <span class="cm-operator">--<span class="cm-variable">username <span class="cm-variable">dalong <span class="cm-operator">--<span class="cm-variable">password <span class="cm-variable">dalong <span class="cm-operator">--<span class="cm-variable">db <span class="cm-variable">appdemos <span class="cm-operator">--<span class="cm-variable">authenticationDatabase <span class="cm-variable">appdemos <span class="cm-operator">--<span class="cm-variable">out <span class="cm-variable">schemas<span class="cm-operator">/<span class="cm-variable">schemas.<span class="cm-property">drdl</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<ul>
<li>启动服务</li>
</ul>
<div id="wiz_cm_1581763734260_8357" class="wiz-code-container" data-mode="JavaScript" data-theme="default">
<div class="CodeMirror cm-s-default CodeMirror-wrap" data-id="wiz_cm_1581763734260_8357">
<div class="CodeMirror-scroll">
<div class="CodeMirror-sizer">
<div>
<div class="CodeMirror-lines">
<div>
<div class="CodeMirror-code">
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">bin<span class="cm-operator">/<span class="cm-variable">mongosqld <span class="cm-operator">--<span class="cm-variable">config <span class="cm-variable">mongosqld<span class="cm-operator">-<span class="cm-variable">config.<span class="cm-property">yml</span></span></span></span></span></span></span></span></span></span></pre>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<ul>
<li>连接
<blockquote>
<p>注意部分工具可能使用了mysql jdbc 8 以及一些高版本的连接会有问题</p>
</blockquote>
</li>
</ul>
<p>对于连接因为使用了ssl，需要使用工具需要勾选ssl，同时对于mysql cli 需要添加<code>--enable-cleartext-plugin</code> ，同时需要注意如果代码连接也需要配置 <br><img src="https://img2018.cnblogs.com/i-beta/562987/202002/562987-20200215185039762-1999934879.png" alt=""></p>
<p> </p>
<p> </p>
<p><br><img src="https://img2018.cnblogs.com/i-beta/562987/202002/562987-20200215185049155-15952514.png" alt=""></p>
<p> </p>
<p> </p>
<p><br><img src="https://img2018.cnblogs.com/i-beta/562987/202002/562987-20200215185101300-478001281.png" alt=""></p>
<p> </p>
<p> </p>
<h2 id="几个问题">几个问题</h2>
<ul>
<li>ssl 问题 <br>
这个问题也是网上大家都反馈比较多的， 尽管net配置中可以ssl disable,但是没有效果（这个是使用密码关系,属于工具的一个强制策略） <br>
尽管有人说只能本地运行，这是不对的，碰到问题最好还是多看看官方文档，这样会少点折腾</li>
<li>代码连接问题 <br>
spring jdbc 配置,这个后边会有介绍使用的，关于nodejs 的集成就很不好了，好多对于ssl 的支持一般，后边也有介绍</li>
</ul>
<div id="wiz_cm_1581763734239_2740" class="wiz-code-container" data-mode="JavaScript" data-theme="default">
<div class="CodeMirror cm-s-default CodeMirror-wrap" data-id="wiz_cm_1581763734239_2740">
<div class="CodeMirror-scroll">
<div class="CodeMirror-sizer">
<div>
<div class="CodeMirror-lines">
<div>
<div class="CodeMirror-cursors"> </div>
<div class="CodeMirror-code">
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">spring.<span class="cm-property">datasource.<span class="cm-property">driver<span class="cm-operator">-<span class="cm-keyword">class<span class="cm-operator">-<span class="cm-variable">name<span class="cm-operator">=<span class="cm-variable">com.<span class="cm-property">mysql.<span class="cm-property">jdbc.<span class="cm-property">Driver</span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">spring.<span class="cm-property">datasource.<span class="cm-property">url<span class="cm-operator">=<span class="cm-variable">jdbc:<span class="cm-variable">mysql:<span class="cm-comment">//localhost:3307/appdemos?useSSL=true</span></span></span></span></span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">spring.<span class="cm-property">datasource.<span class="cm-property">username<span class="cm-operator">=<span class="cm-variable">dalong</span></span></span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">spring.<span class="cm-property">datasource.<span class="cm-property">password<span class="cm-operator">=<span class="cm-variable">dalong</span></span></span></span></span></span></pre>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<ul>
<li>关于mysql fdw 的集成问题 <br>
这个测试没用通过（mysql 协议支持的问题，当然修改fdw 代码，让只支持查询，应能是可行的，后边尝试下），如果真的需要 <br>
集成推荐使用mongo 的fdw（这个更靠谱稳定）</li>
<li>实际生成的mongo 查询 </li>
</ul>
<p> 这个我们可以通过mongotranslate 工具，以及sql 的查询计划任务看出来，以下是sql 查询分析的 </p>
<p><img src="https://img2018.cnblogs.com/i-beta/562987/202002/562987-20200215185558219-561772668.png" alt=""></p>
<p> </p>
<p> </p>
<p><img alt=""></p>
<ul>
<li>linux systemd 启动文件 <br>
mongosqld 命令行支持包含了使用系统服务管理的命令，可以快速帮助我们生成系统级别的服务管理脚本，如下是centos 的一个 <br>
目录:/etc/systemd/system/mongosql.service 方便大家学习</li>
</ul>
<div id="wiz_cm_1581763734205_1305" class="wiz-code-container" data-mode="JavaScript" data-theme="default">
<div class="CodeMirror cm-s-default CodeMirror-wrap" data-id="wiz_cm_1581763734205_1305">
<div class="CodeMirror-scroll">
<div class="CodeMirror-sizer">
<div>
<div class="CodeMirror-lines">
<div>
<div class="CodeMirror-cursors"> </div>
<div class="CodeMirror-code">
<div>
<pre class=" CodeMirror-line "><span>[<span class="cm-variable">Unit]</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">Description<span class="cm-operator">=<span class="cm-variable">MongoSQL <span class="cm-variable">accesses <span class="cm-variable">MongoDB <span class="cm-variable">data <span class="cm-keyword">with <span class="cm-variable">SQL</span></span></span></span></span></span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">ConditionFileIsExecutable<span class="cm-operator">=<span class="cm-string-2">/usr/<span class="cm-variable">bin<span class="cm-operator">/<span class="cm-variable">mongosqld</span></span></span></span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span>[<span class="cm-variable">Service]</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">StartLimitInterval<span class="cm-operator">=<span class="cm-number">5</span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">StartLimitBurst<span class="cm-operator">=<span class="cm-number">10</span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">ExecStart<span class="cm-operator">=<span class="cm-string-2">/usr/<span class="cm-variable">bin<span class="cm-operator">/<span class="cm-variable">mongosqld <span class="cm-string">"--config=/root/mong/mongosqld-config.yml"</span></span></span></span></span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">Restart<span class="cm-operator">=<span class="cm-variable">always</span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">RestartSec<span class="cm-operator">=<span class="cm-number">120</span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">EnvironmentFile<span class="cm-operator">=-<span class="cm-string-2">/etc/<span class="cm-variable">sysconfig<span class="cm-operator">/<span class="cm-variable">mongosql</span></span></span></span></span></span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span>[<span class="cm-variable">Install]</span></span></pre>
</div>
<div>
<pre class=" CodeMirror-line "><span><span class="cm-variable">WantedBy<span class="cm-operator">=<span class="cm-variable">multi<span class="cm-operator">-<span class="cm-variable">user.<span class="cm-property">target</span></span></span></span></span></span></span></pre>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<h2 id="参考资料">参考资料</h2>
<p>https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#bin.mongosqld <br>
https://www.mongodb.com/download-center/bi-connector <br>
https://docs.mongodb.com/bi-connector/master/release-notes/</p><br><br>
来源：https://www.cnblogs.com/rongfengliang/p/12313132.html
頁:
[1]