Use SQL without Databases
Tags: webdev, sql, database
Published: 2023-12-15 12:34:56


**We have some csv/xls files as the following shows:**
<p><img alt="" style="box-shadow: rgb(238, 238, 238) 0px 0px 5px; background-image: none; background-color: transparent;" src="https://img.raqsoft.com.cn/file/2023/11/4161550c69774182bfce3d969cbe0ae7_430d9b9a7e07665a5fca83de8237df88_531322.png"></p> 
<p><img alt="" style="box-shadow: rgb(238, 238, 238) 0px 0px 5px; background-image: none; background-color: transparent;" src="https://img.raqsoft.com.cn/file/2023/11/bd25d8e93bec44c1a0267569d699807e_dfb0d3c19ee6d9d57f7e2d522f2b605f_531320.png"></p> 
<p>SQL is suitable for handling those data, but the language depends on databases to work. We need to install a database and import the data into it to process. This results in bloated application system. Yet such a small task is not worth the effort. Is there a technology that can treat these files as data tables and use SQL to directly query them?</p> 
<hr> 
<p>esProc SPL is just what you expect.</p> 
<p>esProc SPL is an open-source software, which is offered in <a href="https://github.com/SPLWare/esProc" target="_blank" rel="nofollow">https://github.com/SPLWare/esProc</a>.</p> 
<hr> 
<p>It provides standard JDBC driver. By importing it in a Java application, we can perform SQL queries directly on files.</p> 
<pre><div aria-label="Copy" onmouseover="this.setAttribute('aria-label', 'Copy')" class="sym-code__copy tooltipped tooltipped__w" onclick="this.previousElementSibling.select();document.execCommand('copy');this.setAttribute('aria-label', 'Copied')"><svg><use xlink:href="#iconArticles"></use></svg></div><code class="hljs bash" style="max-height: 750px;">Class.forName(<span class="hljs-string">"com.esproc.jdbc.InternalDriver"</span>);
Connection conn =DriverManager.getConnection(<span class="hljs-string">"jdbc:esproc:local://"</span>);
PrepareStatement st = conn.prepareStatement(<span class="hljs-string">"<span class="hljs-variable">$select</span> * from employee.txt where SALARY &gt;=? and SALARY&lt;?"</span>);
st.setObject(1, 3000);
st.setObject(2, 5000);
ResultSet result=st.execute();
</code></pre> 
<p>We can also query a file in SQL from the command line:</p> 
<pre><div aria-label="Copy" onmouseover="this.setAttribute('aria-label', 'Copy')" class="sym-code__copy tooltipped tooltipped__w" onclick="this.previousElementSibling.select();document.execCommand('copy');this.setAttribute('aria-label', 'Copied')"><svg><use xlink:href="#iconArticles"></use></svg></div><code class="hljs sql" style="max-height: 750px;">esprocx.exe -R <span class="hljs-keyword">select</span> <span class="hljs-keyword">Client</span>,<span class="hljs-keyword">sum</span>(Amount) <span class="hljs-keyword">from</span> d:/Orders.csv <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">Client</span>
</code></pre> 
<hr> 
<p>esProc supports SQL syntax similar to SQL92 standard:</p> 
<pre><div aria-label="Copy" onmouseover="this.setAttribute('aria-label', 'Copy')" class="sym-code__copy tooltipped tooltipped__w" onclick="this.previousElementSibling.select();document.execCommand('copy');this.setAttribute('aria-label', 'Copied')"><svg><use xlink:href="#iconArticles"></use></svg></div><code class="hljs sql" style="max-height: 750px;"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> orders.xls <span class="hljs-keyword">where</span> Amount&gt;<span class="hljs-number">100</span> <span class="hljs-keyword">and</span> Area=<span class="hljs-string">'West'</span> <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> OrderDate <span class="hljs-keyword">desc</span>
<span class="hljs-keyword">select</span> Area, <span class="hljs-keyword">sum</span>(Amount) <span class="hljs-keyword">from</span> orders.xls <span class="hljs-keyword">having</span> <span class="hljs-keyword">sum</span>(Amount)&gt;<span class="hljs-number">1000</span>
<span class="hljs-keyword">select</span> <span class="hljs-keyword">distinct</span> Company <span class="hljs-keyword">from</span> orders.xls <span class="hljs-keyword">where</span> OrderDate&gt;<span class="hljs-built_in">date</span>(<span class="hljs-string">'2012-7-1'</span>)
</code></pre> 
<p>Joins:</p> 
<pre><div aria-label="Copy" onmouseover="this.setAttribute('aria-label', 'Copy')" class="sym-code__copy tooltipped tooltipped__w" onclick="this.previousElementSibling.select();document.execCommand('copy');this.setAttribute('aria-label', 'Copied')"><svg><use xlink:href="#iconArticles"></use></svg></div><code class="hljs sql" style="max-height: 750px;"><span class="hljs-keyword">select</span> o.OrderId,o.Client,e.Name e.Dept <span class="hljs-keyword">from</span> d:/Orders.csv o ,d:/Employees.csv e <span class="hljs-keyword">where</span> o.SellerId=e.Eid
<span class="hljs-keyword">select</span> o.OrderId,o.Client,e.Name e.Dept,e.EId <span class="hljs-keyword">from</span> Orders.txt o <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> Employees.txt e <span class="hljs-keyword">on</span> o.SellerId=e.Eid
</code></pre> 
<p>And subqueries and with statement:</p> 
<pre><div aria-label="Copy" onmouseover="this.setAttribute('aria-label', 'Copy')" class="sym-code__copy tooltipped tooltipped__w" onclick="this.previousElementSibling.select();document.execCommand('copy');this.setAttribute('aria-label', 'Copied')"><svg><use xlink:href="#iconArticles"></use></svg></div><code class="hljs sql" style="max-height: 750px;"><span class="hljs-keyword">select</span> t.Client, t.s, ct.Name, ct.address <span class="hljs-keyword">from</span> 
   (<span class="hljs-keyword">select</span> <span class="hljs-keyword">Client</span> ,<span class="hljs-keyword">sum</span>(amount) s <span class="hljs-keyword">from</span> Orders.csv <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">Client</span>) 
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> ClientTable ct <span class="hljs-keyword">on</span> t.Client=ct.Client
<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> d:/Orders.txt o <span class="hljs-keyword">where</span> o.sellerid <span class="hljs-keyword">in</span> (<span class="hljs-keyword">select</span> eid <span class="hljs-keyword">from</span> Employees.txt)
<span class="hljs-keyword">with</span> t <span class="hljs-keyword">as</span> (<span class="hljs-keyword">select</span> <span class="hljs-keyword">Client</span> ,<span class="hljs-keyword">sum</span>(amount) s <span class="hljs-keyword">from</span> Orders.csv <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">Client</span>)
<span class="hljs-keyword">select</span> t.Client, t.s, ct.Name, ct.address <span class="hljs-keyword">from</span> t <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> ClientTable ct <span class="hljs-keyword">on</span> t.Client=ct.Client
</code></pre> 
<hr> 
<p>In fact, esProc does not aim specifically to provide SQL syntax. It has its own SPL syntax and supports the database language based on SPL passingly. This explains its ability to execute SQL without databases.</p> 
<p>With the support of SPL, SQL gets a broader range of application scenarios, including those with irregular-format files:</p> 
<p>Text files separated by |:</p> 
<pre><div aria-label="Copy" onmouseover="this.setAttribute('aria-label', 'Copy')" class="sym-code__copy tooltipped tooltipped__w" onclick="this.previousElementSibling.select();document.execCommand('copy');this.setAttribute('aria-label', 'Copied')"><svg><use xlink:href="#iconArticles"></use></svg></div><code class="hljs coffeescript" style="max-height: 750px;">select * from {file(<span class="hljs-string">"Orders.txt"</span>).import@t(;<span class="hljs-string">"|"</span>)} where Amount&gt;=<span class="hljs-number">100</span> <span class="hljs-keyword">and</span> Client like <span class="hljs-string">'bro'</span> <span class="hljs-keyword">or</span> OrderDate <span class="hljs-keyword">is</span> <span class="hljs-literal">null</span>
</code></pre> 
<p>Text files without the title row, where SPL uses ordinal numbers to represent field names:</p> 
<pre><div aria-label="Copy" onmouseover="this.setAttribute('aria-label', 'Copy')" class="sym-code__copy tooltipped tooltipped__w" onclick="this.previousElementSibling.select();document.execCommand('copy');this.setAttribute('aria-label', 'Copied')"><svg><use xlink:href="#iconArticles"></use></svg></div><code class="hljs sql" style="max-height: 750px;"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> {<span class="hljs-keyword">file</span>(<span class="hljs-string">"Orders.txt"</span>).import()} <span class="hljs-keyword">where</span> _4&gt;=<span class="hljs-number">100</span> <span class="hljs-keyword">and</span> _2 <span class="hljs-keyword">like</span> <span class="hljs-string">'bro'</span> <span class="hljs-keyword">or</span> _5 <span class="hljs-keyword">is</span> <span class="hljs-literal">null</span>
</code></pre> 
<p>Reading a certain sheet from an Excel file:</p> 
<pre><div aria-label="Copy" onmouseover="this.setAttribute('aria-label', 'Copy')" class="sym-code__copy tooltipped tooltipped__w" onclick="this.previousElementSibling.select();document.execCommand('copy');this.setAttribute('aria-label', 'Copied')"><svg><use xlink:href="#iconArticles"></use></svg></div><code class="hljs coffeescript" style="max-height: 750px;">select * from {file(<span class="hljs-string">"Orders.xlsx"</span>).xlsimport@t(;<span class="hljs-string">"sheet3"</span>)} where Amount&gt;=<span class="hljs-number">100</span> <span class="hljs-keyword">and</span> Client like <span class="hljs-string">'bro'</span> <span class="hljs-keyword">or</span> OrderDate <span class="hljs-keyword">is</span> <span class="hljs-literal">null</span>
</code></pre> 
<p>Querying a JSON file:</p> 
<pre><div aria-label="Copy" onmouseover="this.setAttribute('aria-label', 'Copy')" class="sym-code__copy tooltipped tooltipped__w" onclick="this.previousElementSibling.select();document.execCommand('copy');this.setAttribute('aria-label', 'Copied')"><svg><use xlink:href="#iconArticles"></use></svg></div><code class="hljs sql" style="max-height: 750px;"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> {<span class="hljs-keyword">json</span>(<span class="hljs-keyword">file</span>(<span class="hljs-string">"data.json"</span>).read())} <span class="hljs-keyword">where</span> Amount&gt;=<span class="hljs-number">100</span> <span class="hljs-keyword">and</span> <span class="hljs-keyword">Client</span> <span class="hljs-keyword">like</span> <span class="hljs-string">'bro'</span> <span class="hljs-keyword">or</span> OrderDate <span class="hljs-keyword">is</span> <span class="hljs-literal">null</span>
</code></pre> 
<p>And handling JSON data downloaded from the web:</p> 
<pre><div aria-label="Copy" onmouseover="this.setAttribute('aria-label', 'Copy')" class="sym-code__copy tooltipped tooltipped__w" onclick="this.previousElementSibling.select();document.execCommand('copy');this.setAttribute('aria-label', 'Copied')"><svg><use xlink:href="#iconArticles"></use></svg></div><code class="hljs sql" style="max-height: 750px;"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> {<span class="hljs-keyword">json</span>(httpfile(<span class="hljs-string">"http://127.0.0.1:6868/api/getData"</span>).read())} <span class="hljs-keyword">where</span> Amount&gt;=<span class="hljs-number">100</span> <span class="hljs-keyword">and</span> <span class="hljs-keyword">Client</span> <span class="hljs-keyword">like</span> <span class="hljs-string">'bro'</span> <span class="hljs-keyword">or</span> OrderDate <span class="hljs-keyword">is</span> <span class="hljs-literal">null</span>
</code></pre> 
<p>Besides relational databases, SPL can also directly access data coming from MongoDB, Kafka, etc. This forms its mixed computing ability over diverse sources.</p> 
<hr> 
<p>Yet, SPL has more abilities than these. From the beginning, the software aims to provide more powerful and more convenient computational capability. SQL syntax has limits in phrasing logics and is more fit for simple scenarios.</p> 
<p>Here is an example. To find the largest number of consecutively rising days for a stock, SQL needs a nested query, which is lengthy and hard to read:</p> 
<pre><div aria-label="Copy" onmouseover="this.setAttribute('aria-label', 'Copy')" class="sym-code__copy tooltipped tooltipped__w" onclick="this.previousElementSibling.select();document.execCommand('copy');this.setAttribute('aria-label', 'Copied')"><svg><use xlink:href="#iconArticles"></use></svg></div><code class="hljs sql" style="max-height: 750px;"><span class="hljs-keyword">select</span> <span class="hljs-keyword">max</span>(ContinuousDays) <span class="hljs-keyword">from</span> (
    <span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(*) ContinuousDays <span class="hljs-keyword">from</span> (
        <span class="hljs-keyword">select</span> <span class="hljs-keyword">sum</span>(UpDownTag) <span class="hljs-keyword">over</span> (<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> TradeDate) NoRisingDays <span class="hljs-keyword">from</span> (
            <span class="hljs-keyword">select</span> TradeDate,<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> Price&gt;lag(price) <span class="hljs-keyword">over</span> ( <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> TradeDate)<span class="hljs-keyword">then</span> <span class="hljs-number">0</span> <span class="hljs-keyword">else</span> <span class="hljs-number">1</span> <span class="hljs-keyword">end</span> UpDownTag <span class="hljs-keyword">from</span> Stock ))
    <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> NoRisingDays )
</code></pre> 
<p>It is much simpler and easier to write the logic in SPL:</p> 
<pre><div aria-label="Copy" onmouseover="this.setAttribute('aria-label', 'Copy')" class="sym-code__copy tooltipped tooltipped__w" onclick="this.previousElementSibling.select();document.execCommand('copy');this.setAttribute('aria-label', 'Copied')"><svg><use xlink:href="#iconArticles"></use></svg></div><code class="hljs css" style="max-height: 750px;"><span class="hljs-selector-tag">Stock</span><span class="hljs-selector-class">.sort</span>(<span class="hljs-selector-tag">TradeDate</span>)<span class="hljs-selector-class">.group</span>@<span class="hljs-keyword">i</span>(<span class="hljs-keyword">Price</span>&lt;<span class="hljs-keyword">Price</span>[-<span class="hljs-keyword">1</span>]).<span class="hljs-keyword">max</span>(~.<span class="hljs-keyword">len</span>())
</code></pre> 
<hr> 
<p>Apart from all these, esProc provides a WYSIWYG IDE, which enables much more conveniently debugging than SQL:</p> 
<p><img alt="" style="box-shadow: rgb(238, 238, 238) 0px 0px 5px; background-image: none; background-color: transparent;" src="https://img.raqsoft.com.cn/file/2023/11/b020d43ec7884123ba721c9fd0735080_clipboard.png"></p> 
<p>Read <a href="https://c.scudata.com/article/1681713093064" rel="nofollow">A programming language coding in a grid</a> and get deeper understanding about SPL. The language can replace almost all database computing abilities with yet more powerful performance.</p>
      
[Download SPL here](http://c.raqsoft.com/article/1595817756260)

[Check GitHub Repo](https://github.com/SPLWare/esProc)
_Don't forget to give us a star ⭐ on the repo_