| <!DOCTYPE html> |
| |
| <html lang="en" data-content_root="./"> |
| <head> |
| <meta charset="utf-8" /> |
| <meta name="viewport" content="width=device-width, initial-scale=1.0" /><meta name="viewport" content="width=device-width, initial-scale=1" /> |
| |
| <title>Arrow JDBC Adapter — Apache Arrow Java Cookbook documentation</title> |
| <link rel="stylesheet" type="text/css" href="_static/pygments.css?v=d1102ebc" /> |
| <link rel="stylesheet" type="text/css" href="_static/alabaster.css?v=49eeb2a1" /> |
| <script src="_static/documentation_options.js?v=5929fcd5"></script> |
| <script src="_static/doctools.js?v=888ff710"></script> |
| <script src="_static/sphinx_highlight.js?v=dc90522c"></script> |
| <link rel="icon" href="_static/favicon.ico"/> |
| <link rel="index" title="Index" href="genindex.html" /> |
| <link rel="search" title="Search" href="search.html" /> |
| <link rel="prev" title="Avro" href="avro.html" /> |
| |
| |
| <link rel="stylesheet" href="_static/custom.css" type="text/css" /> |
| |
| |
| |
| |
| <!-- Matomo --> |
| <script> |
| var _paq = window._paq = window._paq || []; |
| /* tracker methods like "setCustomDimension" should be called before "trackPageView" */ |
| /* We explicitly disable cookie tracking to avoid privacy issues */ |
| _paq.push(['disableCookies']); |
| _paq.push(['trackPageView']); |
| _paq.push(['enableLinkTracking']); |
| (function() { |
| var u="https://analytics.apache.org/"; |
| _paq.push(['setTrackerUrl', u+'matomo.php']); |
| _paq.push(['setSiteId', '20']); |
| var d=document, g=d.createElement('script'), s=d.getElementsByTagName('script')[0]; |
| g.async=true; g.src=u+'matomo.js'; s.parentNode.insertBefore(g,s); |
| })(); |
| </script> |
| <!-- End Matomo Code --> |
| |
| </head><body> |
| |
| |
| <div class="document"> |
| <div class="documentwrapper"> |
| <div class="bodywrapper"> |
| |
| |
| <div class="body" role="main"> |
| |
| <section id="arrow-jdbc-adapter"> |
| <span id="arrow-jdbc"></span><h1><a class="toc-backref" href="#id1" role="doc-backlink">Arrow JDBC Adapter</a><a class="headerlink" href="#arrow-jdbc-adapter" title="Link to this heading">¶</a></h1> |
| <p>The <a class="reference external" href="https://arrow.apache.org/docs/java/jdbc.html">Arrow Java JDBC module</a> |
| converts JDBC ResultSets into Arrow VectorSchemaRoots.</p> |
| <nav class="contents" id="contents"> |
| <p class="topic-title">Contents</p> |
| <ul class="simple"> |
| <li><p><a class="reference internal" href="#arrow-jdbc-adapter" id="id1">Arrow JDBC Adapter</a></p> |
| <ul> |
| <li><p><a class="reference internal" href="#resultset-to-vectorschemaroot-conversion" id="id2">ResultSet to VectorSchemaRoot Conversion</a></p></li> |
| <li><p><a class="reference internal" href="#configuring-array-subtypes" id="id3">Configuring Array subtypes</a></p></li> |
| <li><p><a class="reference internal" href="#configuring-batch-size" id="id4">Configuring batch size</a></p></li> |
| <li><p><a class="reference internal" href="#configuring-numeric-decimal-precision-and-scale" id="id5">Configuring numeric (decimal) precision and scale</a></p></li> |
| </ul> |
| </li> |
| </ul> |
| </nav> |
| <section id="resultset-to-vectorschemaroot-conversion"> |
| <h2><a class="toc-backref" href="#id2" role="doc-backlink">ResultSet to VectorSchemaRoot Conversion</a><a class="headerlink" href="#resultset-to-vectorschemaroot-conversion" title="Link to this heading">¶</a></h2> |
| <p>The main class to help us to convert ResultSet to VectorSchemaRoot is |
| <a class="reference external" href="https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrow.html">JdbcToArrow</a></p> |
| <div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.ArrowVectorIterator</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcToArrow</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.memory.BufferAllocator</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.memory.RootAllocator</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.vector.VectorSchemaRoot</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.ibatis.jdbc.ScriptRunner</span><span class="p">;</span> |
| |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.io.BufferedReader</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.io.FileReader</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.io.IOException</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.Connection</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.DriverManager</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.ResultSet</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.SQLException</span><span class="p">;</span> |
| |
| <span class="k">try</span><span class="w"> </span><span class="p">(</span><span class="n">BufferAllocator</span><span class="w"> </span><span class="n">allocator</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">RootAllocator</span><span class="p">();</span> |
| <span class="w"> </span><span class="n">Connection</span><span class="w"> </span><span class="n">connection</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">DriverManager</span><span class="p">.</span><span class="na">getConnection</span><span class="p">(</span> |
| <span class="w"> </span><span class="s">"jdbc:h2:mem:h2-jdbc-adapter"</span><span class="p">))</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="n">ScriptRunner</span><span class="w"> </span><span class="n">runnerDDLDML</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">ScriptRunner</span><span class="p">(</span><span class="n">connection</span><span class="p">);</span> |
| <span class="w"> </span><span class="n">runnerDDLDML</span><span class="p">.</span><span class="na">setLogWriter</span><span class="p">(</span><span class="kc">null</span><span class="p">);</span> |
| <span class="w"> </span><span class="n">runnerDDLDML</span><span class="p">.</span><span class="na">runScript</span><span class="p">(</span><span class="k">new</span><span class="w"> </span><span class="n">BufferedReader</span><span class="p">(</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">FileReader</span><span class="p">(</span><span class="s">"./thirdpartydeps/jdbc/h2-ddl.sql"</span><span class="p">)));</span> |
| <span class="w"> </span><span class="n">runnerDDLDML</span><span class="p">.</span><span class="na">runScript</span><span class="p">(</span><span class="k">new</span><span class="w"> </span><span class="n">BufferedReader</span><span class="p">(</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">FileReader</span><span class="p">(</span><span class="s">"./thirdpartydeps/jdbc/h2-dml.sql"</span><span class="p">)));</span> |
| <span class="w"> </span><span class="k">try</span><span class="w"> </span><span class="p">(</span><span class="n">ResultSet</span><span class="w"> </span><span class="n">resultSet</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">connection</span><span class="p">.</span><span class="na">createStatement</span><span class="p">().</span><span class="na">executeQuery</span><span class="p">(</span> |
| <span class="w"> </span><span class="s">"SELECT int_field1, bool_field2, bigint_field5 FROM TABLE1"</span><span class="p">);</span> |
| <span class="w"> </span><span class="n">ArrowVectorIterator</span><span class="w"> </span><span class="n">iterator</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">JdbcToArrow</span><span class="p">.</span><span class="na">sqlToArrowVectorIterator</span><span class="p">(</span> |
| <span class="w"> </span><span class="n">resultSet</span><span class="p">,</span><span class="w"> </span><span class="n">allocator</span><span class="p">))</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="k">while</span><span class="w"> </span><span class="p">(</span><span class="n">iterator</span><span class="p">.</span><span class="na">hasNext</span><span class="p">())</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="k">try</span><span class="w"> </span><span class="p">(</span><span class="n">VectorSchemaRoot</span><span class="w"> </span><span class="n">root</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">iterator</span><span class="p">.</span><span class="na">next</span><span class="p">())</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="n">System</span><span class="p">.</span><span class="na">out</span><span class="p">.</span><span class="na">print</span><span class="p">(</span><span class="n">root</span><span class="p">.</span><span class="na">contentToTSVString</span><span class="p">());</span> |
| <span class="w"> </span><span class="p">}</span> |
| <span class="w"> </span><span class="p">}</span> |
| <span class="w"> </span><span class="p">}</span> |
| <span class="p">}</span><span class="w"> </span><span class="k">catch</span><span class="w"> </span><span class="p">(</span><span class="n">SQLException</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">IOException</span><span class="w"> </span><span class="n">e</span><span class="p">)</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="n">e</span><span class="p">.</span><span class="na">printStackTrace</span><span class="p">();</span> |
| <span class="p">}</span> |
| </pre></div> |
| </div> |
| <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 |
| 101 true 1000000000300 |
| 102 true 100000000030 |
| 103 true 10000000003 |
| </pre></div> |
| </div> |
| </section> |
| <section id="configuring-array-subtypes"> |
| <h2><a class="toc-backref" href="#id3" role="doc-backlink">Configuring Array subtypes</a><a class="headerlink" href="#configuring-array-subtypes" title="Link to this heading">¶</a></h2> |
| <p>JdbcToArrow accepts configuration through <a class="reference external" href="https://arrow.apache.org/docs/java/reference/org/apache/arrow/adapter/jdbc/JdbcToArrowConfig.html">JdbcToArrowConfig</a>. |
| For example, the type of the elements of array columns can be specified by |
| <code class="docutils literal notranslate"><span class="pre">setArraySubTypeByColumnNameMap</span></code>.</p> |
| <div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.ArrowVectorIterator</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcFieldInfo</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcToArrow</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcToArrowConfig</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcToArrowUtils</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.memory.BufferAllocator</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.memory.RootAllocator</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.vector.VectorSchemaRoot</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.ibatis.jdbc.ScriptRunner</span><span class="p">;</span> |
| |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.io.BufferedReader</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.io.FileReader</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.io.IOException</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.Connection</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.DriverManager</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.ResultSet</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.SQLException</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.Types</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.util.HashMap</span><span class="p">;</span> |
| |
| <span class="k">try</span><span class="w"> </span><span class="p">(</span><span class="n">BufferAllocator</span><span class="w"> </span><span class="n">allocator</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">RootAllocator</span><span class="p">();</span> |
| <span class="w"> </span><span class="n">Connection</span><span class="w"> </span><span class="n">connection</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">DriverManager</span><span class="p">.</span><span class="na">getConnection</span><span class="p">(</span> |
| <span class="w"> </span><span class="s">"jdbc:h2:mem:h2-jdbc-adapter"</span><span class="p">))</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="n">ScriptRunner</span><span class="w"> </span><span class="n">runnerDDLDML</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">ScriptRunner</span><span class="p">(</span><span class="n">connection</span><span class="p">);</span> |
| <span class="w"> </span><span class="n">runnerDDLDML</span><span class="p">.</span><span class="na">setLogWriter</span><span class="p">(</span><span class="kc">null</span><span class="p">);</span> |
| <span class="w"> </span><span class="n">runnerDDLDML</span><span class="p">.</span><span class="na">runScript</span><span class="p">(</span><span class="k">new</span><span class="w"> </span><span class="n">BufferedReader</span><span class="p">(</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">FileReader</span><span class="p">(</span><span class="s">"./thirdpartydeps/jdbc/h2-ddl.sql"</span><span class="p">)));</span> |
| <span class="w"> </span><span class="n">runnerDDLDML</span><span class="p">.</span><span class="na">runScript</span><span class="p">(</span><span class="k">new</span><span class="w"> </span><span class="n">BufferedReader</span><span class="p">(</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">FileReader</span><span class="p">(</span><span class="s">"./thirdpartydeps/jdbc/h2-dml.sql"</span><span class="p">)));</span> |
| <span class="w"> </span><span class="n">JdbcToArrowConfig</span><span class="w"> </span><span class="n">config</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">JdbcToArrowConfigBuilder</span><span class="p">(</span><span class="n">allocator</span><span class="p">,</span> |
| <span class="w"> </span><span class="n">JdbcToArrowUtils</span><span class="p">.</span><span class="na">getUtcCalendar</span><span class="p">())</span> |
| <span class="w"> </span><span class="p">.</span><span class="na">setArraySubTypeByColumnNameMap</span><span class="p">(</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">HashMap</span><span class="o"><></span><span class="p">()</span><span class="w"> </span><span class="p">{{</span> |
| <span class="w"> </span><span class="n">put</span><span class="p">(</span><span class="s">"LIST_FIELD19"</span><span class="p">,</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">JdbcFieldInfo</span><span class="p">(</span><span class="n">Types</span><span class="p">.</span><span class="na">INTEGER</span><span class="p">));</span> |
| <span class="w"> </span><span class="p">}}</span> |
| <span class="w"> </span><span class="p">)</span> |
| <span class="w"> </span><span class="p">.</span><span class="na">build</span><span class="p">();</span> |
| <span class="w"> </span><span class="k">try</span><span class="w"> </span><span class="p">(</span><span class="n">ResultSet</span><span class="w"> </span><span class="n">resultSet</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">connection</span><span class="p">.</span><span class="na">createStatement</span><span class="p">().</span><span class="na">executeQuery</span><span class="p">(</span> |
| <span class="w"> </span><span class="s">"SELECT int_field1, bool_field2, bigint_field5, char_field16, list_field19 FROM TABLE1"</span><span class="p">);</span> |
| <span class="w"> </span><span class="n">ArrowVectorIterator</span><span class="w"> </span><span class="n">iterator</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">JdbcToArrow</span><span class="p">.</span><span class="na">sqlToArrowVectorIterator</span><span class="p">(</span> |
| <span class="w"> </span><span class="n">resultSet</span><span class="p">,</span><span class="w"> </span><span class="n">config</span><span class="p">))</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="k">while</span><span class="w"> </span><span class="p">(</span><span class="n">iterator</span><span class="p">.</span><span class="na">hasNext</span><span class="p">())</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="k">try</span><span class="w"> </span><span class="p">(</span><span class="n">VectorSchemaRoot</span><span class="w"> </span><span class="n">root</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">iterator</span><span class="p">.</span><span class="na">next</span><span class="p">())</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="n">System</span><span class="p">.</span><span class="na">out</span><span class="p">.</span><span class="na">print</span><span class="p">(</span><span class="n">root</span><span class="p">.</span><span class="na">contentToTSVString</span><span class="p">());</span> |
| <span class="w"> </span><span class="p">}</span> |
| <span class="w"> </span><span class="p">}</span> |
| <span class="w"> </span><span class="p">}</span> |
| <span class="p">}</span><span class="w"> </span><span class="k">catch</span><span class="w"> </span><span class="p">(</span><span class="n">SQLException</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">IOException</span><span class="w"> </span><span class="n">e</span><span class="p">)</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="n">e</span><span class="p">.</span><span class="na">printStackTrace</span><span class="p">();</span> |
| <span class="p">}</span> |
| </pre></div> |
| </div> |
| <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19 |
| 101 true 1000000000300 some char text [1,2,3] |
| 102 true 100000000030 some char text [1,2] |
| 103 true 10000000003 some char text [1] |
| </pre></div> |
| </div> |
| </section> |
| <section id="configuring-batch-size"> |
| <h2><a class="toc-backref" href="#id4" role="doc-backlink">Configuring batch size</a><a class="headerlink" href="#configuring-batch-size" title="Link to this heading">¶</a></h2> |
| <p>By default, the adapter will read up to 1024 rows in a batch. This |
| can be customized via <code class="docutils literal notranslate"><span class="pre">setTargetBatchSize</span></code>.</p> |
| <div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.ArrowVectorIterator</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcFieldInfo</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcToArrow</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcToArrowConfig</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcToArrowUtils</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.memory.BufferAllocator</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.memory.RootAllocator</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.vector.VectorSchemaRoot</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.ibatis.jdbc.ScriptRunner</span><span class="p">;</span> |
| |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.io.BufferedReader</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.io.FileReader</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.io.IOException</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.Connection</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.DriverManager</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.ResultSet</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.SQLException</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.Types</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.util.HashMap</span><span class="p">;</span> |
| |
| <span class="k">try</span><span class="w"> </span><span class="p">(</span><span class="n">BufferAllocator</span><span class="w"> </span><span class="n">allocator</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">RootAllocator</span><span class="p">();</span> |
| <span class="w"> </span><span class="n">Connection</span><span class="w"> </span><span class="n">connection</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">DriverManager</span><span class="p">.</span><span class="na">getConnection</span><span class="p">(</span> |
| <span class="w"> </span><span class="s">"jdbc:h2:mem:h2-jdbc-adapter"</span><span class="p">))</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="n">ScriptRunner</span><span class="w"> </span><span class="n">runnerDDLDML</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">ScriptRunner</span><span class="p">(</span><span class="n">connection</span><span class="p">);</span> |
| <span class="w"> </span><span class="n">runnerDDLDML</span><span class="p">.</span><span class="na">setLogWriter</span><span class="p">(</span><span class="kc">null</span><span class="p">);</span> |
| <span class="w"> </span><span class="n">runnerDDLDML</span><span class="p">.</span><span class="na">runScript</span><span class="p">(</span><span class="k">new</span><span class="w"> </span><span class="n">BufferedReader</span><span class="p">(</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">FileReader</span><span class="p">(</span><span class="s">"./thirdpartydeps/jdbc/h2-ddl.sql"</span><span class="p">)));</span> |
| <span class="w"> </span><span class="n">runnerDDLDML</span><span class="p">.</span><span class="na">runScript</span><span class="p">(</span><span class="k">new</span><span class="w"> </span><span class="n">BufferedReader</span><span class="p">(</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">FileReader</span><span class="p">(</span><span class="s">"./thirdpartydeps/jdbc/h2-dml.sql"</span><span class="p">)));</span> |
| <span class="w"> </span><span class="n">JdbcToArrowConfig</span><span class="w"> </span><span class="n">config</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">JdbcToArrowConfigBuilder</span><span class="p">(</span><span class="n">allocator</span><span class="p">,</span> |
| <span class="w"> </span><span class="n">JdbcToArrowUtils</span><span class="p">.</span><span class="na">getUtcCalendar</span><span class="p">())</span> |
| <span class="w"> </span><span class="p">.</span><span class="na">setTargetBatchSize</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span> |
| <span class="w"> </span><span class="p">.</span><span class="na">setArraySubTypeByColumnNameMap</span><span class="p">(</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">HashMap</span><span class="o"><></span><span class="p">()</span><span class="w"> </span><span class="p">{{</span> |
| <span class="w"> </span><span class="n">put</span><span class="p">(</span><span class="s">"LIST_FIELD19"</span><span class="p">,</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">JdbcFieldInfo</span><span class="p">(</span><span class="n">Types</span><span class="p">.</span><span class="na">INTEGER</span><span class="p">));</span> |
| <span class="w"> </span><span class="p">}}</span> |
| <span class="w"> </span><span class="p">)</span> |
| <span class="w"> </span><span class="p">.</span><span class="na">build</span><span class="p">();</span> |
| <span class="w"> </span><span class="k">try</span><span class="w"> </span><span class="p">(</span><span class="n">ResultSet</span><span class="w"> </span><span class="n">resultSet</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">connection</span><span class="p">.</span><span class="na">createStatement</span><span class="p">().</span><span class="na">executeQuery</span><span class="p">(</span> |
| <span class="w"> </span><span class="s">"SELECT int_field1, bool_field2, bigint_field5, char_field16, list_field19 FROM TABLE1"</span><span class="p">);</span> |
| <span class="w"> </span><span class="n">ArrowVectorIterator</span><span class="w"> </span><span class="n">iterator</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">JdbcToArrow</span><span class="p">.</span><span class="na">sqlToArrowVectorIterator</span><span class="p">(</span> |
| <span class="w"> </span><span class="n">resultSet</span><span class="p">,</span><span class="w"> </span><span class="n">config</span><span class="p">))</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="k">while</span><span class="w"> </span><span class="p">(</span><span class="n">iterator</span><span class="p">.</span><span class="na">hasNext</span><span class="p">())</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="k">try</span><span class="w"> </span><span class="p">(</span><span class="n">VectorSchemaRoot</span><span class="w"> </span><span class="n">root</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">iterator</span><span class="p">.</span><span class="na">next</span><span class="p">())</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="n">System</span><span class="p">.</span><span class="na">out</span><span class="p">.</span><span class="na">print</span><span class="p">(</span><span class="n">root</span><span class="p">.</span><span class="na">contentToTSVString</span><span class="p">());</span> |
| <span class="w"> </span><span class="p">}</span> |
| <span class="w"> </span><span class="p">}</span> |
| <span class="w"> </span><span class="p">}</span> |
| <span class="p">}</span><span class="w"> </span><span class="k">catch</span><span class="w"> </span><span class="p">(</span><span class="n">SQLException</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">IOException</span><span class="w"> </span><span class="n">e</span><span class="p">)</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="n">e</span><span class="p">.</span><span class="na">printStackTrace</span><span class="p">();</span> |
| <span class="p">}</span> |
| </pre></div> |
| </div> |
| <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19 |
| 101 true 1000000000300 some char text [1,2,3] |
| 102 true 100000000030 some char text [1,2] |
| INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19 |
| 103 true 10000000003 some char text [1] |
| </pre></div> |
| </div> |
| </section> |
| <section id="configuring-numeric-decimal-precision-and-scale"> |
| <h2><a class="toc-backref" href="#id5" role="doc-backlink">Configuring numeric (decimal) precision and scale</a><a class="headerlink" href="#configuring-numeric-decimal-precision-and-scale" title="Link to this heading">¶</a></h2> |
| <p>By default, the scale of any decimal values must exactly match the defined |
| scale of the Arrow type of the column, or else an UnsupportedOperationException |
| will be thrown with a message like <code class="docutils literal notranslate"><span class="pre">BigDecimal</span> <span class="pre">scale</span> <span class="pre">must</span> <span class="pre">equal</span> <span class="pre">that</span> <span class="pre">in</span> <span class="pre">the</span> <span class="pre">Arrow</span> |
| <span class="pre">vector</span></code>.</p> |
| <p>This can happen because Arrow infers the type from the ResultSet metadata, which |
| is not accurate for all database drivers. The JDBC adapter lets you avoid this |
| by either overriding the decimal scale, or by providing a RoundingMode via |
| <code class="docutils literal notranslate"><span class="pre">setBigDecimalRoundingMode</span></code> to convert values to the expected scale.</p> |
| <p>In this example, we have a BigInt column. By default, the inferred scale |
| is 0. We override the scale to 7 and then set a RoundingMode to convert |
| values to the given scale.</p> |
| <div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.ArrowVectorIterator</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcFieldInfo</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcToArrow</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcToArrowConfig</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcToArrowConfigBuilder</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.adapter.jdbc.JdbcToArrowUtils</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.memory.BufferAllocator</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.memory.RootAllocator</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.arrow.vector.VectorSchemaRoot</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">org.apache.ibatis.jdbc.ScriptRunner</span><span class="p">;</span> |
| |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.io.BufferedReader</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.io.FileReader</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.io.IOException</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.math.RoundingMode</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.Connection</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.DriverManager</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.ResultSet</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.SQLException</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.sql.Types</span><span class="p">;</span> |
| <span class="kn">import</span><span class="w"> </span><span class="nn">java.util.HashMap</span><span class="p">;</span> |
| |
| <span class="k">try</span><span class="w"> </span><span class="p">(</span><span class="n">BufferAllocator</span><span class="w"> </span><span class="n">allocator</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">RootAllocator</span><span class="p">();</span> |
| <span class="w"> </span><span class="n">Connection</span><span class="w"> </span><span class="n">connection</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">DriverManager</span><span class="p">.</span><span class="na">getConnection</span><span class="p">(</span> |
| <span class="w"> </span><span class="s">"jdbc:h2:mem:h2-jdbc-adapter"</span><span class="p">))</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="n">ScriptRunner</span><span class="w"> </span><span class="n">runnerDDLDML</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">ScriptRunner</span><span class="p">(</span><span class="n">connection</span><span class="p">);</span> |
| <span class="w"> </span><span class="n">runnerDDLDML</span><span class="p">.</span><span class="na">setLogWriter</span><span class="p">(</span><span class="kc">null</span><span class="p">);</span> |
| <span class="w"> </span><span class="n">runnerDDLDML</span><span class="p">.</span><span class="na">runScript</span><span class="p">(</span><span class="k">new</span><span class="w"> </span><span class="n">BufferedReader</span><span class="p">(</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">FileReader</span><span class="p">(</span><span class="s">"./thirdpartydeps/jdbc/h2-ddl.sql"</span><span class="p">)));</span> |
| <span class="w"> </span><span class="n">runnerDDLDML</span><span class="p">.</span><span class="na">runScript</span><span class="p">(</span><span class="k">new</span><span class="w"> </span><span class="n">BufferedReader</span><span class="p">(</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">FileReader</span><span class="p">(</span><span class="s">"./thirdpartydeps/jdbc/h2-dml.sql"</span><span class="p">)));</span> |
| <span class="w"> </span><span class="n">JdbcToArrowConfig</span><span class="w"> </span><span class="n">config</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">JdbcToArrowConfigBuilder</span><span class="p">(</span><span class="n">allocator</span><span class="p">,</span> |
| <span class="w"> </span><span class="n">JdbcToArrowUtils</span><span class="p">.</span><span class="na">getUtcCalendar</span><span class="p">())</span> |
| <span class="w"> </span><span class="p">.</span><span class="na">setTargetBatchSize</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span> |
| <span class="w"> </span><span class="p">.</span><span class="na">setArraySubTypeByColumnNameMap</span><span class="p">(</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">HashMap</span><span class="o"><></span><span class="p">()</span><span class="w"> </span><span class="p">{{</span> |
| <span class="w"> </span><span class="n">put</span><span class="p">(</span><span class="s">"LIST_FIELD19"</span><span class="p">,</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">JdbcFieldInfo</span><span class="p">(</span><span class="n">Types</span><span class="p">.</span><span class="na">INTEGER</span><span class="p">));</span> |
| <span class="w"> </span><span class="p">}}</span> |
| <span class="w"> </span><span class="p">)</span> |
| <span class="w"> </span><span class="p">.</span><span class="na">setExplicitTypesByColumnName</span><span class="p">(</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">HashMap</span><span class="o"><></span><span class="p">()</span><span class="w"> </span><span class="p">{{</span> |
| <span class="w"> </span><span class="n">put</span><span class="p">(</span><span class="s">"BIGINT_FIELD5"</span><span class="p">,</span> |
| <span class="w"> </span><span class="k">new</span><span class="w"> </span><span class="n">JdbcFieldInfo</span><span class="p">(</span><span class="n">Types</span><span class="p">.</span><span class="na">DECIMAL</span><span class="p">,</span><span class="w"> </span><span class="mi">20</span><span class="p">,</span><span class="w"> </span><span class="mi">7</span><span class="p">));</span> |
| <span class="w"> </span><span class="p">}}</span> |
| <span class="w"> </span><span class="p">)</span> |
| <span class="w"> </span><span class="p">.</span><span class="na">setBigDecimalRoundingMode</span><span class="p">(</span><span class="n">RoundingMode</span><span class="p">.</span><span class="na">UNNECESSARY</span><span class="p">)</span> |
| <span class="w"> </span><span class="p">.</span><span class="na">build</span><span class="p">();</span> |
| <span class="w"> </span><span class="k">try</span><span class="w"> </span><span class="p">(</span><span class="n">ResultSet</span><span class="w"> </span><span class="n">resultSet</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">connection</span><span class="p">.</span><span class="na">createStatement</span><span class="p">().</span><span class="na">executeQuery</span><span class="p">(</span> |
| <span class="w"> </span><span class="s">"SELECT int_field1, bool_field2, bigint_field5, char_field16, list_field19 FROM TABLE1"</span><span class="p">);</span> |
| <span class="w"> </span><span class="n">ArrowVectorIterator</span><span class="w"> </span><span class="n">iterator</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">JdbcToArrow</span><span class="p">.</span><span class="na">sqlToArrowVectorIterator</span><span class="p">(</span> |
| <span class="w"> </span><span class="n">resultSet</span><span class="p">,</span><span class="w"> </span><span class="n">config</span><span class="p">))</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="k">while</span><span class="w"> </span><span class="p">(</span><span class="n">iterator</span><span class="p">.</span><span class="na">hasNext</span><span class="p">())</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="k">try</span><span class="w"> </span><span class="p">(</span><span class="n">VectorSchemaRoot</span><span class="w"> </span><span class="n">root</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">iterator</span><span class="p">.</span><span class="na">next</span><span class="p">())</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="n">System</span><span class="p">.</span><span class="na">out</span><span class="p">.</span><span class="na">print</span><span class="p">(</span><span class="n">root</span><span class="p">.</span><span class="na">contentToTSVString</span><span class="p">());</span> |
| <span class="w"> </span><span class="p">}</span> |
| <span class="w"> </span><span class="p">}</span> |
| <span class="w"> </span><span class="p">}</span> |
| <span class="p">}</span><span class="w"> </span><span class="k">catch</span><span class="w"> </span><span class="p">(</span><span class="n">SQLException</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">IOException</span><span class="w"> </span><span class="n">e</span><span class="p">)</span><span class="w"> </span><span class="p">{</span> |
| <span class="w"> </span><span class="n">e</span><span class="p">.</span><span class="na">printStackTrace</span><span class="p">();</span> |
| <span class="p">}</span> |
| </pre></div> |
| </div> |
| <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19 |
| 101 true 1000000000300.0000000 some char text [1,2,3] |
| 102 true 100000000030.0000000 some char text [1,2] |
| INT_FIELD1 BOOL_FIELD2 BIGINT_FIELD5 CHAR_FIELD16 LIST_FIELD19 |
| 103 true 10000000003.0000000 some char text [1] |
| </pre></div> |
| </div> |
| </section> |
| </section> |
| |
| |
| </div> |
| |
| </div> |
| </div> |
| <div class="sphinxsidebar" role="navigation" aria-label="main navigation"> |
| <div class="sphinxsidebarwrapper"> |
| <p class="logo"> |
| <a href="index.html"> |
| <img class="logo" src="_static/arrow-logo_vertical_black-txt_transparent-bg.svg" alt="Logo" /> |
| |
| </a> |
| </p> |
| |
| |
| |
| |
| |
| |
| <p> |
| <iframe src="https://ghbtns.com/github-btn.html?user=apache&repo=arrow-cookbook&type=none&count=true&size=large&v=2" |
| allowtransparency="true" frameborder="0" scrolling="0" width="200px" height="35px"></iframe> |
| </p> |
| |
| |
| |
| |
| |
| <h3>Navigation</h3> |
| <p class="caption" role="heading"><span class="caption-text">Contents:</span></p> |
| <ul class="current"> |
| <li class="toctree-l1"><a class="reference internal" href="create.html">Creating Arrow Objects</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="schema.html">Working with Schema</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="io.html">Reading and writing data</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="flight.html">Arrow Flight</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="dataset.html">Dataset</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="substrait.html">Substrait</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="data.html">Data manipulation</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="avro.html">Avro</a></li> |
| <li class="toctree-l1 current"><a class="current reference internal" href="#">Arrow JDBC Adapter</a><ul> |
| <li class="toctree-l2"><a class="reference internal" href="#resultset-to-vectorschemaroot-conversion">ResultSet to VectorSchemaRoot Conversion</a></li> |
| <li class="toctree-l2"><a class="reference internal" href="#configuring-array-subtypes">Configuring Array subtypes</a></li> |
| <li class="toctree-l2"><a class="reference internal" href="#configuring-batch-size">Configuring batch size</a></li> |
| <li class="toctree-l2"><a class="reference internal" href="#configuring-numeric-decimal-precision-and-scale">Configuring numeric (decimal) precision and scale</a></li> |
| </ul> |
| </li> |
| </ul> |
| |
| |
| <hr /> |
| <ul> |
| |
| <li class="toctree-l1"><a href="https://arrow.apache.org/docs/java/index.html">User Guide</a></li> |
| |
| <li class="toctree-l1"><a href="https://arrow.apache.org/docs/java/reference/index.html">API Reference</a></li> |
| |
| </ul> |
| <div class="relations"> |
| <h3>Related Topics</h3> |
| <ul> |
| <li><a href="index.html">Documentation overview</a><ul> |
| <li>Previous: <a href="avro.html" title="previous chapter">Avro</a></li> |
| </ul></li> |
| </ul> |
| </div> |
| <div id="searchbox" style="display: none" role="search"> |
| <h3 id="searchlabel">Quick search</h3> |
| <div class="searchformwrapper"> |
| <form class="search" action="search.html" method="get"> |
| <input type="text" name="q" aria-labelledby="searchlabel" autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false"/> |
| <input type="submit" value="Go" /> |
| </form> |
| </div> |
| </div> |
| <script>document.getElementById('searchbox').style.display = "block"</script> |
| |
| |
| |
| |
| |
| |
| |
| |
| </div> |
| </div> |
| <div class="clearer"></div> |
| </div> |
| <div class="footer"> |
| ©2022, Apache Software Foundation. |
| |
| | |
| Powered by <a href="https://www.sphinx-doc.org/">Sphinx 7.2.6</a> |
| & <a href="https://alabaster.readthedocs.io">Alabaster 0.7.16</a> |
| |
| | |
| <a href="_sources/jdbc.rst.txt" |
| rel="nofollow">Page source</a> |
| </div> |
| |
| |
| |
| |
| </body> |
| </html> |
