calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject [2/2] calcite git commit: [CALCITE-1704] Execute queries on CSV files using simple sqlline command
Date Fri, 17 Mar 2017 21:32:39 GMT
[CALCITE-1704] Execute queries on CSV files using simple sqlline command

Document how to use file adapter without an explicit model;
make sure that baseDirectory is set even without explicit model.


Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/0e11746a
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/0e11746a
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/0e11746a

Branch: refs/heads/master
Commit: 0e11746aca3e0abf4e60fd1545eb89ecf174ca95
Parents: 6b54b6e
Author: Julian Hyde <jhyde@apache.org>
Authored: Thu Mar 16 11:24:06 2017 -0700
Committer: Julian Hyde <jhyde@apache.org>
Committed: Fri Mar 17 11:48:45 2017 -0700

----------------------------------------------------------------------
 .../org/apache/calcite/model/ModelHandler.java  | 10 ++--
 site/_docs/file_adapter.md                      | 56 +++++++++++++++++---
 2 files changed, 56 insertions(+), 10 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/calcite/blob/0e11746a/core/src/main/java/org/apache/calcite/model/ModelHandler.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/model/ModelHandler.java b/core/src/main/java/org/apache/calcite/model/ModelHandler.java
index bd8bcac..adfdd07 100644
--- a/core/src/main/java/org/apache/calcite/model/ModelHandler.java
+++ b/core/src/main/java/org/apache/calcite/model/ModelHandler.java
@@ -236,13 +236,15 @@ public class ModelHandler {
           builder.put(extraOperand.camelName, modelUri);
           break;
         case BASE_DIRECTORY:
+          File f = null;
           if (!modelUri.startsWith("inline:")) {
             final File file = new File(modelUri);
-            final File parentFile = file.getParentFile();
-            if (parentFile != null) {
-              builder.put(extraOperand.camelName, parentFile);
-            }
+            f = file.getParentFile();
           }
+          if (f == null) {
+            f = new File("");
+          }
+          builder.put(extraOperand.camelName, f);
           break;
         case TABLES:
           if (jsonSchema instanceof JsonCustomSchema) {

http://git-wip-us.apache.org/repos/asf/calcite/blob/0e11746a/site/_docs/file_adapter.md
----------------------------------------------------------------------
diff --git a/site/_docs/file_adapter.md b/site/_docs/file_adapter.md
index b47d72a..57e9de4 100644
--- a/site/_docs/file_adapter.md
+++ b/site/_docs/file_adapter.md
@@ -22,7 +22,7 @@ limitations under the License.
 {% endcomment %}
 -->
 
-# Overview
+## Overview
 
 The file adapter is able to read files in a variety of formats,
 and can also read files over various protocols, such as HTTP.
@@ -53,7 +53,11 @@ comprising almost 1/2 of the state's population:
 +---------------------+----------------------+
 ```
 
-# A simple example
+For simple file formats such as CSV, the file is self-describing and
+you don't even need a model.
+See [CSV files and model-free browsing](#csv_files_and_model_free_browsing).
+
+## A simple example
 
 Let's start with a simple example. First, we need a
 [model definition]({{ site.baseurl }}/docs/model.html),
@@ -151,7 +155,7 @@ sqlline> select * from sales.emps;
 5 rows selected
 {% endhighlight %}
 
-# Mapping tables
+## Mapping tables
 
 Now for a more complex example. This time we connect to Wikipedia via
 HTTP, read pages for US states and cities, and extract data from HTML
@@ -208,7 +212,7 @@ navigation; selectors for both tables and fields follow the
 Field definitions may be used to rename or skip source fields, to
 select and condition the cell contents and to set a data type.
 
-# Parsing cell contents
+### Parsing cell contents
 
 The file adapter can select DOM nodes within a cell, replace text
 within the selected element, match within the selected text, and
@@ -217,7 +221,7 @@ steps are applied in the order described and replace and match
 patterns are based on
 [Java regular expressions](http://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html).
 
-# Further examples
+### Further examples
 
 There are more examples in the form of a script:
 
@@ -229,7 +233,47 @@ $ ./sqlline -f file/src/test/resources/webjoin.sql
 each query containing a join.  These are expected and do not affect
 query results.  These messages will be suppressed in the next release.)
 
-# Future improvements
+## CSV files and model-free browsing
+
+Some files are describe their own schema, and for these files, we do not need a model. For
example, `DEPTS.csv` has an
+integer `DEPTNO` column and a string `NAME` column:
+
+{% highlight json %}
+DEPTNO:int,NAME:string
+10,"Sales"
+20,"Marketing"
+30,"Accounts"
+{% endhighlight %}
+
+You can launch `sqlline`, and pointing the file adapter that directory,
+and every CSV file becomes a table:
+
+{% highlight bash %}
+$ ls file/src/test/resources/sales-csv
+ -rw-r--r-- 1 jhyde jhyde  62 Mar 15 10:16 DEPTS.csv
+ -rw-r--r-- 1 jhyde jhyde 262 Mar 15 10:16 EMPS.csv.gz
+
+$ ./sqlline -u "jdbc:calcite:schemaFactory=org.apache.calcite.adapter.file.FileSchemaFactory;schema.directory=file/src/test/resources/sales-csv"
+sqlline> !tables
++-----------+-------------+------------+------------+
+| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE |
++-----------+-------------+------------+------------+
+|           | adhoc       | DEPTS      | TABLE      |
+|           | adhoc       | EMPS       | TABLE      |
++-----------+-------------+------------+------------+
+
+sqlline> select distinct deptno from depts;
++--------+
+| DEPTNO |
++--------+
+| 20     |
+| 10     |
+| 30     |
++--------+
+3 rows selected (0.985 seconds)
+{% endhighlight %}
+
+## Future improvements
 
 We are continuing to enhance the adapter, and would welcome
 contributions of new parsing capabilities (for example parsing JSON


Mime
View raw message