[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/branch-1.12 Commit: 0e11746aca3e0abf4e60fd1545eb89ecf174ca95 Parents: 6b54b6e Author: Julian Hyde <[email protected]> Authored: Thu Mar 16 11:24:06 2017 -0700 Committer: Julian Hyde <[email protected]> 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
