Apache Calcite Calcite Logo

File adapter

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.

For example if you define:

  • States - https://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States
  • Cities - https://en.wikipedia.org/wiki/List_of_United_States_cities_by_population

You can then write a query like:

select
    count(*) "City Count",
    sum(100 * c."Population" / s."Population") "Pct State Population"
from "Cities" c, "States" s
where c."State" = s."State" and s."State" = 'California';

And learn that California has 69 cities of 100k or more comprising almost 1/2 of the state’s population:

+---------------------+----------------------+
|     City Count      | Pct State Population |
+---------------------+----------------------+
| 69                  | 48.574217177106576   |
+---------------------+----------------------+

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.

A simple example

Let’s start with a simple example. First, we need a model definition, as follows.

{
  "version": "1.0",
  "defaultSchema": "SALES",
  "schemas": [ {
    "name": "SALES",
    "type": "custom",
    "factory": "org.apache.calcite.adapter.file.FileSchemaFactory",
    "operand": {
      "tables": [ {
        "name": "EMPS",
        "url": "file:file/src/test/resources/sales/EMPS.html"
      }, {
        "name": "DEPTS"
        "url": "file:file/src/test/resources/sales/DEPTS.html"
      } ]
    }
  ]
}

Schemas are defined as a list of tables, each containing minimally a table name and a url. If a page has more than one table, you can include in a table definition selector and index fields to specify the desired table. If there is no table specification, the file adapter chooses the largest table on the page.

EMPS.html contains a single HTML table:

<html>
  <body>
    <table>
      <thead>
        <tr>
          <th>EMPNO</th>
          <th>NAME</th>
          <th>DEPTNO</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td>100</td>
          <td>Fred</td>
          <td>30</td>
        </tr>
        <tr>
          <td>110</td>
          <td>Eric</td>
          <td>20</td>
        </tr>
        <tr>
          <td>110</td>
          <td>John</td>
          <td>40</td>
        </tr>
        <tr>
          <td>120</td>
          <td>Wilma</td>
          <td>20</td>
        </tr>
        <tr>
          <td>130</td>
          <td>Alice</td>
          <td>40</td>
        </tr>
      </tbody>
    </table>
  </body>
</html>

The model file is stored as file/src/test/resources/sales.json, so you can connect via sqlline as follows:

$ ./sqlline
sqlline> !connect jdbc:calcite:model=file/src/test/resources/sales.json admin admin
sqlline> select * from sales.emps;
+-------+--------+------+
| EMPNO | DEPTNO | NAME |
+-------+--------+------+
| 100   | 30     | Fred |
| 110   | 20     | Eric |
| 110   | 40     | John |
| 120   | 20     | Wilma |
| 130   | 40     | Alice |
+-------+--------+------+
5 rows selected

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 tables on those pages. The tables have more complex formats, and the file adapter helps us locate and parse data in those tables.

Tables can be simply defined for immediate gratification:

{
  tableName: "RawCities",
  url: "https://en.wikipedia.org/wiki/List_of_United_States_cities_by_population"
}

And subsequently refined for better usability / querying:

{
  tableName: "Cities",
  url: "https://en.wikipedia.org/wiki/List_of_United_States_cities_by_population",
  path: "#mw-content-text > table.wikitable.sortable",
  index: 0,
  fieldDefs: [
    {th: "2012 rank", name: "Rank", type: "int", pattern: "(\\d+)", matchGroup: 0},
    {th: "City", selector: "a", selectedElement: 0},
    {th: "State[5]", name: "State", selector: "a:eq(0)"},
    {th: "2012 estimate", name: "Population", type: "double"},
    {th: "2010 Census", skip: "true"},
    {th: "Change", skip: "true"},
    {th: "2012 land area", name: "Land Area (sq mi)", type: "double", selector: ":not(span)"},
    {th: "2012 population density", skip: "true"},
    {th: "ANSI", skip: "true"}
  ]
}

Connect and execute queries, as follows.

$ ./sqlline
sqlline> !connect jdbc:calcite:model=file/src/test/resources/wiki.json admin admin
sqlline> select * from wiki."RawCities";
sqlline> select * from wiki."Cities";

Note that Cities is easier to consume than RawCities, because its table definition has a field list.

The file adapter uses Jsoup for HTML DOM navigation; selectors for both tables and fields follow the Jsoup selector specification.

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

The file adapter can select DOM nodes within a cell, replace text within the selected element, match within the selected text, and choose a data type for the resulting database column. Processing steps are applied in the order described and replace and match patterns are based on Java regular expressions.

Further examples

There are more examples in the form of a script:

$ ./sqlline -f file/src/test/resources/webjoin.sql

(When running webjoin.sql you will see a number of warning messages for each query containing a join. These are expected and do not affect query results. These messages will be suppressed in the next release.)

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:

DEPTNO:int,NAME:string
10,"Sales"
20,"Marketing"
30,"Accounts"

You can launch sqlline, and pointing the file adapter that directory, and every CSV file becomes a table:

$ 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)

Future improvements

We are continuing to enhance the adapter, and would welcome contributions of new parsing capabilities (for example parsing JSON files) and being able to form URLs dynamically to push down filters.