You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by mm...@apache.org on 2019/07/20 04:17:09 UTC
[calcite] branch master updated: [CALCITE-3176] File adapter for
parsing JSON files
This is an automated email from the ASF dual-hosted git repository.
mmior pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new ab71c4c [CALCITE-3176] File adapter for parsing JSON files
ab71c4c is described below
commit ab71c4cae5a5c3c7d979337a2d38ddaf271aa206
Author: XuQianJin-Stars <x1...@163.com>
AuthorDate: Sat Jul 6 21:30:18 2019 +0800
[CALCITE-3176] File adapter for parsing JSON files
---
.../org/apache/calcite/adapter/csv/CsvSchema.java | 12 +--
.../apache/calcite/adapter/csv/JsonEnumerator.java | 51 ++++++------
.../{JsonTable.java => JsonScannableTable.java} | 34 +++-----
.../org/apache/calcite/adapter/csv/JsonTable.java | 93 ++++++++++++++++------
.../test/java/org/apache/calcite/test/CsvTest.java | 18 +++--
example/csv/src/test/resources/bug/ARCHERS.json | 43 ++++++++++
example/csv/src/test/resources/bug/archers.json | 27 -------
.../apache/calcite/adapter/file/FileSchema.java | 29 ++++---
.../calcite/adapter/file/FileReaderTest.java | 81 +++++++++++++++++++
.../org/apache/calcite/adapter/file/SqlTest.java | 83 ++++++++++++++++++-
file/src/test/resources/sales-json.json | 32 ++++++++
file/src/test/resources/sales-json/DATE.csv | 9 +++
file/src/test/resources/sales-json/DEPTS.json | 30 +++++++
file/src/test/resources/sales-json/EMPS.json | 78 ++++++++++++++++++
file/src/test/resources/sales-json/EMPTY.json | 16 ++++
file/src/test/resources/sales-json/SDEPTS.json | 42 ++++++++++
site/_docs/file_adapter.md | 51 ++++++++++++
17 files changed, 603 insertions(+), 126 deletions(-)
diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvSchema.java b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvSchema.java
index 8744966..e3bfa01 100644
--- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvSchema.java
+++ b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvSchema.java
@@ -92,14 +92,14 @@ public class CsvSchema extends AbstractSchema {
Source sourceSansGz = source.trim(".gz");
final Source sourceSansJson = sourceSansGz.trimOrNull(".json");
if (sourceSansJson != null) {
- JsonTable table = new JsonTable(source);
+ final Table table = new JsonScannableTable(source);
builder.put(sourceSansJson.relative(baseSource).path(), table);
- continue;
}
- final Source sourceSansCsv = sourceSansGz.trim(".csv");
-
- final Table table = createTable(source);
- builder.put(sourceSansCsv.relative(baseSource).path(), table);
+ final Source sourceSansCsv = sourceSansGz.trimOrNull(".csv");
+ if (sourceSansCsv != null) {
+ final Table table = createTable(source);
+ builder.put(sourceSansCsv.relative(baseSource).path(), table);
+ }
}
return builder.build();
}
diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonEnumerator.java b/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonEnumerator.java
index ecf660f..fbb5b61 100644
--- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonEnumerator.java
+++ b/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonEnumerator.java
@@ -18,40 +18,38 @@ package org.apache.calcite.adapter.csv;
import org.apache.calcite.linq4j.Enumerator;
import org.apache.calcite.linq4j.Linq4j;
-import org.apache.calcite.util.Source;
-import com.fasterxml.jackson.core.JsonParser;
-import com.fasterxml.jackson.databind.ObjectMapper;
-
-import java.io.IOException;
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.LinkedHashMap;
import java.util.List;
+import java.util.Map;
+
+/**
+ * Enumerator that reads from a JSON file.
+ */
+public class JsonEnumerator implements Enumerator<Object[]> {
-/** Enumerator that reads from a JSON file. */
-class JsonEnumerator implements Enumerator<Object[]> {
- private final Enumerator<Object> enumerator;
+ private Enumerator<Object[]> enumerator;
- JsonEnumerator(Source source) {
- try {
- final ObjectMapper mapper = new ObjectMapper();
- mapper.configure(JsonParser.Feature.ALLOW_UNQUOTED_FIELD_NAMES, true);
- mapper.configure(JsonParser.Feature.ALLOW_SINGLE_QUOTES, true);
- mapper.configure(JsonParser.Feature.ALLOW_COMMENTS, true);
- List<Object> list;
- if (source.protocol().equals("file")) {
+ public JsonEnumerator(List<Object> list) {
+ List<Object[]> objs = new ArrayList<Object[]>();
+ for (Object obj : list) {
+ if (obj instanceof Collection) {
//noinspection unchecked
- list = mapper.readValue(source.file(), List.class);
+ List<Object> tmp = (List<Object>) obj;
+ objs.add(tmp.toArray());
+ } else if (obj instanceof Map) {
+ objs.add(((LinkedHashMap) obj).values().toArray());
} else {
- //noinspection unchecked
- list = mapper.readValue(source.url(), List.class);
+ objs.add(new Object[]{obj});
}
- enumerator = Linq4j.enumerator(list);
- } catch (IOException e) {
- throw new RuntimeException(e);
}
+ enumerator = Linq4j.enumerator(objs);
}
public Object[] current() {
- return new Object[] {enumerator.current()};
+ return enumerator.current();
}
public boolean moveNext() {
@@ -63,12 +61,9 @@ class JsonEnumerator implements Enumerator<Object[]> {
}
public void close() {
- try {
- enumerator.close();
- } catch (Exception e) {
- throw new RuntimeException("Error closing JSON reader", e);
- }
+ enumerator.close();
}
+
}
// End JsonEnumerator.java
diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonTable.java b/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonScannableTable.java
similarity index 62%
copy from example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonTable.java
copy to example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonScannableTable.java
index 3c7c202..fe0c013 100644
--- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonTable.java
+++ b/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonScannableTable.java
@@ -20,43 +20,35 @@ import org.apache.calcite.DataContext;
import org.apache.calcite.linq4j.AbstractEnumerable;
import org.apache.calcite.linq4j.Enumerable;
import org.apache.calcite.linq4j.Enumerator;
-import org.apache.calcite.rel.type.RelDataType;
-import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.schema.ScannableTable;
-import org.apache.calcite.schema.impl.AbstractTable;
-import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.util.Source;
/**
* Table based on a JSON file.
+ *
+ * <p>It implements the {@link ScannableTable} interface, so Calcite gets
+ * data by calling the {@link #scan(DataContext)} method.
*/
-public class JsonTable extends AbstractTable implements ScannableTable {
- private final Source source;
-
- /** Creates a JsonTable. */
- public JsonTable(Source source) {
- this.source = source;
+public class JsonScannableTable extends JsonTable
+ implements ScannableTable {
+ /**
+ * Creates a JsonScannableTable.
+ */
+ public JsonScannableTable(Source source) {
+ super(source);
}
public String toString() {
- return "JsonTable";
- }
-
- public RelDataType getRowType(RelDataTypeFactory typeFactory) {
- return typeFactory.builder().add("_MAP",
- typeFactory.createMapType(
- typeFactory.createSqlType(SqlTypeName.VARCHAR),
- typeFactory.createTypeWithNullability(
- typeFactory.createSqlType(SqlTypeName.VARCHAR), true))).build();
+ return "JsonScannableTable";
}
public Enumerable<Object[]> scan(DataContext root) {
return new AbstractEnumerable<Object[]>() {
public Enumerator<Object[]> enumerator() {
- return new JsonEnumerator(source);
+ return new JsonEnumerator(list);
}
};
}
}
-// End JsonTable.java
+// End JsonScannableTable.java
diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonTable.java b/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonTable.java
index 3c7c202..d73a2eb 100644
--- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonTable.java
+++ b/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonTable.java
@@ -16,46 +16,91 @@
*/
package org.apache.calcite.adapter.csv;
-import org.apache.calcite.DataContext;
-import org.apache.calcite.linq4j.AbstractEnumerable;
-import org.apache.calcite.linq4j.Enumerable;
-import org.apache.calcite.linq4j.Enumerator;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
-import org.apache.calcite.schema.ScannableTable;
+import org.apache.calcite.schema.Statistic;
+import org.apache.calcite.schema.Statistics;
import org.apache.calcite.schema.impl.AbstractTable;
-import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.util.Pair;
import org.apache.calcite.util.Source;
+import com.fasterxml.jackson.core.JsonParser;
+import com.fasterxml.jackson.databind.ObjectMapper;
+import com.fasterxml.jackson.databind.exc.MismatchedInputException;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Map;
+
/**
* Table based on a JSON file.
*/
-public class JsonTable extends AbstractTable implements ScannableTable {
- private final Source source;
+public class JsonTable extends AbstractTable {
+ private final ObjectMapper objectMapper = new ObjectMapper();
+ protected final List<Object> list;
+
+ private LinkedHashMap<String, Object> jsonFieldMap = new LinkedHashMap<>(1);
- /** Creates a JsonTable. */
public JsonTable(Source source) {
- this.source = source;
- }
+ Object jsonObj = null;
+ try {
+ objectMapper.configure(JsonParser.Feature.ALLOW_UNQUOTED_FIELD_NAMES, true)
+ .configure(JsonParser.Feature.ALLOW_SINGLE_QUOTES, true)
+ .configure(JsonParser.Feature.ALLOW_COMMENTS, true);
+ if (source.file().exists() && source.file().length() > 0) {
+ if ("file".equals(source.protocol())) {
+ //noinspection unchecked
+ jsonObj = objectMapper.readValue(source.file(), Object.class);
+ } else {
+ //noinspection unchecked
+ jsonObj = objectMapper.readValue(source.url(), Object.class);
+ }
+ }
+ } catch (MismatchedInputException e) {
+ if (!e.getMessage().contains("No content")) {
+ throw new RuntimeException(e);
+ }
+ } catch (Exception e) {
+ throw new RuntimeException(e);
+ }
- public String toString() {
- return "JsonTable";
+ if (jsonObj == null) {
+ list = new ArrayList<>();
+ jsonFieldMap.put("EmptyFileHasNoColumns", Boolean.TRUE);
+ } else if (jsonObj instanceof Collection) {
+ //noinspection unchecked
+ list = (List<Object>) jsonObj;
+ //noinspection unchecked
+ jsonFieldMap = (LinkedHashMap) (list.get(0));
+ } else if (jsonObj instanceof Map) {
+ //noinspection unchecked
+ jsonFieldMap = (LinkedHashMap) jsonObj;
+ //noinspection unchecked
+ list = new ArrayList(((LinkedHashMap) jsonObj).values());
+ } else {
+ jsonFieldMap.put("line", jsonObj);
+ list = new ArrayList<>();
+ list.add(0, jsonObj);
+ }
}
public RelDataType getRowType(RelDataTypeFactory typeFactory) {
- return typeFactory.builder().add("_MAP",
- typeFactory.createMapType(
- typeFactory.createSqlType(SqlTypeName.VARCHAR),
- typeFactory.createTypeWithNullability(
- typeFactory.createSqlType(SqlTypeName.VARCHAR), true))).build();
+ final List<RelDataType> types = new ArrayList<RelDataType>();
+ final List<String> names = new ArrayList<String>();
+
+ for (Object obj : jsonFieldMap.keySet()) {
+ final RelDataType type = typeFactory.createJavaType(jsonFieldMap.get(obj).getClass());
+ names.add(obj.toString());
+ types.add(type);
+ }
+
+ return typeFactory.createStructType(Pair.zip(names, types));
}
- public Enumerable<Object[]> scan(DataContext root) {
- return new AbstractEnumerable<Object[]>() {
- public Enumerator<Object[]> enumerator() {
- return new JsonEnumerator(source);
- }
- };
+ public Statistic getStatistic() {
+ return Statistics.UNKNOWN;
}
}
diff --git a/example/csv/src/test/java/org/apache/calcite/test/CsvTest.java b/example/csv/src/test/java/org/apache/calcite/test/CsvTest.java
index eee4804..f2a9c86 100644
--- a/example/csv/src/test/java/org/apache/calcite/test/CsvTest.java
+++ b/example/csv/src/test/java/org/apache/calcite/test/CsvTest.java
@@ -330,13 +330,19 @@ public class CsvTest {
}
@Test public void testJson() throws SQLException {
- final String sql = "select _MAP['id'] as id,\n"
- + " _MAP['title'] as title,\n"
- + " CHAR_LENGTH(CAST(_MAP['title'] AS VARCHAR(30))) as len\n"
- + " from \"archers\"\n";
+ final String sql = "select * from archers\n";
+ final String[] lines = {
+ "id=19990101; dow=Friday; longDate=New Years Day; title=Tractor trouble.; "
+ + "characters=[Alice, Bob, Xavier]; script=Julian Hyde; summary=; "
+ + "lines=[Bob's tractor got stuck in a field., "
+ + "Alice and Xavier hatch a plan to surprise Charlie.]",
+ "id=19990103; dow=Sunday; longDate=Sunday 3rd January; "
+ + "title=Charlie's surprise.; characters=[Alice, Zebedee, Charlie, Xavier]; "
+ + "script=William Shakespeare; summary=; "
+ + "lines=[Charlie is very surprised by Alice and Xavier's surprise plan.]",
+ };
sql("bug", sql)
- .returns("ID=19990101; TITLE=Tractor trouble.; LEN=16",
- "ID=19990103; TITLE=Charlie's surprise.; LEN=19")
+ .returns(lines)
.ok();
}
diff --git a/example/csv/src/test/resources/bug/ARCHERS.json b/example/csv/src/test/resources/bug/ARCHERS.json
new file mode 100644
index 0000000..0e36759
--- /dev/null
+++ b/example/csv/src/test/resources/bug/ARCHERS.json
@@ -0,0 +1,43 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+[
+{
+ "id": "19990101",
+ "dow": "Friday",
+ "longDate": "New Years Day",
+ "title": "Tractor trouble.",
+ "characters": [ "Alice", "Bob", "Xavier" ],
+ "script": "Julian Hyde",
+ "summary": "",
+ "lines": [
+ "Bob's tractor got stuck in a field.",
+ "Alice and Xavier hatch a plan to surprise Charlie."
+ ]
+},
+{
+ "id": "19990103",
+ "dow": "Sunday",
+ "longDate": "Sunday 3rd January",
+ "title": "Charlie's surprise.",
+ "characters": [ "Alice", "Zebedee", "Charlie", "Xavier" ],
+ "script": "William Shakespeare",
+ "summary": "",
+ "lines": [
+ "Charlie is very surprised by Alice and Xavier's surprise plan."
+ ]
+}
+]
diff --git a/example/csv/src/test/resources/bug/archers.json b/example/csv/src/test/resources/bug/archers.json
deleted file mode 100644
index 37d20be..0000000
--- a/example/csv/src/test/resources/bug/archers.json
+++ /dev/null
@@ -1,27 +0,0 @@
-[
-{
- "id": "19990101",
- "dow": "Friday",
- "longDate": "New Years Day",
- "title": "Tractor trouble.",
- "characters": [ "Alice", "Bob", "Xavier" ],
- "script": "Julian Hyde",
- "summary": "",
- "lines": [
- "Bob's tractor got stuck in a field.",
- "Alice and Xavier hatch a plan to surprise Charlie."
- ]
-},
-{
- "id": "19990103",
- "dow": "Sunday",
- "longDate": "Sunday 3rd January",
- "title": "Charlie's surprise.",
- "characters": [ "Alice", "Zebedee", "Charlie", "Xavier" ],
- "script": "William Shakespeare",
- "summary": "",
- "lines": [
- "Charlie is very surprised by Alice and Xavier's surprise plan."
- ]
-}
-]
diff --git a/file/src/main/java/org/apache/calcite/adapter/file/FileSchema.java b/file/src/main/java/org/apache/calcite/adapter/file/FileSchema.java
index 29f3b9a..3bfc85a 100644
--- a/file/src/main/java/org/apache/calcite/adapter/file/FileSchema.java
+++ b/file/src/main/java/org/apache/calcite/adapter/file/FileSchema.java
@@ -17,7 +17,7 @@
package org.apache.calcite.adapter.file;
import org.apache.calcite.adapter.csv.CsvFilterableTable;
-import org.apache.calcite.adapter.csv.JsonTable;
+import org.apache.calcite.adapter.csv.JsonScannableTable;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.schema.Table;
import org.apache.calcite.schema.impl.AbstractSchema;
@@ -44,10 +44,10 @@ class FileSchema extends AbstractSchema {
/**
* Creates an HTML tables schema.
*
- * @param parentSchema Parent schema
- * @param name Schema name
+ * @param parentSchema Parent schema
+ * @param name Schema name
* @param baseDirectory Base directory to look for relative files, or null
- * @param tables List containing HTML table identifiers
+ * @param tables List containing HTML table identifiers
*/
FileSchema(SchemaPlus parentSchema, String name, File baseDirectory,
List<Map<String, Object>> tables) {
@@ -55,17 +55,21 @@ class FileSchema extends AbstractSchema {
this.baseDirectory = baseDirectory;
}
- /** Looks for a suffix on a string and returns
+ /**
+ * Looks for a suffix on a string and returns
* either the string with the suffix removed
- * or the original string. */
+ * or the original string.
+ */
private static String trim(String s, String suffix) {
String trimmed = trimOrNull(s, suffix);
return trimmed != null ? trimmed : s;
}
- /** Looks for a suffix on a string and returns
+ /**
+ * Looks for a suffix on a string and returns
* either the string with the suffix removed
- * or null. */
+ * or null.
+ */
private static String trimOrNull(String s, String suffix) {
return s.endsWith(suffix)
? s.substring(0, s.length() - suffix.length())
@@ -103,9 +107,8 @@ class FileSchema extends AbstractSchema {
Source sourceSansGz = source.trim(".gz");
final Source sourceSansJson = sourceSansGz.trimOrNull(".json");
if (sourceSansJson != null) {
- JsonTable table = new JsonTable(source);
- builder.put(sourceSansJson.relative(baseSource).path(), table);
- continue;
+ addTable(builder, source, sourceSansJson.relative(baseSource).path(),
+ null);
}
final Source sourceSansCsv = sourceSansGz.trimOrNull(".csv");
if (sourceSansCsv != null) {
@@ -132,11 +135,11 @@ class FileSchema extends AbstractSchema {
}
private boolean addTable(ImmutableMap.Builder<String, Table> builder,
- Source source, String tableName, Map<String, Object> tableDef) {
+ Source source, String tableName, Map<String, Object> tableDef) {
final Source sourceSansGz = source.trim(".gz");
final Source sourceSansJson = sourceSansGz.trimOrNull(".json");
if (sourceSansJson != null) {
- JsonTable table = new JsonTable(source);
+ final Table table = new JsonScannableTable(source);
builder.put(Util.first(tableName, sourceSansJson.path()), table);
return true;
}
diff --git a/file/src/test/java/org/apache/calcite/adapter/file/FileReaderTest.java b/file/src/test/java/org/apache/calcite/adapter/file/FileReaderTest.java
index fb5a570..6bf82e7 100644
--- a/file/src/test/java/org/apache/calcite/adapter/file/FileReaderTest.java
+++ b/file/src/test/java/org/apache/calcite/adapter/file/FileReaderTest.java
@@ -244,6 +244,87 @@ public class FileReaderTest {
rs.close();
}
}
+
+ /**
+ * Tests reading a JSON file via the file adapter.
+ */
+ @Test public void testJsonFile() throws Exception {
+ Properties info = new Properties();
+ final String path = resourcePath("sales-json");
+ final String model = "inline:"
+ + "{\n"
+ + " \"version\": \"1.0\",\n"
+ + " \"defaultSchema\": \"XXX\",\n"
+ + " \"schemas\": [\n"
+ + " {\n"
+ + " \"name\": \"FILES\",\n"
+ + " \"type\": \"custom\",\n"
+ + " \"factory\": \"org.apache.calcite.adapter.file.FileSchemaFactory\",\n"
+ + " \"operand\": {\n"
+ + " \"directory\": " + TestUtil.escapeString(path) + "\n"
+ + " }\n"
+ + " }\n"
+ + " ]\n"
+ + "}";
+ info.put("model", model);
+ info.put("lex", "JAVA");
+
+ try (Connection connection =
+ DriverManager.getConnection("jdbc:calcite:", info);
+ Statement stmt = connection.createStatement()) {
+ final String sql = "select * from FILES.DEPTS";
+ final ResultSet rs = stmt.executeQuery(sql);
+ assertThat(rs.next(), is(true));
+ assertThat(rs.getString(1), is("10"));
+ assertThat(rs.next(), is(true));
+ assertThat(rs.getString(1), is("20"));
+ assertThat(rs.next(), is(true));
+ assertThat(rs.getString(1), is("30"));
+ assertThat(rs.next(), is(false));
+ rs.close();
+ }
+ }
+
+ /**
+ * Tests reading two JSON file with join via the file adapter.
+ */
+ @Test public void testJsonFileWithJoin() throws Exception {
+ Properties info = new Properties();
+ final String path = resourcePath("sales-json");
+ final String model = "inline:"
+ + "{\n"
+ + " \"version\": \"1.0\",\n"
+ + " \"defaultSchema\": \"XXX\",\n"
+ + " \"schemas\": [\n"
+ + " {\n"
+ + " \"name\": \"FILES\",\n"
+ + " \"type\": \"custom\",\n"
+ + " \"factory\": \"org.apache.calcite.adapter.file.FileSchemaFactory\",\n"
+ + " \"operand\": {\n"
+ + " \"directory\": " + TestUtil.escapeString(path) + "\n"
+ + " }\n"
+ + " }\n"
+ + " ]\n"
+ + "}";
+ info.put("model", model);
+ info.put("lex", "JAVA");
+
+ try (Connection connection =
+ DriverManager.getConnection("jdbc:calcite:", info);
+ Statement stmt = connection.createStatement()) {
+ final String sql = "select a.EMPNO,a.NAME,a.CITY,b.DEPTNO "
+ + "from FILES.EMPS a, FILES.DEPTS b where a.DEPTNO = b.DEPTNO";
+ final ResultSet rs = stmt.executeQuery(sql);
+ assertThat(rs.next(), is(true));
+ assertThat(rs.getString(1), is("100"));
+ assertThat(rs.next(), is(true));
+ assertThat(rs.getString(1), is("110"));
+ assertThat(rs.next(), is(true));
+ assertThat(rs.getString(1), is("120"));
+ assertThat(rs.next(), is(false));
+ rs.close();
+ }
+ }
}
// End FileReaderTest.java
diff --git a/file/src/test/java/org/apache/calcite/adapter/file/SqlTest.java b/file/src/test/java/org/apache/calcite/adapter/file/SqlTest.java
index 48ad142..bc443cc 100644
--- a/file/src/test/java/org/apache/calcite/adapter/file/SqlTest.java
+++ b/file/src/test/java/org/apache/calcite/adapter/file/SqlTest.java
@@ -274,7 +274,7 @@ public class SqlTest {
* <a href="https://issues.apache.org/jira/browse/CALCITE-1754">[CALCITE-1754]
* In Csv adapter, convert DATE and TIME values to int, and TIMESTAMP values
* to long</a>. */
- @Test public void testGroupByTimestampAdd() throws SQLException {
+ @Test public void testCsvGroupByTimestampAdd() throws SQLException {
final String sql = "select count(*) as c,\n"
+ " {fn timestampadd(SQL_TSI_DAY, 1, JOINEDAT) } as t\n"
+ "from EMPS group by {fn timestampadd(SQL_TSI_DAY, 1, JOINEDAT ) } ";
@@ -343,6 +343,87 @@ public class SqlTest {
return checking(expectUnordered(expectedLines));
}
}
+
+ /** Reads the DEPTS table from the JSON schema. */
+ @Test public void testJsonSalesDepts() throws SQLException {
+ final String sql = "select * from sales.depts";
+ sql("sales-json", sql)
+ .returns("DEPTNO=10; NAME=Sales",
+ "DEPTNO=20; NAME=Marketing",
+ "DEPTNO=30; NAME=Accounts")
+ .ok();
+ }
+
+ /** Reads the EMPS table from the JSON schema. */
+ @Test public void testJsonSalesEmps() throws SQLException {
+ final String sql = "select * from sales.emps";
+ final String[] lines = {
+ "EMPNO=100; NAME=Fred; DEPTNO=10; GENDER=; CITY=; EMPID=30; AGE=25; SLACKER=true; MANAGER=false; JOINEDAT=1996-08-03",
+ "EMPNO=110; NAME=Eric; DEPTNO=20; GENDER=M; CITY=San Francisco; EMPID=3; AGE=80; SLACKER=null; MANAGER=false; JOINEDAT=2001-01-01",
+ "EMPNO=110; NAME=John; DEPTNO=40; GENDER=M; CITY=Vancouver; EMPID=2; AGE=null; SLACKER=false; MANAGER=true; JOINEDAT=2002-05-03",
+ "EMPNO=120; NAME=Wilma; DEPTNO=20; GENDER=F; CITY=; EMPID=1; AGE=5; SLACKER=null; MANAGER=true; JOINEDAT=2005-09-07",
+ "EMPNO=130; NAME=Alice; DEPTNO=40; GENDER=F; CITY=Vancouver; EMPID=2; AGE=null; SLACKER=false; MANAGER=true; JOINEDAT=2007-01-01",
+ };
+ sql("sales-json", sql).returns(lines).ok();
+ }
+
+ /** Reads the EMPTY table from the JSON schema. The JSON file has no lines,
+ * therefore the table has a system-generated column called
+ * "EmptyFileHasNoColumns". */
+ @Test public void testJsonSalesEmpty() throws SQLException {
+ final String sql = "select * from sales.\"EMPTY\"";
+ checkSql(sql, "sales-json", resultSet -> {
+ try {
+ assertThat(resultSet.getMetaData().getColumnCount(), is(1));
+ assertThat(resultSet.getMetaData().getColumnName(1),
+ is("EmptyFileHasNoColumns"));
+ assertThat(resultSet.getMetaData().getColumnType(1),
+ is(Types.BOOLEAN));
+ String actual = toString(resultSet);
+ assertThat(actual, is(""));
+ } catch (SQLException e) {
+ throw TestUtil.rethrow(e);
+ }
+ return null;
+ });
+ }
+
+ /**
+ * Test returns the result of two json file joins.
+ *
+ * @throws SQLException
+ */
+ @Test public void testJsonJoinOnString() throws SQLException {
+ final String sql = "select emps.EMPNO, emps.NAME, depts.deptno from emps\n"
+ + "join depts on emps.deptno = depts.deptno";
+ final String[] lines = {
+ "EMPNO=100; NAME=Fred; DEPTNO=10",
+ "EMPNO=110; NAME=Eric; DEPTNO=20",
+ "EMPNO=120; NAME=Wilma; DEPTNO=20",
+ };
+ sql("sales-json", sql).returns(lines).ok();
+ }
+
+ /**
+ * The folder contains both JSON files and CSV files joins.
+ *
+ * @throws SQLException
+ */
+ @Test public void testJsonWithCsvJoin() throws SQLException {
+ final String sql = "select emps.empno,\n"
+ + " NAME,\n"
+ + " \"DATE\".JOINEDAT\n"
+ + " from \"DATE\"\n"
+ + "join emps on emps.empno = \"DATE\".EMPNO limit 3";
+ final String[] lines = {
+ "EMPNO=100; NAME=Fred; JOINEDAT=1996-08-03",
+ "EMPNO=110; NAME=Eric; JOINEDAT=2001-01-01",
+ "EMPNO=110; NAME=Eric; JOINEDAT=2002-05-03",
+ };
+ sql("sales-json", sql)
+ .returns(lines)
+ .ok();
+ }
}
// End SqlTest.java
diff --git a/file/src/test/resources/sales-json.json b/file/src/test/resources/sales-json.json
new file mode 100644
index 0000000..4b8b320
--- /dev/null
+++ b/file/src/test/resources/sales-json.json
@@ -0,0 +1,32 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ *
+ * A JSON model of a Calcite schema based on CSV files.
+ */
+{
+ "version": "1.0",
+ "defaultSchema": "SALES",
+ "schemas": [
+ {
+ "name": "SALES",
+ "type": "custom",
+ "factory": "org.apache.calcite.adapter.file.FileSchemaFactory",
+ "operand": {
+ "directory": "sales-json"
+ }
+ }
+ ]
+}
diff --git a/file/src/test/resources/sales-json/DATE.csv b/file/src/test/resources/sales-json/DATE.csv
new file mode 100644
index 0000000..2999baf
--- /dev/null
+++ b/file/src/test/resources/sales-json/DATE.csv
@@ -0,0 +1,9 @@
+EMPNO:int,JOINEDAT:date,JOINTIME:time,JOINTIMES:timestamp
+100,"1996-08-03","00:01:02","1996-08-03 00:01:02"
+110,"2001-01-01","00:00:00","2001-01-01 00:00:00"
+110,"2002-05-03","00:00:00","2002-05-03 00:00:00"
+120,"2005-09-07","00:00:00","2005-09-07 00:00:00"
+130,"2007-01-01","00:00:00","2007-01-01 00:00:00"
+140,"2015-12-31","07:15:56","2015-12-31 07:15:56"
+150,"2015-12-31","13:31:21","2015-12-31 13:31:21"
+200,,,
diff --git a/file/src/test/resources/sales-json/DEPTS.json b/file/src/test/resources/sales-json/DEPTS.json
new file mode 100644
index 0000000..999f645
--- /dev/null
+++ b/file/src/test/resources/sales-json/DEPTS.json
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+[
+ {
+ "DEPTNO": 10,
+ "NAME": "Sales"
+ },
+ {
+ "DEPTNO": 20,
+ "NAME": "Marketing"
+ },
+ {
+ "DEPTNO": 30,
+ "NAME": "Accounts"
+ }
+]
\ No newline at end of file
diff --git a/file/src/test/resources/sales-json/EMPS.json b/file/src/test/resources/sales-json/EMPS.json
new file mode 100644
index 0000000..26db25b
--- /dev/null
+++ b/file/src/test/resources/sales-json/EMPS.json
@@ -0,0 +1,78 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+[
+ {
+ "EMPNO": 100,
+ "NAME": "Fred",
+ "DEPTNO": 10,
+ "GENDER": "",
+ "CITY": "",
+ "EMPID": 30,
+ "AGE": 25,
+ "SLACKER": true,
+ "MANAGER": false,
+ "JOINEDAT": "1996-08-03"
+ },
+ {
+ "EMPNO": 110,
+ "NAME": "Eric",
+ "DEPTNO": 20,
+ "GENDER": "M",
+ "CITY": "San Francisco",
+ "EMPID": 3,
+ "AGE": 80,
+ "SLACKER": null,
+ "MANAGER": false,
+ "JOINEDAT": "2001-01-01"
+ },
+ {
+ "EMPNO": 110,
+ "NAME": "John",
+ "DEPTNO": 40,
+ "GENDER": "M",
+ "CITY": "Vancouver",
+ "EMPID": 2,
+ "AGE": null,
+ "SLACKER": false,
+ "MANAGER": true,
+ "JOINEDAT": "2002-05-03"
+ },
+ {
+ "EMPNO": 120,
+ "NAME": "Wilma",
+ "DEPTNO": 20,
+ "GENDER": "F",
+ "CITY": "",
+ "EMPID": 1,
+ "AGE": 5,
+ "SLACKER": null,
+ "MANAGER": true,
+ "JOINEDAT": "2005-09-07"
+ },
+ {
+ "EMPNO": 130,
+ "NAME": "Alice",
+ "DEPTNO": 40,
+ "GENDER": "F",
+ "CITY": "Vancouver",
+ "EMPID": 2,
+ "AGE": null,
+ "SLACKER": false,
+ "MANAGER": true,
+ "JOINEDAT": "2007-01-01"
+ }
+]
diff --git a/file/src/test/resources/sales-json/EMPTY.json b/file/src/test/resources/sales-json/EMPTY.json
new file mode 100644
index 0000000..dcc7541
--- /dev/null
+++ b/file/src/test/resources/sales-json/EMPTY.json
@@ -0,0 +1,16 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
\ No newline at end of file
diff --git a/file/src/test/resources/sales-json/SDEPTS.json b/file/src/test/resources/sales-json/SDEPTS.json
new file mode 100644
index 0000000..c58b29b
--- /dev/null
+++ b/file/src/test/resources/sales-json/SDEPTS.json
@@ -0,0 +1,42 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+[
+ {
+ "DEPTNO": 10,
+ "NAME": "Sales"
+ },
+ {
+ "DEPTNO": 20,
+ "NAME": "Marketing"
+ },
+ {
+ "DEPTNO": 30,
+ "NAME": "Accounts"
+ },
+ {
+ "DEPTNO": 40,
+ "NAME": "40"
+ },
+ {
+ "DEPTNO": 50,
+ "NAME": "50"
+ },
+ {
+ "DEPTNO": 60,
+ "NAME": "60"
+ }
+]
\ No newline at end of file
diff --git a/site/_docs/file_adapter.md b/site/_docs/file_adapter.md
index 1112a09..809d1b4 100644
--- a/site/_docs/file_adapter.md
+++ b/site/_docs/file_adapter.md
@@ -273,6 +273,57 @@ sqlline> select distinct deptno from depts;
3 rows selected (0.985 seconds)
{% endhighlight %}
+## JSON 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.json` has an integer `DEPTNO` column and a string `NAME` column:
+
+{% highlight json %}
+[
+ {
+ "DEPTNO": 10,
+ "NAME": "Sales"
+ },
+ {
+ "DEPTNO": 20,
+ "NAME": "Marketing"
+ },
+ {
+ "DEPTNO": 30,
+ "NAME": "Accounts"
+ }
+]
+{% endhighlight %}
+
+You can launch `sqlline`, and pointing the file adapter that directory,
+and every JSON file becomes a table:
+
+{% highlight bash %}
+$ ls file/src/test/resources/sales-json
+ -rw-r--r-- 1 jhyde jhyde 62 Mar 15 10:16 DEPTS.json
+
+$ ./sqlline -u "jdbc:calcite:schemaFactory=org.apache.calcite.adapter.file.FileSchemaFactory;schema.directory=file/src/test/resources/sales-json"
+sqlline> !tables
++-----------+-------------+------------+------------+
+| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE |
++-----------+-------------+------------+------------+
+| | adhoc | DATE | TABLE |
+| | adhoc | DEPTS | TABLE |
+| | adhoc | EMPS | TABLE |
+| | adhoc | EMPTY | TABLE |
+| | adhoc | SDEPTS | 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