Querying a File System Introduction

Files and directories are like standard SQL tables to Drill. You can specify a file system “database” as a prefix in queries when you refer to objects across databases. In Drill, a file system database consists of a storage plugin name followed by an optional workspace name, for example . or hdfs.logs.

The following example shows a query on a file system database in a Hadoop distributed file system.

SELECT * FROM hdfs.logs.`AppServerLogs/20104/Jan/01/part0001.txt`;

The path component in backticks is parsed by Drill as a Java string which means that the backslash \ is the escape character that can be used to include special characters in the path. The next example shows another query against a filesystem storage plugin, but now one which is configured to access the local filesystem on an MS Windows machine (which may, of course, included mounted network drives). Note the doubled backslashes needed due to its status as the escape character.

SELECT * FROM dfs.`C:\\Users\\Alice\\Downloads\\sample-dataset.csv`;

The default dfs storage plugin configuration registered with Drill has a default workspace. If you query data in the default workspace, you do not need to include the workspace in the query. Refer to Workspaces for more information.

Drill supports the following file types:

  • Plain text files, including:
    • Comma-separated values (CSV, type: text)
    • Tab-separated values (TSV, type: text)
    • Pipe-separated values (PSV, type: text)
  • Structured data files:

The extensions for these file types must match the configuration settings for your registered storage plugins. For example, PSV files may be defined with a .tbl extension, while CSV files are defined with a .csv extension.

Implicit Columns

Drill 1.8 introduces implicit columns. Implicit columns provide file information, such as the directory path to a file and the file extension. You can query implicit columns in files, directories, nested directories, and files.

The following table lists the implicit columns available and their descriptions:

Implicit Column Name Description
FQN The fully qualified name. Contains the full path to the file, including the file name.
FILEPATH The full path to the file, without the file name.
FILENAME The file name with the file extension. Does not include the path to the file.
SUFFIX The file suffix without the dot (.) at the beginning.
LMT The last modification time of the file as recorded by the containing file system

To access implicit columns, you must explicitly include the columns in a query, as shown in the following example:

   0: jdbc:drill:zk=local> SELECT fqn, filepath, filename, suffix FROM dfs.`/dev/data/files/test.csvh` LIMIT 1;

   |-------------------------------------|--------------------------|---------------|----------------|
   |             fqn                     |      filepath            |  filename     | suffix         |
   |-------------------------------------|--------------------------|---------------|----------------|
   | /dev/data/files/test.csvh           | /dev/data/files          | test.csvh     | csvh           |
   |-------------------------------------|--------------------------|---------------|----------------|

Note

If a table has a column with the same name as an implicit column, such as “suffix,” the implicit column overrides the table column.

If a column name has the same name as an implicit column, you can change the default implicit column name using the [ALTER SYSTEM SESSION SET](/docs/alter-system/) command with the appropriate parameter, as shown in the following example:
   ALTER SYSTEM SET `drill.exec.storage.implicit.suffix.column.label` = appendix;

Use the following configuration options to change the default implicit column names:

   drill.exec.storage.implicit.fqn.column.label
   drill.exec.storage.implicit.filepath.column.label
   drill.exec.storage.implicit.filename.column.label
   drill.exec.storage.implicit.suffix.column.label